OracleByAjay

   Materialized view  CREATE MATERIALIZED VIEW EMP_MV       BUILD IMMEDIATE        REFRESH COMPLETE         ENABLE QUERY REWRITE         AS SELECT DEPTNO, SUM(SAL) FROM EMP    GROUP BY DEPTNO refresh method of a Materialized view exec dbms_mview.refresh('MV1');

  CREATE TABLE AUDIT_EMP  (RCD DATE, EMPID NUMBER(4), old_salary NUMBER(7,2), new_salary NUMBER(7,2) ); SELECT * FROM AUDIT_EMP; CREATE OR REPLACE TRIGGER audit_emp_values     AFTER DELETE OR INSERT OR UPDATE ON emp     FOR EACH ROW     BEGIN     INSERT INTO audit_emp     VALUES ( SYSDATE, :OLD.empno,:OLD.sal, :NEW.sal );    END; UPDATE EMP SET SAL = SAL+100 WHERE EMPNO =7369; SELECT * FROM AUDIT_EMP;

  Compound Trigger     In Oracle 11g, the concept of compound trigger was introduced. A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points: 1.    Before the firing statement 2.    Before each row that the firing statement affects 3.    After each row that the firing statement affects 4.    After the firing statement   With the compound trigger, both the statement-level and row-level action can be put up in a single trigger. Plus there is an added advantage: it allows sharing of common state between all the trigger-points using variable.   CREATE OR REPLACE TRIGGER compound_trigger_name FOR [ INSERT | DELETE ] UPDATE [ OF column ] ON table COMPOUND TRIGGER    -- Declarative Section (optional)    -- Variables declared here have firing-statement duration.         ...

  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 ...

 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            ...

   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) -------------------   ...

  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 ...