Oracle10 g Developer: PL/SQL Programming

1. Oracle10g Developer: PL/SQL Programming Chapter 11 Introduction to Dynamic SQL and Object Technology

2. Chapter Objectives • After completing this lesson, you should be able to understand: • Creating dynamic SQL • Using object technology

3. Brewbean’s Challenge • Add flexibility to queries, such as allowing shoppers to determine if they want to search product names or descriptions • Explore potential capabilities of object technology features for providing greater data consistency and control

4. Dynamic SQL • Allows identifiers such as column and table names to be provided at run time • Two mechanisms available • DBMS_SQL package (Oracle7) • Native dynamic SQL (Oracle8)

5. DBMS_SQL – DML Example CREATE OR REPLACE PROCEDURE dyn_dml_sp (p_col VARCHAR2, p_price NUMBER, p_id NUMBER) IS lv_cursor INTEGER; lv_update VARCHAR2(150); lv_rows NUMBER(1); BEGIN --Open Cursor lv_cursor := DBMS_SQL.OPEN_CURSOR;

6. DBMS_SQL – DML Example (continued) --Create DML statement lv_update := 'UPDATE bb_product SET ' || p_col || ' = :ph_price WHERE idProduct = :ph_id'; --Parse the statement DBMS_SQL.PARSE(lv_cursor, lv_update, DBMS_SQL.NATIVE); --Associate parameters with placeholders in the statement DBMS_SQL.BIND_VARIABLE(lv_cursor, ':ph_price', p_price); DBMS_SQL.BIND_VARIABLE(lv_cursor, ':ph_id', p_id);

7. DBMS_SQL – DML Example (continued) --Run the DML statement lv_rows := DBMS_SQL.EXECUTE(lv_cursor); --Close the cursor DBMS_SQL.CLOSE_CURSOR(lv_cursor); --Save changes COMMIT; --Check how many rows affected DBMS_OUTPUT.PUT_LINE(lv_rows); END;

8. DBMS_SQL – DML Example (continued)

9. DBMS_SQL – Query Example CREATE OR REPLACE PROCEDURE dyn_query1_sp (p_col IN VARCHAR2, p_value IN VARCHAR2) IS lv_query LONG; lv_status INTEGER; lv_cursor INTEGER; lv_col1 NUMBER(2); lv_col2 VARCHAR2(25); lv_col3 NUMBER(6,2); lv_col4 NUMBER(5,1);

10. DBMS_SQL – Query Example (continued) BEGIN --Open the cursor lv_cursor := DBMS_SQL.OPEN_CURSOR; --Build the query lv_query := 'SELECT idProduct, productname, price, stock FROM bb_product WHERE '|| UPPER(p_col) ||' = ' || 'UPPER(:ph_value)'; --Parse the statement DBMS_SQL.PARSE(lv_cursor, lv_query, DBMS_SQL.NATIVE); --Identify data types for each item selected DBMS_SQL.DEFINE_COLUMN(lv_cursor, 1, lv_col1); DBMS_SQL.DEFINE_COLUMN(lv_cursor, 2, lv_col2, 25); DBMS_SQL.DEFINE_COLUMN(lv_cursor, 3, lv_col3); DBMS_SQL.DEFINE_COLUMN(lv_cursor, 4, lv_col4);

11. DBMS_SQL – Query Example (continued) --Associate placeholder with a parameter DBMS_SQL.BIND_VARIABLE(lv_cursor, ':ph_value', p_value); --Execute the query lv_status := DBMS_SQL.EXECUTE(lv_cursor); --Fetch row returned and place into PL/SQL variables IF (DBMS_SQL.FETCH_ROWS(lv_cursor) > 0) THEN DBMS_SQL.COLUMN_VALUE(lv_cursor, 1, lv_col1); DBMS_SQL.COLUMN_VALUE(lv_cursor, 2, lv_col2); DBMS_SQL.COLUMN_VALUE(lv_cursor, 3, lv_col3); DBMS_SQL.COLUMN_VALUE(lv_cursor, 4, lv_col4); DBMS_OUTPUT.PUT_LINE(lv_col1||' '||lv_col2||' '||lv_col3||' '||lv_col4); END IF; --Close cursor DBMS_SQL.CLOSE_CURSOR(lv_cursor); END;

12. DBMS_SQL – Query Example (continued)

13. Native Dynamic SQL • Simpler coding • More efficient processing • Limited capabilities compared to DBMS_SQL package • Two methods • EXECUTE IMMEDIATE • OPEN FOR

14. Native Dynamic SQL Example CREATE OR REPLACE PROCEDURE dyn_query3_sp (p_col IN VARCHAR2, p_value IN VARCHAR2) IS lv_query VARCHAR2(200); lv_id bb_product.idProduct%TYPE; lv_name bb_product.productname%TYPE; lv_price bb_product.price%TYPE; lv_stock bb_product.stock%TYPE; BEGIN --use a variable to hold the query construction to -- make it more readable

