CodesJava

Easy learning with example program codes

Oracle pl sql package tutorial


Oracle pl sql package:

A package is a schema object that groups logically related PL/SQL types, variables and subprograms.

Parts of a package:

1. Package specification
2. Package body or definition

Package specification:

The package specification is the package interface which declares the types, variables, constants, exceptions, cursors and subprograms that can be referenced from outside the package.
Note: All objects in the package specification are known as public objects.

Syntax of package specification:

CREATE PACKAGE package_name AS
   PROCEDURE procedure_name;
END cust_sal;
/

Example:

CREATE PACKAGE emp_sal AS
   PROCEDURE find_sal(e_id employees.id%type);
END emp_sal;
/

Package body or definition:

The package body or definition defines the queries for the cursors and the code for the subprograms.
Note: All objects in the package body or definition are known as private objects.

Syntax of body or definition:

CREATE OR REPLACE PACKAGE BODY package_name AS
   PROCEDURE procedure_name IS
   	//procedure body
   END procedure_name;
END package_name;
/

Example:

CREATE OR REPLACE PACKAGE BODY emp_sal AS
   PROCEDURE find_sal(e_id employees.id%TYPE) IS
   e_sal employees.salary%TYPE;
   BEGIN
      SELECT salary INTO e_sal
      FROM employees
      WHERE id = e_id;
      dbms_output.put_line('Salary: '|| e_sal);
   END find_sal;
END emp_sal;
/

 



Industrial Training

We offers Placement Oriented Training on Java, Spring, JSF, Hibernate, PHP, AngularJS, Angular 4, PLSQL, Oracle BI Publisher etc. We also provides Online training, please mail us at hr@codesjava.com.

Development

We also provides the Development services for Website Development , Java Development, PHP Development, Android App Development etc. You can contact us on hr@codesjava.com.

Copyright © 2019 CodesJava DMCA.com Protection Status SiteMap Reference: Java Wiki