# Oracle PL/SQL Language Pocket Reference- P26

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
37
lượt xem
7

## Oracle PL/SQL Language Pocket Reference- P26

Mô tả tài liệu

Oracle PL/SQL Language Pocket Reference- P26: This pocket guide features quick-reference information to help you use Oracle's PL/SQL language. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i. It is a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. Updated for Oracle8, that large volume (nearly 1,000 pages) fills a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL and building them the right way. ...

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: Oracle PL/SQL Language Pocket Reference- P26

1. value as shown below: new_rate := mortgage_rate (down_payment, prime_rate, mortgage_type); The mortgage_rate function was, unfortunately, setting new_rate to NULL. After examining the function, it was clear to me that the only way it could return a NULL was if one of the inputs was NULL. I then used this information as follows: 1. I had just examined the global variable holding the mortgage type. That global value was transferred to the mortgage_type variable in an earlier program and passed to my current module, so I knew that it was OK. 2. I performed a walk-through of the code and could not identify how any of the other two variables could be NULL. 3. So I inserted a trace statement before and after the call to mortgage_rate. My code now looked like this: DBMS_OUTPUT.PUT_LINE ('Inputs: ' || TO_CHAR (down_payment) || '-' || TO_CHAR (prime_rate)); new_rate := mortgage_rate ( down_payment, prime_rate, bank, mortgage_type); DBMS_OUTPUT.PUT_LINE ('Rate: ' || NVL (TO_CHAR (new_rate), 'Missing')); 4. I ran the program and no matter what data I entered on the screen, my trace statements remained the same: Inputs: 55000-9.5 Rate: Missing I wracked my obviously overwrought brain: what could cause a stored function to return a NULL value? I looked at the source code for the function again. There wasn't much to it. Just division and multiplication. How could it return a NULL without a NULL input? After two hours of this nonsense, I finally said to myself, "Well, you know that you really haven't verified the value of the mortgage_type variable." I knew that it was OK, but, hey, it wouldn't hurt to check -- and if I didn't solve this one soon I would have to actually ask for help. So I modified my trace statement and, sure enough, the mortgage type was NULL. Turns out that while the global variable held the proper value, the previous program did not pass it to the local variable properly. My assumption did me in. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
3. debugger, xrayvizn.zip, on the RevealNet Web site. While you are unlikely to be using SQL*Forms at this point, you may find the source code of interest (stored in the good, old INP files). When Oracle Forms 4.x (even the version in Oracle Developer/2000 that has its own source code debugger) came along, I realized that it was impossible to view and change data structures created at runtime (record groups, parameter lists, etc.). So I built a utility named Arjy (pronounced "RG" for Record Group), which gave me that access. The shareware version of Arjy, arjy.zip is also available at the RevealNet Web site. The basic PL/SQL product from Oracle Corporation will never have everything you need. If you can't find what you need to get the job done, then get creative and take a crack at meeting your own needs. Dive in and build your own utility. Not only will you improve your productivity (and that of others), but you will gain a feeling of intense satisfaction from solving your own problems all by yourself. 24.2.9 Build Debugging Messages into Your Packages If you do not use a GUI-based, source code debugger, you probably spend a fair amount of time throwing debug or trace messages into your code and then removing them when things are fixed. A much better approach is to leave these messages intact, but give yourself lots of flexibility in deciding when you want to see them. The simplest model for this technique is actually built right into the DBMS_OUTPUT package. The DBMS_OUTPUT.PUT_LINE procedure displays output from your PL/SQL program when that program completes execution. But it will not show anything unless the package itself is enabled with a call to DBMS_OUTPUT.ENABLE and/or unless from within SQL*Plus you issue the SET SERVEROUTPUT ON command. Furthermore, this is an all-or-nothing proposition: you either see no output, or you see output from every call to this procedure from every corner of your application. That can be overwhelming if you have inserted lots of trace messages. You can easily come up with a more discerning technique when working with packages. Suppose I have developed a package to perform calculations for profit-and-loss statements. My p_and_l package maintains a last statement date as a global variable for use within the package. I build a "get and set" layer of code around the variable so that applications can retrieve and manipulate the variable -- but only through my code layer. Here is the package: CREATE OR REPLACE PACKAGE p_and_l IS PROCEDURE set_lastdate (date_in IN DATE); FUNCTION lastdate RETURN DATE; /* Lots of other stuff, too! */ . . . END p_and_l; / Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
4. CREATE OR REPLACE PACKAGE BODY p_and_l IS g_lastdate DATE; PROCEDURE set_lastdate (date_in IN DATE) IS BEGIN /* Date cannot be in future. */ g_lastdate := LEAST (SYSDATE, date_in); END; FUNCTION lastdate RETURN DATE IS BEGIN RETURN g_lastdate; END; END p_and_l; / As I test this package as part of a large, complex application, suppose that I find that the last date variable is being set improperly, but I can't figure out what is doing it and why. I can go into the p_and_l.set_lastdate procedure and insert a call to DBMS_OUTPUT.PUT_LINE as follows: PROCEDURE set_lastdate (date_in IN DATE) IS BEGIN DBMS_OUTPUT.PUT_LINE (setting date to  || TO_CHAR (date_in)); /* Date cannot be in future. */ g_lastdate := LEAST (SYSDATE, date_in); END; but then I have to see all the output in my application and try to find this one statement among all the others. The approach I take instead is to provide a debug "toggle" in my package which allows me to focus output to just the statements I need to see. With the toggle technique, I add three programs to my package specification: CREATE OR REPLACE PACKAGE p_and_l IS PROCEDURE set_lastdate (date_in IN DATE); FUNCTION lastdate RETURN DATE; PROCEDURE dbg; PROCEURE nodbg; FUNCTION debugging RETURN BOOLEAN; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
5. /* Lots of other stuff, too! */ . . . END p_and_l; / I also modify the package body to both implement this toggle and use it inside the set_lastdate procedure: CREATE OR REPLACE PACKAGE BODY p_and_l IS g_lastdate DATE; g_dbg BOOLEAN := FALSE; PROCEDURE dbg IS BEGIN g_dbg := TRUE; END; PROCEDURE nodbg IS BEGIN g_dbg := FALSE; END; FUNCTION debugging RETURN BOOLEAN RETURN g_dbg; END; PROCEDURE set_lastdate (date_in IN DATE) IS BEGIN IF debugging THEN DBMS_OUTPUT.PUT_LINE (before set  || TO_CHAR (g_lastdate)); END IF; /* Date cannot be in future. */ g_lastdate := LEAST (SYSDATE, date_in); IF debugging THEN DBMS_OUTPUT.PUT_LINE (after set  || TO_CHAR (g_lastdate)); END IF; END; FUNCTION lastdate RETURN DATE IS BEGIN RETURN g_lastdate; END; END p_and_l; / Then if I want to see what is happening to the g_lastdate variable, I can issue the debug command in SQL*Plus for this package and see just the output I need: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
10. substrings into a long string? There are two basic approaches you can take to using this handy function: q Write again and again the kind of script you see above, changing the program or lines of code executed. q Encapsulate the way dbms_utility.get_time operates inside a package, which will hide the details and make it easier to use. You will find on the companion disk an explanation and code for such a package, sp_timer, in the files sptimer.sps and sptimer.spb. In addition, you will find in Advanced Oracle PL/SQL Programming with Packages a more complete performance timing utility based on DBMS_UTILITY. GET_TIME in the PLVtmr package. Once you have encapsulated your usage of DBMS_UTILITY.GET_TIME, it is very easy to put together scripts which not only analyze performance, but also compare different implementations. The following script, for example, executes two different versions of the is_number function (see "Section 25.4, "Tuning Your Algorithms"" for more information on this function) and displays the resulting elapsed times (using the PLVtmr and p packages from the PL/Vision library; again, see Advanced Oracle PL/SQL Programming with Packages: SET VERIFY OFF DECLARE b BOOLEAN; BEGIN PLVtmr.set_factor (&1) PLVtmr.capture; FOR repind IN 1 .. &1 -- Number of iterations LOOP b := isnum ('&2'); -- The string to test IF repind = 1 THEN p.l (b); END IF; END LOOP; PLVtmr.show_elapsed (TO_NUMBER Version'); PLVtmr.set_factor (&1) PLVtmr.capture; FOR repind IN 1 .. &1 LOOP b := isnum_translate ('&2'); PLVtmr.last_timing := 15; IF repind = 1 THEN Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
14. procedure. Once you have pinned the package, you can revoke the PUBLIC access. q The SQL DDL command, ALTER SYSTEM FLUSH SHARED_POOL, does not affect pinned packages. You must either explicitly UNKEEP a package or restart the database to release the package from the shared pool. 25.2.2.1 Candidates for pinning in the shared pool You might consider pinning the following packages in the shared pool to improve performance: STANDARD Package which implements the core elements of the PL/SQL language. DBMS_STANDARD Package of standard database-level modules, such as RAISE_APPLICATION_ERROR. DBMS_UTILITY Package of low-level database utilities which are used to analyze schemas and objects. DBMS_DESCRIBE Package containing a utility to describe the structure of a stored module. DBMS_OUTPUT Package which allows programmers to display output to the screen. In addition, if you make frequent use of any other Oracle-provided "SYS" packages such as DBMS_LOCK or DBMS_PIPE, pinning those objects could improve performance as well. You are probably getting the idea that the more you pin into the shared pool, the better off you are. Certainly that is true, at least as true as the statement: "If all your data is stashed in memory, your applications will run much faster." Memory is always quicker than disk access. The problem is making sure you have enough memory. The more you pin into the shared pool, the less space is left in the SGA for other memory-based resources, such as data dictionary latches, application data, and shared SQL. Since a pinned object is never aged out of the SGA using a least-recently-used algorithm, other elements in the SGA are instead pushed out of the way. You can use SQL to generate a script to KEEP packages in the SGA. You can use the following SQL statement to access the v$db_object_cache to generate a KEEP for each package currently in the shared pool: SELECT 'EXECUTE DBMS_SHARED_POOL.KEEP('''||name||''');' FROM v$db_object_cache Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
15. WHERE type='PACKAGE'; You can also generate a KEEP statement for every package currently stored in the database with these other SQL statements: SELECT DISTINCT 'EXECUTE DBMS_SHARED_POOL.KEEP('''|| name||''');' FROM user_source WHERE type='PACKAGE'; SELECT DISTINCT 'EXECUTE DBMS_SHARED_POOL.KEEP('''|| object_name||''');' FROM user_objects WHERE object_type='PACKAGE'; 25.2.3 Tune ACCESS$Table to Reduce First Execution Time of Code When a database object is first referenced in a PL/SQL program, the PL/SQL engine checks the ACCESS$ table (owned by SYS) to see if the executor of the program has authority on that database object. The structure of this table is shown here: SQL> desc access$Name Null? Type ------------------------------- -------- ---- D_OBJ# NOT NULL NUMBER ORDER# NOT NULL NUMBER COLUMNS RAW(32) TYPES NOT NULL NUMBER The PL/SQL engine searches through this table on the D_OBJ# column, so if you create a nonunique index on the D_OBJ# column, you may in some cases reduce significantly the amount of time needed to perform this security check. 25.2.4 Creating Packages with Minimal Interdependencies Design your code (preferably, most of it inside packages) so that you only load into memory the code you need at any given moment in time. To accomplish this objective, you should create more smaller packages, each of which is tightly focused on implementing functionality in a given area. The Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 16. alternative, which too many of us employ without giving it much thought, is to create a few large packages that group together lots of different elements of functionality. The problem with this approach is that if I need to execute one function in that 32K package, the entire package still must be loaded up into memory. Suppose that my application then touches another element of the package, such as a constant or perhaps a different function in a different functional area. The least-recently-used algorithm will then ensure that all the memory for that package continues to be set aside, perhaps crowding out other smaller programs. The result can be excessive swapping of code. As you build your programs and design your package interfaces, be on the lookout for an opportunity to break up a single package into two or even more distinct packages with minimal interdependencies. 25.2.5 Reducing Memory Usage of Package Variables Prior to PL/SQL8, any data declared in a package simply stayed around until the end of the session, whether or not it was needed any more by the application. This is an important feature of PL/SQL packages (persistent, global data), but it limits scalability since such memory grows linearly with the number of users. To help applications better manage memory usage, PL/SQL8 provides the pragma SERIALLY_REUSABLE, which lets you mark some packages as "serially reusable." You can so mark a package if its state is needed only for the duration of a call to the server (for example, an OCI call to the server, a PL/SQL client-to-server, or server-to-server RPC). The global memory for such packages is not kept in the memory area per user, but instead in a small SGA pool. At the end of the call to the server, this memory is returned to the pool for reuse. Before reuse, the package global variables are initialized to NULL or to the default values provided. The pool is kept in SGA memory so that the work area of a package can be reused across users who have requests for the same package. In this scheme, the maximum number of work areas needed for a package is only as many as there are concurrent users of the package, which is typically much fewer than the total number of logged on users. The use of "serially reusable" packages does increase the shared-pool requirements slightly, but this is more than offset by the decrease in the per-user memory requirements. Further, Oracle ages out work areas not in use when it needs to reclaim shared pool memory. The following example shows how global variables in a "serially reusable" package behave across call boundaries: CREATE OR REPLACE PACKAGE sr_pkg IS PRAGMA SERIALLY_REUSABLE; num NUMBER := 0; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 17. PROCEDURE print_pkg; PROCEDURE init_pkg (n NUMBER); END sr_pkg; / CREATE OR REPLACE PACKAGE BODY sr_pkg IS -- the body is required to have the pragma since the -- specification of this package has the pragma PRAGMA SERIALLY_REUSABLE; -- Print package state PROCEDURE print_pkg is BEGIN DBMS_OUTPUT.PUT_LINE ('num: ' || sr_pkg.num); END; -- Initialize package state PROCEDURE init_pkg(n NUMBER) IS BEGIN sr_pkg.num := n; END; END sr_pkg; / Now I will exercise this package. First, I enable output from SQL*Plus: SQLPLUS> set serveroutput on; Next, I initialize the package with a value of 4 and then display package contents -- all within a single PL/SQL block: SQLPLUS> begin -- initialize and print the package SR_PKG.init_pkg(4); -- Print it in the same call to the server. -- We should see the new values. SR_PKG.print_pkg; end; / Statement processed. num: 4 And we see that initial value of 4. If I had not placed the call to sr_pkg.print_pkg inside the same PL/ SQL block, however, that package variable would lose its setting, as you can see in the following steps: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 18. SQLPLUS> begin -- We should see that the package state is reset to the -- initial (default) values. SR_PKG.print_pkg; end; / Statement processed. num: 0 Use this feature with care! Many of the packages I have constructed over the years absolutely rely on the persistent data feature. Previous: 25.1 Analyzing Oracle PL/SQL Next: 25.3 Tuning Access Program Performance Programming, 2nd Edition to Your Data 25.1 Analyzing Program Book Index 25.3 Tuning Access to Your Performance Data The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 19. Previous: 25.2 Tuning Chapter 25 Next: 25.4 Tuning Your Access to Compiled Code Tuning PL/SQL Applications Algorithms 25.3 Tuning Access to Your Data Much of the tuning you do will attempt to optimize the way PL/SQL programs manipulate data in the Oracle database, both queries and DML (updates, inserts, deletes). Lots of the issues here involve tuning SQL statements, and I am not even going to attempt to show you how to tune SQL (definitely not my strong suit). There are certainly steps you can take, though, in your PL/SQL code and environment to improve the performance of even an optimally constructed chunk of SQL. 25.3.1 Use Package Data to Minimize SQL Access When you declare a variable in a package body or specification, its scope is not restricted to any particular procedure or function. As a result, the scope of package-level data is the entire Oracle session, and the value of that data persists for the entire session. Take advantage of this fact to minimize the times you have to access data from the SQL layer. Performing lookups against structures located in your own Program Global Area (PGA) is much, much faster than going through the SGA -- even if the data you want is resident in shared memory. This tip will come in handy most when you find that your application needs to perform multiple lookups which do not change during your session. Suppose, for example, that one of your programs needs to obtain a unique session identifier to avoid overlaps with other sessions. One of the ways to do this is to call DBMS_LOCK.ALLOCATE_UNIQUE to retrieve a unique lockname. Here is the inefficient way to do this: I need the ID or lockname in the calc_totals procedure. So I make the call to the built-in package right inside the procedure: PROCEDURE calc_totals IS v_lockname VARCHAR2(100); BEGIN v_lockname := DBMS_LOCK.ALLOCATE_UNIQUE; /* Use the lock name to issue a request for data. */ send_request (v_lockname); . . . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 20. END; The problem with this approach is that every time calc_totals is called, the built-in function is also called to get the unique value -- a totally unnecessary action. After you get it the first time, you needn't get it again. Packages provide a natural repository for these "session-level" pieces of data. The following sesspkg (session package) defines a variable to hold the lock name and then assigns it a value on initialization of the package: PACKAGE sesspkg IS lockname CONSTANT VARCHAR2(100) := DBMS_LOCK. ALLOCATE_UNIQUE; END; Now the calc_totals procedure can directly reference the package global in its call to send_request. The first time calc_totals is called, the sesspkg package is instantiated and the built-in function called. All subsequent calls to calc_totals within that session will not, however, result in any additional processing inside sesspkg. Instead, it simply returns the value resident in the constant. PROCEDURE calc_totals IS BEGIN /* Use the lock name to issue a request for data. */ send_request (sesspkg.lockname); END; You can apply a similar technique for lookups against database tables -- both persistent and session- based. In the following example, the SELECT from v$parameter will be executed only once per session, regardless of how many times the db_name function is executed. Since the database name will never change during the session, this is reasonable. Note also the judicious use of a function with side effects, where one side effect (modification of a persistent package variable) is used to gain the efficiency: /* Filename on companion disk: dbdata.spp */ CREATE OR REPLACE PACKAGE db_data IS /* function to return the name of the database */ FUNCTION db_name RETURN VARCHAR2; END db_data; / CREATE OR REPLACE PACKAGE BODY db_data IS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.