Chapter Seventeen Subprogramming - PowerPoint PPT Presentation

1 / 55

About This Presentation

Title:

Chapter Seventeen Subprogramming

Description:

PROCEDURE NewDept (NewName VARCHAR2, NoFaculty NUMBER) IS. BEGIN ... CREATE PROCEDURE AddName (Name VARCHAR2(20), ...) --illegal. DECLARE ... – PowerPoint PPT presentation

Number of Views:43

Avg rating:3.0/5.0

Slides: 56

Provided by: acade121

Category:

more less

Transcript and Presenter's Notes

Title: Chapter Seventeen Subprogramming

1
Chapter SeventeenSubprogramming

Objective
Procedures
Functions
Packages

2
Stored Procedures

Advantages of Stored Procedures
Call Stored Procedure from PL/SQL
Raise_salary ( id, amount)
Call Stored Procedure from SQL
EXECUTE Raise_salary ( id, amount)
Running Stored Procedure owned by other user
EXECUTE Mohsen.Raise_salary ( id, amount)

3
Stored Procedures

Rename Stored Procedure owned by other user
CREATE SYNONYM my_Raise_salary FOR
Mohsen.Raise_salary
EXECUTE my_Raise_salary ( id, amount)

4
1 Procedure Syntax

--Header
CREATE OR REPLACE PROCEDURE P_Name (P1,
P2,..)
IS AS
--Local_declarations
--Body
BEGIN
--Executable_statements
EXCEPTION exception_handling
END P_Name

5
Procedures (Example)

PROCEDURE Raise_salary (faculty_ID NUMBER, amount
NUMBER) IS
current_salary NUMBER -- Declaration
salary_missing EXCEPTION
BEGIN
SELECT Salary INTO current_salary --Execution
FROM Faculty
WHERE IDfaculty_ID
IF current_salary IS NULL THEN
RAISE salary_missing
ELSE
UPDATE Faculty
SET Salary Salary amount
WHERE ID faculty_ID
END IF

Continued
6
Procedures (Example)

EXCEPTION --Exception
WHEN NO_DATA_FOUND THEN
INSERT INTO Logfile_Salary
VALUES (Faculty_ID, NOT exist)
WHEN salary_missing THEN
INSERT INTO Logfile_Salary
VALUES (Faculty_ID, No Salary)
END Raise_salary
/

7
Procedures

-- To call a procedure
Raise_Salary(111, 250)
-- To call a procedure without parameters
Proc_one
Proc_one()

8
Actual and Formal Parameters

CREATE PROCEDURE p1 (x STRING)
BEGIN
..
END
--Procedure call
n NUMBER(5)
p1(n)
SQLgt EXEC p1(Mary)

9
Why Use Subprogramming

Extensibility
PROCEDURE NewDept (NewName VARCHAR2, NoFaculty
NUMBER) IS
BEGIN
INSERT INTO Dept VALUES (NewName, NoFaculty)
END
Abstraction
Modularity
Reusability
Maintainability

10
Constrain on Datatypes

CREATE PROCEDURE AddName (Name VARCHAR2(20), )
--illegal
DECLARE
SUBTYPE vchar20 is VARCHAR2(20)
CREATE PROCEDURE AddName (Name vchar20, )

11
Procedures (Example 2)

CREATE OR REPLACE PROCEDURE NewStudent (
P_first Student.FirstTYPE,
P_last Student.LastTYPE,
P_Major Student.MajorTYPE)
AS
BEGIN
INSERT INTO Student ( ID, First, Last, Major)
VALUES (Student_sequence.NEXTVAL, P_first,
P_last, P_Major)
END NewStudent
.
NewStudent(Jim, Johnson, COSC)

12
2 Functions Syntax

CREATE OR REPLACE
FUNCTION F_Name (P1, P2,..) RETURN datatype
IS AS
Local_declaration
BEGIN
executable_statements
EXCEPTION exception_handling
END F_Name

13
Functions (Example)

CREATE FUNCTION Salary_OK
(Salary REAL, Title VARCHAR2) RETURN
BOOLEAN -- Header
IS
Min_Salary REAL -- Declaration
Max_Salary REAL
BEGIN -- Execution
SELECT L_Sal, H_Sal INTO Min_Salary, Max_Salary
FROM Faculty
WHERE JobTitle
RETURN (Salary gt Min_Salary) AND (Salary lt
Max_Salary)
END Salary_OK

