Subprograms in PL/SQL
• PL/SQL blocks are anonymous, can not be called from other PL/SQL
blocks
• A PL/SQL subprogram is a named PL/SQL block that can be invoked
repeatedly.
• Subprogram is a named PL/SQL block that could be either
• Procedure, or
• Function, or
• Package
CONFIDENTIAL© Copyright 2008 Tech Mahindra Li 2
mited
Pl sql stored procedure:
• The pl sql stored procedure is a named PL/SQL block which performs
one or more specific tasks. A pl sql stored procedure can be divided
into two parts: Header and Body part.
Header: The header part contains the name of the procedure and the
parameters passed to the procedure.
Body: The body part contains declaration section, execution section
and exception section.
How to pass parameter in a procedure?
• We can use the below modes to pass the parameters in a procedure:
IN-parameters: These parameters are the read-only parameters.
Procedure cannot change the value of IN parameters.
OUT-parameters: These parameters are the write-only parameters
and used to return values back to the calling program. Procedure can
change the value of OUT parameters.
IN OUT-parameters: These parameters are read and write parameters
i.e. a procedure can reads and change the IN OUT parameter value
and return it back to the calling program.
Syntax of pl sql stored procedure:
• CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
• IS | AS
• //Declaration block
• BEGIN
• //Execution block
• EXCEPTION
• //Exception block
• END;
How to create a procedure?
• Procedure example without parameters:
• CREATE OR REPLACE PROCEDURE hello_world
• AS
• BEGIN
• dbms_output.put_line('Hello World!');
• END;
Excecution of procedure
• BEGIN
• hello_world;
• END;
Drop procedure
• DROP PROCEDURE hello_world;
Procedure example with parameters:
• CREATE OR REPLACE PROCEDURE add_student(rollNo IN NUMBER,
name IN VARCHAR)
• IS
• BEGIN
• insert into students values(rollNo,name);
• END;
• BEGIN
• add_student(1,'neha');
• END;
• DECLARE
• a number;
• b number;
• c number;
• PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
• BEGIN
• IF x < y THEN
• z:= x;
• ELSE
• z:= y;
• END IF;
• END;
• BEGIN
• a:= 23;
• b:= 45;
• findMin(a, b, c);
• dbms_output.put_line(' Minimum of (23, 45) : ' || c);
• DECLARE
• a number;
• PROCEDURE squareNum(x IN OUT number) IS
• BEGIN
• x := x * x;
• END;
• BEGIN
• a:= 23;
• squareNum(a);
• dbms_output.put_line(' Square of (23): ' || a);
• END;
FUNCTION
• The pl sql function is a named PL/SQL block which performs one or more specific
tasks and must returns a value.
• CREATE [OR REPLACE] FUNCTION function_name [parameters]
• RETURN return_datatype;
• IS|AS
• //Declaration block
• BEGIN
• //Execution_block
• Return return_variable;
• EXCEPTION
• //Exception block
• Return return_variable;
• END;
• CREATE OR REPLACE FUNCTION totalCustomers
• RETURN number IS
• total number(2) := 0;
• BEGIN
• SELECT count(*) into total
• FROM customers;
•
• RETURN total;
• END;
Calling a Function
• DECLARE
• c number(2);
• BEGIN
• c := totalCustomers();
• dbms_output.put_line('Total no. of Customers: ' || c);
• END;
• DECLARE
• a number;
• b number;
• c number;
• FUNCTION findMax(x IN number, y IN number)
• RETURN number
• IS
• z number;
• BEGIN
• IF x > y THEN
• z:= x;
• ELSE
• Z:= y;
• END IF;
• RETURN z;
• END;
• BEGIN
• a:= 23;
• b:= 45;
Insert the a record into the table
• declare
• a int := :a;
• b varchar(20):= :b;
• begin
• insert into td values(a,b);
• end;