DatabaseServer architecture SQL is a data management language

Database/Server architecture SQL is a data management language focused on managing sets/bags of rows. SQL is NOT a general-purpose programming language (aka 3 GL language). To combine the advantages of 3 GL and SQL, SQL is interfaced with other languages in 2 ways: Case 1) Individual (1 user) runs stand-alone (non-networked) app combining 3 GL language(s) + SQL Case 2) Networked (many users) layered, aka “tiered” services: One form is “ 3 -tier” (diagram below). SQL and each 3 GL can inter-operate and interact (i. e. , run concurrently) in several standardized ways, for either of the Cases above. This inter-operation is common in internet and web apps. -----------------------More on Case 2) - In the diagram below, a web request “Req” arrives at the Front end, Req is passed to the 3 GL tier for specialized computations, and assuming database processing needed, Req is passed to SQL Req Look only at : 3 GL < --- > SQL tier in CSC 204 - it applies to both Case 1) AND Case 2) above Results/response Internet facing Front end Form/field processing One or more 3 GL apps that do special computations not suited for SQL PL/SQL, Java, C#, C++, etc. (SQL*Plus on DBMS server OR other 3 GL on app server) DBMS server SQL 1

First way that a 3 GL and SQL can inter operate - 1 of 2 A summary of how 3 GL and SQL pass/share info 1 ) Create a 3 GL variable called a bind / host variable. Examples from 3 different host languages PL/SQL as “host language”: : SQL> set echo on SQL> set serveroutput on SQL> declare 2 bsz number; -- Value undefined if not initialized 3 v_expr number; 4 begin 5 bsz : = 4000; 6 dbms_output. put_line('bsz is: ' || bsz); < -- PL/SQL variables are automatically bind variables 7 unlike bind variables in other 3 GL languages 8 /* Demo how SQL can use bsz */ 9 select vsize(rowid) + bsz into v_expr from dual; 10 dbms_output. put_line('v_expr is: ' || v_expr); 11 end; 12 / bsz is: 4000 v_expr is: 4010 PL/SQL procedure successfully completed. SQL> show errors No errors. 2

First way that a 3 GL and SQL can inter operate – 2 of 2 SQL*Plus command language as “host language”: : Script results for SAME EXAMPLE as in PL/SQL on previous slide SQL> /* declare and initialize a variable in SQL*Plus */ SQL> variable bsz number; SQL> exec : bsz : = 4000; < -- Review: assigning a value (: = is assignment operator) PL/SQL procedure successfully completed. < - exec does a callout to PL/SQL SQL> /* Demo how SQL can use bsz */ SQL> select vsize(rowid) + : bsz from dual; VSIZE(ROWID)+: BSZ --------4010 Languages not within the native Oracle environment, such as PL/SQL must name bind variables in queries with syntax Python 3 language as “host language” : bind. Variable. Name Script results for SAME EXAMPLE as in PL/SQL on previous slide dsn_tns = cx_Oracle. makedsn('cscoracle. ecs. csus. edu', '1521', service_name='cscoracle') conn = cx_Oracle. connect(user='cs 204120', password=‘xxxxxxxx', dsn=dsn_tns) cur = conn. cursor() bsz = 4000 # Initialize bind variable Typical syntax for making cur. execute("select : bsz + vsize(rowid) from dual", {"bsz": bsz}) a connection to an Oracle for x in cur: session from a non-native print("Expression value, returned as a Python tuple is ", x) Oracle environment. Expression value, returned as a Python tuple is (4010, ) We will not spend time on such language-dependent issues. Notes 1) The “: ” name convention is not followed by many languages (Java uses “? ” < -- > 1/2/3… associations; you must read a language’s documentation to know 3 2) Setup and query is a 2 -liner in Python (and other dynamic languages) – no variable declarations

Other ways that a 3 GL and SQL can inter operate 2) Pass/share a table or view row between them via a SQL cursor (covered in module P 4) We soon see that EVERY SQL statement has a cursor associated with it 3 GL < ===== SQL 3) A 3 GL procedure / function named X stored in the DB as a genuine DB object (thus, can be managed by SQL); X executes app logic and DB accesses. Abbreviate procedure and function to proc and fcn for the rest of this document. Note – in PL/SQL a proc or fcn is, coding-wise, simply a named anonymous block with parameters: Header < == Name and parameters, if any begin Code Body end; Relevant DD views: user_procedures user_triggers user_objects (and of course the 3 all_xxx views) 4) 3 GL Statement Level Interface where static or dynamic SQL statements can be passed from 3 GL to SQL for execution Some language mis-matches a) b) Unlike most languages, you cannot define a constant in SQL The SQL null value often does NOT BEHAVE THE SAME WAY in many 3 GLs as it does in SQL The SQL null has no direct equivalence in 3 GL languages: Java’s null, Python’s None, etc. are a mis-match with SQL null. In SQL, null can never be compared to another object, even itself. And in a typical 3 GL language, e. g. , in Java, null==null is true, and in Python, None == None is True Non-equivalent implementations of 1) – 4) above SQL standard’s specification and description of the above communication methods 1) – 4) is incomplete. One reason was to give RDBMS vendors flexibility when implementing capabilities 1) – 4). Unfortunately, this reduces standardization among RDBMSs A small number of RDBMSs (including Oracle & Postgres) extended SQL standard to quite complete 4 functional implementations of 1) - 4), but, again, syntax details differ from the SQL standard among 3 GLs.

PL/SQL overview – a native 3 GL interface to Oracle RDBMS Given the many choices of 3 GL languages that have been interfaced with SQL, and given the un-even programming languages background of CSC 204 students, in CSC 204, use Oracle built-in language PL/SQL (3 GL features) as our host language for SQL This module is a basic intro. to PL/SQL, and is the most direct way to cover the most important aspects of 3 GL < -- > SQL: bind variables, cursors, and stored procedures PL/SQL : - Is an Oracle-developed 3 GL used at the application service tier level - Has many OO properties (in fact, “under the hood” PL/SQL implements an “object-relational” model, including object views < == use in future homework) - Is strongly-coupled to Oracle SQL – Example: all Oracle data types inherit into PL/SQL - Has been in use long before Java/Python/Ruby/ … were in common use - Is more “portable” across platforms (eliminates feature differences across OSs) ‘Programming Language/Structured Query Language’ (abbreviated: PL/SQL) Some of the more notable PL/SQL properties: - User-defined types are created at the schema level with statement: create type ( …) substantial OO (3 GL) can be interfaced to little-known SQL standard constructs - block structured => PL/SQL is least-cluttered way to cover the important structures for 3 GL/SQL interface: 1) bind variables, 2) cursors 3) stored proc/fcn 4) Statement Level Interface CSC 204 course length only permits a focus on 1), 2), and 3) above Since PL/SQL is probably new to many in CSC 204, its use starts everyone at same place 5

PL/SQL intro syntax • An PL/SQL program consists of statements, organized in (nested) block structure • Upper/Lower case letters in a program identifier treated the same When using un-quoted identifiers, Ab. C is the same name as ABC, • Reserved words and Keywords of PL/SQL include: BEGIN, END, IF, WHILE, EXCEPTION, DECLARE, LOOP and various package names such as “htp” (package name for HTML generation) are examples. As you can guess, BEGIN … END block pairs surround a collection of statements to be treated as a block Some of the PL/SQL Reserved words are also Reserved by SQL You should not use them to name program objects such as constants, variables, cursors, schema objects such as columns, tables, or indexes. (There approx. 1800 of them) 6

