Plsql

PL/SQL advantages: ------------1.programming capability 2.conditional checking capability 3.improved performance(network traffic reduces) 4.tight integration with SQL(all the feature of the sql can be used) 5.portability 6.It has the features of exception handling 7.Using plsql we can write 2 type of prgs: --------------------------------------------1.Ananymous blocks These are not stored in database, they are stored in o/s files. they are not named blocks,not reusable,they are standalone. 2.Stored prg or stored units these are stored in db. so they can be referred from db. eg: procedures,functions,packages,triggers These are used in front end applications. structure of Plsql prg: ----------------------declare //declaration section begin //executable stmts; exception; //exception handling section end; Variable declaration: ---------------------syntax: var_name data_type(size); eg: n number(3); name varchar2(10); constant declaration: ----------------------syntax: var_name constant data_type(size); eg: PI constant number(5,3); Initializing the values: ----------------------syntax: var_name data_type(size):=value; eg: course varchar2(20):='java'; Accepting values: ------------------syntax: var_name data_type(size):=&var_name; eg: salary number(7,2):=&salary; Output statement: dbms_output.put_line(variable||'msg'); To see the output : set serveroutput on; 1.sample prg: cl scr; begin

dbms_output.put_line('welcome to pl/sql'); end; press '/' to execute the program type: set serveroutput on; 2.wap to assign a variable and display its value. declare x number; begin x:=100; --initialization dbms_output.put_line('given value is '||x); end; 3.wap to accept a ur name and display it. declare name varchar2(10) ; begin name:='&name'; dbms_output.put_line('your name '||name); end; 4. wap to accept a number and display it. set serveroutput on prompt Enter a number accept a ; declare a number; begin dbms_output.put_line(a); end; / 5.wap to accept 2 numbers and add. declare a number; b number; c number:=0; begin a:=&a; b:=&b; c:=a+b; dbms_output.put_line('the sum '||c); end; 6.wap to find the length of a string. declare name varchar2(10):='&name'; len number; begin dbms_output.put_line('your name '||name); len:=length(name); dbms_output.put_line('its length '||len);

end; / Exercise: 1.wap to accept 3 numbers and find the avg. 2.wap to find the area of rectangle. 3.wap to area of a circle 4.wap to find the primeter of square. //wap to find the length and breadth of rectangle. //wap to display system date Note: To retrieve info from a table: ------------------------------1.SQL : select columnlist from tablename; 2.PL/SQL : select columnlist into duplicate columnlist from tablename; //wap to accept an empno and display his name,job and sal declare eno number:=&eno; name varchar2(10); vjob varchar2(10); salary number; begin select ename,job,sal into name,vjob,salary from emp where empno=eno; dbms_output.put_line(eno||name||vjob||salary); end; /

//wap to accept a ename and display his annual sal. declare name varchar2(10):='&name'; salary number(7,2); begin select (sal+comm)*12 into salary from emp where ename=name; dbms_output.put_line(name||salary); end; / //WAP TO COUNT THE NO.OF EMPS IN A GiVEN DEPT. declare deptnum number:=&deptnum; cnt number; begin select count(empno) into cnt from emp where deptno=deptnum; dbms_output.put_line('No.of employees '||cnt); end; / //WAP TO ACCEPT A STRING AND DISPLAY SOME PART OF IT. cl scr declare

