---------------------- PL/SQL in a hurry.... ---------------------- Mainly a utility-language used for batch by DBA's. Can to some extend be used for end-user applications. Provides server-side procedural extension to SQL. Specially useful for named (stored) procedures, functions and triggers which can be used from other end-user applications made in other languages. * Blocks * ********** Code is organized in blocks, either anonymous or named. Anonymous would normally be stored in files (as scripts) or is run only one time. Named blocks becomes part of the database as procedures, functions, packages or triggers. A block can consist of 3 sections: - a declaration section (optional) - an executable section - an exception-handling section (optional) [see syntax p. 229] * Comments * ************ Comments ("-- single line" or "/* multi line ... */") can be used. * Data types * ************** Main data types are: Scalar (single-value) - CHAR, VARCHAR2 for characters - BINARY_INTEGER, INTEGER, INT etc. for integers - NUMBER, DEC, NUMERIC, FLOAT, REAL etc. for floating point - BOOLEAN true/false - DATE date and time Composite - records (a collection of variables/fields) - tables (like a list) - varrays (like simple array) Note that types are not identical to Oracles SQL types. * Variables * ************* [see variable declaration p. 234] A powerful type declaration for variables is "anchored declaration" that guarantees that a type is exactly the same as the anchors. Example: DECLARE v_EmpFirst employee.Fname%TYPE; v_EmpLast employee.Lname%TYPE; Assignment operator is ":=" (like in Pascal). Example: v_EmpFirst := 'Allan'; * I/O * ******* To read input values from the keyboard "substitution variables" are used. To avoid echo of the substitution use SET VERIFY OFF [see pp. 238-239] To print out the package DBMS_OUTPUT provides procedures PUT_LINE and PUT To switch on the output feature use SET SERVEROUTPUT ON [see p. 239] Example: SET VERIFY OFF SET SERVEROUTPUT ON DECLARE v_EmpId employee.EmployeeId%TYPE; BEGIN v_EmpId := &EmpId; DBMS_OUTPUT.PUT('Entered value : '); DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_EmpId)); DBMS_OUTPUT.PUT_LINE(v_EmpId); END; / The "&" in front of the variable-name "EmpId" creates a prompt: "Enter value for EmpId:" After you enter a value and hit the enter-key the value is assigned into v_EmpId. Then a label and the value is printed on the same line. The use of TO_CHAR() is for safety if value is not a char-type. Next line the value is printed as it is. * Arithmetic operators * ************************ The five operators are: + addition - subtraction * multiplication / division ** exponentiation * Selection * ************* [see figure 11-1 p. 245 for relational operators] [see figure 11-2 p. 246 for logical operators] Mainly one of the "IF" selections are used: IF .. THEN .. END IF IF .. THEN .. ELSE .. END IF IF .. THEN .. ELSEIF .. END IF (Note that "END IF" must be two words) The first two are like Java. The third is used if selection is to be made among many alternatives. In that situation CASE .. END CASE can be used [see figure 11-11 p. 252 for examples of the CASE syntax] * Loops * ********* There is 3 kinds of loop: - Basic loop An infinite loop stopped by an (conditional) EXIT statement. [see figure 11-14 p. 256 for an example] - WHILE loop Very much like a while-loop in Java (slightly different syntax) [see figure 11-15 p. 257 for an example] - FOR loop Simplest form of loop. Initialisation, test and change of the loop control variable is done automatically. [see figure 11-17 p. 258 for an example] * SQL in PL/SQL * ***************** Basically all DML (insert, update, delete), transaction control (rollback, commit, savepoint) and select statements can be used. Retrieved data can be assigned into variables or cursors for further processing. DDL (create, alter, drop) and DCL (grant, revoke) is not supported. * SELECT in PL/SQL * ******************** The main difference between SELECT in SQL and in PL/SQL is that the latter stores the result into variables. [see p. 260 for general syntax] [see figure 11-18 p. 261 for a simple example] * DML and transaction control * ******************************* Same syntax as in SQL. [see figure 11-20 p. 262 for an INSERT example] [see figure 11-21 p. 263 for a DELETE example] [see figure 11-22 p. 263 for an UPDATE example] [see p. 264 for explanation of transaction control] * Cursor * ********** A cursor is a memory-location (work area) holding the statement and the result from executing the statement. A cursor can be static (always same content) or dynamic (can change value and statement). After a cursor is declared it can be opened, rows can be fetched and the cursor can be closed. [see p. 269 for syntax of cursor declaration] [see figures 12-1 and 12-2 for examples] As a cursor is declared using SELECT it is linked to at least one table. When a cursor is opened the statement is executed and the result from this is stored as a virtual table (or active set). If the underlying data is changed it has no effect on the cursors content - it is as a snapshot taken when the cursor was opened. When opened the cursor points to the first row in the virtual table. The row pointed to can be retrieved (fetched) and the data put into variables (or record). At each fetch the pointer moves to the next row in the virtual table. [see p. 271 for syntax of FETCH] * Cursor attributes * ********************* Four attributes tell about a cursors state: %ISOPEN returns true if the cursor is open %FOUND returns true if the last fetch returned a row %NOTFOUND opposite of %FOUND %ROWCOUNT returns total number of rows returned (fetched) * Cursor FOR loop * ******************* Cursor is opened implicitly, each iteration fetches a row into a record. Cursor is automatically closed when loop ends. [see p. 275 for syntax] [see figure 12-4 p. 275 for example of cursor FOR loop] The FOR loop can use a subquery an eliminate declaration of explicit cursor. * Cursor with parameters * ************************** If declared with parameters a cursor can be opened many times with different values passed to it. [see figure 12-6 p. 278 for example] * Cursor variables * ******************** A cursor variable is a reference to a cursor. It can be assigned different cursors. First the cursor type is declared, then a cursor variable using the type is declared. [see pp. 279-280 for an example] * Exceptions * ************** Works rather similar to the Java exceptions. Exceptions are raised (term "thrown" used in Java) when an error occurs. Control of the block shifts to the exception handler section If it doesn't exist control propagates to the outer block until an exception handler section is found or the script stops in error. An exception handler section must hold a match for the exception in question (name is used for match). If no matching handler is present the error message is displayed. The "WHEN OTHERS" is used to trap any exception. Different types of exceptions exist: - predefined Oracle server exceptions - non-predefined Oracle server exceptions - user-defined exceptions [see figure 12-12 p. 287 for an example of user-defined exceptions] [see figures 12-14 to 12-17 for various examples of PL/SQL code] * Records * *********** The structure of a record is like a row in a database table. It consists of components (scalar, record or PL/SQL table) known as fields that each has their own value. A record is based on a cursor, a tables row or a user-defined type. Example using a cursor: -- cursor declaration: CURSOR EmpNames_Cur IS SELECT Fname, Lname FROM employee; -- record based upon cursor: EmpName_rec EmpNames_Cur%ROWTYPE; Fields in EmpName_rec takes the names and types from the attributes (Fname and Lname). Example using a SQL table: Employee_rec Employee%ROWTYPE Fields in Employee_rec takes the names and types from the attributes of the Employee table. [see p. 297 for an example using a user-defined record] * Working with records * ************************ Fields are referenced like: recordname.fieldname Records are only known in the block where they are declared. Values can be assigned to the record using SELECT or FETCH. If a record is assigned NULL all the fields will be set to NULL. [see figure 13-1 p. 299 for an example of working with records] Records can be nested. * PL/SQL tables and varrays * ***************************** A PL/SQL table is very much like a simple array in Java except that it has dynamic size. First a table type has to be declared, then a table is declared using the table type. It is mandatory to add an INDEX BY BINARY_INTEGER. The index is used when referencing the elements in the table. [see figure 13-3 p. 303 for an example] [see figure 13-4 for PL/SQL built-in table methods] The data type used for a table type can be a record (in which case the PL/SQL table is rather similar to a SQL table). A varray is a variable-sized array. Declaration is done same way as a PL/SQL table. After declaration the varray is NULL and must be initialised before use. Before adding an element to the varray the EXTEND method must be used. [see figure 13-6 for an example] * Named blocks * **************** Named blocks can be procedures, functions and triggers. Purpose is to create modules that can be used (activated) from other blocks or external applications accessing the database. * Procedures * ************** [see p. 314 for syntax] Procedures are much like Java methods. Declaration includes a name, a list of parameters (optional) and an executable section (with an optional exception handling section). [see figure 14-3 p. 317 for an example] The parameter list is somewhat different from Java. A parameter can be used for passing a value (literal, variable or expression) to be used (read) by the procedures code. Such parameters are declared as IN. Parameters can be used to hold a value given from the procedure to be used by the calling statement (the actual parameter must thus be a variable). Such parameters are declared as OUT. Parameters can be used to pass a value to the procedure and then get a value from the procedure (actual parameter must be a variable). Such parameters are declared IN OUT. [see figure 14-4 p. 318 for an example using parameters] [see figure 14-5 p. 318 for an example of calling a procedure] * Functions * ************* Functions are very much like procedures except that the activation of a function results in a value returned by the function to the calling block. Thus OUT parameters are rare and considered bad practice! The main difference in the declaration is that the return type must be stated and the executable section must hold at least one RETURN statement. Like in Java you can have more than one RETURN statements (i.e. in a selection statement) - but when a RETURN statement is executed the function ends (like a method returning a value in Java). [see p. 319 for syntax] [see figure 14-6 p. 321 for an example of declaring a function] [see figure 14-7 p. 321 for an example of calling a function] Functions can be called from SQL. In Oracle you use the dual system table. Example: SELECT get_deptname(10) FROM dual; * Packages * ************ A package is a collection of PL/SQL objects (cursors, variables, constants, exceptions, type declarations, procedures, functions). Packages enhance modularity. DBMS_OUTPUT is an example of an built-in package. [see p. 324 for syntax] [see figures 14-10 to 14-13 pp.325-329 for examples] * Triggers * ************ A trigger is like a procedure except that you can't activate it explicitly. A trigger is executed (or "fired") by a DML event (INSERT, UPDATE or DELETE). Triggers are very useful for several operations: generating values for attributes on inserts and updates, checking validity of values or dependencies among values, store informations about the access and management of tables (keeping historical information), maintaining security and much more. [see p 328 for syntax] * BEFORE and AFTER triggers * ***************************** Triggers can be executed BEFORE or AFTER the DML-event. [see figure 14-14 p. 330 for a BEFORE trigger] [see figure 14-16 p. 332 for an AFTER trigger] As a trigger can be fired from more than one event (the one in figure 14-16 fires on DELETE as well as on UPDATE) three boolean functions, INSERTING, DELETING and UPDATING can be used to determine which event actually fired the trigger. *:NEW and :OLD * **************** Within a trigger body of a row trigger, code and SQL statements have access to the old and new column values of the current row affected by the triggering statement using two pseudo-records: :NEW and :OLD. Depending on the type of triggering statement, certain correlation names might not have any meaning: - A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT operation, the old values are null. - A trigger fired by an UPDATE statement has access to both old and new column values for both BEFORE and AFTER row triggers. - A trigger fired by a DELETE statement has meaningful access to old (:OLD) column values only. Because the row no longer exists after the row is deleted, the new (:NEW) values are NULL and cannot be modified. * FOR EACH ROW * **************** When using FOR EACH ROW the trigger will be fired multiple times if the SQL-statement affects multiple rows. Without FOR EACH ROW the trigger is fired only once for the statement. * INSTEAD OF triggers * *********************** BEFORE and AFTER triggers are based on tables. A special type of triggers, INSTEAD OF, is based on views. The trigger substitutes the triggering statement. In this way it may be possible to perform manipulation otherwise not valid on a view (remember that views build on joins have restrictions in modifications of the tables involved).