Creation of Oracle Packages

The composition of a pack

1) Baotou (package): Baotou part of the declaration of data types, constants, variables, cursors, subroutines and exception error handling,

These elements are the public elements of the package.

2) Package Body: The package body is the specific implementation of the package Definition section, which is responsible for the declaration of the subroutine in the Baotou

Provides a concrete implementation that can also declare private elements of a package in the package body

3) Baotou and the package body separately compiled, and as two separate objects stored in the database dictionary

--Delete Package

--drop package pkg_lxg_test;

--1) First create the package header (the SQL of the header and body does not write in the same file)

----can declare multiple procedure here (if external calls are declared)

Create or replace package pkg_lxg_test as

Procedure Deletetask (Tasktypecode in VARCHAR2,--Task type code

Businesskey in Varchar2,--Business number

ResultCode out VARCHAR2,--Result code

Resultmessage out VARCHAR2--Results information

);

End Pkg_lxg_test;

--) 2 re-create the package body (the head above cannot be executed with the body, and should be written in two SQL files for handover)

---) 3 When the creation is complete, it will be compiled automatically if it is created successfully.

---) 4 If you are not successful in creating the package, you can view the error in the View and description

---) 5 package after creation is complete,--> right-click, View---> then select proc in the package you want to test and right---"test

CREATE OR REPLACE Package BODY Nbadata.pkg_lxg_test as

function function2 (Tasktypecode in Varchar2,businesskey in varchar2) return VARCHAR2

Is

V_preview_environment VARCHAR2 (4);

V_voucher_no VARCHAR2 (50);

V_count number default 0;

Begin

v_preview_environment:= ' 1 ';

--dbms_output.put_line (' performed method function2 ');

return v_preview_environment;

End function2;

--Define procedure here, you can specify multiple

Procedure Deletetask (Tasktypecode in VARCHAR2,--Task type code

Businesskey in Varchar2,--Business number

ResultCode out VARCHAR2,--Result code

Resultmessage out VARCHAR2--Results information

) is

Begin

Begin

ResultCode: = ' 0 ';

Resultmessage: = ' Delete asynchrouns task sucess ';

--dbms_output.put_line (' Successfully executed stored procedure ');

exception

When others then

ResultCode: = ' 1 ';

Resultmessage: = ' fail in deleting Asynchrouns task sucess ';

End

End Deletetask;

End Pkg_lxg_test;

/

Creation of Oracle Packages