Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. —Samuel Johnson Show
This chapter shows how PL/SQL supports the SQL commands, functions, and operators that let you manipulate Oracle data. This chapter contains these topics:
Overview of SQL Support in PL/SQLBy extending SQL, PL/SQL offers a unique combination of power and ease of use. You can manipulate Oracle data flexibly and safely because PL/SQL fully supports all SQL data manipulation statements (except Data ManipulationTo manipulate Oracle data, you use the Transaction ControlOracle is transaction oriented; that is, Oracle uses transactions to ensure data integrity. A transaction is a series of SQL data manipulation statements that does a logical unit of work. For example, two At the end of a transaction that makes database changes, Oracle makes all the changes permanent or undoes them all. If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction, restoring the database to its former state. You use the SQL FunctionsFor example, the following example shows some queries that call SQL functions: DECLARE job_count NUMBER; emp_count NUMBER; BEGIN SELECT COUNT(DISTINCT job_id) INTO job_count FROM employees; SELECT COUNT(*) INTO emp_count FROM employees; END; / SQL PseudocolumnsPL/SQL recognizes the SQL pseudocolumns: CURRVAL and NEXTVAL
Before you can reference sequence_name.CURRVAL sequence_name.NEXTVAL After creating a sequence, you can use it to generate unique sequence numbers for transaction
processing. You can use CREATE TABLE employees_temp AS SELECT employee_id, first_name FROM employees; CREATE TABLE employees_temp2 AS SELECT employee_id, first_name FROM employees; DECLARE next_value NUMBER; BEGIN -- The NEXTVAL value is the same no matter what table you select from. SELECT employees_seq.NEXTVAL INTO next_value FROM dual; -- You usually use NEXTVAL to create unique numbers when inserting data. INSERT INTO employees_temp VALUES (employees_seq.NEXTVAL, 'value 1'); -- If you need to store the same value somewhere else, you use CURRVAL. INSERT INTO employees_temp2 VALUES (employees_seq.CURRVAL, 'value 1'); -- Because NEXTVAL values might be referenced by different users and -- applications, and some NEXTVAL values might not be stored in the -- database, there might be gaps in the sequence. END; / DROP TABLE employees_temp; DROP TABLE employees_temp2; Each time you reference the LEVEL You use
In the ROWID
When you select or fetch a physical rowid into a ROWNUM
You can use CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE CURSOR c1 IS SELECT employee_id, salary FROM employees_temp WHERE salary > 2000 AND ROWNUM <= 10; -- 10 arbitrary rows CURSOR c2 IS SELECT * FROM (SELECT employee_id, salary FROM employees_temp WHERE salary > 2000 ORDER BY salary DESC) WHERE ROWNUM < 5; -- first 5 rows, in sorted order BEGIN -- Each row gets assigned a different number UPDATE employees_temp SET employee_id = ROWNUM; END; / DROP TABLE employees_temp; The value of ... WHERE ROWNUM < constant; ... WHERE ROWNUM <= constant; SQL OperatorsPL/SQL lets you use all the SQL comparison, set, and row operators in SQL statements. This section briefly describes some of these operators. For more information, see Oracle Database SQL Reference. Comparison Operators Typically, you use comparison operators in the
Set Operators Set operators combine the results of two queries into one result. Row Operators Row operators return or reference particular rows. Performing DML Operations from PL/SQL (INSERT, UPDATE, and DELETE)You can write CREATE table1 AS SELECT object_name, object_type FROM user_objects; BEGIN INSERT INTO table1(col1, col2) VALUES('value1','value2'); UPDATE table1 SET col1 = 'another value' WHERE col2 IS NULL; DELETE FROM table1 WHERE col1 = col2; COMMIT; END; / DROP table1; To
find out how many rows are affected by these statements, you can check the value of SET SERVEROUTPUT ON; BEGIN UPDATE employees SET salary = salary * 1.05 WHERE ...; dbms_output.put_line('Updated ' || SQL%ROWCOUNT || ' salaries.'); END; / Wherever you would use literal values, or bind variables in some other programming language, you can directly substitute PL/SQL variables: CREATE table1 AS SELECT object_name, object_type FROM user_objects; DECLARE x VARCHAR2(128) := 'value1'; y NUMBER := 10; BEGIN INSERT INTO table1(col1, col2) VALUES(x, x); UPDATE table1 SET col1 = x WHERE col3 < y; DELETE FROM table1 WHERE col1 = x; COMMIT; END; / DROP table1; With this notation, you can use variables in place of values in the Overview of Implicit Cursor AttributesImplicit cursor attributes return information about the execution of an Note: The %FOUND Attribute: Has a DML Statement Changed Rows?Until a SQL data manipulation statement is executed, DELETE FROM emp WHERE empno = my_empno; IF SQL%FOUND THEN -- delete succeeded INSERT INTO new_emp VALUES (my_empno, my_ename, ...); %ISOPEN Attribute: Always FALSE for Implicit CursorsOracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %NOTFOUND Attribute: Has a DML Statement Failed to Change Rows?
%ROWCOUNT Attribute: How Many Rows Affected So Far?
DELETE FROM emp WHERE ... IF SQL%ROWCOUNT > 10 THEN -- more than 10 rows were deleted ... END IF; If a Guidelines for Using Implicit Cursor AttributesThe values of the cursor attributes always refer to the most recently executed SQL statement, wherever that statement is. It might be in a different scope
(for example, in a sub-block). To save an attribute value for later use, assign it to a Boolean variable immediately. Doing other operations, such as procedure calls, might change the value of The If a A Using PL/SQL Records in SQL INSERT and UPDATE StatementsInstead of listing each field of a PL/SQL record in DECLARE emp_rec emp%ROWTYPE; BEGIN emp_rec.eno := 1500; emp_rec.ename := 'Steven Hill'; emp_rec.sal := '40000'; -- A %ROWTYPE value can fill in all the row fields. INSERT INTO emp VALUES emp_rec; -- The fields of a %ROWTYPE can completely replace the table columns. UPDATE emp SET ROW = emp_rec WHERE eno = 100; END; / Although this technique integrates PL/SQL variables and types with SQL DML statements, you cannot use PL/SQL records as bind variables in dynamic SQL statements. Issuing Queries from PL/SQLPL/SQL lets you perform queries ( Selecting At Most One Row: SELECT INTO StatementIf you expect a query to only return one row, you can write a regular SQL If the query might return more than one row, but you do not care about values after the first, you can restrict any result set to a single row by comparing the If the query might return no rows at all, use an exception handler to specify any actions to take when no data is found: If you just want to check whether a condition exists in your data, you might be able to code the query with the Selecting Multiple Rows: BULK COLLECT ClauseIf you need to bring a large quantity of data into local PL/SQL variables, rather than looping through a result set one row at a time, you can use the SELECT employee_id, last_name, salary FROM employees BULK COLLECT INTO all_employee_ids, all_last_names, all_salaries; When you query all the columns of a table, you can store the entire result set in a collection of records, which makes it convenient to loop through the results and refer to different columns: SELECT * FROM employees BULK COLLECT INTO all_employees; FOR i IN all_employees.FIRST .. all_employees.LAST LOOP ... END LOOP; This technique can be very fast, but also very memory-intensive. If you use it often, you might be able to improve your code by doing more of the work in SQL:
Looping Through Multiple Rows: Cursor FOR LoopPerhaps the most common
case of a query is one where you issue the The iterator variable for the Performing Complicated Query Processing: Explicit CursorsFor full control over query processing, you can use explicit cursors in combination with the You might want to specify a query in one place but retrieve the rows somewhere else, even in another subprogram. Or you might want to choose very different
query parameters, such as Because explicit cursors are so flexible, you can choose from different notations depending on your needs. The following sections describe all the query-processing features that explicit cursors provide. Querying Data with PL/SQLIn traditional database programming, you process query results using an internal data structure called a cursor. In most situations, PL/SQL can manage the cursor for you, so that code to process query results is straightforward and compact. This section discusses how to process both simple queries where PL/SQL manages everything, and complex queries where you interact with the cursor. Querying Data with PL/SQL: Implicit Cursor FOR LoopWith PL/SQL, it is very simple to issue a query, retrieve each row of the result into a
Here is an example that you can run in SQL*Plus. It does a query to get the name and status of every index that you can access. BEGIN FOR item IN ( SELECT object_name, status FROM user_objects WHERE object_type = 'INDEX' AND object_name NOT LIKE '%$%' ) LOOP dbms_output.put_line('Index = ' || item.object_name || ', Status = ' || item.status); END LOOP; END; / Before each iteration of the The sequence of statements inside the loop is executed once for each row that satisfies
the query. When you leave the loop, the cursor is closed automatically. The cursor is closed even if you use an See also: LOOP Statements Querying Data with PL/SQL: Explicit Cursor FOR LoopsIIf you need to reference the same query from different parts of the same procedure, you can declare a cursor that specifies the query, and process the results using a FOR loop. The following PL/SQ block runs two variations of the same query, first finding all the tables you can access, then all the indexes you can access: DECLARE CURSOR c1 IS SELECT object_name, status FROM user_objects WHERE object_type = 'TABLE' AND object_name NOT LIKE '%$%'; BEGIN FOR item IN c1 LOOP dbms_output.put_line('Table = ' || item.object_name || ', Status = ' || item.status); END LOOP; END; / See also: LOOP Statements Defining Aliases for Expression Values in a Cursor FOR LoopIn a cursor FOR loop, PL/SQL creates a The select list might contain an expression, such as a column plus a constant, or two columns concatenated together. If so, use a column alias to give unique names to the appropriate columns. In the following example, SET SERVEROUTPUT ON; BEGIN FOR item IN ( SELECT first_name || ' ' || last_name AS full_name, salary * 10 AS dream_salary FROM employees WHERE ROWNUM <= 5 ) LOOP dbms_output.put_line(item.full_name || ' dreams of making ' || item.dream_salary); END LOOP; END; / Overview of Explicit CursorsWhen you need precise control over query processing, you can explicitly declare a cursor in the declarative part of any PL/SQL block, subprogram, or package. You use three commands to control a cursor: This technique requires more code than other techniques such as the implicit cursor FOR loop. Its advantage is flexibility. You can:
Declaring a CursorYou must declare a cursor before referencing it in other statements. You give the cursor a name and associate it with a specific query. You can optionally declare a return type for the cursor (such as For example, you might declare cursors like these: DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; CURSOR c2 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10; The cursor is not a PL/SQL variable: you cannot assign values to a cursor or use it in an expression. Cursors and variables follow the same scoping rules. Naming cursors after database tables is possible but not recommended. A cursor can take parameters, which can appear in the associated query wherever constants can appear. The formal parameters of a cursor must be As the example below shows, you can initialize cursor parameters to default values. You can pass different numbers of actual parameters to a cursor, accepting or overriding the default values as you please. Also, you can add new formal parameters without having to change existing references to the cursor. DECLARE CURSOR c1 (low INTEGER DEFAULT 0, high INTEGER DEFAULT 99) IS SELECT ... Cursor parameters can be referenced only within the query specified in the cursor declaration. The parameter values are used by the associated query when the cursor is opened. Opening a CursorOpening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria. For cursors declared using the DECLARE CURSOR c1 IS SELECT ename, job FROM emp WHERE sal < 3000; ... BEGIN OPEN c1; ... END; Rows in the result set are retrieved by the Fetching with a CursorUnless you use the You can store each column in a separate variable, or store the entire row in a record that has the appropriate fields (usually declared using -- This cursor queries 3 columns. -- Each column is fetched into a separate variable. FETCH c1 INTO my_empno, my_ename, my_deptno; -- This cursor was declared as SELECT * FROM employees. -- An entire row is fetched into the my_employees record, which -- is declared with the type employees%ROWTYPE. FETCH c2 INTO my_employees; For each column value returned by the query associated with the cursor, there must be a corresponding,
type-compatible variable in the LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; -- process data record END LOOP; The query can reference PL/SQL variables within its scope. Any variables in the query are evaluated only when the cursor is opened. In the following example, each retrieved salary is multiplied by DECLARE my_sal employees.salary%TYPE; my_job employees.job_id%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT factor*salary FROM employees WHERE job_id = my_job; BEGIN OPEN c1; -- here factor equals 2 LOOP FETCH c1 INTO my_sal; EXIT WHEN c1%NOTFOUND; factor := factor + 1; -- does not affect FETCH END LOOP; END; / To change the result set or the values of variables in the query, you must close and reopen the cursor with the input variables set to their new values. However, you can use a different DECLARE CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name; name1 employees.last_name%TYPE; name2 employees.last_name%TYPE; name3 employees.last_name%TYPE; BEGIN OPEN c1; FETCH c1 INTO name1; -- this fetches first row FETCH c1 INTO name2; -- this fetches second row FETCH c1 INTO name3; -- this fetches third row CLOSE c1; END; / If you fetch past the last row in the result set, the values of the target variables are undefined. Note: Eventually, the Fetching Bulk Data with a CursorThe DECLARE TYPE NumTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employees.last_name%TYPE; nums NumTab; names NameTab; CURSOR c1 IS SELECT employee_id, last_name FROM employees WHERE job_id = 'ST_CLERK'; BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO nums, names; -- Here is where you iterate through the elements in the NUMS and -- NAMES collections. NULL; CLOSE c1; END; / Closing a CursorThe Using SubqueriesA subquery is a query (usually enclosed by parentheses) that appears within another SQL data manipulation statement. The statement acts upon the single value or set of values returned by the subquery. For example:
DECLARE CURSOR c1 IS -- The main query returns only rows where the salary is greater than the average salary. SELECT employee_id, last_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); CURSOR c2 IS -- The subquery returns all the rows in descending order of salary. -- The main query returns just the top 10 highest-paid employees. SELECT * FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC, last_name) WHERE ROWNUM < 11; BEGIN FOR person IN c1 LOOP dbms_output.put_line('Above-average salary: ' || person.last_name); END LOOP; FOR person IN c2 LOOP dbms_output.put_line('Highest paid: ' || person.last_name || ' $' || person.salary); END LOOP; -- The subquery identifies a set of rows to use with CREATE TABLE or INSERT. EXECUTE IMMEDIATE 'CREATE TABLE temp AS (SELECT * FROM employees WHERE salary > 5000)'; EXECUTE IMMEDIATE 'DROP TABLE temp'; END; / Using a subquery in the DECLARE CURSOR c1 IS SELECT t1.department_id, department_name, staff FROM departments t1, ( SELECT department_id, COUNT(*) as staff FROM employees GROUP BY department_id ) t2 WHERE t1.department_id = t2.department_id AND staff >= 5; BEGIN FOR dept IN c1 LOOP dbms_output.put_line('Department = ' || dept.department_name || ', staff = ' || dept.staff); END LOOP; END; / Using Correlated SubqueriesWhile a subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row. The following example returns the name and salary of each employee whose salary exceeds the departmental average. For each row in the table, the correlated subquery computes the average salary for the corresponding epartment. DECLARE -- For each department, we find the average salary. -- Then we find all the employees in that department making -- more than that average salary. CURSOR c1 IS SELECT department_id, last_name, salary FROM employees t WHERE salary > ( SELECT AVG(salary) FROM employees WHERE t.department_id = department_id ) ORDER BY department_id; BEGIN FOR person IN c1 LOOP dbms_output.put_line('Making above-average salary = ' || person.last_name); END LOOP; END; / Writing Maintainable PL/SQL QueriesInstead of referring to local variables, you can declare a cursor that accepts parameters, and pass values for those parameters when you open the cursor. If the query is usually issued with certain values, you can make those values the defaults. You can use either positional notation or named notation to pass the parameter values. Example 6-1 Passing Parameters to a Cursor FOR Loop The following example computes the total wages paid to employees in a specified department. DECLARE CURSOR c1 (name VARCHAR2, max_wage NUMBER) IS SELECT * FROM employees WHERE last_name = name and salary < max_wage; BEGIN FOR person IN c1('Austin', 30000) LOOP -- process data record dbms_output.put_line('Name = ' || person.last_name || ', salary = ' || person.salary); END LOOP; END; / Example 6-2 Passing Parameters to Explicit Cursors For example, here are several ways to open a cursor: DECLARE emp_name employees.last_name%TYPE := 'Austin'; emp_salary employees.salary%TYPE := 30000; my_record employees%ROWTYPE; CURSOR c1 (name VARCHAR2, max_wage NUMBER) IS SELECT * FROM employees WHERE last_name = name and salary < max_wage; BEGIN -- Any of the following statements opens the cursor: -- OPEN c1('Austin', 3000); -- OPEN c1('Austin', emp_salary); -- OPEN c1(emp_name, 3000); -- OPEN c1(emp_name, emp_salary); OPEN c1(emp_name, emp_salary); LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; -- process data record dbms_output.put_line('Name = ' || my_record.last_name || ', salary = ' || my_record.salary); END LOOP; END; / To avoid confusion, use different names for cursor parameters and the PL/SQL variables that you pass into those parameters. Formal parameters declared with a default value do not need a corresponding actual parameter. If you omit them, they assume their default values when the Using Cursor AttributesEvery explicit cursor and cursor variable has four
attributes: Overview of Explicit Cursor AttributesExplicit cursor attributes return information about the execution of a multi-row query. When an explicit cursor or a cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set. %FOUND Attribute: Has a Row Been Fetched?After a cursor or cursor variable is opened but before the first fetch, DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%FOUND THEN -- fetch succeeded dbms_output.put_line('Name = ' || my_ename || ', salary = ' || my_salary); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END; / If a cursor or cursor variable is not open, referencing it with %ISOPEN Attribute: Is the Cursor Open?
DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; the_name employees.last_name%TYPE; the_salary employees.salary%TYPE; BEGIN IF c1%ISOPEN = FALSE THEN -- cursor was not already open OPEN c1; END IF; FETCH c1 INTO the_name, the_salary; CLOSE c1; END; / %NOTFOUND Attribute: Has a Fetch Failed?
DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%NOTFOUND THEN -- fetch failed, so exit loop -- A shorter form of this test is "EXIT WHEN c1%NOTFOUND;" EXIT; ELSE -- fetch succeeded dbms_output.put_line('Name = ' || my_ename || ', salary = ' || my_salary); END IF; END LOOP; END; / Before the first fetch, EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; If a cursor or cursor variable is not open, referencing it with %ROWCOUNT Attribute: How Many Rows Fetched So Far?When its cursor or cursor variable is opened, DECLARE CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11; name employees.last_name%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO name; EXIT WHEN c1%NOTFOUND; dbms_output.put_line(c1%ROWCOUNT || '. ' || name); IF c1%ROWCOUNT = 5 THEN dbms_output.put_line('--- Fetched 5th record ---'); END IF; END LOOP; CLOSE c1; END; / If a cursor or cursor variable is not open, referencing
it with Table 6-1 shows what each cursor attribute returns before and after you execute an Table 6-1 Cursor Attribute Values
Using Cursor Variables (REF CURSORs)Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. A cursor variable is more flexible because it is not tied to a specific query. You can open a cursor variable for any query that returns the right set of columns. You pass a cursor variable as a parameter to local and stored subprograms. Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval. This technique is also useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language. Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote procedure calls. What Are Cursor Variables (REF CURSORs)?Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa. Why Use Cursor Variables?You use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. PL/SQL and its clients share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle database server can all refer to the same work area. A query work area remains accessible as long as any cursor variable points to it, as you pass the value of a cursor variable from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes. If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. You can also reduce network traffic by having a PL/SQL block open or close several host cursor variables in a single round trip. Declaring REF CURSOR Types and Cursor VariablesTo create cursor variables, you define a DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;
Strong Because
there is no type checking with a weak Once you define a DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong TYPE GenericCurTyp IS REF CURSOR; -- weak cursor1 EmpCurTyp; cursor2 GenericCurTyp; my_cursor SYS_REFCURSOR; -- didn't need to declare a new type above The following example declares the cursor variable DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; dept_cv DeptCurTyp; -- declare cursor variable To avoid declaring the same Example 6-3 Cursor Variable Returning %ROWTYPE In the DECLARE TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE; tmp_cv TmpCurTyp; -- declare cursor variable TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE; emp_cv EmpCurTyp; -- declare cursor variable BEGIN NULL; END; /
Example 6-4 Cursor Variable Returning %TYPE You can also use DECLARE dept_rec departments%ROWTYPE; -- declare record variable TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE; dept_cv DeptCurTyp; -- declare cursor variable BEGIN NULL; END; / Example 6-5 Cursor Variable Returning Record Type This example specifies a user-defined DECLARE TYPE EmpRecTyp IS RECORD ( employee_id NUMBER, last_name VARCHAR2(30), salary NUMBER(7,2)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; emp_cv EmpCurTyp; -- declare cursor variable BEGIN NULL; END; / Passing Cursor Variables As ParametersYou can declare cursor variables as the formal parameters of functions and procedures. The following example defines a DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE; emp EmpCurTyp; -- Once we have a result set, we can process all the rows -- inside a single procedure rather than calling a procedure -- for each row. PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS person employees%ROWTYPE; BEGIN dbms_output.put_line('-----'); dbms_output.put_line('Here are the names from the result set:'); LOOP FETCH emp_cv INTO person; EXIT WHEN emp_cv%NOTFOUND; dbms_output.put_line('Name = ' || person.first_name || ' ' || person.last_name); END LOOP; END; BEGIN -- First find 10 arbitrary employees. OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11; process_emp_cv(emp); CLOSE emp; -- Then find employees matching a condition. OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%'; process_emp_cv(emp); CLOSE emp; END; / Note: Like all pointers, cursor variables increase the possibility of parameter aliasing. See "Overloading Subprogram Names". Controlling Cursor Variables: OPEN-FOR, FETCH, and CLOSEYou use three statements to control a cursor variable: Opening a Cursor VariableThe OPEN {cursor_variable | :host_cursor_variable} FOR { select_statement | dynamic_string [USING bind_argument[, bind_argument]...] }; The cursor variable can be declared directly in PL/SQL, or in a PL/SQL host environment such as an OCI program. The Note: This section discusses the static SQL case, in which Unlike cursors, cursor variables take no parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. The query can reference host variables and PL/SQL variables, parameters, and functions. The example below opens a cursor variable. Notice that you can apply cursor attributes ( DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE; emp_cv EmpCurTyp; BEGIN IF NOT emp_cv%ISOPEN THEN /* Open cursor variable. */ OPEN emp_cv FOR SELECT * FROM employees; END IF; CLOSE emp_cv; END; / Other Example 6-6 Stored Procedure to Open a Ref Cursor Typically, you open a cursor variable by passing it to a stored procedure that declares an IN OUT parameter that is a cursor variable. For example, the following procedure opens a cursor variable: CREATE PACKAGE emp_data AS TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp); END emp_data; / CREATE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS BEGIN OPEN emp_cv FOR SELECT * FROM employees; END open_emp_cv; END emp_data; / DROP PACKAGE emp_data; You can also use a standalone stored procedure to open the cursor variable. Define the Example 6-7 Stored Procedure to Open Ref Cursors with Different Queries To centralize data retrieval, you can group type-compatible queries in a stored procedure. In the example below, the packaged procedure declares a selector as one of its formal parameters. When called, the procedure opens the cursor variable CREATE PACKAGE emp_data AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT); END emp_data; CREATE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) IS BEGIN IF choice = 1 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL; ELSIF choice = 2 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500; ELSIF choice = 3 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20; END IF; END; END emp_data; Example 6-8 Cursor Variable with Different Return Types For more flexibility, a stored procedure can execute queries with different return types: CREATE PACKAGE admin_data AS TYPE GenCurTyp IS REF CURSOR; PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT); END admin_data; CREATE PACKAGE BODY admin_data AS PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT) IS BEGIN IF choice = 1 THEN OPEN generic_cv FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN generic_cv FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN generic_cv FOR SELECT * FROM salgrade; END IF; END; END admin_data; Using a Cursor Variable as a Host VariableYou can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To use the cursor variable, you must pass it as a host variable to PL/SQL. In the following Pro*C example, you pass a host cursor variable and selector to a PL/SQL block, which opens the cursor variable for the chosen query: EXEC SQL BEGIN DECLARE SECTION; ... /* Declare host cursor variable. */ SQL_CURSOR generic_cv; int choice; EXEC SQL END DECLARE SECTION; ... /* Initialize host cursor variable. */ EXEC SQL ALLOCATE :generic_cv; ... /* Pass host cursor variable and selector to PL/SQL block. */ EXEC SQL EXECUTE BEGIN IF :choice = 1 THEN OPEN :generic_cv FOR SELECT * FROM emp; ELSIF :choice = 2 THEN OPEN :generic_cv FOR SELECT * FROM dept; ELSIF :choice = 3 THEN OPEN :generic_cv FOR SELECT * FROM salgrade; END IF; END; END-EXEC; Host cursor variables are compatible with any query return type. They behave just like weakly typed PL/SQL cursor variables. Fetching from a Cursor VariableThe Example 6-9 Fetching from a Cursor Variable into a Record The following example fetches rows one at a time from a cursor variable into a record: DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE; emp_cv EmpCurTyp; emp_rec employees%ROWTYPE; BEGIN OPEN emp_cv FOR SELECT * FROM employees WHERE salary < 3000; LOOP /* Fetch from cursor variable. */ FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched -- process data record dbms_output.put_line('Name = ' || emp_rec.first_name || ' ' || emp_rec.last_name); END LOOP; CLOSE emp_cv; END; / Example 6-10 Fetching from a Cursor Variable into Collections Using the DECLARE TYPE EmpCurTyp IS REF CURSOR; TYPE NameList IS TABLE OF employees.last_name%TYPE; TYPE SalList IS TABLE OF employees.salary%TYPE; emp_cv EmpCurTyp; names NameList; sals SalList; BEGIN OPEN emp_cv FOR SELECT last_name, salary FROM employees WHERE salary < 3000; FETCH emp_cv BULK COLLECT INTO names, sals; CLOSE emp_cv; -- Now loop through the NAMES and SALS collections. FOR i IN names.FIRST .. names.LAST LOOP dbms_output.put_line('Name = ' || names(i) || ', salary = ' || sals(i)); END LOOP; END; / Any variables in the associated query are evaluated only when the cursor variable is opened. To change the result set or the values of variables in the query, reopen the
cursor variable with the variables set to new values. You can use a different PL/SQL makes sure the return type of the cursor variable is compatible with the When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the If you try to fetch from a closed or
never-opened cursor variable, PL/SQL raises the predefined exception Closing a Cursor VariableThe When declaring a
cursor variable as the formal parameter of a subprogram that closes the cursor variable, you must specify the If you try to close an already-closed or never-opened cursor variable, PL/SQL raises the predefined exception Reducing Network Traffic When Passing Host Cursor Variables to PL/SQLWhen passing host cursor variables to PL/SQL, you can reduce
network traffic by grouping /* anonymous PL/SQL block in host environment */ BEGIN OPEN :emp_cv FOR SELECT * FROM employees; OPEN :dept_cv FOR SELECT * FROM departments; OPEN :loc_cv FOR SELECT * FROM locations; END; This technique might be useful in Oracle Forms, for instance, when you want to populate a multi-block form. When you pass host cursor variables to a PL/SQL block for opening, the query work areas to which they point remain accessible after the block completes, so your OCI or Pro*C program can use these work areas for ordinary cursor operations. In the following example, you open several such work areas in a single round trip: BEGIN OPEN :c1 FOR SELECT 1 FROM dual; OPEN :c2 FOR SELECT 1 FROM dual; OPEN :c3 FOR SELECT 1 FROM dual; END; The cursors assigned to BEGIN CLOSE :c1; CLOSE :c2; CLOSE :c3; END; Avoiding Errors with Cursor VariablesIf both cursor variables involved in an assignment are strongly typed, they must have exactly the same datatype (not just the same return type). If one or both cursor variables are weakly typed, they can have different datatypes. If you try to fetch from, close, or refer to cursor attributes of a cursor variable that does not point to a query work area, PL/SQL raises the
If you assign an unopened cursor variable to another cursor variable, the second one remains invalid even after you open the first one. Be careful when passing cursor variables as parameters. At run time, PL/SQL raises Restrictions on Cursor VariablesCurrently, cursor variables are subject to the following restrictions:
Using Cursor ExpressionsA cursor expression returns a nested cursor. Each row in the result set can contain values as usual, plus cursors produced by subqueries involving the other values in the row. A single query can return a large set of related values retrieved from multiple tables. You can process the result set with nested loops that fetch first from the rows of the result set, then from any nested cursors within those rows. PL/SQL supports queries with cursor expressions as part of cursor declarations, CURSOR(subquery) A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when:
Restrictions on Cursor Expressions
Example of Cursor ExpressionsIn this example, we find a specified location ID, and a cursor from which we can fetch all the departments in that location. As we fetch each department's name, we also get another cursor that lets us fetch their associated employee details from another table. DECLARE TYPE emp_cur_typ IS REF CURSOR; emp_cur emp_cur_typ; dept_name departments.department_name%TYPE; emp_name employees.last_name%TYPE; CURSOR c1 IS SELECT department_name, -- The 2nd item in the result set is another result set, -- which is represented as a ref cursor and labelled "employees". CURSOR ( SELECT e.last_name FROM employees e WHERE e.department_id = d.department_id ) employees FROM departments d WHERE department_name like 'A%'; BEGIN OPEN c1; LOOP FETCH c1 INTO dept_name, emp_cur; EXIT WHEN c1%NOTFOUND; dbms_output.put_line('Department: ' || dept_name); -- For each row in the result set, we can process the result -- set from a subquery. We could pass the ref cursor to a procedure -- instead of processing it here in the loop. LOOP FETCH emp_cur INTO emp_name; EXIT WHEN emp_cur%NOTFOUND; dbms_output.put_line(' Employee: ' || emp_name); END LOOP; END LOOP; CLOSE c1; END; / Constructing REF CURSORs with Cursor SubqueriesYou can use cursor subqueries, also know as cursor expressions, to pass sets of rows as parameters to functions. For example, this statement passes a parameter to the StockPivot function consisting of a SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable))); Cursor subqueries are often used with table functions, which are explained in "Setting Up Transformation Pipelines with Table Functions ". Overview of Transaction Processing in PL/SQLThis section explains how to do transaction processing with PL/SQL. You should already be familiar with the idea of transactions, and how to ensure the consistency of a database, such as the You usually do not need to write extra code to prevent problems with multiple users accessing data concurrently. Oracle uses locks to control concurrent access to data, and locks only the minimum amount of data necessary, for as little time as possible. You can request locks on tables or rows if you really do need this level of control. You can choose from several modes of locking such as row share and exclusive. Using COMMIT, SAVEPOINT, and ROLLBACK in PL/SQLYou can include The The
Consider a transaction that transfers money from one bank account to another. It is important that the money come out of one account, and into the other, at exactly the same moment. Otherwise, a problem partway through might make the money be lost from both accounts or be duplicated in both accounts. BEGIN UPDATE accts SET bal = my_bal - debit WHERE acctno = 7715; UPDATE accts SET bal = my_bal + credit WHERE acctno = 7720; COMMIT WORK; END; Transactions are not tied to PL/SQL The optional COMMIT COMMENT 'In-doubt order transaction; notify Order Entry'; PL/SQL does not support the The following example inserts information about an employee into three different database tables. If an DECLARE emp_id INTEGER; BEGIN SELECT empno, ... INTO emp_id, ... FROM new_emp WHERE ... INSERT INTO emp VALUES (emp_id, ...); INSERT INTO tax VALUES (emp_id, ...); INSERT INTO pay VALUES (emp_id, ...); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK; END; Statement-Level RollbacksBefore executing a SQL statement, Oracle marks an implicit savepoint. Then, if the statement fails, Oracle rolls it back automatically. For example, if an Oracle can also roll back single SQL statements to break deadlocks. Oracle signals an error to one of the participating transactions and rolls back the current statement in that transaction. Before executing a SQL statement, Oracle must parse it, that is, examine it to make sure it follows syntax rules and refers to valid schema objects. Errors detected while executing a SQL statement cause a rollback, but errors detected while parsing the statement do not. The following example marks a savepoint before doing an insert. If the DECLARE emp_id emp.empno%TYPE; BEGIN UPDATE emp SET ... WHERE empno = emp_id; DELETE FROM emp WHERE ... SAVEPOINT do_insert; INSERT INTO emp VALUES (emp_id, ...); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO do_insert; END; When you roll back to a savepoint, any savepoints marked after that savepoint are erased. The savepoint to which you roll back is not erased. A simple rollback or commit erases all savepoints. If you mark a savepoint within a recursive subprogram, new instances of the Savepoint names are undeclared identifiers. Reusing a savepoint name within a transaction moves the savepoint from its old position to the current point in the transaction. Thus, a rollback to the savepoint affects only the current part of your transaction: BEGIN SAVEPOINT my_point; UPDATE emp SET ... WHERE empno = emp_id; SAVEPOINT my_point; -- move my_point to current point INSERT INTO emp VALUES (emp_id, ...); EXCEPTION WHEN OTHERS THEN ROLLBACK TO my_point; END; The number of active savepoints for each session is unlimited. How Oracle Does Implicit RollbacksBefore executing an If you exit a stored subprogram with an
unhandled exception, PL/SQL does not assign values to Ending TransactionsYou should explicitly commit or roll back every transaction. Whether you issue the commit or rollback in your PL/SQL program or from a client program depends on the application logic. If you do not commit or roll back a transaction explicitly, the client environment determines its final state. For example, in the SQL*Plus environment, if your PL/SQL block does not include a Oracle precompiler programs roll back the transaction unless the program explicitly commits or rolls back work, and disconnects using the EXEC SQL COMMIT WORK RELEASE; Setting Transaction Properties with SET TRANSACTIONYou use the During a read-only transaction, all queries refer to the same snapshot of the database, providing a multi-table, multi-query, read-consistent view. Other users can continue to query or update data as usual. A commit or rollback ends the transaction. In the example below a store manager uses a read-only transaction to gather sales figures for the day, the past week, and the past month. The figures are unaffected by other users updating the database during the transaction. DECLARE daily_sales REAL; weekly_sales REAL; monthly_sales REAL; BEGIN COMMIT; -- ends previous transaction SET TRANSACTION READ ONLY NAME 'Calculate sales figures'; SELECT SUM(amt) INTO daily_sales FROM sales WHERE dte = SYSDATE; SELECT SUM(amt) INTO weekly_sales FROM sales WHERE dte > SYSDATE - 7; SELECT SUM(amt) INTO monthly_sales FROM sales WHERE dte > SYSDATE - 30; COMMIT; -- ends read-only transaction END; The Restrictions on SET TRANSACTIONOnly the Overriding Default LockingBy default, Oracle locks data structures for you automatically, which is a major strength of the Oracle database: different applications can read and write to the same data without harming each other's data or coordinating with each other. You can request data locks on specific rows or entire tables if you need to override default locking. Explicit locking lets you deny access to data for the duration of a transaction.:
When you declare a cursor that will be referenced in the DECLARE CURSOR c1 IS SELECT empno, sal FROM emp WHERE job = 'SALESMAN' AND comm > sal FOR UPDATE NOWAIT; The
The optional keyword All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. Since the rows are no longer locked, you cannot fetch from a When querying multiple tables, you can use the DECLARE CURSOR c1 IS SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'MANAGER' FOR UPDATE OF sal; As the next example shows, you use the DECLARE CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE; BEGIN OPEN c1; LOOP FETCH c1 INTO ... UPDATE emp SET sal = new_sal WHERE CURRENT OF c1; END LOOP;
You use the LOCK TABLE emp IN ROW SHARE MODE NOWAIT; The lock mode determines what other locks can be placed on the table. For example, many users can acquire row share locks on a table at the same time, but only one user at a time can acquire an exclusive lock. While one user has an exclusive lock on a table, no other users can insert, delete, or update rows in that table. For more information about lock modes, see Oracle Database Application Developer's Guide - Fundamentals. A table lock never keeps other users from querying a table, and a query never acquires a table lock. Only if two different transactions try to modify the same row will one transaction wait for the other to complete.
PL/SQL raises an exception if you try to fetch from a DECLARE CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal; BEGIN FOR emp_rec IN c1 LOOP -- FETCH fails on the second iteration INSERT INTO temp VALUES ('still going'); COMMIT; -- releases locks END LOOP; END; If you want to fetch across commits, use the DECLARE CURSOR c1 IS SELECT ename, job, rowid FROM emp; my_ename emp.ename%TYPE; my_job emp.job%TYPE; my_rowid UROWID; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_job, my_rowid; EXIT WHEN c1%NOTFOUND; UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid; -- this mimics WHERE CURRENT OF c1 COMMIT; END LOOP; CLOSE c1; END; Because the fetched rows are not locked by a The next example shows
that you can use the DECLARE CURSOR c1 IS SELECT ename, sal, rowid FROM emp; emp_rec c1%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; IF ... THEN DELETE FROM emp WHERE rowid = emp_rec.rowid; END IF; END LOOP; CLOSE c1; END; Doing Independent Units of Work with Autonomous TransactionsAn autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions do SQL operations and commit or roll back, without committing or rolling back the main transaction. For example, if you write auditing data to a log table, you want to commit the audit data even if the operation you are auditing later fails; if something goes wrong recording the audit data, you do not want the main operation to be rolled back. Figure 6-1 shows how control flows from the main transaction (MT) to an autonomous transaction (AT) and back again. Advantages of Autonomous TransactionsOnce started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back. More important, autonomous transactions help you build modular, reusable software components. You can encapsulate autonomous transactions within stored procedures. A calling application does not need to know whether operations done by that stored procedure succeeded or failed. Defining Autonomous TransactionsTo define autonomous transactions, you use the pragma (compiler
directive)
You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section. The syntax follows: PRAGMA AUTONOMOUS_TRANSACTION; In the following example, you mark a packaged function as autonomous: CREATE PACKAGE banking AS ... FUNCTION balance (acct_id INTEGER) RETURN REAL; END banking; CREATE PACKAGE BODY banking AS ... FUNCTION balance (acct_id INTEGER) RETURN REAL IS PRAGMA AUTONOMOUS_TRANSACTION; my_bal REAL; BEGIN ... END; END banking; Restriction: You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. The next example marks a standalone procedure as autonomous: CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS PRAGMA AUTONOMOUS_TRANSACTION; my_bal REAL; BEGIN ... END; The following example marks a PL/SQL block as autonomous: DECLARE PRAGMA AUTONOMOUS_TRANSACTION; my_empno NUMBER(4); BEGIN ... END; Restriction: You cannot mark a nested PL/SQL block as autonomous. The example below marks a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as CREATE TRIGGER parts_trigger BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES(:new.pnum, :new.pname); COMMIT; -- allowed only in autonomous triggers END; Comparison of Autonomous Transactions and Nested TransactionsAlthough an autonomous transaction is started by another transaction, it is not a nested transaction:
Transaction ContextThe main transaction shares its context with nested routines, but not with autonomous transactions. When one autonomous routine calls another (or itself recursively), the routines share no transaction context. When an autonomous routine calls a non-autonomous routine, the routines share the same transaction context. Transaction VisibilityChanges made by an autonomous transaction
become visible to other transactions when the autonomous transaction commits. These changes become visible to the main transaction when it resumes, if its isolation level is set to If you set the isolation level of the main transaction to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Controlling Autonomous TransactionsThe first SQL statement in an autonomous routine begins a transaction. When one transaction ends, the next SQL statement begins another transaction. All SQL statements executed since the last commit or rollback make up the current transaction. To control autonomous transactions, use the following statements, which apply only to the current (active) transaction:
Note: Transaction properties set in the main transaction apply only to that transaction, not to its autonomous transactions, and vice versa. Entering and Exiting When you enter the executable section of an autonomous routine, the main transaction suspends. When you exit the routine, the main transaction resumes. To exit normally, you must explicitly commit or roll back all autonomous transactions. If the routine (or any routine called by it) has pending transactions, an exception is raised, and the pending transactions are rolled back. Committing and Rolling Back
Using Savepoints The scope of a savepoint is the transaction in which it is defined. Savepoints defined in the main transaction are unrelated to savepoints defined in its autonomous transactions. In fact, the main transaction and an autonomous transaction can use the same savepoint names. You can roll back only to savepoints marked in the current transaction. In an autonomous transaction, you cannot roll back to a savepoint marked in the main transaction. To do so, you must resume the main transaction by exiting the autonomous routine. When in the main transaction, rolling back to a savepoint marked before you started an autonomous transaction does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction. Avoiding Errors with Autonomous Transactions To avoid some common errors, keep the following points in mind:
Using Autonomous TriggersAmong other things, you can use database triggers to log events transparently. Suppose you want to track all inserts into a table, even those that roll back. In the example below, you use a trigger to insert duplicate rows into a shadow table. Because it is autonomous, the trigger can commit changes to the shadow table whether or not you commit changes to the main table. -- create a main table and its shadow table CREATE TABLE parts (pnum NUMBER(4), pname VARCHAR2(15)); CREATE TABLE parts_log (pnum NUMBER(4), pname VARCHAR2(15)); -- create an autonomous trigger that inserts into the -- shadow table before each insert into the main table CREATE TRIGGER parts_trig BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES(:new.pnum, :new.pname); COMMIT; END; -- insert a row into the main table, and then commit the insert INSERT INTO parts VALUES (1040, 'Head Gasket'); COMMIT; -- insert another row, but then roll back the insert INSERT INTO parts VALUES (2075, 'Oil Pan'); ROLLBACK; -- show that only committed inserts add rows to the main table SELECT * FROM parts ORDER BY pnum; PNUM PNAME ------- --------------- 1040 Head Gasket -- show that both committed and rolled-back inserts add rows -- to the shadow table SELECT * FROM parts_log ORDER BY pnum; PNUM PNAME ------- --------------- 1040 Head Gasket 2075 Oil Pan Unlike regular triggers, autonomous triggers can execute DDL statements using native dynamic SQL (discussed in Chapter 7, " Performing SQL Operations with Native Dynamic SQL"). In the following example, trigger CREATE TRIGGER bonus_trig AFTER UPDATE ON bonus DECLARE PRAGMA AUTONOMOUS_TRANSACTION; -- enables trigger to perform DDL BEGIN EXECUTE IMMEDIATE 'DROP TABLE temp_bonus'; END; For more information about database triggers, see Oracle Database Application Developer's Guide - Fundamentals. Calling Autonomous Functions from SQLA function called from SQL statements must obey certain rules meant to control side effects. (See
"Controlling Side Effects of PL/SQL Subprograms".) To check for violations of the rules, you can use the pragma However, by definition, autonomous routines never violate the rules "read no database state" ( -- create the debug table CREATE TABLE debug_output (msg VARCHAR2(200)); -- create the package spec CREATE PACKAGE debugging AS FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS); END debugging; -- create the package body CREATE PACKAGE BODYq debugging AS FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- the following insert does not violate the constraint -- WNDS because this is an autonomous routine INSERT INTO debug_output VALUES (msg); COMMIT; RETURN msg; END; END debugging; -- call the packaged function from a query DECLARE my_empno NUMBER(4); my_ename VARCHAR2(15); BEGIN ... SELECT debugging.log_msg(ename) INTO my_ename FROM emp WHERE empno = my_empno; -- even if you roll back in this scope, the insert -- into 'debug_output' remains committed because -- it is part of an autonomous transaction IF ... THEN ROLLBACK; END IF; END; What were the primary problems faced by the Roanoke Island colonist?Access to food and deadly conflicts with Native Americans were the two main problems the Roanoke Colony faced. There were actually two attempts to colonize Roanoke. The first was in 1584 and the last was in 1587.
Which of the following best describes the reason why we know so little about most Native Americans?Which of the following best describes the reason why we know so little about most Native American tribes? We don't have much written evidence of their history and culture.
Which two nations looked to the authority of the pope to settle territorial disputes in the new world choose 2?Treaty of Tordesillas, (June 7, 1494), agreement between Spain and Portugal aimed at settling conflicts over lands newly discovered or explored by Christopher Columbus and other late 15th-century voyagers.
What to crops helped create the fortunes of many of the first settlers of the Carolinas?Colonial settlers adopted beneficial agricultural practices from Indians along with their crops. And we still rely on these crops today. Corn and tobacco were two of the most important crops for the colonial economy.
|