1 / 18

Dynamic SQL Oracle Database PL/SQL 10g Programming

Dynamic SQL Oracle Database PL/SQL 10g Programming. Chapter 13. Dyanmic SQL. Defining Dynamic SQL Working with Native Dynamic SQL Working with DBMS_SQL Built-in. Dynamic SQL Defining Dynamic SQL. Build and run SQL statements on the fly. Evaluates dependencies at runtime.

lee
Download Presentation

Dynamic SQL Oracle Database PL/SQL 10g Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Dynamic SQLOracle Database PL/SQL 10g Programming Chapter 13

  2. Dyanmic SQL • Defining Dynamic SQL • Working with Native Dynamic SQL • Working with DBMS_SQL Built-in Oracle Database PL/SQL 10g Programming (Chapter 13)

  3. Dynamic SQLDefining Dynamic SQL • Build and run SQL statements on the fly. • Evaluates dependencies at runtime. • Defines column and table names at runtime. • Lets you define polymorphic statements. • Build dynamic SQL statements by using: • Native Dynamic SQL (NDS) uses EXECUTE IMMEDIATE to run dynamic statements. • DBMS_SQL Built-in provides a collection of overloaded functions and procedures to build and execute dynamic queries. Oracle Database PL/SQL 10g Programming (Chapter 13)

  4. Dynamic SQLNative Dynamic SQL (NDS) • Enables dynamic DDL and DML SQL statements without bind variables. • Enables dynamic DML with a known list of bind variables. • Enables dynamic DQL. Oracle Database PL/SQL 10g Programming (Chapter 13)

  5. Dynamic SQLNDS Advantages • It performs faster than DBMS_SQL. • It has syntax that mirrors standard SQL syntax. • It fetches directly into PL/SQL record types. • It supports all PL/SQL data types, including user-defined data types. Oracle Database PL/SQL 10g Programming (Chapter 13)

  6. Dynamic SQLNDS Advantages • It supports bulk processing: • BULK FETCH statement • BULK EXECUTE IMMEDIATE statement • FORALL statement • COLLECT INTO clause • RETURNING INTO clause • %BULK_ROWCOUNT attribute Oracle Database PL/SQL 10g Programming (Chapter 13)

  7. Dynamic SQLNDS: EXECUTE IMMEDIATE Clauses • INTO • Mode is OUT only. • Supports only single row return statements. • RETURNING <variable> INTO <bind_variable> • Modes are IN and OUT. • Supports name positional notation assigning variables to bind variables. • USING • Modes are IN and OUT by itself. • Mode is IN only when concurrently using RETURNING <variable> INTO <bind_variable> clause. • Supports only positional notation. Oracle Database PL/SQL 10g Programming (Chapter 13)

  8. Dynamic SQLNDS: Without bind variables DECLARE retval NUMBER; statement VARCHAR2(4000); BEGIN statement := 'CREATE SEQUENCE ' || a_sequence || 'INCREMENT BY 1'; EXECUTE IMMEDIATE statement; END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  9. Dynamic SQLNDS: Without substitution variables DECLARE retval NUMBER; sequence_name VARCHAR2(30) := 'sequence_name'; statement VARCHAR2(4000); BEGIN statement := 'CREATE SEQUENCE '||sequence_name||' ' || 'INCREMENT BY 1'; EXECUTE IMMEDIATE statement; END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  10. Dynamic SQLNDS: With bind variables DECLARE retval NUMBER; sequence_name VARCHAR2(30) := 'sequence_name'; statement VARCHAR2(4000); BEGIN statement := 'CREATE SEQUENCE :sequence_name ' || 'INCREMENT BY 1'; EXECUTE IMMEDIATE statement USING sequence_name; END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  11. Dynamic SQLNDS: With bind output variables DECLARE column_table1 VARCHAR2_NESTED_TABLE; column_table2 VARCHAR2_NESTED_TABLE; BEGIN statement := 'BEGIN ' || 'SELECT column_name1, column_name2 ' || 'BULK COLLECT INTO :column_table ' || 'FROM table_name;' || 'END;'; EXECUTE IMMEDIATE statement USING OUT column_table1, OUT column_table2; END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  12. Dynamic SQLDBMS_SQL Built-in • Enables dynamic DDL and DML SQL statements without bind variables. • Enables dynamic DML with a known list of bind variables. • Enables dynamic DQL. Oracle Database PL/SQL 10g Programming (Chapter 13)

  13. Dynamic SQLDBMS_SQL Built-in: Without bind variables DECLARE c INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk INTEGER; statement VARCHAR2(4000); BEGIN statement := 'CREATE SEQUENCE ' || a_sequence || 'INCREMENT BY 1'; DBMS_SQL.PARSE(c,statement,DBMS_SQL.NATIVE); fdbk := DBMS_SQL.EXECUTE(c); DBMS_SQL.CLOSE_CURSOR(c); END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  14. Dynamic SQLDBMS_SQL Built-in: With bind variables DECLARE c INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk INTEGER; input VARCHAR2(30); statement VARCHAR2(4000); BEGIN statement := 'INSERT INTO a_table ' || 'VALUES (:bind_variable)'; DBMS_SQL.PARSE(c,statement,DBMS_SQL.NATIVE); DBMS_SQL.BIND_VALUE(c,bind_variable,input); fdbk := DBMS_SQL.EXECUTE(c); DBMS_SQL.CLOSE_CURSOR(c); END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  15. Dynamic SQLDBMS_SQL Built-in: With bind array variables DECLARE c INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk INTEGER; input VARCHAR2_NESTED_TABLE; statement VARCHAR2(4000); BEGIN statement := 'INSERT INTO a_table ' || 'VALUES (:bind_variable)'; DBMS_SQL.PARSE(c,statement,DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY(c,bind_variable,input); fdbk := DBMS_SQL.EXECUTE(c); DBMS_SQL.CLOSE_CURSOR(c); END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  16. Dynamic SQLDBMS_SQL Built-in: With output bind variables DECLARE c INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk INTEGER; output VARCHAR2(4000); statement VARCHAR2(4000); BEGIN statement := 'SELECT column_value FROM a_table'; DBMS_SQL.PARSE(c,statement,DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN(c,1,output,4000); … loop_on_next_page … END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  17. Dynamic SQLDBMS_SQL Built-in: With output bind variables DECLARE … noted_on_prior_page … BEGIN LOOP EXIT WHEN DBMS_SQL.FETCH_ROWS(c) = 0; DBMS_SQL.COLUMN_VALUE(c,1,output); DBMS_OUTPUT.PUT_LINE('Print ['||output||']'); END LOOP; END; / Oracle Database PL/SQL 10g Programming (Chapter 13)

  18. Summary • Defining Dynamic SQL • Working with Native Dynamic SQL • Working with DBMS_SQL Built-in Oracle Database PL/SQL 10g Programming (Chapter 13)

More Related