14
Calling Functions

IF Salary_OK(20000,MANAGER)
THEN .
--
A Function1()
A Function1

15
Calling Functions

DECLARE
MyName VARCHAR2(50) F1()
Name VARCHAR2(75) F2(John)
BEGIN
?--------?
DECLARE
a StudentInfo StudentInfo(111, Mary, COSC,
2.4)
BEGIN
?-------?
DECLARE
FacultyInfo FacultyROWTYPE
BEGIN
FacultyInfo F3(1111)

16
Parameter Modes

IN (Default) pass by reference (Read Only)
OUT pass by value (Write Only)
IN OUT pass by value (Read Write)

17
Example

CREATE PROCEDURE One( a IN INTEGER,
c IN OUT INTEGER,
b OUT INTEGER) IS
DECLARE x INTEGER
y INTEGER
Begin
b a -- legal
a 10 -- illegal
b 10 -- legal
x c -- legal
yb --possibly legal
c 10 -- legal
END

18
Comparison of IN, OUT, IN OUT
19
Practice

Create a function to calculate the semester GPA
of each student. Call it
Cal_gpa
-Input student id, semester number.
-Return the GPA for that student.

20
Positional Named Subprogram Parameters

PROCEDURE Test (first REAL, second INTEGER) IS
BEGIN..
END
--Call procedure Test
One REAL
Two INTEGER
Test (One, Two) --Positional notation
Test (second gt Two, first gt One) --Named
notation
Test (first gt One, second gt Two) --Named
notation
Test (One, second gt Two) --Mixed notation

21
Concept of NOCOPY

CREATE PROCEDURE Two (S IN OUT
NOCOPY NUMBER , Value OUT NOCOPY num_varray)
BEGIN
..
END

22
Dropping Functions Procedures

DROP PROCEDURE P_name
DROP FUNCTION F_name

23
Subprogram Using a Default Value

CREATE PROCEDURE Three (Today DATE DEFAULT
SYSDATE, ZipCode CHAR DEFAULT
21532) IS
BEGIN
.
END

24
Default Values

PROCEDURE WhoIs
(Name IN VARCHAR2 DEFAULT Hana,
Born_at IN DATE DEFAULT SYSDATE) IS
Begin.. END
--Procedure call
WhoIs (Mary, To_DATE(01-12-2002,
MM-DD-YYYY))
WhoIs(Marry)
WhoIs
WhoIs(Born_at gt To_DATE(03-03-1954,
MM-DD-YYYY))

25
Subprogram within subprogram

PROCEDURE OUTSIDE (date_in IN DATE) IS
PROCEDURE inside1 IS
BEGIN
.
END
Function inside2 (Next_In IN INTEGER)
RETURN BOOLEAN
IS
BEGIN
..
END
BEGIN -- OUTSIDE
.
END OUTSIDE

26
Recursion

