PLSQLDEV_cursor.so

 for preformatted text so that spaces and returns are preserved.
  

      
      

       
        and 
       
       
 to include a bullet list.
  
 
The following table describes all tags:
        
        

         
         , 
         
         
 and 

           
            to include a table.          
          
          
         
         
        
        
        
        
 
Tag
 
 
Description
 
 
%param 
              
               
             
 
 
The description of a function or procedure parameter. Subsequent %param tags will be placed in one table.
 
 
%return 
             
 
 
A description of the return value of a function.
 
 
%value 
              
               
             
 
 
A possible value for a package variable or object type attribute. Subsequent %value tags will be placed in one table.
 
 
%raises 
              
               
             
 
 
A list of exceptions that can be raised by a program unit. Subsequent %raises tags will be placed in one table.
 
 
%see 
             
 
 
See also hyperlink. Subsequent %see tags will be placed in one table. To link to a specific overloading of a program unit, follow the name by a semi-colon and the overload index (1-based). For example:
 
%see employee 
%see department.dname;2
 
 
%usage 
             
 
 
A text to describe the usage of the element.
 
 
%author 
             
 
 
The author of the object.
 
 
%version 
             
 
 
The version of the object.
 
 
{%link 
              
              
 
                 [description]} 
             
 
or
 
{%link 
             
               [description]} 
             
 
 
An explicit hyperlink. To link to a specific overloading of a program unit, follow the name by a semi-colon and the overload index (1-based). The optional description will be placed in the document. For example:
 
The {%link department.name department name function} is...
 
Will result in:
 
The department name function is…
 
 
{*} 
              
               
             
 
 
A bullet of a bullet list. Useful to describe possible values of a parameter or return value. For example:
 
{*} 0 The function returned successfully 
{*} 1 The employee does not exist 
{*} 2 The employee record is locked
 
 
{%skip}
 
 
Skip this comment block for documentation.
 
  
The complete example in the next chapter demonstrates most of these tags.
  
  
 
  
  
  
 
  
  
35   Explain Plan, Profiler Install in own schema
  
Run TOAD, go to Tools > Server side install > Install in individual schema
  
  
 
  
  
36   PRAGMA Keyword
  
A programming notion that is truly derived from Greek is pragma, which means "deed" or, by implication, an "action, It's like an option that you give the compiler; it can result in different runtime behavior for the program, but it doesn't get translated directly into byte code.
  
  
 
  
  
37   Exception
  
System exception
  
An exception that is defined by Oracle and is usually raised by the PL/SQL runtime engine when it detects an error condition. Some system exceptions have names, such as NO_DATA_FOUND, while many others simply have numbers and descriptions.
  
  
 
  
  
Programmer-defined exception
  
An exception that is defined by the programmer and is therefore specific to the application at hand. You can associate exception names with specific Oracle errors using the EXCEPTION_INIT pragma (a compiler directive, requesting a specific behavior), or you can assign a number and description to that error using RAISE_APPLICATION_ERROR.
  
  
 
  
  
37.1RAISE_APPLICATION_ERROR
  
