Learning Objectives – after this chapter you will be able to:
- Explain PL/SQL, anonymous blocks vs stored procedures, and the role of the PL/SQL engine.
- List the features and advantages of PL/SQL.
- Write a PL/SQL block with declarative, executable and exception-handling parts.
- Declare variables, constants and %TYPE variables; use SELECT … INTO and the := operator.
- Use nested and labelled blocks and understand scope and visibility.
- Display output with DBMS_OUTPUT and SET SERVEROUTPUT ON.
What is PL/SQL?
PL/SQL is Oracle’s procedural language extension to SQL. It mixes SQL statements with procedural statements such as IF and loops. PL/SQL is a superset of SQL: it uses SQL for data retrieval and manipulation and its own statements for data processing.
- Anonymous block – a PL/SQL block that appears within an application (wherever SQL can appear).
- Stored procedure – a block stored in the database with a name and executed by name. Oracle also has functions (return a value) and packages (collections of procedures and functions).
PL/SQL engine
PL/SQL blockprocedural + SQL statements
PL/SQL engineProcedural statement executor runs procedural statements
Oracle RDBMSSQL statement executor runs the SQL commands
The PL/SQL engine compiles and executes PL/SQL blocks. It is available in the Oracle Server and in tools such as Oracle Forms and Oracle Reports.
Features and Advantages
Features
- Block structure – programs are blocks, which can be nested.
- Variables and constants.
- Control structures – IF, FOR, WHILE (the most important extension to SQL).
- Exception handling – errors are detected and handled.
- Modularity – procedures and functions with parameters.
- Cursors – implicit for DML and single-row SELECT; explicit for multi-row queries.
- Built-in functions – most SQL functions.
Advantages
- Support for SQL – SQL commands, functions, operators and datatypes; dynamic SQL (native dynamic SQL from Oracle8i, earlier through DBMS_SQL) besides static SQL.
- Better performance – the whole block is sent to the server as one unit, reducing network traffic.
- Portability – runs unchanged on any platform where Oracle runs.
PL/SQL Block Structure
[DECLARE declarations of variables, constants, cursors, exceptions] BEGIN executable commands -- the only mandatory part [EXCEPTION exception handlers] END;Declarative part
declare v_rollno number(5); v_count number(2) := 0; -- initialised v_name students.name%type; -- same type as STUDENTS.NAME done boolean := FALSE; bonus constant number(3) := 500; -- constant: cannot be changed begin null; end;
- %TYPE declares a variable with the datatype of a table column.
- If a variable and a column have the same name, SQL statements treat the name as the column (columns take precedence).
- PL/SQL datatype categories: Scalar (NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN), Composite (RECORD, TABLE, VARRAY), Reference (REF CURSOR, REF object_type) and LOB (BFILE, BLOB, CLOB, NCLOB). BOOLEAN takes TRUE, FALSE or NULL.
First PL/SQL Block and SELECT … INTO
inspay.sql – run with SQL> start c:\orabook\inspay.sql
declare v_rollno students.rollno%type; begin -- roll number of the student who joined most recently select max(rollno) into v_rollno from students; insert into payments values (v_rollno, sysdate, 1000); commit; end; /
Output
PL/SQL procedure successfully completed.
- In PL/SQL, SELECT only retrieves data; the INTO clause names the variable(s) that receive the values. The number and types of INTO variables must match the selected columns.
- Comments:
-- single lineand/* multi-line */. - Assignment uses := (e.g.
hra := bs * 0.05;). Arithmetic operators: + − * / and ** (exponentiation). - Most SQL functions work in PL/SQL statements, but DECODE and group functions (AVG, COUNT, GROUPING, MIN, MAX, SUM, STDDEV, VARIANCE) can be used only inside SQL commands within the block.
Nested Blocks, Scope, Visibility and Labels
- A block can be defined inside another block – nested blocks, each with its own declarations and exception handlers.
- Scope – the region where a variable can be used: from its declaration to the end of its block (including inner blocks).
- Visibility – a variable is visible where it can be referred to without a qualifier. An inner variable with the same name hides the outer one.
- A label
<<name>>before a block names it;label.variableaccesses a hidden variable.
Accessing a hidden variable through a label
<<mainblock>>
declare
n number(5);
begin
<<nestedblock>>
declare
n number(5);
begin
n := 20; -- N of the nested block
mainblock.n := 50; -- hidden N of the main block
end;
end;Displaying Output – DBMS_OUTPUT
DBMS_OUTPUT is a package (a collection of procedures and functions) supplied with Oracle. PUT writes pieces of a line; PUT_LINE writes a value followed by an end-of-line. Output appears only when the program unit has completed and SERVEROUTPUT is ON (set it in every new SQL*Plus session).
SQL and PL/SQL together with DBMS_OUTPUT
SQL> set serveroutput on
declare
v_avgfee courses.fee%type;
v_orafee courses.fee%type;
begin
select avg(fee) into v_avgfee from courses;
select fee into v_orafee from courses where ccode = 'ora';
update courses set fee = greatest(v_avgfee, v_orafee) where ccode = 'vbnet';
commit;
dbms_output.put_line('New VB.NET fee : ' || greatest(v_avgfee, v_orafee));
end;
Key Takeaways
- PL/SQL = SQL + procedural constructs; the PL/SQL engine sends SQL to the SQL statement executor.
- A block has DECLARE, BEGIN (mandatory) and EXCEPTION parts.
- Use %TYPE, SELECT … INTO and := ; labels reach hidden variables.
- SET SERVEROUTPUT ON is required to see DBMS_OUTPUT.PUT_LINE output.
Chapter 15 Notes (PDF)
ch15.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 15 notes (PDF). Click a question to see the answer.
Answer : PL/SQL is Oracle’s procedural language extension to SQL. It allows SQL statements to be mixed with procedural statements such as IF statements and loops. It is a superset of SQL because it uses SQL for data retrieval and manipulation and its own statements for data processing.
Answer : An anonymous block is a PL/SQL block that appears within an application wherever SQL statements can appear; it has no name. A stored procedure is a PL/SQL block stored in the database with a name, which application programs execute using that name. Oracle also allows functions (which return a value) and packages (collections of procedures and functions).
Answer : The PL/SQL engine compiles and executes PL/SQL blocks. It executes the procedural statements using its procedural statement executor and sends the SQL commands to the SQL statement executor in the Oracle RDBMS. It is available in the Oracle Server and in tools such as Oracle Forms and Oracle Reports.
Answer : Block structure (with nesting), variables and constants, control structures (IF, FOR loop, WHILE loop), exception handling, modularity (procedures and functions with parameters), cursors and built-in functions.
Answer : A PL/SQL block is sent to the Oracle server as one unit. Without PL/SQL each SQL command is passed to the server separately, which heavily increases network traffic; sending a collection of statements as a block improves performance.
Answer : Static SQL is written at the time of writing the program. Dynamic SQL is the process of constructing and executing SQL statements on the fly at run time. From Oracle8i PL/SQL supports native dynamic SQL, which is easier than the earlier DBMS_SQL package.
Answer : The declarative part (DECLARE – variables, cursors, exceptions), the executable part (BEGIN ... – SQL and PL/SQL commands) and the exception-handling part (EXCEPTION – handlers for errors raised in the executable part), ending with END. Only the executable part is mandatory.
Answer : %TYPE declares a variable with the same datatype as a table column, e.g. v_name students.name%type takes whatever is the datatype of the NAME column of STUDENTS.
Answer : Oracle assumes the column is being referenced whenever the name is used in SQL commands – column names take precedence over variable names in SQL statements.
Answer : Scalar (a single value: NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN), Composite (a collection of components: RECORD, TABLE, VARRAY), Reference (a pointer: REF CURSOR, REF object_type) and LOB (holds a LOB locator: BFILE, BLOB, CLOB, NCLOB). BOOLEAN can hold TRUE, FALSE or NULL.
Answer : In PL/SQL, SELECT only retrieves data; the INTO clause specifies the variable(s) into which the retrieved values are copied, e.g. SELECT SUM(amount) INTO v_sum FROM payments WHERE rollno = 102. The number and datatypes of the INTO variables must match the selected columns.
Answer : A constant is declared with the CONSTANT keyword and an initial value, e.g. bonus CONSTANT number(3) := 500; any attempt to change it gives an error. Single-line comments start with two hyphens (--) and multi-line comments are enclosed in /* and */.
Answer : Scope is the region of the program in which a variable can be used – from its declaration to the end of its block, including nested blocks. A variable is visible where it can be referred to without a qualifier. If an inner block declares a variable with the same name, the outer variable is still in scope but not visible (hidden) in the inner block.
Answer : By labelling the outer block with <<label>> placed before it and prefixing the variable with the label, e.g. mainblock.n := 50 assigns to the variable N of the block labelled MAINBLOCK even though the inner block declares its own N.
Answer : Using the DBMS_OUTPUT package: PUT writes pieces that make up a line and PUT_LINE writes a value followed by an end-of-line character (for NUMBER, VARCHAR2 or DATE values). The output is seen only when the program unit has completed and the SQL*Plus variable SERVEROUTPUT is set ON (SET SERVEROUTPUT ON in each new session).
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
- Oracle Corporation. (n.d.). Oracle Database PL/SQL Packages and Types Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/
- Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.