Context Switches and Performance
Almost every program PL/SQL developers write includes both PL/SQL and SQL statements.
PL/SQL statements are run by the PL/SQL statement executor.
SQL statements are run by the SQL statement executor.
When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine.The SQL engine executes the SQL statement and returns information back to the PL/SQL engine.This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.
Almost every program PL/SQL developers write includes both PL/SQL and SQL statements.
PL/SQL statements are run by the PL/SQL statement executor.
SQL statements are run by the SQL statement executor.
When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine.The SQL engine executes the SQL statement and returns information back to the PL/SQL engine.This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.
With the RETURNING clause, I can reduce network round trips, consume
less server CPU time, and minimize the number of cursors opened and managed
in the application.
create or replace TYPE array_type IS VARRAY (3) OF VARCHAR2 (100);
create or replace type number_type is varray(3) of number;
less server CPU time, and minimize the number of cursors opened and managed
in the application.
create or replace TYPE array_type IS VARRAY (3) OF VARCHAR2 (100);
create or replace type number_type is varray(3) of number;
Use the RETURNING clause to retrieve a value (the new salary) that was computed within the UPDATE statement.
Example
DECLAREmyname employees.last_name%TYPE;
mysal employees.salary%TYPE;
BEGIN
FOR rec IN (SELECT * FROM employees)
LOOP
UPDATE employees
SET salary = salary * 1.5
WHERE employee_id = rec.employee_id
RETURNING salary, last_name INTO mysal, myname;
DBMS_OUTPUT.PUT_LINE ('New salary for ' ||
myname || ' = ' || mysal);
END LOOP;
END;
Example
DECLAREmyname employees.last_name%TYPE;
mysal employees.salary%TYPE;
BEGIN
FOR rec IN (SELECT * FROM employees)
LOOP
UPDATE employees
SET salary = salary * 1.5
WHERE employee_id = rec.employee_id
RETURNING salary, last_name INTO mysal, myname;
DBMS_OUTPUT.PUT_LINE ('New salary for ' ||
myname || ' = ' || mysal);
END LOOP;
END;
Use the RETURNING clause to retrieve not just into a single variable, but into collection.
Example
DECLARE
names array_type := array_type ();
names_ret array_type := array_type ();
numbers number_type := number_type();
BEGIN
names.extend(2);
names_ret.extend(2);
names(1) := 'Mahmoud';
names(2) := 'Noor';
numbers.extend(2);
numbers(1) := 100;
numbers(2) := 101;
FORALL i IN names.first..names.last
UPDATE employees SET FIRST_NAME = names(i) WHERE EMPLOYEE_ID = numbers(i)
RETURNING FIRST_NAME BULK COLLECT INTO names_ret;
DBMS_OUTPUT.PUT_LINE(names_ret(1));
END;
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
Example
DECLARE
names array_type := array_type ();
names_ret array_type := array_type ();
numbers number_type := number_type();
BEGIN
names.extend(2);
names_ret.extend(2);
names(1) := 'Mahmoud';
names(2) := 'Noor';
numbers.extend(2);
numbers(1) := 100;
numbers(2) := 101;
FORALL i IN names.first..names.last
UPDATE employees SET FIRST_NAME = names(i) WHERE EMPLOYEE_ID = numbers(i)
RETURNING FIRST_NAME BULK COLLECT INTO names_ret;
DBMS_OUTPUT.PUT_LINE(names_ret(1));
END;
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
