This chapter provides additional examples to further explore the tight integration of TimesTen SQL in PL/SQL:
Use the SELECT... INTO statement to retrieve exactly one row of data. TimesTen returns an error for any query that returns no rows or multiple rows.
Example 5-1 Using SELECT... INTO to return sum of salaries
This example uses the SELECT...INTO statement to calculate the sum of salaries for all employees in the department where department_id is 60.
Command> DECLARE
> v_sum_sal NUMBER (10,2);
> v_dept_no NUMBER NOT NULL := 60;
> BEGIN
> SELECT SUM(salary) -- aggregate function
> INTO v_sum_sal FROM employees
> WHERE department_id = v_dept_no;
> DBMS_OUTPUT.PUT_LINE ('Sum is ' || v_sum_sal);
> END;
> /
Sum is 28800
PL/SQL procedure successfully completed.
Example 5-2 Using SELECT...INTO to query another user's table
This example provides two users, USER1 and USER2, to show one user employing SELECT...INTO to query another user's table.
The following privileges are assumed:
grant create session to user1; grant create session to user2; grant create table to user1; grant select on user1.test to user2;
USER1:
Command> create table test(name varchar2(20), id number);
Command> insert into test values('posey', 363);
1 row inserted.
USER2:
Command> declare
> targetid number;
> begin
> select id into targetid from user1.test where name='posey';
> dbms_output.put_line('Target ID is ' || targetid);
> end;
> /
Target ID is 363
PL/SQL procedure successfully completed.
Oracle TimesTen In-Memory Database supports the TimesTen DML statements INSERT, UPDATE, DELETE, and MERGE. This section has an example of the INSERT statement.
Example 5-3 Using the INSERT statement in PL/SQL
This example uses the AS SELECT query clause to create table emp_copy, sets AUTOCOMMIT off, creates a sequence to increment employee_id, and uses the INSERT statement in PL/SQL to insert a row of data in table emp_copy.
Command> CREATE TABLE emp_copy AS SELECT * FROM employees;
107 rows inserted.
Command> SET AUTOCOMMIT OFF;
Command> CREATE SEQUENCE emp_copy_seq
> START WITH 207
> INCREMENT BY 1;
Command> BEGIN
> INSERT INTO emp_copy
> (employee_id, first_name, last_name, email, hire_date, job_id,
> salary)
> VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
> 'AD_ASST', 4000);
> END;
> /
PL/SQL procedure successfully completed.
Continuing, the example confirms the row was inserted, then rolls back the transaction.
Command> SELECT * FROM EMP_COPY WHERE first_name = 'Parker'; < 207, Parker, Cores, PCORES, <NULL>, 2008-07-19 21:49:55, AD_ASST, 4000, <NULL>, <NULL>, <NULL> > 1 row found. Command> ROLLBACK; Command> SELECT * FROM emp_copy WHERE first_name = 'Parker'; 0 rows found.
Now INSERT is executed again, then the transaction is rolled back in PL/SQL. Finally, the example verifies that TimesTen did not insert the row.
Command> BEGIN
> INSERT INTO emp_copy
> (employee_id, first_name, last_name, email, hire_date, job_id,
> salary)
> VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
> 'AD_ASST',4000);
> ROLLBACK;
> END;
> /
PL/SQL procedure successfully completed.
Command> SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.
The examples in this section use IN, OUT, and IN OUT parameters, including bind variables (host variables) from outside PL/SQL.
Example 5-4 Using IN and OUT parameters
This example creates a procedure query_emp to retrieve information about an employee, passes the employee_id value 171 to the procedure, and retrieves the name and salary into two OUT parameters.
Command> CREATE OR REPLACE PROCEDURE query_emp
> (p_id IN employees.employee_id%TYPE,
> p_name OUT employees.last_name%TYPE,
> p_salary OUT employees.salary%TYPE) IS
> BEGIN
> SELECT last_name, salary INTO p_name, p_salary
> FROM employees
> WHERE employee_id = p_id;
> END query_emp;
> /
Procedure created.
Command> -- Execute the procedure
> DECLARE
> v_emp_name employees.last_name%TYPE;
> v_emp_sal employees.salary%TYPE;
> BEGIN
> query_emp (171, v_emp_name, v_emp_sal);
> DBMS_OUTPUT.PUT_LINE (v_emp_name || ' earns ' ||
> TO_CHAR (v_emp_sal, '$999,999.00'));
> END;
> /
Smith earns $7,400.00
PL/SQL procedure successfully completed.
Example 5-5 Using bind variables to execute a procedure
This example uses bind variables to execute procedure query_emp from Example 5-4 above. Remember to check that data types are compatible.
Command> VARIABLE b_name VARCHAR2 (25);
Command> VARIABLE b_sal NUMBER;
Command> BEGIN
> query_emp (171, :b_name, :b_sal);
> END;
> /
PL/SQL procedure successfully completed.
Command> PRINT b_name
B_NAME : Smith
Command> PRINT b_sal
B_SAL : 7400
You can use bind variables to pass data between a user application and PL/SQL.
Example 5-6 Using IN OUT parameters and bind variables
Consider a situation where you want to format a phone number. You decide to use an IN OUT parameter to pass the unformatted phone number to a procedure. After the procedure is executed, the IN OUT parameter contains the formatted phone number value. Procedure FORMAT_PHONE in this example accomplishes that, accepting a 10-character string containing digits for a phone number. Bind variable b_phone_no first provides the input value passed to FORMAT_PHONE, then after execution is used as an output value returning the updated string.
Command> CREATE OR REPLACE PROCEDURE format_phone
> (p_phone_no IN OUT VARCHAR2 ) IS
> BEGIN
> p_phone_no := '(' || SUBSTR (p_phone_no,1,3) ||
> ') ' || SUBSTR (p_phone_no,4,3) ||
> '-' || SUBSTR (p_phone_no,7);
> END format_phone;
> /
Procedure created.
Create the bind variable, execute the procedure, and verify the results.
Command> VARIABLE b_phone_no VARCHAR2 (15);
Command> EXECUTE :b_phone_no := '8006330575';
PL/SQL procedure successfully completed.
Command> PRINT b_phone_no;
B_PHONE_NO : 8006330575
Command> BEGIN
> format_phone (:b_phone_no);
> END;
> /
PL/SQL procedure successfully completed.
Command> PRINT b_phone_no
B_PHONE_NO : (800) 633-0575
Oracle TimesTen In-Memory Database supports cursors, as discussed in "Use of cursors in PL/SQL programs". Use a cursor to handle the result set of a SELECT statement.
Examples in this section cover the following:
See "Explicit Cursor Attributes" in Oracle Database PL/SQL Language Reference for information about the cursor attributes used in these examples.
This section provides examples of how to fetch values from a cursor, including how to fetch the values into a record.
Example 5-7 Fetching values from a cursor
The following example uses a cursor to select employee_id and last_name from the employees table where department_id is 30 Two variables are declared to hold the fetched values from the cursor, and the FETCH statement retrieves rows one at a time in a loop to retrieve all rows. Execution stops when there are no remaining rows in the cursor, illustrating use of the %NOTFOUND cursor attribute.
%NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows.
Command> DECLARE
> CURSOR c_emp_cursor IS
> SELECT employee_id, last_name FROM employees
> WHERE department_id = 30;
> v_empno employees.employee_id%TYPE;
> v_lname employees.last_name%TYPE;
> BEGIN
> OPEN c_emp_cursor;
> LOOP
> FETCH c_emp_cursor INTO v_empno, v_lname;
> EXIT WHEN c_emp_cursor%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE (v_empno || ' ' || v_lname);
> END LOOP;
> CLOSE c_emp_cursor;
> END;
> /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
Example 5-8 Fetching values into a record
This is similar to Example 5-7 above, with the same results, but fetches the values into a PL/SQL record instead of PL/SQL variables.
Command> DECLARE
> CURSOR c_emp_cursor IS
> SELECT employee_id, last_name FROM employees
> WHERE department_id = 30;
> v_emp_record c_emp_cursor%ROWTYPE;
> BEGIN
> OPEN c_emp_cursor;
> LOOP
> FETCH c_emp_cursor INTO v_emp_record;
> EXIT WHEN c_emp_cursor%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE (v_emp_record.employee_id || ' ' |
> v_emp_record.last_name);
> END LOOP;
> CLOSE c_emp_cursor;
> END;
> /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
PL/SQL procedure successfully completed.
Example 5-9 shows how to use the %ROWCOUNT cursor attribute as well as the %NOTFOUND cursor attribute previously shown in Example 5-7 and Example 5-8 above.
Example 5-9 Using %ROWCOUNT and %NOTFOUND attributes
This example has the same results as Example 5-8, but illustrating the %ROWCOUNT cursor attribute as well as the %NOTFOUND attribute for exit conditions in the loop.
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement or returned by a SELECT...INTO or FETCH...INTO statement.
Command> DECLARE
> CURSOR c_emp_cursor IS
> SELECT employee_id, last_name FROM employees
> WHERE department_id = 30;
> v_emp_record c_emp_cursor%ROWTYPE;
> BEGIN
> OPEN c_emp_cursor;
> LOOP
> FETCH c_emp_cursor INTO v_emp_record;
> EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE (v_emp_record.employee_id || ' ' ||
> v_emp_record.last_name);
> END LOOP;
> CLOSE c_emp_cursor;
> END;
> /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
PL/SQL procedure successfully completed.
PL/SQL in TimesTen supports cursor FOR loops, as shown in the following examples.
Example 5-10 Using a cursor FOR loop
In this example, PL/SQL implicitly declares emp_record. No OPEN and CLOSE statements are necessary. The results are the same as in Example 5-9 above.
Command> DECLARE
> CURSOR c_emp_cursor IS
> SELECT employee_id, last_name FROM employees
> WHERE department_id = 30;
> BEGIN
> FOR emp_record IN c_emp_cursor
> LOOP
> DBMS_OUTPUT.PUT_LINE (emp_record.employee_id || ' ' ||
> emp_record.last_name);
> END LOOP;
> END;
> /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
PL/SQL procedure successfully completed.
Example 5-11 Using a cursor FOR loop with subqueries
This example illustrates a FOR loop using subqueries. The results are the same as in Example 5-9 and Example 5-10 above.
Command> BEGIN
> FOR emp_record IN (SELECT employee_id, last_name FROM
> employees WHERE department_id = 30)
> LOOP
> DBMS_OUTPUT.PUT_LINE (emp_record.employee_id || ' ' ||
> emp_record.last_name);
> END LOOP;
> END;
> /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
PL/SQL procedure successfully completed.
Oracle TimesTen In-Memory Database supports bulk binding and the FORALL statement and BULK COLLECT feature, as noted in "FORALL and BULK COLLECT operations".
Examples in this section cover the following:
The %BULK_ROWCOUNT cursor attribute is a composite structure designed for use with the FORALL statement.
The attribute acts like an associative array (index-by table). Its ith element stores the number of rows processed by the ith execution of the INSERT statement. If the ith execution affects no rows, then %BULK_ROWCOUNT(i) returns zero.
This is demonstrated in Example 5-12.
Example 5-12 Using the FORALL statement with SQL%BULKROWCOUNT
Command> DECLARE
> TYPE num_list_type IS TABLE OF NUMBER
> INDEX BY BINARY_INTEGER;
> v_nums num_list_type;
> BEGIN
> v_nums (1) := 1;
> v_nums (2) := 3;
> v_nums (3) := 5;
> v_nums (4) := 7;
> v_nums (5) := 11;
> FORALL i IN v_nums.FIRST .. v_nums.LAST
> INSERT INTO num_table (n) VALUES (v_nums (i));
> FOR i IN v_nums.FIRST .. v_nums.LAST
> LOOP
> DBMS_OUTPUT.PUT_LINE ('Inserted '||
> SQL%BULK_ROWCOUNT (i) || ' row (s)' ||
> ' on iteration ' || i );
> END LOOP;
> END;
> /
Inserted 1 row (s) on iteration 1
Inserted 1 row (s) on iteration 2
Inserted 1 row (s) on iteration 3
Inserted 1 row (s) on iteration 4
Inserted 1 row (s) on iteration 5
PL/SQL procedure successfully completed.
Use BULK COLLECT with the SELECT statement in PL/SQL to retrieve rows without using a cursor.
Example 5-13 Using BULK COLLECT INTO with queries
This example selects all rows from the departments table for a specified location into a nested table, then uses a FOR LOOP to output data.
Command> CREATE OR REPLACE PROCEDURE get_departments (p_loc NUMBER) IS
> TYPE dept_tab_type IS
> TABLE OF departments%ROWTYPE;
> v_depts dept_tab_type;
> BEGIN
> SELECT * BULK COLLECT INTO v_depts
> FROM departments
> where location_id = p_loc;
> FOR i IN 1 .. v_depts.COUNT
> LOOP
> DBMS_OUTPUT.PUT_LINE (v_depts(i).department_id
> || ' ' || v_depts (i).department_name);
> END LOOP;
> END;
> /
Procedure created.
The following executes the procedure and verifies the results:
Command> EXECUTE GET_DEPARTMENTS (1700);
10 Administration
30 Purchasing
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
PL/SQL procedure successfully completed.
Command> SELECT department_id, department_name FROM departments WHERE
location_id = 1700;
< 10, Administration >
< 30, Purchasing >
< 90, Executive >
< 100, Finance >
< 110, Accounting >
< 120, Treasury >
< 130, Corporate Tax >
< 140, Control And Credit >
< 150, Shareholder Services >
< 160, Benefits >
< 170, Manufacturing >
< 180, Construction >
< 190, Contracting >
< 200, Operations >
< 210, IT Support >
< 220, NOC >
< 230, IT Helpdesk >
< 240, Government Sales >
< 250, Retail Sales >
< 260, Recruiting >
< 270, Payroll >
21 rows found.
Example 5-14 uses a cursor to bulk-collect rows from a table.
Example 5-14 Using BULK COLLECT INTO with cursors
This example uses a cursor to bulk-collect rows from the departments table with a specified location_id. value. Results are the same as in Example 5-13 above.
Command> CREATE OR REPLACE PROCEDURE get_departments2 (p_loc NUMBER) IS
> CURSOR cur_dept IS
> SELECT * FROM departments
> WHERE location_id = p_loc;
> TYPE dept_tab_type IS TABLE OF cur_dept%ROWTYPE;
> v_depts dept_tab_type;
> BEGIN
> OPEN cur_dept;
> FETCH cur_dept BULK COLLECT INTO v_depts;
> CLOSE cur_dept;
> FOR i IN 1 .. v_depts.COUNT
> LOOP
> DBMS_OUTPUT.PUT_LINE (v_depts (i).department_id
> || ' ' || v_depts (i).department_name );
> END LOOP;
> END;
> /
Procedure created.
Command> EXECUTE GET_DEPARTMENTS2 (1700);
10 Administration
30 Purchasing
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
PL/SQL procedure successfully completed.
SAVE EXCEPTIONS allows an UPDATE, INSERT, or DELETE statement to continue executing after it issues an exception. When the statement finishes, an error is issued to signal that at least one exception occurred. Exceptions are collected into an array that you can examine using %BULK_EXCEPTIONS after the statement has executed.
Example 5-15 Using SAVE EXCEPTIONS with BULK COLLECT
In this example, PL/SQL raises predefined exceptions because some new values are too large for the job_id column. After the FORALL statement, SQL%BULK_EXCEPTIONS.COUNT returns 2, and the contents of SQL%BULK_EXCEPTIONS are (7, 01401) and (13, 01401), indicating the error number and the line numbers where the error was detected. To get the error message, the negative of SQL%BULK_EXCEPTIONS(i).ERROR_CODE is passed to the error-reporting function SQLERRM (which expects a negative number).
The following script is used:
-- create a temporary table for this example
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
emp_sr empid_tab;
-- create an exception handler for ORA-24381
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
SELECT employee_id
BULK COLLECT INTO emp_sr FROM emp_temp
WHERE hire_date < '1994-12-30';
-- add '_SR' to the job_id of the most senior employees
FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
UPDATE emp_temp SET job_id = job_id || '_SR'
WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
-- Figure out what failed and why
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE
('Number of statements that failed: ' || errors);
FOR i IN 1..errors LOOP
DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||
'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
/
DROP TABLE emp_temp;
Results are as follows:
Number of statements that failed: 2 Error #1 occurred during iteration #7 Error message is ORA-01401: inserted value too large for column Error #2 occurred during iteration #13 Error message is ORA-01401: inserted value too large for column PL/SQL procedure successfully completed.
TimesTen supports the EXECUTE IMMEDIATE statement, as noted in "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)". This section provides additional examples to consider as you develop your PL/SQL applications in TimesTen, including how to use EXECUTE IMMEDIATE to alter a PL/SQL connection attribute or call a TimesTen built-in procedure.
Example 5-16 Using EXECUTE IMMEDIATE to alter PLSCOPE_SETTINGS
This example uses the EXECUTE IMMEDIATE statement with ALTER SESSION to alter the PLSQL_OPTIMIZE_LEVEL setting, calling the ttConfiguration built-in procedure before and after to verify the results. Refer to "ttConfiguration" in Oracle TimesTen In-Memory Database Reference for information about this procedure.
Command> call ttconfiguration; ... < PLSCOPE_SETTINGS, IDENTIFIERS:NONE > < PLSQL, 1 > < PLSQL_CCFLAGS, <NULL> > < PLSQL_CODE_TYPE, INTERPRETED > < PLSQL_CONN_MEM_LIMIT, 100 > < PLSQL_MEMORY_ADDRESS, 0x10000000 > < PLSQL_MEMORY_SIZE, 32 > < PLSQL_OPTIMIZE_LEVEL, 2 > < PLSQL_TIMEOUT, 30 > ... 54 rows found. Command> begin > execute immediate 'alter session set PLSQL_OPTIMIZE_LEVEL=3'; > end; > / PL/SQL procedure successfully completed. Command> call ttconfiguration; ... < PLSCOPE_SETTINGS, IDENTIFIERS:NONE > < PLSQL, 1 > < PLSQL_CCFLAGS, <NULL> > < PLSQL_CODE_TYPE, INTERPRETED > < PLSQL_CONN_MEM_LIMIT, 100 > < PLSQL_MEMORY_ADDRESS, 0x10000000 > < PLSQL_MEMORY_SIZE, 32 > < PLSQL_OPTIMIZE_LEVEL, 3 > < PLSQL_TIMEOUT, 30 > ... 54 rows found.
Example 5-17 Using the EXECUTE IMMEDIATE statement with a single row query
In this example, the function get_emp retrieves the employee record into variable v_emprec. Execute the function and return the results in v_emprec.
Command> CREATE OR REPLACE FUNCTION get_emp (p_emp_id NUMBER)
> RETURN employees%ROWTYPE IS
> v_stmt VARCHAR2 (200);
> v_emprec employees%ROWTYPE;
> BEGIN
> v_stmt:= 'SELECT * FROM EMPLOYEES '||
> 'WHERE employee_id = :p_emp_id';
> EXECUTE IMMEDIATE v_stmt INTO v_emprec USING p_emp_id;
> RETURN v_emprec;
> END;
> /
Function created.
Command> DECLARE
> v_emprec employees%ROWTYPE := GET_EMP (100);
> BEGIN
> DBMS_OUTPUT.PUT_LINE ('Employee: ' || v_emprec.last_name);
> END;
> /
Employee: King
PL/SQL procedure successfully completed.
Example 5-18 Using EXECUTE IMMEDIATE with TimesTen specific syntax
Use the EXECUTE IMMEDIATE statement to execute a TimesTen SELECT FIRST n statement. This syntax is specific to TimesTen.
Command> DECLARE v_empid NUMBER;
> BEGIN
> EXECUTE IMMEDIATE 'SELECT FIRST 1 employee_id FROM employees'
> INTO v_empid;
> DBMS_OUTPUT.PUT_LINE ('Employee id: ' || v_empid);
> END;
> /
Employee id: 100
PL/SQL procedure successfully completed.
Example 5-19 Using EXECUTE IMMEDIATE to call ttConfiguration
In PL/SQL, you can use the EXECUTE IMMEDIATE statement with CALL syntax to call TimesTen built-in procedures, such as ttConfiguration.
For example, to call the built-in procedure ttConfiguration and return its output result set, create a PL/SQL record type then use EXECUTE IMMEDIATE with BULK COLLECT to fetch the result set into an array.
For more information on TimesTen built-in procedures, see "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.
Command> DECLARE
> TYPE ttConfig_record IS RECORD
> (name varchar2(255), value varchar2 (255));
> TYPE ttConfig_table IS TABLE OF ttConfig_record;
> v_ttConfigs ttConfig_table;
> BEGIN
> EXECUTE IMMEDIATE 'CALL ttConfiguration'
> BULK COLLECT into v_ttConfigs;
> DBMS_OUTPUT.PUT_LINE ('Name: ' || v_ttConfigs(1).name
> || ' Value: ' || v_ttConfigs(1).value);
> end;
> /
Name: CacheGridEnable Value: 0
PL/SQL procedure successfully completed.
This section includes the following two examples using the RETURNING INTO clause:
See "RETURNING INTO clause" for an overview.
The following example uses ttIsql to run a SQL script that uses a RETURNING INTO clause to return data into a record. The example gives a raise to a specified employee, returns his name and new salary into a record, then outputs the data from the record. For reference, the original salary is shown before running the script.
Command> SELECT SALARY,LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
< 24000, King >
1 row found.
Command> run ReturnIntoWithRecord.sql;
CREATE TABLE emp_temp AS SELECT * FROM employees;
107 rows inserted.
DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE,
salary employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
BEGIN
UPDATE emp_temp SET salary = salary * 1.1
WHERE employee_id = emp_id
RETURNING last_name, salary INTO emp_info;
DBMS_OUTPUT.PUT_LINE
('Just gave a raise to ' || emp_info.last_name ||
', who now makes ' || emp_info.salary);
ROLLBACK;
END;
/
Just gave a raise to King, who now makes 26400
PL/SQL procedure successfully completed.
The following example uses ttIsql to run a SQL script that uses a RETURNING INTO clause with BULK COLLECT to return data into nested tables, a type of PL/SQL collection. The example deletes all the employees from a specified department, then, using one nested table for employee IDs and one for last names, outputs the employee ID and last name of each deleted employee. For reference, the IDs and last names of employees in the department are also displayed before execution of the script.
Command> select employee_id, last_name from employees where department_id=30;
< 114, Raphaely >
< 115, Khoo >
< 116, Baida >
< 117, Tobias >
< 118, Himuro >
< 119, Colmenares >
6 rows found.
Command> run ReturnIntoWithBulkCollect.sql;
CREATE TABLE emp_temp AS SELECT * FROM employees;
107 rows inserted.
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp_temp WHERE department_id = 30
RETURNING employee_id, last_name
BULK COLLECT INTO enums, names;
DBMS_OUTPUT.PUT_LINE
('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE
('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
Deleted 6 rows:
Employee #114: Raphaely
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares
PL/SQL procedure successfully completed.
This section runs a script twice with just one change, first defining a PL/SQL procedure with AUTHID CURRENT_USER for invoker's rights, then with AUTHID DEFINER for definer's rights. See "Definer's rights and invoker's rights" for related information.
The script assumes three users have been created: a tool vendor and two tool users (brandX and brandY). Each has been granted CREATE SESSION, CREATE PROCEDURE, and CREATE TABLE privileges as necessary. The following setup is also assumed, to allow "use username;" syntax to connect to the database as username:
connect adding "uid=toolVendor;pwd=pw" as toolVendor; connect adding "uid=brandX;pwd=pw" as brandX; connect adding "uid=brandY;pwd=pw" as brandY;
The script does the following:
Creates the procedure, printInventoryStatistics, as the tool vendor.
Creates a table with the same name, myInventory, in each of the three user schemas, populating it with unique data in each case.
Runs the procedure as each of the tool users.
The different results between the two executions of the script show the difference between invoker's rights and definer's rights.
Here is the script for the invoker's rights execution:
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
create or replace procedure printInventoryStatistics authid current_user is
inventoryCount pls_integer;
begin
select count(*) into inventoryCount from myInventory;
dbms_output.put_line('Total items in inventory: ' || inventoryCount);
for currentItem in (select * from myInventory) loop
dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
end loop;
end;
/
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
set serveroutput on
execute toolVendor.printInventoryStatistics;
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
set serveroutput on
execute toolVendor.printInventoryStatistics;
The only difference for the definer's rights execution is the change in the AUTHID clause for the procedure definition.
...
create or replace procedure printInventoryStatistics authid definer is
inventoryCount pls_integer;
begin
select count(*) into inventoryCount from myInventory;
dbms_output.put_line('Total items in inventory: ' || inventoryCount);
for currentItem in (select * from myInventory) loop
dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
end loop;
end;
/
...
Example 5-20 Using AUTHID CURRENT_USER
Following are the results when the procedure is defined with invoker's rights. Note that when the tool users brandX and brandY run the printInventoryStatistics procedure, each sees the data in his own (the invoker's) myInventory table.
Command> run invoker.sql
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
create or replace procedure printInventoryStatistics authid current_user is
inventoryCount pls_integer;
begin
select count(*) into inventoryCount from myInventory;
dbms_output.put_line('Total items in inventory: ' || inventoryCount);
for currentItem in (select * from myInventory) loop
dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
end loop;
end;
/
Procedure created.
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
toothpaste 100
PL/SQL procedure successfully completed.
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
shampoo 10
PL/SQL procedure successfully completed.
Use the following to terminate all the connections:
Command> disconnect all;
Example 5-21 Using AUTHID DEFINER
Following are the results when the procedure is defined with definer's rights. Note that when the tool users brandX and brandY run printInventoryStatistics, each sees the data in myInventory belonging to the tool vendor (the definer).
Command> run definer.sql
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
create or replace procedure printInventoryStatistics authid definer is
inventoryCount pls_integer;
begin
select count(*) into inventoryCount from myInventory;
dbms_output.put_line('Total items in inventory: ' || inventoryCount);
for currentItem in (select * from myInventory) loop
dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
end loop;
end;
/
Procedure created.
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
PL/SQL procedure successfully completed.
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
PL/SQL procedure successfully completed.
In this case, it is also instructive to see that although brandX and brandY can each access the toolVendor.myInventory table through the procedure, they cannot access it directly. That is a key use of definer's rights, to allow specific and restricted access to a table or other SQL object through the actions of a procedure.
Command> use brandX; brandx: Command> select * from toolVendor.myInventory; 15100: User BRANDX lacks privilege SELECT on TOOLVENDOR.MYINVENTORY The command failed. brandx: Command> use brandY; brandy: Command> select * from toolVendor.myInventory; 15100: User BRANDY lacks privilege SELECT on TOOLVENDOR.MYINVENTORY The command failed.
Use the following to terminate all the connections:
Command> disconnect all;
This section provides an example that queries a system view.
Example 5-22 Querying system view USER_SOURCE
This example queries the USER_SOURCE system view to examine the source code of procedure query_emp from Example 5-4. (You must create that procedure before completing this example.)
Command> SELECT SUBSTR (text, 1, LENGTH(text)-1)
> FROM user_source
> WHERE name = 'QUERY_EMP' AND type = 'PROCEDURE';
This produces the following output:
< PROCEDURE query_emp > < (p_id IN employees.employee_id%TYPE, > < p_name OUT employees.last_name%TYPE, > < p_salary OUT employees.salary%TYPE) IS > < BEGIN > < SELECT last_name, salary INTO p_name, p_salary > < FROM employees > < WHERE employee_id = p_id; > < END query_emp; > 9 rows found.
Note:
As with otherUSER_* system views, all users have SELECT privilege for the USER_SOURCE system view.