PL/SQL. Mutating table/trigger. Robert Wrembel Politechnika Poznańska Instytut Informatyki - PDF

Description
PL/SQL Mutating table/trigger Dynamic SQL Robert Wrembel Politechnika Poznańska Instytut Informatyki Outline Triggers - overview Mutating table/trigger

Please download to get full document.

View again

of 15
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information
Category:

Documents

Publish on:

Views: 17 | Pages: 15

Extension: PDF | Download: 0

Share
Transcript
PL/SQL Mutating table/trigger Dynamic SQL Robert Wrembel Politechnika Poznańska Instytut Informatyki Outline Triggers - overview Mutating table/trigger - test cases Dynamic SQL DBMS_SQL execute immediate 2 Defining Trigger CREATE [OR REPLACE] TRIGGER trig-name {BEFORE AFTER INSTEAD OF} {INSERT UPDATE DELETE} ON {tab-name view-name} [WHEN condition] [FOR EACH ROW] [DECLARE /* declaring variables, constants, cursors, exceptions*/] /* trigger body */ INSTEAD OF applicable to views only 3 Defining Trigger CREATE OR REPLACE TRIGGER test AFTER UPDATE OF att1,..., attrn ON tab-name... CREATE OR REPLACE TRIGGER test AFTER INSERT OR UPDATE OR DELETE ON tab-name IF INSERTING THEN... ELSIF UPDATING THEN... ELSIF DELETING THEN... END IF; 4 FOR EACH ROW and WHEN CREATE OR REPLACE TRIGGER test BEFORE UPDATE ON inventory FOR EACH ROW WHEN (OLD.quantity 100) IF (:NEW.quantity = 100) THEN... END IF; IF (:NEW.ord_date = :OLD.ship_date) THEN... END IF; CREATE OR REPLACE TRIGGER genid BEFORE INSERT ON emp FOR EACH ROW IF (:NEW.empID IS NULL) THEN SELECT seq_emp.nextval INTO :NEW.empID FROM DUAL; END IF; 5 Schema Trigger CREATE [OR REPLACE] TRIGGER trig-name {BEFORE AFTER} event-type ON {SCHEMA DATABASE} [DECLARE /* declaring variables, constants, cursors, exceptions*/] /* trigger body */ Every event can be detected either on the level of a user (schema) or the whole database 6 Schema Trigger Event types: after servererror fired by Oracle error after logon before logoff before/after ddl any DDL command before/after alter before/after create before/after drop before/after truncate before/after rename before/after analyze before/after audit before/after noaudit before/after grant before/after revoke 7 Schema Trigger CREATE OR REPLACE TRIGGER ddltrg_bcs BEFORE CREATE ON SCHEMA INSERT INTO ddl_log SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL, USER, SYSDATE FROM dual; system functions Creating ON DATABASE triggers requires system privilege ADMINISTER DATABASE TRIGGER 8 Schema Trigger CREATE OR REPLACE TRIGGER ddltrg_bcraldrs BEFORE CREATE OR ALTER OR DROP ON SCHEMA DECLARE oper varchar2(20); SELECT ora_sysevent INTO oper FROM dual; IF oper IN ('CREATE', 'DROP') THEN... ELSIF oper = 'ALTER' THEN... END IF; 9 Managing Triggers ALTER TABLE tab-name DISABLE [ENABLE] ALL TRIGGERS; ALTER TRIGGER trig-name DISABLE [ENABLE]; DROP TRIGGER trig-name; USER_TRIGGERS trigger_name trigger_type triggering_event table_name trigger_body... USER_DEPENDENCIES referenced_name referenced_owner... 10 Mutating table/trigger create or replace trigger BIR_count before insert on sales create table sales for each row (id number(3) primary key, declare productid varchar2(20), no number:=0; custid number(3), s_count_custid number(3)); select count(*)+1 into no from sales where custid=:new.custid; :new.s_count_custid:=no; update sales set s_count_custid=no where custid=:new.custid; insert into sales (id, productid, custid) values (1, 'book1', 10); the above INSERT does not cause mutation single row is inserted by the command no chances to see mutliple table states 11 Mutating table/trigger insert into sales (select id+10, productid, custid, null from sales); ERROR at line 1: ORA-04091: table ROBCIO.SALES is mutating, trigger/function may not see it ORA-06512: at ROBCIO.BIR_COUNT , line 4 ORA-04088: error during execution of trigger 'ROBCIO.BIR_COUNT' Bulk insert each inserted row would see different table state 12 Mutating table/trigger create or replace trigger ADR_count after delete on sales for each row declare no number:=0; select count(*) into no from sales where custid=:old.custid; update sales set s_count_custid=no where custid=:old.custid; delete from sales where custid=10; delete from sales where ID=10; mutating multiple mutating rows? deleted mutating? 13 Mutating table/trigger create or replace trigger AD_count after delete on sales declare no number:=0; update sales s set s.s_count_custid= (select count(*) from sales where custid=s.custid); delete from sales where custid=10; not mutating 14 Mutating table/function update sales s set s_count_custid= (select count(*) from sales where custid=s.custid); UPDATE executed correctly create or replace function f_count(v_custid IN number) return number as no number(2):=0; select count(*) into no from sales where custid=v_custid; return no; update sales s set s_count_custid= f_count(s.custid); ERROR at line 1: ORA-04091: table ROBCIO.SALES is mutating, trigger/function may not see it ORA-06512: at ROBCIO.F_COUNT , line 5 15 Dynamic SQL Constructing SQL commands on-line (in a program) select, DML, DDL, DCL Command EXECUTE IMMEDIATE processing queries that return a single row processing objects and collections (these are not supported by DBMS_SQL) Package DBMS_SQL processing queries that return multiple rows 16 DBMS_SQL 17 DBMS_SQL - Proc and Functions OPEN_CURSOR opens a new cursor and assigns it ID IS_OPEN checks if a cursor is open PARSE parsing a command BIND_VARIABLE binds a given value to the variable identified by its name in the parsed statement in a given cursor EXECUTE executes a command and returns the number of row processed for IUD, for other commands the value may be unspecified FETCH_ROWS retrieves a row for the specified cursor (multiple rows are fetched in a loop) CLOSE_CURSOR closes the specified cursor... 18 DBMS_SQL CREATE OR REPLACE PROCEDURE drop_object (type_in IN VARCHAR2, name_in IN VARCHAR2) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; n PLS_INTEGER; DBMS_SQL.PARSE (cur, 'DROP ' type_in ' ' name_in, DBMS_SQL.NATIVE); n:= DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); V6 (or 0) V6 behavior NATIVE (or 1) behavior of the database to which the program is connected V7 (or 2) V7 behavior 19 DBMS_SQL Procedure that deletes sales records of a given customer CREATE or replace PROCEDURE p_del(cid in number) as cmd varchar2(100); curs number; n number; cmd:='delete from sales where custid = :v_cid'; curs:= dbms_sql.open_cursor; dbms_sql.parse(curs, cmd, dbms_sql.native); dbms_sql.bind_variable(curs, 'v_cid', cid); n:=dbms_sql.execute(curs); dbms_sql.close_cursor(curs); dbms_output.put_line(n ' rows deleted'); 20 DBMS_SQL DECLARE cmd varchar2(100); arr_custid dbms_sql.number_table; curs number; n number; arr_custid(1):=10; arr_custid(2):=20; cmd:='delete from sales where custid = :v_arr'; curs:= dbms_sql.open_cursor; dbms_sql.parse(curs, cmd, dbms_sql.native); dbms_sql.bind_array(curs, 'v_arr', arr_custid, 1, 2); n:=dbms_sql.execute(curs); dbms_sql.close_cursor(curs); dbms_output.put_line(n ' rows deleted'); EXCEPTION when others then Program that deletes sales records: binding variable as an array array index array end index if dbms_sql.is_open(curs) then dbms_sql.close_cursor(curs); end if; raise; 21 DBMS_SQL Program that bulk-inserts sales DECLARE records: binding variables as arrays cmd varchar2(100); arr_id dbms_sql.number_table; arr_prodid dbms_sql.varchar2_table; bulk insert - table of data curs number; n number; for i in 1..3 loop arr_id(i):=i+10; arr_prodid(i):='book' to_char(i+10); end loop; cmd:= 'insert into sales (id, productid, custid) values (:id, :prodid, 40)'; curs:= dbms_sql.open_cursor; dbms_sql.parse(curs, cmd, dbms_sql.native); dbms_sql.bind_array(curs, 'id', arr_id); dbms_sql.bind_array(curs, 'prodid', arr_prodid); n:= dbms_sql.execute(curs); dbms_sql.close_cursor(curs); 22 DECLARE curs number; n number; col_cnt number; rec_tab dbms_sql.desc_tab; col_num number; curs:=dbms_sql.open_cursor; DBMS_SQL dbms_sql.parse(curs,'select * from sales', dbms_sql.native); n:=dbms_sql.execute(curs); dbms_sql.describe_columns(curs, col_cnt, rec_tab); col_num := rec_tab.first; if (col_num IS NOT NULL) then loop dbms_output.put_line('col_name=' rec_tab(col_num).col_name); dbms_output.put_line('col_type=' rec_tab(col_num).col_type); dbms_output.put_line('col_maxlen=' rec_tab(col_num).col_max_len); col_num := rec_tab.next(col_num); exit when (col_num is null); end loop; end if; dbms_sql.close_cursor(curs); Using DESCRIBE_COLUMNS to get the info about columns in SELECT variable in OUT mode, stores the number of columns in SELECT 23 DBMS_SQL DECLARE curs integer; v_pid varchar2(20); v_cid number:=&cid; n integer; curs:=dbms_sql.open_cursor; defines CHAR column that is to be returned and retrieved in a cursor dbms_sql.parse(curs, 'select distinct productid from sales where custid :cid', dbms_sql.native); dbms_sql.bind_variable(curs, 'cid', v_cid); dbms_sql.define_column_char(curs, 1, 'varchar2', 20); -- this is also possible: dbms_sql.define_column_char(curs, 1, v_pid, 20); n:=dbms_sql.execute(curs); loop column position in SELECT if dbms_sql.fetch_rows(curs)=0 then exit; end if; dbms_sql.column_value_char(curs, 1, v_pid); dbms_output.put_line('product id=' v_pid); end loop; dbms_sql.close_cursor(curs); defines a variable to receive a CHAR value EXCEPTION from a cursor when others then dbms_sql.close_cursor(curs); Robert raise_application_error(-20000, Wrembel, Politechnika Poznańska, Instytut Informatyki 'Error: ' sqlcode ' ' sqlerrm); 24 Execute Immediate execute immediate 'set role all'; variable c number execute immediate 'select count(*) from emp' into :c; / print c /* passing parameters into insert */ declare l_dep varchar2(20) := 'QA'; l_loc varchar2(10) := 'Warszawa'; execute immediate 'insert into dept values (:1, :2, :3)' using 50, l_dep, l_loc; / 25 Execute Immediate /* reading a value of a variable */ declare n_emp number(2); r_emp emp%rowtype; sql_comm varchar2(100); execute immediate 'select count(1) from emp' into n_emp; execute immediate 'select * from emp where empno=7839' into r_emp; dbms_output.put_line('r_emp.ename=' r_emp.ename); sql_comm:='select * from emp where empid = :id'; execute immediate sql_comm into r_emp using 7840; Remark: INTO must precede USING 26 Execute Immediate DECLARE sql_comm varchar2(100); bnd_deptid number(6); bnd_name VARCHAR2(5) := 'IT'; bnd_address VARCHAR2(25) := 'xxx'; r_emp emp%rowtype; EXECUTE IMMEDIATE 'CREATE TABLE test (id NUMBER, val VARCHAR2(10))'; EXECUTE IMMEDIATE 'declare x number; x:=' 'f_count(20); dbms_output.put_line(x); '; sql_comm:='insert INTO dept VALUES (seq_dept.nextval, :1, :2) ' 'RETURNING deptid INTO :3'; EXECUTE IMMEDIATE sql_stmt USING bnd_name, bnd_address RETURNING INTO bnd_deptid; 27 Execute Immediate /* calling procedure with parameters */ declare p_proc varchar2(100):= 'proc_add'; p1 varchar2(10):='test'; p2 number; p3 number; execute immediate ' ' p_proc '(:1, :2, :3); ' using in p1, out p2, in out p3; 28 Execute Immediate declare type sales_tab_type is table of sales%rowtype; sales_tab sales_tab_type; v_custid number:=&custid; execute immediate 'select * from sales where custid=:1' bulk collect into sales_tab using v_custid; for i in 1..sales_tab.count loop dbms_output.put_line('custid=' sales_tab(i).custid); end loop; / 29 DBMS_SQL vs. EXECUTE IMMEDIATE x select 'text' from dual Statistics available in v$mystat v$statname v$latch the output of tkprof 30
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks