Advanced SQL: Stored
Procedures
Instructor: Mohamed Eltabakh
meltabakh@[Link]
1
Today’s Roadmap
Views
Triggers
Assertions
Cursors
Stored Procedures
Stored Procedures & Functions
Views
Way to register queries inside DBMS
Stored Procedures &
Functions
Way to register code inside DBMS
Stored Procedures & Functions
What is stored procedure?
Piece of code stored inside the DBMS
SQL allows you to define procedures and functions and store
them inside DBMS
Advantages
Reusability: do not need to write the code again and again
Programming language-like environment
Assignment, Loop, For, IF statements
Call it whenever needed
From select statement, another procedure, or another function
Stored Procedures in Oracle
Stored procedures in Oracle follow a
language called PL/SQL
PL/SQL: Procedural Language SQL
Same language used inside DB triggers
cs3431
Creating A Stored Procedure
If exists, then drop it and create it again ‘IS’ or ‘AS’ both are valid
CREATE [OR REPLACE] PROCEDURE <procedureName> (<paramList>) [IS| AS]
<localDeclarations>
Begin
<procedureBody>;
End;
/
A parameter in the paramList is specified as:
<name> <mode> <type>
Mode:
IN input parameter (default)
OUT output parameter
INOUT input and output parameter
cs3431
General Structure
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
[IS | AS]
[declaration_section]
BEGIN
executable_section Optional section for exception handling
[EXCEPTION
exception_section]
END [procedure_name];
Example I
Define a variable
By default, it is IN
You can use the procedure name
before the parameter name
In PL/SQL a ‘;’ ends a line without execution
Execute the command and
create the procedure
Example II
Declaration section
Define a cursor that references the
input parameter
When anything goes wrong, it will
come to Exception section
Calling a Stored Procedure
SQL> exec <procedureName> [(<paramList>)];
SQL > exec remove_emp (10);
Printing From Stored Procedures
Taking three parameters
Printing lines to output screen
Features in Stored Procedures
IN parameters
Create Procedure profiler_control(start_stop IN VARCHAR2, OUT parameters
run_comm IN VARCHAR2,
ret OUT number) AS
ret_code INTEGER; Variable declaration
BEGIN
ret_code := 10; Variable assignment
IF start_stop NOT IN ('START','STOP') THEN
ret:= 0;
ELSIF start_stop = 'START' THEN
ret:= 1;
ELSE IF statement
ret:= ret_code;
END IF;
END profiler_control;
/
More Features: LOOP Statement
CREATE PROCEDURE testProcedure (name varchar2) AS
credit_rating NUMBER := 0;
BEGIN
LOOP The Loop statement
credit_rating := credit_rating + 1;
IF credit_rating > 3 THEN
EXIT;
END IF;
END LOOP;
-- control resumes here
IF name > ‘abc’ THEN
RETURN; Return means exit the procedure
END IF;
DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating));
END;
/
More Features: CURSOR &
FOR Statement
Create Procedure OpeningBal (p_type IN string) AS
cursor C1 Is
Select productId, name, price
From products
where type = p_type;
Begin
For rec in C1 Loop
Insert into Temp values ([Link], [Link], [Link]);
End Loop;
End;
/
Return Value
Stored procedures can set output variables
Stored procedures do not return values
Stored functions differ from procedure in
that they return values
Stored Functions
Similar to stored procedures except that they return value
CREATE [OR REPLACE] FUNCTION <functionName>
RETURN <type> [(<paramList>)] AS
<localDeclarations>
<functionBody>;
The function return a number
Select into a variable
Return to the called
Stored Functions
All features in stored procedures are valid
in in stored functions
Functions have an extra ‘Return’ statement
Using Stored Procedures or
Functions
Stored Procedures
Called from other procedures, functions, triggers, or
standalone
Stored Functions
In addition to above, can be used inside SELECT statement
In WHERE, HAVING, or projection list
Example I
CREATE FUNCTION MaxNum() RETURN number AS
num1 number;
BEGIN
SELECT MAX (sNumber) INTO num1 FROM Student;
RETURN num1;
END;
/
SQL> Select * from Student where sNumber = MaxNum();
Calling the function in the Where clause
(function will be executed once)
Example II Adding a parameter
CREATE FUNCTION MaxNum(lastName_in varchar2)
RETURN number AS
num1 number;
BEGIN
SELECT MAX (sNumber) INTO num1 FROM Student
Where lastName = lastName_in;
RETURN num1;
END;
/
SQL> Select * from Student S where [Link] = MaxNum([Link]);
Calling the function in the Where clause
(function will execute with each record in Student)
Example III
CREATE FUNCTION MaxNum(lastName_in varchar2)
RETURN number AS
num1 number;
BEGIN
SELECT MAX (sNumber) INTO num1 FROM Student
Where lastName = lastName_in;
RETURN num1;
END;
/
SQL> Select MaxNum([Link]) from Student S;
Calling the function in the projection list
Summary of Stored
Procedures/Functions
Code modules that are stored inside the DBMS
Used and called repeatedly
Powerful programing language style
Can be called from other procedures, functions, triggers, or
from select statement (only functions)
End of Advanced SQL
Views
Triggers
Assertions
Cursors
Stored Procedures/Functions