These are the schema objects that groups are logically related PL/SQL types, variables, and subprograms. They have two mandatory parts:
- Package specification
- Package body or definition
Package Specification
This is the interface to the package. This only declares the types, variables, constants, exceptions, cursors, and subprograms that are to be referenced from outside the package. We can also say that, it is said to contain all the information about the content of the package, but excludes the code for the subprograms. All the objects are placed in the specification are said to be called public objects. Any of the subprogram not in the package specification but then they are coded in the package body is called a private object.
CREATE PACKAGE teachers_sal AS
PROCEDURE find_sal(t_id teachers.id%type);
END teachers_sal;
/
When we execute the above code is executed at the SQL prompt.
Package created.
PACKAGE BODY
This code as for various methods that are declared in the package specification and also other private declarations, that are hidden from the code outside the package. The statement CREATE PACKAGE BODY is used for creating the package body.
Example:
CREATE OR REPLACE PACKAGE BODY teachers_sal AS
PROCEDURE find_sal(t_id teachers.id%TYPE) IS
t_sal teachers.salary%TYPE;
BEGIN
SELECT salary INTO t_sal
FROM teachers
WHERE id = t_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END teachers_sal;
/
Output:
Package body created.
Using the package elements
The package elements like variables, procedures or functions are accessed by the syntax that is given below:
package_name.element_name;
Example:
DECLARE
code teachers.id%type := &tt_id;
BEGIN
teachers_sal.find_sal(code);
END;
/
Output:
Enter value for tt_id: 2
Salary: 3000
PL/SQL procedure successfully completed.