PL/SQL: Control mythical code

Post from: http://www.oracle.com/global/cn/oramag/oracle/04-sep/o54plsql.html

Control mythical code
Author: Steven Feuerstein

Study and crack some myths in your code.

"Do not use the XYZ feature; there was a problem in previous versions of N. N. "" To always use explicit pointers, this is the most effective way to extract a single row of data. "

"To avoid using packages, they consume too much memory. "

Me
You must have heard of these arguments, or either of them. They are just a few myths that have long existed in our PL/SQL applications. In some cases, they are initially placed
There must be a good reason in our software. In other cases, ignoring them is a more positive approach. No matter what the original motive is, software that shows these myths is hard to find
Or even difficult to replace with "correct" code. This article disclaims the most harmful PL/SQL myth, explains how to systematically eliminate them, and provides information on how to avoid making today's written
Code becomes a mythical code suggestion for tomorrow.

"Do not use the xyx feature"

How Does myth get in the code? Let's take a look at a common situation of Software Development: writing code error handler tools.

Assume that I am building an application based on a third-party analysis package called analyze_rates. It contains an optimal_plan
Function, which returns information about a given company's best plan.

CREATE OR REPLACE PACKAGE analyze_rates
IS
   TYPE optimal_info_rt IS RECORD (
      rate_level PLS_INTEGER,
      rate_type PLS_INTEGER,
      is_optimal BOOLEAN);
   FUNCTION optimal_plan (id_in 
    IN company.id%TYPE) 
  RETURN optimal_info_rt;

Assuming that this plan is not the best, this function is in is_optimal of optimal_info_rt record type
The value of faslse is returned. Unfortunately, this function has an error: even if the plan is not the best plan, it always returns true in is_optimal. Worse, I cannot modify this package because it is provided by a third party and packaged.

Fortunately, there is a processing program: it can give that if the speed level and type are null, the plan is not the best.

I hurriedly sent a memorandum to my team with 25 developers to explain the problem and its solution. I ask everyone to write code like the following when calling analyze_rates.optimal_plan:

DECLARE
  l_company_id company.id%TYPE;
  opt_info analyze_rates.optimal_info_rt;
BEGIN
  get_company (l_company_id);
  opt_info := analyze_rates.optimal_plan (l_company_id);
  IF opt_info.rate_level 
   IS NULL 
   AND opt_info.rate_type 
   IS NULL
 THEN
    -- Not the optimal plan!

This method is indeed effective-at least for now. Therefore, all of my developers can copy and paste this If statement whenever they need it. Thus, a "mythical" basis is established.

However, if the vendor changes the optimal_plan function feature and invalidates this handler, what will happen? If the vendor actually solves their problem, optimal_plan
What happens if false is returned in the is_optimal domain of the record?

My team is unlikely to repeat all programs to find and replace the compound if statement.

The most likely result is that the processing program becomes a permanent part of the application, and it also becomes a myth that reflects the old reality and leads to confusion, and greatly increased the possibility of generating more future errors.

At the end of this article, "How to keep myth away from code"
In this section, I will explain to you how to avoid this situation and make the upgrade easier after the program error is corrected and the function is improved, in this way, we can clear the myth before entering the code. However, let's take a look at some of the most common myths in PL/SQL programs.

"You must always use an explicit Pointer"

PL/SQL always proves that it is designed as the fastest and easiest way to program Oracle databases. This is obvious when querying data in a database, because PL/SQL provides several options for you to write and execute select
Pointer method, including Implicit Static pointer, explicit static pointer, dbms_ SQL dynamic pointer, local dynamic SQL pointer, and bulk collect. Implicit and explicit static pointers are the most common methods for querying information from databases.

• Implicit pointer: Write only one SELECT statement in the program and contain one
  To receive the queried value, the Oracle database will complete the rest of the work for you (open, extract, close ). Here is an example:

  CREATE OR REPLACE change_team (
     id_in IN team_member.id%TYPE
    ,new_team_id_in IN team_member.team_id%TYPE   
     )
  IS
    l_team_member team_member%ROWTYPE;
  BEGIN
    SELECT * INTO l_team_member
      FROM team_member
     WHERE id = id_in;
  