15. Native Dynamic SQL Example (continued) lv_query := 'SELECT idProduct, productname, price, stock FROM bb_product WHERE UPPER(' || p_col || ') = UPPER(:ph_value)'; --Run the dynamic query supplying variables to hold the -- return values in the INTO clause and associate the -- parameter to the placeholder with the USING clause EXECUTE IMMEDIATE lv_query INTO lv_id, lv_name, lv_price, lv_stock USING p_value; DBMS_OUTPUT.PUT_LINE(lv_id||' '||lv_name||' ' ||lv_price||' '||lv_stock); END;

16. Native Dynamic SQL Example (continued)

17. DBMS_SQL vs. Native Dynamic SQL • Use native dynamic SQL when: • The number and types of columns to be used is known • The number and type of bind variables is known • To perform DDL • Executing the statement only once or twice • User-defined types such as object and collections are used (not supported by DBMS_SQL) • Fetching rows of data into PL/SQL records (not supported by DBMS_SQL) • SQL statement is less than 32KB in size

18. Object Technology • Object Types • Object Methods • Object Relations • Object Views

19. Object Types • Represent an entity such as an address or a person • Defined with attributes and methods • Can be used as a data type for a table column • Can contain multiple data elements or attributes

20. Create an Object Type CREATE OR REPLACE TYPE addr_ot AS OBJECT (street1 VARCHAR2(25), street2 VARCHAR2(25), city VARCHAR2(25), state CHAR(2), zip NUMBER(9) );

21. Use Object Type CREATE TABLE bb_order (ord_id NUMBER(4), cust_id NUMBER(4), ord_date DATE, total NUMBER(6,2), bill_addr addr_ot, ship_addr addr_ot );

22. Object Type Constructor DECLARE lv_bill addr_ot; lv_ship addr_ot; BEGIN lv_bill := addr_ot('11 Bush Dr' ,NULL, 'Savannah', 'GA',346668229); lv_ship := addr_ot('812 Scott Lane','Apt #52','Savannah','GA',346668227); INSERT INTO bb_order VALUES (102,31,'11-NOV-03’ ,34.50,lv_bill,lv_ship); END;

23. DESCRIBE Command

24. Methods • Add program units to object types • Referred to as members • Similar to package creation • Add function ALTER TYPE addr_ot ADD MEMBER FUNCTION lbl_print RETURN VARCHAR2;

25. Object Type Body

26. Object Relations • Create customer object type CREATE TYPE cust_ot AS OBJECT (cust_id NUMBER(4), first VARCHAR2(15), last VARCHAR2(20), email VARCHAR2(25), phone NUMBER(10) ); • Create customer table CREATE TABLE bb_cust OF cust_ot (PRIMARY KEY (cust_id));

27. Object Relations (continued) • Create orders object type CREATE TYPE ord_ot AS OBJECT (ord_id NUMBER(4), cust_ref REF cust_ot, ord_date DATE, total NUMBER(6,2)); • Create orders object type CREATE TABLE bb_ord OF ord_ot (PRIMARY KEY (ord_id));

28. Object Relations (continued) • Use REF variable to establish relation INSERT INTO bb_cust VALUES (cust_ot(12,'Joe','Cool','jcool@yahoo.com', 7773335555)); INSERT INTO bb_ord SELECT ord_ot(1,REF(c),SYSDATE,24.50) FROM bb_cust c WHERE cust_id = 12; COMMIT;

29. REF Pointers • Does not prevent broken relations like a foreign key constraint

30. Object Views • Provide a layer on top of traditional database designs to enable object features • OBJECT OID identifies unique view row identifier • CAST and MULTISET handle subquery mapping to view elements

31. Object View Example CREATE TYPE bask_ot AS OBJECT (idBasket NUMBER(5), total NUMBER(7,2)) / CREATE TYPE bask_tab AS TABLE OF bask_ot / CREATE TYPE shop_ot AS OBJECT (idShopper NUMBER(4), last_name VARCHAR2(20), city VARCHAR2(20), idBasket bask_tab) /

32. Object View Example (continued) CREATE VIEW shop_vu OF shop_ot WITH OBJECT OID(idShopper) AS SELECT s.idShopper, s.lastname, s.city, CAST(MULTISET(SELECT idBasket, total FROM bb_basket b WHERE b.idShopper = s.idShopper) AS bask_tab) FROM bb_shopper s;

33. Summary • Dynamic SQL allows identifiers and DDL statements to process within PL/SQL • DBMS_SQL package and native dynamic SQL are two mechanisms providing dynamic SQL capabilities • Native dynamic SQL is simpler to code and executes more efficiently • Object types represent an entity that can contain multiple data attributes and program units

34. Summary (continued) • Program units in object types are called methods • Relationships between object rows are established using REF variables • Object views provide a mechanism to use object technology features with a traditional database design