Download
1 / 19
190 likes | 395 Views
PL/SQL. Introduction to PL/SQL block. Declare declarations Begin executable statement Exception exception handlers End;. Example. Declare a emp := emp( 10, ‘raja’, 22000, 10); Begin Dbms_output.put_line(‘The emp no is’|| a.eno); Dbms_output.put_line(‘The emp name is’|| a.ename);
E N D
Introduction to PL/SQL block Declare declarations Begin executable statement Exception exception handlers End;
Example Declare a emp := emp( 10, ‘raja’, 22000, 10); Begin Dbms_output.put_line(‘The emp no is’|| a.eno); Dbms_output.put_line(‘The emp name is’|| a.ename); Dbms_output.put_line(‘The emp salary is’|| a.salary); Dbms_output.put_line(‘The emp department no is’|| a.dno); End; Output The emp no is 10 The emp name is raja The emp salary is 22000 The emp department no is 10
Attributes % type % type attribute is used when declaring variables that refer to the database columns. %rowtype %row type attribute provides a record type that represents a row in a table.
Control Structures • Conditional control • Iterative control • Sequential control
Conditional control If condition then Sequence of statements; End if; If condition then Else Sequence of statements 1; Else Sequence of statements 1; End if;
Iterative control • Simple loop • For loop • While loop
Simple loop Loop Sequence of statements ; End loop; Example Sum of 100 to 200 Declare S number :=0; A number :=100; Begin Loop S:=S+A; A:=A+1; Exit when a>200; End loop Dbms_output.put_line(“Sum of 100 to 200”|| sum);
For loop For counter in [reverse] lowerbound .. Upperbound Loop Sequence_of_statements; End loop;
While loop Syntax While <condition> Loop Sequence_of-statements; End loop; Example Declare S number :=0; I number :=0; Begin While i<=100 loop S=S+I; I=I+1; End loop Dbms_output.put_line (“Sum of 1 to 100 is :” || s); End
WORKING WITH PL/SQL BLOCKS: SQL> set serveroutput on; SQL> declare 2 a number:=10; 3 begin 4 dbms_output.put_line(a); 5 end; 6 / 10 PL/SQL procedure successfully completed. SQL> declare 2 a number:=10; 3 begin 4 loop 5 a:=a+20; 6 exit when a = 30; 7 end loop; 8 dbms_output.put_line(a); 9 end; 10 / 30 PL/SQL procedure successfully completed.
SQL> set serveroutput on; SQL> declare 2 name varchar2(20); 3 m1 number(3); 4 m2 number(3); 5 t number(3); 6 r varchar2(10); 7 begin 8 m1 := &mark1; 9 m2 := &mark2; 10 t :=m1+m2; 11 if ( m1>=50 and m2>=50) then 12 r :='Pass'; 13 else 14 r:='Fail'; 15 end if; 16 dbms_output.put_line('Student details'); 17 dbms_output.put_line('Mark1 - Marks2 - total - Result'); 18 dbms_output.put_line(m1); 19 dbms_output.put_line(m2); 20 dbms_output.put_line(t); 21 dbms_output.put_line(r); 22 end; 23 /
Enter value for mark1: 99 old 8: m1 := &mark1; new 8: m1 := 99; Enter value for mark2: 90 old 9: m2 := &mark2; new 9: m2 := 90; Student details Mark1 - Marks2 - total - Result 99 90 189 Pass PL/SQL procedure successfully completed. SQL> / Enter value for mark1: 40 old 8: m1 := &mark1; new 8: m1 := 40; Enter value for mark2: 60 old 9: m2 := &mark2; new 9: m2 := 60; Student details Mark1 - Marks2 - total - Result 40 60 100 Fail PL/SQL procedure successfully completed.
goto statement declare A number :=1; Begin <<top1>> If(a<=10) then Dbms_output.put_line(“…”||a); A=a+1; Goto top1; Else Goto bottom1; End if; <<bottom1>> Dbms_output.put_line(“end of the statement”); End;
PL/SQL Supports two types of subprograms. They are • Procedures • Functions
Procedures A Procedure is a subprogram that performs a specific action. The syntax for creating a procedure is given below. Syntax Create or replace procedure <proc_name> [parameter list] is <local declarations>; Begin {executable statement} [exception] (exception handlers) End; Call procedure Exec <proce_name> (parameters);
SQL> create or replace procedure p1 ( a in number) is 2 c number(10,2); 3 begin 4 select comm into c from emp where eno=a; 5 dbms_output.put_line ('Commsion is'); 6 dbms_output.put_line(c); 7 end; 8 / Procedure created. SQL> declare 2 s number(10); 3 begin 4 s:=&no; 5 p1(s); 6 end; 7 / Enter value for no: 100 old 4: s:=&no; new 4: s:=100; Commsion is 550 PL/SQL procedure successfully completed.
Functions A function is a subprogram that computes a value. The syntax for creating a function is given below. Syntax Create or replace function <function_name> [Argument] return datatype is (local declaration) Begin (executable statements) [exception] (exception handlers) End;
Function SQL> create or replace function f1 2 (no in number) return number as sal number(10,2); 3 begin 4 select salary into sal from emp where eno=no; 5 return sal; 6 end; 7 / Function created. SQL> set serveroutput on; SQL> declare 2 s number(10,2); 3 begin 4 s:=100; 5 dbms_output.put_line('Employee salary is'); 6 dbms_output.put_line(f1(s)); 7 end; 8 / Employee salary is 50800 PL/SQL procedure successfully completed.