• Explicit pointer: If you want to control the pointer processing process more and possibly reuse your SQL statements, you can explicitly declare your own pointer, then perform each operation on your own. Below is
  After the program is rewritten, the explicit pointer is used this time:

  CREATE OR REPLACE change_team (
    id_in IN team_member.id%TYPE
   ,new_team_id_in 
    IN team_member.team_id%TYPE   
     )
  IS
    CURSOR member_cur IS
       SELECT * 
          FROM team_member
         WHERE id = id_in;   
     l_team_member member_cur%ROWTYPE;
  BEGIN
    OPEN member_cur;
    FETCH member_cur INTO l_team_member;
  

Over the years, many Oracle experts (including me) have been promoting a fairly rigid creed about explicit and implicit pointers. This creed is described as follows:

You
You should always use an explicit pointer (cursor <cursor_name> is) to avoid using implicit pointers (select)
Because explicit pointers are always more efficient. They are more efficient because the implicit pointer behavior must comply with the ANSI standard, which stipulates that even for single row queries, you must always perform two
Fetch: Get the row at one time, and check whether there are too many rows (two or more rows ). Therefore, using implicit pointers is slower than using explicit pointers. For explicit pointers, you can send
Generates a single FETCH Command.

Is this logical and convincing? The problem is that although this was correct for oracle6 a long time ago, it is true for Oracle8i, Oracle9i, or Oracle
Of course, 10 Gb is incorrect. In fact, implicit pointers often run faster than explicit pointers. "Explicit pointers are always more efficient" is one of the longest-lasting PL/SQL myths, as it spread earlier and more widely.

So what should you do when writing pointers? I recommend the following principles:

1. Use bulk collect whenever possible-it can greatly improve performance. In Oracle9i 2nd, you can even use Bulk
Collect retrieves multiple rows of data and places them directly in a collection of records.

2. If only one row is taken, it makes no sense to use Bulk collect. No matter which pointer you choose to use to extract a single row of data, the pointer is hidden behind the function interface. Place a PL/SQL statement between the application logic and the basic SQL
The Code layer enables you to modify the query execution without affecting the application code. Using this method, my change_team program is as follows:

CREATE OR REPLACE change_team (
  id_in IN team_member.id%TYPE
 ,new_team_id_in 
   IN team_member.team_id%TYPE   
   )
IS
  l_team_member team_member%ROWTYPE;
BEGIN
  l_team_member :=
    team_member_qp.onerow (id_in);

Team_member_qp is a query package for team_member. It encapsulates a large number of query logics for tables, including the onerow function. In onerow
In the function, I can select an explicit or implicit Pointer -- or even check whether the cached team_member data is meaningful in the package-level set. It is important that application programmers can detect this execution problem so that they can be modified without causing a chain reaction.

"Avoid using the package"

In general, I respect the Oracle Database Administrator very much. They had to master a lot of knowledge about the Oracle architecture and its internal structure, and they had to keep up with all the changes brought about by each new version. However, if
Keeping up with the latest developments in Oracle, or failing to correctly understand one aspect of the database architecture, people may stick to rules that are meaningless.

For example, when DBAs tell developers to avoid using packages because they use too much memory, it shows ignorance of the runtime architecture of PL/SQL. Of course, like any other myth or legend, there is always something right in its core, and this myth is no exception.

When you call any single program in a software package, one thing is correct, that is, the compiled code of the entire software package is loaded into the memory. Therefore, if you are careless in designing a software package, you may use too much memory. The meaning is as follows:
Assume that I have created a very large software package, bigpkg, which contains 100 programs and requires 250 KB of memory. These programs contain a large number of functions. Suppose I want to call the program bigpkg. onething in my application
But does not use other programs in the software package. If onething only requires 20 KB of memory, I will use the additional 230 kb system global zone (SGA) memory without any need.

Therefore, there is a possibility of excessive memory consumption-but this is not a software package structure error. This is the result of poor Software Package Design and Application Design. In fact, if you carefully design your software package (see the following principles), you can improve the memory usage of your code in SGA.

To build a PL/SQL-based application, we recommend that you use the following software package principles:

• Put all the code into the software package to avoid independent processes and functions. (Indeed, this principle is the opposite of the myth mentioned above .) Even if there is only one program in your software package, you should consider that there will be another program with similar functions in the future, then you will have the Logical space to place the program.
• Create
  Build a large number of targeted software packages with small functions. For example
  The software package has no meaning. It is the "garbage dump" of everything ". Instead, you should create several software packages with carefully defined functions, such as string_utils, date_utils,
  Constants_pkg and config_pkg.
• Ensure that the software package and program names accurately describe their content. Carefully selecting a name makes an application easy to understand and maintain without making it a mess.

