Doing SQL from PL/SQL: Best and Worst Practices
Doing SQL from PL/SQL: Best and Worst Practices
An Oracle White Paper September 2008
21-September-2008
technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf
NOTE
The following is intended to outline our general product direction. it is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle.
Doing SQL from PL/SQL: Best and Worst Practices
21-September-2008
technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf
Doing SQL from PL/SQL: Best and Worst Practices
CONTENTS
Abstract . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Caveat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Periodic revision of this paper . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Embedded SQL, native dynamic SQL and the DBMS_Sql API . . . . . . . . . . . . . . 5 Embedded SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Resolution of names in embedded SQL statements. . . . . . . . . . . . . . . . . . . . . . . 6 Name capture, fine grained dependency tracking, and defensive programming . . 8 Ultimately, all SQL issued by a PL/SQL program is dynamic SQL . . . . . . . . . . . 9 Embedded SQL is more expressive than some programmers realize . . . . . . . . . 10 Native dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 The DBMS_Sql API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Cursor taxonomy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Questions addressed by the cursor taxonomy . . . . . . . . . . . . . . . . . . . . . . . . . . 17 The terms of art . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 sharable SQL structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 session cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 implicit cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 explicit cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 ref cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 cursor variable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 strong ref cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 weak ref cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 identified cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 DBMS_Sql numeric cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 explicit cursor attribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 implicit cursor attribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Approaches for select statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Selecting many rows -- unbounded result set . . . . . . . . . . . . . . . . . . . . . . . . 30 Programming the fetch loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Opening the cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Selecting many rows -- bounded result set . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Selecting many rows -- select list or binding requirement not known until run-time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Selecting a single row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Approaches for producer/consumer modularization . . . . . . . . . . . . . . . . . . 41 Stateful producer/consumer relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Stateless producer/consumer relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Doing SQL from PL/SQL: Best and Worst Practices
21-September-2008
technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf
Approaches for insert, update, delete, and merge statements . . . . . . . . . . . . . 47 Single row operation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Single row insert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Single row update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Single row delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Single row merge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Multirow operation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Handling exceptions caused when executing the forall statement . . . . . . . . . . . . 54 Digression: DML Error Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Referencing fields of a record in the forall statement . . . . . . . . . . . . . . . . . . . . . . 56 Bulk merge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Using native dynamic SQL for insert, update, delete, and merge . . . . . . . . . . . . . 57
Some use cases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Changing table data in response to query results . . . . . . . . . . . . . . . . . . . . . . 59 Number of in list items unknown until run time . . . . . . . . . . . . . . . . . . . . . . 61
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Appendix A:
Change History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Appendix B:
Summary of best practice principles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Appendix C: alternative approaches to populating a collection of records with the result of
a select statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Appendix D: Creating the test user Usr, and
the test table Usr.t(PK number, v1 varchar2(30), ...) . . . . . . . . . . . . . . . . . . . . 72
Doing SQL from PL/SQL: Best and Worst Practices
21-September-2008
technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf
Doing SQL from PL/SQL: Best and Worst Practices
ABSTRACT
The PL/SQL developer has many constructs for executing SQL statements, and the space of possibilities has several dimensions: embedded SQL versus native dynamic SQL versus the DBMS_Sql API; bulk versus non-bulk; implicit cursor versus parameterized explicit cursor versus ref cursor; and so on. Deciding which to use might seem daunting. Moreover, as new variants have been introduced, older ones sometimes have ceased to be the optimal choice. Oracle Database 11g has kept up the tradition by bringing some improvements in the area of dynamic SQL. This paper examines and categorizes the use cases for doing SQL from PL/SQL, takes the Oracle Database 11g viewpoint, and explains the optimal approach for the task at hand.
Doing SQL from PL/SQL: Best and Worst Practices
page 1
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- sams teach yourself oracle pl sql in 10 minutes
- doing sql from pl sql best and worst practices
- oracle xml db best practices to get optimal performance
- database programming with pl sql
- migrating non oracle databases and their applications to
- database system security
- cs405 database programming using oracle 11g
- mastering oracle pl sql practical solutions
- chapter 8 advanced sql
- 60 415 assignment 2 solution sql ddl and pl sql total
Related searches
- best and worst used cars to buy
- best and worst used cars
- best and worst cars list
- best and worst mortgage companies
- best and worst mortgage lenders
- best and worst veterans charities
- best and worst personality types
- best and worst schools by state
- best and worst senators
- best and worst navy bases
- best and worst roman emperors
- consumers report best and worst cars