In addition, a developer can raise exceptions using RAISE_APPLICATION_ERROR (covered later under "Raising Exceptions) that consist of nothing more than an error number (between -20000 and -20999) and an error message.
  
  
 
  
  
When this procedure is run, execution of the current PL/SQL block halts immediately, and any changes made to OUT or IN OUT arguments (if present) will be reversed. Changes made to global data structures, such as packaged variables, and to database objects (by executing an INSERT, UPDATE, or DELETE) will not be rolled back. You must execute an explicit ROLLBACK in your exception section to reverse the effect of DML operations.
  
Here's the header for this procedure (defined in package DBMS_STANDARD):
  
    PROCEDURE RAISE_APPLICATION_ERROR (
  
       num binary_integer,
  
       msg varchar2,
  
       keeperrorstack boolean default FALSE);
  
  
 
  
  
EXCEPTION_INIT
  
EXCEPTION_INIT is a compile-time command or pragma used to associate a name with an internal error code. EXCEPTION_INIT instructs the compiler to associate an identifier, declared as an EXCEPTION, with a specific error number. Once you have made that association, you can then raise that exception by name and write an explicit WHEN handler that traps the error.
  
  
 
  
  
It cannot be -1403 (one of the two error codes for NO_DATA_FOUND). If for some reason you want to associate your own named exception with this error, you need to pass 100 to the EXCEPTION_INIT pragma.
  
It cannot be 0 or any positive number besides 100.
  
It cannot be a negative number less than -1000000.
  
  
 
  
  
invalid_table_name EXCEPTION;
  
PRAGMA EXCEPTION_INIT (invalid_table_name, -903);
  
  
 
  
  
invalid_column_name EXCEPTION;
  
PRAGMA EXCEPTION_INIT (invalid_column_name, -904);
  
  
 
  
  
  
 
  
  
Table 3
  
        
        
 
Table 6-1. Some of the predefined exceptions in PL/SQL
 
 
Name of exception Oracle error/SQLCODE
 
 
Description
 
 
CURSOR_ALREADY_OPEN ORA-6511 SQLCODE=-6511
 
 
You tried to OPEN a cursor that was already OPEN. You must CLOSE a cursor before you try to OPEN or re-OPEN it.
 
 
DUP_VAL_ON_INDEX ORA-00001 SQLCODE= -1
 
 
Your INSERT or UPDATE statement attempted to store duplicate values in a column or columns in a row that is restricted by a unique index.
 
 
INVALID_CURSOR ORA-01001 SQLCODE=-1001
 
 
You made reference to a cursor that did not exist. This usually happens when you try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed.
 
 
INVALID_NUMBER ORA-01722 SQLCODE =-1722
 
 
PL/SQL executes a SQL statement that cannot convert a character string successfully to a number. This exception is different from the VALUE_ERROR exception because it is raised only from within a SQL statement.
 
 
LOGIN_DENIED ORA-01017 SQLCODE= -1017
 
 
Your program tried to log into the Oracle RDBMS with an invalid username-password combination. This exception is usually encountered when you embed PL/SQL in a 3GL language.
 
 
NO_DATA_FOUND ORA-01403 SQLCODE= +100
 
 
This exception is raised in three different scenarios: (1) You executed a SELECT INTO statement (implicit cursor) that returned no rows. (2) You referenced an uninitialized row in a local PL/SQL table. (3) You read past end-of-file with the UTL_FILE package.
 
 
NOT_LOGGED ON ORA-01012 SQLCODE= -1012
 
 
Your program tried to execute a call to the database (usually with a DML statement) before it had logged into the Oracle RDBMS.
 
 
PROGRAM_ERROR ORA-06501 SQLCODE= -6501
 
 
PL/SQL encounters an internal problem. The message text usually also tells you to "Contact Oracle Support."
 
 
STORAGE_ERROR ORA-06500 SQLCODE= -6500
 
 
Your program ran out of memory, or memory was in some way corrupted.
 
 
TIMEOUT_ON_RESOURCE ORA-00051 SQLCODE=-51
 
 
A timeout occurred in the RDBMS while waiting for a resource.
 
 
TOO_MANY_ROWS ORA-01422 SQLCODE= -1422
 
 
A SELECT INTO statement returned more than one row. A SELECT INTO can return only one row; if your SQL statement returns more than one row, you should place the SELECT statement in an explicit CURSOR declaration and FETCH from that cursor one row at a time.
 
 
TRANSACTION_BACKED_OUT ORA-00061 SQLCODE= -61
 
 
The remote part of a transaction is rolled back, either with an explicit ROLLBACK command or as the result of some other action (such as a failed SQL/DML on the remote database).
 
 
VALUE_ERROR ORA-06502 SQLCODE= -6502
 
 
PL/SQL encountered an error having to do with the conversion, truncation, or invalid constraining of numeric and character data. This is a very general and common exception. If this type of error is encountered in a SQL DML statement within a PL/SQL block, then the INVALID_NUMBER exception is raised.
 
 
ZERO_DIVIDE ORA-01476 SQLCODE= -1476
 
 
Your program tried to divide by zero.
 
  
  
 
  
  
  
 
  
  
38   Built-in Error Functions
  
SQLCODE
  
SQLCODE returns the error code of the most recently raised exception in your block.
  
  
 
  
  
SQLERRM
  
SQLERRM is a function that returns the error message for a particular error code
  
  
 
  
  
DBMS_UTILITY.FORMAT_ERROR_STACK
  
This built-in function, like SQLERRM, returns the message associated with the current error (i.e., the value returned by SQLCODE). It differs from SQLERRM in two ways:
  
Its length is not restricted; it will return the full error message string.
  
You cannot pass an error code number to this function; it cannot be used to return the message for a "random" error code
  
  
 
  
  
DBMS_UTILITY.FORMAT_CALL_STACK     
 
  
  
DBMS_UTILITY.FORMAT_CALL_STACK returns a formatted string showing the execution call stack inside your PL/SQL application. Its usefulness is not restricted to error management; you will also find it handy for tracing the execution of your code. If, however, you are not yet running Oracle Database 10g, you can include this function in your exception section and at least see the sequence of program calls that got you to the exception handler.
  
  
 
  
  
  
 
  
  
Useful applications of SQLERRM
  
As mentioned earlier in this chapter, SQLERRM will return the error message for an error code. If, however, you pass SQLERRM a code that is not valid, it does not raise an exception. Instead, it returns a string in one of the following two forms:
  
If the number is negative:
  
    ORA-NNNNN: Message NNNNN not found;  product=RDBMS; facility=ORA
  
  
 
  
  
If the number is positive:
  
    -NNNNN: non-ORACLE exception
  
  
 
  
  
Unhandled Exceptions
  
PL/SQL returns the error that raised the unhandled exception all the way back to the application environment from which PL/SQL was run, in the case of SQL*Plus, a ROLLBACK of any DML changes from within that top-level block's logic is automatically performed
  
  
 
  
  
How can this be so? Because all user-defined exceptions have the same error number and error message: 1 and "User-defined error", respectively. Not very informative. Only the exception's name is unique. But outside of local_block, that name, that exception, no longer exists.
  
Decide on Your Error Management Strategy
  
Linked tightly to these very high-level questions are more concrete issues, such as:
  
Should I include an exception-handling section in every one of my PL/SQL blocks?
  
Should I have an exception-handling section only in the top-level or outermost blocks?
  
How should I manage my transactions when errors occur?
  
  
 
  
  
It depends at least in part on the application architecture and the way it is used (batch process versus user-driven transactions, for example). However you answer these questions for your application, I strongly suggest that you "codify" the strategy and rules for error handling within a standardized package. I address this topic in a later section.
  
  
 
  
  
Here are some general principles you may want to consider:
  
When an error occurs in your code, obtain as much information as possible about the context in which the error was raised. You are better off with more information than you really need, rather than with less. You can then propagate the exception to outer blocks, picking up more information as you go.
  
Avoid hiding errors with handlers that look like WHEN error THEN NULL;. There may be a good reason for you to write code like this, but do make sure it is really what you want and document the usage so that others will be aware of it.
  
Rely on the default error mechanisms of PL/SQL whenever possible. Avoid writing programs that return status codes to the host environment or calling blocks. The only time you will want to use status codes is if the host environment cannot gracefully handle Oracle errors (in which case, you might want to consider switching your host environment!).
  
  
 
  
  
Notice that I am writing to a database table for my log. This means that the log entry has become a part of my logical transaction. If I need to roll back that transaction, I lose my error log.
  
  
 
  
  
39   Internet Datatypes
  
The XMLType allows you to query and store XML data in the database using functions like SYS_XMLGEN and the DBMS_XMLGEN package. It also allows you to use native operators in the SQL language to perform searching with the XPath language.
  
  
 
  
  
The URI-related types, including URIType and HttpURIType, are all part of an object type inheritance hierarchy and can be used to store URLs to external web pages and files, as well as to refer to data within the database
  
  
 
  
  
40   "Any" Datatypes
  
We write more generic kinds of code. For those situations, the "Any" datatypes might come in very handy.
  
  
 
  
  
The "Any" datatypes include AnyType, AnyData, and AnyDataSet.
  
  
 
  
  
41   Anchored Declarations
  
variable name type attribute%TYPE [optional default value assignment];

variable name table_name | cursor_name%ROWTYPE [optional default value
           
           
           
           

assignment];

41.1Anchoring to Cursors and Tables

    DECLARE

       CURSOR book_cur IS

          SELECT author, title FROM book

           WHERE  isbn = '1-56592-335-9'



 



       l_book book_cur%ROWTYPE;

    BEGIN

       OPEN book_cur;

       FETCH book_cur INTO l_book;

       process_book (l_book);

    END;



 



Benefits of Anchored Declarations

Synchronization with database columns

Normalization of local variables

42   Anchoring to NOT NULL Datatypes

If you use a NOT NULL variable in a %TYPE declaration, the new variable must have a default value provided. The same is not true, however, for variables declared with %TYPE where the source is a database column defined as NOT NULL. A NOT NULL constraint from a database table is not automatically transferred to a variable.



43   Programmer-Defined Subtypes

There are two kinds of subtypes, constrained and unconstrained

Constrained subtype

SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1 .. 2147483647;



 



Unconstrained subtype

SUBTYPE FLOAT IS NUMBER;

SUBTYPE big_string IS VARCHAR2(32767);



 



In other words, an unconstrained subtype provides an alias or alternate name for the original datatype



 



44   The VARCHAR2 Datatype

you must also specify a maximum length for the string, which can range from 1 to 32767 bytes.



 



If you need to work with strings greater than 4000 bytes in length (or 2000 prior to Oracle8i Database), consider storing those strings in CLOB (character large object) columns



 



feature_name VARCHAR2(100 BYTE); -- 100 byte string

emp_name VARCHAR2(30 CHAR); -- 30 character string



 



 If you leave off the BYTE or CHAR qualifier, the results will depend on the setting of the NLS_LENGTH_SEMANTICS parameter.



 





 



45   Working with Strings

If you want to embed a single quote within a string constant, you can do so by typing the single quote twice:

    'Aren''t you glad you''re learning PL/SQL?'



 



Another way to embed single quotes within string constants is to specify your own delimiter. Do this using the q prefix (uppercase Q may also be specified). For example:

    q'!Aren't you glad you're learning PL/SQL?!'



 



or:

    q'{Aren't you glad you're learning PL/SQL?}'



 



you may find yourself faced with the need to explicitly specify a string constant to be represented in the national character set. You can do so using the n prefix:

    n'This string will be represented using the national character set.'



 



If you need a string in the national character set, and you also want to specify some characters by their Unicode code point, you can use the u prefix:

    u'Thi/0073 /0073tring will be repre/0073ented u/0073ing ...'



 



45.1Using Nonprintable Characters

    SELECT name || chr(10)

           || address1 || chr(10)

           || address2 || chr(10)

           || address3 || chr(10)

           || address4 || chr(10)

           || city || ', ' || state || ' ' || zipcode

           AS company_address

    FROM company;



 



46   Concatenating Strings

CONCAT (string1, string2)



 



CONCAT always appends string2 to the end of string1 

  





    CONCAT ('abc', 'defg') --> 'abcdefg'

    CONCAT (NULL, 'def') --> 'def'

    CONCAT ('ab', NULL) --> 'ab'

    CONCAT (NULL, NULL) --> NULL



 



x := 'abc' || 'def' || 'ghi';

You can see that the || operator not only is much easier to use than CONCAT, but also results in much more readable code.

47   Trimming

What LPAD and RPAD giveth, LTRIM and RTRIM taketh away. For example:

    DECLARE

       a VARCHAR2(40) := 'This sentence has too many periods......';

       b VARCHAR2(40) := 'The number 1';

    BEGIN

       DBMS_OUTPUT.PUT_LINE(   RTRIM(a,'.')   );

       DBMS_OUTPUT.PUT_LINE(

          LTRIM(b,'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz')

       );

    END;



 



And the output is:

    This sentence has too many periods

    1



 



    DECLARE

       x VARCHAR2(30) := '.....Hi there!.....';

    BEGIN

       DBMS_OUTPUT.PUT_LINE(   TRIM(LEADING '.' FROM x)   );

       DBMS_OUTPUT.PUT_LINE(   TRIM(TRAILING '.' FROM x)   );

       DBMS_OUTPUT.PUT_LINE(   TRIM(BOTH '.' FROM x)   );



 



       --The default is to trim from both sides

       DBMS_OUTPUT.PUT_LINE(   TRIM('.' FROM x)   );



 



       --The default trim character is the space:

       DBMS_OUTPUT.PUT_LINE(   TRIM(x)   );

    END;



 



The output is:

    Hi there!.....

    .....Hi there!

    Hi there!

    Hi there!

    .....Hi there!.....



 



Regular Expression Searching

comma_delimited := REGEXP_LIKE(names,'^([a-z A-Z]*,)+([a-z A-Z]*){1}$');

j_location := REGEXP_INSTR(names, 'A[^,]*[^aeiou],|A[^,]*[^aeiou]$')

             REGEXP_SUBSTR(names,

                              'A[^,]*[^aeiou],|A[^,]*[^aeiou]$',

                              1, name_counter);



 





 



48   Working with Empty Stings

The following code demonstrates Oracle's behavior:

    /* File on web: empty_is_null.tst */

    DECLARE

       empty_varchar2 VARCHAR2(10) := '';

       empty_char CHAR(10) := '';

    BEGIN

       IF empty_varchar2 IS NULL THEN

          DBMS_OUTPUT.PUT_LINE('empty_varchar2 is NULL');

       END IF;



 



       IF '' IS NULL THEN

          DBMS_OUTPUT.PUT_LINE(''''' is NULL');

       END IF;



 



       IF empty_char IS NULL THEN

          DBMS_OUTPUT.PUT_LINE('empty_char is NULL');

       END IF;

    END;



 



The output is:

    empty_varchar2 is NULL

    '' is NULL



 





 



    DECLARE

       user_entered_name VARCHAR2(30);

       name_from_database VARCHAR2(30);

       ...

    BEGIN

    ...

    IF user_entered_name <> name_from_database THEN

    ...



 



If the user entered an empty string instead of a name, the IF condition shown in this example would never be TRUE. That's because a NULL is never not-equal, nor equal, to any other value. One alternative approach to this IF statement is the following:



 



49   Variable-to-database conversion

When you INSERT or UPDATE a CHAR variable into a VARCHAR2 database column, the SQL kernel does not trim the trailing blanks before performing the change. When the following PL/SQL is executed, the company_name in the new database record is set to "ACME SHOWERS········" (where · indicates a space). It is, in other words, padded out to 20 characters, even though the default value was a string of only 12 characters.

    DECLARE

       comp_id#   NUMBER;

       comp_name  CHAR(20) := 'ACME SHOWERS';

    BEGIN

       SELECT company_id_seq.NEXTVAL

          INTO comp_id#

          FROM dual;

       INSERT INTO company (company_id, company_name)

          VALUES (comp_id#, comp_name);

    END;



 



50   String comparisons

    DECLARE

       company_name CHAR(30)   := 'Feuerstein and Friends';

       char_parent_company_name CHAR(35)  := 'Feuerstein and Friends';

       varchar2_parent_company_name VARCHAR2(35)  := 'Feuerstein and Friends';

    BEGIN

       --Compare two CHARs, so blank-padding is used

       IF company_name = char_parent_company_name THEN

          DBMS_OUTPUT.PUT_LINE ('first comparison is TRUE');

       ELSE

          DBMS_OUTPUT.PUT_LINE ('first comparison is FALSE');

       END IF;



 



       --Compare a CHAR and a VARCHAR2, so nonblank-padding is used

       IF company_name = varchar2_parent_company_name THEN

          DBMS_OUTPUT.PUT_LINE ('second comparison is TRUE');

       ELSE

          DBMS_OUTPUT.PUT_LINE ('second comparison is FALSE');

       END IF;

    END;



 



The output is:

    first comparison is TRUE

    second comparison is FALSE



 



The first comparison is between two CHAR values, so blank-padding is used: PL/SQL blank-pads the shorter of the two values out to the length of the longer value. It then performs the comparison. In this example, PL/SQL adds five spaces to the end of the value in company_name and then performs the comparison between company_name and char_parent_company_name. The result is that both strings are considered equal. Note that PL/SQL does not actually change the company_name variable's value. It copies the value to another memory structure and then modifies this temporary data for the comparison.

The second comparison involves a VARCHAR2 value, so PL/SQL performs a non-blank-padding comparison. It makes no changes to any of the values, uses the existing lengths, and performs the comparison. In this case, the first 22 characters of both strings are the same, "Feuerstein and Friends", but the fixed-length company_name is padded with eight space characters, whereas the variable-length varchar2_company_name is not. Because one string has trailing blanks and the other does not, the two strings are not considered equal.

51   String Function

GREATEST(string1, string2, ...)

Takes one or more strings as input, and returns the string that would come last (i.e., that is the greatest) if the inputs were sorted in ascending order. Also see the LEAST function , which is the opposite of GREATEST.



 



LEAST(string1, string2, ...)

Takes one or more strings as input and returns the string that would come first (i.e., that is the least) if the inputs were sorted in ascending order. Also see GREATEST, which is the opposite of LEAST.

TRANSLATE (string1, search_set, replace_set)

Replaces every instance in string1 of a character from search_set with the corresponding character from replace_set. For example:

    TRANSLATE ('abcd', 'ab', '12') --> '12cd'



 



TRIM (FROM string1)

Returns a version of string1 that omits any leading and trailing spaces. Variations include:



 



TRIM(LEADING FROM ...)

Trims only leading spaces.



 



TRIM(TRAILING FROM ...)

Trims only trailing spaces.



 



TRIM(BOTH FROM ...)

Explicitly specifies the default behavior of trimming both leading and trailing spaces.



 



TRIM(...trim_character FROM string1)

Removes occurrences of trim_character, which may be any one character that you want to specify.



 



52   Number





 



52.1The PLS_INTEGER Type

The PLS_INTEGER datatype is available in PL/SQL Release 2.3 (Oracle7 Database Release 3) and above and stores signed integers in the range -2,147,483,647 through 2,147,483,647. Values are represented using your hardware platform's native integer format.



 



The PLS_INTEGER datatype was designed for speed



 



The BINARY_INTEGER Type

The semantics of this datatype have changed from Oracle9i Database Release 2 to Oracle Database 10g Release 1. Beginning in Oracle Database 10g Release 1, BINARY_INTEGER is equivalent to PLS_INTEGER



 



53   TO_CHAR

SELECT TO_CHAR(123456789.01,'L999G999G999D99') FROM DUAL; 

  







 



Result:

          $123,456,789.01



 



SELECT TO_CHAR(0,'LB000G000G009D99') FROM DUAL;



 



Result: N/A



 



SELECT TO_CHAR (SYSDATE, 'DY') FROM DUAL;

Result: MON



 



SELECT TO_CHAR (SYSDATE, 'DAY') FROM DUAL;

Result: MONDAY



 



54   Using CAST

CAST (expression AS datatype)



 



    DECLARE

       a NUMBER := -123.45;

       a1 VARCHAR2(30);

       b VARCHAR2(30) := '-123.45';

       b1 NUMBER;

       b2 BINARY_FLOAT;

       b3 BINARY_DOUBLE;

    BEGIN

       a1 := CAST (a AS VARCHAR2);

       b1 := CAST (b AS NUMBER);

       b2 := CAST (b AS BINARY_FLOAT);

       b3 := CAST (b AS BINARY_DOUBLE);

       DBMS_OUTPUT.PUT_LINE(a1);

       DBMS_OUTPUT.PUT_LINE(b1);

       DBMS_OUTPUT.PUT_LINE(b2);

       DBMS_OUTPUT.PUT_LINE(b3);

    END;



 



The output is:

    -123.45

    -123.45

    -1.23449997E+002

    -1.2345E+002



 



55   Rounding and Truncation Functions


        
        
 
Function
 
 
Summary
 
 
CEIL
 
 
Returns the smallest integer that is greater than or equal to the specified value. This integer is the "ceiling" over your value.
 
 
FLOOR
 
 
Returns the largest integer that is less than or equal to the specified value. This integer is the "floor" under your value.
 
 
ROUND
 
 
Performs rounding on a number. You can round with a positive number of decimal places (the number of digits to the right of the decimal point) and also with a negative number of decimal places (the number of digits to the right of the decimal point).
 
 
TRUNC
 
 
            
 
             
Truncates a number to the specified number of decimal places. TRUNC simply discards all values beyond the decimal places provided in the call.
 
            
 



 





 







 





 



56   Datetime Datatypes

DATE

Stores a date and time, resolved to the second. Does not include time zone.

TIMESTAMP

Stores date and time without respect to time zone. Except for being able to resolve time to the billionth of a second, TIMESTAMP is the equivalent of DATE

TIMESTAMP WITH TIME ZONE

Stores the time zone along with each date and time value

TIMESTAMP WITH LOCAL TIME ZONE

Stores a date and time that is assumed to be in the local time zone. For database columns of this type, the "local" time zone is the database time zone. For PL/SQL variables of this type, the "local" time zone is the session time zone. Values that move between database columns and PL/SQL variables are converted from one time zone to the other.





 





 



Table 4


        
        
 
Function
 
 
Time zone
 
 
Datatype returned
 
 
CURRENT_DATE
 
 
Session
 
 
DATE
 
 
CURRENT_TIMESTAMP
 
 
Session
 
 
TIMESTAMP WITH TIME ZONE
 
 
LOCALTIMESTAMP
 
 
Session
 
 
TIMESTAMP
 
 
SYSDATE
 
 
Server
 
 
DATE
 
 
SYSTIMESTAMP
 
 
Server
 
 
TIMESTAMP WITH TIME ZONE
 



 



57   Interval Datatypes

Oracle supports two interval datatypes

INTERVAL YEAR TO MONTH

Allows you to define an interval of time in terms of years and months.

INTERVAL DAY TO SECOND

Allows you to define an interval of time in terms of days, hours, minutes, and seconds (including fractional seconds).



 



Why Two INTERVAL Datatypes?

The reason for having two INTERVAL types with a dividing line at the month level is that months are the only datetime component for which the length of time in question varies. Think about having an interval of 1 month and 30 days. How long is that, really? Is it less than two months? The same as two months? More than two months? If the one month is January, then 30 days gets you past February and into March, resulting in a 61-day interval that is a bit more than "two months" long. If the one month is February, then the interval is exactly two months (but only 59 or 60 days). If the one month is April, then the interval is slightly less than two months, for a total of 60 days.



 



Declaration

    var_name INTERVAL YEAR [(year_precision)] TO MONTH

or:

    var_name INTERVAL DAY [(day_precision)] TO SECOND [(frac_sec_prec)]



 





 



    /* File on web: interval_between.sql */

    DECLARE

       start_date TIMESTAMP;

       end_date TIMESTAMP;

       service_interval INTERVAL YEAR TO MONTH;

       years_of_service NUMBER;

       months_of_service NUMBER;

    BEGIN

       --Normally, we would retrieve start and end dates from a database.

       start_date := TO_TIMESTAMP('20-NOV-2005','dd-mon-yyyy');

       end_date := TO_TIMESTAMP ('26-DEC-2006','dd-mon-yyyy');



 



       --Determine and display years and months of service:

       service_interval := (end_date - start_date) YEAR TO MONTH;

       DBMS_OUTPUT.PUT_LINE(service_interval);



 



       --Use the new EXTRACT function to grab individual

       --year and month components.

       years_of_service := EXTRACT(YEAR FROM service_interval);

       months_of_service := EXTRACT(MONTH FROM service_interval);

       DBMS_OUTPUT.PUT_LINE(years_of_service || ' years and '

                            || months_of_service || ' months');

    END;



 



Result:

+01-01 
  
1 years and 1 months 

  


  


    DECLARE

       A INTERVAL YEAR TO MONTH;

    BEGIN

       A := INTERVAL '40-3' YEAR TO MONTH;



 



       DBMS_OUTPUT.PUT_LINE(

          EXTRACT(YEAR FROM A) || ' Years and '

          || EXTRACT(MONTH FROM A) || ' Months'

       );

    END;



 



The output is:

    40 Years and 3 Months



 



SELECT INTERVAL '10-3' YEAR TO MONTH FROM DUAL;



 



58   CAST and EXTRACT

    DECLARE

       a TIMESTAMP WITH TIME ZONE;

       b VARCHAR2(40);

       c TIMESTAMP WITH LOCAL TIME ZONE;

    BEGIN

       a := CAST ('24-Feb-2002 09.00.00.00 PM US/Eastern'

                  AS TIMESTAMP WITH TIME ZONE);

       b := CAST (a AS VARCHAR2);

       c := CAST (a AS TIMESTAMP WITH LOCAL TIME ZONE);



 



       DBMS_OUTPUT.PUT_LINE(a);

       DBMS_OUTPUT.PUT_LINE(b);

       DBMS_OUTPUT.PUT_LINE(c);

    END;

The output is:

    24-FEB-02 09.00.00.000000 PM US/EASTERN

    24-FEB-02 09.00.00.000000 PM US/EASTERN

    24-FEB-02 09.00.00.000000 PM



 



EXTRACT (component_name, FROM {datetime | interval})

    BEGIN

       IF EXTRACT (MONTH FROM SYSDATE) = 11 THEN

          DBMS_OUTPUT.PUT_LINE('It is November');

       ELSE

          DBMS_OUTPUT.PUT_LINE('It is not November');

       END IF;

    END;



 



Table 5


        
        
 
Name
 
 
Description
 
 
ADD_MONTHS
 
 
Adds a specified number of months to a DATE. See the section "Adding and Subtracting Intervals to/from Datetimes" for an example.
 
 
CAST
 
 
Converts between datatypesfor example, between DATE and the various TIMESTAMP datatypes. See the section "CAST and EXTRACT."
 
 
EXTRACT
 
 
Returns the value of a specific datetime element, such as the hour, the minute, the day, the year, etc. See the section "CAST and EXTRACT."
 
 
LAST_DAY
 
 
Returns the last day in the month containing a specified input DATE.
 
 
MONTHS_ BETWEEN
 
 
Calculates the number of months between two DATEs. See the section"Computing the Interval Between Two Datetimes" for an example.
 
 
NEXT_DAY
 
 
Returns the DATE of the first weekday specified that is later than an input DATE.
 
 
NUMTOYMINTERAL
 
 
Converts a number representing some number of years or months (your choice) to a value of type INTERVAL YEAR TO MONTH.
 
 
ROUND
 
 
Returns a DATE rounded to a specified level of granularity.
 



 





 



59   Records in PL/SQL

    DECLARE

       my_book   books%ROWTYPE;

    BEGIN

       SELECT *

         INTO my_book

         FROM books

        WHERE title = 'Oracle PL/SQL Programming, 4th Edition';

     

       IF my_book.author LIKE '%Feuerstein%'

       THEN

          DBMS_OUTPUT.put_line ('Our newest ISBN is ' || my_book.isbn);

       END IF;

    END;



 



Create corresponding cursors and records

    DECLARE

       CURSOR my_books_cur IS

          SELECT * FROM books

           WHERE author LIKE '%FEUERSTEIN%';



 



       one_SF_book my_books_cur%ROWTYPE;



 



Create table-based records

    DECLARE

       one_book books%ROWTYPE;



 



Pass records as parameters

    DECLARE

       TYPE book_info_rt IS RECORD (

          author books.author%TYPE,

          category VARCHAR2(100),

          total_page_count POSITIVE);



 



       steven_as_author book_info_rt;



 



    DECLARE

       TYPE book_rc IS REF CURSOR RETURN books%ROWTYPE;

       book_cv book_rc;



 



       one_book book_cv%ROWTYPE;

    BEGIN



 





 



Declaring programmer-defined record TYPEs



 



The datatype of a record's field can be any of the following:

Hardcoded, scalar datatype (VARCHAR2, NUMBER, etc.).

Programmer-defined SUBTYPE.

Anchored declarations using %TYPE or %ROWTYPE attributes. In the latter case, we have created a nested record one record inside another.

PL/SQL collection type; a field in a record can be a list or even a collection.

REF CURSOR, in which case the field contains a cursor variable.



 



Declaring record

    prev_customer_sales_rec        customer_sales_rectype;

    top_customer_rec                       customer_sales_rectype;



 



60   Examples of programmer-defined record declarations

    DECLARE SUBTYPE long_line_type IS VARCHAR2(2000);



 



    CURSOR company_sales_cur IS

       SELECT name, SUM (order_amount) total_sales

         FROM company c, order o

        WHERE c.company_id = o.company_id;



 



    TYPE employee_ids_tabletype IS

       TABLE OF employee.employee_id%TYPE

       INDEX BY BINARY_INTEGER;



 



    TYPE mishmash_rectype IS RECORD

       (emp_number NUMBER(10) NOT NULL,

        paragraph_text long_line_type,

        company_nm company.name%TYPE,

        total_sales company_sales.total_sales%TYPE := 0,

        new_hires_tab employee_ids_tabletype,

        prefers_nonsmoking_fl BOOLEAN := FALSE,

        new_company_rec company_rectype

       );

  


Field-level operations with nested records

    DECLARE

       TYPE phone_rectype IS RECORD

          (intl_prefix   VARCHAR2(2),

           area_code     VARCHAR2(3),

           exchange      VARCHAR2(3),

           phn_number    VARCHAR2(4),

           extension     VARCHAR2(4)

          );



 



       -- Each field is a nested record...

      TYPE contact_set_rectype IS RECORD

          (day_phone#    phone_rectype,

           eve_phone#    phone_rectype,

           fax_phone#    phone_rectype,

           home_phone#   phone_rectype,

           cell_phone#   phone_rectype

          );

     

       auth_rep_info_rec contact_set_rectype;

    BEGIN



 



Reference:

    auth_rep_info_rec.fax_phone#.area_code :=

       auth_rep_info_rec.home_phone#.area_code;



 



61   Trigger Pseudo-Records

When you are writing code inside database triggers for a particular table, Oracle makes available to you two structures, OLD and NEW, which are pseudo-records . These structures have the same format as table-based records declared with %ROWTYPE: they have a field for every column in the table:



 



    CREATE OR REPLACE TRIGGER check_raise  AFTER UPDATE OF salary ON employee  FOR EACH ROW

    WHEN  (OLD.salary != NEW.salary) OR

          (OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR

          (OLD.salary IS NOT NULL AND NEW.salary IS NULL)

    BEGIN

       IF :NEW.salary > 100000 THEN ...



 





 



62   Collections

Keep track of lists 

  





Emulate bidirectional or random-access cursors 

  





Improve performance of lookups

Cache database information 

  







 



Nested table multiset operations

Oracle Database 10g Release 1 added a number of MULTISET operators that give us the ability to manipulate the contents of nested tables as sets (union, intersection, minus, etc.). You can also compare two nested tables for equality.



 



Associative arrays

These are single-dimensional, unbounded, sparse collections of homogeneous elements that are available only in PL/SQL 

  





Nested tables 

  





They are initially dense but can become sparse and unbounded through deletions.

VARRAYs

Like the other two collection types, VARRAYs (variable-sized arrays) are also single-dimensional collections of homogeneous elements. However, they are always bounded and never sparse.



 



Regardless of the particular type or usage, however, a collection is at its core a single-dimensional list of homogeneous elements.



 



Indexed by integers

All collections support the ability to reference a row via the row number, an integer value



 



Indexed by strings

Starting with Oracle9i Database Release 2, it is possible to index an associative array by string values (currently up to 32K in length) instead of by numeric row numbers. This feature is not available for nested tables or VARRAYs.



 



any of the following INDEX BY clauses are now accepted:

    INDEX BY BINARY_INTEGER;

    INDEX BY PLS_INTEGER;

    INDEX BY POSITIVE;

    INDEX BY NATURAL;

    INDEX BY SIGNTYPE;

    INDEX BY VARCHAR2(32767);

    INDEX BY table.column%TYPE;

    INDEX BY cursor.column%TYPE;

    INDEX BY package.variable%TYPE;

    INDEX BY package.subtype;



 



Associative array example

      1  DECLARE

      2     TYPE list_of_names_t IS TABLE OF person.first_name%TYPE

      3        INDEX BY PLS_INTEGER;

      4     happyfamily   list_of_names_t;

      5     l_row PLS_INTEGER;

      6  BEGIN

      7     happyfamily (2020202020) := 'Eli';

      8     happyfamily (-15070) := 'Steven';

      9     happyfamily (-90900) := 'Chris';

     10     happyfamily (88) := 'Veva';

     11

     12     l_row := happyfamily.FIRST;

     13

     14     WHILE (l_row IS NOT NULL)

     15     
         
         
          
          LOOP
         
         

     16        DBMS_OUTPUT.put_line (happyfamily (l_row));

     17        l_row := happyfamily.NEXT (l_row);

     18     END 
         
         
          
          LOOP
         
         ;

     19* END;

    SQL> /

    Chris

    Steven

    Veva

    Eli



 



Nested table example

    REM Section A

    SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);

      2  /

    Type created.

     

    REM Section B

    SQL>

      1  DECLARE

      2     happyfamily   list_of_names_t := list_of_names_t (  );

      3     children      list_of_names_t := list_of_names_t (  );

      4     parents       list_of_names_t := list_of_names_t (  );

      5  BEGIN

      6     happyfamily.EXTEND (4);

      7     happyfamily (1) := 'Eli';

      8     happyfamily (2) := 'Steven';

      9     happyfamily (3) := 'Chris';

     10     happyfamily (4) := 'Veva';

     11

     12     children.EXTEND;

     13     children (1) := 'Chris';

     14     children.EXTEND;

     15     children (2) := 'Eli';

     16

     17     parents := happyfamily MULTISET EXCEPT children;

     18

     19     FOR l_row IN parents.FIRST .. parents.LAST

     20     
         
         
          
          LOOP
         
         

     21        DBMS_OUTPUT.put_line (parents (l_row));

     22     END 
         
         
          
          LOOP
         
         ;

     23* END;

    SQL> /

    Steven

    Veva



 



VARRAY example

    REM Section A

    SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);

      2  /

    Type created.

     

    SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);

      2  /

    Type created.

     

    REM Section B

    SQL> CREATE TABLE family (

      2     surname VARCHAR2(1000)

      3   , parent_names first_names_t

      4   , children_names child_names_t

      5   );

     

    Table created.

     

    REM Section C

    SQL>

      1  DECLARE

      2     parents    first_names_t := first_names_t (  );

      3     children   child_names_t := child_names_t (  );

      4  BEGIN

      5     parents.EXTEND (2);

      6     parents (1) := 'Samuel';

      7     parents (2) := 'Charina';

      8     --

      9     children.EXTEND;

     10     children (1) := 'Feather';

     11

     12     --

     13     INSERT INTO family

     14                 (surname, parent_names, children_names

     15                 )

     16          VALUES ('Assurty', parents, children

     17                 );

     18  END;

    SQL> /

     

    PL/SQL procedure successfully completed.

     

    SQL> SELECT * FROM family

      2  /

     

    SURNAME

    PARENT_NAMES

    CHILDREN_NAMES

    --------------------------------------------

    Assurty

    FIRST_NAMES_T('Samuel', 'Charina')

    CHILD_NAMES_T('Feather')



 



Collections as datatypes of a function's return value

    CREATE FUNCTION true_colors (whose_id IN NUMBER) RETURN Color_tab_t

    AS

       l_colors Color_tab_t;

    BEGIN

       SELECT favorite_colors INTO l_colors

         FROM personality_inventory

        WHERE person_id = whose_id;

       RETURN l_colors;

    EXCEPTION

       WHEN NO_DATA_FOUND

       THEN

          RETURN NULL;

    END;



 



Option #1 is easy. Notice, by the way, that this is another circumstance where you don't have to initialize the collection variable explicitly:

    DECLARE

       color_array Color_tab_t;

    BEGIN

       color_array := true_colors (8041);

    END;



 



With Option #2, we actually give the function call a subscript. The general form is:

    variable_of_element_type := function() (subscript);



 



Or, in the case of the true_colors function:

    DECLARE

       one_of_my_favorite_colors VARCHAR2(30);

    BEGIN

       one_of_my_favorite_colors := true_colors (whose_id=>8041) (1);

    END;



 



As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct. Why is this? They involve the least amount of coding. You don't have to initialize or extend them. They have historically been the most efficient collection type (although this distinction will probably fade over time). However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY?



 




        
        
 
Characteristic
 
 
Associative array
 
 
Nested table
 
 
VARRAY
 
 
Dimensionality
 
 
Single
 
 
Single
 
 
Single
 
 
Usable in SQL?
 
 
No
 
 
Yes
 
 
Yes
 
 
Usable as column datatype in a table?
 
 
No
 
 
Yes; data stored "out of line" (in separate table)
 
 
Yes; data stored "in line" (in same table)
 
 
Uninitialized state
 
 
Empty (cannot be null); elements undefined
 
 
Atomically null; illegal to reference elements
 
 
Atomically null; illegal to reference elements
 
 
Initialization
 
 
Automatic, when declared
 
 
Via constructor, fetch, assignment
 
 
Via constructor, fetch, assignment
 
 
In PL/SQL, elements
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
referenced via
 
 
BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647)
 
VARCHAR2 (Oracle9i Database Release 2 and above)
 
 
Positive integer between 1 and 2,147,483,647
 
 
Positive integer between 1 and 2,147,483,647
 
 
Sparse?
 
 
Yes
 
 
Initially, no; after deletions, yes
 
 
No
 
 
Bounded?
 
 
No
 
 
Can be extended
 
 
Yes
 
 
Can assign value to any
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
element at any time?
 
 
Yes
 
 
No; may need to EXTEND first
 
 
No; may need to EXTEND first, and cannot EXTEND past upper bound
 
 
Means of extending
 
 
Assign value to element with a new subscript
 
 
Use built-in EXTEND procedure (or TRIM to condense), with no predefined
 
 
 
 
 
 
 
maximum
 
 
EXTEND (or TRIM), but only up to declared maximum size
 
 
 
 
 
 
 
 
 
 
 
 
Can be compared for equality?
 
 
No
 
 
Yes, in Oracle Database 10g
 
 
No
 
 
Can be manipulated with set operators
 
 
No
 
 
Yes, in Oracle Database 10g
 
 
No
 
 
Retains ordering and subscripts when stored in and retrieved from database?
 
 
N/A
 
 
No
 
 
Yes
 



 



Operation



 




        
        
 
Limit
 
 
Suitable: varray
 
Exception: COLLECTION_IS_NULL
 
 
Prior, Next
 
 
Suitable: nested table, associative arrays, varray
 
Exception: COLLECTION_IS_NULL
 
Note: Applied to collections that have no elements, it returns NULL
 
 
Trim
 
 
Suitable: nested table, varray
 
Exception: SUBSCRIPT_BEYOND_COUNT, COLLECTION_IS_NULL
 
 
Count
 
 
Suitable: associative array, nested table, varray
 
Exception: COLLECTION_IS_NULL
 
 
Delete
 
 
Suitable: associative array, nested table, varray
 
Exception: COLLECTION_IS_NULL
 
Note: delete without argument removes all of the elements of collection.
 
 
Exists
 
 
Suitable: nested tables, associative array, varray
 
Exception: VALUE_ERROR
 
Note: always return TRUE/FALSE
 
 
Extend
 
 
Suitable: nested table, varray
 
Exception: COLLECTION_IS_NULL, SUBSCRIPT_BEYOND_LIMIT
 
 
First, Last
 
 
Suitable: nested table, associative array, varray
 
Exception: COLLECTION_IS_NULL (uninitialized)
 



 





 



Declaring Collection Types

There are two ways to create user-defined collection types :

You can declare the collection type within a PL/SQL program within BLOCK using the TYPE statement

You can define a nested table type or VARRAY type as a schema-level object within the Oracle database by using the CREATE TYPE command .



 



Declaring a nested table or VARRAY

    CREATE [ OR REPLACE ] TYPE type_name AS | IS

       TABLE OF element_datatype [ NOT NULL ];



 



    CREATE [ OR REPLACE ] TYPE type_name AS | IS

       VARRAY (max_elements) OF element_datatype [ NOT NULL ];



 



    CREATE TYPE list_t AS VARRAY(10) OF VARCHAR2(80);

    /

    ALTER TYPE list_t MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;

    /



 



Aggregate assignments

      1  DECLARE

      2     TYPE name_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;

      3     old_names name_t;

      4     new_names name_t;

      5  BEGIN

      6     /* Assign values to old_names table */

      7     old_names(1) := 'Smith';

      8     old_names(2) := '
         
         
          
          Harrison
         
         ';

      9

     10     /* Assign values to new_names table */

     11     new_names(111) := 'Hanrahan';

     12     new_names(342) := 'Blimey';

     13

     14     /* Transfer values from new to old */

     15     old_names := new_names;

     16

     17     /* This statement will display 'Hanrahan' */

     18     DBMS_OUTPUT.PUT_LINE (

     19       old_names.FIRST || ': ' || old_names(old_names.FIRST));

     20* END;

    SQL> /

    111: Hanrahan



 



A collection-level assignment completely replaces the previously defined rows in the collection. In the preceding example, rows 1 and 2 in old_names are defined before the last, aggregate assignment.

After the assignment, only rows 111 and 342 in the old_names collection have values



 



Assigning rows from a relational table

    DECLARE

       TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;

       l_emps emp_copy_t := emp_copy_t(  );

    BEGIN

       l_emps.EXTEND;

       SELECT *

         INTO l_emps (1)

         FROM emp

        WHERE empno = 7521;

    END;



 





 



    DECLARE

       TYPE emp_copy_t IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;

       l_emps emp_copy_t;

    BEGIN

       FOR emp_rec IN (SELECT * FROM emp)

       
         
         
          
          LOOP
         
         

[3]          l_emps (emp_rec.empno) := emp_rec;

       END 
         
         
          
          LOOP
         
         ;

    END;



 



    DECLARE

       TYPE emp_copy_nt IS TABLE OF emp%ROWTYPE;

       l_emps emp_copy_nt;

    BEGIN

       SELECT * BULK COLLECT INTO l_emps FROM emp;

    END;



 



Here are some final notes on the collection caching technique:

This technique is a classic tradeoff between CPU and memory. Each session has its own copy of the collection (this is program data and is stored in the PGA). If you have 10,000 users, the total memory required for these 10,000 small caches could be considerable.

Consider using this technique with any of the following scenarios: small, static tables in a multiuser application; large, static tables in which a given user will access only a small portion of the table; manipulation of large tables in a batch process (just a single connect taking up possibly a lot of memory).



 



Well, if the data isn't changing especially during a user session then why would I want to keep querying the information from the database? Even if the data is cached in the System Global Area ([4]SGA) , I still need to visit the SGA, confirm that the query has already been parsed, find that information in the data buffers and finally return it to the session program area (the Program Global Area, or P[5]GA).



 



Unnamed nested collections: multidimensional arrays

    /* Files on web: multdim.pkg, multdim.tst, multdim2.pkg */

    CREATE OR REPLACE PACKAGE multdim

    IS

       TYPE dimX_t IS TABLE OF VARCHAR2 (32767) INDEX BY PLS_INTEGER;

       TYPE dimY_t IS TABLE OF dimX_t INDEX BY PLS_INTEGER;

       TYPE dimZ_t IS TABLE OF dimY_t INDEX BY PLS_INTEGER;

     

    PROCEDURE setcell (

       array_in   IN OUT   dimZ_t,

       dimX_in         PLS_INTEGER,

       dimY_in         PLS_INTEGER,

       dimZ_in         PLS_INTEGER,

       value_in   IN   VARCHAR2

    )

    IS

    BEGIN

       array_in(dimZ_in )(dimY_in )(dimX_in) := value_in;

    END;

     

   FUNCTION getcell (

       array_in   IN   dimZ_t,

       dimX_in         PLS_INTEGER,

       dimY_in         PLS_INTEGER,

       dimZ_in         PLS_INTEGER

    )

       RETURN VARCHAR2

    IS

    BEGIN

       RETURN array_in(dimZ_in )(dimY_in )(dimX_in);

    END;



 



    FUNCTION EXISTS (

       array_in   IN   dimZ_t,

       dimX_in         PLS_INTEGER,

       dimY_in         PLS_INTEGER,

       dimZ_in         PLS_INTEGER

     )

       RETURN BOOLEAN

    IS

       l_value varchar2(32767);

    BEGIN

        l_value := array_in(dimZ_in )(dimY_in )(dimX_in);

        RETURN TRUE;

    EXCEPTION

       WHEN NO_DATA_FOUND

       THEN

          RETURN FALSE;

    END;



 



Working with Collections in SQL

CAST

Maps a collection of one type to a collection of another type. This can encompass mapping a VARRAY to a nested table.

MULTISET

Maps a database table to a collection. With MULTISET and CAST, you can actually retrieve rows from a database table as a collection-typed column.

TABLE

Maps a collection to a database table. This is the inverse of MULTISET.



 



The UROWID and ROWID Datatypes

Enter the UROWID datatype . The U in UROWID stands for Universal, and a UROWID variable can contain any type of rowid from any type of table.



 



We recommend the use of UROWID for all new development involving rowids. The ROWID type provides backward compatibility but can't accommodate all types of rowids now encountered in an Oracle database. UROWID is safer because it accommodates any type of rowid.



 



Getting at Rowids

    DECLARE

       employee_rowid UROWID;

       employee_salary NUMBER;

    BEGIN

       --Retrieve employee information that we might want to modify

       SELECT rowid, salary INTO employee_rowid, employee_salary

       FROM employee

       WHERE last_name='Grubbs' AND first_name='John';

    END;



 



[6]An often better way to achieve the same effect as using ROWID in an UPDATE or DELETE statement is to use an explicit cursor to retrieve data, and then use the WHERE CURRENT OF CURSOR clause to modify or delete it. See Chapter 15 for detailed information on this technique.



 



Do rowids ever change?

Only if you delete a row and reinsert itas you might when doing an export followed by an importwill you get a new rowid for a row.



 



63   The LOB Datatypes

LOB: --- large object datatypes



 



BFILE:

Binary file. Declares a variable that holds a file locator pointing to an operating-system file outside the database. Oracle treats the data in the file as binary data.



 



BLOB

Binary large object. stored inside the database 

    

  






 



CLOB

Character large object, stored inside the database 

  







 



NCLOB

National Language Support (NLS) character large object, stored inside the database.



 



[7]LOBs can be categorized as internal or external. Internal LOBs (BLOBs, CLOBs, and NCLOBs) are stored in the database and can participate in a transaction in the database server. External LOBs cannot participate in transactions; in other words, you cannot commit or roll back changes to a BFILE. Instead, you must rely on the underlying filesystem for data integrity.



 



64   Working with LOBs

    CREATE TABLE waterfalls (

       falls_name VARCHAR2(80),

       falls_photo BLOB,

       falls_directions CLOB,

       falls_description NCLOB,

       falls_web_page BFILE);



 



Understanding LOB Locators

Fundamental to working with LOBs is the concept of a LOB locator. A LOB locator is a pointer to large object data in a database.



 



to retrieve the binary photo data from the falls_photo BLOB column used in the previous example, you would go through the following steps:

Issue a SELECT statement to retrieve the LOB locator for the photo you wish to display.

Open the LOB via a call to DBMS_LOB.OPEN.

Make a call to DBMS_LOB.GETCHUNKSIZE to get the optimal chunk size to use when reading (and writing) the LOB's value.

Make a call to DBMS_LOB.GETLENGTH to get the number of bytes or characters in the LOB value.

Make multiple calls to DBMS_LOB.READ in order to retrieve the LOB data.

Close the LOB.



 



Empty Versus NULL LOBs

     INSERT INTO waterfalls

                 (falls_name,falls_directions)

          VALUES ('
         
         
          
          
           
           Munising
          
           
          
          
           
           Falls
          
          
         
         ',EMPTY_CLOB(  ));



 



Writing into a LOB

    /* File on web: munising_falls_01.sql */

    DECLARE

       directions CLOB;

       amount BINARY_INTEGER;

       offset INTEGER;

       first_direction VARCHAR2(100);

       more_directions VARCHAR2(500);

    BEGIN

       --Delete any existing rows for '
         
         
          
          
           
           Munising
          
           
          
          
           
           Falls
          
          
         
         ' so that this

       --example can be executed multiple times

       DELETE

         FROM waterfalls

        WHERE falls_name='
         
         
          
          
           
           Munising
          
           
          
          
           
           Falls
          
          
         
         ';



 



       --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator

       INSERT INTO waterfalls

                 (falls_name,falls_directions)

          VALUES ('
         
         
          
          
           
           Munising
          
           
          
          
           
           Falls
          
          
         
         ',EMPTY_CLOB(  ));



 



       --Retrieve the LOB locator created by the previous INSERT statement

       SELECT falls_directions

         INTO directions

         FROM waterfalls

        WHERE falls_name='
         
         
          
          
           
           Munising
          
           
          
          
           
           Falls
          
          
         
         ';



 



       --Open the LOB; not strictly necessary, but best to open/close LOBs.

       DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);



 



       --Use DBMS_LOB.WRITE to begin

       first_direction := 'Follow I-75 across the Mackinac Bridge.';

       amount := LENGTH(first_direction);  --number of characters to write

       offset := 1; --begin writing to the first character of the CLOB

       DBMS_LOB.WRITE(directions, amount, offset, first_direction);



 



       --Add some more directions using DBMS_LOB.WRITEAPPEND

       more_directions := ' Take US-2 west from St. Ignace to 
         
         
          
          
           
           Blaney
          
           
          
          
           
           Park
          
          
         
         .'

                       || ' Turn north on M-77 and drive to Seney.'

                       || ' From Seney, take M-28 west to Munising.';

       DBMS_LOB.WRITEAPPEND(directions,

                            LENGTH(more_directions), more_directions);



 



       --Add yet more directions

       more_directions := ' In front of the paper mill, turn right on H-58.'

                       || ' Follow H-58 to 
         
         
Washington Street
. Veer left onto'



                       || ' 
         
         
Washington Street
. You''ll find the Munising'



                       || ' Falls visitor center across from the hospital at'

                       || ' the point where 
         
         
Washington Street
becomes'



                       || ' 
         
         
Sand Point Road.
';



       DBMS_LOB.WRITEAPPEND(directions,

                            LENGTH(more_directions), more_directions);



 



       --Close the LOB, and we are done.

       DBMS_LOB.CLOSE(directions);

    END;



 



BFILEs Are Different

From within PL/SQL and Oracle in general, you can only read BFILEs. Oracle does not allow you to write BFILE data. You must generate the external filesto which BFILE locators pointcompletely outside of the Oracle database.



 



If you have the CREATE ANY DIRECTORY privilege, you can create a directory alias and grant access to it as follows:

    CREATE DIRECTORY bfile_data AS 'c:/PLSQL Book/Ch12_Misc_Datatypes';



 



    GRANT READ ON DIRECTORY bfile_data TO gennick;



 



Creating a BFILE locator

    DECLARE

       web_page BFILE;

    BEGIN

       --Delete row for 
         
         
          
          
           
           Tannery
          
           
          
          
           
           Falls
          
          
         
          so this example can

       --be executed multiple times

       DELETE FROM waterfalls WHERE falls_name='
         
         
          
          
           
           Tannery
          
           
          
          
           
           Falls
          
          
         
         ';



 



       --Invoke BFILENAME to create a BFILE locator

       web_page := BFILENAME('BFILE_DATA','Tannery Falls.htm');



 



       --Save our new locator in the waterfalls table

       INSERT INTO waterfalls (falls_name, falls_web_page)

          VALUES ('
         
         
          
          
           
           Tannery
          
           
          
          
           
           Falls
          
          
         
         ',web_page);

    END;



 



65   Transaction Management

The default behavior in a PL/SQL program is that there is one transaction per session, and all changes that you make are a part of that transaction. By using a feature called autonomous transactions , however, you can create nested transactions within the main, session-level transaction. This feature was added to PL/SQL in Oracle8i Database and is covered near the end of this chapter in the section "Autonomous Transactions."



 



65.1Implicit SQL cursor attributes


        
        
 
Name
 
 
Description
 
 
SQL%FOUND
 
 
Returns TRUE if one or more rows were modified (created, changed, removed) successfully
 
 
SQL%NOTFOUND
 
 
Returns TRUE if no rows were modified by the DML statement
 
 
SQL%ROWCOUNT
 
 
Returns number of rows modified by the DML statement
 
 
SQL%ISOPEN
 
 
            
 
             
Always returns FALSE for implicit cursors because Oracle opens and closes implicit cursors automatically
 
            
 



 



Use SQL%FOUND to determine if your DML statement affected any rows

    CREATE OR REPLACE PROCEDURE change_author_name (

       old_name_in        IN       books.author%TYPE,

       new_name_in        IN       books.author%TYPE,

       changes_made_out   OUT      BOOLEAN)

    IS

    BEGIN

       UPDATE books

          SET author = new_name_in

        WHERE author = old_name_in;



 



       changes_made_out := SQL%FOUND;

       rename_count_out := SQL%ROWCOUNT;

    END;



 



RETURNING Information

With the RETURNING clause, you can reduce network round trips, consume less server CPU time, and minimize the number of cursors opened and managed in the application



 



          UPDATE    employee

                SET salary = new_compensation (rec)

              WHERE employee_id = rec.employee_id

          RETURNING salary, last_name INTO mysal, myname;



 



The returning clause specifies the values return from DELETE, EXECUTE IMMEDIATE, INSERT, and UPDATE statements. You can retrieve the column values into individual variables or into collections. You cannot use the RETURNING clause for remote or parallel deletes. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined. 

  







  
 




66   DML and Exception Handling

    CREATE OR REPLACE PROCEDURE empty_library (

       pre_empty_count OUT PLS_INTEGER)

    IS

    BEGIN

       -- The tabCount function returns the number

       -- of rows in the specified table, using

       -- Native Dynamic SQL. See Chapter 16 for details.

       pre_empty_count := tabcount ('books');



 



       DELETE FROM books;

       RAISE NO_DATA_FOUND;

    END;



 



    DECLARE

       table_count   NUMBER := -1;

    BEGIN

       INSERT INTO books VALUES (...);

       empty_library (table_count);

    EXCEPTION

       WHEN OTHERS

       THEN

          DBMS_OUTPUT.put_line (tabcount ('books'));

          DBMS_OUTPUT.put_line (table_count);

    END;



 



The output is:

    

    -1



 



[8]Here are some things to keep in mind 

  





If your block is an autonomous transaction (described later in this chapter), then you must perform a rollback or commit (usually a rollback) when an exception is raised.



 



You can use savepoints to control the scope of a rollback. In other words, you can roll back to a particular savepoint and thereby preserve a portion of the changes made in your session. Savepoints are also explored later in this chapter



 



If an exception propagates past the outermost block (i.e., it goes "unhandled"), then in most host execution environments for PL/SQL like SQL*Plus, an unqualified rollback is automatically executed, reversing any outstanding changes



 



67   DML and Records

New to Oracle9i Database Release 2, you can now [9]use records in INSERT and UPDATE statements. Here is an example:

    CREATE OR REPLACE PROCEDURE set_book_info(book_in IN books%ROWTYPE)

    IS

    BEGIN

       INSERT INTO books VALUES book_in;

    EXCEPTION

       WHEN DUP_VAL_ON_INDEX

       THEN

          UPDATE books SET ROW = book_in

           WHERE isbn = book_in.isbn;

    END;



 



Record-based inserts

    DECLARE

       my_book books%ROWTYPE;

    BEGIN

       my_book.isbn := '1-56592-335-9';

       my_book.title := '
         
         
ORACLE PL
/SQL PROGRAMMING';



       my_book.summary := 'General user guide and reference';

       my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';

       my_book.page_count := 1000;



 



       INSERT INTO books VALUES my_book;

    END;



 



Notice that you do not include parentheses around the record specifier. If you use this format:

    INSERT INTO books VALUES (my_book); -- With parentheses, INVALID!



 



[10]You can also use a record based on a programmer-defined record TYPE to perform the INSERT, but that record type must be 100% compatible with the table %ROWTYPE definition. You may not, in other words, INSERT using a record that covers only a subset of the table's columns



 



Record-based updates

    DECLARE

       my_book books%ROWTYPE;

    BEGIN

       my_book.isbn := '1-56592-335-9';

       my_book.title := '
         
         
ORACLE PL
/SQL PROGRAMMING';



       my_book.summary := 'General user guide and reference';

       my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';

       my_book.page_count := 980; -- new page count for 3rd edition



 



       UPDATE books

          SET ROW = my_book

         WHERE isbn = my_book.isbn;

    END;



 



There are some [11]restrictions on record-based updates:

You must update an entire row with the ROW syntax. You cannot update a subset of columns (although this may be supported in future releases).

You cannot perform an update using a subquery



 



68   Bulk DML with the FORALL Statement

Oracle introduced a significant enhancement to PL/SQL's DML capabilities for Oracle8i Database and above with the FORALL statement,  allow you to bulk together multiple context switches into a single switch,



 



    DECLARE

       TYPE employee_aat IS TABLE OF employee%ROWTYPE

          INDEX BY PLS_INTEGER;

       l_employees   employee_aat;

    BEGIN

       FORALL l_index IN l_employees.FIRST .. l_employees.LAST

          INSERT INTO employee (employee_id, last_name)

            VALUES (l_employees (l_index).employee_id

                  , l_employees (l_index).last_name

            );

    END;



 





 



    [12]FORALL indx IN INDICES OF l_top_employees

       EXECUTE IMMEDIATE

          'INSERT INTO ' || l_table || ' VALUES (:emp_pky, :new_salary)

          USING l_new_salaries(indx).employee_id,

                l_new_salaries(indx).salary;



 



Cursor Attributes for FORALL

Oracle deposits in the Nth element in this collection the number of rows processed by the Nth execution of the FORALL's INSERT, UPDATE, or DELETE. If no rows were affected, the Nth row will contain a zero value



 




        
        
 
SQL%BULK_ROWCOUNT
 
 
Returns a collection that tells you the number of rows processed by each corresponding SQL statement executed via FORALL. Note: when %BULK_ROWCOUNT(i) is zero, %FOUND and %NOTFOUND are FALSE and TRUE, respectively
 



 



    DECLARE

       TYPE isbn_list IS TABLE OF VARCHAR2(13);



 



       my_books  isbn_list := isbn_list (

             '1-56592-375-8',

             '0-596-00121-5',

             '1-56592-849-0',

             '1-56592-335-9',

             '1-56592-674-9',

             '1-56592-675-7',

             '0-596-00180-0',

             '1-56592-457-6'

          );

    BEGIN

       FORALL book_index IN

              my_books.FIRST..my_books.LAST

          UPDATE books

             SET page_count

           WHERE isbn = my_books (book_index);



 



       -- Did I update the total number of books I expected?

       IF SQL%ROWCOUNT != 8

       THEN

          DBMS_OUTPUT.PUT_LINE (

             'We are missing a book!');

       END IF;



 



       -- Did the 4th UPDATE statement affect any rows?

       IF SQL%BULK_ROWCOUNT(4) = 0

       THEN

          DBMS_OUTPUT.PUT_LINE (

             'What happened to Oracle PL/SQL Programming?');;

       END IF;

    END;



 



The FORALL statement and %BULK_ROWCOUNT use the same subscripts or row numbers in the collections. For example, if the collection passed to FORALL has data in rows 10 through 200, then the %BULK_ROWCOUNT pseudo-collection will also have rows 10 through 200 defined and populated. Any other rows will be undefined.



 



ROLLBACK Behavior with FORALL

What happens when one of those DML statements fails? Here is the default behavior:

The FORALL statement stops executing. It isn't possible to request that the FORALL skip over the offending statement and continue on to the next row in the collection.

The DML statement being executed is rolled back to an implicit savepoint marked by the PL/SQL engine before execution of the statement.

Any previous DML operations in that FORALL statement that already executed without error are not rolled back.



 



Continuing Past Exceptions with FORALL

SAVE EXCEPTIONS, which can be used inside a FORALL statement. By including this clause, you instruct Oracle to continue processing even when an error has occurred. Oracle will then "save the exception" (or multiple exceptions, if more than one error occurs). When the DML statement completes, it will then raise the ORA-24381 exception.



 



    /* File on web: bulkexc.sql */

1    CREATE OR REPLACE PROCEDURE bulk_exceptions (

2       whr_in IN VARCHAR2 := NULL)

3    IS

4       TYPE namelist_t IS TABLE OF VARCHAR2 (100);

5       enames_with_errors namelist_t := -- Max of 10 characters in emp.

6          namelist_t ('LITTLE', 'BIGBIGGERBIGGEST', 'SMITHIE', '');

7       bulk_errors EXCEPTION;

8       PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );

9    BEGIN

10       FORALL indx IN

11             enames_with_errors.FIRST ..

12             enames_with_errors.LAST

13          SAVE EXCEPTIONS

14          EXECUTE IMMEDIATE

15             'UPDATE emp SET ename = :newname'

16             USING enames_with_errors(indx);

17    EXCEPTION

18       WHEN bulk_errors

19       THEN

20          FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT

21          
         
         
          
          LOOP
         
         

22             DBMS_OUTPUT.PUT_LINE (

23                'Error ' || indx || ' occurred during ' ||

24                'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ||

25                ' updating name to ' ||

26                     enames_with_errors (

27                      SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX));

28             DBMS_OUTPUT.PUT_LINE (

29                'Oracle error is ' ||

30                SQLERRM (-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));

31          END 
         
         
          
          LOOP
         
         ;

32    END;



 



    SQL> EXEC bulk_exceptions



 



    Error 1 occurred during iteration 2 updating name to BIGBIGGERBIGGEST

    Oracle error is ORA-01401: inserted value too large for column



 



    Error 2 occurred during iteration 4 updating name to

    Oracle error is ORA-01407: cannot update () to NULL



 



In other words, Oracle encountered two exceptions as it processed the DML for the names collection. It did not stop with the first exception, but continued on, cataloging a third



 



INDICES OF, VALUES OF

PL/SQL now offers the INDICES OF and VALUES OF clauses, both of which allow you to specify the portion of the binding array to be processed by FORALL.



 



‘INDICES OF’ Example:

    /* File on web: 10g_indices_of.sql */

1    DECLARE

2       TYPE employee_aat IS TABLE OF employee.employee_id%TYPE

3          INDEX BY PLS_INTEGER;

4     

5       l_employees           employee_aat;

6     

7       TYPE boolean_aat IS TABLE OF BOOLEAN

8          INDEX BY PLS_INTEGER;

9     

10       l_employee_indices   boolean_aat;

11    BEGIN

12       l_employees (1) := 7839;

13       l_employees (100) := 7654;

14       l_employees (500) := 7950;

15       --

16       l_employee_indices (1) := TRUE;

17       l_employee_indices (500) := TRUE;

18       --

19       FORALL l_index IN INDICES OF l_employee_indices

20          UPDATE employee

21             SET salary = 10000

22           WHERE employee_id = l_employees (l_index);

23    END;

24    /



 



Result:

    SQL> SELECT employee_id FROM employee  WHERE salary = 10000;

     

    EMPLOYEE_ID

    -----------

           7839

           7950



 



VALUES OF Example

    /* File on web: 10g_values_of.sql */

1    DECLARE

2       TYPE employee_aat IS TABLE OF employee.employee_id%TYPE

3          INDEX BY PLS_INTEGER;

4     

5       l_employees           employee_aat;

6     

7       TYPE indices_aat IS TABLE OF PLS_INTEGER

8          INDEX BY PLS_INTEGER;

9     

10       l_employee_indices   indices_aat;

11    BEGIN

12       l_employees (-77) := 7820;

13       l_employees (13067) := 7799;

14       l_employees (99999999) := 7369;

15       --

16       l_employee_indices (100) := -77;

17       l_employee_indices (200) := 99999999;

18       --

19       FORALL l_index IN VALUES OF l_employee_indices

20          UPDATE employee

21             SET salary = 10000

22           WHERE employee_id = l_employees (l_index);

23    END;

24    /



 



Result:

    SQL> SELECT employee_id FROM employee WHERE salary = 10000;

     

    EMPLOYEE_ID

    -----------

           7369

           7820



 



69   Transaction Management



  
 




SET TRANSACTION READ ONLY

In a read-only transaction, all subsequent queries see only those changes that were committed before the transaction began



 



SET TRANSACTION READ WRITE 

  







  
 




SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | READ COMMITTED 

  





When you specify SERIALIZABLE, a data manipulation statement (UPDATE, INSERT, DELETE) that attempts to modify a table already modified in an uncommitted transaction will fail.



 



If you specify READ COMMITTED[13], a DML statement that requires row-level locks held by another transaction will wait until those row locks are released

The default isolation level is READ COMMITTED, which means that as soon as changes are committed, they are visible to the main transaction



 



The LOCK TABLE Statement

 LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT];



 



Lock Mode:

ROW SHARE 

ROW EXCLUSIVE 

SHARE UPDATE 

SHARE 

SHARE ROW EXCLUSIVE 

EXCLUSIVE



 



1) Locking a table never stops other users from querying or reading the table.



 



2) If you specify the NOWAIT keyword , Oracle will not wait for the lock if the table has already been locked by another user.



 



Autonomous Transactions

That block becomes an independent transaction that is started by another transaction, referred to as the main transaction.



 



PRAGMA AUTONOMOUS_TRANSACTION;



 







 



These statements have a different scope of impact and visibility when executed within an autonomous transaction, and you will need to include a COMMIT or ROLLBACK in your program.



 



Rules and Restrictions on Autonomous Transactions

If an autonomous transaction attempts to access a resource held by the main transaction (which has been suspended until the autonomous routine exits), a deadlock can occur in your program.

    /* File on web: autondlock.sql */

    CREATE OR REPLACE PROCEDURE

       update_salary (dept_in IN NUMBER)

    IS

       PRAGMA AUTONOMOUS_TRANSACTION;



 



       CURSOR myemps IS

          SELECT empno FROM emp

           WHERE deptno = dept_in

             FOR UPDATE NOWAIT;

    BEGIN

       FOR rec IN myemps

       
         
         
          
          LOOP
         
         

          UPDATE emp SET sal = sal * 2

           WHERE empno = rec.empno;

       END 
         
         
          
          LOOP
         
         ;

       COMMIT;

    END;



 



    BEGIN

       [14]UPDATE emp SET sal = sal * 2;

       update_salary (10);

    END;



 



The results are not pretty:

    ERROR at line 1:

    ORA-00054: resource busy and acquire with NOWAIT specified



 



To exit without errors from an autonomous transaction program that has executed at least one INSERT, UPDATE or DELETE, you must perform an explicit commit or rollback.

The COMMIT and ROLLBACK statements end the active autonomous transaction, but they do not force the termination of the autonomous routine. You can, in fact, have multiple COMMIT and/or ROLLBACK statements inside your autonomous block

You must indicate autonomous transactions explicitly in each program(procedure)

You can roll back only to savepoints marked in the current transaction. When you are in an autonomous transaction, you cannot roll back to a savepoint set in the main transaction 

    

  






 



When to Use Autonomous Transactions

Logging mechanism

This is probably the most common motivation for PL/SQL developers to use autonomous transactions and is explored at the end of this section.

Perform commits and rollbacks in your database triggers

Suppose that you want to keep track of each action against a table, whether or not the action completed. You might even want to be able to detect which actions failed.

Reusable application components 

  





As we move more and more into the dispersed, multilayered world of the Internet, it becomes ever more important to be able to offer standalone units of work (also known as cartridges) that get their job done without any side effects on the calling environment 

  





Call user-defined functions in SQL that modify tables 

  





Oracle lets you call your own functions inside a SQL statement, you define your function as an autonomous transaction, you will then be able to insert, update or delete inside that function as it is run from within a query. 

  





Retry counter. 

  





Suppose that you want to let a user try to get access to a resource N times before an outright rejection 

  







  
 




Building an Autonomous Logging Mechanism

    /* File on web: log.pkg */

    CREATE TABLE logtab (

        code INTEGER,

        text VARCHAR2(4000),

        created_on DATE,

        created_by VARCHAR2(100),

        changed_on DATE,

        changed_by VARCHAR2(100),

        machine VARCHAR2(100),

        program VARCHAR2(100)

        );



 



    /* File on web: log.pkg */

    CREATE OR REPLACE PACKAGE BODY log

    IS

       PROCEDURE putline (

          code_in IN INTEGER, text_in IN VARCHAR2)

       IS

       BEGIN

          INSERT INTO logtab

               VALUES (

                  code_in,

                  text_in,

                  SYSDATE,

                  USER,

                  SYSDATE,

                  USER

               );

       END;



 



       PROCEDURE saveline (

          code_in IN INTEGER, text_in IN VARCHAR2)

       IS

          PRAGMA AUTONOMOUS_TRANSACTION;

       BEGIN

          putline (code_in, text_in);

          COMMIT;

       EXCEPTION WHEN OTHERS THEN ROLLBACK;

       END;

    END;



 



With this package in place, my error handler shown earlier can be as simple as this:

    EXCEPTION

       WHEN OTHERS THEN

          log.saveline (SQLCODE, SQLERRM);

    END;



 



70   Data Retrieval

Typical Query Operations

Parse 

  





Bind 

  





Open 

  





Execute 

  





Fetch 

  





Close



 



71   Choosing Between Explicit and Implicit Cursors

In some cases, explicit cursors will be more efficient. You should test your critical, often-executed queries in both formats to see which will be better in that particular situation



 



Explicit cursors offer much tighter programmatic control. If a row is not found, for example, Oracle will not raise an exception, instead forcing the execution block to shut down



 



I suggest that the question to answer is not "implicit or explicit?," but rather, "encapsulate or expose?" And the answer is (new wisdom revealed):

You should always encapsulate your single-row query, hiding the query behind a function interface, and passing back the data through the RETURN clause.

Don't worry about explicit versus implicit. Instead, worry about how you can tune and maintain your code if single-row queries are duplicated throughout your code. Take the time to encapsulate them behind functions, preferably package-based functions.



72   Declaring cursors in packages

By defining cursors in packages, you can more easily reuse those queries and avoid writing the same logical retrieval statement over and over again throughout your application



 



You will also realize some performance gains by minimizing the number of times your queries will need to be parsed.

If you declare cursors in packages for reuse, you need to be aware of one important factor. Data structures, including cursors, that are declared at the "package level" (not inside any particular function or procedure) maintain their values or persist for your entire session. This means that a packaged cursor will stay until you explicitly close it or until your session ends. Cursors declared in local blocks of code close automatically when that block terminates execution.



 



    CREATE OR REPLACE PACKAGE book_info

    IS

       CURSOR books_cur (title_filter_in IN book.title%TYPE)

          RETURN book%ROWTYPE

    END;



 



    CREATE OR REPLACE PACKAGE BODY book_info

    IS

       CURSOR books_cur (title_filter_in IN book.title%TYPE)

          RETURN book%ROWTYPE

       IS

          SELECT *

            FROM book

           WHERE title LIKE title_filter_in;

    END;



 



Adventage

1) Hide information 

  





2) Minimize recompilation

If the SELECT statement in your cursor uses a FOR UPDATE clause, all the rows identified by the query are locked when the cursor is opened.



 



If you have opened a SELECT FOR UPDATE query, it is especially important to close the cursor as soon as you are done because this query causes row-level locks to be applied.



 



    DECLARE

       CURSOR company_cur is SELECT ...;

       company_rec company_cur%ROWTYPE;

    BEGIN

       OPEN company_cur;

       FETCH company_cur INTO company_rec;



 



You should always fetch into a record that was defined with %ROWTYPE against the cursor; avoid fetching into lists of variables. Fetching into a record usually means that you write less code and have more flexibility to change the select list without having to change the FETCH statement.



 



    CREATE OR REPLACE PACKAGE bookinfo_pkg

    IS

       CURSOR bard_cur

           IS SELECT title, date_published

         FROM books

        WHERE UPPER(author) LIKE 'SHAKESPEARE%';

    END bookinfo_pkg;



 



    DECLARE

       bard_rec   bookinfo_pkg.bard_cur%ROWTYPE;

    BEGIN

       -- Check to see if the cursor is already opened.

       -- This is possible, since it is a packaged cursor.

       -- If so, first close it and then re-open it to

       -- ensure a "fresh" result set.

       IF bookinfo_pkg.bard_cur%ISOPEN

       THEN

          CLOSE bookinfo_pkg.bard_cur;

       END IF;



 



       OPEN bookinfo_pkg.bard_cur;



 



       -- Fetch each row, but stop when I've displayed the

       -- first five works by Shakespeare or when I have

       -- run out of rows.

       
         
         
          
          LOOP
         
         

          FETCH bookinfo_pkg.bard_cur INTO bard_rec;

          EXIT WHEN bookinfo_pkg.bard_cur%NOTFOUND

                 OR bookinfo_pkg.bard_cur%ROWCOUNT = 6;

          DBMS_OUTPUT.put_line (

                bcur%ROWCOUNT

             || ') '

             || rec.title

             || ', published in '

             || TO_CHAR (rec.date_published, 'YYYY')

          );

       END 
         
         
          
          LOOP
         
         ;



 



       CLOSE bard_cur;

    END;



 



Cursor Parameters

    PROCEDURE explain_joke (main_category_in IN joke_category%TYPE)

    IS

       /*

       || Cursor with parameter list consisting of a single

       || string parameter.

       */

       CURSOR joke_cur (category_in VARCHAR2)

       IS

          SELECT name, category, last_used_date

            FROM joke

           WHERE category = UPPER (category_in);



 



       joke_rec joke_cur%ROWTYPE;



 



    BEGIN

       /* Now when I open the cursor, I also pass the argument */

       OPEN joke_cur (main_category_in);

       FETCH joke_cur INTO joke_rec;



 



    OPEN joke_cur (jokes_pkg.category);

    OPEN joke_cur ('husband');

    OPEN joke_cur ('politician');

    OPEN joke_cur (jokes_pkg.relation || ' IN-LAW');



 



The most common place to use a parameter in a cursor is in the WHERE clause, but you can make reference to it anywhere in the SELECT statement, as shown here



 



With the restriction that a cursor parameter can be an IN parameter only. You cannot specify OUT or IN OUT modes for cursor parameters 

    

  






 



73   BULK COLLECT

Example-1:

    DECLARE

       CURSOR major_polluters_cur

       IS

          SELECT name, mileage

            FROM transportation

           WHERE TYPE = 'AUTOMOBILE'

             AND mileage < 20;

       names name_varray;

       mileages number_varray;

    BEGIN

       FOR bad_car IN major_polluters

       
         
         
          
          LOOP
         
         

          names.EXTEND;

          names (major_polluters%ROWCOUNT) := bad_car.NAME;

          mileages.EXTEND;

          mileages (major_polluters%ROWCOUNT) := bad_car.mileage;

       END 
         
         
          
          LOOP
         
         ;

       -- Now work with data in the collections

    END;



 



You can use BULK COLLECT with both dynamic and static SQL



 



You can use BULK COLLECT keywords in any of the following clauses: SELECT INTO, FETCH INTO, and RETURNING INTO



 





 





 



Example-2:

    DECLARE

       TYPE transportation_aat IS TABLE OF transportation%ROWTYPE

          INDEX BY PLS_INTEGER;

    BEGIN

       SELECT *

         FROM transportation

         BULK COLLECT INTO l_transportation

           WHERE TYPE = 'AUTOMOBILE'

             AND mileage < 20;

     

       -- Now work with data in the collections

    END;



 



Example-3:

    DECLARE

       CURSOR major_polluters IS

          SELECT name, mileage

            FROM transportation

           WHERE TYPE = 'AUTOMOBILE'

             AND mileage < 20;

       names name_varray;

       mileages number_varray;

    BEGIN

       OPEN major_polluters;

       FETCH major_polluters BULK COLLECT INTO names, mileages;

       CLOSE major_polluters;

     

       -- Now work with data in the collections

     

    END;



 



Example-4:

    DECLARE

       CURSOR allrows_cur IS SELECT * FROM EMPLOYEE;

     

       TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE

          INDEX BY BINARY_INTEGER;

     

       l_employees employee_aat;

       l_row PLS_INTEGER;

    BEGIN

       OPEN allrows_cur;

       
         
         
          
          LOOP
         
         

          FETCH allrows_cur BULK COLLECT INTO l_employees

             LIMIT 100;

          EXIT WHEN l_employees.COUNT = 0;

      

          -- Process the data by scanning through the collection.

     

          l_row := l_employees.FIRST;

          WHILE (l_row IS NOT NULL)

          
         
         
          
          LOOP
         
         

             upgrade_employee_status (l_employees(l_row).employee_id);

             l_row := l_employees.NEXT (l_row);

          END 
         
         
          
          LOOP
         
         ;

       END 
         
         
          
          LOOP
         
         ;

      

       CLOSE allrows_cur;

    END;



 



74   Bulk Fetching of Multiple Columns(*)

This feature became available in Oracle9i Database Release 2.

    DECLARE

       -- Declare the type of collection

       TYPE VehTab IS TABLE OF transportation%ROWTYPE;



 



       -- Instantiate a particular collection from the TYPE.

       gas_guzzlers VehTab;

    BEGIN

       SELECT *

         BULK COLLECT INTO gas_guzzlers

         FROM transportation

        WHERE mileage < 20;

       ...



 



75   RETURNING Clause with Bulk Operations

       big_bucks NUMBER := salforexec ('CEO');

       min_sal NUMBER := big_bucks / 50;

       names name_varray;

       old_salaries number_varray;

       new_salaries number_varray;



 



       CURSOR affected_employees (ceosal IN NUMBER)

       IS

          SELECT name, salary + bonus old_salary

            FROM compensation

           WHERE title != 'CEO'

             AND ((salary + bonus < ceosal / 50)

                  OR (salary + bonus > ceosal / 10)) ;



 





 



    OPEN affected_employees (big_bucks);

    FETCH affected_employees

       BULK COLLECT INTO names, old_salaries;



 



    FORALL indx IN names.FIRST .. names.L*

       UPDATE compensation

          SET salary =

              GREATEST(

                 DECODE (

                    GREATEST (min_sal, salary),

                       min_sal, min_sal,

                    salary / 5),

                 min_sal )

        WHERE name = names (indx)

        RETURNING salary BULK COLLECT INTO new_salaries;



 



76   SELECT ... FOR UPDATE

Oracle automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, holding the records "for your changes only" as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT but other sessions can still read the data.



 



    CURSOR toys_cur IS

       SELECT name, manufacturer, preference_level, sell_at_yardsale_flag

         FROM my_sons_collection

        WHERE hours_used = 0

          FOR UPDATE;



 



    CURSOR fall_jobs_cur IS

       SELECT task, expected_hours, tools_required, do_it_yourself_flag

         FROM winterize

        WHERE year = TO_CHAR (SYSDATE, 'YYYY')

          FOR UPDATE OF [15]task;



 



If you simply state FOR UPDATE in the query and do not include one or more columns after the OF keyword, the database will then lock all identified rows across all tables listed in the FROM clause. 

    

  






 



You can append the optional keyword NOWAIT to the FOR UPDATE clause ,Without the NOWAIT clause, your process will block until the table is available



 



    UPDATE table_name

       SET set_clause

     WHERE CURRENT OF cursor_name;



 



    DELETE

      FROM table_name

     WHERE CURRENT OF cursor_name;



 



77   REF CURSOR Types

    TYPE company_curtype IS REF CURSOR [16]RETURN company%ROWTYPE;

    TYPE generic_curtype IS [17]REF CURSOR;



 



Oracle provides a predefined weak REF CURSOR type named SYS_REFCURSOR

    DECLARE

       my_cursor SYS_REFCURSOR;



 



Opening Cursor Variables

   OPEN cursor_name FOR select_statement;



 



    DECLARE

       TYPE emp_curtype IS REF CURSOR;

       emp_curvar emp_curtype;

    BEGIN

       OPEN emp_curvar FOR SELECT * FROM emp;

       OPEN emp_curvar FOR SELECT employee_id FROM emp;

       OPEN emp_curvar FOR SELECT company_id, name FROM company;

    END;



 



If the cursor variable is of the weak REF CURSOR type, the PL/SQL compiler cannot perform the same kind of check it performs for a strong REF CURSOR type



 



    TYPE building_curtype IS REF CURSOR;



 



    PROCEDURE open_site_list

       (address_in IN VARCHAR2,

        site_cur_inout IN OUT building_curtype)

    IS

       home_type CONSTANT INTEGER := 1;

       commercial_type CONSTANT INTEGER := 2;



 



       /* A static cursor to get building type. */

       CURSOR site_type_cur IS

          SELECT site_type FROM property_master

           WHERE address = address_in;

       site_type_rec site_type_cur%ROWTYPE;



 



    BEGIN

       /* Get the building type for this address. */

       OPEN site_type_cur;

       FETCH site_type_cur INTO site_type_rec;

       CLOSE site_type_cur;



 



       /* Now use the site type to select from the right table.*/

       IF site_type_rec.site_type =  home_type

       THEN

          /* Use the home properties table. */

          OPEN site_cur_inout FOR

             SELECT * FROM home_properties

              WHERE address LIKE '%' || address_in || '%';



 



       ELSIF site_type_rec.site_type =  commercial_type

       THEN

          /* Use the commercial properties table. */

          OPEN site_cur_inout FOR

             SELECT * FROM commercial_properties

              WHERE address LIKE '%' || address_in || '%';

       END IF;

    END open_site_list;



 



Cursor variable aliases

    DECLARE

       TYPE curvar_type IS REF CURSOR;

       curvar1 curvar_type;

       curvar2 curvar_type;

       story fairy_tales%ROWTYPE;

    BEGIN

       OPEN curvar1 FOR SELECT * FROM fairy_tales;

       curvar2 := curvar1;

       FETCH curvar1 INTO story;

       FETCH curvar2[MSOffice8]  INTO story;

       CLOSE curvar2;

       FETCH curvar1[MSOffice9]  INTO story;

    END;



 



Scope of cursor object

    DECLARE

       /* Define weak REF CURSOR type, cursor variable

          and local variable */

       TYPE curvar_type IS REF CURSOR;

       curvar1 curvar_type;

       do_you_get_it VARCHAR2(100);

    BEGIN

       /*

       || Nested block which creates the cursor object and

       || assigns it to the curvar1 cursor variable.

       */

       DECLARE

          curvar2 curvar_type;

       BEGIN

          OPEN curvar2 FOR SELECT punch_line FROM jokes;

          curvar1 := curvar2;

       END;

       /*

       || The curvar2 cursor variable is no longer active,

       || but "the baton" has been passed to curvar1, which

       || does exist in the enclosing block. I can therefore

       || fetch from the cursor object, through this other

       || cursor variable.

       */

       FETCH curvar1 INTO do_you_get_it;

       CLOSE curvar1;

    END;



 



Passing Cursor Variables as Arguments



 



    PACKAGE company

    IS

       /* Define the REF CURSOR type. */

       TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;

    END package;



 



    PROCEDURE open_company (curvar_out [18]OUT company.curvar_type) IS

    BEGIN

       ...

    END;



 





 





 



  


Cursor Expressions

    CREATE OR REPLACE PROCEDURE emp_report (p_locid NUMBER)

    IS

       TYPE refcursor IS REF CURSOR;



 



    -- The query returns only 2 columns, but the second column is

    -- a cursor that lets us traverse a set of related information.

       CURSOR all_in_one_cur is

          SELECT l.city,

                 CURSOR (SELECT d.department_name,

                                CURSOR(SELECT e.last_name

                                         FROM employees e

                                        WHERE e.department_id =

                                                    d.department_id)

                                              AS ename

                           FROM departments d

                          WHERE l.location_id = d.location_id) AS dname

            FROM locations l

           WHERE l.location_id = p_locid;



 



       departments_cur    refcursor;

       employees_cur   refcursor;



 



       v_city    locations.city%TYPE;

       v_dname   departments.department_name%TYPE;

       v_ename   employees.last_name%TYPE;

    BEGIN

       OPEN all_in_one_cur;



 



       
         
         
          
          LOOP
         
         

          FETCH all_in_one_cur INTO v_city, departments_cur;

          EXIT WHEN all_in_one_cur%NOTFOUND;



 



          -- Now I can loop through departments and I do NOT need to

          -- explicitly open that cursor. Oracle did it for me.

          
         
         
          
          LOOP
         
         

             FETCH departments_cur INTO v_dname, employees_cur;

             EXIT WHEN departments_cur%NOTFOUND;



 



             -- Now I can loop through employees for that department.

             -- Again, I do not need to open the cursor explicitly.

             
         
         
          
          LOOP
         
         

                FETCH employees_cur INTO v_ename;

                EXIT WHEN employees_cur%NOTFOUND;

                DBMS_OUTPUT.put_line (

                      v_city

                   || ' '

                   || v_dname

                   || ' '

                   || v_ename

                );

             END 
         
         
          
          LOOP
         
         ;

          END 
         
         
          
          LOOP
         
         ;

       END 
         
         
          
          LOOP
         
         ;



 



       CLOSE all_in_one_cur;

    END;



 



78   Dynamic SQL



 



/* File on web: tabcount_nds.sf */

CREATE OR REPLACE FUNCTION tabcount (

   tab IN VARCHAR2, whr IN VARCHAR2 := NULL)

   RETURN PLS_INTEGER AUTHID CURRENT_USER

IS

   str      VARCHAR2 (32767) := 'SELECT COUNT(*) FROM ' || tab;

   retval   PLS_INTEGER;

BEGIN

   IF whr IS NOT NULL THEN

      str := str || ' WHERE ' || whr;

   END IF;

 

   EXECUTE IMMEDIATE str INTO retval;

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line (

         'TABCOUNT ERROR: ' || DBMS[B10] _UTILITY.FORMAT_ERROR_STACK);

      DBMS_OUTPUT.put_line (str);

      RETURN NULL;

END;

/



 



/* File on web: updnval.sf */

CREATE OR REPLACE FUNCTION updNVal (

   col IN VARCHAR2,

   val IN NUMBER,

   start_in IN DATE,

   end_in IN DATE)

   RETURN PLS_INTEGER

IS

BEGIN

   EXECUTE IMMEDIATE

      'UPDATE employee SET ' || col || ' = :the_value

        WHERE hire_date BETWEEN :lo AND :hi'

     USING val, start_in, end_in;

   RETURN SQL%ROWCOUNT;

END;



 





 



/* File on web: run9am.sp */

CREATE OR REPLACE PROCEDURE run_9am_procedure (

   id_in IN employee.employee_id%TYPE,

   hour_in IN INTEGER)

IS

   v_apptCount INTEGER;

   v_name VARCHAR2(100);

BEGIN

   EXECUTE IMMEDIATE

      'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') ||

         '_set_schedule (:id, :hour, :name, :appts); END;'

     USING IN id_in, IN hour_in, OUT v_name, OUT v_apptCount;



 



   DBMS_OUTPUT.PUT_LINE (

      'Employee ' || v_name || ' has ' || v_apptCount ||

      ' appointments on ' || TO_CHAR (SYSDATE));

END;



 



/* File on web: showcol 

.sp */

CREATE OR REPLACE PROCEDURE showcol (

   tab IN VARCHAR2,

   col IN VARCHAR2,

   whr IN VARCHAR2)

IS

   TYPE cv_type IS REF CURSOR;

   cv cv_type;

   val VARCHAR2(32767);

   PROCEDURE display_header_info ... END;

BEGIN

   /* Construct the very dynamic query and open the cursor. */

   OPEN cv FOR

      'SELECT ' || col ||

      '  FROM ' || tab ||

      ' WHERE ' || whr;



 



   
         
         
          
          LOOP
         
         

      /* Fetch the next row, and stop if no more rows. */

      FETCH cv INTO val;

      EXIT WHEN cv%NOTFOUND;



 



      /* Display the data, with a header before the first row. */

      IF cv%ROWCOUNT = 1

      THEN

         display_header_info (tab, col);

      END IF;

      DBMS_OUTPUT.PUT_LINE (val);

   END 
         
         
          
          LOOP
         
         ;



 



   CLOSE cv; --All done, so clean up!

END;



 



OPEN cv FOR

      'SELECT ' || col ||

      '  FROM ' || tab ||

      ' WHERE ' || dtcol ||

         ' BETWEEN TRUNC (:startdt)

               AND TRUNC (:enddt)'

   USING dt1, NVL (dt2, dt1+1);



 



Passing NULL Values

Wrong way to using null, because NULL is not data type.

EXECUTE IMMEDIATE

   'UPDATE employee SET salary = :newsal

     WHERE hire_date IS NULL'

   USING NULL;

You will, however, get this error:

PLS-00457: in USING clause, expressions have to be of SQL types



 



Hide the NULL value behind a variable façade

DECLARE

   /* Default initial value is NULL */

   no_salary_when_fired NUMBER;

BEGIN

    EXECUTE IMMEDIATE

      'UPDATE employee SET salary = :newsal

        WHERE hire_date IS NULL'

      USING no_salary_when_fired;

END;



 



Use a conversion function to convert the NULL value to a typed value explicitly

BEGIN

    EXECUTE IMMEDIATE

      'UPDATE employee SET salary = :newsal

        WHERE hire_date IS NULL'

      USING TO_NUMBER (NULL);

END;



 



Replace Repetitive Code with Dynamic Block

CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER)

IS

BEGIN

   IF    line = 1 THEN process_line1;

   ELSIF line = 2 THEN process_line2;

   ...

   ELSIF line = 514 THEN process_line514;

   ...

   ELSIF line = 2057 THEN process_line2057;

   END IF;

END;



 



CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER)

IS

BEGIN

   EXECUTE IMMEDIATE

[B11]       'BEGIN process_line' || line || '; END;';

END;



 



CREATE OR REPLACE PROCEDURE get_rows (

   table_in   IN   VARCHAR2

 , where_in   IN   VARCHAR2

)

IS

BEGIN

   EXECUTE IMMEDIATE

      'SELECT * FROM ' || table_in || ' WHERE ' || where_in

      INTO l_row;

END;



 



CREATE OR REPLACE PROCEDURE get_rows (

   table_in   IN   VARCHAR2

 , where_in   IN   VARCHAR2

)

IS

BEGIN

   EXECUTE IMMEDIATE

      'DECLARE

          l_row ' || table_in || '%ROWTYPE;

       BEGIN

          SELECT * INTO l_row

            FROM ' || table_in || ' WHERE ' || where_in || ';

       END;';

END get_rows;



 





 



Use Binding Rather Than Concatenation

Table 6 

  






        
        
 
Binding
 
 
Concatenation
 
 
EXECUTE IMMEDIATE
 
   'UPDATE ' ||
 
   tab 'SET sal = :new_sal' USING v_sal;
 
 
 
 
 
 
EXECUTE IMMEDIATE
 
    'UPDATE ' ||
 
    tab 'SET sal = ' ||
 
    v_sal;
 



 



1) Binding is usually faster

2) Binding is easier to write and maintain 

  







  
 




3) Binding helps avoid implicit conversions 

  







  
 




4) Binding negates the chance of code injection 

  







  
 




Avoid Code Injection with Binding

BEGIN

   get_rows ('EMPLOYEE'

     ,'employee_id=7369;

       EXECUTE IMMEDIATE ''CREATE TABLE nasty_data (mycol NUMBER)''' );[19]

END;



 



79   Benefits of Local Modularization

1) To reduce the size of the module by stripping it of repetitive code 

  