str1 varchar2(50):='&str1'; str2 varchar2(50); begin str2:=substr(str1,5); dbms_output.put_line('str1 '|| str1); dbms_output.put_line('str2 '||str2); end; / //WAP TO ACCEPT 2 STRINGS AND COMBINE THEM. Dynamic declaration of variables: ---------------------------------------Compatability is not there when the variables are declared before. In future anyone can alter the program. Hence to overcome these we use dynamic declaration. 2 ways of declaring variables dynamically. --------------------------------------------1 %type for individual COLS 2 %rowtype for ALL COLS. 1.%type --------//WAP WHICH WILL RETRIEVE THE EMP JOB,SAL,COMM. declare eno emp.empno%type:=&eno; ejob emp.job%type; salary emp.sal%type; cum emp.comm%type; begin select job,sal,comm into ejob,salary,cum from emp where empno=eno; dbms_output.put_line(ejob||' '||salary||' '||cum); end; / practice some programs using above method. 2.%rowtype ------------A single variable is declared which will be adjustED to different datatypes OF A LL COLS. //WAP TO ACCEPT EMPNO DISPLAY ENAME,JOB,SAL,COMM,HIREDATE declare empr emp%rowtype; eno emp.empno%type:=&eno; begin select * into empr from emp where empno=eno; dbms_output.put_line(empr.ename||empr.sal||empr.comm||empr.hiredate); end; / 3.record type: ---------------Collection of different types of variables. syntax: type record_name is record(variable tablename%rowtype,-------------);

record_variable record_name; //WAP A BLOCK TO DISPLAY ENAME, SALARY, JOB. declare type emprec is record(name emp.ename%type, vjob emp.job%type, salary emp .sal%type); e1 emprec; begin select ename,job,sal into e1 from emp where empno=&empno; dbms_output.put_line(e1.name||e1.vjob||e1.salary); end; / Control statements: 1.if --syntax: if conditon then statements; end if; 2.if..else --------syntax: if then statement1; else statement2; end if; 3.elsif...statement. -------------------syantax: if then statement; elsif then statements; elsif then statements; ------------; else statements; end if; //WAP TO TEST FOR +VE NO. declare no number:=&no; begin if no>0 then dbms_output.put_line('it is positive '); end if; end; //WAP TO TEST FOR EVEN OR ODD NO. declare n number :=&n; begin if mod(n,2)=0 then

dbms_output.put_line('even'); else dbms_output.put_line('odd'); end if; end; //WAP TO TEST FOR LEAP YEAR OR NOT. declare year number:=&year; begin if mod(year,4)=0 and mod(year,100)!=0 or mod(year,400)=0 then dbms_output.put_line('leap year'); else dbms_output.put_line('not leap year '); end if; end; //WAP TO ACCEPT EMPNO AND INCREMENT SAL BY 500 IF COMM IS NULL ELSE 200. declare salary emp.sal%type; com emp.comm%type; begin select sal,comm into salary,com from emp where empno=&empno; if com is null then salary:=salary+500; else salary:=salary+200; end if; dbms_output.put_line(salary||' '||com); end; //WAP TO ACCEPT CLERK SALESMAN ANALYST MANAGER 1500 OTHERS

EMPNO AND BASED ON JOB INCREMENT SAL AS FOLLOWS. 500 700 1000 200

declare salary emp.sal%type; sal1 emp.sal%type; vjob emp.job%type; begin select sal,job into sal1,vjob from emp where empno=&empno; salary:=sal1; if vjob like 'clerk'then salary:=salary+500; elsif vjob like 'salesman' then salary:=salary+700; elsif vjob like 'analyst' then salary:=salary+1000; elsif vjob like 'manager' then salary:=salary+1500; else salary:=salary+200; end if; dbms_output.put_line(vjob||' '||sal1); dbms_output.put_line('incremented salary '||salary);

end; Loop control statements: 1.loop 2.while loop 3.for loop 1.loop statement; syntax: loop statements; exit when end loop; 2.while loop syntax: while loop statements; end loop; 3.for loop syntax: for variable in value1..valueN loop statements; end loop; //WAP TO PRINT 1 TO 100 declare i number(3):=1; begin loop dbms_output.put_line(i); i:=i+1; exit when i>100; end loop; end; //WAP TO PRINT EVEN NOs UP TO 100 declare i number(3):=1; begin loop if mod(i,2)=0 then dbms_output.put_line(i); end if; i:=i+1; exit when i>100; end loop; end; //WAP TO print SUM OF FIRST 100 NATURAL NOs using while. declare i number:=1; s number:=0; begin while(i<=100)

