Đang chuẩn bị liên kết để tải về tài liệu:
Oracle Built−in Packages- P27

Oracle Built−in Packages- P27: Ah, for the good old days of Version 1.0 of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms 3.0, and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk examine the consequences of using a placeholder. Suppose my dynamic PL SQL block were constructed as follows BEGIN nm_in val END When I bind in my value with a call to DBMS_SQL.BIND_VARIABLE it is not treated as a literal. For example if I call dynvar.copyto with the following arguments dynvar.copyto steven s hairline rapid.retreat then the PL SQL block I would execute is BEGIN rapid.retreat steven s hairline END which would definitely run into compile problems. When I first encountered this problem I figured that I would embed the val placeholder inside single quotes to make sure that the value I pass in was treated as literal. My parse statement then looked like this DBMS_SQL.PARSE cur BEGIN nm_in val END DBMS_SQL.NATIVE But this would not work DBMS_SQL did not recognize val as a placeholder any longer since it was inside single quotes. Sigh. The only solution was to remove val and instead of using placeholders simply concatenate the value directly into the string inside single quotes. The dynvar package should get you comfortable with executing dynamic PL SQL code and also with the concept of indirect referencing. I suggest that you try extending dynvar to support other datatypes numbers and dates in particular . See what challenges you encounter. You will definitely need to make some changes to incorporate these different datatypes into your dynamically constructed blocks. 2.5.6 Array Processing with DBMS_SQL One of the most significant advances for DBMS_SQL in Oracle8 is the support for array processing through use of the BIND_ARRAY and DEFINE_ARRAY procedures. Using these procedures you can greatly speed up dynamic SQL processing that involves multiple rows of data. This section offers some general suggestions and then provides detailed examples of using array processing in DBMS_SQL to perform fetches queries and dynamic PL SQL. In order to take advantage of array processing you will need to be comfortable with the use