1 / 55

Chapter Seventeen Subprogramming

Chapter Seventeen Subprogramming. Objective: Procedures Functions Packages. 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);

dexter-mcknight
Download Presentation

Chapter Seventeen Subprogramming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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); Chapter 17: Subprogramming

  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); Chapter 17: Subprogramming

  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]; Chapter 17: Subprogramming

  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 ID=faculty_ID; IF current_salary IS NULL THEN RAISE salary_missing ELSE UPDATE Faculty SET Salary = Salary + amount WHERE ID= faculty_ID; END IF; Continued Chapter 17: Subprogramming

  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; / Chapter 17: Subprogramming

  7. Procedures -- To call a procedure Raise_Salary(111, 250); -- To call a procedure without parameters Proc_one; Proc_one(); Chapter 17: Subprogramming

  8. Actual and Formal Parameters: CREATE PROCEDURE p1 (x: STRING) BEGIN ….. END; --Procedure call n NUMBER(5); p1(n); … SQL> EXEC p1(‘Mary’); Chapter 17: Subprogramming

  9. Why Use Subprogramming • Extensibility: PROCEDURE NewDept (NewName VARCHAR2, NoFaculty NUMBER) IS BEGIN INSERT INTO Dept VALUES (NewName, NoFaculty); END; • Abstraction • Modularity • Reusability • Maintainability Chapter 17: Subprogramming

  10. Constrain on Datatypes CREATE PROCEDURE AddName (Name VARCHAR2(20), …) --illegal DECLARE SUBTYPE vchar20 is VARCHAR2(20); CREATE PROCEDURE AddName (Name vchar20, …) Chapter 17: Subprogramming

  11. Procedures (Example 2) CREATE OR REPLACE PROCEDURE NewStudent ( P_first Student.First%TYPE, P_last Student.Last%TYPE, P_Major Student.Major%TYPE) 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’); Chapter 17: Subprogramming

  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]; Chapter 17: Subprogramming

  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 Job=Title; RETURN (Salary >= Min_Salary) AND (Salary <= Max_Salary); END Salary_OK; Chapter 17: Subprogramming

  14. Calling Functions IF Salary_OK(20000,’MANAGER’) THEN ………. -- A := Function1(); A := Function1 ; Chapter 17: Subprogramming

  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 Faculty%ROWTYPE; BEGIN FacultyInfo := F3(1111); Chapter 17: Subprogramming

  16. Parameter Modes • IN (Default) pass by reference (Read Only) • OUT pass by value (Write Only) • IN OUT pass by value (Read & Write) Chapter 17: Subprogramming

  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 y:=b; --possibly legal c:= 10; -- legal END; Chapter 17: Subprogramming

  18. Comparison of IN, OUT, IN OUT Chapter 17: Subprogramming

  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. Chapter 17: Subprogramming

  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 => Two, first => One); --Named notation Test (first => One, second => Two); --Named notation Test (One, second => Two); --Mixed notation Chapter 17: Subprogramming

  21. Concept of NOCOPY CREATE PROCEDURE Two (S IN OUT NOCOPY NUMBER , Value OUT NOCOPY num_varray) BEGIN ….. END; Chapter 17: Subprogramming

  22. Dropping Functions & Procedures • DROP PROCEDURE P_name; • DROP FUNCTION F_name; Chapter 17: Subprogramming

  23. Subprogram Using a Default Value CREATE PROCEDURE Three (Today DATE DEFAULT SYSDATE, ZipCode CHAR DEFAULT ‘21532’) IS BEGIN …. END; Chapter 17: Subprogramming

  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 => To_DATE(’03-03-1954’, ‘MM-DD-YYYY)); Chapter 17: Subprogramming

  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; Chapter 17: Subprogramming

  26. Recursion • --n!= n*(n-1)! FUNCTION fact (n POSITIVE) RETURN INTEGER IS BEGIN IF n=1 THEN RETURN 1; ELSE RETURN n* fact (n-1); END IF; END FACT; Chapter 17: Subprogramming

  27. Forward Declaration DECLARE PROCEDURE TWO; --Forward Declaration PROCEDURE ONE IS BEGIN TWO; END; PROCDURE TWO IS BEGIN ONE; END; Chapter 17: Subprogramming

  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. Chapter 17: Subprogramming

  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 Chapter 17: Subprogramming

  30. Creating Package Specification: CREATE [OR REPLACE ] PACKAGE P_name [IS |AS ] --package specification: --functions, procedures, variables, --constant, cursors, exceptions END [P_name]; Chapter 17: Subprogramming

  31. Packaging subprogramsPackage 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; Chapter 17: Subprogramming

  32. Creating Package Body: Syntax: CREATE [OR REPLACE] PACKAGE BODY p_name [IS |AS] --package boady END p_name; Chapter 17: Subprogramming

  33. Packaging subprogramsPackage 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; / Chapter 17: Subprogramming

  34. Use of Packages BEGIN Salary_Pack.Hire(1111,‘Lorry’) END; / Chapter 17: Subprogramming

  35. When To Use Packages • Encapsulation Data (hidden) • Avoid hard-coding literals • Grouping together logically related functions Chapter 17: Subprogramming

  36. Why Packages CREATE or REPLACE PROCEDURE fac_name(Fac_ID IN faculty.id%TYPE) IS FullName VARCHAR2(100); BEGIN SELECT Last_name || ‘, ‘ || first_name INTO FullName FROM faculty WHERE faculty.id = Fac_ID; END; Chapter 17: Subprogramming

  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? Chapter 17: Subprogramming

  38. Why Packages CREATE OR REPLACE PACKAGE faculty_pkg AS SUBTYPE FullName_t IS VARCHAR2(200); FUNCTION fac_Name (Last_In faculty.last_name%TYPE, First_In faculty.first_name%TYPE) RETURN fullName_t; FUNCTION fac_Name(f_id IN faculty.id%TYPE) RETURN fullName_t; END faculty_pkg; Chapter 17: Subprogramming

  39. Why Packages CREATE OR REPLACE PACKAGE BODY faculty_pkg AS FUNCTION fac_Name (Last_In faculty.last_name%TYPE, First_In faculty.first_name%TYPE) RETURN fullName_t IS BEGIN RETURN Last_In || ‘, ‘ || First_In END; Chapter 17: Subprogramming

  40. Why Packages FUNCTION fac_Name(f_id IN faculty.id%TYPE) 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; Chapter 17: Subprogramming

  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; / Chapter 17: Subprogramming

  42. Overloading CREATE OR REPLACE PACKAGE Student_Pack AS PROCEDURE AddStudent( P_ID IN Student.id%TYPE, P_Name IN Student.Name%TYPE, P_Major IN Student.Major%TYPE); PROCEDURE AddStudent( P_ID IN Student.id%TYPE, P_Name IN Student.Name%TYPE); END Student_Pack; Chapter 17: Subprogramming

  43. Overloading CREATE OR REPLACE PACKAGE BODY Student_Pack AS PROCEDURE AddStudent( P_ID IN Student.id%TYPE, P_Name IN Student.Name%TYPE, P_Major IN Student.Major%TYPE) IS BEGIN INSERT INTO Student(id, Name, Major) VALUES (P_ID, P_Name, P_Major); END; PROCEDURE AddStudent( P_ID IN Student.id%TYPE, P_Name IN Student.Name%TYPE) IS BEGIN INSERT INTO Student(id, Name) VALUES (P_ID, P_Name); END; END Student_Pack; Chapter 17: Subprogramming

  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 Chapter 17: Subprogramming

  45. Invoker’s Right: Stored procedure executed with the privilege of their owner; (not current user). CREATE PROCEDURE addempl( ssn NUMBER, name VARCHAR2, salary NUMBER) AUTHID CURRENT_USER AS --vs. DEFINER BEGIN INSERT INTO employee VALUES (ssn, name, salary); END;_ Chapter 17: Subprogramming

  46. Invoker’s Right: USER_USERS: USERNAME USER_ID Default_tablespace STATUS Chapter 17: Subprogramming

  47. Subprogram Location Subprograms: • Text • Compiled: P_code Chapter 17: Subprogramming

  48. Subprogram Location Example CREATE OR REPLACE PROCEDURE Simple AS a NUMBER; BEGIN a:= 10; END Simple; Chapter 17: Subprogramming

  49. Subprogram Location • USER_OBJECTS • USER_SOURCE • USER_ERRORS SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = ‘SIMPLE’; Chapter 17: Subprogramming

  50. Subprogram Location SQL> SELECT TEXT FROM USER_SOURCE WHERE NAME = ‘SIMPLE’ AND TYPE= ‘PROCEDURE’ ORDER BY line; TEXT ----------------------------------------- CREATE OR REPLACE PROCEDURE Simple AS a NUMBER; BEGIN a:= 10; END Simple; Chapter 17: Subprogramming

More Related