Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE sale (
- outletid IN NUMBER,
- itemcod IN NUMBER,
- start_date IN DATE,
- end_date IN DATE,
- outletid1 OUT NUMBER,
- itemcod1 OUT NUMBER,
- amount OUT NUMBER,
- quantity OUT NUMBER,
- entry_date OUT DATE
- ) IS
- BEGIN
- SELECT
- l.outlet_id,
- itemcode,
- SUM(amount) amount,
- SUM(quantity) quantity,
- i.entry_date
- INTO
- outletid1,
- itemcod1,
- amount,
- quantity,
- entry_date
- FROM
- idstransaction i
- JOIN lup_outlet l ON l.outlet_id = i.outlet_id
- JOIN lup z ON z.zone_id = l.zone_id
- JOIN prod p ON p.serial = itemcode
- JOIN lup_master m ON m.sup_id = p.supplier_id
- WHERE
- l.outlet_id IN (
- outletid
- )
- AND itemcode IN (
- itemcod
- )
- AND TO_DATE(i.entry_date) BETWEEN start_date AND end_date
- GROUP BY
- l.outlet_id,
- itemcode,
- i.entry_date; -- THE SEMICOLON MISSING HERE
- END;
- ----------
- DECLARE
- var NUMBER;
- var1 NUMBER;
- var2 NUMBER;
- var3 NUMBER;
- var4 DATE;
- begin for c
- in(sale(outletid => 809, itemcod => 128169, start_date => DATE '2018-01-01', end_date => DATE '2019-01-01', amount => var, quantity
- => var1, outletid1 => var2, itemcod1 => var3, entry_date => var4));
- LOOP
- var := c.amount;
- var1 := c.quantity;
- var2 := c.outletid1;
- var3 := c.itemcod1;
- var4 := c.entry_date;
- dbms_output.enable;
- dbms_output.put_line(var);
- dbms_output.enable;
- dbms_output.put_line(var1);
- dbms_output.enable;
- dbms_output.put_line(var2);
- dbms_output.enable;
- dbms_output.put_line(var3);
- dbms_output.enable;
- dbms_output.put_line(var4);
- END LOOP;
- end;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement