Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

PL/SQL Quick Reference - Oracle

RSS
Modified on Thu, Jul 21, 2011, 9:36 AM by Administrator Categorized as Oracle, Quick Reference
{outline||<1> - |.<1> - }

Basic Block Structure

VARIABLE g_Hello VARCHAR2(46)      global (a.k.a. bind) variable. Note: NO semi-colon
 
DECLARE
  v_variable VARCHAR2(5);
 optional section
 
BEGIN
  SELECT column_name
  INTO v_variable
  FROM table_name;
 required section
 
  :g_Hello := 'Hello, world!'  assigning a bind variable a value
 
EXCEPTION
  WHEN exception_name THEN
  . . .
 optional section for error handling
END;  required keyword

Variables

Declaration Syntax

identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

Element Usage
identifier The name of the variable. At most 30 characters long, and must begin with a letter and contain only letters, numbers, and special characters. Should not be the same as the name of a table column used in the block
CONSTANT disallows the variable to be changed in the program; must be initialized
datatype a scalar (holds a single value and has no internal components), composite (a.k.a., collections: RECORD, TABLE, Nested TABLE, VARRAY. Note that the record and table datatypes are not the same as the database objects), refererence, or LOB data type
NOT NULL forces the variable to have a value; must be initialized
expr used to initialize the variable at the time it's declared

Scalar Data Types

Data Type Description
VARCHAR2(maxlen) variable length character data up to 32,767 bytes
NUMBER [(precision, scale)] fixed and floating point
DATE dates and times
CHAR [(maxlen)] fixed length character data up to 32,767 bytes
LONG variable length character up to 32,760 bytes
LONG RAW binary data and byte strings up to 32,760 bytes
BOOLEAN TRUE, FALSE, or NULL
BINARY_INTEGER integers in the range -2,147,483,647 and 2,147,483,647
PLS_INTEGER signed integers in the range -2,147,483,647 and 2,147,483,647; required less storage and are faster than NUMBER and BINARY_INTEGER values
column_name%TYPE takes on the data type of column_name

Assignment

identifier := expr
identifier  the variable name
expr a variable, literal, or function call, but not a database column (use a SELECT or FETCH statement for this purpose)

Retrieving Data into Variables

Syntax
SELECT ColumnNameList
INTO { variable_name [, variable_name] . . . | record_name }
FROM table
WHERE condition

Notes
  • Host variables must be prefixed with a colon
  • Must return exactly one row. Use explicit cursors for multi-row queries
  • Trap exceptions NO_DATA_FOUND and TOO_MANY_ROWS in the exception handling block

Bind Variables

Definition a variable declared in a host environment and then use to pass runtime values into/out of PL/SQL programs, which can use them as any other variable
Declaring VARIABLE identifier datatype
Displaying value SQL> VARIABLE g_n NUMBER

. . .
PRINT g_n
Assigning value :g_Hello := 'Hello, world!'

Output

SET SERVEROUTPUT ON
DBMS_OUTPUT.PUT_LINE('Monthly salary = ' || TO_CHAR(v_sal)); 

Control Structures

IF Structure
IF ''condition'' THEN
   ''statements'';
[ELSIF ''condition'' THEN // NOTE SPELLING OF KEYWORD !!!
   ''statements'';]
[ELSE
   ''statements'';]
END IF;

LOOP Structure
LOOP
   statement1;
   . . .
   EXIT [WHEN condition];
END LOOP;

FOR Loop Structure
FOR ''counter'' in [REVERSE] ''lower_bound''..''upper_bound'' LOOP
   ''statements'';
END LOOP

  • Keyword REVERSE causes the counter to decrement. In this usage, the lower_bound is still referenced first
  • Do not declare counter; it is declared implicitly as an integer

WHILE Loop Structure
WHILE condition LOOP
   statements;
END LOOP

PL/SQL Records

Declaring
TYPE type_name IS RECORD
   (field_declaration [, field_declaration] . . .);
identifier type_name;

