OraclePLSQL--“TYPE … RECORD” --- DECLARE

“TYPE … RECORD”  à DECLARE à PL/SQL

SCENARIO

By using “type—record” we want to extract the following information from our database

•       most  sale item “product_id” and least sale item “product_id”
•       most sale item” name” and  least sale item “name”
•      “list_price “of most sale item and “list_price” of least sale item
•       “nos. of order” of most sale item and “nos. of order ” of least sale item

and by this information we want to order these items for  our warehouse…

DECLARE

TYPE QUANTITY

IS RECORD

(PRODUCT_ID        PRODUCT_INFORMATION.PRODUCT_ID%TYPE,

PRODUCT_NAME      PRODUCT_INFORMATION.PRODUCT_NAME%TYPE,

TRANSLATED_NAME   PRODUCT_DESCRIPTIONS.TRANSLATED_NAME%TYPE,

QUANTITY          ORDER_ITEMS.QUANTITY%TYPE,

LIST_PRICE        PRODUCT_INFORMATION.LIST_PRICE%TYPE,

NEED  NUMBER(6,2)

);

MORENEED  QUANTITY;

LESSNEED QUANTITY;

BEGIN

SELECT C.PRODUCT_ID ,C.PRODUCT_NAME,O.TRANSLATED_NAME,QUANTITY,LIST_PRICE, 100

INTO  MORENEED

FROM PRODUCT_INFORMATION C ,PRODUCT_DESCRIPTIONS O,  ORDER_ITEMS T

WHERE c.PRODUCT_ID  = o.PRODUCT_ID

AND o.PRODUCT_ID = t.PRODUCT_ID

AND t.QUANTITY = (SELECT MAX(QUANTITY) FROM ORDER_ITEMS)

AND ROWNUM <= 1;

dbms_output.put_line ('MAX-ORDER_PRODUCT_NAME: ' || MORENEED.PRODUCT_NAME);

dbms_output.put_line ('MAX-ORDER_TRANSLATED_NAME: ' || MORENEED.TRANSLATED_NAME);

dbms_output.put_line ('MAX-ORDER_PRODUCT_ID: ' || MORENEED.PRODUCT_ID);

dbms_output.put_line ('MAX-ORDER_LIST_PRICE: ' || MORENEED.LIST_PRICE);

dbms_output.put_line ('MAX-ORDER-QUANTITY: ' || MORENEED.QUANTITY);

dbms_output.put_line ('QUANTITY_NEED: ' || MORENEED.NEED);

SELECT C.PRODUCT_ID ,C.PRODUCT_NAME,O.TRANSLATED_NAME,QUANTITY,LIST_PRICE, 5

INTO  LESSNEED

FROM PRODUCT_INFORMATION C ,PRODUCT_DESCRIPTIONS O,  ORDER_ITEMS T

WHERE c.PRODUCT_ID  = o.PRODUCT_ID

AND o.PRODUCT_ID = t.PRODUCT_ID

AND t.QUANTITY = (SELECT MIN(QUANTITY ) FROM ORDER_ITEMS)

AND ROWNUM <= 1;

dbms_output.put_line ('MIN-QUANTITY_PRODUCT_NAME: ' || LESSNEED.PRODUCT_NAME);

dbms_output.put_line ('MIN-QUANTITY_TRANSLATED_NAME: ' || LESSNEED.TRANSLATED_NAME);

dbms_output.put_line ('MIN-QUANTITY_PRODUCT_ID: ' || LESSNEED.PRODUCT_ID);

dbms_output.put_line ('MIN-QUANTITY_LIST_PRICE: ' || LESSNEED.LIST_PRICE);

dbms_output.put_line ('MIN-ORDER-QUANTITY: ' || LESSNEED.QUANTITY);

dbms_output.put_line ('QUANTITY_NEED: ' || LESSNEED.NEED);

END;

anonymous block completed

MAX-ORDER_PRODUCT_NAME: Chemicals - TCS

MAX-ORDER_TRANSLATED_NAME: ¿¿¿¿ ¿¿¿¿¿¿¿ - TCS

MAX-ORDER_PRODUCT_ID: 2365

MAX-ORDER_LIST_PRICE: 78

MAX-ORDER-QUANTITY: 209

QUANTITY_NEED: 100

MIN-QUANTITY_PRODUCT_NAME: MB - S900/650+

MIN-QUANTITY_TRANSLATED_NAME: MB - S900/650+

MIN-QUANTITY_PRODUCT_ID: 3114

MIN-QUANTITY_LIST_PRICE: 101

MIN-ORDER-QUANTITY: 0

QUANTITY_NEED: 5