KEMBAR78
Stored Procedures | PDF | Parameter (Computer Programming) | Subroutine
Scribd
Upload
245 views15 pages

Stored Procedures

This document discusses stored procedures in PL/SQL. It defines a stored procedure as a type of subprogram that can be stored in a database for repeated execution, providing reusability and a programming environment. It provides the structure for creating procedures, including parameter lists, and examples of creating procedures with different parameter modes. It also demonstrates how to call a stored procedure and remove one. Stored procedures allow reusable code blocks to be stored in a database for repeated execution from functions, triggers, and select statements.

Uploaded by

Surya Vivek
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
245 views15 pages

Stored Procedures

This document discusses stored procedures in PL/SQL. It defines a stored procedure as a type of subprogram that can be stored in a database for repeated execution, providing reusability and a programming environment. It provides the structure for creating procedures, including parameter lists, and examples of creating procedures with different parameter modes. It also demonstrates how to call a stored procedure and remove one. Stored procedures allow reusable code blocks to be stored in a database for repeated execution from functions, triggers, and select statements.

Uploaded by

Surya Vivek
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 15

PL/SQL: Stored

Procedures

Presenter: Surya Vivek.B


suryavivekb@gmail.com

Objectives
Uses

of procedures
Create procedures
Features of parameter modes
Procedures with parameters
Invoke a procedure
Remove a procedure
Examples

Stored Procedures

What is stored procedure?

A procedure is a type of subprogram that performs an action.


A procedure can be stored in the database, as a schema object,
for repeated execution.

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

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

Creating Procedures with


Parameters

General Structure
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
[IS | AS]
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

Optional section for exception handling

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

Developing Procedures

Calling a Stored Procedure

SQL> exec <procedureName> [(<paramList>)];

SQL > exec remove_emp (10);

EXAMPLE - II
CREATE OR REPLACE PROCEDURE raise_salary
(p_id IN employees.employee_id%TYPE)
IS
BEGIN
UPDATE employees SET salary = salary * 1.10
WHERE employee_id = p_id;
END raise_salary;
/
To invoke a procedure in iSQL*Plus, use the EXECUTE command.
EXECUTE raise_salary (176)
To invoke a procedure from another procedure, use a direct call. At the location of
calling the new procedure, enter the procedure name and actual parameters.
raise_salary (176);

Example - III
CREATE OR REPLACE PROCEDURE query_emp
( p_id IN employees.employee_id%TYPE,
p_name OUT employees.last_name%TYPE,
p_salary OUT employees.salary%TYPE,
p_comm OUT employees.commission_pct%TYPE)
IS
BEGIN
SELECT last_name, salary, commission_pct
INTO p_name, p_salary, p_comm
FROM employees
WHERE employee_id = p_id;
END query_emp;
/

Procedure created

continue.

Load and run the emp_query.sql script file to create the


QUERY_EMP procedure.
Declare host variables, execute the QUERY_EMP
procedure, and print the value of the global G_NAME variable.
VARIABLE g_name VARCHAR2(25)
VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER
EXECUTE query_emp(171, :g_name, :g_sal, :g_comm)

Removing Procedures
Drop a procedure stored in the database.
Syntax:

Summary of Stored Procedures

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)

THANK YOU

Presenter: Surya Vivek.B


suryavivekb@gmail.com

You might also like