Table D-1 PL/SQL Reserved Words Oracle 10 g – Complete list of Reserved words (Cannot be used as identifiers) Begins with: Reserved Words A ALL, ALTER, AND, ANY, ARRAY, ARROW, ASC, AT B BEGIN, BETWEEN, BY C CASE, CHECK, CLUSTERS, CLUSTER, COLAUTH, COLUMNS, COMPRESS, CONNECT, CRASH, CREATE, CURRENT D DECIMAL, DECLARE, DEFAULT, DELETE, DESC, DISTINCT, DROP E ELSE, END, EXCEPTION, EXCLUSIVE, EXISTS F FETCH, FORM, FOR, FROM G GOTO, GRANT, GROUP H HAVING I IDENTIFIED, IF, INDEXES, INDEX, INSERT, INTERSECT, INTO, IS L LIKE, LOCK M MINUS, MODE N NOCOMPRESS, NOT, NOWAIT, NULL O OF, ON, OPTION, ORDER, OVERLAPS P PRIOR, PROCEDURE, PUBLIC R RANGE, RECORD, RESOURCE, REVOKE S SELECT, SHARE, SIZE, SQL, START, SUBTYPE T TABAUTH, TABLE, THEN, TO, TYPE U UNION, UNIQUE, UPDATE, USE V VALUES, VIEWS W WHEN, WHERE, WITH 7

Oracle 10 g PL/SQL Keywords - (can be used as identifiers, but this is NOT recommended) Table D-2 PL/SQL Keywords (such identifiers have special meaning in a specific contest, thus, could be used as an identifier) Begins with: Keywords A A, ADD, AGENT, AGGREGATE, ARRAY, ATTRIBUTE, AUTHID, AVG B BFILE_BASE, BINARY, BLOB_BASE, BLOCK, BODY, BOTH, BOUND, BULK, BYTE C C, CALLING, CASCADE, CHAR_BASE, CHARACTER, CHARSETFORM, CHARSETID, CHARSET, CLOB_BASE, CLOSE, COLLECT, COMMENT, COMMITTED, COMPILED, CONSTANT, CONSTRUCTOR, CONTEXT, CONVERT, COUNT, CURSOR, CUSTOMDATUM D DANGLING, DATA, DATE_BASE, DAY, DEFINE, DETERMINISTIC, DOUBLE, DURATION E ELEMENT, ELSIF, EMPTY, ESCAPE, EXCEPTIONS, EXECUTE, EXIT, EXTERNAL F FINAL, FIXED, FLOAT, FORALL, FORCE, FUNCTION G GENERAL H HASH, HEAP, HIDDEN, HOUR I IMMEDIATE, INCLUDING, INDICATOR, INDICES, INFINITE, INSTANTIABLE, INTERFACE, INTERVAL, INVALIDATE, ISOLATION J JAVA L LANGUAGE, LARGE, LEADING, LENGTH, LEVEL, LIBRARY, LIKE 2, LIKE 4, LIKEC, LIMITED, LOCAL, LONG, LOOP M MAP, MAXLEN, MEMBER, MERGE, MINUTE, MODIFY, MONTH, MULTISET N NAME, NAN, NATIONAL, NATIVE, NCHAR, NEW, NOCOPY, NUMBER_BASE O OBJECT, OCICOLL, OCIDATETIME, OCIDATE, OCIDURATION, OCIINTERVAL, OCILOBLOCATOR, OCINUMBER, OCIRAW, OCIREFCURSOR, OCIREF, OCIROWID, OCISTRING, OCITYPE, ONLY, OPAQUE, OPEN, OPERATOR, ORACLE, ORADATA, ORGANIZATION, ORLANY, ORLVARY, OTHERS, OUT, OVERRIDING P PACKAGE, PARALLEL_ENABLE, PARAMETERS, PARTITION, PASCAL, PIPELINED, PRAGMA, PRECISION, PRIVATE R RAISE, RANGE, RAW, READ, RECORD, REFERENCE, REMAINDER, RENAME, RESULT, RETURNING, REVERSE, ROLLBACK, ROW S SAMPLE, SAVEPOINT, SB 1, SB 2, SB 4, SECOND, SEGMENT, SELF, SEPARATE, SEQUENCE, SERIALIZABLE, SET, SHORT, SIZE_T, SOME, SPARSE, SQLCODE, SQLDATA, SQLNAME, SQLSTATE, STANDARD, STATIC, STDDEV, STORED, STRING, STRUCT, STYLE, SUBMULTISET, SUBPARTITION, SUBSTITUTABLE, SUBTYPE, SUM, SYNONYM T TDO, THE, TIMESTAMP, TIMEZONE_ABBR, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TRAILING, TRANSACTIONAL, TRUSTED, TYPE U UB 1, UB 2, UB 4, UNDER, UNSIGNED, UNTRUSTED, USE, USING V VALIST, VALUE, VARIABLE, VARIANCE, VARRAY, VARYING, VOID W WHILE, WORK, WRAPPED, WRITE Y YEAR Z ZONE Notice that the SQL standard tr DCL statements are usable within PL/SQL; The general advice: completely AVOID using Reserved words or Keywords in PL/SQL 8

