Lesson 9
Modularizing Programming
with Subprograms
Objectives
Determine the types of program units and where
to use them.
Explain the differences between and benefits of
procedures and functions.
Develop subprograms.
Invoke subprograms from Procedure Builder.
2-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Topic Coved
• DBMS and RDBMS
• Installation of Oracle Database 21c XE and Oracle SQL Developer
• Oracle SQL (Querying data, Sorting data, Filtering data)
• Grouping and Joining tables and Sub queries
• Views, Indexes and Sequences
• Regular Expressions
• PL/SQL Blokes
• Control Structure, Iterative processing with loops, Select Into in PL/SQL
• Cursors, Records and Stored procedures and Functions
• Exception handlers
• Program Units
• Database Triggers
• PL/SQL Collections
• Dynamic SQL
3-25
.Oracle Corporation, 1996. All rights reserved سCopyright
What Are PL/SQL Program Units?
Named PL/SQL blocks
Three main categories:
– Procedures to perform actions
– Functions to compute a value
– Packages to bundle logically related
procedures and functions
Stored in the database or developed as an
application subprogram.
4-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Procedure in Oracle
A procedure in PL/SQL is subprogram that
performs a specific action.
A Stored procedures is also called PL/SQL block
that accepts some input in the form of parameters
and performs some task and may or may not
return a value.
Procedures are created to perform one or more
DML operations over DATABASE
5-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Subprograms in Oracle
Subprogram are named PL/SQL blocks.
They can be called with or without parameters.
PL/SQL has two types of subprogram:
Procedure : to perform an action
Function : to compute a value.
6-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Procedure in oracle
A procedure in PL/SQL is subprogram that
performs a specific action.
It is also called as stored procedure.
Procedure is created to:
Perform DML operations
Implement transactions
7-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Advantage of procedure
Modular programming
Reusability
Security
Performance improvement
8-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Types of procedure
Standalone stored procedure
Procedure as a part of a package
9-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Standalone procedure
Standalone procedure is a subprogram that
performs a specific action.
It is stored in the database.
It is not a part of a package.
10-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Subprogram Components
HEADER
HEADER –– Mandatory
Mandatory
–– Subprogram
Subprogram name,
name, type,
type, and
and arguments
arguments
DECLARATIVE
DECLARATIVE –– Optional
Optional
–– Local
Local identifiers
identifiers
EXECUTABLE
EXECUTABLE –– Mandatory
Mandatory
–– SQL
SQL statements
statements
–– PL/SQL
PL/SQL control
control statements
statements
EXCEPTION
EXCEPTION HANDLING
HANDLING –– Optional
Optional
–– Actions
Actions to
to perform
perform when
when errors
errors occur
occur
END;
END; –– Mandatory
Mandatory
11-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Subprogram
Select Environment
Create Subprogram
Debug
Compile Code
Invoke
12-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Procedure: Syntax
PROCEDURE
PROCEDURE name
name
[(
[(parameter,...
parameter,...)]
)]
IS
IS
pl/sql_block
pl/sql_block;;
13-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Example
CREATE or replace PROCEDURE hello
IS
BEGIN
dbms_output.put_line('Hello Oracle');
END;
/
14-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Execute a procedure
SQL prompt
Another PL/SQL program
Front end application oracle forms
15-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Execute a procedure from SQL prompt
Exec procedure_name[(parameters)];
Execute procedure_name[(parameters)];
16-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Execute a procedure from PL/SQL
program
Begin
procedure_name[(parameters)];
End;
/
17-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Parameters in procedures and
functions
Parameters in a procedure of function are used to
send or receive runtime values.
18-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Types of parameters
IN : used to send values to procedure or function
OUT: used to get values from procedure (similar to
a return type in function)
IN OUT: used to send values to and get values
from procedure
NOTE:
If a parameter is not explicitly defined a parameter
type, then by default it is an IN type parameter
19-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Syntax for parameters
Where the parameter syntax is
parameter_name
parameter_name [IN
[IN || OUT
OUT || IN
IN OUT]
OUT] datatype
datatype
[{:=
[{:= || DEFAULT}
DEFAULT} expr
expr]]
.Do not specify a constraint on the datatype
20-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Parameters in functions
21-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Example with IN parameter
CREATE or replace PROCEDURE add_pro
(n1 number,n2 number)
IS
n3 number;
BEGIN
n3:=n1+n2;
dbms_output.put_line(n3);
END;
22-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Example with IN and OUTPUT
parameter
CREATE or replace PROCEDURE getsal
( empno IN employees.EMPLOYEE_ID%TYPE,
sal OUT [Link]%TYPE)
IS
BEGIN
SELECT salary into sal FROM employees
where EMPLOYEE_ID=empno;
dbms_output.put_line(sal);
END;
23-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Example using by DML
create or REPLACE procedure RAISE_salary
(E in NUMBER, amt in NUMBER, s out NUMBER)
is
BEGIN
UPDATE employees set salary=salary+amt where
employee_id= e;
COMMIT;
SELECT salary into s from employees where
employee_id=e;
End;
24-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Execute DML procedure
First you will create variable called bind that
receive output parameter
variable k number
execute RAISE_salary (101,1000,:k);
Print :k
SELECT * FROM employees
25-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Example using by Cursor
CREATE or replace PROCEDURE empinfo
IS
cursor c1 is SELECT * FROM employees;
BEGIN
for i in c1 loop
dbms_output.put_line
( i.employee_id || rpad(i.first_name || ' ' ||
i.last_name,20) || [Link]);
end loop;
END ;
26-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Procedure: Guidelines
Use the CREATE OR REPLACE clause when
building your procedure in SQL*Plus.
Enter any parameters.
Start the PL/SQL block with IS.
Enter a local variable declaration or the keyword
BEGIN after IS.
27-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Procedural Parameter Modes
Procedure
IN Argument
Calling
Environment OUT Argument
IN OUT
Argument
)DECLARE(
BEGIN
EXCEPTION
;END
28-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Parameter Modes for Formal
Parameters
IN OUT IN OUT
Default Must be specified. Must be specified.
Value is
Passed into Returned to calling Passed into subprogram;
subprogram. environment. returned to calling
environment.
Formal parameter acts as
A constant. An uninitialized An initialized variable.
variable.
Actual parameter
Can be a literal, Must be a variable. Must be a variable.
expression,
constant, or
initialized variable.
29-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Procedure: Example
PROCEDURE
PROCEDURE change_salary
change_salary
(v_emp_id
(v_emp_id IN
IN NUMBER,
NUMBER,
v_new_salary
v_new_salary IN IN NUMBER)
NUMBER)
IS
IS
BEGIN
BEGIN
UPDATE
UPDATE s_emp
s_emp
SET
SET salary
salary == v_new_salary
v_new_salary
WHERE
WHERE id id == v_emp_id;
v_emp_id;
COMMIT;
COMMIT;
END
END change_salary;
change_salary;
30-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Procedure or Function?
Procedure Function
Calling Calling
Environment IN Argument Environment IN Argument
OUT Argument
IN OUT
Argument
)DECLARE( )DECLARE(
BEGIN BEGIN
EXCEPTION EXCEPTION
;END ;END
31-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Comparing Functions and Procedures
Procedure Function
Execute as a PL/SQL Called as part of an expression
statement
No RETURN datatype Must contain a RETURN datatype
Can return a value Must return a value
32-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Drop procedure
Syntax:-
Drop procedure <procedure name>;
Example:
Drop procedure hello;
If you want to see procedure
Select text from user_source where name =
‘Empinfo’
33-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Function in Oracle
34-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Function
Stored function / user function / user –define
function is a sub program in Oracle.
It is very similar to procedure but it returns a
value.
It can be used as part of SQL expression.
A user defined Function is also a named pl/sql
block that accepts some input perform some
calculations and must return a value
35-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Function
Create a PL/SQL function to return a value to the
calling environment.
Add a RETURN clause with the datatype in the
declaration of the function.
Include at least one RETURN statement in the
PL/SQL block.
Pre-defined function in oracle like
substr ,instr,upper , lower ,Initcap, todate , tonum
36-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Function: Syntax
CREATE
CREATE or
or replace
replace FUNCTION
FUNCTION name
name
[(
[(parameter,...
parameter,...)]
)]
RETURN
RETURN datatype
datatype
IS
IS
Begin
Begin
Statement
Statement ;;
Return
Return <expr>;
<expr>;
End;
End;
Remember to include at least one RETURN
.statement in the PL/SQL block
37-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Function: Example
CREATE or replace FUNCTION addn
(a number, b number)
return number
IS
BEGIN
return a+b;
END addn;
38-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Function: Example
.Return the tax based on the value
CREATE
CREATE or
or replace
replace FUNCTION
FUNCTION tax
tax
(v_value
(v_value IN
IN NUMBER)
NUMBER)
RETURN
RETURN NUMBER
NUMBER
IS
IS
BEGIN
BEGIN
RETURN
RETURN (v_value
(v_value ** .07);
.07);
END
END tax;
tax;
39-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Creating a Function: Example
CREATE or replace FUNCTION calc
(a number, b number,op char)
return number
IS
BEGIN
if op='+' then return (a+b);
else if op='-' then return (a+b);
else if op='*' then return (a*b);
else return (a/b);
end if;
end if;
end if;
40-20 END;
.Oracle Corporation, 1996. All rights reserved سCopyright
Execute Function
Function is not independent executable object , It
has to be with in procedure or SQL statement.
It can be excuted by:
1. sql commands
2. sql prompt
3. another pl/sql block
4. front-end pl/sql block
Example:
1) SELECT addn(100,200) from dual
2) exec dbms_output.put_line(addn(20,30))
41-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Execute Function
SELECT calc(100,200,'+') from dual
3) variable k number
exec :k :=calc(20,10,'+')
print :k
42-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Invoking Procedures from Procedure
Builder: Example
Enter the procedure name with actual parameters,
if applicable, at the Interpreter prompt.
PL/SQL>
PL/SQL> change_salary
change_salary (17,
(17, 1000);
1000);
43-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Invoking Procedures from Another
Procedure: Example
PROCEDURE
PROCEDURE process_sal
process_sal
(v_emp_id
(v_emp_id IN
IN NUMBER,
NUMBER,
v_new_salary
v_new_salary IN
IN NUMBER)
NUMBER)
IS
IS
BEGIN
BEGIN
change_salary
change_salary (v_emp_id,
(v_emp_id, v_new_salary);
v_new_salary);
--invoking
--invoking procedure
procedure change_salary
change_salary
...
...
END;
END;
44-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Invoking Standalone Functions from
Procedure Builder: Example
Display the tax based on a specified value.
PL/SQL>
PL/SQL> .CREATE
.CREATE NUMBER
NUMBER xx PRECISION
PRECISION 44
PL/SQL>
PL/SQL> :x
:x :=
:= tax(100);
tax(100);
PL/SQL>
PL/SQL> TEXT_IO.PUT_LINE
TEXT_IO.PUT_LINE (TO_CHAR(:x));
(TO_CHAR(:x));
77
45-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Invoking Functions in SQL
Statements
Advantages
– Permit calculations otherwise not easily
obtained in SQL.
– Increase efficiency of queries.
Guidelines
– Only use stored functions, not procedures.
– Single row functions
– No DML statements
– Formal parameters must be IN.
– Oracle Server datatypes, no PL/SQL datatypes
– EXECUTE privileges
46-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Calling a Function in a SQL Statement
Valid SQL clauses
Select list of SELECT command
Condition of WHERE and HAVING clause
CONNECT BY, START WITH, ORDER BY, and
GROUP BY clauses
VALUES clause of the INSERT command
SET clause of the UPDATE command
47-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Invoking Functions in SQL
Statements: Example
Enter a SQL statement with a function call at the
Interpreter prompt.
PL/SQL>
PL/SQL> SELECT
SELECT total,
total, tax(total)
tax(total)
+>
+> FROM
FROM s_ord
s_ord
+>
+> WHERE
WHERE id
id == 100;
100;
TOTAL
TOTAL TAX(TOTAL)
TAX(TOTAL)
-------------
------------- ----------
----------
601100.00
601100.00 42077
42077
48-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Summary
Subprograms fall into three main categories:
– Procedures to perform actions
– Functions to compute a value
– Packages to bundle logically related
procedures and functions
A function differs from a procedure in two ways:
– You invoke the function as part of an
expression.
– A function must return a value.
49-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Summary
Procedure and function components:
– Header – To name and type the block
– Declarative –To set up local identifiers
(optional)
– Executable – To perform the actions
– Exception handling – To handle exceptions
(optional)
Invoke the subprograms from the Procedure
Builder Interpreter prompt.
50-20
.Oracle Corporation, 1996. All rights reserved سCopyright
Practice Overview
Creating a simple procedure
Debugging the procedure
Executing the procedure
51-20
.Oracle Corporation, 1996. All rights reserved سCopyright