2) To improve the readability of your code



 



PROCEDURE calc_percentages (tot_sales_in IN NUMBER)

IS

   l_profile sales_descriptors%ROWTYPE;

   /* Define a function right inside the procedure! */

   FUNCTION pct_stg (val_in IN NUMBER) RETURN VARCHAR2

   IS

   BEGIN

      RETURN TO_CHAR ((val_in/tot_sales_in ) * 100, '$999,999');

   END;

BEGIN

   l_profile.food_sales_stg := pct_stg (sales_pkg.food_sales);

   l_profile.service_sales_stg := pct_stg (sales_pkg.service_sales);

   l_profile.toy_sales_stg := pct_stg (sales_pkg.toy_sales);

END;



 



Scope of Local Modules

The big difference between local modules and package modules is their scope. Local modules can be called only from within the block in which they are defined; package modules can not a minimumbe called from anywhere in the package. If the package modules are also listed in the package specification, they can be called by any other program in your application.



 



80   Advanced Topics

80.1Calling Your Function Inside SQL

All of the function's parameters must use the IN mode. Neither IN OUT nor OUT

The stored function may not modify database tables. It cannot execute an INSERT, DELETE, or UPDATE statement

The stored function may not call another module (stored procedure or function) that breaks any of the preceding rules



 