PL/SQL naming Naming rules {do not confuse PL/SQL identifiers with SQL identifiers: (Recall that SQL has 2 different identifier naming systems: regular and quoted identifiers} - spaces not allowed in names - no special characters other than #, _, $ (and “$” is discouraged, but legal) Ex: a_long_PLSQL_identifier - a name must be unique within a PL/SQL block (PL/SQL has nested blocking) Literals (self-defining items) have 3 types – Number 100, 3. 14, -67, 5. 2 E 7, NULL – Character string ‘A’, ‘This is a string’, ‘ 0001’, NULL (also represented by ‘’) (Embed a single quote in a string with an extra quote: ‘New Year’’s Day’ – Boolean TRUE, FALSE, NULL (these are not enclosed in quote marks) General languages note – Within a block “B” of PL/SQL code, it is possible to communicate (read & write) data and other kinds of entities in: * SQL*Plus * call a PL/SQL stored procedure/function * call-out to other programming languages * access “foreign” database systems using some form of gateway entry 9 (or a database link between Oracle server 1 < --- > Oracle server 2)

PL/SQL blocks Notes: there are 2 modes of PL/SQL translation: Interpreted* & native-compiled As with most languages, PL/SQL source is partially compiled & partially interpreted via bytecode • 2 types of (source code) block – Anonymous (abbr anon) – can be used anywhere in SQL*Plus commands or a script This kind of block exists ONLY in source code textfile form, and is directly translated and executed where it appears in a stream of SQL*Plus. No part of an anonymous block is stored in the DD; the Oracle DD stores/knows NOTHING about an anonymous block – Named (procedure or function) – both source and executable form is stored in the DBMS itself => enables SQL and the DD info to manage procs/fcns Large-scale apps organized into “Packages” – each package is a collection of related procedures, functions, data structures, etc. • Both anonymous and stored proc/fcn block types have the following sections: – Declarations – Executable – Exception handling Outline of syntax for a PL/SQL block DECLARE Optional declaration of constants, variables, cursors, user-defined exceptions, … BEGIN Mandatory section containing PL/SQL statements (Note: NULL; is the do-nothing (i. e. , noop) statement) EXCEPTION {Optional section specifying different action(s) to do for various errors in executable section a WHEN keyword starts exception code and “; ”, not an “END: ” terminates this exception code part; there can be any number of WHEN sections} 10 END; Of the 4 keywords, only BEGIN and END are mandatory

PL/SQL anonymous block – first example • Anonymous blocks - are unnamed – translated/executed, but not stored anywhere; exists only in source code form, as a file; Instructor’s file name convention for any PL/SQL source code is x. plsql - use “; ” to terminate each SQL statement or PL/SQL control statement in each block (except for BEGIN) - use “. ” to close SQL*Plus buffer contents - a PL/SQL block is treated as one continuous statement in a query buffer, and a “; ” within the block does not terminate the block nor execute the buffer contents. At the SQL> prompt, a database connection already exists; execute file x. plsql the same way as x. sql, using @ 3 GLs (Java/Python/C …) need connection+authentication source code (as illustrated in Python bind variable demo) First anonymous block example: skeleton of code: SELECT … INTO a local variable DECLARE Multiple statements per line legal, but code will be less readable v_variable VARCHAR 2(5); <--- Local variable into which a database value is stored BEGIN SELECT column_name INTO v_variable < -- SELECT INTO extracts a SQL data value into a 3 GL variable FROM table_name; < -- Table/object must exist; this is compiled code, not runtime interpreted EXCEPTION (SELECT xxx INTO is the fastest way to get one scalar value from SQL to PL/SQL) WHEN exception_name THEN. . . ; < -- Exception-handler code END; < -- These last two lines should ALWAYS be included in a PL/SQL show errors block of either type; “/” translates source, and if no errors, executes it A full, complete first example of a PL/SQL anonymous block is at home. Page: plsql_ex 1_204_s 21. plsql / ALWAYS REMEMBER -- Before executing PL/SQL source code, do SQL*Plus setting - set serveroutput on Compile and run in SQL*Plus: SQL> @xxx. plsql 11 When no unhandled execution errors or compile errors occur, and following any/all output generated by the source code, you should see msg:

PL/SQL variables and declarations Types of built-in variables in PL/SQL - scalar – a single value - composite – such as records so that groups of fields can be manipulated - reference – “refs”/ “pointers” to designate other program items (Ex: ref cursor) - LOB – these are locators/references that specify large objects stored in files (for example, a movie or audio clip could be a BFILE type of LOB) - non-PL/SQL -- variables in languages that interact with PL/SQL, and accessible in PL/SQL Example: bind variables declared in other languages All PL/SQL variables have (similar to SQL, but not exactly the same): - datatype: - storage format - valid range of values DECLARING variables: identifier [CONSTANT] datatype [NOT NULL] [value: = expr]; DECLARE v_hiredate v_dno c_pi v_name v_mynumber DATE; built-in type “DATE” inherited from Oracle date type number(2) not null : = 10; Initialization of value CONSTANT NUMBER : = 3. 14159; e. lname%type; Inherits type of employee. lname column; PL/SQL also recognizes public synonyms; In x%type, x is called an “anchored variable” number; ( More examples of anchored variables in next slides) < -- What is the INITIAL value ? ? ? ANSWER: null (the only time a type-less value can be assigned) Note: Identifier name convention: c_xxx (or v_xxx) is a PL/SQL constant (or variable) name ( The “v_. . . ” and “c_. . . ” instructor conventions for this module, and are NOT PL/SQL rules; they are naming conventions used in this course ) 12

DBMS_OUTPUT package; simple SELECT into PL/SQL § § PL/SQL does not have virtual I/O statement(s) such as “READ”, but built-in package DBMS_OUTPUT has many I/O methods/routines for use in PL/SQL DBMS_OUTPUT. PUT_LINE( ) is one of the most-used routines (PUT_LINE does not cause immediate output flush (does lazy writes)) § The SQL*Plus session variable SERVEROUTPUT must be toggled ON to display session output (the default is OFF) § Next, illustrate another SQL retrieval of a single column value from a table into an anchored variable in PL/SQL There is a utility utl_file for reading from flat files - probably not be used in this course 13

Complete PL/SQL anonymous block example -- File. Name. plsql -- First PL/SQL anonymous block example -- Usage: Enable DBMS_OUTPUT output by set serveroutput on, and compile via: SQL> @File. Name. plsql DECLARE v_lname company. employee. lname%TYPE; BEGIN -- Limitation: only ONE lname can be returned into variable v_lname SELECT lname INTO v_lname FROM company. employee String concatenation WHERE ssn like '333445555'; DBMS_OUTPUT. PUT_LINE ('Last name of SSN 333445555 is: ' || v_lname); END; / show errors Notes: 1. SQL code is recognized without need of any extra interface syntax 2. || (two vertical bars) is the string concatenation operator for string expressions 3. A version of this code is on course home. Page: Demo this: blocks 3. plsql 4. PL/SQL variable MUST be used to receive values from SQL – the simplest way is with SELECT … INTO … SQL> begin select count(*) from p; end; / < = Generates an error begin select count(*) from p; end; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00428: an INTO clause is expected in this SELECT statement 5. Returned row count of SELECT … INTO … is checked by PL/SQL IF … like ‘ 333445555’; is replaced by like ‘ 9%’, the query fails, with error msg: ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 8 < == This shows that PL/SQL enforces the 14 INTO limit of at most one returned scalar value

Variable initializations and null values in PL/SQL SQL> @pass. Null. To_plsql < -- Executing PL/SQL source code file pass. Null. To_plsql SQL> set echo on SQL> set serveroutput on < -- Ensures that PL/SQL generated output is displayed SQL> spool pass. Null. To_plsql. ans SQL> declare 2 v_var e. superssn%type : = 'abcdefghi’; < -- Declare and initialize a PL/SQL variable to a non-null value 3 v_i. Am. Initially. Null number; < -- By contrast, declare, but do NOT initialize type number variable 4 begin 5 select superssn into v_var from e where ssn = '888665555’; Borg has no supervisor (superssn is null) 6 dbms_output. put_line('Passed value of Borg superssn(=null) from SQL to PL/SQL is: ' || v_var); 7 -- A more user_friendly feature is the NVL function; it displays a specified 2 nd nvl passed argument rather than nothing 8 -- However, the type of NVL's arguments must match 9 dbms_output. put_line('Initialized value of v_i. Am. Initially. Null is: ' || nvl(v_var, 'Null. Repr’)); < -- nvl returns 2 nd arg when 1 st arg 10 -- Additional confirmation that v_var has been set to null expression value is null 11 if v_var IS NULL then 12 dbms_output. put_line('IS NULL compare shows that v_var has been set to null'); 13 end if; 14 -- Also, confirm that v_i. Am. Initially. Null was actually initialized to null 15 dbms_output. put_line('Initialized value of v_i. Am. Initially. Null is ' || nvl(v_i. Am. Initially. Null, -111111)); 16 end; 17 / Passed value of Borg superssn(=null) from SQL to PL/SQL is: < -- A null value is displayed in Oracle shows nothing Initialized value of v_i. Am. Initially. Null is: Null. Repr < -- A null value represented by a displayable value IS NULL compare shows that v_var has been set to null < -- Use IS NULL to test for a null value Initialized value of v_i. Am. Initially. Null is -111111 < -- NVL function result for displaying a null value of type number PL/SQL procedure successfully completed. SQL> show errors No errors. 15

bind and Anchored variables • bind variables are: - Declared in the bind environment such as SQL*Plus, PL/SQL, Java, Python, etc Ex: SQL> variable g_double varchar 2(20) < -- SQL*Plus bind declaration - Within PL/SQL code, a bind variable is accessed/used with “: ” prefix (Ex: : g_double) - And a bind variable can be referenced, assigned a value, and PRINTed in PL/SQL A simple anonymous block, interactively created and executed in SQL*Plus: SQL> BEGIN 2 : g_double : = ‘Hi from PL/SQL’; < -- g_double must already exist as a SQL*Plus variable 3 END; 4 / < -- Execute the anonymous block SQL> PRINT g_double (do not use leading “: ” to refer to bind variable in SQL*Plus itself) ---------------With bind variables, we now have 3 languages that can access each other’s variables: Results of PRINT statement: G_DOUBLE ---------------- bind language: SQL*Plus 3 GL language: PL/SQL DB language: SQL Hi from PL/SQL = => Later see: bind variables can speed up execution of loops containing SQL statements Note: Identifier naming convention by instructor: g_xxx is an SQL*Plus bind variable § Anchored variable examples declared in PL/SQL code - declare via: x%TYPE, where x can be a declared PL/SQL variable or a table or view or column name or reference - Also, iterative anchoring is allowed: v_comm NUMBER(7, 2); v_total_comm v_comm%TYPE; 16 v_net_comm v_total_comm %TYPE; - NOT NULL constraints inherit from PL/SQL declarations, but not from SQL table columns

§ § Cursors A more general interface is needed for 3 GL apps to access SQL queries with n results rows vs. first anon ex. with only one row returned into one PL/SQL variable Note: a 3 GL “array” is not the interface solution A cursor is the SQL-92 standard construct for a RDBMS < - - > app interface Cursor results correspond to a temporary table containing cursor’s query results; results are accessed only one row at a time in the 3 GL (there is a “BULK COLLECT” option for getting n-rows-per-round-trip from SQL) § Cursor returns data results to 3 GL and also implements a program < -- > /SQL/DB communication area, for a) data control b) errors & other purposes, and an Oracle cursor also contains info usable by the qo § 2 types of cursors, as categorized by their names and how created - implicit cursor: not declared; has various processing methods; user has some, but not complete control of such cursors - explicit cursor: has a name; must be declared in the DECLARE section, and contains a SQL SELECT statement; there are SQL language standard commands to process such a cursor: OPEN, CLOSE, FOR … LOOP etc. CURSOR statement must be in DECLARE section cursor. Name is any legal identifier Syntax of explicit cursor declaration: CURSOR cursor. Name IS SELECT statement; Query might be followed by other clauses (more soon) Like to an anonymous block, a cursor’s only non-volatile existence is in PL/SQL source code; 17 however, for qo purposes, a given cursor “CR’s” internal representation might be cached/saved