loop s:=s+i; i:=i+1; end loop; dbms_output.put_line('sum is '||s); end; //WAP TO FIND THE SUM OF THE digits of a GIVEN NUMBER declare n number:=&n; r number; s number:=0; begin while(n>0) loop r:=mod(n,10); s:=s+r; n:=floor(n/10); end loop; dbms_output.put_line(s); end; //WAP TO PRINT 1 TO 10 USING FOR LOOP. declare i number; begin for i in 1..10 loop dbms_output.put_line(i); end loop; end; Exercise: 1. wap to 2. wap to 3. wap to 4. wap to 5. wap to

find the factorial of a number test for prime number test for armstrong number reverse the digits of a number. print fibonacci series.

Cursors: --------Cursor is context area which holds the records.It is a private SQL area. cursors are used when a select stmt returns more than one row from the db. A cursor is a set of rows that we can access one at a time. We retrieve the rows into the cursor using select stmt and then fetch the rows from the cur sor. steps -----1.declare variable 2.declare cursor 3.open the cursor 4.fetch the rows from cursor 5.close the cursor Cursors are 2 types: 1.implicit cursor 2.explicit cursor implict cursors are used by oracle when SQL stmt is processed. explict cursors are used by the user.

The rows held in a cursor are called active set. It is also know as hand ler or pointer Cursor uses ------------1.open -to open a cursor 2.fetch -to fetch the records 3.close -to close the cursor these are carried by oracle implicitly in implicit cursors. Cursor Attributes ------------------1.%isopen-checks whether cursor is open or not 2.%found 3.%notfound 4.%rowcount-returns no. of row in a cursor syntax: declare cursor cur_name is select stmt; cur_variable cur_name; begin open cur_name; loop fetch cur_name into cur_variable; exit when cur_name%notfound; stmts; end loop; close cur_name; end; //DECLARE A CURSOR TO DISPLAY THE EMP INFO. declare cursor c1 is select * from emp; cv c1%rowtype; begin open c1; loop fetch c1 into cv; exit when c1%notfound; dbms_output.put_line(cv.ename||cv.job||cv.sal); end loop; close c1; end; //DECLARE A CURSOR WHICH DISPLAY INFO SUCH AS ENAME,JOB,SAL,DEPTNO,Dname,Loc,gra de. declare cursor c1 is select ename,sal,job,d.deptno,dname,loc,grade from emp e,dept d,sal grade s where d.deptno=e.deptno and sal between losal and hisal; cv c1%rowtype; begin open c1; loop fetch c1 into cv; exit when c1%notfound; dbms_output.put_line(cv.ename||cv.sal||cv.job||cv.deptno||cv.dname||cv.loc||cv.g

rade); end loop; close c1; end; / //DECLARE A CURSOR TO DISPLAY THE MAX SAL OF EMP TABLE. declare cursor c1 is select sal from emp; salary emp.sal%type:=0; cv c1%rowtype; begin open c1; loop fetch c1 into cv; if cv.sal>salary then salary:=cv.sal; end if; exit when c1%notfound; end loop; close c1; dbms_output.put_line('the max sal '||salary); end; //DECLARE A CURSOR TO DISPLAY THE NO.OF RECORDS IN EMP TABLE. declare cursor c1 is select * from emp; cv c1%rowtype; begin open c1; loop fetch c1 into cv; exit when c1%notfound; end loop; dbms_output.put_line('the no of rows '||c1%rowcount); close c1; end; Cursor for loops: -----------------Here we are not needed to open,fetch,and close a cursor. syntax: declare cursor cur_name is select stmt; begin for cur_variable in cur_name; loop statements; end loop; end; //WAP TO PRINT NAME,SAL OF ALL EMPs. declare cursor c1 is select * from emp; begin for cv in c1 loop dbms_output.put_line(cv.ename||cv.sal);

