web analytics
Oracle PL/SQL Packages, Stored Procedures and Functions Options
davegate
Posted: Tuesday, October 17, 2017 9:13:57 AM

Rank:Advanced Member
Groups: Member
Joined: 12/14/2015
Posts: 182
Points: 894

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions.

A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause.

Sponsor
Posted: Tuesday, October 17, 2017 9:13:57 AM
 
davegate
Posted: Tuesday, October 17, 2017 9:15:28 AM

Rank:Advanced Member
Groups: Member
Joined: 12/14/2015
Posts: 182
Points: 894

How To Create a PL/SQL Package?

To create package specs, use the PL/SQL statement CREATE PACKAGE. A CREATE PACKAGE BODY statement defines the package body.

CREATE OR REPLACE PACKAGE personnel IS

  FUNCTION count_employee
  return number;
 
  FUNCTION f_get_employee_by_id
  (
   i_emp_id number
  )
  RETURN SYS_REFCURSOR;
  PROCEDURE p_get_employee_by_id
  (
     i_emp_id   in number,
     emp_refcur out SYS_REFCURSOR
  );
 
end personnel;

/
CREATE OR REPLACE PACKAGE BODY personnel IS

  FUNCTION count_employee
  return number
  IS
   num number(4);
  BEGIN
   select count(*) into num from dev.employee;
    return num;
   
  END count_employee;
 
  FUNCTION f_get_employee_by_id
  (
   i_emp_id number
  )
  RETURN SYS_REFCURSOR
  IS
    emp_refcur      SYS_REFCURSOR;
  BEGIN
   
    OPEN emp_refcur FOR
    SELECT * from dev.employee where id = i_emp_id;
    RETURN emp_refcur;
  END f_get_employee_by_id;

  PROCEDURE p_get_employee_by_id
  (
     i_emp_id   in number,
     emp_refcur out SYS_REFCURSOR
  )
  AS
  BEGIN
   
    OPEN emp_refcur FOR
    SELECT * from dev.employee where id = i_emp_id;
   
  END p_get_employee_by_id;
 
END personnel;
/
Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2018 Digcode.com. All rights reserved.