Saved Bookmarks
| 1. |
Block Structure in PL/SQL |
|
Answer» In PL/SQL, packages are the schema objects that groups logically RELATED PL/SQL types and variables. A Package has the following TWO parts:
The specification has the information about the package content. It declares the types, variables, constant, cursors, etc. It excludes the code for subprograms. The objects in the specification are the public object, however, a subprogram not part of the specification is a private object.
The Package Body has the implementation of subprogram declared in the specification. To create Package Body, use the CREATE PACKAGE BODY statement. The following is our sample table: <DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | MARKETING | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+The following is an example wherein we have a package “dept_pck”: CREATE OR REPLACE PACKAGE BODY dept_pck AS PROCEDURE get_budget(d_id deptbudget.id%TYPE) IS d_budget deptbudget.budget%TYPE; BEGIN SELECT budget INTO d_budget FROM deptbudget WHERE id = d_id; dbms_output.put_line('Department Budget: '|| d_budget); END get_budget; END dept_pck; /On executing the above code, the following is VISIBLE: Package body created.Now, to access the package variable, procedures, ett, you can use the dot operator as shown below: package_name.element_name;Continuing our example: DECLARE myid deptbudget.id%type := &dd_id; BEGIN dept_pck. get_budget (myid); END; /When you will run the above code, you need to input the department id. Let’s say your input is department id 3, the output would be: Department Budget: 98979 |
|