Hi folks,
A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package always has a specification, which declares the public items that can be referenced from outside the package. If the public items include cursors or subprograms, then the package must also have a body. For example, if you define a pack that only contains variables declaration, you don’t need to declare the body.
The body must define queries for public cursors and code for public subprograms. The body can also declare and define private items that cannot be referenced from outside the package but are necessary for the internal workings of the package. Finally, the body can have an initialization part, whose statements initialize variables and do other one-time setup steps, and an exception-handling part.
Basic Syntax for body and specification
--spec create or replace PACKAGE <name> AS --your code END <name>; / --body create or replace PACKAGE BODY <name>AS --your code END <name>; /
Example 1
Well, in this example I’m creating a package with spec and body with a procedure and a function, you can check how I did each statement separated in the links below
create or replace PACKAGE PACKAGE2 AS FUNCTION F_GET_NEW_CITY_ID RETURN NUMBER; PROCEDURE p_insert_log (p_descr_log varchar2, p_msg_return OUT varchar2); END PACKAGE2; /
create or replace
PACKAGE BODY PACKAGE2 AS
FUNCTION F_GET_NEW_CITY_ID RETURN NUMBER AS
v_new_id number := 0;
v_is_an_used_id varchar2(1) := 'N';
BEGIN
select max(nvl(id, 0)) + 1 into v_new_id from city;
LOOP
BEGIN
select 'Y' into v_is_an_used_id from city where id = v_new_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_is_an_used_id := 'N';
END;
EXIT WHEN v_is_an_used_id = 'N';
v_new_id := v_new_id +1;
END LOOP;
RETURN v_new_id;
END F_GET_NEW_CITY_ID;
PROCEDURE p_insert_log (p_descr_log varchar2,
p_msg_return OUT varchar2) AS
v_new_id NUMBER;
v_local_except EXCEPTION;
v_error_msg VARCHAR2(255);
BEGIN
select nvl(max(id), 0) + 1 into v_new_id from it_tutorial_log;
IF NVL(p_descr_log, 'N') = 'N' THEN
v_error_msg := 'The description is a required column!';
raise v_local_except;
END IF;
insert into it_tutorial_log (id, descr_log, date_log, user_log) values (v_new_id, p_descr_log, sysdate, user);
EXCEPTION
WHEN v_local_except THEN
p_msg_return := 'Program: p_insert_log - Error: ' || v_error_msg;
END p_insert_log;
END PACKAGE2;
How to test/execute in an anonymous block
Procedure
DECLARE
P_DESCR_LOG VARCHAR2(200);
P_MSG_RETURN VARCHAR2(200);
BEGIN
P_DESCR_LOG := NULL;
PACKAGE2.P_INSERT_LOG(
P_DESCR_LOG => P_DESCR_LOG,
P_MSG_RETURN => P_MSG_RETURN
);
DBMS_OUTPUT.PUT_LINE('P_MSG_RETURN = ' || P_MSG_RETURN);
END;
Function
DECLARE
v_Return NUMBER;
BEGIN
v_Return := PACKAGE2.F_GET_NEW_CITY_ID();
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
This post is part of a series about the most common structures in PL/SQL. You can check the rest of them in the link below.
Please write down in the comments any further questions you may have. I’ll be happy to help you.