PL/SQL Chapter 3 Solutions

Review Questions
1. C
2. D
3. B
4. A
5. C
6. C
7. A, C
8. A
9. A
10. A

11. Variables are named memory areas that hold values to allow retrieval and manipulation of values within our programs

12. A SELECT statement within a PL/SQL block must include an INTO clause to indicate the variable/s that will hold the data being retrieved. The INTO clause follows the SELECT clause and precedes the FROM clause. In addition, a SELECT statement that returns no rows will raise an Oracle error.

13. The %TYPE attribute is used to declare the data type of a variable based on the data type of a database table column. The data type declaration must include the table and column name in the form: tablename.columnname%TYPE.

14. The %ROWTYPE attribute is used to declare the data type of a record variable based on the row structure of a table. The data type declaration must include the table name in the form: tablename%ROWTYPE.

15. Composite data types allow the creation of a variable that can hold multiple values with various data types as a single unit. Examples include a record and a table of records.

Advanced Review Questions
1. B
2. C
3. D
4. A
5. B

Hands-On Assignments

Assignment 3-1
VARIABLE g_basket NUMBER
BEGIN
:g_basket := 3;
END;
/
DECLARE
lv_ship_date bb_basketstatus.dtstage%TYPE; lv_shipper_txt bb_basketstatus.shipper%TYPE; lv_ship_num bb_basketstatus.shippingnum%TYPE;
BEGIN
SELECT dtstage, shipper, shippingnum INTO lv_ship_date, lv_shipper_txt, lv_ship_num FROM bb_basketstatus WHERE idbasket = :g_basket 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;
/
BEGIN
:g_basket := 7;
END;
/

Assignment 3-2
VARIABLE