Explicit vs. Implicit cursors The life cycle for an explicit cursor is shown below Definition in source PL/SQL code (in the DECLARE section) Operations/Actions during cursor execution time An implicit cursor example – x is an implicit cursor, thus, is not declared declare 2 v_row. Count number; -- Init table row count 3 4 begin 5 for x in ( select * from T ) < == Assume table/view T exists 6 loop < = In this outline example x is not referenced in loop body, 7 v_row. Count : = v_row. Count+1; but if x was referenced, its type would be the row type of T 8 end loop; 9 dbms_output. put_line('Table row. Count is ' || v_row. Count); 10 end; Review point: There is an error in this example – can you find it? 18

SQL Standard processing on explicit Cursors The model/form of cursor usage follows the familiar simple model of I/O programming: 1. Open an existing file “f” and execute a loop. 2. The loop body has code to 1) read next record “r” from f and 2) process contents of r. All control/action functions listed below were specified by fairly early SQL standards, including those covered in subsequent slides appear in the SQL-99 (aka SQL-3) standard § OPEN Cursor. Name(args); – successful call gives access to “first” row of the active data set; “active” is meaningful because a row can be referenced by > 1 cursor (the active cursor is THE one of n cursors currently being processed by app code) § Values in the rows returned in the cursor can be retrieved into PL/SQL variables for arbitrary application ‘processing’ i. e. , the ‘processing’ best done in a 3 GL and/or not suited for SQL § During cursor row extraction into PL/SQL (via SELECT into), the number of columns in the SELECT’s result row schema must be matched by the PL/SQL variables receiving data § A PL/SQL LOOP control statement can be executed, and in the loop body, individual rows are retrieved using an FETCH statement and then each row is processed by 3 GL application code < - - Like file system op. 192. at top of this slide

§ Explicit cursor standard SQL functions: FETCH, %FOUND, etc. ; FETCH Cursor. Name INTO Variable. List or Record. Name; Retrieves the current row of the active cursor into specified variables or row schema record type occurrence, then, by default, the current cursor positions to “next row” in the cursor data, if a next row exists (no need for some form of “get next row”) Ex: FETCH employee_cur INTO v_ssn, v_sal; < - - Store row’s columns in two PL/SQL variables OR (and often preferred) < -- SQL results MUST be stored in PL/SQL structures FETCH employee_cur INTO emp_rec; < - - Store next cursor row into a PL/SQL record instance In this example, emp_rec is a record structure declared using a %ROWTYPE declaration (example soon) § Some other SQL standard CURSOR functions - %ISOPEN - %FOUND - returns TRUE if cursor is open Example use: cursor. Name%ISOPEN - returns TRUE if last FETCH returned a row, and complementary attribute is: %NOTFOUND - %ROWCOUNT - returns total number of rows that have been accessed with this cursor since most-recent OPEN + There is also support for accessing a random result row Two types of cursors based on effect on DB data read-only - can only read cursor data and use 3 GL code to calculate with it in 3 GL only VS. updatable – data obtained from cursor rows can be modified by 3 GL code and written back to the DB, thus modifying DB content; Note: This has consequences for tp because cursor row update processing is automatically in the context of Oracle tp. 20 Thus, when a cursor result row is updated in 3 GL, cursor execution is in current tr isolation level of this 3 GL app.

Cursor parameters, and a read-only cursor example § A cursor can be declared with parameters. Parameters provide for more flexible cursors - Use of parameter values for parts of cursor’s queries - Pass different parameters at various OPENs (cursor re-use within one SQL session) § CURSOR Cursor. Name [(Parameter 1 Data. Type, Parameter 2 Data. Type, …)] IS SELECT query; Parameter values can be passed to a cursor as literals, PL/SQL expressions, or bind variables § First example of: PL/SLQ CURSOR PROCESSING with a read-only cursor (the default cursor type) undefine dnum < == removes any current copy of dnum from previous module executions DECLARE employee_rec employee_cur%ROWTYPE; (It is legal to reference a cursor’s row type before the cursor definition) CURSOR employee_cur (dept_id company. employee. dno%TYPE) IS < == formal parameter example SELECT * FROM e WHERE dno = dept_id; dept_num e. dno%type : =&&dnum; -- Automatic one-time prompt for department number; value assigned to dept_num BEGIN OPEN employee_cur (dept_num); Passing an actual parameter for a dept_id value DBMS_OUTPUT. PUT_LINE('Name and salary of employes in Dept: ' || dept_num); FETCH employee_cur into employee_rec; < -- e. salary type: number (10, 2), WHILE employee_cur%FOUND LOOP but TO_CHAR type conversions is DBMS_OUTPUT. PUT_LINE NOT needed here (employee_rec. lname || ‘, ’ || employee_rec. fname || ‘makes’ || TO_CHAR(employee_rec. salary)); FETCH employee_cur into employee_rec; END LOOP ; CLOSE employee_cur; END; / show errors Ex: parameter init: CURSOR employee_cur (dept_id company. employee. Dept. Id%TYPE : = 99) IS The above code version at course home. Page: cursor_parameters_demo. plsql 21

Updatable cursor example § UPDATE and DELETE on cursor row data can be performed on fetched rows § CURSOR Cursor. Name IS SELECT Column. Names FROM Table. Name [ WHERE condition ] [ FOR UPDATE [of Column. Names] [NOWAIT] ]; 1. The FOR UPDATE specifies intention to update some cursor rows in the database 2. If NOWAIT is specified, you are notified immediately if another tr holds a lock on >=1 row of cursor data = > your exception block will execute because control has returned to your app. After exception handled, your appl can do other processing, then, optionally, retry; = > the appl request is NOT QUEUED as happens with SQL-level row lock requests 3. Without NOWAIT, your app automatically WAITs (WAIT is default) until the other tr release(s) locks held on the cursor’s data; that is, your app’s tr is in a tr wait state With no notification of why § UPDATE or DELETE in a cursor is performed, per row on row(s) fetched. WHERE CURRENT OF clause allows manipulation of the current cursor row: Assuming app does a commit; , these changes are written back to the database UPDATE Table. Name SET clause WHERE CURRENT OF Cursor. Name; DELETE FROM Table. Name WHERE CURRENT OF Cursor. Name; 22

Block nesting example with loop EXIT demo -- Source filename: plsql. Block. Exit. Demo. plsql SQL> set echo on SQL> set serveroutput on SQL> declare 2 v_sum number : = 0; 3 begin 4 declare 5 k number : = 0; 6 begin 7 while k < 3 loop 8 dbms_output. put_line('Starting loop body with k = ' || k); 9 v_sum : = v_sum + k; 10 if k = 2 then 11 dbms_output. put_line('Now exiting loop'); 12 exit; 13 end if; 14 k : = k + 1; 15 end loop; 16 dbms_output. put_line('Finished inner-block execution'); 17 exception 18 when others then 19 dbms_output. put_line('Unhandled exception in inner block'); 20 end; 21 dbms_output. put_line('Final v_sum value is: ' || v_sum); 23 end; 24 / Starting loop body with k = 0 Starting loop body with k = 1 Starting loop body with k = 2 Now exiting loop Finished inner-block execution Final v_sum value is: 3 PL/SQL procedure successfully completed. (Omitted final ‘No errors’ message. ) 23

• Cursor variables A cursor variable can be used with different cursors within a program (and thus acts like a pointer or reference to a cursor) All of: OPEN, FETCH, %FOUND, etc. operators introduced earlier are called using syntax: cursor. Name%function where ‘function’ is one of OPEN/FETCH/ etc. OR with open l_cursor for … where l_cursor is of type ref cursor A given cursor variable can also be assigned to another cursor variable § Declaring a cursor variable, example of a strong/static ref cursor: - create a referenced cursor type, as in: TYPE Cursor. Type. Name IS REF CURSOR [RETURN Return. Type]; Return. Type must be a record or %ROWTYPE structure - declare a cursor variable Cursor. Var. Name Cursor. Type. Name; < -- Called a strong ref cursor • Example use of a variable of type ref cursor: for k in 1. . 10 loop open Cursor. Var. Name for Some SQL query ; close Cursor. Var. Name; end loop; (Homework#4, Q#1 includes code that uses a ref cursor type variable) 24

Exceptions § § § In the PL/SQL language, errors are called exceptions Exceptions can be caused by hardware/system/user/applications errors When an exception arises in a block, the PL/SQL program transfers to a section of that block specified by the EXCEPTION section for dealing with that exception: There are 3 situations: : - if an exception handler exists, its actions are performed AND the block containing the EXCEPTION statement exits to the containing block (if any), else return to caller of this module - if no exception handler exists in current block, control shifts to outer blocks and search for associated handlers in outer block(s) by inner-to-outer nesting order - if no handler exists in any outer block, an error msg is returned, run terminates, and control returns to caller of this module § Recall syntax of anonymous blocks: DECLARE Declaration of constants, variables, cursors, exceptions Exception. Name 1 exception; Example exception declaration BEGIN -- Suppose exception is raised here Each WHEN clause in an EXCEPTION section is followed by n statements, each EXCEPTION statement, including the last one, -- Exception is processed here terminated by “; ” END; The general syntax of the exception section is: EXCEPTION WHEN Exception. Name 1 [OR Exception. Name 2, … ] THEN Exception handler code Executable statements [WHEN Exception. Name 3 [OR Exception. Name 4, … ] THEN Executable statements 25 [WHEN OTHERS THEN Executable statements]

Exceptions - continued § In the PL/SQL language, there are 3 types of exceptions: - About 20 Oracle pre-defined and named (raised implicitly when a DBMS or PL/SQL error occurs) - Non-pre-defined Oracle server exceptions, standard Oracle server errors and they are not named - User-defined are declared in the DECLARE section and raised by app code explicitly; user decides which abnormal conditions are exceptions We will not have detailed look at various kinds of exceptions, but here is a good overview of this topic: https: //plsql-tutorial. com/plsql-exception-handling. htm Example EXCEPTION section code: EXCEPTION WHEN some. Exception. Name THEN … WHEN OTHERS THEN This catches any errors caused by failed statements : that are not handled by another part of this EXCEPTION section ROLLBACK TO q 1_end; tr savepoints are also visible within an EXCEPTION block -- Pass Error Msg to log table DBMS_OUTPUT. PUT_LINE(SQLERRM); SQLERRM is pre-defined & returns SQL error codes DBMS_OUTPUT. PUT_LINE(‘ ian Error Msg string executed during EXCEPTION handling'); write_h 7 log(SQLERRM); END; / 26 show errors General Note – Executing PL/SQL code always happens in tp mode (including EXCEPTION block code)

Exceptions – Outline of cursor execution retry when cursor rows are locked DECLARE <= Cursor and cursor record declarations v_max. Retries number : = 2; -- Max number of cursor executio retries v_final. Value number; -- Final table T's row value BEGIN -- Main block -- Loop to retry update cursor execution FOR i in 1. . v_max. Retries LOOP < -- i does not need to be declared; a for some. Range loop is easiest PL/SQL loop construct begin <= 'Starting cursor execution … ‘ msg savepoint exec. Cursor. Again; FOR v_xyz_rec in xyz LOOP < -- FETCH operation not needed for this form of updatable cursor <= Update code for T END LOOP; -- Cursor execution loop commit; -- Commit changes by cursor execution EXIT; -- Terminate cursor execution retries loop, and resume in containing block EXCEPTION WHEN OTHERS THEN BEGIN <= All code for handling a cursor-rows-locked exception END; end; -- For i body block end loop; -- For i loop <= Implements last part of the output for a successful cursor execution END; -- Main block / Complete and functioning updatable cursor execution, with a limited number (here, assuming 2) of retries must fill in code at the places indicated by “ <= xxx” 27

An implicit cursor FOR UPDATE example An implicit cursor demo SQL> create table ctemp(c number); Table created. SQL> insert into ctemp values(1); 1 row created. SQL> commit; Commit complete. SQL> select * from ctemp; C -----1 SQL> begin 2 for x in (select * from ctemp for update) 3 loop 4 update ctemp 5 set c = -1; 6 end loop; 7 end; 8 / Implicit cursors occur frequently in practical 3 GL apps that process data (large and small); many such cursors are executed in repetitive open cursor / execute / close cursor scenarios. The example to the left demos an implicit for update type cursor. Such cursor executions can consume large amounts of SQL resources, and doing so will impact other users on a DB server. It can be shown how bind variables and cursor caching can greatly decrease cursor processing overhead and execution time. Simpler syntax vs. explicit cursors that need FOR UPDATE clause PL/SQL procedure successfully completed. SQL> /* Final table */ SQL> select * from ctemp; C -----1 28

SQL statement translation – Hard parse vs. Soft parse Hard parse If a session executes a SQL statement S that does not exist in the shared pool (= a global area of sharable data structures for DB sessions), then Oracle must do a hard parse of S. Def – hard parse of a statement involves: 1. Allocate memory for the statement from the shared pool. 2. Check the statement syntactically 3. Check if the user trying to execute the statement has the necessary rights to execute it 4. Do query optimization (qo) A hard parse is expensive, for CPU used, number of shared pool latches taken, and number of library cache latches acquired and released. It should be avoided whenever possible. Soft parse Def - If a session executes an SQL statement S that exists in the shared pool and there is a version of the statement that can be used, then this is referred to as a soft parse <= the dominant savings is avoidance of qo (Step 4. above) Identical Statements? That is, can a cursor be shared/re-used? A statement is identical to another statement, only if there is absolutely no difference between the characters in the statements. For example, select x from y and SELECT X FROM Y are not identical, as text, although they clearly do the same thing. Even if two statements are identical, this doesn't mean they are shareable. In order for two identical statements to be shareable, the following must be true: Object names must resolve the same actual objects The qo settings are compatible Types and sizes of bind variables are similar 29 The NLS (National Language Standard = ability to support a database use with native languages)

SQL statement translation – Hard parse vs. Soft parse The diagram visualizes major steps that are applied to each and every submitted Oracle SQL statement. If a SQL statement does a Soft parse most of the significant statement translation cost (Optimization & Query Transform) is avoided: such costs include excessive latching and shared pool memory processing. A Hard parse does all of the left-diagram’s operation except for Execution. Many believe that poor SQL performance is always due to excessive disk I/O. This is NOT true, particularly with excessive Hard parsing that will involve excessive CPU processing = > A DB can be CPU-bound just as it can be I/O-bound when extreme rates of latching/locking and SQL calculations happen. 30

The SQL/PSM ANSI standard • SQL/PSM is part of the SQL standard and specifies how to code Persistent Stored Modules • By persistent is meant: – the ability to store in the DBMS (not in OS files) both source and an executable form of a procedure/function (abbreviate them as: proc/fcn) – The DD stores metadata about each proc/fcn, such as name, signature, validity (status such as VALID or INVALID, …), etc. – In Oracle, the user_objects DD view can be queried to see proc/fcn metadata – With this and other views, SQL, rather than programmers, can manage (query/report/organize) these items, reducing tedious, error-prone human management – A stored procedure/function is managed by ALTER, DROP, etc. , just as any other genuine Oracle object • PL/SQL implements the PSM specifications of the SQL standard document, using syntax that differs from the syntax of the standard. For example, the SQL-99 standard syntax for a stored procedure is different in Oracle, but, if anything, Oracle extends on the standard. • Elmasri&Navathe Textbook, (Ch 9, section 9. 6. 2 Edition#5) covers the SQL/PSM specification = = > The next few slides cover some syntax (i. e. , declaration) details on stored procedures and stored functions 31

PL/SQL stored procedures & functions Up till now, the PL/SQL coding involved only anonymous blocks Although good for testing and proof-of-concept, and some other things listed below, they have many limitations: § Anonymous blocks cannot be called by another block during execution (without a name there is no possible referencing/calling mechanism) § They cannot get arguments from another block § They can, however, call PL/SQL procedures and functions (both of which are named blocks with optional parameters) § A given anonymous block can be nested within another anonymous block or a procedure or function ========================== Procedures and functions (in either PL/SQL or any 3 GL) implement modularized PL/SQL code, procedures and functions § The following slides introduce stored procedures and functions as a more general and flexible structuring approach to the 3 GL app code. It is a short summary of the basics from many places in Oracle documentation and books. 32

PL/SQL stored procedures Procedures A procedure is a named PL/SQL block that can have arbitrary programming logic. It has following general structure: CREATE [OR REPLACE] PROCEDURE Procedure. Name [(parameter 1 [, parameter 2…]) ] Nested procedures could be IS declared here. [ Constant/Variable declarations ] Each parameter has a mode: BEGIN IN, OUT, or IN OUT Executable statements [ EXCEPTION 1 space here Exception handling statements ] Exception handling optional END [ Procedure. Name ]; The create procedure command indicates that , just as with tables, indexes, etc. , a procedure is an object. Note – Irregular syntax: unlike anonymous block, there is no DECLARE statement in either a stored procedure or a stored function; 33 simply place declarations following the IS reserved word

PL/SQL stored procedures Calling a stored procedure can be done within PL/SQL by: Procedure. Name [ (argument 1, …) ]; Example calls: MONTHLY_SALARY(V_SALARY); DISPLAY_MSG; Parentheses not needed if no parameters Procedure Header CREATE OR REPLACE PROCEDURE monthly_salary (v_salary_in IN company. employee. salary%TYPE) CREATE OR REPLACE PROCEDURE display_msg Parameters are in 3 kinds: IN – passes a value in; read only (the default parameter mode) OUT – passes a value back to caller; write only IN OUT – (1 space/blank after IN) passes a value in and returns a (possibly different) value back to caller 34

PL/SQL stored procedures -- Procedure Call SEARCH_EMP( 543, LAST); -- Procedure Header PROCEDURE SEARCH_EMP(EMPNO IN NUMBER, LAST OUT VARCHAR 2) Formal and actual parameters are associated/matched in 2 ways: - positionally - named (Example: in call statement, use notation EMPNO => 543) A compiled PL/SQL procedure can be called from SQL*Plus, i. e. , SQL by: SQL> EXECUTE Procedure. Name [ parameter 1, … ] When you get an errors (instead of results and ‘PL/SQL procedure successfully completed’), this is when error diagnostics are generated from SQL> SHOW ERRORs In fact, DD view user_errors gives error info about proc/fcn compilation, etc Recompiling a procedure A procedure cataloged in the DD becomes invalid if the tables it is based on are deleted or altered. You can recompile with create or replace … OR via: SQL> ALTER PROCEDURE Procedure. Name COMPILE; 35

PL/SQL stored procedure – example definition & call CREATE OR REPLACE PROCEDURE search_emp (i_ssn IN VARCHAR 2, -- An employee ssn o_lname OUT VARCHAR 2, -- An employee last name no. Data IN OUT BOOLEAN) -- True if SELECT INTO retrieves no data -- Note: parameter type declarations such as: char(9) give compile error in PL/SQL -- This is why i_ssn and o_lname are declared as shown (without max sizes) -- Same rule for numeric types: declare number, not number(a, b) indicating scale/precision IS BEGIN SELECT lname NO_DATA_FOUND is a built-in Oracle INTO o_lname exception type recognized by all modules FROM e WHERE SSN = i_ssn; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT. PUT_LINE('PK ' || i_ssn || ' was not found'); no. Data : = TRUE; END search_emp; / show errors DECLARE Anonymous block driver module v_lname e. lname%TYPE; Parameter v_no. Data used to v_ssn varchar 2(10) : = &&emp_ssn; suppress the anon block v_no. Data boolean : = FALSE; PUT_LINE I/O when the query BEGIN returned no data search_emp(v_ssn, v_lname, v_no. Data); < - - search_emp call -- Display name only when data was found IF v_no. DATA = FALSE THEN DBMS_OUTPUT. PUT_LINE('Employee with SSN: ' || v_ssn || ' name is: ' || v_lname); END IF; END; / show errors See class home. Page file: demo_search_emp_example. plsql 36

PL/SQL stored functions A function, like a stored procedure, is a named PL/SQL block. The main difference is that a function always returns a value to caller. Function properties - can be passed >= 0 parameters - must have a RETURN statement (returns a value) in the executable section (at least one RETURN statement must appear) - return value’s data type must be declared in function header’s RETURN clause - cannot be executed as a stand-alone statement (must be a term in an expression) - can have IN, OUT, and IN OUT parameters, but use of OUT and IN OUT parameters is consider bad programming practice CREATE [OR REPLACE] FUNCTION Function. Name [ (parameter 1 [, parameter 2…]) ] < = Some other clauses are possible here (we will cover one of them) RETURN Data. Type <= the type is NOT sized IS [ Constant / Variable declarations ] BEGIN Executable statements RETURN Return. Value [EXCEPTION exception handling statements RETURN Return. Value ] 37 END [ Function. Name ];

PL/SQL functions CREATE OR REPLACE FUNCTION get_deptname (p_deptid IN NUMBER) RETURN VARCHAR 2 IS v_deptname VARCHAR 2 (12); BEGIN SELECT dname INTO v_deptname FROM department WHERE dnumber = p_deptid; RETURN v_deptname; END get_deptname; / show errors Example of calling get_deptname from a stream of SQL*Plus code: : set serveroutput on DECLARE -- Get employee’s ssn from user session via substitution variable v_dept# e. dnumber%TYPE; v_empid e. ssn%TYPE : = &ssn; v_deptname VARCHAR 2 (12); BEGIN : Expression for department number, v_deptname : = get_deptname( v_dept# ); here assumed obtained by executing : a SQL query above this source line Complete Example PL/SQL procedure driver module that calls another stored procedure: plsql_procedures_and_calls_example. out 38

PL/SQL procs/fcns – execution definer’s rights and executer/caller rights (RE: security) How the Privileges of a Schema Affect the Use of Invoker's Rights Procedures: : An invoker’s rights procedure is useful in situations where a lower-privileged user must execute a procedure owned by a higher-privileged user. When a user runs an invoker's rights procedure (or any PL/SQL program unit that has been created with the AUTHID CURRENT_USER clause), the procedure temporarily inherits all of the privileges of the calling user while the procedure runs, regardless of procedure owner. During execution, the procedure owner also has access to this invoking user's privileges. Usage example CREATE OR REPLACE FUNCTION fcn. Name (p_some. Name IN NUMBER) RETURN VARCHAR 2 AUTHID CURRENT_USER IS. . . The default setting for AUTHID is definer (where definer is the owner of the procedure) 39

PL/SQL functions – the DETERMINISTIC clause (execution semantics) An example of an advanced clause that can be used with a PL/SQL stored fcn is DETERMINISTIC This is used in various advanced database purposes not covered in this course) CREATE OR REPLACE FUNCTION fcn. Name (p_some. Name IN NUMBER) RETURN VARCHAR 2 DETERMINISTIC IS The DETERMINISTIC clause may appear at most once in a function declaration or definition. A function so marked is called deterministic. A deterministic function must return the same value on two distinct invocations if all IN and IN OUT arguments provided to the two invocations are the same. A DETERMINISTIC function may not have side effects. A DETERMINISTIC function may cannot raise an unhandled exception. If a function with a DETERMINISTIC clause violates any of these semantic rules, the results of its invocation, its value, and the effect on its invoker are all undefined. Usage Notes The DETERMINISTIC clause is an assertion that the function obeys the semantic rules. If the function does not, neither the compiler, SQL execution, or PL/SQL execution may diagnose the problem and incorrect results might be silently produced (that is, with no warning messages) By definition, a function cannot be deterministic if it returns values based on a) the time-of-day b) random values obtained from statistical distributions c) random numbers, etc. 40