80.2Replacing DECODEs with IF statements

SELECT FC.year_number,

       SUM (DECODE (GREATEST (ship_date, FC.q1_sdate),

                    ship_date,

                    DECODE (LEAST (ship_date, FC.q1_edate),

                            ship_date, 1,

                            0),

                    0)) Q1_results,

       SUM (DECODE (GREATEST (ship_date, FC.q2_sdate),

                    ship_date,

                    DECODE (LEAST (ship_date, FC.q2_edate),

                            ship_date, 1,

                            0),

                    0)) Q2_results,

       SUM (DECODE (GREATEST (ship_date, FC.q3_sdate),

                    ship_date,

                    DECODE (LEAST (ship_date, FC.q3_edate),

                            ship_date, 1,

                            0),

                    0)) Q3_results,

       SUM (DECODE (GREATEST (ship_date, FC.q4_sdate),

                    ship_date,

                    DECODE (LEAST (ship_date, FC.q4_edate),

                            ship_date, 1,

                            0),

                    0)) Q4_results

  FROM orders O,

       fiscal_calendar FC

 GROUP BY year_number;



 



FUNCTION incr_in_range

   (ship_date_in IN DATE, sdate_in IN DATE, edate_in IN DATE)

   RETURN INTEGER

IS

BEGIN

   IF ship_date_in BETWEEN sdate_in AND edate_in

   THEN

      RETURN 1;

   ELSE

      RETURN 0;

   END IF;

END;



 



SELECT FC.year_number,

       SUM (incr_in_range (ship_date, q1_sdate, q1_edate)) Q1_results,

       SUM (incr_in_range (ship_date, q2_sdate, q2_edate)) Q2_results,

       SUM (incr_in_range (ship_date, q3_sdate, q3_edate)) Q3_results,

       SUM (incr_in_range (ship_date, q4_sdate, q4_edate)) Q4_results

  FROM orders O,

       fiscal_calendar FC

 GROUP BY year_number;



 



Table Functions

Section 17.8



 



Deterministic Functions

A function is called deterministic if it returns the same result value whenever it is called with the same values for its arguments.



 



CREATE OR REPLACE FUNCTION betwnStr (

   string_in IN VARCHAR2,start_in IN INTEGER, end_in IN INTEGER

   ) RETURN VARCHAR2 DETERMINISTIC IS

BEGIN

   RETURN (

      SUBSTR (

         string_in, start_in, end_in - start_in + 1));

END;

When I next call the function with those arguments, it can return the result without executing the function!



 



81   Package

81.1Execute complex initialization logic

If the assignment of the default value raises an exception, that exception cannot be trapped within the package: it will instead propagate out unhandled. This issue is covered in more detail in the section, "When initialization fails."



 



81.2When initialization fails

/* File on web: valerr.pkg */

CREATE OR REPLACE PACKAGE valerr

IS

   FUNCTION get RETURN VARCHAR2;

END valerr;

/

CREATE OR REPLACE PACKAGE BODY valerr

IS

   -- A package-level, but private global variable

   v VARCHAR2(1) := 'ABC';



 



   FUNCTION get RETURN VARCHAR2

   IS

   BEGIN

      RETURN v;

   END;

BEGIN

   DBMS[B12] _OUTPUT.PUT_LINE ('Before I show you v...');



 



EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE ('Trapped the error!');

END valerr;



 



Suppose that I connect to SQL*Plus and try to run the valerr.get function (for the first time in that session). This is what I see:

SQL> exec DBMS_OUTPUT.PUT_LINE (valerr.get)

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

In other words, my attempt in the declaration of the v variable to assign a value of "ABC" caused a VALUE_ERROR exception. The exception section at the bottom of the package did not trap the error; it can only trap errors raised in the initialization section itself. And so the exception goes unhandled. Notice, however, that when I call that function a second time in my session, I do not get an error: 

    

  




SQL> BEGIN

  2     DBMS_OUTPUT.PUT_LINE ('V is set to ' || NVL (valerr.get, 'NULL'));

  3  END;

  4  /

V is set to NULL



 



Cache static session information

If you want to make sure that the information is retrieved just once in your session, then the initialization section is an ideal, automatically managed way to get this to happen.



 



This is accomplished by moving the data closer to the user, into the Program Global Area of each user. If there are 1,000 distinct sessions, then there are 1,000 copies of the cached data. This agreement decreases the CPU usage, but takes up more memory.



 



CREATE OR REPLACE PACKAGE BODY company IS

BEGIN

   /*

   || Initialization section of company_pkg updates the global

   || package data of a different package. This is a no-no!

   */

   SELECT SUM (salary)

     INTO employee_pkg.max_salary

     FROM employee;

END company;



 



Working with Package Data

In the PL/SQL runtime architecture, package data structures persist (hold their values) for the duration of a session (rather than the duration of execution for a particular program).



 



If package data is declared inside the package body, then that data persists for the session but can be accessed only by elements defined in the package itself (private data).



 



They are accessible only within a single Oracle session or connection; package data is not shared across sessions.



 



If you need to share data between different Oracle sessions, you can use the DBMS_PIPE package or Oracle Advanced Queuing



 





 



Encapsulating Data Manipulation



 



Gain many of the advantages of data encapsulation without having to completely revamp your coding techniques. At a minimum, I suggest that you:

Hide all your single-row queries behind a function interface. That way, you can make sure that error handling is performed and can choose the best implementation (implicit or explicit cursors, for example).



 



Identify the tables that are most frequently and directly manipulated by developers and build layers of code around them



 



Create packaged programs to handle complex transactions. If "add a new order" involves inserting two rows, updating six others, and so on, make sure to embed this logic inside a procedure that handles the complexity



 



/* File on web: givebonus2.sp */

1    CREATE OR REPLACE PROCEDURE give_bonus (

2       dept_in    IN   employee_tp.department_id_t

3     , bonus_in   IN   employee_tp.bonus_t

4    )

5    IS

6       l_department     department_tp.department_rt;

7       l_employees      employee_tp.employee_tc;

8       l_row            PLS_INTEGER;

9       l_rows_updated   PLS_INTEGER;

10    BEGIN

11       l_department := department_qp.onerow (dept_in);

12       l_employees := employee_qp.ar_fk_emp_department (dept_in);

13     

14       l_row := l_array.FIRST;

15     

16       WHILE (l_row IS NOT NULL)

17       
         
         
          
          LOOP
         
         

18          IF employee_rp.eligible_for_bonus (rec)

19          THEN

20             employee_cp.upd_onecol_pky

21                (colname_in          => 'salary'

22               , new_value_in        =>   l_employees (l_row).salary

23                                        + bonus_in

24             , employee_id_in      => l_employees (l_row).employee_id

25               , rows_out            => l_rows_updated

26                );

27          END IF;

28     

29          l_row := l_employees.NEXT (l_row);

30       END 
         
         
          
          LOOP
         
         ;

31     

32       ... more processing with name and other elements

33    * END;



 





 



Avoid Hardcoding Literals

CREATE OR REPLACE PACKAGE config_pkg

IS

   closed_status     CONSTANT VARCHAR2(1) := 'C';

   open_status       CONSTANT VARCHAR2(1) := 'O';

   active_status     CONSTANT VARCHAR2(1) := 'A';

   inactive_status   CONSTANT VARCHAR2(1) := 'I';



 



   min_difference    CONSTANT PLS_INTEGER := 1;

   max_difference    CONSTANT PLS_INTEGER := 100;



 



   earliest_date     CONSTANT DATE := SYSDATE;

   latest_date       CONSTANT DATE := ADD_MONTHS (SYSDATE, 120);



 



END config_pkg;



 



IF footing_difference

   BETWEEN config_pkg.min_difference and config_pkg.max_difference

THEN

   adjust_line_item;

END IF;



 



IF cust_status = config_pkg.closed_status

THEN

   reopen_customer;

END IF;



 



82   Packages and Object Types

Key differences between the two, including the following

An object type is a template for data; you can instantiate multiple object type instances (a.k.a. "objects") from that template



 



In Oracle9i Database and Oracle Database 10g, object types offer inheritance



 



With packages, you can create private, hidden data and programs. This is not supported in object types, in which everything is publicly declared and accessible (although you can still hide the implementation of methods in the object type body).

83   Trigger

By default, DML triggers participate in the transaction from which they were fired. This means that:

If a trigger raises an exception, that part of the transaction will be rolled back.

If the trigger performs any DML itself (such as inserting a row into a log table), then that DML becomes a part of the main transaction.

You cannot issue a COMMIT or ROLLBACK from within a DML trigger.



 



84   Recompiling Invalid Code

Oracle uses its dependency-related views (such as PUBLIC_DEPENDENCIES) to identify all objects that depend on the changed object. It then marks those dependent objects as INVALID, essentially throwing away any compiled code.



 



84.1Recompile individual program units

ALTER FUNCTION a_function COMPILE REUSE SETTINGS;

ALTER PACKAGE my_package COMPILE REUSE SETTINGS;

ALTER PACKAGE my_package COMPILE SPECIFICATION REUSE SETTINGS;

ALTER PACKAGE my_package COMPILE BODY REUSE SETTINGS;



 



BULK recompile:

SELECT 'ALTER ' || object_type || ' ' || object_name

       || ' COMPILE REUSE SETTINGS;'

  FROM all_objects

 WHERE status = 'INVALID';



 