How to keep myth away from your code

The important question remains: how to avoid putting more and more new myths into the code?

There are two basic answers to this question: querying authority and encapsulation.

Inquire about authority. If your DBA tells you that there was a historic grievance (for example, there was a mistake ...) Avoid using a PL/SQL function field, or convince you of an advanced policy argument. For example, if "it will occupy too much memory", you must verify it by yourself. Are there still errors or have the problem been solved? Oracle provides excellent resources in Metalink to help you answer these questions.

If your problem is more about performance or functionality, you can write your own test program. After all, there is no philosophical problem. They are all technical points that can be challenged, which may be verified,
You can also remove it. One of the major attractiveness of software (indeed this is one of the main reasons I believe code writing is sometimes attractive) is that we are working on a closed system. A computer runs as a command.
. There is no mysterious soul here, so the PL/SQL runtime engine will never lie to you.

Therefore, if someone claims that the software package has memory problems, you can run your own test program and analyze the memory usage. Code List 1 contains a program that you can use to display the memory usage of UGA and PGA in a specific session, which is very useful to PL/SQL developers, because program data (such as a set) occupies space in PGA.

If someone claims that a particular technology (such as implicit pointers) is slow or slower than another method, you can write your own program to compare various technologies. You can use set_timing in SQL * Plus
On, or use the function in dbms_utility (get_time or get_cpu_time -- the latter is the new function in Oracle 10g) to help you calculate the time spent with sub-second precision. For example, code list 2 contains an object type description that allows you to start and stop the timer in your program.

Encapsulation. Location
The second major mechanism of mythical code is encapsulation. When You encapsulate the software, build a code layer between the other two parts of the software (usually the application logic and some basic functions. If you have reason to believe that
In the future, this basic function will be changed, hidden in a function or process, and then called. After the function is improved (for example, the error is corrected), you only need to replace the old one with a new implementation.
The mythical code disappears before it becomes a myth.

Because you have not changed the public appearance of the program-its name and parameter list-so each program in the application layer of the Code will not be affected. The programming world is very familiar with this process of changing the internal organization of the program without modifying its external interfaces by resetting the internal components.

It is very important to encapsulate the error handler or patch for handling errors. You can take some special steps for this encapsulation.
Now let's review the optimal_plan error introduced at the beginning of this article.

Earlier, I reminded developers to write code similar to the following to disclose the error handler in their code:

IF opt_info.rate_level IS NULL 
  AND opt_info.rate_type IS NULL
THEN
  -- Not the optimal plan!

Let's take a look at how encapsulated in this situation can help us get rid of the predicament. The following are a set of alternative steps that can be followed when an error is responded:

1. Create a software package to include the error handler for the analyze_rates package. We call it analyze_rates_wa.

2. Create a function in this error handling package, which is located at the top of optimal_plan and has the same name. It calls a third-party function, but also implements a handler for is_optimal errors. The operation method of this error handler function is the same as the operation method originally proposed.

3. Set a rule for your application: Never call analyze_rates.optimal_plan
Instead, call analyze_rates_wa.optimal_plan. Note: You can perform and check the global search and replacement carefully to implement this modification in the existing code.

4. accurately describe what is wrong and how to exclude it in the error handling program.

5. After the vendor solves the code error/upgrades the code, you can do one of the following:
The reference is changed back to the reference to analyze_rates.optimal_plan, or if there are other errors, keep the calls to this error handler in your code, but upgrade the internal organization of the program, to use the new features in the provided software.

Code List 3
Contains the implementation example of my error handling package for the optimal_plan function. I have included a comment in my function to explain the cause of the problem, the features of the error handler, and how to upgrade the code after eliminating the program error.

Assume that the program error is fixed two years after you have compiled the application. The original development team has already been disbanded. Because you have a foresight to write an error handling program, developers who maintain the application can easily and confidently upgrade the Code to reflect the new reality, instead of fighting against the obscure error handler that will later become a myth.

Code without myth is maintainable

The lifetime of the program you write is generally longer than anyone might expect. In addition, the more code you write, the more code you enter the production and maintenance status. If you cannot write your program carefully, you (or anyone who takes over your program) will eventually have to deal with complex, chaotic, and vulnerable applications.

Mythical code-some PL/SQL code lines that present an incorrect scenario-can only make this situation worse. Today, the more effort you make to eliminate and avoid the myth in your code, the better everyone will live and work!