I am trying to create a package and need some advice, I have not done alot of PL/SQL. The premise is that we want to send SMS and /or email messages to client on various events. We cannot use table triggers for various technical reasons, please don't suggest it.
The package will be run every hour to pick up new events and insert a record into two different tables, one for SMS messages and one for email. We already have an app that picks up the messages from those tables and sends them, I just need to populate the two tables.
I need some advice on how to put this together. I have the various cursors perfected, now I just need to consolidate the package and reduce redundant sections. I have the package body like this:
CREATE OR REPLACE PACKAGE BODY SMS_EMAIL_JOB
AS
V_JOB_START DATE;
....bunch of other variables....
FUNCTION Mob_num (PH1 IN VARCHAR2)
RETURN NUMBER
IS
.... bunch of code to return a valid mobile or null....
END MOB_NUM;
PROCEDURE CREDIT_CARD_EXPIRY IS
CURSOR CUR_CREDIT_EXP
SELECT
..... bunch of code that returns message type, first name, expiry date, mobile number, email... blah blah blah;
CUR_REC CUR_CREDIT_EXP%ROWTYPE;
BEGIN
FOR CUR_REC IN CUR_CREDIT_EXP LOOP
IF ( CUR_REC.PREFERENCE IN ( 1, 3 )
INSERT INTO SMS_TABLE
... blah blah blah
END IF;
IF ( CUR_REC.PREFERENCE IN ( 2, 3 )
INSERT INTO EMAIL_TABLE
... blah blah blah
END IF;
END LOOP;
END CREDIT_CARD_EXPIRY;
PROCEDURE ORDER_SENT IS
CURSOR CUR_ORDER_SENT
SELECT
..... bunch of code that returns message type, first name, order sent date... blah blah blah, exactly the same format as the credit card expiry above;
CUR_REC CUR_ORDER_SENT%ROWTYPE;
BEGIN
FOR CUR_REC IN CUR_ORDER_SENT LOOP
IF ( CUR_REC.PREFERENCE IN ( 1, 3 )
INSERT INTO SMS_TABLE
... blah blah blah
END IF;
IF ( CUR_REC.PREFERENCE IN ( 2, 3 )
INSERT INTO EMAIL_TABLE
... blah blah blah
END IF;
END LOOP;
END ORDER_SENT;
END SMS_EMAIL_JOB;
As you can see the inserting code is repeated in both procedures (I will end up with about 7 procedures). How do I create a separate section so I can reuse the same code that does the inserting again and again for each procedure?
I have been struggling to make it so that each procedure looks something like this:
Procedure XXXX IS
Cursor CUR_XXXX
Select blah, blah, blah from table;
BEGIN
insert_rec(CUR_XXXX); <- This passes the results of the cursor to insert_rec that does the inserting into the two tables for me
END XXXX;
Should the insert_rec be a procedure? A function? Should it go before the procedures that use it? Should it be outside this package as a separate procedure?