end loop; end; Parameterised cursors: ------------------------syntax: declare cursor cur_name(parameter dt size) is select stmt; cur_var cur_name ; begin open cur_name(parameter) loop stmts; fetch cur_name into cur_var; exit when condition; end loop; close cur_name; end; //WAP TO ACCEPT DEPTNO AND DISPLAY EMP NAMES. declare cursor c1(dno number) is select * from emp where deptno=dno; cv c1%rowtype; dep number:=&deptno; begin open c1(dep); loop fetch c1 into cv; dbms_output.put_line(cv.ename); exit when c1%notfound; end loop; end; Implicit cursors: -----------------This type of cursors are created and handled by oracle automatically. They uses the following attributes. 1.SQL%found 2.SQL%notfound 3.SQL%isopen 4.SQL%rowcount eg:

select * from emp;

NOTE : In the above command oracle automatically opens the cursor and closes. Therefore SQL%isopen is always false. //WAP TO TEST WHETHER CURSOR %isopen is opened or closed. begin if SQL%isopen then dbms_output.put_line('Hai...opened'); else dbms_output.put_line('Bye...closed'); end if; end;

//DECLARE A CURSOR TO FIND THE SUM OF THE SALARIES. declare salary emp.sal%type:=0; s emp.sal%type:=0; cursor c1 is select sal from emp; begin open c1; loop fetch c1 into salary; exit when c1%notfound; s:=s+salary; end loop; close c1; dbms_output.put_line('the sum of salaries :'|| s); end; EXCEPTION HANDLING ------------------------Abnormal termination of normal flow of program.Its a run time error.exceptions a re also raised due to hardware problems. Exceptions are 2 types: ------------------------1.predefined exceptions 2.User defined exceptions 1.predefined exceptions: --------------------------These are supplied by system, stored in standard package available to all progra ms. Handled automatically by system. eg: no_data_found; zero_divide,cursor_already_open,invalid_cursor, prog_error: It occurs when function created without return stmt; value_error: too many rows storage_error: no data found invalid_number: attempting to convert from string to number others: -------Its a exception handler. It must be the last stmt in the prg. syntax: declare -----------; begin exe stmts; exception when then exp_handler1; when then exp_handler2; --------------------------------------------; when others then stmts; end; prg without exception handler declare a number:=&a; b number:=&b;

c number; begin c:=a/b; dbms_output.put_line(c); dbms_output.put_line('end of program'); end; / Above prg :HANDLING ZERO_DIVIDE. declare a number:=&a; b number:=&b; c number; begin c:=a/b; dbms_output.put_line(c); exception when zero_divide then dbms_output.put_line('division by zero not possible'); end; //HANDLING NO_DATA_FOUND declare emprec emp%rowtype; begin select * into emprec from emp where empno=&empno; dbms_output.put_line(emprec.ename); exception when no_data_found then dbms_output.put_line('no such employee'); end; --demonstrate To Too_many_rows Exception Declare Z Emp%rowtype; Begin Select * Into Z From Emp Where Deptno = 20; Dbms_output.put_line(z.empno||z.ename||z.deptno); Exception When Too_many_rows Then Dbms_output.put_line('too Many Rows In Row Type Variable'); End; / --cursor_already_open Declare Cursor S Is Select Ename From Emp; Ena Emp.ename%type; Begin Open S; Loop Fetch S Into Ena; Exit When S%notfound; Dbms_output.put_line(ena); End Loop; Open S;--opened Again

Exception When Cursor_already_open Then Dbms_output.put_line('cursor Should Not Open More Than One Time'); End; / --value_error Declare N Number(3);--3digits Begin N:=&number; Dbms_output.put_line('sqr Value Is ' ||(n*n)); Exception When Value_error Then Dbms_output.put_line('value Is Larger Than Given Width'); End; / 2.User defined exceptions: ----------------------------Declared in program by user. Available to only particular prg. They must be rais ed using 'raise' stmt. These can be created using exception key word. syntax: declare exp_name exception ; -----------------------; begin -----------------------; raise exp_name; writing an handle: exception when exp_name then statements; -----------------------; end; --WAP TO ACCEPT A NUMBER.RAISE AN EXCEPTION IF IT NOT LIES B/W 0 & 100. declare Orbit exception; a number:=&a; begin if a<0 or a>100 then raise Orbit; else dbms_output.put_line('The value is '||a); end if; exception when Orbit then dbms_output.put_line('Enter bw 0 and 100'); dbms_output.put_line('Bye....'); end; / --wap to accept ename. raise an exp 1.no commision if comm is null 2. no such em p if name is not found 3. else other exp. declare emprec emp%rowtype; name emp.ename%type:='&name';

no_comm exception; begin select * into emprec from emp where ename=name; if emprec.comm is null then raise no_comm; else dbms_output.put_line(emprec.empno||' '||emprec.job||' '||emprec.sal||' ' ||emprec.comm||' '||emprec.deptno); end if; exception when no_comm then dbms_output.put_line('no comm for such emp'); when no_data_found then dbms_output.put_line('No such emp'); when others then dbms_output.put_line('Cant retrieve more than 1 row..'); end; / --To withdraw the amt from the bank. Raise an exception to withdraw only if the min balance is atleast 500 --create table bank(acct number,name varchar2(20),balance number(7,2)) / insert into bank values(101,'ramesh',3000); insert into bank values(102,'shiva',1000); insert into bank values(103,'micky',2000); declare no bank.acct%type:=&acct; amt bank.balance%type:=&amt; bal number; NoBalance exception; begin select balance into bal from bank where acct=no; dbms_output.put_line('amt to withdraw'||amt); if(bal-amt>500) then update bank set balance=bal-amt where acct=no; else raise NoBalance; end if; exception when NoBalance then dbms_output.put_line('No min balance '); end; Stored Programs: ------------------adv: -supports reusability and modularity -easy maintainance -reducing coding -performance is improved eg: procedures,functions,packages ,triggers Procedures and functions are a series of executable statements and are present i n data base. These are more useful while developing the applications

A function can return a value but a procedure cannot return a value technically, but returns more than 1 value using out parameter. Procedure :syntax: ----------create or replace procedure p_name(parameters in/out/in out datatype) is/as loca l declaration; begin executable stmts; exception exception handling section end pro_name; //SAMPLE PROCEDURE. create or replace procedure pro1 as begin dbms_output.put_line('welcome to procedures... '); end pro1; / Procedure can be executed in 2 ways using 1.execute command syntax: execute (args); eg: execute pro1; or exec pro1; 2.client program. Procedures without parameter :Disadvantage : If we want to use same procedure more than once ,it must be compiled and execute d once again. This can be overcome by using procedure with parameters. //WAP TO ACCEPT A NUMBER AND DISPLAY. create or replace procedure pro2(a number) as begin dbms_output.put_line('entered value '||a); end; / execution: if saved in a file compile as @ file_name; exec pro2(&a); PARAMETER MODES: ----------------------It always refers behaviour of the parameter. These are 3 types. 1.in 2.out 3.in out IN : It is the default mode which allows us to accept the value with a procedure para meter. OUT : This allows us to return the value from a procedure to the caller of the procedu re which behaves as uninitialized value.