Intro to PL/SQL packages The “package” construct (and overall software concept) is: ONE OF THE MOST IMPORTANT ORGANIZING TECHNIQUES FOR LARGE-SCALE PROGRAMMING. { Java has packages, Python has packages, and PL/SQL has had packages for almost 40 years } Concept Def – a package is a named collection of related language elements Examples a) A package of functions that calculate statistics values from some table b) A package of lock services (REQUEST, RELEASE, SLEEP etc. ) for implementing tp concurrency Package access from apps can be – a) built-in, such as dbms_lock (as cited in b) above OR b) User-written Overall package structure – a) A specification < = interface to callers b) A body < = implements/defines items in specification AND also private items, if any Summary of benefits of using packages – 1. Reduce/remove compile dependencies between procedures 2. Increase namespace capability (the contents of each package has its own namespace) 3. As an encapsulation and information hiding tool 4. Related items occur together (Ex: built-in package name dbms_output makes it obvious that this package is the place to look for output methods and procedures) Package Coding best practice 1. Design and define the package specifications before the package bodies = > provides top-down design First by creating specifications and deferring bodies until later, and then Second implementation as package bodies are created 41 2. In package specifications, declare only items that must be visible to invoking programs

DB triggers – Intro • A Trigger is the database analog of a systems programming “interrupt handler” procedure They are one form of Oracle implementation of SQL standard ASSERTION statement (As noted in E&N: No RDBMS vendor has fully implemented the ASSERTION statement) (+) there are useful things that can be done with triggers (-) triggers can be OVERused; any number of triggers per table is allowed BUT too many triggers will slow down table processing in big apps with big tables In Oracle, a trigger is a form of stored proc Uses: Internally in a DB, 1) reacting to pre-specified DB state changes and events 2) database auditing/logging • Triggers have temporal specification: BEFORE trigger fires before a SQL statement like INSERT executes Some Uses: * Ex: place pre-defined values into a new row (reduces complexity of INSERT commands) * Ex: validate a value in a row INSERT before actual insert is executed AFTER trigger fires after a DML statement is executed Some Uses: * Ex: could use to initiate some action(s) based on a DML statement type that just occurred, such as logging an operation Available built-in boolean fcns INSERTING, UPDATING and DELETING One of these will return TRUE depending on the type of DML statement; = = > allows different processing for different DML operations in the trigger body: For example: IF UPDATING THEN … • In Oracle, the 5 categories of triggers are 1) DML Relevant DD views: user_triggers, user_objects 2) INSTEAD OF – these fire instead of the corresponding DML operation, and are defined only on views (Ex: can be used to implement 42 updates for a non-updatable view) – not covered here

