|
|
|
|
The final step is to generate code. Sample code generated by SnapProc is shown below:
-- ----------------------------------------------------------- -- * AIM : PL/SQL PROCESSING PROGRAM -- -- * APPLICATION: SAMPLE -- -- * PROCESS : SPSUMUP -- -- * GENERATED : ON 1/18/02 12:47:03 PM -- ------------------------------------------------------------- DECLARE
gl_eof boolean := FALSE; gl_fields boolean := FALSE;
gn_rcnt number := 2; gn_crcnt number := 0;
gc_space char(2) := ' '; amount float; payment float;
prices_price float; salestax float;
states_tax float; subtotal float;
upord_amnt float; upord_saltax float;
upord_subtot float; pprices_price float;
pstates_tax float; pupord_amnt float;
pupord_saltax float; pupord_subtot float;
cust_custno number; items_orderno number;
items_productno number; items_quantity number;
orders_cust_no number; orders_ordno number;
prices_prodcode number; upord_cust_no number;
pcust_custno number; pitems_orderno number;
pitems_productno number; pitems_quantity number;
porders_cust_no number; porders_ordno number;
pprices_prodcode number; pupord_cust_no number;
orders_ddate date; upord_ddate date;
porders_ddate date; pupord_ddate date;
cust_address char(30); cust_city char(15);
cust_company char(30); cust_firstname char(15);
cust_lastname char(15); cust_mrms char(3);
cust_region char(9); cust_state char(2);
cust_zip char(10); orders_shiptype char(15);
orders_terms char(32); prices_descrptn char(32);
states_abbrev char(2); states_state char(15);
pcust_address char(30); pcust_city char(15);
pcust_company char(30); pcust_firstname char(15);
pcust_lastname char(15); pcust_mrms char(3);
pcust_region char(9); pcust_state char(2);
pcust_zip char(10); porders_shiptype char(15);
porders_terms char(32); pprices_descrptn char(32);
pstates_abbrev char(2); pstates_state char(15); cust_row char(25); pcust_row char(25);
items_row char(25); pitems_row char(25);
orders_row char(25); porders_row char(25);
prices_row char(25); pprices_row char(25);
states_row char(25); pstates_row char(25);
upord_row char(25); pupord_row char(25); -- ---------------------------
-- DECLARATION OF THE SELECTS
-- ---------------------------
CURSOR cust_cursor is
SELECT rowid,address,city,company
,custno,firstname,lastname
,mrms,region,state
,zip
FROM spcust
WHERE cust.company=:orders_cust_no;CURSOR items_cursor is
SELECT rowid,orderno,productno,quantity
FROM spitems
WHERE items.orderno=:orders_ordno;CURSOR orders_cursor is
SELECT rowid,cust_no,ddate,ordno
,shiptype,terms
FROM sporders
WHERE items.orderno=:orders_ordno;CURSOR prices_cursor is
SELECT rowid,descrptn,price,prodcode
FROM spprices
WHERE prices.prodcode=:items_productno;CURSOR states_cursor is
SELECT rowid,abbrev,state,tax
FROM spstates
WHERE states.abbrev=:cust_state;CURSOR upord_cursor is
SELECT rowid,amnt,cust_no,ddate
,saltax,subtot
FROM spupord
WHERE upord.cust_no=:orders_cust_no;CURSOR dtsum_cursor is
SELECT rowid,ddate,paymnt
FROM dtsum;CURSOR comsum_cursor is
SELECT rowid,company,paymnt
FROM comsum;-- --------- FUNCTION DECLARATION procedure spsumup_fld; procedure spsumup_tot; procedure spsumup_ttl; procedure spsumup_body; procedure spsumup_gfr; procedure spsumup_gnr; procedure spsumup_clrc; procedure spsumup_hd1; procedure spsumup_ft1; procedure spsumup_clr1; procedure spsumup_hd2; procedure spsumup_ft2; procedure spsumup_clr2; procedure spsumup_hd3; procedure spsumup_ft3; procedure spsumup_clr3; procedure spsumup_gnr0; procedure spsumup_gfr0; procedure spsumup_gnr1; procedure spsumup_gfr1; procedure spsumup_summ; -- ---- END FUNCTION DECLARATION -- ----------- -- functions -- ----------- -- ----------------------
-- Clear Composite Record
-- ----------------------
procedure spsumup_clrc is
BEGIN
amount := 0.00;
payment := 0.00;
salestax := 0.00;
return ;
END spsumup_clrc ;
-- --------------
-- Group 1 Header
-- --------------
procedure spsumup_hd1 is
BEGIN
spsumup_hd2;
return ;
END spsumup_hd1 ;-- --------------
-- Group 2 Header
-- --------------
procedure spsumup_hd2 is
BEGIN
spsumup_hd3;
return ;
END spsumup_hd2 ;-- --------------
-- Group 3 Header
-- --------------
procedure spsumup_hd3 is
BEGIN
return ;
END spsumup_hd3 ;-- -----------
-- Write Title
-- -----------
procedure spsumup_ttl is
BEGIN
return ;
END spsumup_ttl ;-- --------------
-- Group 1 Footer
-- --------------
procedure spsumup_ft1 is
BEGIN
spsumup_ft2;
if comsum_cursor%isopen = false then
OPEN comsum_cursor ;
else
CLOSE comsum_cursor ;
OPEN comsum_cursor ;
end if;
INSERT INTO spcomsum(company,paymnt)
VALUES(pcust_company, payment);
spsumup_clr1;
return ;
END spsumup_ft1 ;-- --------------
-- Group 2 Footer
-- --------------
procedure spsumup_ft2 is
BEGIN
spsumup_ft3;
if dtsum_cursor%isopen = false then
OPEN dtsum_cursor ;
else
CLOSE dtsum_cursor ;
OPEN dtsum_cursor ;
end if;
INSERT INTO spdtsum(ddate,paymnt)
VALUES(porders_ddate, payment);
return ;
END spsumup_ft2 ;-- --------------
-- Group 3 Footer
-- --------------
procedure spsumup_ft3 is
BEGIN
if upord_cursor%isopen = false then
OPEN upord_cursor ;
else
CLOSE upord_cursor ;
OPEN upord_cursor ;
end if;
UPDATE spupord
SET spupord.saltax = salestax, spupord.subtot = subtotal
WHERE rowid = pupord_row ;
return ;
END spsumup_ft3 ;-- -------------
-- Write Summary
-- -------------
procedure spsumup_summ is
BEGIN
return ;
END spsumup_summ ;-- ------------------------
-- Move Data to Destination
-- ------------------------
procedure spsumup_fld is
BEGIN
if gl_fields = true then
return;
end if; pcust_address := cust_address;
pcust_city := cust_city;
pcust_company := cust_company;
pcust_custno := cust_custno;
pcust_firstname := cust_firstname;
pcust_lastname := cust_lastname;
pcust_mrms := cust_mrms;
pcust_region := cust_region;
pcust_state := cust_state;
pcust_zip := cust_zip;
pitems_orderno := items_orderno;
pitems_productno := items_productno;
pitems_quantity := items_quantity;
porders_cust_no := orders_cust_no;
porders_ddate := orders_ddate;
porders_ordno := orders_ordno;
porders_shiptype := orders_shiptype;
porders_terms := orders_terms;
pprices_descrptn := prices_descrptn;
pprices_price := prices_price;
pprices_prodcode := prices_prodcode;
pstates_abbrev := states_abbrev;
pstates_state := states_state;
pstates_tax := states_tax;
pupord_amnt := upord_amnt;
pupord_cust_no := upord_cust_no;
pupord_ddate := upord_ddate;
pupord_saltax := upord_saltax;
pupord_subtot := upord_subtot;
pcust_row := cust_row;
pitems_row := items_row;
porders_row := orders_row;
pprices_row := prices_row;
pstates_row := states_row;
pupord_row := upord_row;
amount := items_quantity*prices_price;
spsumup_tot;
gl_fields := TRUE;
return ;
END spsumup_fld ;
-- ------------------
-- Write Detail Lines
-- ------------------
procedure spsumup_body is
BEGIN
spsumup_fld;
return ;
END spsumup_body ;-- -----------------
-- Accumulate Totals
-- -----------------
procedure spsumup_tot is
BEGIN
subtotal := subtotal + amount;
salestax := states_tax*subtotal;
payment := salestax+subtotal;
return ;
END spsumup_tot ;-- --------------------
-- Clear Level 3 Totals
-- --------------------
procedure spsumup_clr3 is
BEGIN
return ;
END spsumup_clr3 ;-- --------------------
-- Clear Level 2 Totals
-- --------------------
procedure spsumup_clr2 is
BEGIN
return ;
END spsumup_clr2 ;-- --------------------
-- Clear Level 1 Totals
-- --------------------
procedure spsumup_clr1 is
BEGIN
subtotal := 0;
return ;
END spsumup_clr1 ;
-- --------------------------
-- get first composite record
-- --------------------------
procedure spsumup_gfr is
BEGIN
-- ----- READING THE FIRST RECORD OF MASTER FILE ------
if orders_cursor%isopen = false then
OPEN orders_cursor ;
else
CLOSE orders_cursor ;
OPEN orders_cursor ;
end if;
loop
FETCH orders_cursor
INTO orders_row,orders_cust_no,orders_ddate
,orders_ordno,orders_shiptype,orders_terms
;
exit;
end loop;
spsumup_gfr0;
return ;
END spsumup_gfr ;-- -------------------------
-- get next composite record
-- -------------------------
procedure spsumup_gnr is
BEGIN
spsumup_gnr1;
return ;
END spsumup_gnr ;-- -----------------------------------
-- get first composite record, level 0
-- -----------------------------------
procedure spsumup_gfr0 is
BEGIN
gl_eof := false;
while TRUE loop
-- ----- CHECK EOF() OF MASTER -------
if orders_cursor%notfound then
gl_eof := TRUE;
return;
end if; -- --------------------
-- EXACT LOOKUP IN SPCUST
-- ---------------------
if cust_cursor%isopen = false then
OPEN cust_cursor ;
else
CLOSE cust_cursor ;
OPEN cust_cursor ;
end if;
loop
FETCH cust_cursor
INTO cust_row,cust_address,cust_city
,cust_company,cust_custno,cust_firstname
,cust_lastname,cust_mrms,cust_region
,cust_state,cust_zip;
exit;
end loop;
if cust_cursor%notfound then
EXEC SQL CLOSE cust_cursor ; -- ----- CASE : TERMINATE --------
return ; -- exit(0)
end if; -- --------------------
-- EXACT LOOKUP IN SPUPORD
-- ---------------------
if upord_cursor%isopen = false then
OPEN upord_cursor ;
else
CLOSE upord_cursor ;
OPEN upord_cursor ;
end if;
loop
FETCH upord_cursor
INTO upord_row,upord_amnt,upord_cust_no
,upord_ddate,upord_saltax,upord_subtot
;
exit;
end loop;
if upord_cursor%notfound then
----- CASE : BLANK -------
upord_amnt := 0.00;
upord_cust_no := 0;
upord_ddate := NULL;
upord_saltax := 0.00;
upord_subtot := 0.00;
end if; -- --------------------
-- EXACT LOOKUP IN SPSTATES
-- ---------------------
if states_cursor%isopen = false then
OPEN states_cursor ;
else
CLOSE states_cursor ;
OPEN states_cursor ;
end if;
loop
FETCH states_cursor
INTO states_row,states_abbrev,states_state
,states_tax;
exit;
end loop;
if states_cursor%notfound then
----- CASE : BLANK -------
states_abbrev := gc_space;
states_state := gc_space;
states_tax := 0.00;
end if;
spsumup_gfr1; -- ----- CASE : SKIP -------
if gl_eof = true then
spsumup_gnr0;
end if;
exit ; -- end while loop
end loop;
return ;
END spsumup_gfr0 ;-- -----------------------------------
-- get next composite record, level 0
-- -----------------------------------
procedure spsumup_gnr0 is
BEGIN
gl_eof := false ;
while TRUE loop
<<lbl1>>
-- ----- SKIP MASTER FOR NEXT RECORD -------
loop
FETCH orders_cursor
INTO orders_row,orders_cust_no,orders_ddate
,orders_ordno,orders_shiptype,orders_terms
;
exit;
end loop;
-- ----- CHECK EOF() OF MASTER -------
if orders_cursor%notfound then
gl_eof := TRUE;
return;
end if; -- --------------------
-- EXACT LOOKUP IN SPCUST
-- ---------------------
if cust_cursor%isopen = false then
OPEN cust_cursor ;
else
CLOSE cust_cursor ;
OPEN cust_cursor ;
end if;
loop
FETCH cust_cursor
INTO cust_row,cust_address,cust_city
,cust_company,cust_custno,cust_firstname
,cust_lastname,cust_mrms,cust_region
,cust_state,cust_zip;
exit;
end loop;
if cust_cursor%notfound then
-- ----- CASE : TERMINATE --------
return ; -- exit(0)
end if; -- --------------------
-- EXACT LOOKUP IN SPUPORD
-- ---------------------
if upord_cursor%isopen = false then
OPEN upord_cursor ;
else
CLOSE upord_cursor ;
OPEN upord_cursor ;
end if;
loop
FETCH upord_cursor
INTO upord_row,upord_amnt,upord_cust_no
,upord_ddate,upord_saltax,upord_subtot
;
exit;
end loop;
if upord_cursor%notfound then
----- CASE : BLANK -------
upord_amnt := 0.00;
upord_cust_no := 0;
upord_ddate := NULL;
upord_saltax := 0.00;
upord_subtot := 0.00;
end if; -- --------------------
-- EXACT LOOKUP IN SPSTATES
-- ---------------------
if states_cursor%isopen = false then
OPEN states_cursor ;
else
CLOSE states_cursor ;
OPEN states_cursor ;
end if;
loop
FETCH states_cursor
INTO states_row,states_abbrev,states_state
,states_tax;
exit;
end loop;
if states_cursor%notfound then
----- CASE : BLANK -------
states_abbrev := gc_space;
states_state := gc_space;
states_tax := 0.00;
end if;
spsumup_gfr1; -- ----- CASE : SKIP -------
if gl_eof = false then
exit;
else
goto lbl1;
end if;
exit ; -- end while loop
end loop ; -- while true
return ;
END spsumup_gnr0 ;-- -----------------------------------
-- get first composite record, level 1
-- -----------------------------------
procedure spsumup_gfr1 is
BEGIN
gl_eof := false; -- -----------
-- SCAN FOR SPITEMS
-- -----------
if items_cursor%isopen = false then
OPEN items_cursor ;
else
CLOSE items_cursor ;
OPEN items_cursor ;
end if;
loop
FETCH items_cursor
INTO items_row,items_orderno,items_productno
,items_quantity;
exit;
end loop;
while TRUE loop
-- ----- NOT FOUND() ------
if items_cursor%notfound then
-- ----- CASE : SKIP ------
gl_eof := true;
CLOSE items_cursor ;
return;
end if;
CLOSE items_cursor ; -- --------------------
-- EXACT LOOKUP IN SPPRICES
-- ---------------------
if prices_cursor%isopen = false then
OPEN prices_cursor ;
else
CLOSE prices_cursor ;
OPEN prices_cursor ;
end if;
loop
FETCH prices_cursor
INTO prices_row,prices_descrptn,prices_price
,prices_prodcode;
exit;
end loop;
if prices_cursor%notfound then
----- CASE : BLANK -------
prices_descrptn := gc_space;
prices_price := 0.00;
prices_prodcode := 0;
end if;
exit;
end loop; -- while true
return ;
END spsumup_gfr1 ;-- ----------------------------------
-- get next composite record, level 1
-- ----------------------------------
procedure spsumup_gnr1 is
BEGIN
gl_eof := false ;
while TRUE loop
<<lbl2>>
-- ----- SKIP SPITEMS FOR NEXT RECORD -----
if items_cursor%isopen = false then
OPEN items_cursor ;
else
CLOSE items_cursor ;
OPEN items_cursor ;
end if;
gn_crcnt := 0;
loop
FETCH items_cursor
INTO items_row,items_orderno,items_productno
,items_quantity;
if items_cursor%notfound then
gn_rcnt := 2 ;
exit ;
else
gn_crcnt := gn_crcnt + 1 ;
exit when gn_crcnt >= gn_rcnt;
end if;
end loop; -- ----- NOT FOUND() ------
if items_cursor%notfound then
spsumup_gnr0;
return;
else
gn_rcnt := gn_rcnt + 1;
end if;
CLOSE items_cursor ; -- --------------------
-- EXACT LOOKUP IN SPPRICES
-- ---------------------
if prices_cursor%isopen = false then
OPEN prices_cursor ;
else
CLOSE prices_cursor ;
OPEN prices_cursor ;
end if;
loop
FETCH prices_cursor
INTO prices_row,prices_descrptn,prices_price
,prices_prodcode;
exit;
end loop;
if prices_cursor%notfound then
----- CASE : BLANK -------
prices_descrptn := gc_space;
prices_price := 0.00;
prices_prodcode := 0;
end if;
exit;
end loop; -- while true
return ;
END spsumup_gnr1 ;BEGIN
spsumup_clrc;
spsumup_gfr;
if gl_eof = true then
return ; -- exit(0)
end if;
spsumup_clr1;
spsumup_fld;
spsumup_hd1; -- ------------MAIN PROGRAM
while TRUE loop
spsumup_body;
spsumup_gnr;
gl_fields := false;
if gl_eof = true then
gl_fields := true;
exit;
elsif pcust_company <> cust_company then
spsumup_ft1;
spsumup_hd1;
elsif porders_ddate <> orders_ddate then
spsumup_ft2;
spsumup_hd2;
elsif porders_ordno <> orders_ordno then
spsumup_ft3;
spsumup_hd3;
end if;
end loop ;-- --------------- TERMINATION spsumup_fld;
spsumup_ft1;
if orders_cursor%isopen = true then
CLOSE orders_cursor;
end if;END ; -- end of main / |
|
