420-ch15-procsAndfuncs(2).pptx - IS 420 Chapter 15 PL/SQL...

IS 420
Chapter 15
PL/SQL Language
Stored procedures and functions
1

Objectives
•
After completing this lesson you will know
how to use:
–
PL/SQL Stored Procedures
–
PL/SQL Stored Functions
–
PL/SQL Exceptions
2

About Program Units/Blocks
•
Program unit
–
Self-contained group of program statements that
can be used within larger program
•
Anonymous PL/SQL programs
•
Stored PL/SQL program units
•
Server-side program units
•
Client-side program units
3

Types of Stored Program Units
4

PL/SQL Procedure
•
A PL/SQL procedure is a named PL/SQL block
that is stored on the database server
•
A procedure can receive multiple input
parameters (values), can return multiple
output values, or can return no output values
Chapter 3 - PL/SQL 5

An Example of Procedure
6
create or replace procedure HelloWorld(s varchar)
as
begin
dbms_output.put_line(s);
end;

General Steps
•
Create function or procedure using create
function or create procedure command
•
Oracle will compile and store the procedure
–
In case of compilation error, type
show errors
–
You will see the line/column of error and the
description of error
–
Try to fix errors
–
Otherwise, you will see “procedure compiled” message
7

Show Errors
•
Suppose you have created a procedure like
•
Now you type (must do it after you click
execute)
8
create or replace procedure HelloWorld(s varchar)
as
begin
dbms_output.putline(s);
end;
show errors;

Show Errors
•
Output:
•
Meaning:
–
Error occurs on Line 4
–
putline is not recognized
•
You can also google error number “PLS-00302”
to obtain a more detailed description of error
9

General Steps
•
To see the names of procedures
select * from user_procedures;
•
To check the parameters of procedures
desc procedure-name;
10

General Steps
•
Execute the procedure with passed parameter
–
Execute in command:
exec procedure-name(parameter values);
–
Execute in PL/SQL (in anonymous program or another procedure
or function):
procedure-name(parameter values);
exec HelloWorld(‘hello world!’);
begin
HelloWorld(‘hello world!’);
end;
11

Calling a Procedure from another Procedure
12
Proc 1 Proc 2
Create or replace
Procedure Proc1 is
.
.
Begin
…
Proc2;
DBMS_OUTPUT….
…
End;
Create or replace
Procedure Proc2 is
.
.
Begin
…
End;
Call Proc2
Return result to Proc1

Revisit: Example of Procedure
Example:
•
s is a parameter
•
You cannot specify size for parameter types (since Oracle does not
allow () within parameter type)
15
create or replace procedure HelloWorld(s varchar)
as
begin
dbms_output.put_line(s);
end;
-- click execute button now
exec HelloWorld(‘Hello’);

Creating a Procedure
CREATE OR REPLACE PROCEDURE procedure_name
( parameter1 mode datatype,
parameter2 mode datatype , …
) IS | AS
local variable declarations
BEGIN
program statements
EXCEPTION
exception handlers
END;
16
header
body
Differences with anonymous PL/SQL
programs?

Example: Procedure to update table
CREATE OR REPLACE PROCEDURE update_invoices_credit_total
(
invoice_number_param