Trigger clauses & specifications We will limit trigger coverage in this course to DML statements on tables for Oracle 11 g capabilities Generally speaking, a trigger can be defined on DB objects such as: table/view/schema/database Statement vs. Row-level trigger types – specifies the number of times that a trigger executes for a given SQL statement General Oracle trigger specification create or replace trigger <trigger_name> {before | after } {insert | update of column 1 | delete} on <table_name> Each trigger is defined on only one table [ for each row ] IF missing, is a “statement” level trigger; IF present, is a “row-level” trigger [FOLLOWS | PRECEDES another_trigger] Added in Oracle version 11 g to specify Order of firing among n triggers [ENABLE / DISABLE ] A trigger is enabled, by default, at creation, but ALTER TRIGGER trigger. Name DISABLE; disables it [ when (logical_expression) ] Conditions each row must satisfy for this trigger to fire for this row [declare] No IS nor AS keyword used in Oracle trigger definition [pragma autonomous_transaction; ] Allows possibility of creating a stand-alone satelite tr declaration_statements begin execution_statements end <trigger_name>; Statement Level trigger create or replace trigger price 1_trig < = The trigger is fired ONE time after a SQL UPDATE stmt S executes, after update of price_type on price regardless of the number of rows changed by S declare No ‘FOR EACH ROW’ clause price_id number; begin 43 select price_log_seq. nextval into price_id from dual; price_log_seq is an automatic PK generator insert into price_type_log values (price_id, USER, SYSDATE);

Trigger specifications – cont’d Row Level trigger create or replace trigger contact_insert_trig before insert on contact for each row ‘FOR EACH ROW’ clause specifies firing the trigger before each row ri is inserted when (Condition) but ri also must satisfy the WHEN clause condition, if any begin processing statements; end contact_insert_trig; / Pseudo columns For a given SQL statement “S” that is DML (INSERT/DELETE/UPDATE) Pseudo-row values refer to old vs new versions of column values for rows affected by the trigger (depending on place used in trigger source code) To refer to a value before S executes – Ex: original value of a column: old - used in the WHEN clause : old - used inside the trigger body Similarly, for pseudo-row new To refer to a column value that would exist after S executes – Ex: changed value of a row column new – used in WHEN clause : new – used inside the trigger body old and new must make sense relative to the DML operation: For example, old is meaningless for INSERT old and new are both meaningful for UPDATE new is meaningless for DELETE 44

DML Triggers – Oracle implementation Example of a BEFORE row level trigger: Prior to each row INSERT into table tbl, the column colx value is assigned as a copy of the PL/SQL variable v_colx value; this will override any column default -- Filename: tbl_bi_trigger. plsql -- Purpose: Before trigger to put pre-determined value into data column of a table -- Usage: Ora user coding triggers must have create trigger privilege CREATE OR REPLACE TRIGGER tbl_bi_trigger Name can be any Ora namespace-legal -- Note: tablename aliases CANNOT be used here identifier -- If u do not have update privilege, you get an Ora error when referencing trigger target table BEFORE INSERT ON tbl FOR EACH ROW DECLARE v_colx tbl. c 2%TYPE; BEGIN -- The value specified for current row's (when an INSERT is executed), colx col is assigned to v_colx -- This value could also be the value input to a Substitution variable using & or && (but would -- require a re-compile of the trigger) v_colx : = ‘Dog. Bone'; : NEW. c 2 : = v_colx; : OLD and : NEW reference a Changed vs. Existing item END; Example DML (here, INSERT) statement executions and result rows / SQL> -- User-defined value, null value, and unspecified value SQL> INSERT into tbl VALUES (1, 'Row#1'); show errors 1 row created. SQL> INSERT into tbl VALUES (2, null); 1 row created. SQL> INSERT into tbl(c 1) VALUES (3); 1 row created SQL> SELECT * from tbl; C 1 C 2 -----------1 Dog. Bone 2 Dog. Bone 3 Dog. Bone 45

Trigger firing/execution order in PL/SQL modules Multiple triggers per table; trigger firing order Note – the Oracle rules for trigger firing order are NOT standardized across RDBMSs; also other relational-like DBs have their own DB-specific rules There can be any number of triggers defined on any given table. Question: What is the firing order when n triggers are defined & enabled on table T? Answer – Firing order is a combination of ‘category’ and randomized order: There are 4 categories, and their relative firing orders are: Statement level Before triggers - sb Row level Before triggers - rb Row level After triggers - ra Statement level After triggers - sa The default firing order Example: Given: 1 sb and 3 ra triggers on table T, firing order is: The sb and then, in random order, the 3 ra For Oracle 11 g and newer: the clauses “FOLLOWS” and “PRECEDES” can be used for all triggers in a given category to order the firings (and override default order) Additional flexibility: one trigger can apply to MORE THAN ONE kind of DML statement, 46 that is, a trigger can fire, for example, on table row INSERT or DELETE

Cascading triggers One trigger execution might result in other triggers also firing (cascading) One aspect of “trigger management” (assessing their OVERuse) is the DD views user_triggers and all_triggers < = Find all triggers and assess their effects 47

