ROWTYPE is used to declare a record as same type found in database table 15 A

Rowtype is used to declare a record as same type

This preview shows page 2 - 7 out of 17 pages.

%ROWTYPE is used to declare a record as same type found in database table. 15 – A composite data type could be a record, table, nested table, varray. This is so because all of them are composed of multiple data types. Composite data types can be used to construct complex data types fulfilling different requirements. Composite data types are also known as Collections. i.e. RECORD, TABLE, NESTED TABLE, VARRAY. Composite data types are of 2 types: PL/SQL RECORDS PL/SQL Collections- Table, Varray, Nested Table Advanced Review Questions 1 – B 2 - B 3 – B 4 – C 5 – B Hands-on Assignment part 1
Image of page 2
1 – DECLARE lv_ship_date bb_basketstatus.dtstage%TYPE; lv_shipper_txt bb_basketstatus.shipper%TYPE; lv_ship_num bb_basketstatus.shippingnum%TYPE; lv_bask_num bb_basketstatus.idbasket%TYPE := 3; BEGIN SELECT dtstage, shipper, shippingnum INTO lv_ship_date, lv_shipper_txt, lv_ship_num FROM bb_basketstatus WHERE idbasket = lv_bask_num AND idstage = 5; DBMS_OUTPUT.PUT_LINE('Date Shipped: '||lv_ship_date); DBMS_OUTPUT.PUT_LINE('Shipper: '||lv_shipper_txt); DBMS_OUTPUT.PUT_LINE('Shipping #: '||lv_ship_num); END; Date Shipped: 25-Jan-2012 Shipper: UPS Shipping #: ZW845584GD89H569 Statement processed. 0.01 seconds Adding data type assignment to the first three variables declared DECLARE lv_ship_date DATE; lv_shipper_txt VARCHAR2(5);
Image of page 3
lv_ship_num VARCHAR2(20); lv_bask_num bb_basketstatus.idbasket%TYPE := 3; BEGIN SELECT dtstage, shipper, shippingnum INTO lv_ship_date, lv_shipper_txt, lv_ship_num FROM bb_basketstatus WHERE idbasket = lv_bask_num AND idstage = 5; DBMS_OUTPUT.PUT_LINE('Date Shipped: '||lv_ship_date); DBMS_OUTPUT.PUT_LINE('Shipper: '||lv_shipper_txt); DBMS_OUTPUT.PUT_LINE('Shipping #: '||lv_ship_num); END; Date Shipped: 25-Jan-2012 Shipper: UPS Shipping #: ZW845584GD89H569 Statement processed. 0.01 seconds The results are the same DECLARE lv_ship_date DATE; lv_shipper_txt VARCHAR2(5); lv_ship_num VARCHAR2(20); lv_bask_num bb_basketstatus.idbasket%TYPE := 7; BEGIN SELECT dtstage, shipper, shippingnum INTO lv_ship_date, lv_shipper_txt, lv_ship_num FROM bb_basketstatus
Image of page 4
WHERE idbasket = lv_bask_num AND idstage = 5; DBMS_OUTPUT.PUT_LINE('Date Shipped: '||lv_ship_date); DBMS_OUTPUT.PUT_LINE('Shipper: '||lv_shipper_txt); DBMS_OUTPUT.PUT_LINE('Shipping #: '||lv_ship_num); END; ORA-01403: no data found 0.00 seconds 2 – DECLARE rec_ship bb_basketstatus%ROWTYPE; lv_bask_num bb_basketstatus.idbasket%TYPE := 3; BEGIN SELECT * INTO rec_ship FROM bb_basketstatus WHERE idbasket = lv_bask_num AND idstage = 5; DBMS_OUTPUT.PUT_LINE('Date Shipped: '||rec_ship.dtstage); DBMS_OUTPUT.PUT_LINE('Shipper: '||rec_ship.shipper); DBMS_OUTPUT.PUT_LINE('Shipping #: '||rec_ship.shippingnum); DBMS_OUTPUT.PUT_LINE('Notes: '||rec_ship.notes); END; Date Shipped: 25-Jan-2012
Image of page 5
Shipper: UPS Shipping #: ZW845584GD89H569
Image of page 6
Image of page 7

You've reached the end of your free preview.

Want to read all 17 pages?

  • Spring '16
  • Mary Gable
  • Type system, Type theory, idbasket

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture