preview

Plsql Chap7 Sols Essay

Good Essays

PL/SQL Chapter 7 Solutions

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

11. The user_objects view can be used to determine all the packages that exist in the database. In the query, use a WHERE clause to select the rows that contain the term 'PACKAGE' in the object type column. Selecting the text column of the user_source view will list all the source code lines in the package. Use a WHERE clause on the name column to select a single package.

12. If there are values referenced continually in the application, storing them in packaged variables will allow the values to only be retrieved once thus saving data retrieval processing. The values in global constructs are persistent for the entire user session. …show more content…

END;
/

SELECT order_info_pkg.ship_name_pf(idBasket) FROM bb_basket WHERE idBasket = 12;

Assignment 7-3
CREATE OR REPLACE PACKAGE order_info_pkg IS PROCEDURE basket_info_pp (p_basket IN NUMBER, p_shop OUT NUMBER, p_date OUT DATE, p_name OUT VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY order_info_pkg IS FUNCTION ship_name_pf (p_basket IN NUMBER) RETURN VARCHAR2 IS lv_name_txt VARCHAR2(25); BEGIN SELECT shipfirstname||' '||shiplastname INTO lv_name_txt FROM bb_basket WHERE idBasket = p_basket; RETURN lv_name_txt; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid basket id'); END ship_name_pf; PROCEDURE basket_info_pp (p_basket IN NUMBER, p_shop OUT NUMBER, p_date OUT DATE, p_name OUT VARCHAR2 ) IS BEGIN SELECT idshopper, dtordered INTO p_shop, p_date FROM bb_basket WHERE idbasket = p_basket; p_name := ship_name_pf(p_basket); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid basket id'); END basket_info_pp;
END;
/

SET SERVEROUTPUT ON
DECLARE
lv_name_txt VARCHAR2(20); lv_id_num NUMBER(4); lv_ord_dat DATE;
BEGIN
order_info_pkg.basket_info_pp(12,lv_id_num, lv_ord_dat, lv_name_txt); DBMS_OUTPUT.PUT_LINE(lv_name_txt); DBMS_OUTPUT.PUT_LINE(lv_id_num); DBMS_OUTPUT.PUT_LINE(lv_ord_dat);
END;
/

Assignment 7-4
CREATE OR REPLACE PACKAGE login_pkg IS pv_id_num NUMBER(3); pv_zip3_num NUMBER(3); FUNCTION login_ck_pf (p_user IN VARCHAR2, p_pass

Get Access