10.1 How do I…Create anonymous PL/SQL blocks?Problem
I want to perform tasks not well-suited for SQL on the database, such as performing conditional logic and working with the procedural components available in PL/SQL. Because the PL/SQL block is the smallest logical unit of PL/SQL code, I want to start by creating a PL/SQL block to perform a simple action. How do I create anonymous PL/SQL blocks in Oracle?
Technique
PL/SQL is a block structured language. PL/SQL blocks are implemented using anonymous blocks, procedures, and functions. Unlike functions and procedures, no name is associated with an anonymous PL/SQL block and, hence, cannot be referenced from other PL/SQL blocks. Anonymous blocks are standalone; they neither take input parameters nor return values and can be nested within another PL/SQL block. The anonymous PL/SQL block is made up of three sections, of which two are optional (indicated by square brackets):
DECLARE
Declarative Section]
BEGIN
Executable Section
[EXCEPTION
Exception handling Section]
END;
The declarative section, started with the DECLARE statement, is where objects are declared. User-defined variables, datatypes, cursors, and PL/SQL subprograms local to the block are declared in this section. The objects within the declarative section are limited to the block and cannot be used outside it. The executable section starts with the BEGIN keyword and contains procedural and SQL statements that are executed when the block is run. Next, the exception section starts with the EXCEPTION keyword and contains exception handlers to handle errors that occur during processing. The error handling code in the exception section is executed only when an error occurs. A PL/SQL block is terminated with the END statement and a semicolon.
Steps
1. Run SQL*Plus and connect to the database as the WAITE user account. Run the CHP10_1.SQL file at the prompt. It has the simplest form of the anonymous block, one that does not have the optional declaration and exception sections. The PL/SQL block contained in the file calls the DBMS_OUTPUT.PUT_LINE to display a message in SQL*Plus. Figure 10.1 shows the code and the output of the anonymous PL/SQL block in SQL*Plus.
For the output of the DBMS_OUTPUT package to be displayed in SQL*Plus, the SERVEROUTPUT system variable must be set to ON. The anonymous block calls the PUT_LINE procedure in the DBMS_OUTPUT built-in package to display a text message in SQL*Plus. The PUT_LINE procedure is passed a string to be displayed within SQL*Plus and can accept DATE and NUMBER variables as well. To display data of other datatypes, a conversion function such as TO_CHAR must be used. Table 10.1 shows procedures in the DBMS_OUTPUT package.
Table 10.1 Procedures in the DBMS_OUTPUT package
Procedure Description ENABLE Enables message output (or SERVEROUTPUT ON) DISABLE Disables message output (or SERVEROUTPUT OFF) PUT_LINE Puts the output on a new line PUT Puts the output on the same line NEW_LINE Signals the beginning of a new line GET_LINE Retrieves a line from the buffer GET_LINES Retrieves an array of lines from the buffer Note that line 4 in the file has a / signaling SQL*Plus to run the anonymous block after it has loaded the block into the SQL*Plus buffer. The DBMS_OUTPUT.PUT_LINE procedure is important because it is often used to debug PL/SQL code.
To debug a PL/SQL block with compilation errors, issue a SHOW ERRORS command at the SQL*Plus prompt; you can also include the SHOW ERRORS command at the end of the block in the line following the /, as shown in the preceding example. This makes SQL*Plus display any errors generated by the last module compiled in the current session. A blank line is left as the line following the /, to avoid a benign SQL*Plus error (Input truncated to n characters).
2. Run CHP10_2.SQL, which has a PL/SQL block that declares and uses a numeric variable. Figure 10.2 shows the code and output.
Lines 1 and 2 contain the declarative section of the block, which is started with the DECLARE keyword and continues until the BEGIN keyword. When declaring variables in PL/SQL, the datatype is specified after the variable name. Lines 4 and 5 contain the code segment executed when the block is run. The statement on line 4 sets the value of the variable ORDER_NO to the number 3000. The colon-equals operator (:=) is used to set a variable to a value in PL/SQL.
Table 10.2 contains the operators used in PL/SQL, listed in the order of descending precedence; operators with higher precedence are evaluated first. Whenever the precedence of operators is in doubt or you need to override the default precedence, use the ( and ) expression delimiters around the expression that needs to be evaluated first.
Exceptions are handled in the exception section starting on line 6. The WHEN OTHERS exception handler in line 7 handles any exceptions not dealt with by other exception handlers and must be the last exception handler. The NULL statement in line 8 is a “do nothing” construct, and here it specifies that no action is taken for exceptions.
How-To’s 10.11 and 10.12 cover the process of handling exceptions in detail. Both the declarative section and the exception section are optional. If you don’t need any local variables for your block or you don’t want to handle exceptions that occur, these sections can be removed.
Table 10.2 PL/SQL operators
Operator Description ** Exponentiation NOT† Logical negation + Unary identity - Unary negation * Multiplication / Division + Addition - Subtraction || String concatenation = Equality != Not equals <> Not equals ~= Not equals ^= Not equals < Less than > Greater than <= Less than or equal >= Greater than or equal IS NULL Comparison for null LIKE Comparison for match || String concatenation = Equality != Not equals <> Not equals ~= Not equals ^= Not equals < Less than > Greater than <= Less than or equal >= Greater than or equal IS NULL Comparison for null LIKE Comparison for match || String concatenation = Equality != Not equals <> Not equals ~= Not equals ^= Not equals < Less than > Greater than <= Less than or equal >= Greater than or equal IS NULL Comparison for null LIKE Comparison for match BETWEEN Range of values IN Set membership AND Conjunction OR Inclusion := Assignment => Association .. Range operator † Negation operator NOT can be used as IS NOT NULL, NOT LIKE, NOT BETWEEN, NOT IN.
SQL> START CHP10_3
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 ORDER_DATE DATE; -- Date variable
3 ORDER_NO NUMBER := 24; -- Initialized numeric variable
4 PI CONSTANT NUMBER := 3.1416; -- Numeric constant
5 /* The declarative section of the outer block ends here. All the
6 variables declared above have scope limited to the outer block */
7 BEGIN
8 ORDER_NO := 25;
9 DECLARE -- Inner block begins here
10 CUST_NAME VARCHAR2(30); -- Character variable with a width of 20
11 BEGIN
12 ORDER_NO := 26; -- Outer block variable used in inner block
13 CUST_NAME := ‘JOE SHMOE’;
14 DBMS_OUTPUT.PUT_LINE(‘ORDER NO = ‘ || ORDER_NO);
15 DBMS_OUTPUT.PUT_LINE(‘CUST_NAME = ‘ || CUST_NAME);
16 DBMS_OUTPUT.PUT_LINE(‘PI = ‘ || PI);
17 EXCEPTION
18 WHEN OTHERS THEN
19 DBMS_OUTPUT.PUT_LINE(‘Inner block error.’);
20 END; -- Inner block ends here
21 EXCEPTION
22 WHEN OTHERS THEN
23 DBMS_OUTPUT.PUT_LINE(‘Outer block error.’);
24 END;
25 /
DATE Date Date values with a format as in the database BOOLEAN Boolean TRUE or FALSE NUMBER Numeric Integer and real values that can be defined with a precision and scale BINARY_INTEGER Numeric Signed integers, less storage than NUMBER POSITIVE Numeric Subtype of BINARY_INTEGER, positive integer values POSITIVEN Numeric Subtype of BINARY_INTEGER, positive integer values, null values not allowed NATURAL Numeric Subtype of BINARY_INTEGER, non-negative integer values NATURALN Numeric Subtype of BINARY_INTEGER, non-negative integer values, null values not allowed SIGNTYPE Numeric -1, 0, or 1 values only DECIMAL Numeric Subtype of NUMBER, decimal DEC Numeric Same as DECIMAL DOUBLE PRECISION Subtype of NUMBER, real numbers with high precision INTEGER Numeric Subtype of NUMBER, integer values only INT Numeric Same as INTEGER NUMERIC Numeric Same as NUMBER REAL Numeric Same as NUMBER SMALLINT Numeric Subtype of NUMBER, small range of integer FLOAT Numeric Subtype of NUMBER, requires a binary precision but no scale PLS_INTEGER Numeric Signed integers, faster than NUMBER and BINARY_INTEGER VARCHAR2 Character Variable-length character strings CHAR Character Fixed-length character strings CHARACTER Character Same as CHAR LONG Character Variable-length byte strings RAW Character Binary data or byte strings, no character set conversions LONG RAW Character Binary data or byte strings ROWID Character Consists of data object number, data file, data block, and row in the data block NCHAR Character Fixed-length NLS character strings NVARCHAR2 Character Variable-length NLS character strings How It Works
Step 1 presents a simple PL/SQL block showing the basic structure of a block. In Step 2, the block contains a declarative section, an executable section, and an exception section. Both the declarative and exception sections are optional. Step 3 demonstrates how blocks can be nested in PL/SQL, and nesting is important when you need exceptions handled separately within each block. Both the outermost and innermost blocks can contain all three block sections. Step 3 also declares variables of a variety of scalar datatypes in a PL/SQL block.
Comments
The PL/SQL block is the fundamental structure in PL/SQL. Anonymous PL/SQL blocks provide control over scope and visibility of identifiers and exception handling. Anonymous blocks are not stored in the database, and typically they are constructed dynamically to be executed only once. You can nest anonymous blocks in the execution or exception section of a PL/SQL block but not in the declarative section. To declare a PL/SQL block in the declarative section, the block has to be a local subprogram—a function or procedure local to the block, which is addressed in How-To 10.4.