This section details information about Oracle PL/SQL. Programming Language/SQL (PL/SQL) is an Oracle extension of the SQL statement set which allows the developer to impose flow control and logic design onto unstructured SQL command blocks. PL/SQL also implements basic exception handling.
SQL *Plus allows you to import and execute SQL scripts from previously saved text files (command files). This can be very handy because you often have functions that you need over and over, especially as a DBA. The problem with this approach is that you may want to perform the same function with a different database object, for instance you may want a script that creates a user account on an Oracle system but probably want it to specify the new account name each time. You can achieve this with variable substitution.
You can use variable substitution on any WHERE clause, text string, column name or table name.
The first type of substitution is the single ampersand "&".
If this statement were typed into SQL *Plus or retrieved as a command file you would see a prompt appear asking you to specify the value of &SEC_NUMBER, this means you can vary the result set whilst using the same pre prepared SQL statement. Remember that date and string values must always be enclosed in single quotes, you can get around having to type them in by putting them into the SQL statement.
SELECT AUTHOR FROM JD11.BOOK WHERE TITLE = '&REQUIRED_TITLE'
You now don't have to remember the quotes as they are part of the prepared statement.
SELECT AUTHOR, &OTHER_FIELD FROM JD11.BOOK WHERE &CONDITION
The first time we run this statement we will be asked for the value of &&SEC_NUMBER as before, but if we run the statement again (without closing the SQL *Plus session) it uses the value that you specified the first time and doesn't prompt you again.
To change a variable saved by a double ampersand command or define a new variable for future use you use the DEFINE command (can be abbreviated to DEF).
If we run this statement and define &&SEC_NUMBER as 10,
then we decide we really wanted section 9 for the rest of our work then we can use the DEFINE command to change the value of the variable (or we could have defined the variable at the session start to avoid all prompts).
Note that the "&&" is not required here.
If we wanted to remove the variable completely we can use the UNDEFINE command (can be abbreviated to UNDEF).
UNDEF SEC_NUMBER
SQL *Plus Command Reference
<><><><><><><>
PL/SQL blocks come in three types, these are anonymous procedure, named procedure and named function. All of these block types share most PL/SQL features so during this tutorial the features that apply to all block types will be grouped into single subjects.
A named procedure may be called, it may accept inbound parameters but won't explicitly return any value.
BEGIN
Statements that make up the block.
EXCEPTION
All exception handlers.
END;
End of block marker.
Example :-
DECLARE
TEMP_COST NUMBER(10,2);
BEGIN
SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = 21;
IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21; END IF;
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'ISBN 21 NOT FOUND');
END;
<><><><><><><>
The rules of block structure are :-
Every unit of PL/SQL must constitute a block. As a minimum there must be the delimiting words BEGIN and END around the executable statements.
If PL/SQL variables or objects are defined for use in a block then you must also have a DECLARE section.
PL/SQL blocks may be nested, nesting can occur wherever an executable statement could be placed (including the EXCEPTION section).
A variable or object's (cursor, constant Etc.) scope is determined by the block that it is declared in. It is only available until the block it is defined in ends execution. Remember that block variables are defined under the DECLARE keyword.
NUM8 NUMBER(3,1);
XYZ NUMBER(2,2) := 31.8;
ABC12 NUMBER(9,2) := XYZ * 131;
V_CHR1 CHAR(89);
V_CHR2 VARCHAR2(12) := "JANUARY";
TODAY DATE := SYSDATE;
TRUEFALSE BOOLEAN;
VAT CONSTANT NUMBER(4,2) := 17.5;
A PL/SQL record is a variable that contains a collection of separate fields. Each field is individually addressable. You can reference the field names in both assignments and expressions. The fields within a record may have different datatypes and sizes, like the columns of a database table. Records are a convenient way of storing a complete fetched row from a database table.
DECLARE
REC1 JD11.BOOK%ROWTYPE;
REC4 JD11.BOOK%ROWTYPE;
The above declaration sets the object REC1 to be a record object holding fields that match the columns in the BOOK table. It doesn't hold any values until it is populated.
Assign values into a PL/SQL record by naming the record after the INTO keyword of a SELECT statement. The INTO keyword defines the name specification for the storage area(s) of queried value(s).
END;
END;
REC4.SECTION_ID := 10; ELSE
REC4.SECTION_ID := 7; END IF;
END;
NUM1 := NUM1 + NUM2 + (NUM3 * 3);
ANUM3 := AVG_COST * 7 + NVL(AVG_BUY_COST, 0);
SENT := 1;
STR1 := 'No rest until one = '|| TO_CHAR(SENT);
CHR1 := 'ABCDEFG';
FLAG1 := TRUE;
FLAG1 := FALSE;
MALE := UPPER(TITLE);
Whenever a SQL statement is issued the Database server opens an area of memory in which the command is parsed and executed. This area is called a cursor. Microsoft tends to refer to cursors as datasets throughout much of their databse product documentation..
PL/SQL provides some attributes which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements.
The SQL cursor attributes are :-
<><><><><><><>
An example follows :-
DECLARE
ROW_DEL_NO NUMBER;
BEGIN
DELETE * FROM JD11.SECTION;
ROW_DEL_NO := SQL%ROWCOUNT;
END;
Explicit cursors.
<><><><><><><>
BEGIN
OPEN MYCUR;
END;
To access the rows of data within the cursor we use the FETCH statement.
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN OPEN MYCUR;
FETCH MYCUR INTO THISISBN, THISCOST;
END;
The FETCH statement reads the column values for the current cursor row and puts them into the specified variables. The cursor pointer is updated to point at the next row. If the cursor has no more rows the variables will be set to null on the first FETCH attempt, subsequent FETCH attempts will raise an exception.
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN
OPEN MYCUR; FETCH MYCUR INTO THISISBN, THISCOST;
CLOSE MYCUR;
END;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN OPEN MYCUR; LOOP FETCH MYCUR INTO THISISBN, THISCOST;
EXIT WHEN MYCUR%NOTFOUND; END LOOP; CLOSE MYCUR;
PL/SQL records may be based on a cursor. This is very convenient for processing rows from the active data set. An example follows :
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO PARTBOOK;
EXIT WHEN MYCUR%NOTFOUND;
IF PARTBOOK.ISBN = 21 THEN PARTBOOK.COST = 19.10;
END IF;
END LOOP;
CLOSE MYCUR;
END;
You can use the WHERE CURRENT OF clause to execute DML commands against the current row of a cursor, this makes it easier to update rows. An example is below :
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO PARTBOOK;
EXIT WHEN MYCUR%NOTFOUND;
IF PARTBOOK.ISBN = 21 THEN
DELETE FROM JD11.BOOK WHERE CURRENT OF MYCUR;
END IF;
END LOOP;
CLOSE MYCUR;
END;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR(21);
OPEN MYCUR(101);
FETCH MYCUR INTO PARTBOOK;
CLOSE MYCUR;
END;
Exceptions are identifiers in PL/SQL that are raised during the execution of a block to terminate its action. A block is always terminated when PL/SQL raises an exception but you can define your own error handler to capture exceptions and perform some final actions before quitting the block. If PL/SQL handles the exception within the block then the exception will not propagate out to an enclosing block or environment.
There are two classes of exceptions, these are :
Predefined - Oracle predefined errors which are associated with specific error codes.
The two most common errors originating from a SELECT statement occur when it returns no rows or more than one row (remember that this is not allowed). The example below deals with these two onditions.
TEMP_ISBN NUMBER(10);
BEGIN SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
> 21;
IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN > 21; ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE
ISBN > 21; END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'NOT FOUND');
WHEN TOO_MANY_ROWS THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'TOO MANY');
END;
TEMP_ISBN NUMBER(10);
BEGIN SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
> 21; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN > 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN > 21; END IF; COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE,MESSAGE) VALUES(99,‘NOT FOUND’); WHEN TOO_MANY_ROWS THEN INSERT INTO JD11.ERRORS (CODE,MESSAGE) VALUES(99,‘TOO MANY’);
WHEN OTHERS THEN
INSERT INTO JD11.ERRORS(CODE, MESSAGE) VALUES(99, ‘SOME ERROR OCCURRED’);
END;
This block will trap all errors. If the exception isn't no rows returned or too many rows returned then the OTHERS action will perform the error handling.
TEMP_ISBN NUMBER(10);
ERR_MSG VARCHAR2(100);
ERR_CDE NUMBER;
BEGIN SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
> 21; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN > 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN > 21; END IF; COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘NOT FOUND’); WHEN TOO_MANY_ROWS THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘TOO MANY’); WHEN OTHERS THEN
ERR_MSG := SUBSTR(SQLERRM,1,100);
ERR_CDE := SQLCODE;
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(ERR_CDE, ERR_MSG);
END;
If you explicitly need to raise an error you can do this by using the RAISE statement. For example, if you wanted to raise an error if a SELECT statement found a row (which it shouldn't have found) you would only be able to do this by raising your own error.
DECLARE
TEMP_COST NUMBER(10,2);
TEMP_ISBN NUMBER(10);
ERR_MSG VARCHAR2(100);
ERR_CDE NUMBER;
BEGIN
SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
< 0;
RAISE THIS_IS_WRONG
EXCEPTION
WHEN THIS_IS_WRONG THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘OOPS’);
END;
The example above shouldn't find any rows with an ISBN less than 0. If it did we raise our own exception to force PL/SQL to pass control to our own action within the EXCEPTION section.
IF A = 1 THEN D := 1 + E; E1 := H * 1.176;
ELSIF A = 1 THEN E := 1 + D; E1 := H * 1.1876; E2 := 3;
LOOP I := I + 1; IF I = 10 THEN
EXIT; END IF;
END LOOP;
LOOP
I := I + 1;
EXIT WHEN I = 10;
END LOOP;
FOR CNT IN REVERSE 18 .. 121 LOOP A := A + (CNT * 10);
END LOOP;
The condition is evaluated at the start of the loop, if it is false at the start of the loop then no iterations will be performed. Note that if the condition(s) of the WHILE loop don't change within the loop then the loop will never end. You can include one or many EXIT statements if early breakouts are required.
TEMP_COST NUMBER(10,2);
TEMP_ISBN NUMBER(10);
BEGIN
SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
> 21;
IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN > 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN > 21; END IF;
<<INNERBLK1>> DECLARE I NUMBER(4); AB CHAR(10); CNT NUMBER(3); A NUMBER(6); BEGIN
<<OUTERLOOP>> LOOP I := I + 1;
IF I = 5 THEN
GOTO DOCOMMITTAG; END IF; IF I = 10 THEN
EXIT OUTERLOOP;
END IF;
<<INNERLOOP>> FOR CNT IN 12 .. 150 LOOP
A := A + (CNT * 10);
EXIT OUTERLOOP
WHEN A > 12900;
END LOOP INNERLOOP;
END LOOP OUTERLOOP;
<<DOCOMMITTAG>> COMMIT; END;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘NOT FOUND’); WHEN TOO_MANY_ROWS THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘TOO MANY’);
END;
A procedure or function is a named PL/SQL block - they are normally stored in the database within package specifications (which is a wrapper for a group of named blocks) but they may be stored on the database individually. The advantage of this is that when a block is placed on the database it is parsed at the time it is stored. When it is subsequently executed Oracle already has the block compiled and it is therefore much faster. It is also a good way of grouping application functionality together and exposing only function calls (not the code itself). It is possible to invoke a stored procedure or function from most Oracle tools including SQL *Plus. It is also possible to attach a block to a database or Form trigger.
· Modify one routine to eliminate duplicate testing.
BEGIN SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = 21; IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21; END IF;
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN 21 NOT FOUND’);
END;
This block could be saved to a command file and executed from there but it would be parsed each and every time it was called. To save this block to the database we need to name it.
IS BEGIN SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = 21; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21; END IF; COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN 21 NOT FOUND’);
END MYPROC1;
CREATE OR REPLACE PROCEDURE MYPROC1
(REQISBN IN NUMBER,
MYVAR1 IN OUT CHAR,
TCOST OUT NUMBER)
TEMP_COST NUMBER(10,2))
IS BEGIN SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN =
REQISBN; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = REQISBN; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = REQISBN; END IF;
TCOST := TEMP_COST; COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN NOT FOUND’);
END MYPROC1;
EXECUTE JD11.MYPROC1 (21,:PCOST) - will execute the procedure in SQL *PLUS
(PCOST is the name of a SQL *PLUS bind variable). 21 will be passed into the
procedure, the value of TCOST will be passed out of the procedure and stored in
:PCOST.
BEGIN SELECT COST INTO RCOST FROM JD11.BOOK WHERE ISBN = 21;
RETURN (RCOST);
END MYFUNC1;
EXECUTE :PCOST := JD11.MYFUNC1 - will execute the procedure in SQL *PLUS
(PCOST is the name of a SQL *PLUS bind variable). The purchase value of book 21 will be passed out of the function and stored in :PCOST.
CREATE OR REPLACE PACKAGE MYPACK1
AS
PROCEDURE MYPROC1 (REQISBN IN NUMBER, MYVAR1 IN OUT CHAR,TCOST OUT NUMBER);
FUNCTION MYFUNC1;
END MYPACK1;
To create a package body we now specify each PL/SQL block that makes up the package, note that we are not creating these blocks separately (no CREATE OR REPLACE is required for the procedure and function definitions). An example follows :
CREATE OR REPLACE PACKAGE BODY MYPACK1 AS
PROCEDURE MYPROC1
(REQISBN IN NUMBER,MYVAR1 IN OUT CHAR, TCOST OUT NUMBER)
TEMP_COST NUMBER(10,2))
IS BEGIN
SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = REQISBN;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = REQISBN;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = REQISBN;
END IF;
TCOST := TEMP_COST;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN NOT FOUND’);
END MYPROC1;
FUNCTION MYFUNC1
RETURN NUMBER
IS RCOST NUMBER(10,2);
BEGIN
SELECT COST INTO RCOST FROM JD11.BOOK WHERE ISBN = 21;
RETURN (RCOST);
END MYFUNC1;
END MYPACK1;
Triggers may be called BEFORE or AFTER the following events :
INSERT, UPDATE and DELETE.
Triggers may be STATEMENT or ROW types. STATEMENT triggers fire BEFORE or AFTER the execution of the statement that caused the trigger to fire. ROW triggers fire BEFORE or AFTER any affected row is processed.
An example of a statement trigger follows :
CREATE OR REPLACE TRIGGER MYTRIG1
BEFORE DELETE OR INSERT OR UPDATE ON JD11.BOOK
BEGIN
IF (TO_CHAR(SYSDATE,’dy’) IN (‘sat’,’sun’)) OR (TO_CHAR(SYSDATE,’hh24:mi’) NOT BETWEEN ’08:30’ AND ’18:30’)
THEN RAISE_APPLICATION_ERROR(-20500,’Table is secured’);
END IF;
END;
After the CREATE OR REPLACE statement is the object identifier (TRIGGER) and the object name (MYTRIG1). This trigger specifies that before any data change event on the BOOK table this PL/SQL code block will be compiled and executed. The user will not be allowed to update the table outside of normal working hours.
An example of a row trigger follows :-
FOR EACH ROW
BEGIN
IF DELETING THEN
INSERT INTO JD11.XBOOK (PREVISBN, TITLE, DELDATE) VALUES (:OLD.ISBN, :OLD.TITLE, SYSDATE);
ELSIF INSERTING THEN
INSERT
INTO JD11.NBOOK (ISBN, TITLE, ADDDATE) VALUES (:NEW.ISBN,
:NEW.TITLE, SYSDATE);
ELSIF UPDATING (‘ISBN) THEN
INSERT INTO JD11.CBOOK (OLDISBN, NEWISBN, TITLE, UP_DATE) VALUES (:OLD.ISBN :NEW.ISBN, :NEW.TITLE, SYSDATE);
ELSE /* UPDATE TO ANYTHING ELSE THAN ISBN */
INSERT INTO JD11.UBOOK (ISBN, TITLE, UP_DATE) VALUES(:OLD.ISBN :NEW.TITLE, SYSDATE);
END IF
END;
In this case we have specified that the trigger will be executed after any data change event on any affected row. Within the PL/SQL block body we can check which update action is being performed for the currently affected row and take whatever action we feel is appropriate. Note that we can specify the old and new values of updated rows by prefixing column names with the :OLD and :NEW qualifiers.
Runtime Variable Substitution
SQL *Plus allows you to import and execute SQL scripts from previously saved text files (command files). This can be very handy because you often have functions that you need over and over, especially as a DBA. The problem with this approach is that you may want to perform the same function with a different database object, for instance you may want a script that creates a user account on an Oracle system but probably want it to specify the new account name each time. You can achieve this with variable substitution.
Stored command files are fetched and run by specifying the file name (with full path) after the "@" symbol. For example :-
@C:\SCRIPTS\QUERY\JD11\FINDSECT.SQL
You can use variable substitution on any WHERE clause, text string, column name or table name.
The first type of substitution is the single ampersand "&".
SELECT TITLE FROM JD11.BOOK WHERE SECTION = &SEC_NUMBER
If this statement were typed into SQL *Plus or retrieved as a command file you would see a prompt appear asking you to specify the value of &SEC_NUMBER, this means you can vary the result set whilst using the same pre prepared SQL statement. Remember that date and string values must always be enclosed in single quotes, you can get around having to type them in by putting them into the SQL statement.
SELECT AUTHOR FROM JD11.BOOK WHERE TITLE = '&REQUIRED_TITLE'
You now don't have to remember the quotes as they are part of the prepared statement.
SELECT AUTHOR, &OTHER_FIELD FROM JD11.BOOK WHERE &CONDITION
The statement above will generate two prompts. The first prompt will ask you to specify a column name, this allows you to vary the fields you want the result set to display. The second prompt will ask you to specify the entire WHERE clause, this gives you the freedom to change the WHERE clause each time you run the statement.
The second type of substitution is the double ampersand "&&".
This type of substitution means that you only type a variable value in once.SELECT TITLE FROM JD11.BOOK WHERE SECTION = &&SEC_NUMBER
The first time we run this statement we will be asked for the value of &&SEC_NUMBER as before, but if we run the statement again (without closing the SQL *Plus session) it uses the value that you specified the first time and doesn't prompt you again.
To change a variable saved by a double ampersand command or define a new variable for future use you use the DEFINE command (can be abbreviated to DEF).
If we run this statement and define &&SEC_NUMBER as 10,
SELECT TITLE FROM JD11.BOOK WHERE SECTION = &&SEC_NUMBER
then we decide we really wanted section 9 for the rest of our work then we can use the DEFINE command to change the value of the variable (or we could have defined the variable at the session start to avoid all prompts).
DEF SEC_NUMBER = 9
Note that the "&&" is not required here.
If we wanted to remove the variable completely we can use the UNDEFINE command (can be abbreviated to UNDEF).
UNDEF SEC_NUMBER
SQL *Plus Command Reference
SQL *Plus is the command line console used in most Oracle environments to interact with the database, a few places run scripts on it which are used as applications but this is quite rare. Most installations use it for DBA and other management functions for which it is adequate (barely).
In general SQL *Plus provides a text environment command prompt at which you type in SQL strings, format the result set output, retrieve, save and execute command files and set up variable values.The most common SQL *Plus commands are given below.
Command | <><><><><><><> Description | <><><><><><><>
SPOOL filename | <><><><><><><> Write all console text from this point on to named text file. | <><><><><><><>
SPOOL OFF | <><><><><><><> Close any open spool file, stop spooling. | <><><><><><><>
DESCRIBE tablename | <><><><><><><> Describe the structure of the named table, abbreviate to DESC. | <><><><><><><>
HELP | <><><><><><><> Call system help (if installed) | <><><><><><><>
HOST command | <><><><><><><> Run command on local shell (UNIX or DOS) | <><><><><><><>
CONNECT userid/password | <><><><><><><> Connect to another schema. | <><><><><><><>
CONNECT userid/password@instname | <><><><><><><> Connect to another schema on another database instance. | <><><><><><><>
ED | <><><><><><><> Start the local text editor and load the last SQL command. When you save the text file the SQL will be placed back into SQL *Plus. | <><><><><><><>
SAVE commandfilename | <><><><><><><> Save the last SQL command to the named command file | <><><><><><><>
LOAD commandfilename | <><><><><><><> Load a command file but don't run it. | <><><><><><><>
START commandfilename | <><><><><><><> Load a command file and run it, also use @commandfilename. | <><><><><><><>
PROMPT text | <><><><><><><> Output a line of text to any user of the command file with the PROMPT in it. | <><><><><><><>
REMARK | <><><><><><><> Comment line in a command file. | <><><><><><><>
PAUSE | <><><><><><><> In command file, forces command file to halt execution until any key is pressed. | <><><><><><><>
SET | <><><><><><><> Used to set SQL *Plus environment variables. | <><><><><><><>
Basic PL/SQL Block Structure
PL/SQL is structured into blocks and can use conditional statements, loops and branches to control program flow. Variables can be scoped so that they are only visible within the block where they are defined.
PL/SQL blocks come in three types, these are anonymous procedure, named procedure and named function. All of these block types share most PL/SQL features so during this tutorial the features that apply to all block types will be grouped into single subjects.
An anonymous procedure is an unnamed procedure, it can't be called. It is placed where it is to be run, normally attached to a database trigger or application event.
A named procedure may be called, it may accept inbound parameters but won't explicitly return any value.
A named function may also be called, it may accept inbound parameters and will always return a value.
DECLARE
Definition of any variables or objects that are used within the declared block.BEGIN
Statements that make up the block.
EXCEPTION
All exception handlers.
END;
End of block marker.
Example :-
DECLARE
TEMP_COST NUMBER(10,2);
BEGIN
SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = 21;
IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21; END IF;
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'ISBN 21 NOT FOUND');
END;
As you can see there are several elements in the example that haven't been covered in the SQL tutorial, these elements are the PL/SQL extensions. They include :-
Variables and Constants | <><><><><><><> These objects are used to store and manipulate block level data. They can be CHAR, VARCHAR2, NUMBER, DATE or BOOLEAN data types. | <><><><><><><>
SQL support | <><><><><><><> All SQL statements are supported within PL/SQL blocks including transaction control statements. | <><><><><><><>
Composite Datatypes | <><><><><><><> Records allow groups of fields to be defined and manipulated in PL/SQL blocks. | <><><><><><><>
Flow Control | <><><><><><><> Ifs, Loops, GOTOs and labels provide conditional actions, tests, branching and iterative program control. | <><><><><><><>
Built In functions | <><><><><><><> Most SQL data functions are supported within PL/SQL blocks. | <><><><><><><>
Cursor handling | <><><><><><><> Cursors (a memory area holding a result set) can be explicitly defined and manipulated allowing the processing of multiple rows. A group of PL/SQL system attributes provide the ability to test a cursor's internal state. | <><><><><><><>
Exception handling | <><><><><><><> Blocks have the ability to trap and handle local error conditions (implicit exceptions). You may also self generate explicit exceptions that deal with logic and data errors. | <><><><><><><>
Code storage | <><><><><><><> Blocks may be stored within an Oracle database as procedures, functions, packages (a group of blocks) and triggers. | <><><><><><><>
The rules of block structure are :-
Every unit of PL/SQL must constitute a block. As a minimum there must be the delimiting words BEGIN and END around the executable statements.
SELECT statements within PL/SQL blocks are embedded SQL (an ANSI category). As such they must return one row only.
SELECT statements that return no rows or more than one row will generate an
error. If you want to deal with groups of rows you must place the returned data
into a cursor. The INTO clause is mandatory for SELECT statements within PL/SQL blocks (which are not within a cursor definition), you must store the returned values from a SELECT.
SELECT statements that return no rows or more than one row will generate an
error. If you want to deal with groups of rows you must place the returned data
into a cursor. The INTO clause is mandatory for SELECT statements within PL/SQL blocks (which are not within a cursor definition), you must store the returned values from a SELECT.
If PL/SQL variables or objects are defined for use in a block then you must also have a DECLARE section.
If you include an EXCEPTION section the statements within it are only processed if the condition to which they refer occurs. Block execution is terminated after an exception handling routine is executed.
PL/SQL blocks may be nested, nesting can occur wherever an executable statement could be placed (including the EXCEPTION section).
Scope of Block Objects
The scope of an object defines where it is visible, it is the area of the program logic that can legally refer to a given object.
A variable or object's (cursor, constant Etc.) scope is determined by the block that it is declared in. It is only available until the block it is defined in ends execution. Remember that block variables are defined under the DECLARE keyword.
For nested blocks an object defined in a parent block is available within all its child (nested blocks). The reverse is not true, objects defined in a child block are not visible to the parent.
If a nested block defines an object with the same name as an object in its parent block then only the local object is visible. Unlike Java there is no method provided to get at the parent object in this circumstance.
Declaring Variables and Constants
PL/SQL supports a variety of data types that you can use for declaring variables and constants. You can assign values to variables as you declare them and you can change the value of a variable through further assignments.
You must assign the value of a constant to it when you declare it, this value is fixed and cannot be changed at run time.
Example variable declarations are given below :DECLARE
V_NUM1 NUMBER NOT NULL := 10109;NUM8 NUMBER(3,1);
XYZ NUMBER(2,2) := 31.8;
ABC12 NUMBER(9,2) := XYZ * 131;
V_CHR1 CHAR(89);
V_CHR2 VARCHAR2(12) := "JANUARY";
TODAY DATE := SYSDATE;
TRUEFALSE BOOLEAN;
Example constant declarations are given below :
DECLARE
PI CONSTANT NUMBER(9,3) := 3.142;VAT CONSTANT NUMBER(4,2) := 17.5;
When you declare PL/SQL variables to hold column values you must ensure that the variable type is the same as the column type else you will get a run time error at execution. You can use the %TYPE attribute to base a variable upon the column definition as defined within the Oracle data dictionary. The attribute is prefixed with the schema, table and column name and used where the datatype is required in the DECLARE section.
PL/SQL determines the datatype and size of the variable when the block is
complied and so is always compatible with the column used to populate it. An
example follows :
PL/SQL determines the datatype and size of the variable when the block is
complied and so is always compatible with the column used to populate it. An
example follows :
DECLARE
V_NUM1 JD11.BOOK.COST%TYPE; PL/SQL Records
Use the %ROWTYPE attribute to declare a record based upon a collection of database columns from a table or view. The fields within the record take their names and datatypes from the columns of the table or view.
Declare the record in the DECLARE section along with any other required variables and constants. An example follows :
DECLARE
REC1 JD11.BOOK%ROWTYPE;
REC4 JD11.BOOK%ROWTYPE;
The above declaration sets the object REC1 to be a record object holding fields that match the columns in the BOOK table. It doesn't hold any values until it is populated.
Assign values into a PL/SQL record by naming the record after the INTO keyword of a SELECT statement. The INTO keyword defines the name specification for the storage area(s) of queried value(s).
BEGIN
SELECT * INTO REC1 FROM JD11.BOOK WHERE ISBN = 21;END;
You can assign all the record values from one record to another provided that the record definitions are the same.
BEGIN
REC4 := REC1;END;
Reference the field values within a PL/SQL record like this :-
BEGIN
REC4 := REC1;
IF REC4.COST > 0 THENREC4.SECTION_ID := 10; ELSE
REC4.SECTION_ID := 7; END IF;
END;
PL/SQL Assignments
You can assign values to variables anywhere within a PL/SQL block including during the creation of a variable. Some examples follow :-
NUM1 := NUM1 + NUM2 + (NUM3 * 3);
ANUM3 := AVG_COST * 7 + NVL(AVG_BUY_COST, 0);
SENT := 1;
STR1 := 'No rest until one = '|| TO_CHAR(SENT);
CHR1 := 'ABCDEFG';
FLAG1 := TRUE;
FLAG1 := FALSE;
MALE := UPPER(TITLE);
Cursors
Implicit cursors.Whenever a SQL statement is issued the Database server opens an area of memory in which the command is parsed and executed. This area is called a cursor. Microsoft tends to refer to cursors as datasets throughout much of their databse product documentation..
When the executable part of a PL/SQL block issues a SQL command, PL/SQL creates an implicit cursor which has the identifier SQL. PL/SQL manages this cursor for you.
PL/SQL provides some attributes which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements.
The SQL cursor attributes are :-
%ROWCOUNT | <><><><><><><> The number of rows processed by a SQL statement. | <><><><><><><>
%FOUND | <><><><><><><> TRUE if at least one row was processed. | <><><><><><><>
%NOTFOUND | <><><><><><><> TRUE if no rows were processed. | <><><><><><><>
%ISOPEN | <><><><><><><> TRUE if cursor is open or FALSE if cursor has not been opened or has been closed. Only used with explicit cursors. | <><><><><><><>
An example follows :-
DECLARE
ROW_DEL_NO NUMBER;
BEGIN
DELETE * FROM JD11.SECTION;
ROW_DEL_NO := SQL%ROWCOUNT;
END;
Explicit cursors.
SELECT statements that occur within PL/SQL blocks are known as embedded, they must return one row and may only return one row. To get around this you can define a SELECT statement as a cursor (an area of memory), run the query and then manipulate the returned rows within the cursor. Cursors are controlled via four command statements. They are:
DECLARE | <><><><><><><> Defines the name and structure of the cursor together with the SELECT statement that will populate the cursor with data. The query is validated but not executed. | <><><><><><><>
OPEN | <><><><><><><> Executes the query that populates the cursor with rows. | <><><><><><><>
FETCH | <><><><><><><> Loads the row addressed by the cursor pointer into variables and moves the cursor pointer on to the next row ready for the next fetch. | <><><><><><><>
CLOSE | <><><><><><><> Releases the data within the cursor and closes it. The cursor can be reopened to refresh its data. | <><><><><><><>
Cursors are defined within a DECLARE section of a PL/SQL block. An example follows :
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;The cursor is defined by the CURSOR keyword followed by the cursor identifier (MYCUR in this case) and then the SELECT statement used to populate it, the SELECT statement can be any legal query.
Cursors are opened with the OPEN statement, this populates the cursor with data.DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;BEGIN
OPEN MYCUR;
END;
To access the rows of data within the cursor we use the FETCH statement.
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN OPEN MYCUR;
FETCH MYCUR INTO THISISBN, THISCOST;
END;
The FETCH statement reads the column values for the current cursor row and puts them into the specified variables. The cursor pointer is updated to point at the next row. If the cursor has no more rows the variables will be set to null on the first FETCH attempt, subsequent FETCH attempts will raise an exception.
The CLOSE statement releases the cursor and any rows within it, you can open the cursor again to refresh the data in it.
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN
OPEN MYCUR; FETCH MYCUR INTO THISISBN, THISCOST;
CLOSE MYCUR;
END;
To process all the rows within a cursor we simply need to place the FETCH statement in a loop and check the cursor NOTFOUND attribute to see if we successfully fetched a row or not.
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN OPEN MYCUR; LOOP FETCH MYCUR INTO THISISBN, THISCOST;
EXIT WHEN MYCUR%NOTFOUND; END LOOP; CLOSE MYCUR;
END;
PL/SQL records may be based on a cursor. This is very convenient for processing rows from the active data set. An example follows :
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO PARTBOOK;
EXIT WHEN MYCUR%NOTFOUND;
IF PARTBOOK.ISBN = 21 THEN PARTBOOK.COST = 19.10;
END IF;
END LOOP;
CLOSE MYCUR;
END;
You can use the WHERE CURRENT OF clause to execute DML commands against the current row of a cursor, this makes it easier to update rows. An example is below :
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO PARTBOOK;
EXIT WHEN MYCUR%NOTFOUND;
IF PARTBOOK.ISBN = 21 THEN
DELETE FROM JD11.BOOK WHERE CURRENT OF MYCUR;
END IF;
END LOOP;
CLOSE MYCUR;
END;
Note that I didn’t need to explicitly specify the row that I want deleted, PL/SQL supplies the required row identifier from the current record in the cursor ensuring that only the correct row is deleted.
It’s possible to vary the returned result set by using parameters, parameters allow you to specify the query selection criteria when you open the cursor.DECLARE
CURSOR MYCUR (PARAM1 NUMBER) IS SELECT ISBN, COST FROM JD11.BOOK WHERE ISBN = PARAM1;PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR(21);
FETCH MYCUR INTO PARTBOOK;
CLOSE MYCUR;OPEN MYCUR(101);
FETCH MYCUR INTO PARTBOOK;
CLOSE MYCUR;
END;
Exception (error) Handling
Exceptions are identifiers in PL/SQL that are raised during the execution of a block to terminate its action. A block is always terminated when PL/SQL raises an exception but you can define your own error handler to capture exceptions and perform some final actions before quitting the block. If PL/SQL handles the exception within the block then the exception will not propagate out to an enclosing block or environment.
There are two classes of exceptions, these are :
Predefined - Oracle predefined errors which are associated with specific error codes.
User-defined - Declared by the user and raised when specifically requested within a block. You may associate a user-defined exception with an error code if you wish.
If an error occurs within a block PL/SQL passes control to the EXCEPTION section of the block. If no EXCEPTION section exists within the block or the EXCEPTION section doesn't handle the error
that's occurred then the block is terminated with an unhandled exception. Exceptions propagate up through nested blocks until an exception handler is found that can handle the error. If no exception handler is found in any block the error is passed out to the host environment. Exceptions occur when either an Oracle error occurs (this automatically raises an exception) or you explicitly raise an error using the RAISE statement.
that's occurred then the block is terminated with an unhandled exception. Exceptions propagate up through nested blocks until an exception handler is found that can handle the error. If no exception handler is found in any block the error is passed out to the host environment. Exceptions occur when either an Oracle error occurs (this automatically raises an exception) or you explicitly raise an error using the RAISE statement.
The two most common errors originating from a SELECT statement occur when it returns no rows or more than one row (remember that this is not allowed). The example below deals with these two onditions.
DECLARE
TEMP_COST NUMBER(10,2);TEMP_ISBN NUMBER(10);
BEGIN SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
> 21;
IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN > 21; ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE
ISBN > 21; END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'NOT FOUND');
WHEN TOO_MANY_ROWS THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'TOO MANY');
END;
The block above will generate an error because there are more than one record with an ISBN greater than 21. The exception raised from this will be passed to the EXCEPTION section where each handled action will be checked. The statements within the TOO_MANY_ROWS action will then be executed before the block is terminated.
If some other error occurred this EXCEPTION section would not handle it because is isn't defined as a checkable action. To cover all possible errors you can specify a catch all action named OTHERS.
DECLARE
TEMP_COST NUMBER(10,2);TEMP_ISBN NUMBER(10);
BEGIN SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
> 21; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN > 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN > 21; END IF; COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE,MESSAGE) VALUES(99,‘NOT FOUND’); WHEN TOO_MANY_ROWS THEN INSERT INTO JD11.ERRORS (CODE,MESSAGE) VALUES(99,‘TOO MANY’);
WHEN OTHERS THEN
INSERT INTO JD11.ERRORS(CODE, MESSAGE) VALUES(99, ‘SOME ERROR OCCURRED’);
END;
This block will trap all errors. If the exception isn't no rows returned or too many rows returned then the OTHERS action will perform the error handling.
PL/SQL provides two special functions for use within an EXCEPTION section, they are SQLCODE and SQLERRM. SQLCODE is the Oracle error code of the exception, SQLERRM is the Oracle error message of the exception. You can use these functions to detect what error has occurred (very useful in an OTHERS action). SQLCODE and SQLERRM should be assigned to variables before you attempt to use them.
DECLARE
TEMP_COST NUMBER(10,2);TEMP_ISBN NUMBER(10);
ERR_MSG VARCHAR2(100);
ERR_CDE NUMBER;
BEGIN SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
> 21; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN > 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN > 21; END IF; COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘NOT FOUND’); WHEN TOO_MANY_ROWS THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘TOO MANY’); WHEN OTHERS THEN
ERR_MSG := SUBSTR(SQLERRM,1,100);
ERR_CDE := SQLCODE;
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(ERR_CDE, ERR_MSG);
END;
If you explicitly need to raise an error you can do this by using the RAISE statement. For example, if you wanted to raise an error if a SELECT statement found a row (which it shouldn't have found) you would only be able to do this by raising your own error.
DECLARE
TEMP_COST NUMBER(10,2);
TEMP_ISBN NUMBER(10);
ERR_MSG VARCHAR2(100);
ERR_CDE NUMBER;
BEGIN
SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
< 0;
RAISE THIS_IS_WRONG
EXCEPTION
WHEN THIS_IS_WRONG THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘OOPS’);
END;
The example above shouldn't find any rows with an ISBN less than 0. If it did we raise our own exception to force PL/SQL to pass control to our own action within the EXCEPTION section.
ProcessingControl Structure
PL/SQL provides several possible control structures for controlling logic flow within a block, these are not dissimilar to structures found in most procedural programming languages. The structures are :IF -
Provides selective control of actions based upon the outcome of a condition test.
Provides selective control of actions based upon the outcome of a condition test.
IF A = 1 THEN D := 1 + E; E1 := H * 1.176;
ELSIF A = 1 THEN E := 1 + D; E1 := H * 1.1876; E2 := 3;
ELSIF A = 3 THEN B := 1.123;
ELSE
B := 2.01;
END IF;
You can have as many ELSIF statements as you like. The ELSE section captures any untrapped conditions. It is not mandatory to have ELSIF or ELSE sections.
Loops
- Provides repetition of statement groups without imposing an overall condition.
- Provides repetition of statement groups without imposing an overall condition.
EXIT
- Provides a breakout function for a loop.
- Provides a breakout function for a loop.
LOOP I := I + 1; IF I = 10 THEN
EXIT; END IF;
END LOOP;
LOOP
I := I + 1;
EXIT WHEN I = 10;
END LOOP;
Note that if you haven't got any exit condition (and an EXIT statement) this will loop forever. The basic loop consists of a group of statements between a LOOP and END LOOP. The EXIT statement terminates the most current loop (unless you explicitly name the loop you want terminated). Note that the two loops are functionally identical, they demonstrate two alternate ways of using the EXIT statement.
FOR Loops -
Provides iterative statement execution based upon a count.
Provides iterative statement execution based upon a count.
FOR CNT IN 12 .. 150 LOOP A := A + (CNT * 10); EXIT WHEN A > 12900;
END LOOP;FOR CNT IN REVERSE 18 .. 121 LOOP A := A + (CNT * 10);
END LOOP;
FOR loops have the same structure as basic loops but have an additional control statement tagged on the front. This additional statement controls the number of executed iterations of the loop (provided that you don't use the EXIT statement to breakout early). By default the loop increments the control variable (which must be declared in the usual way) starting at the low value and going up to the high, use the REVERSE clause to make the loop decrement the control variable from highest to lowest value instead. If you want to step in increments other than one you'll have to mess about with the counter variable within the loop.
WHILE Loops -
Provides iterative statement execution based upon a condition test.
Provides iterative statement execution based upon a condition test.
WHILE B > 0 AND JAD11 <> ‘Lift’ OR JAD11 <> ‘Platform’ LOOP
FOR CNT IN REVERSE 18 .. 121 LOOP A := A + (CNT * 10); IF A > 12900 THEN JAD11 = ‘Platform’; END IF; END LOOP;END LOOP;
The condition is evaluated at the start of the loop, if it is false at the start of the loop then no iterations will be performed. Note that if the condition(s) of the WHILE loop don't change within the loop then the loop will never end. You can include one or many EXIT statements if early breakouts are required.
It is possible to use labels with control structures and elsewhere. Labels are defined by enclosing them between "<<" and ">>" symbols. You can branch to them using GOTO statements (but this is bad programming practice) or more usefully you can use them to break out of nested loops and nested blocks.
<<OUTERBLK>>
DECLARETEMP_COST NUMBER(10,2);
TEMP_ISBN NUMBER(10);
BEGIN
SELECT ISBN, COST INTO TEMP_ISBN, TEMP_COST FROM JD11.BOOK WHERE ISBN
> 21;
IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN > 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN > 21; END IF;
<<INNERBLK1>> DECLARE I NUMBER(4); AB CHAR(10); CNT NUMBER(3); A NUMBER(6); BEGIN
<<OUTERLOOP>> LOOP I := I + 1;
IF I = 5 THEN
GOTO DOCOMMITTAG; END IF; IF I = 10 THEN
EXIT OUTERLOOP;
END IF;
<<INNERLOOP>> FOR CNT IN 12 .. 150 LOOP
A := A + (CNT * 10);
EXIT OUTERLOOP
WHEN A > 12900;
END LOOP INNERLOOP;
END LOOP OUTERLOOP;
<<DOCOMMITTAG>> COMMIT; END;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘NOT FOUND’); WHEN TOO_MANY_ROWS THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘TOO MANY’);
END;
Procedures & Functions
Benefits of using procedures and functions include :-
- Improved data security and integrity.
· Control indirect access to objects from non privileged users with security privileges.
- Improved performance.
· Avoid reparsing for multiple users by exploiting shared SQL.
· Avoid PL/SQL parsing at run time by parsing at compile time.
· Reduce the number of calls to the database and decrease network traffic by bundling commands. - Improved maintenance.
· Modify routines online without interfering with other users.
· Modify one routine to affect multiple applications. · Modify one routine to eliminate duplicate testing.
Procedures are simply a named PL/SQL block, they are created by a particular schema and like other database objects are then owned by that schema. Rights to the procedure are granted / revoked just like any other object except that you grant / revoke the EXECUTE right and not SELECT, UPDATE etc. An example of an unnamed block is given below :
DECLARE
TEMP_COST NUMBER(10,2);BEGIN SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = 21; IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21; END IF;
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN 21 NOT FOUND’);
END;
This block could be saved to a command file and executed from there but it would be parsed each and every time it was called. To save this block to the database we need to name it.
CREATE OR REPLACE PROCEDURE MYPROC1
(TEMP_COST NUMBER(10,2))IS BEGIN SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = 21; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21; END IF; COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN 21 NOT FOUND’);
END MYPROC1;
The procedure (named MYPROC1) is defined in the first line. CREATE OR REPLACE asks Oracle to create a new procedure or if a procedure with this name already exists in this schema to replace it (you may leave out the OR REPLACE if you don't want this to happen).
The DECLARE section is now implicitly the section between the procedure definition and the IS BEGIN statement - it is not explicitly named but is enclosed within brackets. The END statement now ends a named block rather than an unnamed block. Nothing else is required for a procedure to run. To execute the stored procedure simply call it by name.
Note that JD11 is the schema name, if you running the procedure from within JD11 or you have been granted the required privilege on MYPROC1 you may leave the schema name off. If you invoking the procedure in SQL *PLUS you should prefix the procedure name with the EXECUTE command.
The last thing to cover here is how to get values into and out of the procedure, we can do this by defining variables in the implicit declare section as IN, OUT or IN OUT.
CREATE OR REPLACE PROCEDURE MYPROC1
(REQISBN IN NUMBER,
MYVAR1 IN OUT CHAR,
TCOST OUT NUMBER)
TEMP_COST NUMBER(10,2))
IS BEGIN SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN =
REQISBN; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = REQISBN; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = REQISBN; END IF;
TCOST := TEMP_COST; COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN NOT FOUND’);
END MYPROC1;
EXECUTE JD11.MYPROC1 (21,:PCOST) - will execute the procedure in SQL *PLUS
(PCOST is the name of a SQL *PLUS bind variable). 21 will be passed into the
procedure, the value of TCOST will be passed out of the procedure and stored in
:PCOST.
An IN OUT variable can pass values both in and out of a procedure.
Remember that a procedure can read in a list of values but won't directly return any value (it may indirectly return values). A function can also read in a list of values but will explicitly return a single result which is normally assigned to a variable or used in a program control structure condition test (IF statements etc).The function below demonstrates the syntax of a PL/SQL function block, note that as with a procedure the OR REPLACE clause can be left out if you don't want the replacement of an existing function with the same name. Function definitions vary from procedure definitions in that you must explicitly name a variable to return and you must return a value in the variable via the RETURN statement.
CREATE OR REPLACE FUNCTION MYFUNC1
RETURN NUMBER
IS RCOST NUMBER(10,2); BEGIN SELECT COST INTO RCOST FROM JD11.BOOK WHERE ISBN = 21;
RETURN (RCOST);
END MYFUNC1;
EXECUTE :PCOST := JD11.MYFUNC1 - will execute the procedure in SQL *PLUS
(PCOST is the name of a SQL *PLUS bind variable). The purchase value of book 21 will be passed out of the function and stored in :PCOST.
Packages
A package is a set of related functions and / or routines. Packages are used to group together PL/SQL code blocks which make up a common application or are attached to a single business function. Packages consist of a specification and a body. The package specification lists the public interfaces to the blocks within the package body. The package body contains the public and private PL/SQL blocks which make up the application, private blocks are not defined in the package specification and cannot be called by any routine other than one defined within the package body. The benefits of packages are that they improve the organisation of procedure and function blocks, allow you to update the blocks that make up the package body without affecting the specification (which is the object that users have rights to) and allow you to grant execute rights once instead of for each and every block.
To create a package specification we use a variation on the CREATE command, all we need put in the specification is each PL/SQL block header that will be public within the package. An example follows :
CREATE OR REPLACE PACKAGE MYPACK1
AS
PROCEDURE MYPROC1 (REQISBN IN NUMBER, MYVAR1 IN OUT CHAR,TCOST OUT NUMBER);
FUNCTION MYFUNC1;
END MYPACK1;
To create a package body we now specify each PL/SQL block that makes up the package, note that we are not creating these blocks separately (no CREATE OR REPLACE is required for the procedure and function definitions). An example follows :
CREATE OR REPLACE PACKAGE BODY MYPACK1 AS
PROCEDURE MYPROC1
(REQISBN IN NUMBER,MYVAR1 IN OUT CHAR, TCOST OUT NUMBER)
TEMP_COST NUMBER(10,2))
IS BEGIN
SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = REQISBN;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = REQISBN;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = REQISBN;
END IF;
TCOST := TEMP_COST;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN NOT FOUND’);
END MYPROC1;
FUNCTION MYFUNC1
RETURN NUMBER
IS RCOST NUMBER(10,2);
BEGIN
SELECT COST INTO RCOST FROM JD11.BOOK WHERE ISBN = 21;
RETURN (RCOST);
END MYFUNC1;
END MYPACK1;
You can execute a public package block like this :
EXECUTE :PCOST := JD11.MYPACK1.MYFUNC1 - where JD11 is the schema name that owns the package. You can use DROP PACKAGE and DROP PACKAGE BODY to remove the package objects from the database.
EXECUTE :PCOST := JD11.MYPACK1.MYFUNC1 - where JD11 is the schema name that owns the package. You can use DROP PACKAGE and DROP PACKAGE BODY to remove the package objects from the database.
Database Triggers
A trigger is PL/SQL code block attached and executed by an event which occurs to a database table. Triggers are implicitly invoked by DML commands. Triggers are stored as text and compiled at execute time, because of this it is wise not to include much code in them but to call out to previously stored procedures or packages as this will greatly improve performance (this only applies to pre v8i - thanks to Chenggan Duan for pointing that out). You may not use COMMIT, ROLLBACK and SAVEPOINT statements within trigger blocks. Remember that triggers may be executed thousands of times for a large update - they can seriously affect SQL execution performance.
Triggers may be called BEFORE or AFTER the following events :
INSERT, UPDATE and DELETE.
Triggers may be STATEMENT or ROW types. STATEMENT triggers fire BEFORE or AFTER the execution of the statement that caused the trigger to fire. ROW triggers fire BEFORE or AFTER any affected row is processed.
An example of a statement trigger follows :
CREATE OR REPLACE TRIGGER MYTRIG1
BEFORE DELETE OR INSERT OR UPDATE ON JD11.BOOK
BEGIN
IF (TO_CHAR(SYSDATE,’dy’) IN (‘sat’,’sun’)) OR (TO_CHAR(SYSDATE,’hh24:mi’) NOT BETWEEN ’08:30’ AND ’18:30’)
THEN RAISE_APPLICATION_ERROR(-20500,’Table is secured’);
END IF;
END;
After the CREATE OR REPLACE statement is the object identifier (TRIGGER) and the object name (MYTRIG1). This trigger specifies that before any data change event on the BOOK table this PL/SQL code block will be compiled and executed. The user will not be allowed to update the table outside of normal working hours.
An example of a row trigger follows :-
CREATE OR REPLACE TRIGGER MYTRIG2
AFTER DELETE OR INSERT OR UPDATE ON JD11.BOOKFOR EACH ROW
BEGIN
IF DELETING THEN
INSERT INTO JD11.XBOOK (PREVISBN, TITLE, DELDATE) VALUES (:OLD.ISBN, :OLD.TITLE, SYSDATE);
ELSIF INSERTING THEN
INSERT
INTO JD11.NBOOK (ISBN, TITLE, ADDDATE) VALUES (:NEW.ISBN,
:NEW.TITLE, SYSDATE);
ELSIF UPDATING (‘ISBN) THEN
INSERT INTO JD11.CBOOK (OLDISBN, NEWISBN, TITLE, UP_DATE) VALUES (:OLD.ISBN :NEW.ISBN, :NEW.TITLE, SYSDATE);
ELSE /* UPDATE TO ANYTHING ELSE THAN ISBN */
INSERT INTO JD11.UBOOK (ISBN, TITLE, UP_DATE) VALUES(:OLD.ISBN :NEW.TITLE, SYSDATE);
END IF
END;
In this case we have specified that the trigger will be executed after any data change event on any affected row. Within the PL/SQL block body we can check which update action is being performed for the currently affected row and take whatever action we feel is appropriate. Note that we can specify the old and new values of updated rows by prefixing column names with the :OLD and :NEW qualifiers.
No comments:
Post a Comment