Advertisement
Guest User

Untitled

a guest
Jun 20th, 2019
74
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.13 KB | None | 0 0
  1. CREATE OR REPLACE PROCEDURE sale (
  2. outletid IN NUMBER,
  3. itemcod IN NUMBER,
  4. start_date IN DATE,
  5. end_date IN DATE,
  6. outletid1 OUT NUMBER,
  7. itemcod1 OUT NUMBER,
  8. amount OUT NUMBER,
  9. quantity OUT NUMBER,
  10. entry_date OUT DATE
  11. ) IS
  12. BEGIN
  13. SELECT
  14. l.outlet_id,
  15. itemcode,
  16. SUM(amount) amount,
  17. SUM(quantity) quantity,
  18. i.entry_date
  19. INTO
  20. outletid1,
  21. itemcod1,
  22. amount,
  23. quantity,
  24. entry_date
  25. FROM
  26. idstransaction i
  27. JOIN lup_outlet l ON l.outlet_id = i.outlet_id
  28. JOIN lup z ON z.zone_id = l.zone_id
  29. JOIN prod p ON p.serial = itemcode
  30. JOIN lup_master m ON m.sup_id = p.supplier_id
  31. WHERE
  32. l.outlet_id IN (
  33. outletid
  34. )
  35. AND itemcode IN (
  36. itemcod
  37. )
  38. AND TO_DATE(i.entry_date) BETWEEN start_date AND end_date
  39. GROUP BY
  40. l.outlet_id,
  41. itemcode,
  42. i.entry_date; -- THE SEMICOLON MISSING HERE
  43.  
  44. END;
  45. ----------
  46.  
  47. DECLARE
  48. var NUMBER;
  49. var1 NUMBER;
  50. var2 NUMBER;
  51. var3 NUMBER;
  52. var4 DATE;
  53. begin for c
  54. in(sale(outletid => 809, itemcod => 128169, start_date => DATE '2018-01-01', end_date => DATE '2019-01-01', amount => var, quantity
  55. => var1, outletid1 => var2, itemcod1 => var3, entry_date => var4));
  56.  
  57. LOOP
  58. var := c.amount;
  59. var1 := c.quantity;
  60. var2 := c.outletid1;
  61. var3 := c.itemcod1;
  62. var4 := c.entry_date;
  63. dbms_output.enable;
  64. dbms_output.put_line(var);
  65. dbms_output.enable;
  66. dbms_output.put_line(var1);
  67. dbms_output.enable;
  68. dbms_output.put_line(var2);
  69. dbms_output.enable;
  70. dbms_output.put_line(var3);
  71. dbms_output.enable;
  72. dbms_output.put_line(var4);
  73. END LOOP;
  74.  
  75. end;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement