1 / 25

PL/SQL Subprograms

PL/SQL Subprograms. Session - IV. What are SubPrograms?. Subprograms are named PL/SQL block that can take parameters and be invoked. Two Types of Subprogram. PROCEDURES FUNCTIONS. Where are They Allowed?. Subprograms can be defined using any ORACLE tool that supports PL/SQL

klaus
Download Presentation

PL/SQL Subprograms

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. PL/SQL Subprograms Session - IV

  2. What are SubPrograms?. • Subprograms are named PL/SQL block that can take parameters and be invoked. Two Types of Subprogram. • PROCEDURES • FUNCTIONS

  3. Where are They Allowed? • Subprograms can be defined using any ORACLE tool that supports PL/SQL • Must be declared at the end of a declarative section after all other program objects.

  4. E.g.:- Declare PROCEDURE TEST IS DBMS_OUTPUT.PUT_LINE(‘Testing’); End; Begin Test; End;

  5. Procedure • Is Subprogram that performs a specific action. • Has two parts the specification and body.

  6. Specification begins with the keyword PROCEDURE and end with Procedure Name. • Parameter Declaration are optional.

  7. Procedure body begins with the keyword END followed by an optional Procedure Name.

  8. The Procedure body has 3 Parts • Declarative Part • Executable part • Optional Exception-Part

  9. Declarative Part Contains Local Declarations which are placed between IS and BEGIN.

  10. Declare Procedure Pn is enam varchar(15); Begin Select ename INTO enam from emp where empno=7369; DBMS_OUTPUT.PUT_LINE(ENAM); End;

  11. Parameter Modes • IN • OUT • IN OUT

  12. IN • Lets you pass value to the subprogram being called.

  13. OUT • Lets you pass return values to the caller of a Subprogram

  14. IN OUT • Lets you pass initial values to the subprogram and return updated values to the caller.

  15. Function • A Function is a Subprogram that computes a value. Functions and Procedures are structured alike, Except that functions have a RETURN clause.

  16. Function CREATE OR REPLACE FUNCTION FUN (ID NUMBER) RETURN CHAR IS NAME EMP.ENAME%TYPE; BEGIN SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=ID; RETURN(NAME); END;

  17. CREATE OR REPLACE FUNCTION F1(N NUMBER) RETURN NUMBER IS SALARY EMP.SAL%TYPE; BEGIN SELECT SAL INTO SALARY FROM EMP WHERE EMPNO=N; RETURN SALARY; END;

  18. DECLARE A number(30); BEGIN A:=F1(&N); DBMS_OUTPUT.PUT_LINE(A); END; ( OR ) SELECT F1(7369) from dual;

  19. PACKAGES Is an encapsulated collection of related program objects stored together in the database.

  20. Program Objects are :- • Procedures • Functions • Variables • Constants • Cursors • Exceptions

  21. CREATE PACKAGES There are Two Distinct Steps to Create Package 1. PACKAGE SPECIFICATION 2. PACKAGE BODY

  22. PACKAGE SPECIFICATION • CREATE PACKAGE Command <> can declare Program Objects i.e.. Procedures ,Functions etc..

  23. PACKAGE BODY • CREATE PACKAGE BODY Command can declare and define Program Objects

  24. CREATE OR REPLACE PACKAGE P56 IS PROCEDURE P4(ENO NUMBER); END;

  25. CREATE OR REPLACE PACKAGE BODY P56 IS PROCEDURE P4(ENO NUMBER) IS DESG EMP.JOB%TYPE; BEGIN SELECT JOB INTO DESG FROM EMP WHERE EMPNO=ENO; DBMS_OUTPUT.PUT_LINE(DESG); END P4; END P56;

More Related