Trigger use in apps + Example trigger demoing bad app coding practice Important behaviors of Oracle DML triggers in tp context Trigger xyz’s execution does not commit the tr context containing xyz. If trigger xyz is rolled back, all data changed by xyz should also be rolled back. Commits, rollbacks and save points are not allowed in the trigger body. These DCL statements compile without error, but are fatal errors at execution time. Unhandled exceptions in a trigger will cause a rollback of the SQL DML statement that caused the exception, but does NOT rollback the containing transaction. If more than one trigger is defined on an event, the order of firing has some randomness. If some triggers must fire in a specific order, you could create one trigger that executes all the actions in the required order. =============================================== Very important RDBMS app Design Principle: : Each statement “S” in a trigger body should be such that S can be rolled back. Example of Incorrect behavior if this principle is violated: create or replace trigger xyz after insert on some. Table for each row begin (post message m on social. Media. Site, message = > ‘ACME Inc. sold another house’); end; / show errors xyz is bad programming because 1. the Oracle tp system is NOT CONNECTED to any distributed tr system related to social media 2. What if 10 row INSERTs are done on table some. Table , but all these updates are rolled back ? ==> 10 messages have been sent out to the world about events that never happened The posted messages cannot be rolled back by an Oracle tp rollback statement. 48

More notes on tr scope in PL/SQL modules Calling/Called module tr scope • • • A PL/SQL module “M” begins execution, and assume that tr “x” is also initiated along with M During execution of M and execution of any blocks that M calls, assume that no DCL/DDL commands (such as: COMMIT; , ROLLBACK; or create table, etc. ) are issued On return to M from any called module, tr x is still the current tr Triggers and DCL commands – do not use them together Triggers are, in one sense, like hardware interrupts, intended to execute with as little overhead as possible. Also, COMMIT/ROLLBACK/savepoint should NOT appear in a trigger body (as previous slide showed) Recall that executing SQL DML in PL/SQL code (same as Oracle SQL at SQL*Plus command level) always has an associated ongoing transaction tr (need to check this in your 3 GL and DB) A trigger execution should NOT determine whether or not the tr terminates. Unfortunately, the PL/SQL translator does NOT throw an error at compile time, even if trigger body contains a DCL statement such as commit. Instead, a trigger execution-time error occurs when a DCL command is encountered in a trigger body: SQL> insert into A values (2, 0); < - - Assume a trigger on table A contains a DCL command insert into A values (2, 0) * ERROR at line 1: < - - The following Oracle software stack unwind is thrown ORA-04092: cannot COMMIT in a trigger ORA-06512: at "CS 204130. NODCL", line 2 ORA-04088: error during execution of trigger 'CS 204130. NODCL‘ Note: Autonomous trs provide a way for a user to log both failed or committed DB changes 49

Trigger processing issue – mutating table errors Def – a “mutating table” is a table that is currently undergoing changes in row content A row-level trigger cannot change a table (specifically, change >= 1 row) that the trigger also queries as well. Breaking this rule results in a mutating table error Note - There is no such problem/error with a statement level trigger Notes – 1) “Mutating table” refers only to a table whose row content is currently changing 2) a mutating table presents a conflict regarding the consistency of results of a SQL SELECT applied concurrently with the mutation Why Oracle throws an error when a trigger body queries a mutating table The Oracle SQL statement processing architecture guarantees: 1) The execution of each statement S is atomic (all rows affected by S are processed) 2) Each statement S must see a consistent view of data (by the principle of statement read-consistency) 1) and 2) are significant concerning row-level triggers Consider an INSERT affecting n rows for a row-level trigger; you would expect a query in such a trigger to see all the rows inserted as the trigger executes … Since the INSERT is not finished yet as successive rows are inserted, seeing m of n inserted rows would be an unpredictable read result Because of such ambiguities about the data set a query would access, Oracle throws 50 a ‘mutating table error’ rather than allowing inconsistent/ambiguous SELECT results

Mutating table errors - demo Consider the anonymous plsql block below – it initializes the demo -- mutating_table_error_demo_s 18. plsql /* mutating_table_error_demo_s 17. plsql drop table t purge; */ create table t (c number); insert into t values(1); insert into t values(2); insert into t values(4); commit; create or replace trigger t_trig after update on t for each row < - - Row level trigger declare v_t_tot t. c%type; begin select sum(c) into v_t_tot from t; < - - reading from the mutating table end; / show errors ======================================= SQL> update t < - - Trigger execution affects more than 1 row 2 set c = -1 where c> 1; update t * ERROR at line 1: ORA-04091: table CS 204129. T is mutating, trigger/function may not see it < - - Standard Oracle mutating table error ORA-06512: at "CS 204129. T_TRIG", line 4 ORA-04088: error during execution of trigger 'CS 204129. T_TRIG‘ ++ Add a dbms_output. put_line statement after the ‘select sum…’ query to see that v_tot is NOT modified by trigger execution, thus the effects of trigger body execution are discarded; that is, the SELECT … INTO is not actually executed 51

No title This slide is intentionally blank 52

PL/SQL packages As packages are fundamental technique for building large apps , the following slides are a brief summary of the importance and use of packages 53

PL/SQL packages A package is a collection of PL/SQL objects Large-scale database applications should be developed as (one or more) PL/SQL package(s) Objects in a package are grouped within BEGIN END blocks Allowable objects in a package: - cursor - scalar variable - composite variable - constant - exception names - TYPE declarations (but cannot reference a package is a remote DB) - procedure - function Each package must have a 1) specification and 2) body, aka a signature, and an implementation Package objects are public when appearing in the specification, and package objects are private if appearing only in the package body Object access can be just to the specification (allowing use/calls), but can hide the implementation code from users/callers in the body. Oracle implements many packages, and. Interestingly, the package STANDARD implements the PL/SQL environment And, most importantly, for modular programming, a package has its own namespace. 54

Package specification CREATE [OR REPLACE] PACKAGE Package. Name IS [ constant, variable, and type declarations ] [ exception declarations ] [ cursor specifications ] [ function specifications ] [procedure specifications ] END [ Package. Name ]; Example: PACKAGE bb_team IS total_players CONSTANT INTEGER : = 12; player_on_dl EXCEPTION; FUNCTION team_average (points IN NUMBER, players IN NUMBER) RETURN NUMBER; END bb_team; 55

Package body The body contains the code that implements the items described in the specification. In addition, there can be private/hidden elements (not visible outside of the package body), not in the specification, that must be implemented in the body. PACKAGE BODY Package. Name IS [ variable and type declarations ] [ cursor specifications and SELECT queries ] [ specification and body of functions ] [ specification and body of procedures ] [ BEGIN executable statements ] [ EXCEPTION exception handlers ] END [ Package. Name ]; Within a package body, you do not need to use dot notation to reference an object. However, you must use the dot notation to reference an object in another package. For example: IF bb_team. total_players < 10 THEN EXCEPTION WHEN bb_team. player_on_dl THEN 56

Package body Rules for writing package body - Any item declared in the specification must not be declared again - The number of cursor and module definitions in the specification must match the number of cursor and module headers in the body - Any element declared in the specification can be referenced in the body - Body elements can be defined in any order - If a cursor or a procedure appears in the specification, it must also be implemented in the body - An optional “initialization block” in the form of an anonymous block can appear after cursor and procedure implementations. Such a block can define variable and constant values when a package is first instantiated in a session 57

Package use There are several very important reasons why packages should be used instead of a large group of stand-alone procedures & functions - Reduce or remove effects of cascading object STATUS invalidations *** (for example, a table change using alter table could cause recompilation of many other modules when not using a package) - More namespace repetition - instead of just one proc name “p”, you can have many “p” spread over many packages; package my_pkg’s proc p would be referenced as my_pkg. p - One package can contain many overloads of the same procedure, and package body details can be hidden within a package Package proc/fcn calls should be made from a trigger body as much as possible + simplifies the trigger’s code body + Although commit; and rollback; cannot be executed within a trigger, an variable’s value can be used to indicate that a tr x that activated the trigger should be committed or rolled back 58

Passing SQL statements from 3 GL to SQL for execution “STATIC SQL” AND “DYNAMIC SQL” These are two (SQL standard specified) ways that a 3 GL can specify a SQL statement for execution in SQL That is, STATIC (compiled code) and DYNAMIC (code not completely determined until runtime) are two ways that 3 GL code can pass a SQL statement to the DB for execution *** Have full demo of difference in Later Module – it will combine SQL statement passing in 3 GL to SQL with some SQL security topics 59