--n! n(n-1)!
FUNCTION fact (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n1 THEN
RETURN 1
ELSE
RETURN n fact (n-1)
END IF
END FACT

27
Forward Declaration

DECLARE
PROCEDURE TWO --Forward Declaration
PROCEDURE ONE IS
BEGIN
TWO
END
PROCDURE TWO IS
BEGIN
ONE
END

28
Side Effects of Subprogramming Called From SQL

When a function is called from SELECT, INSERT,
UPDATE, or DELETE
the function can not modify any database
tables.
When called from INSERT, UPDATE, or DELETE
the function can not query or modify any
database tables modified by that statement.

29
Side Effects of Subprogramming Called From SQL

When called from SELECT, INSERT, UPDATE, or
DELETE
the function can not execute any
Control statement
Session control
System control statement
DDL statement

30
Creating Package Specification

CREATE OR REPLACE PACKAGE P_name IS AS
--package specification
--functions, procedures, variables,
--constant, cursors, exceptions
END P_name

31
Packaging subprograms Package Specification

CREATE PACKAGE Salary_Pack AS
SUBTYPE NameType is VARCHAR2(100)
PROCEDURE Hire(Fac_Id INTEGER, Name VARCHAR2)
PROCEDURE Fire(Fac_ID INTEGER)
PROCEDURE Raise_Salary(Fac_Id INTEGER, Amount
REAL)
END Salary_Pack

32
Creating Package Body

Syntax
CREATE OR REPLACE PACKAGE BODY p_name IS AS
--package boady
END p_name

33
Packaging subprograms Package Body

CREATE PACKAGE BODY Salary_Pack AS
PROCEDURE Hire(Fac_Id INTEGER, Name VARCHAR2) IS
BEGIN
INSERT INTO faculty VALUES (fac_ID, Name)
END
PROCEDURE Fire (Fac_ID INTEGER) IS
BEGIN
DELETE FROM Faculty WHERE ID Fac_ID
END
PROCEDURE Raise_Salary (Fac_Id INTEGER, Amount
REAL) IS
Begin
UPDATE faculty
SET Salary Salary Amount
WHERE ID Fac_ID
End
END Salary_Pack
/

34
Use of Packages

BEGIN
Salary_Pack.Hire(1111,Lorry)
END
/

35
When To Use Packages

Encapsulation Data (hidden)
Avoid hard-coding literals
Grouping together logically related functions

36
Why Packages

CREATE or REPLACE PROCEDURE fac_name(Fac_ID IN
faculty.idTYPE) IS
FullName VARCHAR2(100)
BEGIN
SELECT Last_name , first_name
INTO FullName
FROM faculty
WHERE faculty.id Fac_ID
END

37
Why Packages

Problems
Length of FullName is hard-coded
How about if I want to see first, last name?
If I need different form of the same code in my
applications How should I maintain my code?

38
Why Packages

CREATE OR REPLACE PACKAGE faculty_pkg AS
SUBTYPE FullName_t IS VARCHAR2(200)
FUNCTION fac_Name (Last_In faculty.last_nameTYPE,
First_In faculty.first_nameTYPE)
RETURN fullName_t
FUNCTION fac_Name(f_id IN faculty.idTYPE)
RETURN fullName_t
END faculty_pkg

39
Why Packages

CREATE OR REPLACE PACKAGE BODY faculty_pkg AS
FUNCTION fac_Name
(Last_In faculty.last_nameTYPE,
First_In faculty.first_nameTYPE)
RETURN fullName_t IS
BEGIN
RETURN Last_In , First_In
END

40
Why Packages

FUNCTION fac_Name(f_id IN faculty.idTYPE)
RETURN fullName_t IS
temp FullName_t
BEGIN
SELECT INTO temp fac_name(LastName, FirstName)
FROM faculty WHERE faculty.id id
RETURN temp
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL
WHEN TOO_MANY_ROWS THEN
..
END
END faculty_pkg

41
Package and Local Variables

CREATE OR REPLACE PACKAGE BODY faculty_pkg IS
TodayDate Date
FUNCTION fac_Name ..
END fac_Name
..
BEGIN package
SELECT SYSDATE into TodayDate
FROM DUAL
END faculty_pkg
/

42
Overloading

CREATE OR REPLACE PACKAGE Student_Pack AS
PROCEDURE AddStudent( P_ID IN Student.idTYPE,
P_Name IN Student.NameTYPE,
P_Major IN Student.MajorTYPE)
PROCEDURE AddStudent( P_ID IN Student.idTYPE,
P_Name IN Student.NameTYPE)
END Student_Pack

43
Overloading

CREATE OR REPLACE PACKAGE BODY Student_Pack AS
PROCEDURE AddStudent( P_ID IN Student.idTYPE,
P_Name IN Student.NameTYPE,
P_Major IN Student.MajorTYPE) IS
BEGIN
INSERT INTO Student(id, Name, Major)
VALUES (P_ID, P_Name, P_Major)
END
PROCEDURE AddStudent( P_ID IN Student.idTYPE,
P_Name IN Student.NameTYPE) IS
BEGIN
INSERT INTO Student(id, Name)
VALUES (P_ID, P_Name)
END
END Student_Pack

44
Restriction on Overloading

Local or Packaged subprogram, or Type methods can
be overloaded
No overloading of two subprograms with only
formal parameter different in name parameter mode
or datatype
No overloading of functions that differ only in
return type

45
Invokers Right