The problem with this "bulk" approach is that as you recompile one invalid object, you may cause many others to be marked INVALID



 



Use UTL_RECOMP

The UTL_RECOMP built-in package offers two programs that you can use to recompile any invalid objects in your schema: RECOMP_SERIAL and RECOMP_PARALLEL



 



If you have multiple processors, the parallel version may help you complete your recompilations more rapidly



 



85   Optimizing PL/SQL Performance

Use BULK COLLECT when querying multiple rows

Use FORALL when modifying multiple rows

Use the NOCOPY hint when passing large structures

Use PLS_INTEGER for intensive integer computations

Use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic

Group together related programs in a package

Pin into shared memory large and frequently executed programs



 



Pin frequently accessed programs in the shared memory pool with the DBMS_SHARED_POOL.PIN procedure

86   Call Java

86.1LoadJava Commander:

    loadjava {-user | -u} username/password[@database]

      [option ...] filename [filename ]...



 



loadjava -user bxu/j777v@jdba -resolve C:/eclipse-3.1.1/workspace/ReportGroupItinerary/

INF/classes/com/jonview/report/JDBCConnect.class



 



86.2Publishing and Using Java in PLSQL

    CREATE [OR REPLACE] --If standalone (not in a package)

    Standard PL/SQL procedure/function header

    {IS | AS} LANGUAGE JAVA

    NAME 'method_fullname (java_type[, java_type]...)

      [return java_type]';



 



    /* File on web: fdelete.sf */

    CREATE OR REPLACE FUNCTION fDelete (

       file IN VARCHAR2)

       RETURN NUMBER

    AS LANGUAGE JAVA

       NAME 'JDelete.delete (

                java.lang.String)

                return int';



 



Using dropjava

    dropjava {-user | -u} username/password[@database]

      [option ...] filename [filename] ...



 



An object name in Oracle, whether the name of a database table or a Java class, cannot be longer than 30 characters. Java does not have this restriction; you can have much longer names. Oracle will allow you to load a Java class into Oracle with a name of up to 4,000 characters.



 



List Java Objects Script

SELECT object_name, object_type, status, timestamp 

  
      FROM all_objects 

  
     WHERE (object_name NOT LIKE 'SYS_%' 

  
       AND object_name NOT LIKE 'CREATE$%' 

  
       AND object_name NOT LIKE 'JAVA$%' 

  
       AND object_name NOT LIKE 'LOADLOB%') 

  
       AND object_type LIKE 'JAVA %' 

  
     ORDER BY object_type, object_name; 

  







 





 





 





 





 



87   Avoiding Boolean Flags

    IF condition1 AND condition2 THEN

       ...

    ELSE

       ...

    END IF;



 



PL/SQL can stop evaluation of the expression when condition1 is FALSE or NULL



 



    IF :customer.order_total > max_allowable_order

    THEN

       order_exceeds_balance := TRUE;

    ELSE

       order_exceeds_balance := FALSE;

    END IF;



 



order_exceeds_balance := :customer.order_total > max_allowable_order;



 



my_boolean := condition1 AND condition2



 



Unlike the case with an IF statement, when condition1 is NULL, this expression will not short-circuit. Why not? Because the result could be either NULL or FALSE, depending on condition2. For an IF statement, NULL and FALSE both lead to the ELSE branch, so a short-circuit can occur. But for an assignment, the ultimate value must be known, and short-circuiting, in this case, can (and will) occur only when condition1 is FALSE.



 



    IF low_CPU_condition AND high_CPU_condition

    THEN

       ...

    END IF;



 



The low_CPU_condition is evaluated first, and if the result is enough to determine the end result of the AND operation (i.e., the result is FALSE), the more expensive condition will not be evaluated, and your application's performance is the better for that evaluation not happening.



 



    IF low_CPU_condition

    THEN

       IF high_CPU_condition

       THEN

          ...

       END IF;

    END IF;



 



88   CASE statement

88.1Simple CASE Statements

    CASE expression

    WHEN result1 THEN

       statements1

    WHEN result2 THEN

       statements2

    ...

    ELSE

       statements_else

    END CASE;



 



    CASE employee_type

    WHEN 'S' THEN

       award_salary_bonus(employee_id);

    WHEN 'H' THEN

       award_hourly_bonus(employee_id);

    WHEN 'C' THEN

       award_commissioned_bonus(employee_id);

    ELSE

       RAISE invalid_employee_type;

    END CASE;



 



Searched CASE Statements

    CASE

    WHEN expression1 THEN

       statements1

    WHEN expression2 THEN

       statements2

    ...

    ELSE

       statements_else

    END CASE;



 



    CASE TRUE

    WHEN salary >= 10000 AND salary <=20000 THEN

       give_bonus(employee_id, 1500);

    WHEN salary > 20000 AND salary <= 40000

       give_bonus(employee_id, 1000);

    WHEN salary > 40000

       give_bonus(employee_id, 500);

    ELSE

       give_bonus(employee_id, 0);

    END CASE;



 



Execution ends once a sequence of statements has been executed. If more than one expression evaluates to TRUE, only the statements associated with the first such expression are executed



 



The ELSE clause is optional. If no ELSE is specified, and no expressions evaluate to TRUE, then a CASE_NOT_FOUND exception is raised



 



WHEN clauses are evaluated in order, from top to bottom



 



Not recommend:

    CASE

    WHEN salary > 40000 THEN

       give_bonus(employee_id, 500);

    WHEN salary > 20000 THEN

       give_bonus(employee_id, 1000);

    WHEN salary >= 10000 THEN

       give_bonus(employee_id, 1500);

    ELSE

       give_bonus(employee_id, 0);

    END CASE;



 



CASE in SQL Statements

SELECT CASE

        WHEN I = 1 THEN ‘1’

        END CASE

   FROM DUAL;



 



89   Cursor FOR Loops

1   DECLARE

 2      CURSOR occupancy_cur IS

 3         SELECT pet_id, room_number

 4           FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);

 5      occupancy_rec occupancy_cur%ROWTYPE;

 6   BEGIN

 7      OPEN occupancy_cur;

 8      
         
         
          
          LOOP
         
         

 9         FETCH occupancy_cur INTO occupancy_rec;

10         EXIT WHEN occupancy_cur%NOTFOUND;

11         update_bill

12             (occupancy_rec.pet_id, occupancy_rec.room_number);

13       END 
         
         
          
          LOOP
         
         ;

14       CLOSE occupancy_cur;

15    END;



 



    DECLARE

       CURSOR occupancy_cur IS

          SELECT pet_id, room_number

            FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);

    BEGIN

       FOR occupancy_rec IN occupancy_cur

       
         
         
          
          LOOP
         
         

          update_bill(occupancy_rec.pet_id, occupancy_rec.room_number);

       END 
         
         
          
          LOOP
         
         ;

    END;



 



90   
         
         
          
          Loop
         
          Labels

    <
         
         
          
          >
         
         

    FOR year_number IN 1800..1995

    
         
         
          
          LOOP
         
         

       <
         
         
          
          >
         
         

       FOR month_number IN 1 .. 12

       
         
         
          
          LOOP
         
         

          IF year_loop.year_number = 1900 THEN ... END IF;

       END LOOP month_loop;

    END LOOP year_loop;



 



You can, in fact, stop the execution of a specific named outer loop by adding a loop label after the EXIT keyword in the EXIT statement of a loop,

    EXIT loop_label;

    EXIT loop_label WHEN condition;



 



91   Use Understandable Names for 
         
         
          
          Loop
         
          Indexes

    FOR focus_account IN start_id .. end_id

    
         
         
          
          LOOP
         
         

       FOR day_in_week IN 1 .. 7

       
         
         
          
          LOOP
         
         

          FOR month_in_biyear IN 1 .. 24

          
         
         
          
          LOOP
         
         

             build_schedule(focus_account,day_in_week,month_in_biyear);

          END 
         
         
          
          LOOP
         
         ;

       END 
         
         
          
          LOOP
         
         ;

    END 
         
         
          
          LOOP
         
         ;



 



92   Obtaining Information About FOR Loop Execution

    DECLARE

       book_count PLS_INTEGER;

    BEGIN

       FOR book_rec IN books_cur (author_in => 'FEUERSTEIN,STEVEN')

       
         
         
          
          LOOP
         
         

          ... process data ...

          book_count := books_cur%ROWCOUNT;

       END 
         
         
          
          LOOP
         
         ;

       IF book_count > 10 THEN ...



 



93   ORACLE TEMPORARY TABLES

Creates a temporary table which can be transaction specific or session specific.



 



Oriented transaction:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (

  column1  NUMBER,

  column2  NUMBER

) ON COMMIT DELETE ROWS;

Oriented session:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (

  column1  NUMBER,

  column2  NUMBER

) ON COMMIT PRESERVE ROWS;



 



Micellaneous Features 

  





If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions. 

Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally. 

Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session. 

Views can be created against temporary tables and combinations of temporary and permanent tables. 

Tempoarary tables can have triggers associated with them. 

Export and Import utilities can be used to transfer the table definitions, but no data rows are processed. 

There are a number of restrictions related to temporary tables but these are version specific. 



 



Multi-session scenarios:

1- Two different users try to create a global temporary table with the same name

Both tables get created. Each user sees his/her own table.  Table definition is oriented to the user. 

    

  




2- Two sessions by a single user try to create a global temporary table with the same name.

Once the table has been created in one of the sessions, the table exists and an error is given to the second session when trying to create the table. This behavior occurs whether or not the table structure is defined to be the same in both sessions.

3- Two sessions by a single user insert rows into a global temporary table.

Each session can insert rows into the table, no matter which session created the table. The data is oriented to the session. 

    

  




4- Two sessions by the same user select from a global temporary table.

A given session will only see the rows that it has inserted, as if the table was private to that session.

5- A session tries to alter a temporary table that another session is also using.

Columns can not be added/deleted/modified as long as the table is bound to a session.

Renaming, however, can be done even when another session has rows in the table.

6- A session attempts to rename a global temporary table in which another session has inserted some rows

The table gets renamed and rows are preserved. After the rename has taken place, both sessions must use the new name to refer to that table. 



 



94   Schema-level collection type

Similarly to object types, you should translate any program-specific type requirement into something generic and reusable. As a starting point, the following should prove useful in any application:

CREATE OR REPLACE TYPE NUMBER_TT AS TABLE OF NUMBER(38,10)

/

CREATE OR REPLACE TYPE INTEGER_TT AS TABLE OF INTEGER

/

CREATE OR REPLACE TYPE DATE_TT AS TABLE OF DATE

/

CREATE OR REPLACE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(4000)

/

CREATE OR REPLACE TYPE TIMESTAMP_TT AS TABLE OF TIMESTAMP



 



95   Use Boolean Elements to Improve Readability

Boolean variables and functions can greatly improve readability of programs.



 





 





 



96   Native SQL Versus DBMS_SQL

1) DBMS_SQL is Supported in Client-Side Programs



 



The DBMS_SQL package is supported in client-side programs, but native dynamic SQL is not. Every call to the DBMS_SQL package from the client-side program translates to a PL/SQL remote procedure call (RPC); these calls occur when you need to bind a variable, define a variable, or execute a statement.



 



2) DBMS_SQL Supports DESCRIBE



 



The DESCRIBE_COLUMNS procedure in the DBMS_SQL package can be used to describe the columns for a cursor opened and parsed through DBMS_SQL. This feature is similar to the DESCRIBE command in SQL*Plus. Native dynamic SQL does not have a DESCRIBE facility.



 



3) DBMS_SQL Lets You Reuse SQL Statements



 



The PARSE procedure in the DBMS_SQL package parses a SQL statement once. After the initial parsing, you can use the statement multiple times with different sets of bind arguments.



 



Native dynamic SQL prepares a SQL statement each time the statement is used, which typically involves parsing, optimization, and plan generation. Although the extra prepare operations incur a small performance penalty, the slowdown is typically outweighed by the performance benefits of native dynamic SQL



 



97  authid current_user vs pragma authid Definer 
    

  

A stored procedure runs either with the rights of the caller (authid current_user) or with the rights of the procedure's owner (authid definer). This behaviour is specified with the authid clause. This authid clause immediatly follows the create procedure, create function, create package or create type statement. It can be ommited, in which case the default authid definer is taken.



 



userA:

                p_current_user: display ‘current_userA’

                p_definer: display ‘definer userA’



 



                Table(T,U)          Procedure(p_current_user, p_definer)



 



userB:

                p_current_user: display ‘current_userB’

                p_definer: display ‘definer userB’



 



                Table(T,U)          Procedure(p_current_user,p_definer)



 



userA call userB.p_current_user:              Result: display ‘current_userA’

userA call userB.p_definer:         Result: display ‘definer userB’



 





 




        
        
 
Privilege  
 
 
 
 
Code Schema  
 
 
 
(grant run to User Scehma)  
 
 
 
 
User Schema  
 
 
 
(Exec code schema package)  
 
 
 
 
Define
 
 
 
 
 
 
 
Run under Code schema
 
 
Current_User
 
 
 
 
 
 
 
Run under User schema
 



 



98   Oct convert Hex

8i version above



 



to_char(100,'XX')

to_number('4D','XX')



 



99   Retrieve from N to M rows

SELECT * 

  
  FROM (SELECT ROWNUM id, 

  
               t.* 

  
          FROM sp_output t 

  
         WHERE ROWNUM <= 30) 

  
 WHERE id > 20 

  
 ORDER BY res_seq; 

  


  


100      Create Global temporary

create global temporary tablename(column list) 
  
on commit preserve rows; --提交保留数据 会话临时表 

  
on commit delete rows; --提交删除数据 事务临时表 

  
临时表是相对于会话的，别的会话看不到该会话的数据。



 



100.1                         Tricks



 



SELECT MIN (NVL (c.total_net_rate, 0)), MIN (c.travel_date) 

  





  FROM wrk_ap_ratedtl a, 

    

  




       wrk_ap_bkdate_ratedtl b, 

    

  




       wrk_ap_bkdate c 

    

  




 WHERE c.session_id = USERENV ('SESSIONID') 

  





   AND a.wrk_ap_ratedtl_seq = b.wrk_ap_ratedtl_seq 

  





   AND c.wrk_ap_bkdate_seq = b.wrk_ap_bkdate_seq 

  





   AND c.res_seq = ii_res_seq 

  





   AND c.pax_prod_group_num_seq = ii_pax_prod_group_seq 

  





   AND a.rate_type_cd != 'CHILD' 

  





   AND c.min_flag IS NULL; 

  







 



Because wrk_ap_ratedtl, wrk_ap_bkdate_ratedtl, wrk_ap_bkdate is GLOBAL TEMPORARY, if want to link each other, we must do ‘COMMIT’ when inserting data, otherwise, the linkage will be failed.



 



101      How to exchange Row,Column

1、固定列数的行列转换


  
student subject grade 

  
--------------------------- 

  
student1 语文 80 

  
student1 数学 70 

  
student1 英语 60 

  
student2 语文 90 

  
student2 数学 80 

  
student2 英语 100 

  
…… 

  
转换为 

  
语文 数学 英语 

  
student1 80 70 60 

  
student2 90 80 100 

  
…… 

  
语句如下： 

  
select student, 

  
sum(decode(subject,'语文', grade,null)) "语文", 

  
sum(decode(subject,'数学', grade,null)) "数学", 

  
sum(decode(subject,'英语', grade,null)) "英语" 

  
from table 

  
group by student



 



102      SYS_CONTEXT

利用以下的查询，你就明白了 

  
select 

  
SYS_CONTEXT('USERENV','TERMINAL') terminal, 

  
SYS_CONTEXT('USERENV','LANGUAGE') language, 

  
SYS_CONTEXT('USERENV','SESSIONID') sessionid, 

  
SYS_CONTEXT('USERENV','INSTANCE') instance, 

  
SYS_CONTEXT('USERENV','ENTRYID') entryid, 

  
SYS_CONTEXT('USERENV','ISDBA') isdba, 

  
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory, 

  
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency, 

  
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar, 

  
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format, 

  
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language, 

  
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort, 

  
SYS_CONTEXT('USERENV','CURRENT_USER') current_user, 

  
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid, 

  
SYS_CONTEXT('USERENV','SESSION_USER') session_user, 

  
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid, 

  
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user, 

  
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid, 

  
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain, 

  
SYS_CONTEXT('USERENV','DB_NAME') db_name, 

  
SYS_CONTEXT('USERENV','HOST') host, 

  
SYS_CONTEXT('USERENV','OS_USER') os_user, 

  
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name, 

  
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address, 

  
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol, 

  
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id, 

  
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id, 

  
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type, 

  
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data 

  
from dual



 



103      OUTPUT parameter by raise

When raise meets in executing, it will reset the output or in output parameter.

PROCEDURE raise_output_parameter( 

  
    in_char                         IN  VARCHAR2, 

  
    is_errhandler                   IN  VARCHAR2, 

  
    on_integer                      OUT INTEGER) 

  
AS 

  
BEGIN 

  
    on_integer := 100; 

  


  
    RAISE NO_DATA_FOUND; 

  
    

  
EXCEPTION 

  
    WHEN NO_DATA_FOUND THEN 

  
        on[B13] _integer := pkg_com.cv_FAILED; 

  
        pkg_err.p_handle_code (vs_section||cs_OBJECT_NAME, 

  
                               pkg_com.cn_NO_DATA_FOUND, 

  
                               vs_exception_message, 

  
                               is_errhandler); 

  
        IF (is_errhandler = pkg_err.cv_APP_ERR) THEN 

  
            RAISE; 

  
        END IF; 

  
END raise_output_parameter; 

  


  


104      FOR IN CURSOR tricky

CURSOR cur_pax( 

  
        in_cpo_res_seq              cpo_res.cpo_res_seq%TYPE) 

  
    IS 

  
        SELECT pax.* 

  
          FROM cpo_pax  pax 

  
         WHERE pax.cpo_res_seq = in_cpo_res_seq; 

  


  
    CURSOR cur_pax_pre( 

  
        in_cpo_res_seq              cpo_res.cpo_res_seq%TYPE) 

  
    IS 

  
        SELECT pax.* 

  
          FROM cpo_pax  pax 

  
         WHERE pax.cpo_res_seq = in_cpo_res_seq; 

  


  
    <<CUR_EXIT>> 

  
    FOR lp_cur_row IN cur_pax(ir_cur_cpo_res.cpo_res_seq) 

  
    LOOP 

  





// must use different cursor name 
      
        FOR lp_pre_row IN cur_pax_pre(ir_pre_cpo_res.cpo_res_seq) 

  
        LOOP 

  
        NEXT

NEXT



 



105      date DD-MON-YY vs DD-MON-RRRR



 



YY implies the current century while 
  
RR implies the Previous century 

  


  
For example: (assuming table=Test_table, column=date1) 

  
insert into test_table (date1) 

  
values (to_date('02/01/95','MM/DD/YY')); will insert Feb, 1, 2095 into the table while 

  


  
insert into test_table (date1) 

  
values (to_date('02/01/95','MM/DD/RR')); will insert Feb, 1, 1995



 



106      Order | Group By 1,2,3

Don’t use order by 1,2,3… because the column name could be shifted in the near future, it will be hard to search and refactoring.



 



107      The PL/SQL compiler

The PL/SQL compiler uses Diana (for Descriptive Intermediate Attributed Notation for Ada) for its internal representation, reflecting PL/SQL’s historical debt to the 
          
          
           
            
            Ada 
           
          
           language



 



107.1                        The Front-end

It will check syntax and semantic correctness, if it fails, it will print out.



 



107.2                        The Back-end compile(the code generator)

The compiler back-end consumes the Diana and generates an executable representation of the program in the machine code of the target machine, The terms Diana and MCode are both used occasionally in Oracle documentation 

  







  
 




The compiler back-end plays a critical role in generating fast run-time code



 



107.3                        The PLSQL virtual machine

Each of PL/SQL and Java are compiled to run on its own specific virtual machine. And this is for the same reason: to simplify the task of implementing the language on a new hardware platform.



 



107.4                        Native compilation of PL/SQL

The back-end can generate its output code in two different representations. In both cases it consumes the Diana for the current PL/SQL unit and computes the MCode. In the interpreted mode, it simply stores the MCode in system-managed structures in the SYS schema. In the native mode, it translates the MCode into C source code with the same semantics. (The MCode is not retained in this mode.)



 




         
         
A PL
/SQL unit will execute faster when compiled native than when compiled interpreted precisely because of the increased efficiency of the housekeeping tasks. 
  







  
 




107.5                        Run time considerations

The object code for a PL/SQL unit (whether MCode or a native DLL) is fetched on demand according to execution flow. The two flavors of object code are fully interoperable, but there is a performance cost for the context switch between the two execution modes. Thus the greatest benefits from native compilation will be seen when both the caller and the called PL/SQL unit are compiled native, even when the real work each or both does is trivial.



 



108      How to make PL/SQL programs run faster


        
        
The best way to improve PL/SQL program performance is to choose the right algorithms and data structures in the first place! 



 




        
        
A second way to improve performance is to use the new language features provided in each release. For example, index-by-varchar2 associative arrays (new in Oracle9iR2) execute much more quickly than any alternative mechanisms that solve the same problems. 



 



109      What changes were made in Oracle10g?

v      the binary_float and binary_double datatypes (the IEEE datatypes).

v      the regexp_like, regexp_instr, regexp_substr and regexp_replace builtins to support regular expression manipulation with standard POSIX syntax.

v      multiset operations on nested table instances supporting operations like equals, union, intersect, except, member, and so on.

v      The user-defined quote character.

v      INDICES OF and VALUES OF syntax for FORALL.

v      The distinction between binary_integer and pls_integer vanishes.



 





 



v      plsql_optimize_level and has the allowed values 2, 1 and 0. Level 2 means apply most effort and it does therefore imply slightly longer compilation times. Nevertheless it is the default. You can check

SELECT * FROM ALL_PLSQL_OBJECT_SETTINGS



 



v      In Oracle10g, the DLL is stored canonically in the database and is cached as a file on an operating system directory only on demand.



 



110      Update Multiple Tables

UPDATE messages 
    
   SET dbcode = (SELECT dbcode 
    
                   FROM db_error_code 

    
                  WHERE db_error_code.msgid = messages.msgid); 
    


    


  





111      Dynamic IN Clause with Native Dynamic SQL 
    

  

CREATE TABLE in_clause_tab ( 

  





   ID INTEGER, 

  





   title VARCHAR2(100), 

  





   description VARCHAR2(2000)); 

  







  
 




CREATE TYPE in_clause_ot AS OBJECT ( 

  





   ID            INTEGER 

  





 , title         VARCHAR2 (100) 

  





 , description   VARCHAR2 (2000) 

  





); 

  







  
 




CREATE OR REPLACE TYPE in_clause_tab_nt IS TABLE OF in_clause_ot; 

  







  
 




CREATE OR REPLACE TYPE pky_nt IS TABLE OF INTEGER; 

  






        
        

  
         
         


    
         
         
 
  
         
         


  
         
         


        
        



  
 




FUNCTION nds_list(list_in IN VARCHAR2) RETURN SYS_REFCURSOR IS 

  





    retval SYS_REFCURSOR; 

  





BEGIN 

  





    OPEN retval FOR 'SELECT * FROM in_clause_tab WHERE id IN (' || list_in || ')'; 

  







  
 




    RETURN retval; 

  





END nds_list; 

  






        
        

  
         
         


    
         
         
 
  
         
         


  
         
         


        
        



    
 

  




FUNCTION nds_bulk_list(list_in IN VARCHAR2) RETURN in_clause_tab_nt IS 

  





    retval in_clause_tab_nt; 

  





BEGIN 

  





    EXECUTE IMMEDIATE 'SELECT in_clause_ot (id, title, description) 

  





             FROM in_clause_tab WHERE id IN (' || list_in || ')' BULK COLLECT 

  





        INTO retval; 

  







  
 




    RETURN retval; 

  





END nds_bulk_list; 

  







  
 




FUNCTION nds_bulk_list2(list_in IN VARCHAR2) RETURN SYS_REFCURSOR IS 

  





    retval SYS_REFCURSOR; 

  





BEGIN 

  





    OPEN retval FOR 

  





        SELECT ic.ID, 

  





               ic.title, 

  





               ic.description 

  





          FROM TABLE(nds_bulk_list(list_in)) ic; 

  







  
 




    RETURN retval; 

  





END nds_bulk_list2; 

  






        
        

  
         
         


    
         
         
 
  
         
         


  
         
         


        
        



    
 

  




FUNCTION dbms_sql_list(list_in IN VARCHAR2) RETURN in_clause_tab_nt 

  





IS 

  





      l_query      VARCHAR2 (32767) 

  





                            := 'SELECT * FROM in_clause_tab WHERE id IN ('; 

  





      l_cur        PLS_INTEGER             := DBMS_SQL.open_cursor; 

  





      l_feedback   PLS_INTEGER; 

  





      l_ids        pky_nt                  := pky_nt (); 

  





      l_row        PLS_INTEGER; 

  





      l_onerow     in_clause_tab%ROWTYPE; 

  





      retval       in_clause_tab_nt        := in_clause_tab_nt (); 

  







  
 




      PROCEDURE string_to_list ... see file for details... 

  





      END string_to_list; 

  





BEGIN 

  





   -- Parse the delimited list to the collection. 

  





   string_to_list(list_in, l_ids); 

  





   l_row := l_ids.FIRST; 

  





   

  





   -- Build the list of bind variables. 

  





   WHILE (l_row IS NOT NULL) 

  





   
          
          
           
           LOOP
          
           

  





       l_query := l_query || ':bv' || l_row || ','; 

  





       l_row   := l_ids.NEXT(l_row); 

  





   END 
          
          
           
           LOOP
          
          ; 

  





   l_query := RTRIM(l_query, ',') || ')'; 

  





   

  





   -- Define the columns to be queried. 

  





   DBMS_SQL.parse(l_cur, l_query, DBMS_SQL.native); 

  





   DBMS_SQL.define_column(l_cur, 1, 1); 

  





   DBMS_SQL.define_column(l_cur, 2, 'a', 100); 

  





   DBMS_SQL.define_column(l_cur, 3, 'a', 2000); 

  





   

  





   -- Bind each variable in the provided list. 

  





   l_row := l_ids.FIRST; 

  





   WHILE (l_row IS NOT NULL) 

  





   
          
          
           
           LOOP
          
           

  





       DBMS_SQL.bind_variable(l_cur, ':bv' || l_row, l_ids(l_row)); 

  





       l_row := l_ids.NEXT(l_row); 

  





   END 
          
          
           
           LOOP
          
          ; 

  





   

  





   -- Execute and then fetch each row. 

  





   l_feedback := DBMS_SQL.EXECUTE(l_cur); 

  





   
          
          
           
           LOOP
          
           

  





       l_feedback := DBMS_SQL.fetch_rows(l_cur); 

  





       EXIT WHEN l_feedback = 0; 

  





       

  





       -- Retrieve individual column values and move them to the nested table. 

  





       DBMS_SQL.column_value(l_cur, 1, l_onerow.ID); 

  





       DBMS_SQL.column_value(l_cur, 2, l_onerow.title); 

  





       DBMS_SQL.column_value(l_cur, 3, l_onerow.description); 

  





       retval.EXTEND; 

  





       retval(retval.LAST) := in_clause_ot(l_onerow.ID, l_onerow.title, l_onerow.description); 

  





   END 
          
          
           
           LOOP
          
          ; 

  





   

  





   DBMS_SQL.close_cursor(l_cur); 

  





   RETURN retval; 

  





