Cover | Table of Contents | Colophon
PRAGMA AUTONOMOUS_TRANSACTION;
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; myempno NUMBER; BEGIN INSERT INTO emp VALUES (myempno, ...); COMMIT; END; /
DECLARE
myempno NUMBER;
BEGIN
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO emp VALUES (myempno, ...);
COMMIT;
END;
END;
/
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here
/* Filename on companion disk: autondlock.sql*/
CREATE OR REPLACE PROCEDURE
update_salary (dept_in IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR myemps IS
SELECT empno FROM emp
WHERE deptno = dept_in
FOR UPDATE NOWAIT;
BEGIN
FOR rec IN myemps
LOOP
UPDATE emp SET sal = sal * 2
WHERE empno = rec.empno;
END LOOP;
COMMIT;
END;
/
BEGIN
UPDATE emp SET sal = sal * 2;
update_salary (10);
END;
/
ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified
/* Filename on companion disk: log81.pkg */
CREATE TABLE log81tab (
code INTEGER,
text VARCHAR2(4000),
created_on DATE,
created_by VARCHAR2(100),
changed_on DATE,
changed_by VARCHAR2(100),
machine VARCHAR2(100),
program VARCHAR2(100)
);
EXCEPTION
WHEN OTHERS
THEN
v_code := SQLCODE;
v_msg := SQLERRM;
INSERT INTO log81tab VALUES (
v_code, v_msg, SYSDATE, USER, SYSDATE, USER, NULL, NULL);
END;
AUTHID CURRENT_USER
CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2) AUTHID CURRENT_USER IS BEGIN EXECUTE IMMEDIATE ddl_in; END; /
http://www.unstoppable.com/22/english/stolenlivesPROJECT
CREATE OR REPLACE PROCEDURE showemps (
where_in IN VARCHAR2 := NULL)
IS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rec employee%ROWTYPE;
fdbk INTEGER;
BEGIN
DBMS_SQL.PARSE
(cur,
'SELECT employee_id, last_name
FROM employee
WHERE ' || NVL (where_in, '1=1'),
DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
DBMS_SQL.DEFINE_COLUMN (cur, 2, user, 30);
fdbk := DBMS_SQL.EXECUTE (cur);
LOOP
/* Fetch next row. Exit when done. */
EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name);
DBMS_OUTPUT.PUT_LINE (
TO_CHAR (rec.employee_id) || '=' ||
rec.last_name);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/CREATE OR REPLACE PROCEDURE showemps (
where_in IN VARCHAR2 := NULL)
IS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rec employee%ROWTYPE;
fdbk INTEGER;
BEGIN
DBMS_SQL.PARSE
(cur,
'SELECT employee_id, last_name
FROM employee
WHERE ' || NVL (where_in, '1=1'),
DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
DBMS_SQL.DEFINE_COLUMN (cur, 2, user, 30);
fdbk := DBMS_SQL.EXECUTE (cur);
LOOP
/* Fetch next row. Exit when done. */
EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name);
DBMS_OUTPUT.PUT_LINE (
TO_CHAR (rec.employee_id) || '=' ||
rec.last_name);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/
CREATE OR REPLACE PROCEDURE showemps (
where_in IN VARCHAR2 := NULL)
IS
TYPE cv_typ IS REF CURSOR;
cv cv_typ;
v_id employee.employee_id%TYPE;
v_nm employee.last_name%TYPE;
BEGIN
OPEN cv FOR
'SELECT employee_id, last_name
FROM employee
WHERE ' || NVL (where_in, '1=1');
LOOP
FETCH cv INTO v_id, v_nm;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
TO_CHAR (v_id) || '=' || v_nm);
END LOOP;
CLOSE cv;
END;
/
EXECUTE IMMEDIATE SQL_string [INTO {define_variable[, define_variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...];
|
Concatenation
|
Binding
|
|---|---|
EXECUTE IMMEDIATE
'UPDATE ' || tab
'SET sal = ' || v_sal;
|
EXECUTE IMMEDIATE
'UPDATE ' || tab
'SET sal = :new_sal'
USING v_sal;
|
CREATE TYPE person AS OBJECT ( name VARCHAR2(50), dob DATE, income NUMBER); / CREATE TYPE preexisting_conditions IS TABLE OF VARCHAR2(25); /
/* Filename on companion disk: health$.pkg */
CREATE OR REPLACE PACKAGE health$
AS
PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2);
PROCEDURE add_profit_source (
hosp_name IN VARCHAR2,
pers IN Person,
cond IN preexisting_conditions);
PROCEDURE weed_out_poor_and_sick (
hosp_name VARCHAR2,
min_income IN NUMBER := 100000,
max_preexist_cond IN INTEGER := 0);
PROCEDURE show_profit_centers (hosp_name VARCHAR2);
END health$;
/
FUNCTION tabname (hosp_name IN VARCHAR2) IS
BEGIN
RETURN hosp_name || '_profit_center';
END;
PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE ' || tabname (hosp_name) || ' (
pers Person,
cond preexisting_conditions)
NESTED TABLE cond STORE AS cond_st';
END;SQL> exec COMMON.execDDL ('create table temp (x date)');
CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2) AUTHID CURRENT_USER IS BEGIN EXECUTE IMMEDIATE ddl_string; END; /
|
Name
|
Description
|
|---|---|
|
execImmed
|
Substitute for EXECUTE IMMEDIATE that does not need a USING or INTO clause; includes error handling.
|
|
openFor
|
Substitute for OPEN FOR that does not need a USING clause; includes error handling.
|
|
showCol
|
Shows the contents of a single column in the specified table.
|
|
tabCount
|
Returns the number of rows in the specified table, with an optional WHERE clause.
|
|
countBy
|
Returns the number of rows in the specified table for a particular GROUP BY expression, with an optional HAVING clause.
|
|
dynPLSQL
|
Executes a dynamic PL/SQL string, automatically making sure that it is a valid block and that it ends in a semicolon. The USING clause is not allowed.
|
CREATE OR REPLACE PROCEDURE update_tragedies (
warcrim_ids IN name_varray,
num_victims IN number_varray
)
IS
BEGIN
FOR indx IN warcrim_ids.FIRST .. warcrim_ids.LAST
LOOP
UPDATE war_criminal
SET victim_count = num_victims (indx)
WHERE war_criminal_id = warcrim_ids (indx);
END LOOP;
END;
DECLARE
CURSOR major_polluters IS
SELECT name, mileage
FROM cars_and_trucks
WHERE vehicle_type IN ('SUV', 'PICKUP');
names name_varray := name_varray();
mileages number_varray := number_varray();
BEGIN
FOR bad_car IN major_polluters
LOOP
names.EXTEND;
names (major_polluters%ROWCOUNT) := bad_car.name;
mileages.EXTEND;
mileages (major_polluters%ROWCOUNT) := bad_car.mileage;
END LOOP;
... now work with data in the arrays ...
END;