Where field_declaration is
field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE}
   [[NOT NULL] {:= | DEFAULT | expr]

Example
. . .
TYPE emp_record_type IS RECORD
   (ename VARCHAR2(10), // these column names should appear
   job VARCHAR2(9),     // in the same order as in the database
   sal NUMBER(7,2));
emp_record emp_record_type;
. . .

The %ROWTYPE Attribute
DECLARE identifier reference%ROWTYPE;

Where reference is the name of a table, view, cursor, or cursor variable on which the record is to be based.

PL/SQL Tables

Declaration Syntax

TYPE type_name IS TABLE OF
   { column_type | variable%TYPE | table.column%TYPE }
   [NOT NULL] [INDEX BY BINARY_INTEGER];
identifier type_name;

Example
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;

Notes
  • PL/SQL Tables can have one column and a primary key, neither of which can be named.
  • The primary key is not the same as a database primary key, but a unique identifier.
  • It doesn't have to be contiguous. There can be gaps in the sequence.

Referencing Syntax

pl/sql_table_name(primary_key_value)

Where primary_key_value belongs to type BINARY_INTEGER

Example
ename_table(3) . . .

PL/SQL Table Methods

Syntax
table_name.method_name[ (parameters) ]

Methods

Method Description
EXISTS(n) Boolean - TRUE if the nth element in the PL/SQL table exists
COUNT Integer (?) - number of elements in the PL/SQL table
FIRST, LAST Integer(?) or NULL (if empty table) - returns the first and last (smallest and largest) index numbers for a PL/SQL table
PRIOR(n) Returns the index number preceding n
NEXT(n) Returns the index number succeeding n
EXTEND(n, i) Increases the size of a PL/SQL table. EXTEND appends one null element; EXTEND(n) appends n null elements; EXTEND(n, i) appends n copies of the ith element
TRIM TRIM removes one element from the end of the PL/SQL table; TRIM(n) removes n elements from the end of the PL/SQL table
DELETE DELETE removes all elements; DELETE(n) removes the nth element DELETE(m, n) removes all elements in the range m..n

Exception Handling

Syntax

EXCEPTION
   WHEN exception1 [OR exception2 . . . ] THEN
     statements;
   [WHEN exception3 [OR exception3 . . .] THEN
     statements;]
   [WHEN OTHERS THEN
     statements;]

Where exception is the standard name of a predefined exception or the name of a user-defined exception

Predefined Exceptions

  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • INVALID_CURSOR
  • ZERO_DIVIDE
  • DUP_VAL_ON_INDEX

Functions Used Within Exception Handling

  • SQLCODE - numeric value for the exception
  • SQLERRM - message associated with the exception

User-Defined Errors

Syntax
DECLARE
   e_emps_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_emps_remaining, -2292)
  . . .
EXCEPTION
   WHEN e_emps_remaining THEN
     . . .
END;

PRAGMA EXCEPTION_INIT Statement

Purpose
To catch the exception raised by RAISE_APPLICATION_ERROR in PL/SQL, you will have to define the exception using the PRAGMA EXCEPTION_INIT statement. If you don't use PRAGMA EXCEPTION_INIT to define the exception, you can only catch the exception in the WHEN OTHERS section of your exception handler.

Syntax
PRAGMA EXCEPTION_INIT(exception_name, error_number)

Example
PROCEDURE xxx()
IS

    PRAGMA  EXCEPTION_INIT(BAD_CUSTOMER_ACCOUNT, -20001)

BEGIN

    RAISE_APPLICATION_ERROR(BAD_CUSTOMER_ACCOUNT,  'Customer account' || cust_account_in || ' is invalid');

EXCEPTION

    WHEN BAD_CUSTOMER_ACCOUNT
        DBMS_OUTPUT.PUT_LINE(SQLERRM);   // log the error message
        RAISE;                           // re-raise the exception for VB 

END;

Raising Errors

Purpose
The RAISE_APPLICATION_ERROR function allows you to define your own exceptions. When called, all database changes are automatically rolled back. The exception can be caught in your Visual Basic 6.0 code; Err.Number and Err.Message will be based on the values you used in the RAISE_APPLICATION_ERROR call.

Advantages
  • Do not have to define a special OUT parameter for the error message.
  • ROLLBACK is automatically called for you.
  • The error comes back as a VB exception that can be handled with a normal error handler.

Syntax
raise_application_error(error_number, message [, use_stack ] );

Called only from an executing stored subprogram.

Parameter Definition
error_number a user specified number for the exception - between -20,000 and -20,999
message the user specified message for the exception - a string up to 2048 bytes long
use_stack TRUE or FALSE: place on the stack of previous errors? If FALSE, the error replaces all previous errors.

Example
RAISE_APPLICATION_ERROR(-20001, 'Customer account' || cust_account_in || ' is invalid')'

See Also

Functions in Oracle Quick Reference
Cursors in Oracle Quick Reference

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.