END dbms_sql_list; 

  







  
 




112      SQL%ROWCOUNT

BEGIN 

  





   DBMS_OUTPUT.put_line ('SQL%ROWCOUNT inside CFL?'); 

  





   FOR rec IN (SELECT * FROM otn_question) 

  





   
          
          
           
           LOOP
          
           

  





      DBMS_OUTPUT.put_line ('   SQL%ROWCOUNT = ' || NVL (TO_CHAR(SQL%ROWCOUNT), 'NULL')); 

  





   END 
          
          
           
           LOOP
          
          ; 

  





END; 

  







 



: SQL%ROWCOUNT inside CFL? 

  





   SQL%ROWCOUNT = NULL 

  





   SQL%ROWCOUNT = NULL 

  






        
        

  
         
         


  
         
         
 

  
         
         


        
        



 



BEGIN 

  





   UPDATE otn_question SET description = UPPER (description); 

  





   DBMS_OUTPUT.put_line ('SQL%ROWCOUNT after update, before CFL = '||SQL%ROWCOUNT); 

  







  
 




   FOR rec IN (SELECT * FROM otn_question) 

  





   
          
          
           
           LOOP
          
           

  





      DBMS_OUTPUT.put_line ('   SQL%ROWCOUNT = ' || NVL (TO_CHAR(SQL%ROWCOUNT), 'NULL')); 

  





   END 
          
          
           
           LOOP
          
          ; 

  





   DBMS_OUTPUT.put_line ('SQL%ROWCOUNT after update, after CFL = '||SQL%ROWCOUNT); 

  





END; 

  







 



SQL%ROWCOUNT after update, before CFL = 2 

  





   SQL%ROWCOUNT = 2 

  





   SQL%ROWCOUNT = 2 

  





SQL%ROWCOUNT after update, after CFL = 2 

  






        
        

  
         
         


  
         
         
 

  
         
         


        
        



 



DECLARE 

  





   CURSOR otn_question_cur 

  





   IS 

  





      SELECT * 

  





        FROM otn_question; 

  





BEGIN 

  





   DBMS_OUTPUT.put_line ('otn_question_cur%ROWCOUNT inside CFL'); 

  





   FOR rec IN otn_question_cur 

  





   
          
          
           
           LOOP
          
           

  





      DBMS_OUTPUT.put_line ('   otn_question_cur%ROWCOUNT = ' || NVL (TO_CHAR(otn_question_cur%ROWCOUNT), 'NULL')); 

  





   END 
          
          
           
           LOOP
          
          ; 

  





END; 

  







  
 




:otn_question_cur%ROWCOUNT inside CFL 

  





   otn_question_cur%ROWCOUNT = 1 

  





   otn_question_cur%ROWCOUNT = 2 

  






        
        

  
         
         


  
         
         
 

  
         
         


        
        



 



DECLARE 

  





   TYPE otn_question_tc IS TABLE OF otn_question%ROWTYPE 

  





      INDEX BY PLS_INTEGER; 

  







  
 




   l_otn_questions otn_question_tc; 

  





BEGIN 

  





   DBMS_OUTPUT.put_line ('SQL%ROWCOUNT after BULK COLLECT'); 

  





   SELECT * 

  





   BULK COLLECT INTO l_otn_questions 

  





     FROM otn_question; 

  





   DBMS_OUTPUT.put_line ('   SQL%ROWCOUNT = ' || NVL (TO_CHAR(SQL%ROWCOUNT), 'NULL')); 

  





   DBMS_OUTPUT.put_line ('   Collection count = ' || NVL (TO_CHAR(l_otn_questions.COUNT), 'NULL')); 

  





END; 

  







  
 




SQL%ROWCOUNT after BULK COLLECT 

  





   SQL%ROWCOUNT = 2 

  






        
        

  
         
         
   Collection count = 2 

    
         
         

  
         
         


  
         
         


        
        



 



113      Why no CONTINUE command in PL/SQL?

<
          
          
           
           >for ... loop 
          
          

  





  ... 

  





  <
          
          
           
           >for ... loop 
          
          

  





    ... 

  





    -- exit both loops 

  





    exit outer when ...; 

  





  end loop inner; 

  





  ... 

  





end loop outer; 

  







 





 



<
          
          
           
           >for ... loop 
          
          

  





  ... 

  





  <
          
          
           
           >for ... loop 
          
          

  





    ... 

  





    -- immediately start the next iteration of the outer loop 

  





    continue outer when ...; 

  





  end loop inner; 

  





  ... 

  





end loop outer; 

  







 



114      prefix,  suffix for type

t              -              type, subtype

rt             -              row type

tc            -              table collection

cc            -              column collection



 



115      build-in package

v      DBMS_ASSERT (QUALIFIED_SQL_NAME,  SQL_OBJECT_NAME,  ENQUOTE_NAME, ENQUOTE_LITERAL)

v      

 





 



SET SERVEROUTPUT ON SIZE UNLIMITED



 



116      Out Join Trick

    SELECT DISTINCT ii.invoice_seq, 

  





           ii.invoice_item_seq, 

  





           ii.booking_item_seq, 

  





           ii.package_seq, 

  





           iic.invoice_item_comm_seq 

  





      FROM invoice_item ii, 

  





           invoice_item_comm iic 

  





     WHERE ii.invoice_seq = cn_invoice_seq 

  





       AND ii.invoice_item_seq = iic.invoice_item_seq(+) 

  





       AND NVL(ii.reversal_flag, 'N') = 'N' 

  





       AND NVL(iic.reversal_flag(+), 'N') = 'N' 

  





     ORDER BY ii.invoice_seq, 

  





              ii.invoice_item_seq, 

  





              iic.invoice_item_comm_seq; 

  







 



Or

       AND iic.reversal_flag(+) IS NULL 

  







 





 



117      Inline functions 


        
        
PL/SQL within SQL 
 
Purity levels 
 
Restrictions 
 

        
        



 



118      DBMS_SQL.VARCHAR2A



 



This collection type is defined in the DBMS_SQL package as follows:

type varchar2a is table of varchar2(32767) 

  





   index by binary_integer; 

  







 



TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER; 

  







  
 




TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 

  







  
 




type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER; 

  







  
 




119      Cursor best practices

Asked by Ajendra Narayansamal from Unknown Location on Tuesday, March 8, 2005 

  





Question: 
    
I have two questions regarding best practices: 

  





1. Which of the following methods is most efficient for a cursor: 
    
  a. Declaring a cursor 

    
  b. Using cursor FOR-LOOP 

    
  c. Bulk collect and then loop through the collection. 

  





2. For best performance should I use capital letters for table names and keywords? 

  





Answer: 
    
Without a doubt, you should use BULK COLLECT whenever possible to query information from your tables. It will be significantly faster than either a cursor FOR loop or loop with explicit cursor. Be aware, however, that a BULK COLLECT of many rows can consume a large amount of memory. You can balance memory utilization against performance improvements by using the LIMIT clause with BULK COLLECT. Here's an example: 

  





DECLARE 

  





   CURSOR allrows_cur IS 

  





      SELECT * FROM employee 

  





      ; 

  





   TYPE employee_aat IS TABLE OF employee%ROWTYPE 

  





      INDEX BY BINARY_INTEGER; 

  





   l_employee employee_aat; 

    

  




   l_row PLS_INTEGER; 

  





BEGIN 

  





   OPEN allrows_cur; 

  





   
          
          
           
           LOOP
          
           

  





      FETCH allrows_cur BULK COLLECT INTO l_employee 

  





        LIMIT 100; 

      

    




  

  





      -- Remember: BULK COLLECT will NOT raise NO_DATA_FOUND if no rows 

  





      -- are queried. Instead, check the contents of the collection to 

  





      -- see if you have anything left to process. 

  





      EXIT WHEN l_employee.COUNT = 0; 

  





  

  





      -- Process the data, if any. 

  





      l_row := l_employee.FIRST; 

  





      WHILE (l_row IS NOT NULL) 

  





      
          
          
           
           LOOP
          
           

  





         process_employee (l_employee(l_row)); 

  





  

  





         l_row := l_employee.NEXT (l_row); 

  





      END 
          
          
           
           LOOP
          
          ; 

  





  

  





   END 
          
          
           
           LOOP
          
          ; 

  





  

  





   -- Clean up when done: close the cursor and delete everything 

  





   -- in the collection. 

  





   CLOSE allrows_cur; 

  





   l_employee.DELETE; 

    

  




END; 

  





As far as your question regarding the formatting for key words in your code: I feel strongly that it doesn't matter in the least what coding style you follow. In fact, write your code any old way you want—make a total mess of it! Then take advantage of the automatic formatting capabilities of your PL/SQL IDE to instantly reformat the code to fit whatever your preferences or group standards dictate. If your IDE does not offer a PL/SQL "beautifier" (aka, formatter, pretty-printer, etc.), then you should switch to a tool that does provide this feature. I can't imagine developing code without it. 

  





One of the side effects of beautification is that SQL statements that are identical logically, but different physically, will be reformatted to the same, physical statement. As a consequence, when these statements are run, the parse operation will occur just once (a long-standing optimization of the Oracle RDBMS). 

  





Fortunately, you don't need a beautifier to achieve this optimization in Oracle 10g PL/SQL and in Oracle9i Release 2 9.2.0.5 and later. The PL/SQL compilers in these release both automatically reformat static SQL into a standard or canonical form to avoid unnecessary parsing. (Oracle8i did this as well, but early Oracle9i releases did not.) 

  





  




 



120      Oracle Built-In Functions 

View an Alphabetical Listing of Functions


        
        
 
Character / String Functions:  
 
 
 
 
 
 
 
 
 
 
Ascii  
 
 
 
 
Convert  
 
 
 
 
Lower  
 
 
 
 
Soundex  
 
 
 
 
 
 
 
 
 
 
AsciiStr  
 
 
 
 
Decompose  
 
 
 
 
Lpad  
 
 
 
 
Substr  
 
 
 
 
 
 
 
 
 
 
Chr  
 
 
 
 
Dump  
 
 
 
 
Ltrim  
 
 
 
 
Translate  
 
 
 
 
 
 
 
 
 
 
Compose  
 
 
 
 
Initcap  
 
 
 
 
Replace  
 
 
 
 
Trim  
 
 
 
 
 
 
 
 
 
 
Concat  
 
 
 
 
Instr  
 
 
 
 
Rpad  
 
 
 
 
Upper  
 
 
 
 
 
 
 
 
 
 
Concat with ||  
 
 
 
 
Length  
 
 
 
 
Rtrim  
 
 
 
 
VSize  
 
 
 



 




        
        
 
Conversion Functions:  
 
 
 
 
 
 
 
 
 
 
Bin_To_Num  
 
 
 
 
NumToDSInterval  
 
 
 
 
To_DSInterval  
 
 
 
 
To_Single_Byte  
 
 
 
 
 
 
 
 
 
 
Cast  
 
 
 
 
NumToYMInterval  
 
 
 
 
To_Lob  
 
 
 
 
To_Timestamp  
 
 
 
 
 
 
 
 
 
 
CharToRowid  
 
 
 
 
To_Char  
 
 
 
 
To_Multi_Byte  
 
 
 
 
To_Timestamp_Tz  
 
 
 
 
 
 
 
 
 
 
From_Tz  
 
 
 
 
To_Clob  
 
 
 
 
To_NClob  
 
 
 
 
To_YMInterval  
 
 
 
 
 
 
 
 
 
 
HexToRaw  
 
 
 
 
To_Date  
 
 
 
 
To_Number  
 
 
 
 
 
 
 
 
 



 




        
        
 
Advanced Functions:  
 
 
 
 
 
 
 
 
 
 
BFilename  
 
 
 
 
Group_ID  
 
 
 
 
NULLIF  
 
 
 
 
User  
 
 
 
 
 
 
 
 
 
 
Cardinality  
 
 
 
 
Lag  
 
 
 
 
NVL  
 
 
 
 
UserEnv  
 
 
 
 
 
 
 
 
 
 
Case Statement  
 
 
 
 
Lead  
 
 
 
 
NVL2  
 
 
 
 
   
 
 
 
 
 
 
 
 
 
 
Coalesce  
 
 
 
 
LNNVL  
 
 
 
 
Sys_Context  
 
 
 
 
   
 
 
 
 
 
 
 
 
 
 
Decode  
 
 
 
 
NANVL  
 
 
 
 
Uid  
 
 
 
 
   
 
 
 



 




        
        
 
Mathematical Functions:  
 
 
 
 
 
 
 
 
 
 
Abs  
 
 
 
 
Covar_pop  
 
 
 
 
Max  
 
 
 
 
Sqrt  
 
 
 
 
 
 
 
 
 
 
Acos  
 
 
 
 
Covar_samp  
 
 
 
 
Median  
 
 
 
 
StdDev  
 
 
 
 
 
 
 
 
 
 
Asin  
 
 
 
 
Count  
 
 
 
 
Min  
 
 
 
 
Sum  
 
 
 
 
 
 
 
 
 
 
Atan  
 
 
 
 
Cume_Dist  
 
 
 
 
Mod  
 
 
 
 
Tan  
 
 
 
 
 
 
 
 
 
 
Atan2  
 
 
 
 
Dense_Rank  
 
 
 
 
Power  
 
 
 
 
Tanh  
 
 
 
 
 
 
 
 
 
 
Avg  
 
 
 
 
Exp  
 
 
 
 
Rank  
 
 
 
 
Trunc (numbers)  
 
 
 
 
 
 
 
 
 
 
Bin_To_Num  
 
 
 
 
Extract  
 
 
 
 
Remainder  
 
 
 
 
Trunc (dates)  
 
 
 
 
 
 
 
 
 
 
BitAnd  
 
 
 
 
Floor  
 
 
 
 
Round (numbers)  
 
 
 
 
Var_pop  
 
 
 
 
 
 
 
 
 
 
Ceil  
 
 
 
 
Greatest  
 
 
 
 
Round (dates)  
 
 
 
 
Var_samp  
 
 
 
 
 
 
 
 
 
 
Corr  
 
 
 
 
Least  
 
 
 
 
Sign  
 
 
 
 
Variance  
 
 
 
 
 
 
 
 
 
 
Cos  
 
 
 
 
Ln  
 
 
 
 
Sin  
 
 
 
 
   
 
 
 
 
 
 
 
 
 
 
Cosh  
 
 
 
 
Log  
 
 
 
 
Sinh  
 
 
 
 
   
 
 
 



 




        
        
 
Date Functions:  
 
 
 
 
 
 
 
 
 
 
Add_Months  
 
 
 
 
Last_Day  
 
 
 
 
Round  
 
 
 
 
To_Date  
 
 
 
 
 
 
 
 
 
 
Current_Date  
 
 
 
 
LocalTimestamp  
 
 
 
 
SessionTimeZone  
 
 
 
 
Trunc  
 
 
 
 
 
 
 
 
 
 
Current_Timestamp  
 
 
 
 
Months_Between  
 
 
 
 
Sysdate  
 
 
 
 
Tz_Offset  
 
 
 
 
 
 
 
 
 
 
DbTimeZone  
 
 
 
 
New_Time  
 
 
 
 
SysTimestamp  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
From_Tz  
 
 
 
 
Next_Day  
 
 
 
 
To_Char  
 
 
 
 
 
 
 
 
 



 




        
        
 
Error Functions:  
 
 
 
 
 
 
 
 
 
 
SQLCODE  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
SQLERRM  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 



 





 



121      DBMS_XPLAN package

  function display(table_name   varchar2      default 'PLAN_TABLE', 

  





                   statement_id varchar2      default null, 

  





                   format       varchar2      default 'TYPICAL', 

  





                   filter_preds varchar2      default null) 

  





  return dbms_xplan_type_table 

  





  pipelined; 

    

  






  
 




  -- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL) 

  





  function display_cursor(sql_id           varchar2 default  null, 

  





                          cursor_child_no  integer  default  0, 

  





                          format           varchar2 default  'TYPICAL') 

  





  return dbms_xplan_type_table 

  





  pipelined; 

    

  






  
 




  -- display from AWR 

  





  function display_awr(sql_id          varchar2, 

  





                       plan_hash_value integer  default null, 

  





                       db_id           integer  default null, 

  





                       format          varchar2 default 'TYPICAL') 

  





  return dbms_xplan_type_table 

  





  pipelined; 

    

  






  
 




  -- display from SQL tuning set 

  





  function display_sqlset(sqlset_name     varchar2, 

  





                          sql_id          varchar2, 

  





                          plan_hash_value integer  default null, 

  





                          format          varchar2 default 'TYPICAL', 

  





                          sqlset_owner    varchar2 default null) 

  





  return dbms_xplan_type_table 

  





  pipelined; 

    

  






  
 




  -- private procedure, used internally 

  





  function  prepare_records(plan_cur        IN sys_refcursor, 

  





                            i_format_flags  IN binary_integer) 

  





  return dbms_xplan_type_table 

  





  pipelined; 

    

  






 



For example:

1.       Explain plain for select * from DUAL;



 



2.       Select * from table(dbms_xplan.display());



 



Execute Plan Table:

                C:/Oracle/product/10.2.0/client_1/RDBMS/ADMIN/utlxplp.sql 



 



Create Plan Table:

                C:/Oracle/product/10.2.0/client_1/RDBMS/ADMIN/catplan.sql 

122      Function pipelined declare

PIPELINED specifies to return the results of a table function iteratively. A table function returns a collection type (a nested table or varray) with elements that are SQL datatypes. You can query table functions using the TABLE keyword before the function name in the FROM clause of a SQL query 

  







 



FUNCTION f_return_sqltable 

  





       RETURN bxu_test3.TABLETYPE_RESERVATION[BX14]  PIPELINED 

  





AS 

  





       vt_res                            bxu_test3.TABLETYPE_RESERVATION; 

  





       vr_res                            bxu_test3.ROWTYPE_RESERVATION; 

  





BEGIN 

  







  
 




      SELECT * 

  





      BULK COLLECT INTO vt_res 

  





      FROM reservation; 

  







  
 




       FOR i IN vt_res.first..vt_res.last 

  





       
          
          
           
           LOOP
          
           

  





              vr_res.reservation_seq := vt_res(i).reservation_seq; 

  





              PIPE ROW(vr_res); 

  





       END 
          
          
           
           LOOP
          
          ; 

  





    

  





RETURN; 

  





          

  





END f_return_sqltable; 

  







  
 




Usage:

                SELECT *

                   FROM TABLE(package_name.f_return_sqltable())



 



123      UPDATE 时, 如何避免数据定位处理被阻塞



 



数据库PUBS中的authors表，想锁定CITY为aaa的记录，为什么执行下面的命令后，CITY为bbb的记录也被锁定了，无法进行UPDATE. 

  





BEGIN TRANSACTION     

  





    SELECT * FROM authors 

  





    WITH (HOLDLOCK) 

  





    WHERE city='aaa' 

  







 



应该不是被锁住，应该只是检索数据的时候，需要从aaa的记录扫描到bbb的记录，而aaa被锁住了，所以扫描无法往下进行，这样看起来似乎就是bbb也被锁住了



 





 



124      Oracle Server Character Sets

                AL32UTF8 ( 4 byte)



 



Client Character Sets:     WE8ISO8859P1



 



125      Obtain Oracle Domain Name

SELECT * FROM GLOBAL_NAME; 

  







 



126      User Privilege & Role

1. oracle用户查看自己的权限和角色 

  





     select * from user_tab_privs; 

  





     select * from user_role_privs; 

  





  

  





2. sys用户查看任一用户的权限和角色 

  





     select * from dba_tab_privs; 

  





     select * from dba_role_privs; 

  







  
 




127      dbms_session set context

CREATE CONTEXT publishing_application  Using my_package;



 



dbms_session.set_context('publishing_application', 'role_name', 'manager'); 
    


    
dbms_session.set_context('publishing_application', 'user_name', 'jsmith'); 

    


    
dbms_session.set_context('publishing_application', 'company', 'rampant_techpress'); 

    


    
dbms_session.set_context('publishing_application', 'role_name', 'author'); 

    


    
dbms_session.set_context('publishing_application', 'user_name', 'mault'); 

    


    
dbms_session.set_context('publishing_application', 'company', 'rampant_techpress'); 

  
 

128      Directory Object

Purpose:

                Separate logical directory and physical directory



 




        
        
DIRECTORY objects are owned by the SYS user, (even if another user creates the DIRECTORY) 
      

    
DIRECTORY names are unique across the database, (because all the directories are located in a single namespace, namely SYS). 
      

    
The DIRECTORY object permissions are NOT the same as OS permissions on the physical directory on the server file system. 
      

    

It can be used by EXTERNAL TABLE and UTL_FILE package. 

  







  
 




Usage: 

  







  
 




CREATE OR REPLACE DIRECTORY test_files AS ‘E:/oracleWork’ 

  






        
        

  
         
         
GRANT READ ON DIRECTORY test_files TO PUBLIC; 

    
         
         

  
         
         


  
         
         


        
        

CREATE TABLE emp_external 

 ( emp_id   NUMBER(4)

 , ename    VARCHAR2(12)

 , job      VARCHAR2(12) 

 , mgr_id   NUMBER(4) 

 , hiredate DATE

 , salary   NUMBER(8)

 , comm     NUMBER(8)

 , dept_id  NUMBER(2)) 

  ORGANIZATION EXTERNAL

  (TYPE oracle_loader

   DEFAULT DIRECTORY TEST_FILES         

   ACCESS PARAMETERS (records delimited BY newline fields terminated BY ',')  

    LOCATION ('emp_load.dat')

  );


        
        

  
         
         


  
         
         
 

  
         
         


        
        



 



DECLARE

BEGIN

/* UTL_FILE.FRENAME (

   location  IN VARCHAR2,

   filename  IN VARCHAR2, 

   dest_dir  IN VARCHAR2,

   dest_file IN VARCHAR2,

   overwrite IN BOOLEAN DEFAULT FALSE);

*/



 



UTL_FILE.FRENAME('TEST_FILES','emp1.dat', 'TEST_FILES', 'employee_records.dat');



 



EXCEPTION

                WHEN OTHERS THEN

                                DBMS_OUTPUT.PUT_LINE(substr(sqlcode,1,100));

 END;



 



Inquiry:



  
 




ALL_DIRECTORIES - all directories accessible to the database user 

  







  
 






  
 






  
 




129      Return DEFAULT when NO DATA FOUND

SELECT NVL((SELECT CASE 

  





                      WHEN a.ar_order_num = 1 THEN 

  





                       'FIRST' 

  





                      WHEN a.ar_order_num = 2 THEN 

  





                       'SECOND' 

  





                  END 

  





             FROM fin_man_ext_data a 

  





            WHERE a.res_seq = 1), 

  





           'DEFAULT') 

  





FROM DUAL; 

  







  
 






  
 




130      SUBTYPE maxvarchar2_t

I don't want to hard-code that length, because I anticipate the day when Oracle will extend the maximize size of a string. 

  





Instead, I create a subtype in a central, generic package: 

  





PACKAGE plsql_limits 
    
IS 

    
   /* Maximum size for VARCHAR2 in PL/SQL */ 

    
   SUBTYPE maxvarchar2_t IS VARCHAR2 (32767); 

      
  

    
   /* Maximum size for VARCHAR2 in database */ 

    
   SUBTYPE dbmaxvarchar2_t IS VARCHAR2 (4000); 

      
END plsql_limits; 

  







  
 




131      乐观锁（ Optimistic Locking ）悲观锁（ Pessimistic Locking ）



 



Pessimistic locking:

select * from account where name=”Erica” for update



 



乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于

数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据.



 





 





 



132      DQL, DDL, DML, DCL

DQL – query (select, )

DDL – definition (truncate, dynamic )

DML – manipulate (insert, delete, update, lock, select fro update)

DCL – control (if , while, loop …)

  




 



133      Connect by, Level


        
        
 
资    产  
 
 
 
 
期末余额  
 
 
 
 
负债及所有者权益  
 
 
 
 
期末余额  
 
 
 
 
  流动资产  
 
 
 
 
   4256.45  
 
 
 
 
  流动负债  
 
 
 
 
  7453.74  
 
 
 
 
      现金  
 
 
 
 
     2.00  
 
 
 
 
      短期存款  
 
 
 
 
   305.54  
 
 
 
 
      存放中央银行款项  
 
 
 
 
   160.77  
 
 
 
 
      财政性存款  
 
 
 
 
   411.80  
 
 
 
 
      存放同业款项  
 
 
 
 
    18.34  
 
 
 
 
      向中央银行借款  
 
 
 
 
  6485.05  
 
 
 
 
      短期贷款  
 
 
 
 
  4103.41  
 
 
 
 
      同业存放款项  
 
 
 
 
     2.15  
 
 
 
 
      其他流动资产  
 
 
 
 
    71.93  
 
 
 
 
      其他流动负债  
 
 
 
 
   249.20  
 
 
 
 
  长期资产  
 
 
 
 
  3287.75  
 
 
 
 
  长期负债  
 
 
 
 
     0.07  
 
 
 
 
      中长期贷款  
 
 
 
 
  3262.89  
 
 
 
 
      发行长期债券  
 
 
 
 
 
 
 
 
 
      减：贷款呆账准备  
 
 
 
 
    73.71  
 
 
 
 
      其他长期负债  
 
 
 
 
     0.07  
 
 
 
 
      固定资产净值  
 
 
 
 
    77.58  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
      其他长期资产  
 
 
 
 
    20.99  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  无形、递延及其它资产  
 
 
 
 
     0.52  
 
 
 
 
  所有者权益  
 
 
 
 
    190.91  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
      其中：实收资本  
 
 
 
 
    165.15  
 
 
 
 