IN OUT : -----------This allows us to accept the value and to return the value. It is a initalized v ariable. //CREATE A PROCEDURE ADD1 TO ADD TWO INTEGERS. create or replace procedure add1(a in number, b in number) is c number; begin c:=a+b; dbms_output.put_line('the sum= '||c); end; / execution: @ prg_name; exec add1(10,20); //WRITE ABOVE PRG TO RETRUN THE SUM. create or replace procedure add2(a in number, b in number,c out number) is begin c:=a+b; end; / EXECUTION: 1.@ prg_name; var n number;//Binded variable exec add2(10,20,:n); print n; 2.client program. declare a number:=&a; b number:=&b; c number; begin add2(a,b,c); dbms_output.put_line('the sum '||c); end; / //USING IN/OUT PARAMETER. create or replace procedure pro5(a number, b in out number) as begin b:=a*b; end; / client program: declare a number:=&a; b number:=&b; begin pro5(a,b); dbms_output.put_line('the product is '||b); end; /

--to delete records of emp1 table. create or replace procedure p5 as eno number:=&eno; begin delete from emp1 where empno=eno; end; execution: 1.@ pro5 exec p5; 2.client prog declare begin p5; end; --procedure with parameters create or replace procedure p2(eno in emp1.empno%type) as begin delete from emp1 where empno=eno; end; client prg: declare eno number:=&eno; begin p2(eno); end; --create a procedure that accepts a deptno & gives the no of employs in that dep tno create or replace procedure p3(dno in emp.deptno%type,cnt out number) as begin select count(empno) into cnt from emp where deptno=dno; end; / --execution --var n number; --exec p3(10,:n); --print n; client program: declare n number; dno emp.deptno%type:=&dno; begin p3(dno,n); dbms_output.put_line('the no of emps are '||n); end; / FUNCTIONS: -------------They return a value to the called environment. Every function can have default a rguments and function can accept parameters. syntax:

create or replace function fun_name(parameters in/out/in out data_type) return d atatype is/as variable var_name; begin executable stmt; return var; exception exception handling section; end [fun_name]; //FUNCTION WITHOUT PARAMETERS: //WAP TO DISPLAY THE NO. OF EMPS IN A DEPARTMENT. create or replace function f1 return number is deptnum emp.deptno%type:=&deptnum; n number; begin select count(*) into n from emp where deptno=deptnum; return n; end; EXECUTION: @ file var n number exec :n:=f1; print n; //FUNCTION WITH PARAMETERS: //WRITE THE ABOVE PRG BY PASSING DEPTNO. create or replace function f3(deptno1 in number default 10) return number is n number; begin select count(*) into n from emp where deptno=deptno1; return n; end; execution: @ file_name; var n number; exec :n:=f3(20); print n; Q: Find the area of rectanlge. create or replace function rect(l in number,b in number) return number as area number; begin area:= l*b; return area; end; / execution var n number exec :n:=rectanlge(10,5); print n; --wap which accepts the job and returns no of employs in that job

create or replace function f2(vjob in emp.job%type) return number as cnt number; begin select count(*) into cnt from emp where job=vjob; return cnt; end f2; Functions are executed in 3 ways 1.by execute command 2.by selecting function from dual 3.by writing client prg --execution --1. var n number; --execute :n:=f2('MANAGER') ; --print n; --2.select f2('MANAGER') from dual; --3.client prg declare m number; vjob emp.job%type:='&vjob'; begin m:=f2(vjob); dbms_output.put_line('the no of emp s '||m); end; / //WAP to ACCEPT EMPNO AND RETURN HIS EXPERIENCE. create or replace function fexp(eno in emp.empno%type) return number is n number; begin select (months_between(sysdate,hiredate)/12) into n from emp where empno=eno; return n; end; EXECUTION: 1.var x number; exec :x:=fexp(7876); print x; 2.select fexp(7839) from dual; 3.declare no number:=&no; begin no:=fexp(no); dbms_output.put_line('the experience '||no); end; confirm procedures,functions: select name,type from user_source; confirm procedures: select * from user_procedures

To drop procedure: drop procedure To drop function: drop function PACKAGES: ------------It is a database object which contains procedures, functions,cursors,exceptions etc; -->packages are not executed but the things in packages -->packages does not have parameters. Creation of packages in 2 steps: ---------------------------------1. Specification of package 2. Body of package. Specification sepcifies things to be excuted. These are available to dif ferent users. Body provides definitions for the things specified in Specification. Specification syntax: ---------------------create or replace package pkg_name as procedure pro_name1(parameter in/out/in out dt); procedure pro_name2(parameter in/out/in out dt); function fun_name1(parameter) return dt; function fun_name2(parameter) return dt; end pkg_name; Body syntax: -------------create or replace package body pkg_name as //Define procedures //Define functions etc. end pkg_name; Q: wap to create a pkg consisting of 2 functions 1.package specification create or replace package pak_fun as function sqr( m in number) return number; function cube(n in number) return number; end; / 2.package body create or replace package body pak_fun as function sqr(m in number) return number is begin return m*m; end sqr; function cube(n in number) return number is begin return n*n*n; end cube; end pak_fun; /

3.client prg for above: declare x number(4); begin x:=&number; dbms_output.put_line('square value '||pak_fun.sqr(x)); dbms_output.put_line('cube value '||pak_fun.cube(x)); end; / --create a package which contains a function to check deptno exists or not and p rocedure to retrieve rows. create or replace package emp_trans as function dcheck(dno in number) return number; procedure eprint(dno in number); end; / create or replace package body emp_trans as function dcheck(dno in number) return number is d emp.deptno%type; begin select distinct deptno into d from emp where deptno=dno; return 0; exception when no_data_found then return 1; end dcheck; procedure eprint(dno in number) as cursor s is select * from emp where deptno=dno; z emp%rowtype; begin open s; loop fetch s into z; exit when s%notfound; dbms_output.put_line(z.ename||z.deptno); end loop; end eprint; end emp_trans; / client prg for above: declare dno emp.deptno%type; begin dno:=&deptno; if(emp_trans.dcheck(dno)=0) then emp_trans.eprint(dno); else dbms_output.put_line('deptno not exist'); end if; end; / drop package: drop package pkg_name;

TRIGGERS: -------------It is a PL/SQL object which executes on its own, when an event occurs. An event may be DML or DDL operation. Adv: ----1.A trigger can audit the information(who made the changes) 2.High level security can be imposed. 3.Complex business rules can be imposed(high level restriction) 4.Trigger can permit accessibility on a table for a particular time. 5. Triggers does not need user interaction, as these are individual objects. Elements of trigger: ---------------------1. trigger event 2. trigger restriction 3. trigger body:-PL/SQL block syntax: create or replace trigger trg_name before/after/insteadof dml event (insert/update/delete) ddl event on table_name; for each row when condition; begin --executable stmt end; Two levels of triggers: -----------------------1. Row level triggers: fires on each row. 2. Statement level triggers: fires automatically once on all the rows in a trans action. Types of triggers: ------------------1. DML triggers: fires when DML operation is performed. 2. System Triggers: fires when a change occurs in schema. Trigger predicates: --------------------1. insert 2. delete 3. update These identify the action performed on the table. pseudo columns/correlation name: 1. :old It indicates the value present before update/delete 2. :new It indicates the value after insert. These are useful to track the changes made to the table. //1.WAT BEFORE INSERTING DATA INTO TABLE. create or replace trigger trg1 before insert on emp1 for each row begin dbms_output.put_line('one rec inserted ..orbit'); end;

