Posted By

wh0emPah on 04/02/10


Tagged


Versions (?)

ee


 / Published in: PL/SQL
 

  1. PACKAGE BODY P5_summit_pkg AS
  2.  
  3. PROCEDURE P1_1_VOEG_KLANT_TOE
  4. (
  5. p_id IN S_CUSTOMER.ID%TYPE,
  6. p_name IN S_CUSTOMER.NAME%TYPE,
  7. p_phone IN S_CUSTOMER.PHONE%TYPE,
  8. p_address IN S_CUSTOMER.ADDRESS%TYPE,
  9. p_city IN S_CUSTOMER.CITY%TYPE,
  10. p_state IN S_CUSTOMER.STATE%TYPE,
  11. p_country IN S_CUSTOMER.COUNTRY%TYPE,
  12. p_zipcode IN S_CUSTOMER.ZIP_CODE%TYPE
  13. )
  14.  
  15. INSERT INTO S_CUSTOMER(ID, NAME, PHONE, address, city, state, country,ZIP_CODE)
  16. VALUES (p_id,p_name,P_PHONE,P_ADDRESS,P_CITY,P_STATE,P_COUNTRY,P_ZIPCODE);
  17. END P1_1_VOEG_KLANT_TOE;
  18.  
  19.  
  20. PROCEDURE P1_2_VOEG_ORDER_TOE
  21. (
  22. P_ID IN S_ORD.ID%TYPE
  23. , P_CUSTOMERID IN S_ORD.CUSTOMER_ID%TYPE
  24. , P_SALES_REP_ID IN S_ORD.SALES_REP_ID%TYPE
  25. , P_DATE_ORDERED IN S_ORD.DATE_ORDERED%TYPE
  26. , P_PAYMENT_TYPE IN S_ORD.PAYMENT_TYPE%TYPE
  27. ) AS
  28. v_jobtitle S_EMP.TITLE%TYPE;
  29. SELECT UPPER(TITLE)
  30. INTO v_jobtitle
  31. FROM S_EMP
  32. WHERE ID = p_sales_rep_id;
  33.  
  34. IF V_JOBTITLE = 'SALES REPRESENTATIVE' THEN
  35. INSERT INTO S_ORD(id, customer_id, SALES_REP_ID, DATE_ORDERED, PAYMENT_TYPE)
  36. VALUES(p_id, p_customerid, p_sales_rep_id, p_date_ordered, p_payment_type);
  37. DBMS_OUTPUT.PUT_LINE('Order toegevoegd');
  38. DBMS_OUTPUT.PUT_LINE('Order NIET toegevoegd');
  39. END P1_2_VOEG_ORDER_TOE;
  40.  
  41. PROCEDURE P1_3_VOEG_ORDERLIJN_TOE
  42. (
  43. P_ORD_ID IN S_ITEM.ORD_ID%TYPE
  44. , P_PRODUCT_ID IN S_ITEM.PRODUCT_ID%TYPE
  45. , P_PRICE IN S_ITEM.PRICE%TYPE
  46. , P_QUANTITY IN S_ITEM.QUANTITY%TYPE
  47. ) AS
  48. V_ITEM_ID S_ITEM.ITEM_ID%TYPE;
  49. SELECT MAX(ITEM_ID)+1
  50. INTO V_ITEM_ID
  51. FROM S_ITEM
  52. WHERE ORD_ID = P_ORD_ID;
  53.  
  54. IF V_ITEM_ID IS NULL THEN
  55. V_ITEM_ID := 1;
  56.  
  57. INSERT INTO S_ITEM(ORD_ID,ITEM_ID,PRODUCT_ID,PRICE,QUANTITY)
  58. VALUES(P_ORD_ID, V_ITEM_ID, P_PRODUCT_ID, P_PRICE, P_QUANTITY);
  59. DBMS_OUTPUT.PUT_LINE('Orderlijn toegevoegd!');
  60. END P1_3_VOEG_ORDERLIJN_TOE;
  61.  
  62. PROCEDURE P4_4_VERWIJDER_MEDEWERKER(
  63. MED_ID IN S_EMP.ID%TYPE)
  64. E_CHILDREN_FOUND EXCEPTION;
  65. PRAGMA EXCEPTION_INIT (E_CHILDREN_FOUND, - 2292);
  66. DELETE FROM S_EMP
  67. WHERE ID = MED_ID;
  68.  
  69. WHEN E_CHILDREN_FOUND THEN
  70. DBMS_OUTPUT.PUT_LINE ('Employee has children!');
  71. RAISE_APPLICATION_ERROR( -20000, 'Onbekende fout opgetreden');
  72. END P4_4_VERWIJDER_MEDEWERKER;
  73.  
  74. PROCEDURE P4_4_VERWIJDER_MEDEWERKER(
  75. P_LAST_NAME IN S_EMP.LAST_NAME%TYPE,
  76. P_FIRST_NAME IN S_EMP.FIRST_NAME%TYPE)
  77. E_CHILDREN_FOUND EXCEPTION;
  78. PRAGMA EXCEPTION_INIT (E_CHILDREN_FOUND, - 2292);
  79. DELETE FROM S_EMP
  80. WHERE LAST_NAME = P_LAST_NAME AND FIRST_NAME = P_FIRST_NAME ;
  81.  
  82. WHEN E_CHILDREN_FOUND THEN
  83. DBMS_OUTPUT.PUT_LINE ('Employee has children!');
  84. RAISE_APPLICATION_ERROR( -20000, 'Onbekende fout opgetreden');
  85. END P4_4_VERWIJDER_MEDEWERKER;
  86.  
  87. PROCEDURE P2_2_HOOGSTE_VOORRAADWAARDE AS
  88. TYPE TYPE_REC_VOORRAADWAARDE IS RECORD(
  89. WHID S_INVENTORY.WAREHOUSE_ID%TYPE,
  90. WHVALUE NUMBER
  91. );
  92.  
  93. TYPE TYPE_COL_VOORRAAD
  94. IS TABLE OF TYPE_REC_VOORRAADWAARDE
  95.  
  96. t_voorraad TYPE_COL_VOORRAAD;
  97. SELECT WAREHOUSE_ID, SUM(I.AMOUNT_IN_STOCK * P.SUGGESTED_WHLSL_PRICE) Waarde
  98. BULK COLLECT INTO t_voorraad
  99. FROM S_INVENTORY I, S_PRODUCT P
  100. WHERE I.PRODUCT_ID = P.ID
  101. GROUP BY WAREHOUSE_ID
  102. ORDER BY Waarde DESC;
  103.  
  104. DBMS_OUTPUT.PUT_LINE('De 3 voorraadruimten met de hoogste voorraadwaarde :');
  105. FOR I IN 1..3 LOOP
  106. DBMS_OUTPUT.PUT_LINE('Voorraadruimte: ' || t_voorraad(I).WHID ||' Waarde: ' ||t_voorraad(I).WHVALUE);
  107.  
  108. END P2_2_HOOGSTE_VOORRAADWAARDE;
  109.  
  110. PROCEDURE VERWIJDER_ORDER
  111. (p_ordid IN s_ord.id%TYPE)
  112. v_ordid NUMBER := 0;
  113. SELECT COUNT(ord_id) INTO v_ordid
  114. FROM s_item
  115. WHERE ord_id = p_ordid;
  116. IF v_ordid <> 0
  117. DELETE FROM s_item WHERE ord_id = p_ordid;
  118. DELETE FROM s_ord WHERE id = p_ordid;
  119. ELSE RAISE_APPLICATION_ERROR (-20006, 'Order bestaat niet ');
  120. END IF;
  121. RAISE_APPLICATION_ERROR(-20005, 'Order bestaat niet ');
  122. END VERWIJDER_ORDER;
  123.  
  124. PROCEDURE VERHOOG_TOTAL_IN_STOCK
  125. (p_product_id IN s_product.id%TYPE,
  126. p_total_in_stock IN s_product.total_in_stock%TYPE)
  127. UPDATE s_product
  128. SET total_in_stock=NVL(total_in_stock,0)+p_total_in_stock
  129. WHERE id=p_product_id;
  130. END VERHOOG_TOTAL_IN_STOCK;
  131.  
  132. FUNCTION P1_5_TOTAALBEDRAG_ORDERS
  133. (
  134. P_PERSONEELSNUMMER IN PLS_INTEGER
  135. v_TOTAAL_BEDRAG_ORDERS NUMBER;
  136. SELECT SUM(TOTAL)
  137. INTO v_TOTAAL_BEDRAG_ORDERS
  138. FROM S_ORD
  139. WHERE SALES_REP_ID = p_personeelsnummer;
  140. DBMS_OUTPUT.PUT_LINE('Het totaal bedrag van ' ||p_personeelsnummer || ' bedraagt ' || v_totaal_bedrag_orders);
  141. RETURN V_TOTAAL_BEDRAG_ORDERS;
  142. END P1_5_TOTAALBEDRAG_ORDERS;
  143.  
  144. FUNCTION P1_7_KLEINSTE_VOORRAAD RETURN NUMBER AS
  145. V_PRODUCTID S_PRODUCT.ID%TYPE;
  146.  
  147. SELECT ID
  148. INTO V_PRODUCTID
  149. FROM S_PRODUCT
  150. WHERE TOTAL_IN_STOCK = (SELECT MIN(TOTAL_IN_STOCK) FROM S_product);
  151.  
  152. RETURN V_PRODUCTID;
  153. END P1_7_KLEINSTE_VOORRAAD;
  154.  
  155. BULK COLLECT INTO t_onafgewerkte_orders
  156. FROM s_ord
  157. WHERE order_filled = 'N';
  158.  
  159. FOR i IN 1 .. t_onafgewerkte_orders.COUNT
  160. DBMS_OUTPUT.put_line(t_onafgewerkte_orders(i).id || ' ' || t_onafgewerkte_orders(i).customer_id || ' ' || t_onafgewerkte_orders(i).date_ordered || ' ' || t_onafgewerkte_orders(i).date_ordered);
  161.  
  162. END P5_summit_pkg;

Report this snippet  

You need to login to post a comment.