Anonymous procedures Named procedures Functions
Overview of PL/SQL
With PL/SQL, you can use SQL statements to
manipulate ORACLE data and flow-of-control statements to process the data.
Moreover, you can declare constants and variables,
define subprograms (procedures and functions), and trap runtime errors. Thus, PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.
PL/SQL
A block (or sub-block) lets you group logically related declarations and statements. That way you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes.
[DECLARE -- declarations] BEGIN -- statements [EXCEPTION -- handlers if any] END; /
How to declare variables
SQL> name char (30); age number(2); dept_no number(2):= 30;
Note - Symbol := functions as an assignment operator to store a value in a variable.
Example 1
SQL> SQL> SET SERVEROUTPUT ON; DECLARE msg char(20); BEGIN msg := Welcome to India; dbms_outpt.put_line(msg); END; SQL>/
SQL> DECLARE radius number(3):=5; AOC number (5); BEGIN AOC := 3.14*radius*radius; dbms_outpt.put_line(area of circle= ||AOC); END; SQL>/
SQL> DECLARE radius number(3):=5; AOC number (5); PI constant number:= 3.14; BEGIN AOC = PI*power (r,2); dbms_outpt.put_line(area of circle= ||AOC); END; SQL>/
Read input value from user
SQL> DECLARE a number := &temp_a; BEGIN dbms_output.put_line(the value of a: ||a); END; SQL>/
PL/SQL block to find a greater number from to numbers input by user
SQL> declare 2 x number :=1; 3 begin 4 loop 5 dbms_output.put_line(x); 6 x:=x+1; 7 exit when (x>10); 8 end loop; 9 end; 10 /
SQL> declare 2 x number :=1; 3 begin 4 while(x<10) 5 loop 6 dbms_output.put_line(x); 7 x:=x+1; 8 end loop; 9 end; 10 /
for var in 110 loop dbms_output.put_line(var); end loop
Named Procedures
Stored procedure Has a name Stored in the database Can be executed again.
Syntax
CREATE [OR REPLACE] PROCEDURE (parameter {IN|OUT} datatype ) {IS|AS} BEGIN
Parameter passed out by value
..PL/SQL statements.. END: /
Parameter definitions Parameter passed in by value
SQL> create or replace procedure adds 2 as 3 x number:=1; 4 begin 5 while(x<10) 6 loop 7 dbms_output.put_line 8 (x); 9 x:=x+1; 10 end loop; 11 end; 12 / Procedure created.
SQL> execute adds 1 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed.
Example 2
SQL> create or replace procedure adds 2 as 3 x number:=&start; 4 y number:=&end; 5 begin 6 while(x<y) 7 loop 8 dbms_output.put_line( x); 9 x:=x+1; 10 end loop; 11 end; 12 / Enter value for start: 2 old 3: x number:=&start; new 3: x number:=2; Enter value for end: 9 old 4: y number:=&end; new 4: y number:=9;
SQL> execute adds 2 3 4 5 6 7 8
