Welcome

PL SQL Tutorial

---

PL SQL Stored Procedures

    PL/SQL Stored Procedures are sub-routines, executes sequence of SQL Statements(one or more).It is a schema Object,precompiled code stored in the database. It has Name, takes parameters, Paramaters can be SQL or PL/SQL Data Types. These stored procedures are going to be called in Higher Programming Language,like C,C++,Java,Python etc., Parameters should be of SQL Types, Where as these are going to be used within Oracle Database Applications,it takes PL/SQL Data Types also.

Pl/SQL Stored Procedures Syntax

CREATE [OR REPLACE] PROCEDURE name[ (parameter[,parameter]) ]  
	{ IS | AS }
	declaration_section
BEGIN
	executable_section
[EXCEPTION
	exception_section]
END [name];    	
      	

Stored Procedure Parameters

   Paramameters are the only way to send information from one subprogram to another subprogram or calliing from Client program, Client Program can be PL/SQL Anonymous Block, Stored procedure ,Function, or Package, or Any external Applications like Java, C++, Python etc.,

		Paramater_name  [MODE] [NOCOPY] DataType   [{:= | DEFAULT } value]   	
      	

Mode	Description
IN	if Mode is not specified, this is the default Mode for the parameter. This indicates input values to the Procedure. This is a read-only Mode.
OUT	This mode indicates, values will be returned from Stored Procedure. so that caller or client can use these values for later processing. This is a Write only Mode
IN OUT	This mode indicates, client passing values to the Stored procedure as a Input, Later Stored Proc ,updates these value, which will gets reflected in Client Side. This is a read-write variable

Creating a Stored Procedures

      		CREATE OR REPLACE PROCEDURE PROC1
      		
      		AS
      		
      		BEGIN
      		
      		       DBMS_OUTPUT.PUT_LINE('CALLING PROC1 PROCEDURE ');
      		
      		END PROC1;
      	

Calling Stored procedures

• Using CALL Statement --> SQL*Plus
• Using EXECUTE Statement--> SQL*Plus
• or From PL/SQL Block such as Anonymous Block or Another Stored Procedure or a Function

Calling Stored Procedure from SQL*PLUS .

      			Set Serveroutput ON;
      			CALL is ANSI standard for calling sub-routine
      			SQL> CALL PROC1();
      			CALLING PROC1 PROCEDURE

      			SQL> EXECUTE PROC1();
      			CALLING PROC1 PROCEDURE
			EXECUTE short-form is EXEC,  EXECUTE and EXEC both commands are same 
      			SQL> EXEC PROC1();
      			CALLING PROC1 PROCEDURE

      		

Calling Stored Procedure from PL/SQL BLOCK.

      			BEGIN
      				PROC1();
      			END;
      			/
      			CALLING PROC1 PROCEDURE

      		

Creating a Stored Procedures with IN parameters

      		CREATE OR REPLACE PROCEDURE PROC2(day IN Number)
      		
      		AS

      		BEGIN
      		
      		       	if day >= 1 and day <= 31 then
      		       	  
      		       		DBMS_OUTPUT.PUT_LINE('DAY IS VALID ');
      		       else
      		            DBMS_OUTPUT.PUT_LINE('DAY IS INVALID ');
      		      
      		       end if;
      		
      		END PROC2;
      	

Calling PROC2 Procedure

      		SQL>EXEC PROC2(22)
      		  DAY IS VALID
	
		SQL>EXEC PROC2(34)
		DAY IS INVALID 
      	

Creating a Stored Procedures with IN parameters, OUT Parameters

    The following Procedure gets total amout earned for a given year.(This procedure uses Sales History schema)

      		CREATE OR REPLACE PROCEDURE PROC3(year IN Number, total_sales OUT NUMBER)
      		
      		AS
      		BEGIN
		select sum(dollars) INTO total_sales from sh.cal_month_sales_mv
			where to_number(substr(calendar_month_desc,0,4),'9999')=year;
		END;
	

Execute above stored procedure Proc3

	set serveroutput on
	
declare
total number;
year number default 1998;
begin
    proc3(year,total);
    dbms_output.put_line(year||' Total Sales:$'||total);
end;
	
output
1998 Total Sales:$24083914.95

	

Stored Procedures Can be Overloaded

    Overloading a Procedure means, same name with different parameters.

• Same parameters with different Type
• Number of Parameters differs

Following Example, Procedure has same name, parameters are of different Types. This is one-way of overloading

      	  		Proc1()
      	  		Proc1(v1 Number)
      	  		Proc1(v1 Varchar2)
      	  		Proc1(v1 Date)
      	  		Proc1(v1 DateTime)
      	  	

Same Number of parameters, But different types.

      	  		Proc1(v1 Number, v2 varchar2);
      	  		Proc1(v1 varchar2,v2 number);
      	  	

Number of parameters differs.

      	  		Proc1(v1 Number);
      	  		Proc1(v1 varchar2,v2 number);
      	  	

Note: PL/SQL Runtime, should able to identify which procedure to call, based on number of parameters and parameter types, because Procedure name is same. i.e if procedure name is same, runtime engine depends on parameters to pick right one.