资产总计  
 
 
 
 
  7644.72  
 
 
 
 
 负债及所有者权益合计  
 
 
 
 
   7644.72  
 
 
 





 



-- CREATE TABLE 

  





CREATE TABLE balance_sheet (BS_ID INTEGER ,BS_PID INTEGER ,BS_NAME VARCHAR2(100) ,BS_VALUE NUMBER(10) ); 

  







  
 




-- INSERT 

  





insert into balance_sheet values(1,0,'流动资产',4256.45); 

  





insert into balance_sheet values(2,1,'现金',2.00); 

  





insert into balance_sheet values(3,1,'存放中央银行款项',160.77); 

  





insert into balance_sheet values(4,1,'存放同业款项',18.34); 

  





insert into balance_sheet values(5,1,'短期贷款', 4103.41); 

  





insert into balance_sheet values(6,1,'其他流动资产',71.93); 

  





insert into balance_sheet values(7,0,'长期资产',3287.75); 

  





insert into balance_sheet values(8,7,'中长期贷款', 3262.89); 

  





insert into balance_sheet values(9,7,'减：贷款呆账准备',73.71); 

  





insert into balance_sheet values(10,7,'固定资产净值',77.58); 

  





insert into balance_sheet values(11,7,'其他长期资产',20.99); 

  





insert into balance_sheet values(12,0,'无形、递延及其它资产',0.52); 

  





insert into balance_sheet values(13,0,'资产总计',7644.72); 

  





insert into balance_sheet values(14,0,'流动负债',7453.74); 

  





insert into balance_sheet values(15,14,'短期存款',305.54); 

  





insert into balance_sheet values(16,14,'财政性存款',411.80); 

  





insert into balance_sheet values(17,14,'向中央银行借款',6485.05); 

  





insert into balance_sheet values(18,14,'同业存放款项',2.15); 

  





insert into balance_sheet values(19,14,'其他流动负债',249.20); 

  





insert into balance_sheet values(20,0,'长期负债',0.07); 

  





insert into balance_sheet values(21,20,'发行长期债券',null); 

  





insert into balance_sheet values(22,20,'其他长期负债', 0.07); 

  





insert into balance_sheet values(23,0,'所有者权益',190.91); 

  





insert into balance_sheet values(24,23,'其中：实收资本',165.15); 

  





insert into balance_sheet values(25,0,'负债及所有者权益合计',7644.72); 

  





commit; 

  







  
 





        
        

  
         
         
SELECT * FROM balance_sheet; 

    
         
         

  
         
         


  
         
         


        
        



  
 




SELECT bs_name, 

  





       bs_value 

  





  FROM balance_sheet 

  





CONNECT BY PRIOR bs_id = bs_pid 

  






        
        

  
         
         
 START WITH bs_pid = 0; 

    
         
         

  
         
         


  
         
         


        
        



  
 






  
 




SELECT bs_name, 

  





       bs_value 

  





  FROM balance_sheet 

  





CONNECT BY PRIOR bs_id = bs_pid 

  






        
        

  
         
         
 START WITH bs_pid = 1; 

    
         
         

  
         
         


  
         
         


        
        



  
 




SELECT (CASE 

  





           WHEN LEVEL = 1 THEN 

  





            '        ' || bs_name 

  





           WHEN LEVEL = 2 THEN 

  





            '                ' || bs_name 

  





       END) bs_name, 

  





       bs_value 

  





  FROM balance_sheet 

  





CONNECT BY PRIOR bs_id = bs_pid 

  





 START WITH bs_pid = 0; 

  






        
        
 
        流动资产  
 
 
 
 
4256.45  
 
 
 
 
                现金  
 
 
 
 
2  
 
 
 
 
                存放中央银行款项  
 
 
 
 
160.77  
 
 
 
 
                存放同业款项  
 
 
 
 
18.34  
 
 
 
 
                短期贷款  
 
 
 
 
4103.41  
 
 
 
 
                其他流动资产  
 
 
 
 
71.93  
 
 
 
 
        长期资产  
 
 
 
 
3287.75  
 
 
 
 
                中长期贷款  
 
 
 
 
3262.89  
 
 
 
 
                减：贷款呆账准备  
 
 
 
 
73.71  
 
 
 
 
                固定资产净值  
 
 
 
 
77.58  
 
 
 
 
                其他长期资产  
 
 
 
 
20.99  
 
 
 
 
        无形、递延及其它资产  
 
 
 
 
0.52  
 
 
 
 
        资产总计  
 
 
 
 
7644.72  
 
 
 
 
        流动负债  
 
 
 
 
7453.74  
 
 
 
 
                短期存款  
 
 
 
 
305.54  
 
 
 
 
                财政性存款  
 
 
 
 
411.8  
 
 
 
 
                向中央银行借款  
 
 
 
 
6485.05  
 
 
 
 
                同业存放款项  
 
 
 
 
2.15  
 
 
 
 
                其他流动负债  
 
 
 
 
249.2  
 
 
 
 
        长期负债  
 
 
 
 
0.07  
 
 
 
 
                发行长期债券  
 
 
 
 
   
 
 
 
 
                其他长期负债  
 
 
 
 
0.07  
 
 
 
 
        所有者权益  
 
 
 
 
190.91  
 
 
 
 
                其中：实收资本  
 
 
 
 
165.15  
 
 
 
 
        负债及所有者权益合计  
 
 
 
 
7644.72  
 
 
 



  
 






  
 




SELECT LTRIM(sys_connect_by_path(BS_Name, 

  





                                 '|'), 

  





             '|') path, 

  





       (CASE 

  





           WHEN LEVEL = 1 THEN 

  





            '        ' || bs_name 

  





           WHEN LEVEL = 2 THEN 

  





            '                ' || bs_name 

  





       END) bs_name, 

  





       bs_value 

  





  FROM balance_sheet 

  





CONNECT BY PRIOR bs_id = bs_pid 

  





 START WITH bs_pid = 0; 

  






        
        

  
         
         


  
         
         
 

  
         
         


        
        



 



133.1                        Level

SELECT ADD_MONTHS(TRUNC(SYSDATE,'mm'), -level) 

  





  FROM DUAL 

  






        
        

  
         
         
CONNECT BY LEVEL <= 24; 

    
         
         

  
         
         


  
         
         


        
        



  
 




SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 24; 

  







  
 




134      Date Quarter 
    

  

select to_char(sysdate,'YY"Q"Q') from dual



  
 




TO_C 

  





---- 

  






        
        

  
         
         
08Q3 

    
         
         

  
         
         


  
         
         


        
        



 



SELECT ntpact 

  





       , CASE WHEN to_number(to_char(NTPACT,'mm'))<4 THEN 'Q1' 

  





         WHEN to_number(to_char(NTPACT,'mm'))<7 THEN 'Q2' 

  





         WHEN to_number(to_char(NTPACT,'mm'))<10 THEN 'Q3' 

  





         ELSE 'Q4' 

  





         END quarter 

  





 FROM testtbl;135      GROUP BY ROLLUP, CUBE

Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果： 

  





  




 




        
        

  
         
         
SELECT channels.channel_desc, calendar_month_desc, 

  
         
         
       countries.country_iso_code,

  
         
         
       TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$

  
         
         
FROM sales, customers, times, channels, countries

  
         
         
WHERE sales.time_id=times.time_id 

  
         
         
  AND sales.cust_id=customers.cust_id 

  
         
         
  AND customers.country_id = countries.country_id

  
         
         
  AND sales.channel_id = channels.channel_id 

  
         
         
  AND channels.channel_desc IN ('Direct Sales', 'Internet') 

  
         
         
  AND times.calendar_month_desc IN ('2000-09', '2000-10') 

  
         
         
  AND countries.country_iso_code IN ('GB', '
          
          
           
           
             US 
           
          
          ')

  
         
         
GROUP BY 

  
         
         
  ROLLUP(channels.channel_desc, calendar_month_desc, countries.country_iso_code);

  
         
         


  
         
         
 

  
         
         


  
         
         
CHANNEL_DESC         CALENDAR CO SALES$

  
         
         
-------------------- -------- -- --------------

  
         
         
Internet             2000-09  GB         16,569

  
         
         
Internet             2000-09  
          
          
           
           
             US 
           
          
                  124,224

  
         
         
Internet             2000-09            140,793

  
         
         
Internet             2000-10  GB         14,539

  
         
         
Internet             2000-10  
          
          
           
           
             US 
           
          
                  137,054

  
         
         
Internet             2000-10            151,593

  
         
         
Internet                                292,387

  
         
         
Direct Sales         2000-09  GB         85,223

  
         
         
Direct Sales         2000-09  US        638,201

  
         
         
Direct Sales         2000-09            723,424

  
         
         
Direct Sales         2000-10  GB         91,925

  
         
         
Direct Sales         2000-10  US        682,297

  
         
         
Direct Sales         2000-10            774,222

  
         
         
Direct Sales                          1,497,646

  
         
         
                                      1,790,032

        
        



 



136      Intersect and Minus

q       intersect运算返回查询结果中相同的部分. 

  





q      minus运算返回在第一个查询结果中与第二个查询结果不相同的那部分行记录.



  
 




137      TNS_ADMIN variable

Not sure about what you can do in the tool, but Windows has a TNS_ADMIN environment variable that you can set to give the location of a tnsnames.ora file. We use this to centralise the TNS entries on a shared location, so we are all working from the same one.



  
 




You can also set a TNS_ADMIN string value in the local machine registry of your Oracle Home. 

  







  
 




TNS_ADMIN=c:/oracle/product/10.2.0/client_1/NETWORK/ADMIN 

  







 



138      Show parameter



 



SQL> show parameter %TRA%



 



139      Check if the record exists


        
        

  
         
         
       select 1

  
         
         
        into  found

  
         
         
        from  dual

  
         
         
       where  exists (select 1

  
         
         
                       from  DISCOUNT

  
         
         
                      where  STOR_ID = old_upd.STOR_ID);

        
        



 



140      Floor & Ceil Function

四舍五入ROUND(value, [scale]) ROUND(101.8) = 102 Round(123.37, 1) = 123.4 
    
截取TRUNC(value, [scale]) Trunc(123.33) = 123 Trunc(123.567, 2) = 123.56 
    
求最大整数FLOOR(value) Floor(128.3)=128 Floor(129.8) = 129 除了不能截取小数部分外，Floor几乎等同于Trunc函数。 
    
求最小整数CELL(value) Cell(128.3)=129 Cell(129.8) = 130 

  







 



141      By value, By reference

By default, 



 




        
        
 
IN
 
 
by reference
 
 
OUT | IN OUT
 
 
by value
 



 



Keep in mind, however, that there is a downside to using NOCOPY. When you pass parameters to a procedure by reference then any modifications you perform on the parameters inside the procedure is done on the same memory location as the actual parameter, so the modifications are visible. In other words, there is no way to “undo” or “rollback” these modifications, even when an exception is raised midway. So if an exception is raised inside the procedure the value of the parameter is “undefined” and cannot be trusted.



 



142      display your Oracle session ID number



 




        
        

  
         
         
SELECT sys_context('USERENV', 

    
         
         

  
         
         


  
         
         


  
         
         
                          'SID') 

    
         
         

  
         
         


  
         
         


  
         
         
  FROM DUAL; 

    
         
         

  
         
         


  
         
         


  
         
         


    
         
         
 
  
         
         


  
         
         


  
         
         
SELECT TO_NUMBER(SUBSTR(dbms_session.unique_session_id, 

    
         
         

  
         
         


  
         
         


  
         
         
                                   1, 

    
         
         

  
         
         


  
         
         


  
         
         
                                   4), 

    
         
         

  
         
         


  
         
         


  
         
         
                        'XXXX') mysid 

    
         
         

  
         
         


  
         
         


  
         
         
  FROM DUAL; 

    
         
         

  
         
         


  
         
         


        
        



  
 




  


Optimizer method

1)       nested loop (USER_NL)

                                outer table is large, inner table is small.



 



SELECT e.employee_id, e.salary 

  





  FROM employees e, jobs j 

  





 WHERE e.employee_id < 103 

  





   AND e.job_id = j.job_id 

  







 



2)      hash join (USE_HASH)

                                outer table is large, inner table is small and related.



  
 




SELECT o.customer_id, l.unit_price * l.quantity 

  





  FROM orders o ,order_items l 

  





 WHERE l.order_id = o.order_id;o 

  







 



3)      sort merge (USE_MERGE)

                                outer table and inner table are both large.



 



Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table.  According to this research using Oracle 10g, the hash join maximum is only to only 200 megabytes (5% of pga_aggregate_target). 

  







  
 






  
 




143      NLS (native Language support)

To see the encoding used by Oracle to store text, using the following DUMP command.



 




        
        

  
         
         
SELECT DUMP('abc', 1016) FROM DUAL;

  
         
         


  
         
         
 

  
         
         


  
         
         
DUMP('ABC',1016) 
    
------------------------------------------------------------------

  
         
         
Typ=96 Len=3 CharacterSet=ZHT16BIG5: 61,62,63

  
         
         
SQL>

        
        



 



If the  character set defined at client is different from that at server, the conversion is  usually done at client.



 



To display the settings on server, checking following command



 




        
        

  
         
         
SELECT * from NLS_SESSION_PARAMETERS; 

  
         
         
SELECT * from NLS_INSTANCE_PARAMETERS; 

  
         
         
SELECT * from NLS_DATABASE_PARAMETERS; 

        
        



 



144      The different between DBA_, ALL_, USER_

DBA_OBJECTS: can see all ALL_OBJECTS, USER_OBJECTS



 



ALL_OBJECTS: can see own objects and any objects that ‘user grant to you’



 



USER_OBJECTS: can only see own objects



 



  




 



145      Using ‘translate’ function to instead ‘replace’


        
        

  
         
         
select translate ('abcde', 'ace', '123') 
    
  from dual; 

    
  

    
-- 結果: 1b2d3 
      
-- 只需一個 translate 即可.

        
        



 




        
        

  
         
         
select translate ('abcde', 'ace', '12') 
    
  from dual; 

    
  

    
-- 結果: 1b2d 
      
-- 因為沒有指定 e 的替換字元，所以 e 就不見了.

        
        



 



146      怎样看懂Oracle的执行计划

146.1                        如何访问数据 
      

    

q      Full Table Scan (FTS)   --全表扫描 

  





q      Index Lookup (unique & non-unique)    --索引扫描（唯一和非唯一） 

  





q      Rowid    --物理行id 

  







 



146.2                        执行计划层次关系 
      

    

--采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行



 



146.2.1                     层次的父子关系 
      

    

PARENT1 
    
**FIRST CHILD 

    
****FIRST GRANDCHILD 

    
**SECOND CHILD 

  





Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output. 



 



146.3                        表访问方式

146.3.1                     Full Table Scan (FTS) 全表扫描          

      

    

全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参数db_block_multiblock_read_count

  




 



146.3.2                     Index Lookup 索引扫描

index unique scan       --索引唯一扫描 

  





index range scan          --索引局部扫描                   Can be used for range operations (e.g. > < <> >= <= between) .

index full scan               --索引全局扫描                   want the data to be ordered in the index order.

index fast full scan      --索引快速全局扫描          不带order by情况下常发生

index skip scan             --索引跳跃扫描                   where条件列是非索引的前导列情况下常发生

Rowid 物理ID扫描      --Rowid扫描是最快的访问数据方式



 



146.4                        表连接方式

146.4.1                     Sort Merge Join (SMJ)    

--由于sort是非常耗资源的，所以这种连接方式要避免


        
        

  
         
         
SELECT STATEMENT [CHOOSE] Cost=17 
    
**MERGE JOIN 

    
****SORT JOIN 

    
******TABLE ACCESS FULL EMP [ANALYZED] 

    
****SORT JOIN 

    
******TABLE ACCESS FULL DEPT [ANALYZED] 

      
         
         

         
         
    
         
         


  
         
         


        
        

146.4.2                     Nested Loops (NL)    

--比较高效的一种连接方式


        
        

  
         
         
SELECT STATEMENT [CHOOSE] Cost=5 
    
**NESTED LOOPS 

    
****TABLE ACCESS FULL DEPT [ANALYZED] 

    
****TABLE ACCESS FULL EMP [ANALYZED] 

      
         
         

         
         
    
         
         


  
         
         


        
        

146.4.3                     Hash Join    

--最为高效的一种连接方式 

  






        
        

  
         
         
SELECT STATEMENT [CHOOSE] Cost=3 
    
**HASH JOIN 

    
****TABLE ACCESS FULL DEPT 

    
****TABLE ACCESS FULL EMP

        
        



 



Cartesian Product    --卡迪尔积，不算真正的连接方式，sql肯定写的有问题



 



146.5                        运算符 
      

    

146.5.1                     sort 

--排序，很消耗资源

146.5.2                     filter

 --过滤，如not in、min函数等容易产生

146.5.3                     view

--视图，大都由内联视图产生 

  





146.5.4                     partition view 

--分区视图



 



146.6                        选择最有效率的表名顺序(只在基于规则的优化器中有效)

Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table) 将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当Oracle处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索 出的记录与第一个表中合适记录进行合并。



 



146.7                        WHERE子句中的连接顺序(只在基于规则的优化器中有效) 
      

    

Oracle采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。 

  







  
 




146.8                        Avoiding sort

As rarely as use ‘‘DISTINCT’, ‘ORDER BY’’ possible, because it will cause sorting in DISK or MEMORY



 





 



147      CONTINUE in 
         
         
          
          Loop
         
         


        
        

  
         
         
BEGIN 
    
  FOR i IN 1 .. 3 
          
          
           
           LOOP
          
           

    
    dbms_output.put_line('i=' || TO_CHAR(i)); 

    


    
    CONTINUE WHEN (i+1) = val; 

    


    
    dbms_output.put_line('Did not jump to the top of the loop'); 

    
  END LOOP; 

    
END contwhen_test;

        
        



 



Continues processing at the top of the loop when the specified condition is met



 



148      Oracle Inline function


        
        

  
         
         
DECLARE 
    
l_loops  NUMBER := 10000000; 

    
  l_start  NUMBER; 

    
  l_return NUMBER; 

    


    
FUNCTION add_numbers (p_1 IN NUMBER, p_2 IN NUMBER) RETURN NUMBER AS 
      
BEGIN 

      
  RETURN p_1 + p_2; 

      
END add_numbers; 

    


    
BEGIN 

    
  l_start := dbms_utility.get_time; 

    


    
  FOR i IN 1 .. l_loops LOOP 

    
    PRAGMA INLINE (add_numbers, 'YES'); 

      
    l_return := add_numbers(1, i); 

    
  END LOOP; 

    


    
  dbms_output.put_line('Elapsed Time: ' || (dbms_utility.get_time - l_start) || ' hsecs'); 

    
END;

        
        



 



149      With Claus


        
        

  
         
         
WITH t AS ( 

      
         
         

    
         
         


  
         
         


  
         
         
  SELECT 100 s FROM dual 

      
         
         

    
         
         


  
         
         


  
         
         
  UNION ALL 

      
         
         

    
         
         


  
         
         


  
         
         
  SELECT 7201 FROM dual) 

      
         
         

    
         
         


  
         
         


  
         
         
SELECT s,TRUNC(s/60)||':'||mod(s,60) "M:S" 

      
         
         

    
         
         


  
         
         


  
         
         
FROM t; 

    
         
         

  
         
         


  
         
         


        
        



 



150      Like Claus

  


 Multiple character:         ‘%’



  
 





        
        

  
         
         
SELECT * 
    
FROM wildcard 

    
WHERE test LIKE '2%5';

        
        



 



Single character:               ‘_’


        
        

  
         
         
SELECT * 

      
         
         

    
         
         


  
         
         


  
         
         
  FROM product_feature a 

      
         
         

    
         
         


  
         
         


  
         
         
 WHERE a.create_userid LIKE 'jv_is';

        
        



 



Find Records Containing Percentage Sign


        
        

  
         
         
SELECT * 
    
FROM wildcard 

    
WHERE test LIKE '%/%%' ESCAPE '/';

        
        



 



151      LNNVL(condition)



 



select * from products 
  
where LNNVL(qty >= reorder_level);



 



it will return qty >= reorder_level OR reorder_level is NULL 
  
This would return the following result: 

  






        
        
 
PRODUCT_ID  
 
 
 
 
QTY  
 
 
 
 
REORDER_LEVEL  
 
 
 
 
1000  
 
 
 
 
20  
 
 
 
 
NULL  
 
 
 
 
3000  
 
 
 
 
8  
 
 
 
 
10  
 
 
 
 
6000  
 
 
 
 
4  
 
 
 
 
5  
 
 
 



 



152      Parallel PL/SQL function in SQL



 



FUNCTION f_parallel_fun ( 

  





      ii_value                      INTEGER 

  





      ) RETURN INTEGER PARALLEL_ENABLE; 

  







  
 




PRAGMA RESTRICT_REFERENCES(f_parallel_fun, WNDS ,WNDS ,RNPS ,WNPS); 

  






        
        

  
         
         


  
         
         
 

  
         
         


        
        

Example1:

      SELECT /*+ full(r) parallel(r,4) */ 

  





                   parallel_fun.f_parallel_fun(r.res_seq) BULK COLLECT 

  





        INTO t_res 

  





        FROM reservation r; 

  





  




 



Example2:

      FOR i IN 1..t_res.count 

  





      
         
         
          
          LOOP
         
          

  





            vn_res := parallel_fun.f_parallel_fun(t_res(i)); 

  





      END 
         
         
          
          LOOP
         
         ; 

  







 



Example1 is better than Example2, because function in SQL can be used ‘PARALLEL’ call, it is faster than Example2.



 



153      Using RETURN INTO Clause to improve performance


        
        

  
         
         
DELETE FROM sales

  
         
         
WHERE product_id = p_product_id

  
         
         
RETURNING sale_value BULK COLLECT INTO t_sale_value; 

    
         
         

  
         
         


  
         
         


  
         
         


  
         
         
 

  
         
         


  
         
         
FOR i IN 1..t_sale_value.COUNT

  
         
         

          
          
           
           LOOP
          
          

  
         
         
   Total := total + t_sale_value(i);

  
         
         
END 
          
          
           
           LOOP
          
          ;

        
        



 



154      Tuning Tips

q      Use Stored programs rather than anonymous blocks

q      Use Packages rather than procedure

q      Pin Frequently used packages in the shared pool

q      Make use of the OF COLUMNs and WHEN clauses of the CREATE TRIGGER to ensure that the trigger only fires when necessary

q      Use explicit cursor

q      Caching with PL/SQL tables



 




        
        

  
         
         
BEGIN

  
         
         
l_product_value := product_val_table(p_product_id);

  
         
         
EXCEPTION 

    
         
         

  
         
         


  
         
         


  
         
         
    WHEN NO_DATA_FOUND THEN

  
         
         
        OPEN product_csr (p_product_id);

  
         
         
        FETCH product_csr INTO l_product_value;

  
         
         
        CLOSE product_csr;

  
         
         
        Product_val_table(p_product_id) := l_product_value;

  
         
         
END 

        
        



 



q      Use temporary tables

q      CREATE SEQUENCE clause includes ORDER BY, it ensures that the sequence is in order.

q      Use DECODE



 



155      Why missing sequence number

q      A transaction obtain the sequence, for some of reason, it ROLLBACKs or FAILED.

q      The server FAILED

q      Sequence number in SGA is insufficient for use



 



156      NULL Values

q      NULL values cannot be indexed

q      The use of NULL values can reduce average row length



 



Don’t define a column as NULLable if it is expected that queries will be constructed that will search for NULL value. Instead, define NOT NULL with default value.



 



157      使用索引的注意事项  



 



select,update,delete 语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高.  



 



 索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用, 而索引释放的空间却不能再用. 

频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下, 也可以阶段性地truncate表,truncate 命令删除表中所有记录,也删除索引碎片. 



 



158      Find TableSpace for one table 
    

  


        
        

  
         
         
SELECT DISTINCT tablespace_name 

    
         
         

  
         
         


  
         
         


  
         
         
FROM all_tables 

    
         
         

  
         
         


  
         
         


  
         
         
WHERE table_name = 'YOURTABLENAME'; 

    
         
         

  
         
         


  
         
         


        
        



 



159      with admin option & with grant option



 



with admin option ：系统权限及角色授予他人时可以使用，权限接受者可以分发权限，但分发出去的权限无法级联回收；



 



with grant option

 ：对象权限授予他人时可以使用，权限接受者可以分发权限，分发出去的权限可以级联回收。



 



160      *PLSQL Developer view others package body



 



It's a little bit tricky. 



 



If you want User 'B' to be able to view package body of a package owned by User 'A' but not able to alter/compile it. Grant select on dba_source to the user 'B'. 

    

  






 



If you've PL/SQL developer, check tools>preferences>options>use DBA views if available.



 



Otherwise, create your own report based on dba_source table.



 



161      Over Funstion

over()函数是Oracle的分析函数，其语法如下： 

  





函数名（ [ 参数 ] ） over（ [ 分区子句 ]  [ 排序子句 [ 滑动窗口子句 ] ]） 

  







 



161.1                        剔除重复记录 

表temp_b的记录： 
    
 

    


    


    
执行如下SQL语句： 

  





select * from ( 
    
       select city_name, city_code, prov_id, row_number() over(partition by city_name order by 1) r from temp_a 

    
) where r = 1; 

  





剔除表temp_a中city_name相同的记录 
    




 





 



  




 



161.2                        分组排序 



 



成绩表的所有记录如下： 

  
 

  


  


  
执行如下SQL语句：

select student_name, class, score, dense_rank() over(partition by class order by score desc) 名次 from temp_b;

分班级按成绩排名次排序如下： 

  
 

  


  
提示： 

  
函数dense_rank()是连续排序，有两个第二名时仍然跟着第三名。 

  
函数rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

  




 



161.3                        分组统计 



 



成绩表的所有记录如下： 

  
 

  


  


  
执行如下SQL语句：

select student_name, class, score, sum(score) over(partition by class order by score desc) 累计 from temp_b;

班级成绩累计（"连续"求和）结果如下： 

  
 

  


  
提示：此功能用于统计某一段时间每天累计的收入非常有用。 

  


  


  
执行如下SQL语句：

select student_name, class, score, sum(score) over(partition by class) 班级总分 from temp_b;

班级成绩总分统计如下： 

  




 



162      WM_CONCAT



 



SQL> select * from t; 
      


      
         I A          D 

      
