| < Day Day Up > |
|
These scripts do not include trigger code. Use of triggers is easy but contradictory to performance tuning.
–every 1 second declare jobno number; i integer default 1; begin for j in 1..10 loop dbms_job.submit(jobno,'SIMULATE;',SYSDATE, 'SYSDATE+1/86400'); end loop; commit; end; / declare cursor cJobs is select job from user_jobs; begin for rJob in cJobs loop dbms_job.remove(rJob.job); end loop; end; / commit; create or replace function random(n IN NUMBER DEFAULT 1) return integer is random integer; begin select to_number(to_char(SYSTIMESTAMP,'FF3'))+1 into random from dual; random := (random/1000)*n; if random = 0 then random := 1; end if; return random; exception when others then dbms_output.put_line('FUNC: random '||SQLERRM (SQLCODE)); end; / alter function random compile; create or replace function getTime(ptime float default 0) return float is begin return to_number(to_char(systimestamp,'SSSSS.FF')) – ptime; exception when others then dbms_output.put_line('FUNC: getTime ' ||SQLERRM(SQLCODE)); end; / alter function getTime compile; create or replace procedure getPrice ( pstock_id IN integer default 0 ,psupplier_id OUT integer ,pprice OUT float ) as begin –find cheapest price for stock_id select a.supplier_id, a.price into psupplier_id, pprice from ( select supplier_id, min(price) as price from stocksource where stock_id = pstock_id group by supplier_id order by price ) a where rownum = 1; exception when others then dbms_output.put_line('PROC: getPrice ' ||SQLERRM(SQLCODE)); end; / alter procedure getprice compile; create or replace procedure InsertSANDP (pdte IN date DEFAULT SYSDATE) as vcustomer_id integer; vsupplier_id integer; vstock_id integer; vorder_id integer; vstockmovement_id integer; vtransaction_id integer; vcheque_id integer; vqty integer; vlines integer; vqtyonhand integer; vprice float; vamount float; vseq# integer; vmarkup float default 1.01; vtime float; begin vtime := to_number(to_char(systimestamp,'SSSSS.FF')); select max(customer_id) into vcustomer_id from customer; vcustomer_id := random(vcustomer_id); select max(stock_id) into vstock_id from stock; vstock_id := random(vstock_id); vqty := random(20); vlines := vqty; select qtyonhand into vqtyonhand from stock where stock_id = vstock_id; if vqty - vqtyonhand > 0 then vqty := vqty - vqtyonhand; —vqty - vqtyonhand always +ve getPrice(vstock_id,vsupplier_id,vprice); vamount := vqty * vprice; insert into orders values(orders_seq.nextval ,null,vsupplier_id,'P',(vamount*–1),pdte) returning order_id into vorder_id; insert into transactions values(transactions_seq.nextval ,'P',null,vsupplier_id,vorder_id,(vamount*–1),pdte ,(select drcoa# from posting where posting='PurchaseInvoice') ,(select crcoa# from posting where posting='PurchaseInvoice')) returning transaction_id into vtransaction_id; insert into cashbook values(cheque_seq.nextval ,(vamount*–1),pdte,'Purchase Invoice '||to_char(vtransaction_id) ,(select drcoa# from posting where posting='PurchaseInvoice_CashBook') ,(select crcoa# from posting where posting='PurchaseInvoice_CashBook') ,vtransaction_id) returning cheque_id into vcheque_id; for vseq# in 1..vlines loop insert into stockmovement values(stockmovement_ seq.nextval ,vstock_id,vqty,vprice,pdte) returning stockmovement_id into vstockmovement_id; insert into ordersline values(vorder_id,vseq# ,(vprice*–1),vstockmovement_id); insert into transactionsline values(vtransaction_id,vseq# ,(vprice*–1),vstockmovement_id); insert into cashbookline values(vcheque_id, vseq#,(vprice*–1)); end loop; dbms_output.put_line('PROC: InsertSANDP Purchase Complete '||to_char(getTime (vtime))); end if; –make sale, gen transaction and pay for it for vqty – vqtyonhand getPrice(vstock_id,vsupplier_id,vprice); vprice := vprice * vmarkup; vamount := vqty * vprice; insert into orders values(orders_seq.nextval ,vcustomer_id,null,'S',vamount,pdte) returning order_id into vorder_id; insert into transactions values(transactions_seq.nextval ,'S',vcustomer_id,null,vorder_id,vamount,pdte ,(select drcoa# from posting where posting='SalesInvoice') ,(select crcoa# from posting where posting='SalesInvoice')) returning transaction_id into vtransaction_id; insert into cashbook values(cheque_seq.nextval ,vamount,pdte,'Sales Invoice '||to_char(vtrans action_id) ,(select drcoa# from posting where posting='SalesInvoice_CashBook') ,(select crcoa# from posting where posting='SalesInvoice_CashBook') ,vtransaction_id) returning cheque_id into vcheque_id; for vseq# in 1..vlines loop insert into stockmovement values(stockmovement_ seq.nextval ,vstock_id,(vqty*–1),vprice,pdte) returning stockmovement_id into vstockmovement_id; insert into ordersline values(vorder_id ,vseq#,vprice,vstockmovement_id); insert into transactionsline values (vtransaction_id ,vseq#,vprice,vstockmovement_id); insert into cashbookline values(vcheque_id, vseq#,vprice); end loop; dbms_output.put_line('PROC: InsertSANDP Sale Complete '||to_char(getTime(vtime))); commit; exception when others then dbms_output.put_line('PROC: InsertSANDP '||SQLERRM (SQLCODE)); rollback; end; / alter procedure InsertSANDP compile; create or replace procedure UpdateSORP (pdte IN date DEFAULT SYSDATE) as vminorder_id integer; vmaxorder_id integer; vorder_id integer; vtransaction_id integer; vcheque_id integer; voption integer; vmod float; vtime float; begin –not supposed to execute a stockmovement (too difficult) vtime := to_number(to_char(systimestamp,'SSSSS.FF')); select min(order_id),max(order_id) into vminorder_id,vmaxorder_id from orders; vorder_id := random(vmaxorder_id vminorder_id)+vminorder_id; select order_id into vorder_id from orders where order_id = vorder_id; voption := random(2); if voption <= 1 then vmod := 1.1; else vmod := 0.9; end if; update ordersline set amount = amount*vmod where order_id = vorder_id; update orders set dte = pdte, amount = (select sum(amount) from Ordersline where order_id = vorder_id group by order_id) where order_id = vorder_id; select transaction_id into vtransaction_id from transactions where order_id = vorder_id; update transactionsline set amount = amount*vmod where transaction_id = vtransaction_id; update transactions set dte = pdte, amount = (select sum(amount) from transactionsline where transaction_id = vtransaction_id group by transaction_id) where transaction_id = vtransaction_id; select cheque_id into vcheque_id from cashbook where transaction_id = vtransaction_id; update cashbookline set amount = amount*vmod where cheque_id = vcheque_id; update cashbook set dte = pdte, amount = (select sum(amount) from cashbookline where cheque_id = vcheque_id group by cheque_id) where cheque_id = vcheque_id; dbms_output.put_line('PROC: UpdateSORP Complete '||to_char(getTime(vtime))); commit; exception when others then dbms_output.put_line('PROC: UpdateSORP '||SQLERRM (SQLCODE));rollback; end; / alter procedure UpdateSORP compile; create or replace procedure DeleteSORP(pdte IN DATE DEFAULT SYSDATE) as vminorder_id integer; vmaxorder_id integer; vorder_id integer; vtransaction_id integer; vstockmovement_id integer; vstock_id integer; vcheque_id integer; vqty integer; vprice float; vid integer; vtime float; type tStockMovement is ref cursor return StockMovement%rowtype; cStockMovements tStockMovement; rStockMovement StockMovement%rowtype; begin vtime := to_number(to_char(systimestamp,'SSSSS.FF')); select min(order_id),max(order_id) into vminorder_id,vmaxorder_id from orders; vorder_id := random(vmaxorder_id-vminorder_id)+ vminorder_id; select order_id into vorder_id from orders where order_id = vorder_id; select transaction_id into vtransaction_id from transactions where order_id = vorder_id; select cheque_id into vcheque_id from cashbook where transaction_id = vtransaction_id; begin open cStockMovements for select stockmovement_seq.nextval, sm.stock_id ,(sm.qty*–1) as qty, sm.price, pdte from stockmovement sm where exists( select stockmovement_id from transactionsline where stockmovement_id = sm.stockmovement_id and transaction_id = vtransaction_id); loop fetch cStockMovements into rStockMovement; exit when cStockMovements%NOTFOUND; insert into stockmovement values (rStockMovement.stockmovement_id ,rStockMovement.stock_id,rStockMovement. qty ,rStockMovement.price,rStockMovement.dte); end loop; close cStockMovements; exception when others then close cStockMovements; end; delete from cashbookline where cheque_id = vcheque_id; delete from cashbook where cheque_id = vcheque_id; delete from transactionsline where transaction_id = vtransaction_id; delete from ordersline where order_id = vorder_id; delete from transactions where transaction_id = vtransaction_id; delete from orders where order_id = vorder_id; dbms_output.put_line('PROC: DeleteSORP Complete '||to_char(getTime(vtime))); commit; exception when others then dbms_output.put_line('PROC: DeleteSORP '||SQLERRM(SQLCODE)); rollback; end; / alter procedure DeleteSORP compile; create or replace procedure simulate as voption integer; vrange integer; vdte date default '01-MAR-03'; begin voption := random(10); vdte := vdte + random(366); dbms_output.put_line('PROC: Simulate '||to_char(vdte)||' '||to_char(voption)); if voption in (1,2,3,4,5,6,7) then InsertSANDP(vdte); elsif voption in (8,9) then UpdateSORP(vdte); elsif voption in (10) then DeleteSORP(vdte); end if; exception when others then dbms_output.put_line('PROC: Simulate ' ||SQLERRM(SQLCODE)); end; / alter procedure simulate compile;
create or replace procedure Reports (popt IN integer) as vcustomer_id integer; vsupplier_id integer; vorder_id integer; vtransaction_id integer; vstock_id integer; vtime float; vcnt integer; type tOrders is ref cursor return Orders%rowtype; cOrders tOrders; rOrders Orders%rowtype; type tOrdersLine is ref cursor return OrdersLine%row type; cOrdersLine tOrdersLine; rOrdersLine OrdersLine%rowtype; type tTransactions is ref cursor return Transactions%rowtype; cTransactions tTransactions; rTransactions Transactions%rowtype; type tTransactionsLine is ref cursor return TransactionsLine%rowtype; cTransactionsLine tTransactionsLine; rTransactionsLine TransactionsLine%rowtype; type tCashbook is ref cursor return Cashbook%rowtype; cCashbook tCashbook; rCashbook Cashbook%rowtype; type tCashbookLine is ref cursor return CashbookLine%rowtype; cCashbookLine tCashbookLine; rCashbookLine CashbookLine%rowtype; type tCOA is ref cursor return COA%rowtype; cCOA tCOA; rCOA COA%rowtype; type tStockMovement is ref cursor return StockMovement%rowtype; cStockMovement tStockMovement; rStockMovement StockMovement%rowtype; voption integer; begin vtime := to_number(to_char(systimestamp,'SSSSS.FF')); voption := rand(8); if voption = 1 then —print all orders select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id); open cOrders for select * from orders where customer_id = vcustomer_id; loop fetch cOrders into rOrders; exit when cOrders%notfound; end loop; select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id); open cOrders for select * from orders where supplier_id = vsupplier_id; loop fetch cOrders into rOrders; exit when cOrders%notfound; end loop; elsif voption = 2 then —print individual orders select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id); select max(order_id) into vorder_id from orders where customer_id = vcustomer_id; vorder_id := rand(vorder_id); open cOrdersLine for select ol.* from orders o join ordersline ol on(ol.order_id = o.order_id) where o.customer_id = vcustomer_id; loop fetch cOrdersLine into rOrdersLine; exit when cOrdersLine%notfound; end loop; select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id); select max(order_id) into vorder_id from orders where supplier_id = vsupplier_id; vorder_id := rand(vorder_id); open cOrdersLine for select ol.* from orders o join ordersline ol on(ol.order_id = o.order_id) where o.supplier_id = vsupplier_id; loop fetch cOrdersLine into rOrdersLine; exit when cOrdersLine%notfound; end loop; elsif voption = 3 then —print all transactions select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id); open cTransactions for select * from transactions where customer_id = vcustomer_id; loop fetch cTransactions into rTransactions; exit when cTransactions%notfound; end loop; select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id); open cTransactions for select * from transactions where supplier_id = vsupplier_id; loop fetch cTransactions into rTransactions; exit when cTransactions%notfound; end loop; elsif voption = 4 then —print individual transactions select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id); select max(transaction_id) into vtransaction_id from transactions where customer_id = vcustomer_id; vtransaction_id := rand(vtransaction_id); open cTransactionsLine for select tl.* from transactions t join transactionsline tl on(tl.transaction_id = t.transaction_id) where t.customer_id = vcustomer_id; loop fetch cTransactionsLine into rTransactionsLine; exit when cTransactionsLine%notfound; end loop; select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id); select max(transaction_id) into vtransaction_id from transactions where supplier_id = vsupplier_id; vtransaction_id := rand(vtransaction_id); open cTransactionsLine for select tl.* from transactions t join transactionsline tl on(tl.transaction_id = t.transaction_id) where t.supplier_id = vsupplier_id; loop fetch cTransactionsLine into rTransactionsLine; exit when cTransactionsLine%notfound; end loop; elsif voption = 5 then —print all invoices select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id); select max(transaction_id) into vtransaction_id from transactions where customer_id = vcustomer_id; vtransaction_id := rand(vtransaction_id); open cCashbook for select cb.* from transactions t join cashbook cb on(cb.transaction_id = t.transaction_id) where t.customer_id = vcustomer_id; loop fetch cCashbook into rCashbook; exit when cCashbook%notfound; end loop; select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id); select max(transaction_id) into vtransaction_id from transactions where supplier_id = vsupplier_id; vtransaction_id := rand(vtransaction_id); open cCashbook for select cb.* from transactions t join cashbook cb on(cb.transaction_id = t.transaction_id) where t.supplier_id = vsupplier_id; loop fetch cCashbook into rCashbook; exit when cCashbook%notfound; end loop; elsif voption = 6 then —print individual invoices select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id); select max(transaction_id) into vtransaction_id from transactions where customer_id = vcustomer_id; vtransaction_id := rand(vtransaction_id); open cCashbookLine for select cbl.* from transactions t join transactionsline tl on(tl.transaction_id = t.transaction_id) join cashbook cb on(cb.transaction_id = t.transaction_id) join cashbookline cbl on(cbl.cheque_id = cb.cheque_id) where t.customer_id = vcustomer_id; loop fetch cCashbookLine into rCashbookLine; exit when cCashbookLine%notfound; end loop; select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id); select max(transaction_id) into vtransaction_id from transactions where supplier_id = vsupplier_id; vtransaction_id := rand(vtransaction_id); open cCashbookLine for select cbl.* from transactions t join transactionsline tl on(tl.transaction_id = t.transaction_id) join cashbook cb on(cb.transaction_id = t.transaction_id) join cashbookline cbl on(cbl.cheque_id = cb.cheque_id) where t.supplier_id = vsupplier_id; loop fetch cCashbookLine into rCashbookLine; exit when cCashbookLine%notfound; end loop; elsif voption = 7 then —coa open cCOA for select c.* from coa c, type t, subtype s where c.type = t.type and c.subtype = s.subtype order by c.coa#, t.type, s.subtype; loop fetch cCOA into rCOA; exit when cCOA%notfound; end loop; elsif voption = 8 then —stock select max(stock_id) into vstock_id from stock; vstock_id := rand(vstock_id); open cStockMovement for select sm.* from category c join stock s on(s.category_id = c.category_id) join stockmovement sm on(sm.stock_id = s.stock_id) where s.stock_id = vstock_id; loop fetch cStockMovement into rStockMovement; exit when cStockMovement%notfound; end loop; elsif voption in (9,10) then —sorting select count(*) into vcnt from (select * from customer order by address); select count(*) into vcnt from (select drcoa#, sum(amount) from transactions group by drcoa#); —selections from the generalledger and stockmovement —tables are avoided here due to reverse index primary keys end if; dbms_output.put_line('PROC: Reports Complete ' ||to_char(getTime(vtime))); simAudit('PROCEDURE','Reports',getTime(vtime), popt); exception when others then dbms_output.put_line('PROC: Reports '||SQLERRM(SQL- CODE)); rollback; end; / alter procedure Reports compile; create or replace procedure simulate as voption integer; vrange integer; vdte date default '01-MAR-03'; begin voption := rand(20); vdte := vdte + rand(366); dbms_output.put_line('PROC: Simulate ('||to_char (voption)||')' ||to_char(vdte)||' '||to_char(voption)); if voption in (1,2,3,4,5,6,7,8,9,10) then Reports(voption); elsif voption in (11,12,13,14,15) then InsertSANDP(vdte,voption); elsif voption in (16,17,18) then UpdateSORP(vdte, voption); elsif voption in (19,20) then DeleteSORP(vdte,voption); end if; exception when others then dbms_output.put_line('PROC: Simulate '||SQLERRM (SQLCODE)); end; / alter procedure simulate compile;
Tip | Pinning all these procedures into the shared pool using the DBMS_SHARED_POOL package KEEP procedure would be sensible. I have not done so because I am attempting to simulate a tunable environment. |
| < Day Day Up > |
|