How Oracle updates a newly inserted data collection with a trigger implementation

How Oracle can update newly inserted data with trigger implementation Collection

1, establish the test table

CREATE TABLE TEST_TB

(

ID number,

WLID number,

PM VARCHAR2 (100),

Djzt VARCHAR2 (10),

SL number,

PH VARCHAR2 (100)

);

2. Set up the package and the package body and function defined with REF CURSOR:

CREATE OR REPLACE

Package Pkg_test AS

/* Define REF CURSOR type

No return type, for weak type, allows dynamic SQL queries,

Otherwise, it is strongly typed and cannot use dynamic SQL queries;

*/

Type myrctype is REF CURSOR;

--Function declaration

function get (IntID number) return myrctype;

End Pkg_test;

/

Create or replace package body pkg_test as

--Function body

function get (IntID number) return Myrctype is

RC Myrctype; --Defining the REF CURSOR variable

Sqlstr VARCHAR2 (500);

Begin

--if Intid=0 Then

--static tests, directly returning results directly with the SELECT statement

--Open RC for select id,name,sex,address,postcode,birthday from student;

--else

--Dynamic SQL assignment, using: w_id to declare that the variable is externally derived

SQLSTR: = ' Select ph,pm from WLZD_TB where ID =:1 ';

--dynamic test, return result with SQLSTR string, pass parameter with using keyword

Open RC for sqlstr using IntID;

--End If;

return RC;

End get;

End Pkg_test;

3. Create a trigger:

Create or Replace Trigger TEST_TG

Before insert

On TEST_TB

For each row

Declare

W_RC Pkg_test.myrctype; --Define the REF CURSOR type variable

Begin

W_RC: = Pkg_test.get (: new.wlid);

Loop

Fetch W_RC into:new.ph,:new.pm;

Exit when W_rc%notfound;

--:NEW.PM: = ref.pm;

--:new.ph: = Test_fc (: new.wlid). ph;

End Loop;

End

4. Test:

Insert into TEST_TB (id,wlid) VALUES (10,345)

Results:

How Oracle updates a newly inserted data collection with a trigger implementation