1 / 19

PL/SQL

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

stu
Télécharger la présentation

PL/SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PL/SQL

  2. Introduction to PL/SQL block Declare declarations Begin executable statement Exception exception handlers End;

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

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

  5. Control Structures • Conditional control • Iterative control • Sequential control

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

  7. Iterative control • Simple loop • For loop • While loop

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

  9. For loop For counter in [reverse] lowerbound .. Upperbound Loop Sequence_of_statements; End loop;

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

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

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

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

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

  15. PL/SQL Supports two types of subprograms. They are • Procedures • Functions

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

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

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

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

More Related