Chapter Seventeen Subprogramming - PowerPoint PPT Presentation

1 / 40

About This Presentation

Title:

Chapter Seventeen Subprogramming

Description:

Chapter 17: Subprogramming. 3. Procedures (Example) ... Chapter 17: Subprogramming. 13. Parameter Modes. IN (Default) pass by reference (Read Only) ... – PowerPoint PPT presentation

Number of Views:28

Avg rating:3.0/5.0

Slides: 41

Provided by: acade121

Category:

more less

Transcript and Presenter's Notes

Title: Chapter Seventeen Subprogramming

1
Chapter SeventeenSubprogramming

Objective
Introduction to Procedures
Introduction to Functions
Introduction to Packages

2
1 Procedure Syntax

CREATE OR REPLACE
PROCEDURE P_Name (P1, P2,..)
IS AS
Local_declaration
BEGIN
executable_statements
EXCEPTION exception_handling
END P_Name

3
Procedures (Example)

PROCEDURE Raise_salary (faculty_ID NUMBER, amount
NUMBER) IS -- Header
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 SalarySalaryamount
WHERE IDfaculty_ID
END IF

Continued
4
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

5
Procedures

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

6
Why Use Subprogramming

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

7
Constrain on Datatypes

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

8
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(1111,Jim, Johnny, COSC)

9
2 Functions Syntax

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

10
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

11
Calling Functions

-- without Paramters
A Function1()
A Function1

12
Calling Functions

DECLARE
MyName VARCHAR2(50) F1()
Name VARCHAR2(75) F2(John)
BEGIN
?--------?
DECLARE
a StudentInfo StudentInto(111, Mary, COSC,
2.4)
BEGIN
IF a.year 2002 THEN
?-------?
DECLARE
FacultyInfo FacultyROWTYPE
BEGIN
FacultyInfo F2(1111)

13
Parameter Modes

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

14
Example

CREATE PROCEDURE One( a IN INTEGER,
c IN OUT INTEGER,
b OUT INTEGER) IS
Begin
b a -- legal
a 10 -- illegal
b 10 -- legal
c -- legal
c 10 -- legal
END

15
Comparison of IN, OUT, IN OUT
16
Positional Named Subprogram Parameters

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

17
Concept of NOCOPY

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

18
Dropping Functions Procedures

DROP PROCEDURE P_name
DROP FUNCTION F_name

19
Subprogram Using a default value

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

20
Default Values

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

21
Subprogram within subprogram

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

22
Recursion
23
Forward Declaration

DECLARE
PROCEDURE ONE IS
BEGIN
TWO
END
PROCDURE TWO IS
BEGIN
ONE
END

PROCEDURE TWO -- forward declaration
24
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.
When called from SELECT, INSERT, UPDATE, or
DELETE the function can not execute any
Control statement
Session control
System control statement
DDL statement

25
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)
FUNCTION
END Salary_Pack

26
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
DELECT FROM Faculyt 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

27
Use of Packages

BEGIN
Salary_Pack.Hire(Lorry, 24000, )
END

28
When To Use Packages

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

29
Why Packages

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

30
Why Packages

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

31
Why Packages

CREATE of REPLACE PACKAGE faculty_pkg AS
SUBTYPE FullName 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

32
Why Packages

CREATE of 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
FUNCTION fac_Name(f_id IN faclty.idTYPE) RETURN
fulName_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

33
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

34
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

35
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

36
Subprogram Location