This is the concluding part of the 2 part series on PL/SQL enhancements in 11g. Here are few more useful enhancements in PL/SQL :-
1. Usage of Named and mixed notation with PL/SQL subprograms in a SQL statement
Prior to 11g, to use a sub-program (e.g. function) in a SELECT statement you have to provide the real parameters in positional notation. Let’s look at an example using both 10g and 11g to understand it better.
Here is a small piece of code which calculates the years of employment of an employee :-
CREATE OR REPLACE FUNCTION cal_employment_duration (
empid IN NUMBER,
as_on IN DATE
)
RETURN NUMBER
IS
vduration NUMBER;
BEGIN
SELECT MONTHS_BETWEEN (as_on, hiredate) / 12
INTO vduration
FROM emp
WHERE empno = empid;
RETURN ROUND (vduration);
END;
/
Now I call this function using a SELECT statement in Oracle 10g. Observe the difference when I use named and mixed notation :-
SIPRAS@ORA10G> SELECT empno, hiredate,
2 cal_employment_duration (empno, SYSDATE) || ' Years' "Employee Tenure"
3 FROM emp
4 WHERE empno = 7788
5 /
EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years
SIPRAS@ORA10G> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 as_on => SYSDATE
4 )
5 || ' Years' "Employee Tenure"
6 FROM emp
7 WHERE empno = 7788;
cal_employment_duration (empid => empno,
*
ERROR at line 2:
ORA-00907: missing right parenthesis
Now lets use the same example in Oracle 11g :-
SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empno, SYSDATE) || ' Years' "Employee Tenure"
3 FROM emp
4 WHERE empno = 7788;
EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years
Now lets use named notations and see if it works or not:-
SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 as_on => SYSDATE
4 )
5 || ' Years' "Employee Tenure"
6 FROM emp
7 WHERE empno = 7788;
EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years
SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empno, as_on => SYSDATE)
3 || ' Years' "Employee Tenure"
4 FROM emp
5 WHERE empno = 7788;
EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years
Great! it works, except in the following case; Note the error message :-
SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 SYSDATE)
4 || ' Years' "Employee Tenure"
5 FROM emp
6 WHERE empno = 7788;
cal_employment_duration (empid => empno,
*
ERROR at line 2:
ORA-06553: PLS-312: a positional parameter association may not follow a named
association
Not sure why this was left out of the enhancements.
2. Trigger Enhancements: A couple of enhancements have been done to triggers. I have captured all of them with examples below.
i) You can now ENABLE or DISABLE triggers at creation time e.g
CREATE TRIGGER test_trig
BEFORE INSERT ON EMP
DISABLED/ENABLED
ii) Compound trigger: A new trigger type called “compound” has been introduced. It basically implements actions for all of the DML timing points in a single trigger. Action for each timing point “BEFORE STATEMENT”, “AFTER STATEMENT”, “BEFORE EACH ROW” and “AFTER EACH ROW” can now be written in a single trigger. Here is an example of :-
This trigger tracks updates to the “sal” column; It (a) assigns an update_id, notes start and end time for each update statement (b) keeps old and new salary for every row updated. All of this done with a single trigger.
CREATE OR REPLACE TRIGGER track_sal_upd
FOR UPDATE OF sal ON emp
COMPOUND TRIGGER
max_sal_upd_id number(4);
BEFORE STATEMENT IS
BEGIN
select nvl(max(sal_upd_id),0) into max_sal_upd_id from sal_update_log;
max_sal_upd_id := max_sal_upd_id + 1;
insert into sal_update_log values(max_sal_upd_id,null,null,null,systimestamp,'salary update process '||max_sal_upd_id||' started');
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
insert into sal_update_log values(max_sal_upd_id,:old.empno,:old.sal,:old.sal,systimestamp,'updated by process '||max_sal_upd_id);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
insert into sal_update_log values(max_sal_upd_id,null,null,null,systimestamp,'salary update process '||max_sal_upd_id||' finished');
END AFTER STATEMENT;
END track_sal_upd;
/
iii) Ordering of triggers: You can now control the order in which the triggers on a table would get fired. Oracle 11g has introduced a new clauses “FOLLOWS” to implement this feature. It will allow you to control the order in which the triggers fire when you have multiple triggers of same type on the same table. Oracle randomly picks up the triggers (if multiple triggers of same type on same table exist) if FOLLOWS clause is not used, which was the case prior to 11g.
Here an example which uses FOLLOWS clause. In this example, we have 2 triggers – first one “check_sal” is only for updates to SAL column whereas second one “check_update_job” will fire on updates to JOB & SAL column. Mark the FOLLOWS clause on “check_sal” trigger. It states that check_sal trigger should fire only after “check_update_job” has fired..
CREATE OR REPLACE TRIGGER check_update_job
BEFORE UPDATE OF job,sal
ON emp
FOR EACH ROW
DECLARE
v_start_range NUMBER (7, 2);
v_end_range NUMBER (7, 2);
BEGIN
IF :OLD.job IN ('CLERK','SALESMAN') THEN
IF :NEW.JOB != 'MANAGER' THEN
raise_application_error (num => -20001,
msg => 'Cannot change job to ' || :NEW.job
);
END IF;
END IF;
END;
/
CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
FOLLOWS check_update_job
DECLARE
v_start_range NUMBER (7, 2);
v_end_range NUMBER (7, 2);
BEGIN
SELECT start_range, end_range
INTO v_start_range, v_end_range
FROM sal_range
WHERE job = :NEW.job;
IF :NEW.sal NOT BETWEEN v_start_range AND v_end_range
THEN
raise_application_error (num => -20000,
msg => 'Salary is not in the prescribed range'
);
END IF;
END;
/
SIPRAS@11glab> select empno,job,sal from emp where empno=7369;
EMPNO JOB SAL
---------- --------- ----------
7369 CLERK 800
SIPRAS@11glab> update emp set sal=500, job='PRESIDENT' where empno=7369;
update emp set sal=500, job='PRESIDENT' where empno=7369
*
ERROR at line 1:
ORA-20001: Cannot change job to PRESIDENT
ORA-06512: at "SIPRAS.CHECK_UPDATE_JOB", line 7
ORA-04088: error during execution of trigger 'SIPRAS.CHECK_UPDATE_JOB'
See the above error, it fired “check_update_job” first. In releases prior to Oracle 11g, you would not be able to control this.
3. Enhancements to PL/SQL Native compilation
Prior to Oracle 11g, initialization parameters were used to setup native compilation of PL/SQL code. However starting 11g, only one parameter “PLSQL_CODE_TYPE” needs to be set. It can be set to “INTERPRETED” (default value) or “NATIVE”. This parameter can be set at session level (ALTER SESSION), system level (ALTER SYSTEM) and for specific subprograms too using ALTER PROCEDURE. We all know, native compilation help improve the speed of PL/SQL programs as it compiles them to native code. It is of great help when your code consists of lot of loops, calculations, branches etc.
The trigger enhancements are really cool! I particularly liked the compound trigger feature the most. Share your thoughts on what you like…This was the concluding part of the PL/SQL enhancement series. Here is the link to
first post in this series.
![Reblog this post [with Zemanta]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_v37vfQ8urVo5cfnTT0Kcw3dm9867pEyKcexdt8_NtU0Jm5rLGGN8-6vJ949Uj5PG5dMVMLFgMINuLTkTatyaELvC-yHIRCWeIvzxaNpqLMssEDO7HTHrxSEiQQpb8WCDmqfpS8IC-gMLYUVtynrDk=s0-d)
