Skip to main content

Posts

Mutating trigger by example

  Mutating triggers / tables   When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).  In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.     CREATE OR REPLACE TRIGGER equitable_salary_trg    AFTER INSERT OR UPDATE    ON employees    FOR EACH ROW DECLARE    l_max_allowed    employees.salary%TYPE; BEGIN    SELECT MIN (salary) * 25      INTO l_max_allowed      FROM employees;      IF l_max_allowed < :NEW.salary    THEN ...
6 comments
Read more

INSTEAD OF TRIGGER EXAMPLE

 INSTEAD OF TRIGGER EXAMP Instead of trigger is used to provide a  way of modifying views that cannot be modified directly through SQL DML statements because the view is not inherently modifiable.  ex :- composite views . , views that are based on two tables with aggregation functions and etc. You can write INSERT, UPDATE, and DELETE statements against the view.  The INSTEAD OF trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables. example :- the following is a complex view and cannot be modified . create view complex_view as  select  e.deptno,d.dname,sum(e.Sal) sum_sal from emp e join dept d on (e.deptno=d.deptno)  group by e.deptno,d.dname SQL>  select * from complex_view;     DEPTNO DNAME             SUM_SAL ---------- -------------- ----------         10 software            ...
13 comments
Read more

Package example

   Package specification creation   CREATE OR REPLACE PACKAGE MY_PACK IS FUNCTION ADD_NUM(X NUMBER, Y NUMBER) RETURN NUMBER  ; FUNCTION AOR( L NUMBER , B NUMBER) RETURN NUMBER ; PROCEDURE UPDATE_SAL(P_EMPNO NUMBER, P_SAL NUMBER) ; END MY_PACK; Package Body creation CREATE OR REPLACE PACKAGE BODY MY_PACK IS FUNCTION ADD_NUM(X NUMBER, Y NUMBER) RETURN NUMBER IS V_NUM NUMBER; BEGIN V_NUM := X+Y; RETURN V_NUM; END ADD_NUM; FUNCTION AOR( L NUMBER , B NUMBER) RETURN NUMBER IS V_AOR NUMBER; BEGIN V_AOR := L*B; RETURN V_AOR; END AOR; PROCEDURE UPDATE_SAL(P_EMPNO NUMBER, P_SAL NUMBER) IS BEGIN UPDATE EMP SET SAL = P_SAL WHERE EMPNO =P_EMPNO; COMMIT; END UPDATE_SAL; END MY_PACK; / Testing the package SQL> select my_pack.add_num(100, 200) from dual; MY_PACK.ADD_NUM(100,200) ------------------------                      300 SQL> select my_pack.aor(10, 200) from dual; MY_PACK.AOR(10,200) -------------------   ...
1 comment
Read more

PL / SQL practice programs

  PL / SQL  practice programs 1. Write a program to print the following format WELCOME TO PL/SQL PROGRAMMING   BEGIN   DBMS_OUTPUT.PUT_LINE('WELCOME   TO   PL/SQL   PROGRAMMING'); END; /   2. Write a program to print the numbers from 1 to 100   DECLARE N NUMBER(3):=1; V VARCHAR2(1000); BEGIN WHILE N <=1000 LOOP V:=V||''||N; N:=N+1; END LOOP; DBMS_OUTPUT.PUT_LINE(V); END; / 3. write a program to print the even numbers from 1 to 100 DECLARE N NUMBER(3):=0; BEGIN WHILE N <=100 LOOP N:=N+2; DBMS_OUTPUT.PUT_LINE(N); END LOOP; END; / 4. Write a program to print the odd numbers from 1 to 100 DECLARE N NUMBER(3):=1; BEGIN WHILE N <=100 LOOP N:=N+2; DBMS_OUTPUT.PUT_LINE(N); END LOOP; END; / 5. write a program for multiplication table DECLARE A NUMBER(2):=&A; B   NUMBER(2):=1; C ...
5 comments
Read more