Hi all,
I need your help in creating a stored procedure. Since I've not used PL SQL in years, it seems to be complicated to me now.. I would appreciate any kind of help.
I created a table called auditing. This table needs to be inserted/updated via the SP by comparing/evaluating with another table acfg. Data in acfg will be entered by the users. Sample Data of acfg table:
ID Mand Bcode Skey I_O Tname Cname Func Group_col1 WhereSQL Message
1 M1 P1 1 O Table1 COUNTRY COUNT COUNTRY %1 entries found for %2
2 M1 P1 2 O Table1 FIRSTNAME COUNT %1 total lines
3 M1 P2 1 I Table1 FIRSTNAME COUNT %1 lines found
SQL>desc auditing;
Name Null? Type
-------------------- -------- -------------------------
ID NOT NULL NUMBER(5)
CDATE DATE
CCODE VARCHAR2(10)
CTERM VARCHAR2(255)
PID NUMBER(20)
MAND VARCHAR2(10)
SKEY NUMBER(10)
MESSAGE VARCHAR2(2000)
SOURCE VARCHAR2(255)
SOURCE_YN VARCHAR2(1)
The stored procedure auditingsp will be called with these input parameters (pid, mand, bcode, opp, I_O, source).
*1*. SP should insert record into auditing table.
*2.* If acfg.v_yn is ‘Y’ (compare with mand=acfg.mand, bcode=acfg.bcode) following logic should be used:
SP should generate a dynamic sql from the a.cfg table
select func(column_name) from tname where mand=’…’ (If I_O=’I’)
select func(column_name) from tname where mand=’…’ and pid=’…’ (If I_O = ‘O’)
So, query for the 1st record in acfg table should be select count(country) from table1 where mand=’M1’ and pid=’..’ group by country;
Note: group_col1, group_col2, group_col3 are optional. But should be considered in the select query when NOT NULL.
*3.* The output of the above query should be written to auditing.message field by filling in the result of the above query in the acfg.message field.
In this example, acfg.message has this value: %1 entries found for %2. So %1 and %2 should be filled in from the output of the above query. The auditing.message should have this
value 20 entries found for Germany.
For the acfg table above, the auditing table should be inserted/updated like this:
ID Cdate Ccode Pid Mand Skey Message
1 01.04.2009 08:30:00 P1 12345 M1 1 20 entries found for Germany
2 01.04.2009 08:30:01 P1 12345 M1 2 10 entries found for USA
3 01.04.2009 08:30:02 P1 12345 M1 3 2 entries found for Denmark
4 01.04.2009 08:30:03 P1 12345 M1 4 32 total lines
5 01.04.2009 08:31:00 P2 12346 M1 1 32 lines found
*4.* Also, skey in auditing table should be assigned for every I or O call based on mand and bcode in the SP.
Please give me your suggestions on this for steps 2,3 and 4.. I'm not sure if I need to use cursors for step 2 & 3.
CREATE OR REPLACE procedure PUBS.auditingsp (pid IN number, mand IN varchar2, bcode IN varchar2, opp IN number, source IN varchar2)
IS
BEGIN
declare
seqno number(38);
begin
select audseq.nextval into seqno from dual ;
insert into auditing (ID,cdate,pid,mand,source)
values(seqno,sysdate,pid,mand,source);
/* steps 2,3,4 */
end;
end auditingsp;
/
Thanks a lot for all your help
Edited by: newbiegal on Jul 6, 2009 2:07 PM
Edited by: newbiegal on Jul 6, 2009 2:10 PM
Edited by: newbiegal on Jul 7, 2009 11:53 AM