/ //2.WAT BEFORE DELETING DATA INTO TABLE. //3.WAT BEFORE UPDATING DATA INTO TABLE. //4.WAT BEFORE INSERT/DELETE/UPDATE'ING A TABLE create or replace trigger trg4 before insert or update or delete on emp1 for each row begin if inserting then dbms_output.put_line('1 record inserted...ramesh'); elsif updating then dbms_output.put_line('record(s) updated..ramesh'); else dbms_output.put_line('record(s) deleted... ramesh'); end if; end; / //5.WAT, ACCEPTING DOJ. IF DOJ !=SYSDATE, REPLACE DOJ WITH SYSDATE. create or replace trigger trg5 before insert or update on emp2 for each row when(new.hiredate<>sysdate) begin if :new.hiredate<>sysdate then :new.hiredate:=sysdate; end if; dbms_output.put_line('doj recovered....'); end; / //6. create or replace trigger t7 before insert or update on emp2 for each row begin if :new.sal>3000 then :new.sal:=5000; elsif :new.sal>2000 then :new.sal:=2000; end if; end; / //7. Resctriction on time create or replace trigger t9 before insert or update or delete on emp2 for each row begin if(to_number(to_char(sysdate,'hh24')) between 10 and 20) then raise_application_error(-2002,'sorry....transaction not allowed between 10 and 2 0..'); end if; end; / //8.Restriction on week days create or replace trigger t10 before insert or update or delete on emp2 for each row begin if to_char(sysdate,'dy') in('fri','sat','wed') then

raise_application_error(-2001,'sorry....!. transactions are not allowed on week ends...'); end if; end; / confirm triggers: select trigger_name,table_name from user_triggers; Drop trigger: drop trigger ; Enable/disable triggers: alter trigger trg_name enable/disable; * for a table any no of triggers can be written. CODD RULES ------------------12 CODD RULES were proposed by EF.codd 1.The information rule: specifies that the info. must be logically represented in exactly at one place i n a systamatic way in rows and cols. 2.The rule of guaranteed access: The user must be in a position to access the data from the data base table. 3.The systematic treatment of NULL values. It specifies that the null values must not depend upon on the data type columns. And null is different from 0. 4.The database description rule. Description of the database must be maintained in systematic manner. 5.The comprehensive sub language rule: 1. Data definiton 2. View definition 3.Data manipulation 4.Integrity constraints. 5.Authorization 6.Transaction boundaries. 6.The view updated rule: The user must be in a position to update a view. 7.The insert and update rule: After creating a table, the user must be in a position to insert and update the table. 8.The physical independence rule: The user or the programmer must be given freedom to manipulate the table. 9.The logical data independenc rule: Logical changes in tables and views such adding/deleting columns or changing fie lds lengths need not necessitate modifications in the programs or in the format of adhoc requests. The database can change and grow to reflect changes in realit y without requiring the user intervention or changes in the applications. For ex ample, adding attribute or column to the base table should not disrupt the progr ams or the interactive command that have no use for the new attribute.

10.The integrity independence rule: Like table/view definition, integrity constraints are stored in the on-line cata log and can therefore be changed without necessitating changes in the applicatio n programs. Integrity constraints specific to a particular RDB must be definable in the relational data sub-language and storable in the catalog. At least the E ntity integrity and referential integrity must be supported. 11.The distribution rule: Application programs and adhoc requests are not affected by change in the distr ibution of physical data. Improved systems reliability since application progra ms will work even if the programs and data are moved in different sites. 12.No subversion rule: It specifies that the higher level database product should not bypass the integr ity constraint to lower level when we are making database construct. Rule zero: -------It specifies that RDBMS product must be an RDBMS product, but it should not look like RDBMS. EG: foxpro : looks like RDBMS but not RDBMS. NORMALIZATION: -------------------------A mechanism used to reduce the repitition of the data. First Normal Form: -------------------Follow functional dependency. Item:

itno,Itname,Itprice.

Second Normal Form: -----------------------First it must follow 1st NF. It follows full functional dependence i.e., all the non key attributes must be f ully functionally dependent upon 1 key attribute. eg: emp table: empno -pk->key attribute ename job sal

-non key attributes.

Third Normal Form: ---------------------First it must follow 2nd NF. It eliminates Transitive dependence. if a>b, b>c =>a>c A-> B-> C | | ----------EG: dept:

emp:

deptno dname dloc

empno ename job

sal deptno