---------- ---------- ------------------- 

      
         1 b          2008-03-27 10:55:42 

      
         1 a          2008-03-27 10:55:46 

      
         1 d          2008-03-27 10:55:30 

      
         2 z          2008-03-27 10:55:55 

      
         2 t          2008-03-27 10:55:59 

      
要获得如下结果，注意字符串需要按照D列的时间排序： 

  
1  d,b,a 
      
2  z,t 

      


  
select i,wmsys.wm_concat(a) 
      
  from  (select * from t order by i,d) 

      
group by i; 

      


  
         I WMSYS.WM_CONCAT(A) 

  
---------- -------------------- 

  
         1 d,b,a 

  
         2 z,t



 




        
        

  
         
         
SELECT client_seq, 

    
         
         

  
         
         


  
         
         


  
         
         
      wm_concat(client_loc_seq) 

    
         
         

  
         
         


  
         
         


  
         
         
  FROM (SELECT cl.client_loc_seq, 

    
         
         

  
         
         


  
         
         


  
         
         
                cl.client_seq 

    
         
         

  
         
         


  
         
         


  
         
         
          FROM client_location cl 

    
         
         

  
         
         


  
         
         


  
         
         
         ORDER BY cl.client_seq, cl.client_loc_seq ) 

    
         
         

  
         
         


  
         
         


  
         
         
 GROUP BY client_seq; 

    
         
         

  
         
         


  
         
         


        
        

  




 



163      Dropdown Value

SELECT r.res_seq, 

  





         (SELECT rs.descr_eng 

  





              FROM reservation_status rs 

  





             WHERE rs.reservation_status_cd = r.reservation_status_cd) status_descr 

  





  FROM reservation r 

  







 



SELECT r.client_loc_seq, 

  





         (SELECT M.DESCR_ENG 

  





              FROM market m 

  





             WHERE m.market_cd = r.market_cd) market_descr 

  





  FROM client_location r 

  





 WHERE r.market_cd IS NULL; 

  







 



It will still print out NULL description



 



164      Displaying Missing Record



 





 




        
        

  
         
         
SELECT NAME, 

      
         
         

    
         
         


  
         
         


  
         
         
      TYPE 

      
         
         

    
         
         


  
         
         


  
         
         
      NVL((SELECT sal 

      
         
         

    
         
         


  
         
         


  
         
         
              FROM missing_type t 

      
         
         

    
         
         


  
         
         


  
         
         
             WHERE t.NAME = t1.NAME 

      
         
         

    
         
         


  
         
         


  
         
         
               AND t.TYPE = t2.TYPE), 

      
         
         

    
         
         


  
         
         


  
         
         
           0) sal 

    
         
         

  
         
         


  
         
         


  
         
         
  FROM (SELECT DISTINCT NAME 

      
         
         

    
         
         


  
         
         


  
         
         
          FROM missing_type) t1, 

      
         
         

    
         
         


  
         
         


  
         
         
      (SELECT DISTINCT TYPE 

      
         
         

    
         
         


  
         
         


  
         
         
          FROM missing_type) t2 

      
         
         

    
         
         


  
         
         


  
         
         
 ORDER BY NAME, 

      
         
         

    
         
         


  
         
         


  
         
         
     TYPE; 

    
         
         

  
         
         


  
         
         


        
        



 



Name    Type                      Sal

------      -----------              -----

AA    TYPE A      10 

  





AA    TYPE B      20 

  





AA    TYPE C      30 

  





BB    TYPE A      10 

  





BB    TYPE B      20 

  





BB    TYPE C      0 

  





CC    TYPE A      10 

  





CC    TYPE B      0 

  





CC    TYPE C      30 

  





  




 



165      Error Logging

BEGIN 

  





   DBMS_ERRLOG.create_error_log ( 

  





      dml_table_name   => 'EMPLOYEES' 

  





    , skip_unsupported => TRUE); 

  





END; 

  





I will now find a table with the following name and structure in my schema: 

  






        
        

  
         
         
TABLE ERR$_EMPLOYEES(

  
         
         
ORA_ERR_NUMBER$  NUMBER,

  
         
         
ORA_ERR_MESG$      VARCHAR2(2000 BYTE),

  
         
         
ORA_ERR_ROWID$    UROWID(4000),

  
         
         
ORA_ ERR_OPTYP$    VARCHAR2(2 BYTE),

  
         
         
ORA_ERR_TAG$         VARCHAR2(2000 BYTE),

  
         
         
EMPLOYEE_ID           VARCHAR2(4000 BYTE),

  
         
         
FIRST_NAME            VARCHAR2(4000 BYTE),

  
         
         
LAST_NAME             VARCHAR2(4000 BYTE),

  
         
         
…

  
         
         
);

        
        



 




        
        

  
         
         
FORALL indx IN 1 .. employee_ids.COUNT()

  
         
         
   UPDATE employees SET salary = salaries (indx) WHERE employee_id =    employee_ids (indx)

  
         
         
         LOG ERRORS REJECT LIMIT UNLIMITED; 

    
         
         

  
         
         


  
         
         


        
        


        
        

  
         
         


  
         
         
 

  
         
         


        
        



 



SQL> exec dbms_errlog.create_error_log('T','T1',USER); 

  
SQL> INSERT INTO T VALUES('X') LOG ERRORS INTO T1; 

  


  
INSERT INTO T VALUES('X') LOG ERRORS INTO T1 

  
ORA-01722: 无效数字 

  


  
SQL> SELECT * FROM T1; 

  
ORA_ERR_NUMBER$ ORA_ERR_MESG$                  O ORA_E ORA_E X 

  
--------------- ------------------------------ - ----- ----- ------ 

  
          1722 ORA-01722: 无效数字              I          X



 





 



  




 



166      Paginate display

SELECT b.rn, 

  





         b.* 

  





  FROM (SELECT a.*, 

  





                     ROWNUM rn 

  





              FROM (SELECT * 

  





                          FROM reservation r 

  





                         ORDER BY r.res_seq) a 

  





             WHERE ROWNUM < 99) b 

  





 WHERE rn > 5; 

  







  
 




SELECT * 

  





  FROM (SELECT cl.client_seq, 

  





                     cl.client_loc_seq, 

  





                     row_number() over(PARTITION BY cl.client_seq ORDER BY cl.client_loc_seq ASC) rn 

  





              FROM client_location cl) 

  





 WHERE rn <= 3 

  





 ORDER BY client_seq, client_loc_seq;

 



167      DBMS_UTIL


        
        

  
         
         
dbms_utility.comma_to_table(

  
         
         
dbms_utility.table_to_comma (

  
         
         


  
         
         
 

  
         
         


  
         
         
dbms_utility.compile_schema(schema         IN VARCHAR2,

  
         
         
   compile_all    IN BOOLEAN DEFAULT TRUE,

  
         
         
   reuse_settings IN BOOLEAN DEFAULT FALSE);

  
         
         


  
         
         
 

  
         
         


  
         
         
dbms_utility.format_call_stack RETURN VARCHAR2;

  
         
         


  
         
         
 

  
         
         


  
         
         
dbms_utility.format_error_backtrace RETURN VARCHAR2;

  
         
         


  
         
         
 

  
         
         


        
        



 



168      DBMS_ASSERT

DBMS_ASSERT.sql_object_name('dbms_assert') 

  







 



169      REGEXP_LIKE, REGEXP_INSTR…

SELECT * 

  





  FROM grp_master a 

  





 WHERE regexp_like(a.group_id, 

  





                           '(^G|^J)'); 

  







  
 




SELECT * 

  





  FROM grp_master a 

  





 WHERE regexp_like(a.group_id, 

  





                           '^[[:digit:]]'); 

  







 



170      RegExp_Replace

SELECT regexp_replace(a.message, 

  





  'AC-240 Product Price for pax_prod_seq: '''||'([0-9]+)'||'''.*', '/1',1,1) 

  





  FROM APP_LOG A 

  





 WHERE TRUNC(A.DATE_TIME) = TRUNC(SYSDATE) - 2 

  





   AND A.USER_ID = 'BXU' 

  





   AND message LIKE '%not found%' 

  





 ORDER BY a.session_id, 

  





              a.date_time, 

  





              a.display_order; 

  







 



Original: 

    

  




AC-240 Product Price for pax_prod_seq: '3878667' and product seq: '57698' and product ID: 'CRCTMA' and currency cd: 'CAD' and room type cd: 'TWIN' and prod avail date: '23-JUN-2006' and single_currency_flag: 'Y' and price_cd: 'PP or PERPRD' not found.



 



Result: 

    

  




3878667



 



171      BLOB compress



 



CREATE OR REPLACE TRIGGER document_insert_trigger 

  





BEFORE INSERT ON document 

  





FOR EACH ROW 

  





DECLARE 

  





  l_compressed_blob BLOB := TO_BLOB('1'); -- Initial value just in case... 

  





BEGIN 

  







  
 




  -- Compress the data. 

  





  UTL_COMPRESS.lz_compress (src => :new.document_binary_data, 

  





                            dst => l_compressed_blob); 

  







  
 




  -- update the blob value 

  





  SELECT l_compressed_blob INTO :new.document_binary_data FROM dual;  

  







  
 




  -- *** this does not work either *** 

  





  --:new.document_binary_data := l_compressed_blob; 

  







  
 




  DBMS_LOB.FREETEMPORARY(l_compressed_blob); 

  





end; 

  







 





 



172      DBMS_PIPE FOR DEBUG


        
        

  
         
         
PROCEDURE p_message(msg VARCHAR2) AS 

    
  sta NUMBER; 

    
BEGIN 

    
  dbms_pipe.pack_message(msg); 

    
  sta := dbms_pipe.send_message('MyDebug', 60); -- 把打包好的消息发送给名为MyDebug的管道，超时设置为60秒 

    
END; 

    
         
         

  
         
         


  
         
         


        
        



 



直接用这个p_message代替平时使用的dbms_output.put_line就可以了，在调试程序前打开PL/SQL Developer里Event Monitor，选择Event Type为DBMS_PIPE，Event Name填上MyDebug，然后按start开始监听。然后就可以像平常一样运行自己存储过程了，不同的是调试信息是实时显示出来的，而不是像 Output那样要等程序执行完才显示，感觉真是方便多了。



 



另外也可以使用DBMS_ALERT，它和DBMS_PIPE最大不同的是它基于事务的，消息要commit才会发送.



 



DBMS_PIPE和DBMS_ALERT这两个包都可以进行多进程间的通信，但两者任然是有一些区别： 

  


  
1) 报警信号是同步的。报警信号直到会话发出Commit时才被发出；如果它所处的事务回滚，则该信号就不发送了。但是管道信号是异步的，而且其通信过程不受 事务提交和回滚的影响。 

  


  
2) 沿着管道进行传递的消息只能被一个进程进行处理，其消息的接收方式是拷贝后删除，但是报警信号可以被多 个进程所获得，即消息的接收方式仅仅是拷贝。 

  


  
3) 管道消息不仅可以传递文本信息，还可以传递其他信息，比如对象等，这一好处得益于其对消息的打包预处理；而报警消息只能是文本，不能是其他类型。



 



173      Condition Compiling

173.1                         Tables

USER_PLSQL_OBJECT_SETTING



 



173.2                         Statement

--


        
        

  
         
         
$IF          $THEN

  
         
         
$ELSIF          $THEN

  
         
         
$ELSE

  
         
         
$END

        
        



 



--


        
        

  
         
         
$ERROR

  
         
         


  
         
         
 

  
         
         


  
         
         
$END

        
        



 




        
        

  
         
         
ALTER PROCEDURE test_plsql_ccflags COMPILE PLSQL_CCFLAGS='debug_mode:false' REUSE SETTINGS;

  
         
         


  
         
         
 

  
         
         


  
         
         
CREATE OR REPLACE PROCEDURE test_plsql_ccflags

  
         
         
IS

  
         
         
BEGIN

  
         
         
    $IF $$debug_mode $THEN DBMS_OUTPUT.PUT_LINE('DEBUGGING'); $END

  
         
         
    NULL;

  
         
         
END test_plsql_ccflags;

  
         
         
/

        
        



  
 




173.3                         Setting

PLSQL_WARNING 

  





ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; 

  





ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE'; 

    

  







  
 




PLSQL_OPTIMIZE_LEVEL



 



PLSQL_CCFLAGS 

  







  
 




173.4                        Packages

Dbms_db_version 

  





Dbms_preprocessor 

  







  
 




173.5                         Example



 




        
        

  
         
         
ALTER PROCEDURE show_info COMPILE

  
         
         
    PLSQL_CCFLAGS = 'current_user_type:0'

  
         
         
    REUSE SETTINGS

        
        



 




        
        

  
         
         
CREATE OR REPLACE PROCEDURE show_info

  
         
         
IS

  
         
         
BEGIN

  
         
         
  $IF $$current_user_type = user_types.administrator  

  
         
         
  $THEN

  
         
         
     DBMS_OUTPUT.PUT_LINE ('Administrator!');

  
         
         
  $ELSIF $$current_user_type = user_types.enduser

  
         
         
  $THEN

  
         
         
     DBMS_OUTPUT.PUT_LINE ('End user!');

  
         
         
  $ELSE

  
         
         
     $ERROR 'Curr user type of ' || $$current_user_type || ' is not known.' 

  
         
         
     $END

  
         
         
  $END

  
         
         
END show_info;

        
        



 



174      Avoid Hard-Code


        
        
 
Scenario  
 
 
 
 
What to do about it  
 
 
 
 
Any literal value  
 
 
 
 
Hide the value behind a constant or a function. The function is a bit slower than referencing a constant but you can hide the value.  
 
 
 
 
Constrained declarations like VARCHAR2(10)  
 
 
 
 
Whenever you declare a variable with a limited size, you run the risk of raising a VALUE_ERROR exception. Better instead to define a subtype and hide the constraint behind the name of the subtype. Then when you declare your variable, use the subtype as the type.  
 
 
 
 
Fetch into a list of variables  
 
 
 
 
This is a hard-coding because if the number or type of expressions in the select list ever changes, you have to change the fetch statement as well. Better instead to fetch into a record based on the cursor with %ROWTYPE.  
 
 
 
 
Business rules and formulas  
 
 
 
 
Rules change all the time and usually get more complex, so you should always hide rules and formulas behind a function, and then just call the function.  
 
 
 
 
SQL statements  
 
 
 
 
This is not obvious to most developers, but every time you write an SQL statement you are taking a "snapshot" of a small slice of your business model - and that model is also always in the process of changing. So....hide all your SQL statements behind procedures (to change data in tables) and functions (to return data) - a table or transaction API. The Quest CodeGen Utility can help you do this.  
 
 
 



 





 



175      Open File with PLSQLDEV.exe

If using DDE, it will load .SQL files in the same instance.

Folder Option -> File Type -> New (Advanced)





 





 



176      PLSQL Standard Developed

q      Always initialize variable after BEGIN, because exception can be raised by error.



 



q      Give DEFAULT value to number, but default of string is NULL



 



q      Never issue RETURN inside the loop



 



q      Never EXIT from within any FOR loop



 



q      Inner anonymous blocks (those with their own DECLARE section), are good to control when resource-intensive variables are allocated in memory, like large collections that are only needed during certain executions.



 



q      Use the NOCOPY parameter mode hint with caution, It is a compiler hint, not a directive. The compiler may ignore you 

  







 



177      ROWNUM & ORDER BY Issue 
    

  

Wrong: 

  





SELECT spo.* 

  





  INTO vr_sp_output 

  





  FROM sp_output     spo, 

  





         wrk_sp_output wspo 

  





 WHERE wspo.session_id = gn_session_id 

  





   AND wspo.orig_sp_output_seq = spo.sp_output_seq 

  





   AND ROWNUM = 1 

  





 ORDER BY spo.sp_output_seq DESC; 

  







  
 




Correct: 

  





SELECT spo.* 

  





  INTO vr_sp_output 

  





  FROM sp_output spo 

  





 WHERE spo.sp_output_seq = (SELECT MAX(wspo.orig_sp_output_seq) 

  





                                            FROM wrk_sp_output wspo 

  





                                           WHERE wspo.session_id = gn_session_id); 

  







 



178      DBMS_SQL.PARSE

-- dynamic fetch column name & column value 

  





CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2) IS 

  





                v_v_val    VARCHAR2(4000); 

  





                v_n_val    NUMBER; 

  





                v_d_val    DATE; 

  





                v_ret      NUMBER; 

  





                c          NUMBER; 

  





                d          NUMBER; 

  





                col_cnt    INTEGER; 

  





                f          BOOLEAN; 

  





                rec_tab    DBMS_SQL.DESC_TAB; 

  





                col_num    NUMBER; 

  





                v_rowcount NUMBER := 0; 

  





BEGIN 

  





                -- create a cursor 

  





                c := DBMS_SQL.OPEN_CURSOR; 

  





                -- parse the SQL statement into the cursor 

  





                DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE); 

  





                -- execute the cursor 

  





                d := DBMS_SQL.EXECUTE(c); 

  





                -- 

  





                -- Describe the columns returned by the SQL statement 

  





                DBMS_SQL.DESCRIBE_COLUMNS(c,  col_cnt,  rec_tab); 

  





                -- 

  





                -- Bind local return variables to the various columns based on their types 

  





                FOR j IN 1 .. col_cnt 

  





                
          
          
           
           LOOP
          
           

  





                                CASE rec_tab(j).col_type 

  





                                                WHEN 1 THEN 

  





                                                                DBMS_SQL.DEFINE_COLUMN(c,   j,   v_v_val,   2000); -- Varchar2 

  





                                                WHEN 2 THEN 

  





                                                                DBMS_SQL.DEFINE_COLUMN(c,  j,   v_n_val); -- Number 

  





                                                WHEN 12 THEN 

  





                                                                DBMS_SQL.DEFINE_COLUMN(c,   j,   v_d_val); -- Date 

  





                                                ELSE 

  





                                                                DBMS_SQL.DEFINE_COLUMN(c,   j,   v_v_val,  2000); -- Any other type return as varchar2 

  





                                END CASE; 

  





                END 
          
          
           
           LOOP
          
          ; 

  





                -- 

  





                -- Display what columns are being returned... 

  





                DBMS_OUTPUT.PUT_LINE('-- Columns --'); 

  





                FOR j IN 1 .. col_cnt 

  





                
          
          
           
           LOOP
          
           

  





                                DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' - ' || CASE rec_tab(j).col_type 

  





                                                                                                                                  WHEN 1 THEN 

  





                                                                                                                                   'VARCHAR2' 

  





                                                                                                                                  WHEN 2 THEN 

  





                                                                                                                                   'NUMBER' 

  





                                                                                                                                  WHEN 12 THEN 

  





                                                                                                                                   'DATE' 

  





                                                                                                                                  ELSE 

  





                                                                                                                                   'Other' 

  





                                                                                                                  END); 

  





                END 
          
          
           
           LOOP
          
          ; 

  





                DBMS_OUTPUT.PUT_LINE('-------------'); 

  





                -- 

  





                -- This part outputs the DATA 

  





                
          
          
           
           LOOP
          
           

  





                                -- Fetch a row of data through the cursor 

  





                                v_ret := DBMS_SQL.FETCH_ROWS(c); 

  





                                -- Exit when no more rows 

  





                                EXIT WHEN v_ret = 0; 

  





                                v_rowcount := v_rowcount + 1; 

  





                                DBMS_OUTPUT.PUT_LINE('Row: ' || v_rowcount); 

  





                                DBMS_OUTPUT.PUT_LINE('--------------'); 

  





                                -- Fetch the value of each column from the row 

  





                                FOR j IN 1 .. col_cnt 

  





                                
          
          
           
           LOOP
          
           

  





                                                -- Fetch each column into the correct data type based on the description of the column 

  





                                                CASE rec_tab(j).col_type 

  





                                                                WHEN 1 THEN 

  





                                                                                DBMS_SQL.COLUMN_VALUE(c,  j,  v_v_val); 

    

  




                                                                                DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' : ' || v_v_val); 

  





                                                                WHEN 2 THEN 

  





                                                                                DBMS_SQL.COLUMN_VALUE(c,  j,  v_n_val); 

  





                                                                                DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' : ' || v_n_val); 

  





                                                                WHEN 12 THEN 

  





                                                                                DBMS_SQL.COLUMN_VALUE(c,  j,  v_d_val); 

  





                                                                                DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' : ' || to_char(v_d_val, 

  





                                                                                                                                                                                                                                                                                   'DD/MM/YYYY HH24:MI:SS')); 

  





                                                                ELSE 

  





                                                                                DBMS_SQL.COLUMN_VALUE(c,  j,  v_v_val); 

  





                                                                                DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' : ' || v_v_val); 

  





                                                END CASE; 

  





                                END 
          
          
           
           LOOP
          
          ; 

  





                                DBMS_OUTPUT.PUT_LINE('--------------'); 

  





                END 
          
          
           
           LOOP
          
          ; 

  





                -- 

  





                -- Close the cursor now we have finished with it 

  





                DBMS_SQL.CLOSE_CURSOR(c); 

  





END; 

  





/ 

  





179      RAISE vs RAISE_APPLICATION_ERROR

The RAISE_APPLICATION_ERROR built-in (defined in the DBMS_STANDARD package) should be used for just a single scenario: you need to communicate an application-specific error back to the user. 

  





  

  





Suppose, for example, I have a rule for the employees table that the minimum salary allowed is $100,000 (ah, wouldn't that be nice?). I want to enforce that rule through a database trigger: 

  






        
        

  
         
         
TRIGGER employees_minsal_tr 
    
   BEFORE INSERT OR 
          
          
           
           
             UPDATE 
            

    
           
           
   
           
           
             ON 
           
          
           employees 

    
   FOR EACH ROW 

    
BEGIN 

    
   IF :new.salary < 1000000 

    
   THEN 

    
      /* communicate error */ 

    
      NULL; 

    
   END IF; 

    
END;

        
        

I can stop the DML from completing by issuing a RAISE statement, such as: 

  





RAISE PROGRAM_ERROR; 

  





But I would not be able to communicate back to the user what the actual problem was. 

  





  

  





If, on the other hand, I use RAISE_APPLICATION_ERROR, I can specify the error number (of little interest to my users, but a good "identifier" for support) and, more importantly, the error message, as in: 

  






        
        

  
         
         
TRIGGER employees_minsal_tr 
    
   BEFORE INSERT OR 
          
          
           
           
             UPDATE 
            

    
           
           
   
           
           
             ON 
           
          
           employees 

    
   FOR EACH ROW 

    
BEGIN 

    
   IF :new.salary < 1000000 

    
   THEN 

    
      RAISE_APPLICATIONE_ERROR (-20000, 

    
          'Salary of '|| :new.salary || 

    
          ' is too low. It must be at least $100,000.'); 

    
   END IF; 

    
END; 

        
        

And that, dear reader, is the motivation for using RAISE_APPLICATION_ERROR: the ability to communicate a custom, application-specific error message to your users. 

  





  

  





Use RAISE when you want to raise an already-defined exception, whether one of Oracle's (such as NO_DATA_FOUND) or one of your definition, as in: 

  





DECLARE 
    
   e_bad_value EXCEPTION; 

    
BEGIN 

    
   RAISE e_bad_value; 

    
END;  

  





but if it is one of your own user-defined exceptions, it only makes sense to raise it this way if you are going to trap it inside the backend as well, and then do something in response to the error. 

  





  

  





If you let the e_bad_value exception propagate out to your users, all they will know about the error is that the error code is 1 and the error message is "User-defined Exception". Not very helpful. 

  







 



180      Use UNION ALL instead of UNION


         
         
          
          UNION
         
          eliminates all duplicated rows from select list, But UNION ALL  return all records, so it is fast.



 



181      Subqueries with temporary tables or WITH clause

Original:

SELECT emp_id 
    
FROM employee, 

    
       department 

    
WHERE emp_id IN (SELECT emp_id 

    
                    FROM emp_small 

    
                   WHERE emp_dept < 'D') 

    
   AND dpt_manager IN (SELECT emp_id 

    
                         FROM emp_small 

    
                        WHERE emp_dept < 'D') 

    
   AND dpt_id = emp_dept 

  







 



To:

WITH temp_emp AS (SELECT emp_id empid 
    
                    FROM emp_small 

    
                   WHERE emp_dept < 'D') 

    
SELECT emp_id 

    
FROM employee, 

    
       department 

    
WHERE emp_id IN (SELECT empid 

    
                    FROM temp_emp) 

    
   AND dpt_manager IN (SELECT empid 

    
                         FROM temp_emp) 

    
   AND dpt_id = emp_dept 

  







 





 




        
        

  
         
         


  
         
         


  
         
         

    
          
          
[1] Procedure scope
  
         
         

  
         
         

    
          
          
[2] Inner scope
  
         
         

  
         
         

    
          
          
[3] PRIMARY KEY as row number
  
         
         

  
         
         

    
          
          
[4] SGA - system global area
  
         
         

  
         
         

    
          
          
[5] PGA – program global area
  
         
         

  
         
         

    
          
          
[6] My Tips
  
         
         

  
         
         

    
          
          
[7] My Tips
  
         
         

  
         
         

    
          
          
[8] My Tips
  
         
         

  
         
         

    
          
          
[9] Keep in mind
  
         
         

  
         
         

    
          
          
[10] Keep in mind
  
         
         

  
         
         

    
          
          
[11] Keep in  mind
  
         
         

  
         
         

    
          
          
[12] Keep in mind for dynamic SQL
  
         
         

  
         
         

    
          
          
[13] Oracle default value
  
         
         

  
         
         

    
          
          
[14] Resource is not released
  
         
         

  
         
         

    
          
          
[15] Column Name
  
         
         

  
         
         

    
          
          
[16] Strong type
  
         
         

  
         
         

    
          
          
[17] Weak type
  
         
         

  
         
         

    
          
          
[18] It can be ‘IN, OUT, IN OUT’ mode
  
         
         

  
         
         

    
          
          
[19] Injected code
  
         
         

        
        


        
        

  
         
         


  
         
         

    
          
          

           
           

      
           
           
 [B1]It will delete all data, because it is always TRUE
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [B2]Procedure identifier
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [B3]It is visible in whole procedure
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [B4]Procedure variable
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [BX5]Values
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [BX6]Key
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [B7]Auto empty to CHR
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [MSOffice8]Will locate second row
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [MSOffice9]Will raise the INVALID_CURSOR exception
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [B10]Trace error
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [B11]Call procedure
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [B12]Never executed
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [B13]It won’t work, it will return null.
    
          
          
  
         
         

  
         
         

    
          
          

           
           

      
           
           
 [BX14]collections