Articles
SQL & PL/SQL
 | Oracle Database 11g: by Arup Nanda |
Efficient PL/SQL Coding
A trigger that fires several times at different triggering events, ability to force triggers of the same type to follow a sequence, and the new CONTINUE statement are some of the new gems that make PL/SQL programming easier.
See Series TOC
Since its inception, PL/SQL has been the language of choice for programming in Oracle Database. Over a period of time, we have seen the language evolve into a comprehensive development platform by virtue of more and more functionality that requires less coding. Oracle Database 11g makes PL/SQL coding yet more efficient for programmers. In this installment, you will see some examples that offer an introductory glimpse into this new functionality.
Compound Triggers
Consider a hotel database: bookings for the hotel rooms are recorded in the table named BOOKINGS. You also want to record the changes to this table to a tracking table—sort of like auditing, but with a twist: You want to make it transactional. Triggers are perfect for that.
You come up with a small after-update row trigger that records the old and new values along with who changed it into a table BOOKINGS_HIST. So far, so good.
But there is a little issue here. The after-update row trigger fires for every row, and some bookings are changed in bulk, updating hundreds of rows in one transaction. Separate after-update-row triggers fire for each of these rows and each execution inserts a record into the bookings_hist table, so performance is not optimal.
A better approach may be to batch these inserts and insert them in bulk to the bookings_hist table as well. You can accomplish that using a complex series of triggers. The trick is to put the values to be placed in the bookings_hist table in a collection in the row trigger and then load the data from the collection to the bookings_hist table in the after-update-statement trigger, which fires only once. As the actual insert happens only once, the process is faster than inserting on each row.
But these are two different triggers in separate pieces of code. The only way to pass a collection variable from one trigger to the other is to create a package with a collection variable such as VARRAY or PL/SQL TABLE in the package specification, populate it on the after-update row trigger, and read in the after-statement trigger—no easy task. Instead, wouldn't it be simpler if you could place all the triggers in one piece of code?
In Oracle Database 11g you can, using compound triggers. A compound trigger is actually four different triggers defined as one. For instance, an UPDATE compound trigger has a before statement, before row, after statement, and after row all rolled into one compound trigger. This a single piece of code, so you can pass variables just like any other monolithic PL/SQL code.
Let's consider an example. The line numbers are added to aid explanation.
1 create or replace trigger tr_bookings_track
2 for update of booking_dt
3 on bookings
4 compound trigger
5 type ty_bookings_hist is table of bookings_hist%rowtype
6 index by pls_integer;
7 coll_bookings_hist ty_bookings_hist;
8 ctr pls_integer := 0;
9 before statement is
10 begin
11 dbms_output.put_line('In before statement');
12 end before statement;
13 before each row is
14 begin
15 dbms_output.put_line('In before each row');
16 end before each row;
17 after each row is
18 begin
19 ctr := ctr + 1;
20 dbms_output.put_line('In after each row. booking_id='||:new.booking_id);
21 coll_bookings_hist(ctr).booking_id := :new.booking_id;
22 coll_bookings_hist(ctr).mod_dt := sysdate;
23 coll_bookings_hist(ctr).mod_user := user;
24 coll_bookings_hist(ctr).old_booking_dt := :old.booking_dt;
25 coll_bookings_hist(ctr).new_booking_dt := :new.booking_dt;
26 end after each row;
27 after statement is
28 begin
29 dbms_output.put_line('In after statement');
30 forall counter in 1..coll_bookings_hist.count()
31 insert into bookings_hist
32 values coll_bookings_hist(counter);
33 end after statement;
34 end tr_bookings_track;
To better understand the workings of the trigger, let's do a sample update, which updates four rows.
update bookings set booking_dt = sysdate where booking_id between 100 and 103;
Here is the output:
In before statement In before each row In after each row. booking_id=100 In before each row In after each row. booking_id=101 In before each row In after each row. booking_id=102 In before each row In after each row. booking_id=103 In after statement
Note how the compound trigger operates. Roughly, it has four sections:
Before Statement
... executes once before the statement ...
Before Row
... executes once per row before the action ...
After Row
... executes once per row after the action ...
After Statement
... executes once per statement ...
As you'll see, this code is monolithic but each section executes at different points.
In the previous example, I placed dbms_output statements at various points to show how each section executes along what points. I updated four rows, with booking_ids 100, 101, 102, and 103, and you can see it called the before- and after-statement triggers once each and the row triggers (before and after) once per row. (In the previous example, there is no need for before-statement or -row triggers but I have placed them there to illustrate the functionality.)
If you look into the table bookings_hist, you will see that there are now four records—one for each booking_id—but these four records were inserted in bulk at the end of the statement, not for each row updated:
BOOKING_ID MOD_DT MOD_USER OLD_BOOKI NEW_BOOKI
---------- --------- ------------------------------ -------- ---------
100 27-SEP-07 ARUP 28-AUG-07 27-SEP-07
101 27-SEP-07 ARUP 06-AUG-07 27-SEP-07
102 27-SEP-07 ARUP 04-SEP-07 27-SEP-07
103 27-SEP-07 ARUP 15-JUN-07 27-SEP-07
One really useful thing about compound triggers is that stateful objects in PL/SQL code, such as variables, packages and so on, are instantiated when the trigger is fired and at the end of the trigger firing, the state is erased clean. In the above example, you can see that I have neither initialized the collection nor deleted the contents from the collection. All this is done automatically without my intervention.
Ordered Execution in Triggers
Since Oracle8, you have had the ability to define multiple triggers of the same type on one table—e.g., two triggers both after each row on insert on the same table. The type of triggers determine ordering of the execution: before statement, before row, after statement, and after row. However, if you have two after-row triggers—T1 and T2—which one will fire first?
The execution of triggers of the same type is somewhat random or at least not guaranteed to follow a pattern. Does that pose a problem? Let's see an example of a table called PAYMENTS, as shown below:
Name Null? Type ----------------------------------------- -------- ---------------------------- PAY_ID NUMBER(10) CREDIT_CARD_NO VARCHAR2(16) AMOUNT NUMBER(13,2) PAY_MODE VARCHAR2(1) RISK_RATING VARCHAR2(6) FOLLOW_UP VARCHAR2(1)
There is a need to calculate the risk rating from the type of the payments and the amount and store that in the column RISK_RATING. The following simple before update row trigger does the job pretty well:
create or replace trigger tr_pay_risk_rating
before update
on payments
for each row
begin
dbms_output.put_line ('This is tr_pay_risk_rating');
if (:new.amount) < 1000 then
:new.risk_rating := 'LOW';
elsif (:new.amount < 10000) then
if (:new.pay_mode ='K') then
:new.risk_rating := 'MEDIUM';
else
:new.risk_rating := 'HIGH';
end if;
else
:new.risk_rating := 'HIGH';
end if;
end;
/
Now, say someone adds another requirement: some items based on the column RISK_RATING, PAY_MODE, etc. should be flagged for follow-up in a new column called FOLLOW_UP. You could have modified the above trigger but it's always a good policy to leave existing code intact and create a new trigger of the same type (before update row) as shown below. (I have placed the dbms_output statements in the code to show how the triggers are getting fired.)
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
begin
dbms_output.put_line ('This is tr_pay_follow_up');
if (
(:new.risk_rating = 'HIGH' and :new.pay_mode = 'C')
or (:new.risk_rating = 'MEDIUM' and :new.pay_mode = 'K')
or (substr(:new.credit_card_no,1,5) = '23456')
) then
:new.follow_up := 'Y';
else
:new.follow_up := 'N';
end if;
end;
/
Now, if you update the table:
SQL> get upd_pay
1 update payments set
2 credit_card_no = '1234567890123456',
3 amount = 100000,
4* pay_mode = 'K'
SQL> @upd_pay
This is tr_pay_follow_up
This is tr_pay_risk_rating
1 row updated.
SQL> select * from payments;
PAY_ID CREDIT_CARD_NO AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
1 1234567890123456 100000 C HIGH N
What happened here? The column risk_rating is HIGH and the column pay_mode is "C", which means the column FOLLOW_UP should have been "Y" but it's "N". Why? To answer the question, look at the order how the triggers fired: tr_pay_follow_up fired before tr_pay_risk_rating. The latter sets the column value as high risk. So when the former fired, it found a null (or "N") in the risk_rating column and therefore it considers the condition satisfied.
In this case the order of execution of triggers is very important. If tr_pay_risk_rating does not fire before the other one, correct variables will not be set and the set up will fail to implement the needs properly. The only possible way earlier was to place all this logic in one code and force the execution by ordering them in the code.
In Oracle Database 11g, you can place a clause in the trigger creation script that forces the ordering among triggers. Here is the upper portion of the trigger with the clause:
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
follows tr_pay_risk_rating
begin
... and so on ...
This clause (FOLLOWS <triggerName>) forces the trigger to fire after that specified trigger. You can test it by running the update script you saw earlier.
SQL> @upd_pay
This is tr_pay_risk_rating
This is tr_pay_follow_up
1 row updated.
SQL> select * from payments;
PAY_ID CREDIT_CARD_NO AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
1 1234567890123456 100000 C HIGH Y
1 row selected.
The column was properly populated, as expected. Also note the correct ordering of the triggers that confirms what you intended to do.
Ordering of triggers allow you to reap the benefits of the modular code while making sure they get executed in the proper sequence.
When Nothing to Do, CONTINUE
With all its power, until now PL/SQL was missing one important piece of grammar: how to instruct it to do nothing, go to the end of the loop, and loop again.
In Oracle Database 11g PL/SQL has a new construct called CONTINUE, which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop. Here is a small example that shows how the control moves to the end of the loop when the counter is not a multiple of 10.
begin
for ctr in 1..100 loop
continue when mod(ctr,10) != 0;
dbms_output.put_line ('ctr='||ctr);
end loop;
end;
/
Here is the output:
ctr=10 ctr=20 ctr=30 ... and so on ...
Another variation of the CONTINUE is using a Loop Name.
begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line ('-> outer='||outer);
for inner in 1..10 loop
continue OuterLoop when mod(inner,3) = 0;
dbms_output.put_line ('..-> inner='||inner);
end loop;
end loop;
end;
/
Here is the output:
-> outer=1 ..-> inner=1 ..-> inner=2 -> outer=2 ..-> inner=1 ..-> inner=2 -> outer=3 ..-> inner=1 ..-> inner=2 ... and so on ...
Instead of a using static construct such as mod(inner,3), you can also use a function that does some computation for you.
begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line ('-> outer='||outer);
for inner in 1..10 loop
continue OuterLoop when (myfunc = 1);
dbms_output.put_line ('..-> inner='||inner);
end loop;
end loop;
end;
/
<>
Needless to say, you can use this construct only inside a loop where it makes sense. If you attempt to use it outside a loop, you will get a compiler error.
Straight Sequences
When you had to use a sequence in a PL/SQL program earlier, you had to use a construct like SELECT <Seq>.NEXTVAL INTO <VariableName> FROM DUAL prior to this release.
declare
trans_id number(10);
begin
select myseq.nextval
into trans_id
from dual;
end;
Not anymore. You can directly assign the next value of a sequence to a variable:
declare
trans_id number(10);
begin
trans_id := myseq.nextval;
end;
/
Now, that's what I call simplicity.
When OTHERS Then Do Something
Many PL/SQL programmers resort to the dangerous practice of leaving the OTHERS exception ignored, as shown below:
when OTHERS then NULL;
This is like saying "When an error occurs, do nothing; just ignore or pretend it never happened and it will not happen again." If only the world were that simple! This practice leads to potentially buggy, unstable code.
Oracle Database 11g helps a bit in that department. It has a new warning called PLW-06009 to warn you of such a problem during compile time. Here's an example.
create or replace procedure myproc as
l_dummy varchar2(1);
begin
select dummy
into l_dummy
from dual;
exception
when OTHERS then
null;
end;
When you compile this procedure, it compiles fine without a bit of warning, just like the good old 10g days. To enable this warning you have to set this session parameter.
SQL> alter session set plsql_warnings = 'enable:all'
2 /
Session altered.
SQL> @others1
SP2-0804: Procedure created with compilation warnings
SQL> show error
Errors for PROCEDURE MYPROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/7 PLW-06009: procedure "MYPROC" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
Note the new warning PLW-06009, which raises during compilation. Note that this is a warning only; the compilation goes through fine. You can execute the procedure, but consider yourself warned!
Disabled Triggers
In highly available production systems, you often get a narrow change window in which to apply your changes. Here's a common and frustrating "Catch-22" scenario in these environments: You want to add a trigger to a table so you get the script all figured out, but when the trigger is created in that change window, it gives compilation errors due to some silly avoidable reason such as a missing synonym. You wish you could have created the trigger earlier, but when you create a trigger, it's enabled, which is not something you can do outside the change window. What can you do?
In Oracle Database 11g, this scenario is no longer a problem; you can create a trigger as initially disabled, which allows you to test for all compilation errors. Later, during your change window, you can enable it. Here is how you create it:
create or replace trigger tr_t
after insert on t
for each row
disable
begin
insert into t1 (a) values (:new.col_a);
end;
/
Now, if you check the status: SQL> select status 2> from user_triggers 3> where trigger_name = 'TR_T' 4> / STATUS -------- DISABLED Even though the trigger is created as disabled, it must be error free. So if you try to create the trigger with an error (such as using a table "M" that does not exist):
1 create or replace trigger tr_t 2 after insert on t 3 for each row 4 disable 5 begin 6 insert into m (a) values (:new.col_a); 7* end; SQL> / Warning: Trigger created with compilation errors. SQL> show error Errors for TRIGGER TR_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/3 PL/SQL: SQL Statement ignored 2/15 PL/SQL: ORA-00942: table or view does not exist
This feature is very useful for change control procedures. Another great application is to enable triggers at a certain point. For instance, say you are building a auditing solution using triggers and the audit_table has not been purged of old records yet. You can create the triggers as disabled and enable later when the table is ready.
Parameter Names in Function
Consider this simple function:
create or replace function myfunc
(
p_param1 number,
p_param2 number
)
return number
is
begin
return p_param1 + p_param2;
end;
/
The function does a very simple thing but that is enough to demonstrate the concept. Since there are two parameters, you can either call the function by passing parameters as positional values, e.g.:
myfunc (1,2)
Or, as named parameters:
myfunc ( p_param1 => 1, p_param2 => 2)
The latter, however, caused problems if used in select statements. In Oracle Database 10g, if you issue the following statement:
SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;
You will get the error:
select myfunc (p_param1=>1,p_param2=>1) from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
In Oracle Database 11g, you are free to use this notation:
SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;
MYFUNC(P_PARAM1=>1,P_PARAM2=>1)
-------------------------------
2
1 row selected.
...which works perfectly. You can specify the named notation to the last; the first ones must be positional. For instance, the following will be valid, where the parameter p_param1 is set to 1:
select myfunc (1,p_param2=>2) from dual
But this one will not be (the positional parameter is at the end):
SQL> select myfunc (p_param1=>1,2) from dual;
select myfunc (p_param1=>1,2) from dual
*
ERROR at line 1:
ORA-06553: PLS-312: a positional parameter association may not follow a named association
Dynamic Cursor and REF CURSOR Interchangeability
You know how useful a Native Dynamic Cursor can prove to be, especially if you don't know what exactly you are going to query prior to making the call. You may have also used Dynamic PL/SQL using DBMS_SQL. Both methods have their own attractiveness. But what if you started developing a program using one approach and later you want to switch to the other approach?
In Oracle Database 11g, that process is fairly simple. The supplied package DBMS_SQL has a new function, TO_REFCURSOR, which converts the DBMS_SQL dynamic cursor to a ref cursor. Here is an example of such a conversion:
1 create or replace procedure list_trans_by_store 2 ( 3 p_store_id number 4 ) 5 is 6 type num_tab is table of number index by binary_integer; 7 type type_refcur is ref cursor; 8 c_ref_trans_cur type_refcur; 9 c_trans_cur number; 10 trans_id num_tab; 11 trans_amt num_tab; 12 ret integer; 13 l_stmt clob; 14 begin 15 c_trans_cur := dbms_sql.open_cursor; 16 l_stmt := 17 'select trans_id, trans_amt from trans where store_id = :store_id'; 18 dbms_sql.parse(c_trans_cur, l_stmt, dbms_sql.native); 19 dbms_sql.bind_variable(c_trans_cur, 'store_id', p_store_id); 20 ret := dbms_sql.execute(c_trans_cur); 21 c_ref_trans_cur := dbms_sql.to_refcursor(c_trans_cur); 22 fetch c_ref_trans_cur bulk collect into trans_id, trans_amt; 23 for ctr in 1 .. trans_id.count loop 24 dbms_output.put_line(trans_id(ctr) || ' ' || trans_amt(ctr)); 25 end loop; 26 close c_ref_trans_cur; 27* end;
Suppose you want to write a generic procedure where you don't know the column list in the select clause at compile time. This is where the native dynamic SQL comes in handy; you can define a ref cursor for that. Now, to make it more interesting, suppose you don't know the bind variable as well, for which dbms_sql is more appropriate. How can you accomplish this complex requirement with minimal code? Simple: Just start with dbms_sql for the bind part and then convert it to ref cursor later for the other part.
Similarly, if you want to convert a Native Dynamic SQL to REF CURSOR, you will need to call another function, TO_CURSOR_NUMBER:
cur_handle := dbms_sql.to_cursor_number (c_ref_cur);
The ref cursor specified by the variable c_ref_cur must be opened prior to this call. After this call, the life of the ref cursor is over; it can be manipulated only as a dbms_sql cursor.
Suppose you know the binds at compile time but not the select list; you start with native dynamic sql with a ref cursor and later change it to dbms_sql to describe and fetch the columns from the cursor.
Forced Replacement of Types (Release 2 Only)
If you have used types, you must have realized how powerful they can be. You can define your own data type that can be a composite of various other data types, or they can be records to group related pieces of data together, even to match a complete table row. Here is an example of a type called TY_TRANS that defines the elements of a transaction:
create or replace type ty_trans
as object
(
trans_id number(2),
trans_amt number(10)
)
/
Next, you can a type to hold sales information. Since every sale will have a transaction, you can define a column of type ty_trans, shown below:
create or replace type ty_sales
as object
(
sales_id number(2),
trans_rec ty_trans
)
/
Once you define the structures this way, TY_SALES becomes a dependent of TY_TRANS. You can confirm that by querying USER_DEPENDENCIES view:
SQL> select referenced_name, dependency_type 2 from user_dependencies 3 where name = 'TY_SALES' 4 / REFERENCED_NAME DEPE --------------------- ---- STANDARD HARD TY_TRANS HARD
This shows that TY_SALES has a “hard” dependency on the type TY_TRANS.
Now, let’s look at a real world possibility. What if you made a mistake in defining the types and defined an attribute with a wrong precision or just want to change the precision keeping with the business needs? Well, not a problem – you simply use the CREATE OR REPLACE statement to recreate that type:
create or replace type ty_sales
as object
(
sales_id number(3),
trans_rec ty_trans
)
/
Here you recreated the type with the one of the attributes as number(3) instead of number(2), as it was previously. While this operation was successful for this type, what if you had to do the same for ty_trans?
SQL> create or replace type ty_trans 2 as object 3 ( 4 trans_id number (4), 5 trans_amt number 6 ) 7 / create or replace type ty_trans * ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents
The error message says it all – you can’t alter this type since it has a dependent, as we saw earlier from the user_dependencies view. It’s sort of a parent-child relationship between the types. If there is at least one child, you can’t drop the parent. So, what are your options for changing the “parent” type definition?
Until Oracle Database 11g Release 2, the only option for modifying that type is the MODIFY ATTRIBUTE clause of ALTER TYPE statement, which is an expensive and potentially error prone proposition. In Release 2, there is a very convenient FORCE clause to replace the type forcibly. Using this, we can alter the type TY_TRANS as:
create or replace type ty_trans
force
as object
(
trans_id number (4),
trans_amt number
)
/
This will execute successfully and the type will be created, due to the FORCE clause shown in bold above. This makes it very convenient when you deploy applications – you don’t have to worry about which specific attributes have changed; rather, a full replace type takes care of the type definition, changed or not.
Printer View