Development on Oracle 10g Packages, Stored procedures, Functions

An Oracle package is a schema object that groups logically related PL/SQL types, items and subprograms.

Packages have two parts, the specification and the body.

• The specification is the interface into the package. It describes all the types, variables, procedures, etc within the package.
• The body has all the implementation. Inside the body you would actually write the procedures, assign the values, and have all the details and logic behind the package.

Sample oracle package

Specification

create or replace PACKAGE ChallengePkg
IS

TYPE ref_cur_type

IS REF CURSOR;

PROCEDURE GetChallenge(ChallengeId IN CHALLENGE.ID%TYPE, challenge OUT ref_cur_type);

PROCEDURE GetChallenges(challenges OUT ref_cur_type);
PROCEDURE InsertChallenge(challengeId OUT CHALLENGE.ID%TYPE,
creationUserId IN CHALLENGE.CREATIONUSERID%TYPE,
publicId IN CHALLENGE.PUBLICID%TYPE,
creationDate IN CHALLENGE.CREATIONDATE%TYPE);

PROCEDURE UpdateChallenge(challengeId IN CHALLENGE.ID%TYPE,
argCreationUserId IN CHALLENGE.CREATIONUSERID%TYPE,
argPublicId IN CHALLENGE.PUBLICID%TYPE,
argCreationDate IN CHALLENGE.CREATIONDATE%TYPE);

PROCEDURE DeleteChallenge(challengeId IN CHALLENGE.ID%TYPE);

END ChallengePkg;

Body

create or replace PACKAGE BODY ChallengePkg
IS

PROCEDURE GetChallenge(ChallengeId IN CHALLENGE.ID%TYPE, challenge OUT ref_cur_type)
IS
BEGIN
OPEN challenge
FOR SELECT CHALLENGE.ID,CHALLENGE.CREATIONUSERID,CHALLENGE.PUBLICID, CHALLENGE.CREATIONDATE
FROM CHALLENGE
WHERE CHALLENGE.ID = ChallengeId;
END GetChallenge;

PROCEDURE GetChallenges(challenges OUT ref_cur_type)
IS

BEGIN
OPEN challenges
FOR SELECT CHALLENGE.ID,CHALLENGE.CREATIONUSERID,CHALLENGE.PUBLICID, CHALLENGE.CREATIONDATE
FROM CHALLENGE;
END GetChallenges;

PROCEDURE InsertChallenge(challengeId OUT CHALLENGE.ID%TYPE,
creationUserId IN CHALLENGE.CREATIONUSERID%TYPE,
publicId IN CHALLENGE.PUBLICID%TYPE,
creationDate IN CHALLENGE.CREATIONDATE%TYPE)

IS
BEGIN
INSERT INTO CHALLENGE(CREATIONUSERID,PUBLICID,CREATIONDATE)
VALUES (creationUserId,publicId,creationDate);
COMMIT;

SELECT CHALLENGE_SEQ.currval INTO challengeId FROM dual;

END InsertChallenge;

PROCEDURE UpdateChallenge(challengeId IN CHALLENGE.ID%TYPE,
argCreationUserId IN CHALLENGE.CREATIONUSERID%TYPE,
argPublicId IN CHALLENGE.PUBLICID%TYPE,
argCreationDate IN CHALLENGE.CREATIONDATE%TYPE)
IS
BEGIN
UPDATE CHALLENGE SET CREATIONUSERID = argCreationUserId, PUBLICID= argPublicId, CREATIONDATE = argCreationDate
WHERE ID = challengeId;
COMMIT;
END UpdateChallenge;

PROCEDURE DeleteChallenge(challengeId IN CHALLENGE.ID%TYPE)
IS
BEGIN
DELETE FROM CHALLENGE
WHERE ID = challengeId;
COMMIT;
END DeleteChallenge;

END ChallengePkg;

Some benefits of using packages

1. Objects don't get invalidated when you make changes to the body. That saves a lot of recompilation and makes changing the implementation much more painless. You will still have to recompile if you change the specification, but that's not something you should be doing very often.

2. You can "overload" subprograms (procedures/functions). You can have several subprograms with the same name, but with a different number of parameters, or different types.

3. You can have persistent variables throughout a session without storing anything in a database table. Packages can have variables and constants that are initialized when the package is first used within a session, and then they are available for the remainder of the session for all future references to anything within that package. That comes in very handy.

4. Speaking of initialization, being able to call a procedure automatically the first time a package is used within a session can also come in very handy.

5. You can take advantage of "encapsulation." In essence, you can hide the implementation details from users but still give them all the information they need to use the package. Since they aren't aware of the details, that means you can change them with minimal impact or risk. Packages also support private subprograms and variables which are available only to other subprograms within the package, and remain completely hidden and inaccessible to anything outside the package.

6. You may notice some performance improvement when using packages. When you first use a package, the entire package may be loaded into memory, meaning fewer disk I/Os as you use the related items within.