Site Logo

IzzySoft


Das inoffizielle Android-HandbuchDas inoffizielle Android-Handbuch
Für 16,99 € bei Amazon kaufen
Das inoffizielle Android-SystemhandbuchDas inoffizielle Android-Systemhandbuch
Für 6,99 € bei Amazon kaufen
Die besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und StudiumDie besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und Studium
Für 2,94 € bei Amazon kaufen
Stand: 2024-03-28 04:07
Preis & Verfügbarkeit können sich geändert haben.

PL/SQL stuff

Native compilation

Some new stuff has been introduced to PL/SQL native compilation with Oracle 11g. As one might guess, native compiled code runs faster than interpreted code; but before Oracle 11g this required some additional preconditions, as e.g. a separate compiler. Not that is built-in.

Though native compilation takes a little longer to complete, code is usually compiled once and then executed many times, so the improvement in execution time very soon makes up for additional time spent at compiling. So how is it achieved?

ALTER SESSION SET plsql_code_type=native;

All PL/SQL compiled after issuing this statement will be native, easy to guess. To revert back:

ALTER SESSION SET plsql_code_type=interpreted;

Just need it temporarily – say, to compile a single package?

ALTER PACKAGE mypackage
  COMPILE BODY
    PLSQL_CODE_TYPE=native
    PLSQL_OPTIMIZE_LEVEL=2
    REUSE SETTINGS;

Optimization level

In above statement I also used the parameter PLSQL_OPTIMIZE_LEVEL. This was introduced with Oracle 10g (levels 0, 1, 2) and extended with Oracle 11g (level 3). And here comes what those levels basically mean:

  1. no optimizations at all
  2. elementary optimizations only (remove irrelevant code pieces, such as unused variables)
  3. standard optimizations (some code refactoring and advanced optimization)
  4. high optimizations (additionally to level 2, all called functions will be inlined; bigger code but faster execution)

The default optimization level can be set system-wide or for the current session:

ALTER <SESSION | SYSTEM> SET plsql_optimize_level=3;

UPSERT

Sometimes one cannot use MERGE for several reasons. But always first figure out whether to update using a SELECT would mean to always have two queries (first the SELECT, and then either an UPDATE or an INSERT). Wouldn't it be smart to save some overhead for better performance, introducing an "UPSERT"?

UPDATESETWHERE; -- pretend it is there and just needs to be updated
IF SQL%NOTFOUND THEN
  INSERT INTOVALUES ();
END IF;

Cursor Attributes

Currently (Oracle 11gR2) there still seem to be but four of them:

SQL%ISOPEN
whether the cursor is currently opened (OPEN CURSOR mycursor)
SQL%FOUND
whether the cursor contains another row
SQL%NOTFOUND
whether the cursor contains no more rows
SQL%ROWCOUNT
how many rows already have been processed (do not confuse this with how many rows the cursor matches – such attribute is still missing)

Exceptions

The only one known by almost all developers, and partially extensively misused:
WHEN OTHERS THEN NULL;
which simply suppresses all errors. But for useful error handling, PL/SQL already has some fine exceptions defined:

Predefined Exceptions

NO_DATA_FOUND
Speaks for itself, and is raised e.g. by some SELECT … INTO
TOO_MANY_ROWS
Just the opposite: e.g. some SELECT … INTO where the SELECT returns more than one row, but the INTO variable is a scalar
INVALID_CURSOR
Tried to close a cursor that was not open?
ZERO_DIVIDE
"Do not want to share with nobody"? Cannot divide by Zero (x/0)
DUP_VAL_ON_INDEX
attempted to store duplicate values in a column that is constrained by a unique index
OTHERS
Everything else.

The OTHERS exception is not primary intended for above mentioned THEN NULL, but the developer here can catch errors without any special pre-defined exception, e.g. using the SQLERRNO and SQLERRM variables:

WHEN OTHERS THEN
  mypackage.logger('',SQLERRNO,SQLERRM);
  RAISE;

or:

WHEN OTHERS THEN
  IF SQLERRNO =THEN
    -- handle specific error here
  END IF;

User Defined Exceptions

It is also possible to define some exceptions:

PROCEDURE myproc IS
  e_myException EXCEPTION;
  BEGINEXCEPTION
    WHEN e_myException THENEND myproc;
2020-11-18