PSM Language Element References

Assignment Statement

Function

Within a PSM block, it stores a value in a variable or in an out-bind parameter.

Syntax

<assignment statement> ::=
    <assignment target> := <value expression>
    ;

<assignment target> ::=
      collection_variable ( index )
    | cursor_variable
    | :host_cursor_variable
    | out_parameter
    | :host_variable [ :indicator_variable ]
    | record_variable . field_name
    | scalar_variable

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

Targets of assignment statements are classified into an external bind parameter and an internal PSM variable.

All variables except for a procedure/ function parameter can have a name assigning a scope.

Examples

The following is an example of using an assignment statement.

gSQL> DECLARE
  V1 INTEGER := 0;
BEGIN
  FOR I IN 1..10 LOOP
    V1 := V1 + I;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
END;
/

V1 = 55

Anonymous PL block executed.
gSQL> \var P1 INTEGER

gSQL> 
BEGIN
  :P1 := 100;
END;
/

gSQL> \print P1 
 P1
---
100

Anonymous PL block executed.

Compatibility

The differences between an assignment statement of GOLDILOCKS and that of SQL standard are as follows.

SQL standard compatibility

Feature ID

Description

Compatibility

P002

Computational completeness

X

P006

Multiple assignment

X

For More Information

Refer to the following.

Basic LOOP Statement

Function

It repeatedly performs statements within LOOP until the LOOP is terminated by performing GOTO or EXIT.

Syntax

<basic loop statement> ::=
    LOOP { <SQL procedure statement> ; }... END LOOP [ loop_name ]
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

A basic loop statement is repeatedly performs statements within LOOP.
A basic loop statement is a loop-family statement, so it can be a target statement which GOTO, EXIT, and CONTINUE indicates as a label.

Examples

DECLARE
  V1 INTEGER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
    V1 := V1 + 1;
    EXIT WHEN V1 > 2;
  END LOOP;
END;
/
V1 = 1 
V1 = 2 

Anonymous PL block executed.

Compatibility

<basic loop statement> statement is the same as <loop statement> of the SQL standard.
SQL standard compatibility

Feature ID

Description

Compatibility

P002

Computational completeness

O

For More Information

Refer to the following.

Block (BEGIN .. END)

Function

It defines a variable, a type, a cursor and an exception, and groups a pl statement to execute.

Syntax

<PSM block> ::=
    [ <label list> ]
    [ DECLARE ]
    [ <declare item list> ] 
    <body>
    ;

<label list> ::=
    << <label name> >> 
    [ ... ]

 <declare item list>
     <declare item> 
     [ ... ]

<declare item> ::=
    <variable declaration>
    | <type definition>
    | <explicit cursor declaration>
    | <explicit cursor definition>
    | <exception declaration>
    | <exception init pragma>
    | <procedure declaration>
    | <procedure definition>
    | <function declaration>
    | <function definition>

<body> ::=
      BEGIN
      <pl statement list>
      [ <exception block> ]
      END [ <name> ]

<pl statement list> ::=
      [ <label list> ] <pl_statement>
      [ ... ]

<pl statement> ::=
      <assignment statement>
    | <basic loop statement>
    | <block statement>
    | <case statement>
    | <close statement>
    | <collection method invocation>
    | <continue statement>
    | <cursor for loop statement>
    | <delete statement extension>
    | <execute immediate statement>
    | <exit statement>
    | <fetch statement>
    | <for loop statement>
    | <goto statement>
    | <if statement>
    | <insert statement extension>
    | <insert into ... update statement extension>
    | <merge statement extension>
    | <null statement>
    | <open statement>
    | <open for statement>
    | <procedure call statement>
    | <raise statement>
    | <return statement>
    | <return table statement>
    | <select into statement>
    | <sql statement>
    | <update statement extension>
    | <while loop statement>

<sql statement> ::=
      <savepoint statement>
    | <release savepoint statement>
    | <rollback statement>
    | <commit statement>
    | <lock table statement>

<exception block> ::=
      EXCEPTION <exception handler list>

<exception handler list> ::=
      <exception handler> [ ... ]

<exception handler> ::=
      WHEN 
      { <exception list> | OTHERS }
      THEN
      <pl statement list>

<exception list> ::=
      <exception> [ OR ... ]

Invocation and Access Rules

It can be used only within PROCEDURE, FUNCTION, PACKAGE BODY or an anonymous block.

Syntax Rules and Parameters

<< label name >>

The label name is a unique identifier for the block, and it is undeclared specifier.
The length of a cursor name should be shorter than 128 bytes.

declare item

body

It is the beginning of executing <PSM block>.
<body> consists of the executable <pl statement list> and <exception block> for exception handling.

pl statement

exception block

It handles the exceptional situation which occurs while executing <psm block>.
<exception> is a name of a predefined exception defined in GOLDILOCKS or a user-defined exception. 
If the specified exception occurs, then the corresponding <pl statement list> is executed.

Description

<psm block> is a basic component of PSM.
A block can have a declaration part and a exception handling part.
A block can be duplicated, and the duplicated block has a new subordinate variable scope. A superordinate block can not refer to a variable in a subordinate block.

Examples

gSQL> 
<<MAIN>>
DECLARE
  V1 INTEGER := 1;
BEGIN
  DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
  <<SUB1>>
  DECLARE
    V1 VARCHAR(10) := 'ABC';
  BEGIN
    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
    DBMS_OUTPUT.PUT_LINE( 'SUB1.V1 = ' || SUB1.V1 );
    DBMS_OUTPUT.PUT_LINE( 'MAIN.V1 = ' || MAIN.V1 );
 END;
END;
/
V1 = 1
V1 = ABC
SUB1.V1 = ABC
MAIN.V1 = 1

Anonymous PL block executed.

Compatibility

<compound statement> of the SQL standard defines ATOMIC /NOT ATOMIC statement which specifies a new savepoint, but GOLDILOCKS does not support it.
SQL standard compatibility

Feature ID

Description

Remarks

P002

Computational completeness

It does not support ATOMIC statement.

For More Information

Refer to Overview of PSM.

Call Specification

Function

It maps the information corresponding to the function name, the parameter data type and the RETURN data type of the program which were written with C, to call it in PSM.

Syntax

<call specification> ::= 
      <c declaration>  
      ; 
 
<c declaration> ::= 
      LANGUAGE C 
      <library clause> 
      [ WITH CONTEXT ] 
      <external parameters> 
 
<library clause> ::= 
      LIBRARY <library name> NAME <double_quote_string>
    | NAME <double_quote_string> LIBRARY <library name>    
 
<external parameters> ::= 
      PARAMETER ( [ <external parameter list> ] ) 
 
<external parameter list> ::= 
      <external parameter>  
     
<external parameter> ::= 
      CONTEXT
    | <parameter name> [ <property> ] [ BY REFERENCE ] <external datatype> 
    | RETURN [ <property> ] [ BY REFERENCE ] <external datatype> 
 
<property> ::= 
       INDICATOR 
     | LENGTH 
     | MAXLEN 
 
<external datatype> ::= 
       UNSIGNED CHAR 
     | CHAR 
     | UNSIGNED SHORT 
     | SHORT 
     | UNSIGNED INT 
     | INT 
     | UNSIGNED LONG 
     | LONG 
     | FLOAT 
     | DOUBLE 
     | STRING 
     | RAW 
     | SQL_LONG_VARIABLE_LENGTH 
     | SQL_NUMERIC 
     | SQL_DATE 
     | SQL_TIME 
     | SQL_TIME_TZ 
     | SQL_TIMESTAMP 
     | SQL_TIMESTAMP_TZ 
     | SQL_INTERNAL

Invocation and Access Rules

One of the following privileges is required to use a library in <library clause>.

Syntax Rules and Parameters

LANGUAGE C

It is an external program written in C language.

LIBRARY <library name>

It is the name of the library object created with CREATE LIBRARY statement.
It can specify the schema as <schema_name>.<library name>.

NAME <double_quote_string>

It displays the function name of the program written in C.
The function name enclosed with "" should be smaller than 128 bytes.

external parameter

It maps the data types of parameter of a procedure or a function, and of the parameter of the external C function.

Description

<call specification> statement maps the information corresponding to the external C function name, parameter datatype and RETURN datatype.
<call specification> can be used in the following statements.
It can not be used in <body> of PL/SQL block.

Examples

gSQL>
CREATE OR REPLACE FUNCTION func1( p1 NATIVE_INTEGER,
                                  p2 NATIVE_INTEGER )
    RETURN NATIVE_INTEGER AS
LANGUAGE C
LIBRARY lib1 NAME "add"
PARAMETERS ( p1 INT ,
             p2 INT ,
             RETURN INT );
/

Function created.

Compatibility

The SQL standard does not define it.
It is similar to <external body reference> of the SQL standard.

For More Information

Refer to External Routine.

CASE Statement

Function

It performs a statement list satisfying conditions which returns TRUE among given conditions.

Syntax

<case statement> ::=
    <simple case statement>
    | <searched case statement>
    ;

<simple case statement> ::=
    CASE <case operand> <simple case statement when clause>...
    [ <case statement else clase> ]
    END CASE

<searched case statement> ::=
    CASE <searched case statement when clause>...
    [ <case statement else clase> ]
    END CASE

<simple case statement when clause> ::=
    WHEN <when operand>
        THEN <executable statement list>

<searched case statement when clause> ::=
    WHEN <search condition>
        THEN <executable statement list>

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

It performs statements in WHEN clause returning TRUE by evaluating conditions like as IF statement.
It evaluates conditional expressions in order, and stops evaluating after finding the TRUE conditional clause.
If the case satisfying the condition does not exist and ELSE clause is not specified, then an error occurs.

Examples

Using a Simple CASE

gSQL> DECLARE
V1 integer := 0;
BEGIN
  SELECT 2 INTO V1 FROM DUAL;

  CASE V1 WHEN 0 THEN DBMS_OUTPUT.PUT_LINE ('Result = 0');
          WHEN 1 THEN DBMS_OUTPUT.PUT_LINE ('Result = 1');
          WHEN 2 THEN DBMS_OUTPUT.PUT_LINE ('Result = 2');
          ELSE DBMS_OUTPUT.PUT_LINE ('Result = OTHER');
  END CASE;
END;
/
Result = 2

Anonymous PL block executed.

Using a Searched CASE

gSQL> DECLARE
V1 integer := 0;
BEGIN
  SELECT 2 INTO V1 FROM DUAL;

  CASE WHEN V1 = 0 THEN DBMS_OUTPUT.PUT_LINE ('Result = 0');
       WHEN V1 = 1 THEN DBMS_OUTPUT.PUT_LINE ('Result = 1');
       WHEN V1 = 2 THEN DBMS_OUTPUT.PUT_LINE ('Result = 2');
       ELSE DBMS_OUTPUT.PUT_LINE ('Result = OTHER');
  END CASE;
END;
/
Result = 2

Anonymous PL block executed.

Compatibility

CASE statement of the SQL standard defines the comparison of row type (list type) values, but GOLDILOCKS does not support it.
CASE statement of the SQL standard can define multiple conditions in a list in <when operand> by delimiting them with ',', but GODILOCKS does not support it.
SQL stantard compatibility

Feature ID

Description

Remarks

P002

Computational completeness

It does not support P004, P008.

P004

Extended CASE statement

-

P008

Comma-separated predicates in simple CASE statement

-

CLOSE Statement

Function

It closes an open cursor.

Syntax

<close statement> ::=
    CLOSE cursor_name
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

It closes an open cursor.
A closed cursor can be opened again by using an open statement.

Examples

gSQL> CREATE TABLE T1 ( I1 INTEGER );

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> DECLARE
  CURSOR C1 IS SELECT I1 FROM T1;
  V1 T1%ROWTYPE;
BEGIN
  OPEN C1;
  FETCH C1 INTO V1;

  CLOSE C1;
END;
/

Anonymous PL block executed.

Compatibility

The SQL standard does not define it.

For More Information

Refer to the following.

Collection Method Invocation

Function

It provides a method which can explores a collection type variable.

Syntax

<collection method> ::=
         variable_name . <method>
    ;

<method> ::=
        first ()
      | last  ()
      | prior ( expression )
      | next  ( expression )
      | count ()
      | exists ( expression )
      | delete ( expression )

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

Refer to the following table.

Function

Name

Function

Return value

Whether to

require

an argument

FIRST

It returns the smallest key.

A key type specified in INDEX OF

X

LAST

It returns the biggest key.

A key type specified in INDEX OF

X

PRIOR

It returns a key smaller than the input key.

A key type specified in INDEX OF

O

NEXT

It returns a key bigger than the input key.

A key type specified in INDEX OF

O

COUNT

It returns the stored count.

INTEGER

X

DELETE

It deletes a value corresponding to a key.

N/A

O

EXISTS

It returns whether a key exists or not.

BOOLEAN

O

Examples

DECLARE
TYPE rec IS TABLE OF VARCHAR(20) INDEX BY VARCHAR(20);
v1 rec;
v2 VARCHAR(20);
BEGIN
  DBMS_OUTPUT.PUT_LINE( '------------------------------------');
  DBMS_OUTPUT.PUT_LINE( 'first = ' || v1.first);
  DBMS_OUTPUT.PUT_LINE( 'last = '  || v1.last);
  DBMS_OUTPUT.PUT_LINE( 'prior = ' || v1.prior('aaa'));
  DBMS_OUTPUT.PUT_LINE( 'next = '  || v1.next('aaa'));
  DBMS_OUTPUT.PUT_LINE( 'count = ' || v1.count());

  FOR I IN 1 .. 9
  LOOP
      v1('a' || to_char(i)) := 'a' || to_char(i);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( '------------------------------------');
  DBMS_OUTPUT.PUT_LINE( 'first = ' || v1.first);
  DBMS_OUTPUT.PUT_LINE( 'last = '  || v1.last);
  DBMS_OUTPUT.PUT_LINE( 'count = ' || v1.count());

  DBMS_OUTPUT.PUT_LINE( '------------------------------------');
  DBMS_OUTPUT.PUT_LINE( 'Print all from first to last');
  v2 := v1.first;
  WHILE v2 IS NOT NULL
  LOOP
      DBMS_OUTPUT.PUT_LINE('Key= ' || v2 || ',Value=' || v1(v2));
      v2 := v1.next(v2);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( '------------------------------------');
  DBMS_OUTPUT.PUT_LINE( 'Print all from last to first');
  v2 := v1.last;
  WHILE v2 IS NOT NULL
  LOOP
      DBMS_OUTPUT.PUT_LINE('Key= ' || v2 || ',Value=' || v1(v2));
      v2 := v1.prior(v2);
  END LOOP;

  v1.delete(v1.first());
  DBMS_OUTPUT.PUT_LINE('count = ' || v1.count() );
  DBMS_OUTPUT.PUT_LINE('first = ' || v1.first() );

END;
/
------------------------------------
first = 
last = 
prior = 
next = 
count = 0
------------------------------------
first = a1
last = a9
count = 9
------------------------------------
Print all from first to last
Key= a1,Value=a1
Key= a2,Value=a2
Key= a3,Value=a3
Key= a4,Value=a4
Key= a5,Value=a5
Key= a6,Value=a6
Key= a7,Value=a7
Key= a8,Value=a8
Key= a9,Value=a9
------------------------------------
Print all from last to first
Key= a9,Value=a9
Key= a8,Value=a8
Key= a7,Value=a7
Key= a6,Value=a6
Key= a5,Value=a5
Key= a4,Value=a4
Key= a3,Value=a3
Key= a2,Value=a2
Key= a1,Value=a1
count = 8
first = a2

Anonymous PL block executed.

For More Information

Refer to COLLECTION Variable Declaration.

COLLECTION Variable Declaration

Function

It declares a collection variable.

Syntax

<declare record variable> ::=
    variable_name <collectionType> 
    ;
 
<Collection Type Definition> ::=
    TYPE <Type-Name> 
    IS TABLE OF <Element-Type> 
    [ NOT NULL ] 
    INDEX BY <Index-Type>
    ;

<Element-Type> ::=
      Built-in SQL Data Type
    | User-Defined Type
    | %TYPE
    | %ROWTYPE

<Index-Type> ::=
      INTEGER
    | LONG
    | CHAR(n)
    | VARCHAR(n)

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of a PL block.

Syntax Rules and Parameters

Description

It declares a collection type.

Examples

gSQL> DECLARE
TYPE rec IS TABLE OF VARCHAR(20) INDEX BY VARCHAR(20);
v1 rec;
v2 VARCHAR(20);
BEGIN
  DBMS_OUTPUT.PUT_LINE( 'first = ' || v1.first);
  DBMS_OUTPUT.PUT_LINE( 'last = '  || v1.last);
  DBMS_OUTPUT.PUT_LINE( 'prior = ' || v1.prior('aaa'));
  DBMS_OUTPUT.PUT_LINE( 'next = '  || v1.next('aaa'));
  DBMS_OUTPUT.PUT_LINE( 'count = ' || v1.count());

  FOR I IN 1 .. 10
  LOOP
      v1('a' || i) := 'a' || i;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( 'first = ' || v1.first);
  DBMS_OUTPUT.PUT_LINE( 'last = '  || v1.last);
  DBMS_OUTPUT.PUT_LINE( 'count = ' || v1.count());

  DBMS_OUTPUT.PUT_LINE( 'Print all from first to last');
  v2 := v1.first;
  WHILE v2 IS NOT NULL
  LOOP
      DBMS_OUTPUT.PUT_LINE('Key= ' || v2 || ',Value=' || v1(v2));
      v2 := v1.next(v2);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( 'Print all from last to first');
  v2 := v1.last;
  WHILE v2 IS NOT NULL
  LOOP
      DBMS_OUTPUT.PUT_LINE('Key= ' || v2 || ',Value=' || v1(v2));
      v2 := v1.prior(v2);
  END LOOP;

END;
/
first =
last =
prior =
next =
count = 0
first = a1
last = a9
count = 10
Print all from first to last
Key= a1,Value=a1
Key= a10,Value=a10
Key= a2,Value=a2
Key= a3,Value=a3
Key= a4,Value=a4
Key= a5,Value=a5
Key= a6,Value=a6
Key= a7,Value=a7
Key= a8,Value=a8
Key= a9,Value=a9
Print all from last to first
Key= a9,Value=a9
Key= a8,Value=a8
Key= a7,Value=a7
Key= a6,Value=a6
Key= a5,Value=a5
Key= a4,Value=a4
Key= a3,Value=a3
Key= a2,Value=a2
Key= a10,Value=a10
Key= a1,Value=a1

Anonymous PL block executed.

Compatibility

The SQL standard does not define it.

For More Information

Refer to Collection Method Invocation.

CONTINUE Statement

Function

It stops currently performing statement list, and performs the next iteration of a superordinate loop statement.

Syntax

<continue statement> ::=
    CONTINUE [ label_name ] [ WHEN condition ] 
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.
A statement with a target label should be one of the following loop family statements.

• basic loop statement
• for loop statement
• while statement
• forall statement

Syntax Rules and Parameters

Description

It stops currently performing statement list, and returns to the superordinate loop statement.
If a label is specified, it returns to the superordinate loop statement of the label name.
If a label is not specified, it returns to the nearest superordinate loop statement.
If multiple superordinate loop statements with the same names exist, then the nearest statement is selected.
It can return to a loop statement (exist in a nested scope) which is visible in the current location.
If a condition is specified, then it returns only when the condition is TRUE.
If a condition is not specified, then it definitely returns.

Examples

DECLARE
  V1 INTEGER := 1;
BEGIN
  <<AAA>>
  WHILE V1 <= 10 LOOP
    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
    V1 := V1 + 1;
    IF V1 <= 2 THEN
      DBMS_OUTPUT.PUT_LINE( 'CONTINUE' );
      CONTINUE;
    ELSE
      EXIT;
    END IF; 
    DBMS_OUTPUT.PUT_LINE( 'END-OF-WHILE' );
  END LOOP AAA;
END;
/
V1 = 1 
CONTINUE
V1 = 2 

Anonymous PL block executed.

Compatibility

<continue statement> statement is similar to <iterate statement> of the SQL standard.
However, <iterate statement> statement does not provide WHEN condition feature.

For More Information

Refer to the following.

Cursor FOR LOOP Statement

Function

It performs loops as many times as the number of rows in the result created by a query or a cursor declared by a user in PSM.

Syntax

<Cursor For Loop statement> ::=
       FOR <Variable_Name> IN <Cursor>
       LOOP
            { <SQL procedure statement> ; }... 
       END LOOP [ Label_Name ]
       ;

<Cursor> ::=
       < ( Implicit_Cursor_Query ) >
     | < Explicit_Cursor_Name > [ ( [ <actual param> ] ) ]
   

<Implicit_Cursor_Query> ::=
       SELECT statement
     | SELECT_FOR_UPDATE statement
     | INSERT_RETURNING_QUERY statement
     | UPDATE_RETURNING_QUERY statement
     | DELETE_RETURNING_QUERY statement


<actual param> ::=
      ( expression [ , expression ] .. )

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body of PSM.

Syntax Rules and Parameters

The variable declared within For~Loop is valid only within that loop scope. (The variable can not be referenced from outside of that Cursor For Loop Block Scope)

When Using Cursor Name

A cursor should already have been declared before performing LOOP by using a cursor name. 
For more information about an actual param, refer to OPEN Statement.

When Using Cursor Query

It can perform only the query which can be internally processed by using an implicit cursor in GOLDILOCKS such as a select and a returning query.

Description

It performs PSM statements within a loop by turning around loops as many time as the number of results created by a cursor. 
If a cursor becomes invalid (e.g. closed) during LOOP, it does not perform the loop and it processes itas an error. 
If an explicit cursor name is specified and the corresponding cursor is already opened, then it is processed as an error.
The variable to which a result of the cursor specified in FOR LOOP clause is returned is automatically created. (It is created as a row type of the result set to be returned by an execution result of a cursor.) 
However, if an alias for a select target expression which is not a column of a specific table among results of a user cursor query is not specified, then an error may occur.

Examples

Using Explicit Cursor

DECLARE
CURSOR c1 IS SELECT * FROM T1;
BEGIN
  FOR rec IN C1
  LOOP
    DBMS_OUTPUT.PUT_LINE( 'RowCount=' || c1%rowcount || ',C1=' || rec.c1 || ', C2=' || rec.c2);
  END LOOP;
END;
/
RowCount=1,C1=1, C2=1
RowCount=2,C1=2, C2=2
RowCount=3,C1=3, C2=3
RowCount=4,C1=4, C2=4
RowCount=5,C1=5, C2=5
RowCount=6,C1=6, C2=6
RowCount=7,C1=7, C2=7
RowCount=8,C1=8, C2=8
RowCount=9,C1=9, C2=9
RowCount=10,C1=10, C2=10

Anonymous PL block executed.

Using Cursor Query

BEGIN
  FOR rec IN (select * from t1)
  LOOP
      DBMS_OUTPUT.PUT_LINE( 'RowCount=' || sql%rowcount || ',C1=' || rec.c1 || ', C2=' || rec.c2);
  END LOOP;
END;
/
C1=1, C2=1
C1=2, C2=2
C1=3, C2=3
C1=4, C2=4
C1=5, C2=5
C1=6, C2=6
C1=7, C2=7
C1=8, C2=8
C1=9, C2=9
C1=10, C2=10

Anonymous PL block executed.

For More Information

Refer to the following.

Cursor Variable Declaration

Function

It declares a cursor variable in DECLARE section of PSM.

Syntax

<cursor variable declaration> ::=
    variable_name <type>
    ;

<cursor type definition> ::=
      TYPE <type_name> IS REF CURSOR [ RETURN <return type> ]

<return type> ::= 
      <table_name | view_name | cursor_name | cursor_variable > % ROWTYPE
    | <record_variable_name> % TYPE
    | <record_type_name>

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of PSM.

Syntax Rules and Parameters

Specifying the initial value of a cursor variable or assigning a cursor variable is allowed only between cursor variables.

Description

A cursor variable is operated like as a pointer indicating a cursor which is not dependent on a specific cursor.

Examples

DECLARE
TYPE rec IS RECORD (V1 VARCHAR(20), V2 VARCHAR(20));
TYPE cv IS REF CURSOR RETURN rec;
BEGIN
    NULL;
END;
/

Anonymous PL block executed.

For More Information

Refer to the following.

DELETE Statement Extension

Function

It can store the result in RETURNING INTO clause by using a record type variable of PSM.

Syntax

<PSM delete statement extension: searched> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        [ <returning into clause> ]
    ;

<delete statement: positioned> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        WHERE CURRENT OF cursor_name
    ;

<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]

<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>

<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]

<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }

<returning into clause> ::=
    { RETURN | RETURNING } { * | { <value expression> [ [AS] alias_name] } [, ...] } INTO variable_name [, ...]

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of PSM.

Syntax Rules and Parameters

If a variable to be returned through RETURNING INTO is a record type, then it can not be used by mixing together with a different variable type.

Description

It can store the result in RETURNING INTO clause by using a record type variable of PSM.

Examples

gSQL> CREATE TABLE T1( C1 VARCHAR(20), C2 VARCHAR(20));
Table created.

gSQL> COMMIT;
Commit complete.

gSQL> INSERT INTO T1 VALUES ('AAA', 'BBB'), ('BBB', 'CCC'), ('CCC', 'DDD');
3 rows created.

gSQL> COMMIT;
Commit complete.


gSQL> DECLARE
  rec t1%ROWTYPE;
BEGIN
  DELETE FROM T1 WHERE C1 = 'AAA' RETURNING * INTO rec;
  DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT=' || SQL%ROWCOUNT);
  DBMS_OUTPUT.PUT_LINE('rec.c1=' || rec.c1 || ', rec.c2=' || rec.c2);
END;
/
SQL%ROWCOUNT=1
rec.c1=AAA, rec.c2=BBB

Anonymous PL block executed.

For More Information

Refer to Deleting Data.

EXCEPTION_INIT Pragma

Function

It sets the error code which is to be processed by a user-defined exception.

Syntax

< PRAGMA EXCEPTION_INIT > ::=
     PRAGMA EXCEPTION_INIT ( <Exception-Name>, <Internal-ErrorCode> ) 
     ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of PSM.

Syntax Rules and Parameters

A predefined exception can not be used in an exception name which is used as an argument. (A predefined exception name can not be declared.) 
An exception name which is used as an argument in the same PL block DECLARE clause should be declared in advance. (Declaration of an exception name in different BLOCK can not be referenced.) 
<Internal-ErrorCode> should be an internal error code existing within DB SYSTEM. (SUCCESS code can not be set.

Description

A user explicitly declares an exception name corresponding to an error code of DB SYSTEM.

Examples

gSQL> DECLARE
user_exception_1 EXCEPTION;
PRAGMA EXCEPTION_INIT( user_exception_1, -17001);
BEGIN
  RAISE USER_EXCEPTION_1;
  EXCEPTION WHEN user_exception_1 THEN DBMS_OUTPUT.PUT_LINE('User Exception_1');
END;
/
User Exception_1

Anonymous PL block executed.

Compatibility

Error codes are different each other according to a vendor, so it is not compatible each other.

For More Information

Refer to the following.

Exception Declaration

Function

It declares an exception name within a PL block.

Syntax

< Exception-Declaration Statement > ::=
     <Exception-Name>  EXCEPTION 
     ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of PSM.

Syntax Rules and Parameters

It can not declare a predefined exception name. 
Duplicated declarations are not allowed in DECLARE clause of the same SCOPE.

Description

A user explicitly declares an exception.

Examples

DECLARE
user_exception_1 EXCEPTION;
user_exception_2 EXCEPTION;
user_exception_3 EXCEPTION;
BEGIN
  RAISE USER_EXCEPTION_2;
  EXCEPTION WHEN user_exception_1 THEN DBMS_OUTPUT.PUT_LINE('User Exception_1');
            WHEN user_exception_2 THEN DBMS_OUTPUT.PUT_LINE('User Exception_2');
            WHEN user_exception_3 THEN DBMS_OUTPUT.PUT_LINE('User Exception_3');
END;
/
User Exception_2

Anonymous PL block executed.

Compatibility

An exception declaration of the standard SQL is as follows, but GOLDILOCKS supports the syntax as above.
<condition declaration> ::=
DECLARE <condition name> CONDITION [ FOR <sqlstate value> ]

For More Information

Refer to the following.

Exception Handler

Function

It performs an operation defined for an exception which is explicitly occurred by a user or an operation defined for an implicit error due to a DB SYSTEM error occurred during performing PL/ SQL.

Syntax

< Exception Handler Statement > ::=
       EXCEPTION < Exception_When_List >
       ;

< Exception_When_List > ::=
       WHEN < Exception_Name_List > THEN <excutable statement list>  [ WHEN OTHERS THEN <excutable statement list> ]

< Exception_Name_List > ::= 
        <Exception_Name> [ { OR <Exception_Name> }... ]

Invocation and Access Rules

It can be used within a PL block.

Syntax Rules and Parameters

OTHERS (predefined exception) can not be specified together with another exception name by using OR.
Duplicated specifying of OTHERS (predefined exception) is not allowed in an exception handler, and OTHERS should be specified at the last.

Description

Exception Types

Type

Definer

Has error

Has name

Raise implicitly

Raise explicitly

Predefined

System

Yes

Yes

Yes

Optionally

User-defined

User

If user assign

If user assign

No

Yes

A predefined exception has an exception name and an error code which are specified in advance in GOLDILOCKS.
Other exceptions are classified into an internally defined exception and a user-defined exception. An internally defined exception is that a user sets the internal error code name of GOLDILOCKS differently from a  predefined exception name, and a user-defined exception is that only the exception name is declared without specifying a separate error code.

Predefined Exception

Predefined exception type

Name

Description

CASE_NOT_FOUND

It can not satisfy all conditions of CASE WHEN, or ELSE clause is not defined.

DUP_VAL_ON_INDEX

INDEX duplicated error occurred.

INVALID_CURSOR

A cursor status is incorrect.

INVALID_NUMBER

It can not be converted to a number.

NO_DATA_FOUND

SELECT statement returns zero data.

ROWTYPE_MISMATCH

Field types of two RowType variables are different each other.

TOO_MANY_ROWS

It returns two or more rows.

VALUE_ERROR

It is an error such as type mismatch and invalid casting.

ZERO_DIVIDE

It tries dividing by 0.

OTHERS

It includes errors which are not defined in a predefined.

Examples

gSQL> DECLARE
V1 INTEGER := 0;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Step1');
   V1 := 1 / 0;
   DBMS_OUTPUT.PUT_LINE('Step2');
   EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 'Exception V1=' || V1);
END;
/
Step1
Exception V1=0

Anonymous PL block executed.

Compatibility

It does not support the SQL standard grammar.

For More Information

Refer to the following.

EXECUTE IMMEDIATE Statement

Function

It executes a dynamic SQL within PSM.

Syntax

<EXECUTE IMMEDIATE statement> ::=
    EXECUTE IMMEDIATE <dynamic-sql> [ <binding-parameters> ]
    ;


<dynamic-sql> ::=
      single_quote_string
    | psm_variable


<binding-parameters> ::=
      <into-clause>
    | <using-clause>
    | <returning-into-clause>
    | <into-clause> <using-clause>
    | <using-clause> <returning-into-clause>



<into-clause> ::=
    INTO psm_variable [ {, psm_variable} ... ]


<using-clause> ::=
    USING [ <Bind-Type> ] <expression> [ {, [ <Bind-Type> ] <expression>} ... ]


<Bind-Type> ::=
     IN
   | OUT
   | IN OUT


<returing-into-clause> ::=
    RETURNING INTO psm_variable [ {, psm_variable} ... ]

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Dynamic SQL

The following is an example of expressing a data by using quote(s) in an SQL statement to be performed.

EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES ( ''Tom'' ) '; -- Tom 
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES ( ''Tom''''House'') '; -- Tom'House
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES ( ''''''Tom'') ';   -- 'Tom
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES ( CHR(39) || ''TOM'' ) '; -- 'Tom
A marker (? or :V1) is used in a location where a user input a variable in a dynamic SQL.
An SQL statement specified in a dynamic SQL should be valid.

INTO Clause

The result of performing a dynamic SQL exists, and it is not bound through a marker, but the result of processing an SQL statement is returned. (It is internally a form of an implicit cursor fetch.) 
The syntax is as follows.
EXECUTE IMMEDIATE 'SELECT ... FROM .. WHERE ...';
EXECUTE IMMEDIATE 'INSERT ... RETURNING ...';
EXECUTE IMMEDIATE 'UPDATE ... RETURNING ...';
EXECUTE IMMEDIATE 'DELETE ... RETURNING ...';

USING Clause

It lists a variable or an expression in USING clause (IN can be omitted.) as many as the number of input variables used in a dynamic SQL.
If the result of performing a dynamic SQL exists, then it lists variables as many as the number of result columns in USING OUT clause. (when returning the results to INTO clause)
The result can be returned in the following syntax by using USING OUT.
EXECUTE IMMEDIATE 'SELECT x, y, z INTO :v1, :v2, :v3 ...';
EXECUTE IMMEDIATE 'INSERT INTO ...  RETURNING C1, C2 INTO :V1, :V2';
EXECUTE IMMEDIATE 'UPDATE T1 SET .. RETURNING C1, C2 INTO :V1, :V2';
EXECUTE IMMEDIATE 'DELETE FROM ...  RETURNING C1, C2 INTO :V1, :V2';

RETURNING Clause

If INSERT/UPDATE/DELETE RETURNING INTO statement is used as a dynamic SQL, the result is returned by binding a variable  specified in USING clause in OUT-mode in GOLDILOCKS.
The same result can be returned by using RETURNING-INTO clause for the compatibility with other DBMS.

Other Rules

Description

Examples

gSQL> DECLARE
V1 INTEGER;
V2 VARCHAR(20);
BEGIN
    V1 := 1;
    V2 := 'abcdef';
    DBMS_OUTPUT.PUT_LINE('#INSERT');
    EXECUTE IMMEDIATE 'insert into t1 values (:a1, :a2)' USING v1, v2;
    EXECUTE IMMEDIATE 'select c1, c2 from t1 where c1 = 1' INTO v1, v2;
    DBMS_OUTPUT.PUT_LINE('C1='|| v1 || ', C2=' || v2);

    V1 := 1;
    V2 := 'xyz';
    DBMS_OUTPUT.PUT_LINE('#UPDATE');
    EXECUTE IMMEDIATE 'update t1 set c2 = :a1 where c1 = :a2' USING V2, V1;

    V1 := 1;
    V2 := '';
    DBMS_OUTPUT.PUT_LINE('#SELECT');
    EXECUTE IMMEDIATE 'select c1, c2 from t1 where c1 = :a1' INTO v1, v2 USING v1;
    DBMS_OUTPUT.PUT_LINE('C1='|| v1 || ', C2=' || v2);

    V1 := 1;
    V2 := '';
    DBMS_OUTPUT.PUT_LINE('#DELETE');
    EXECUTE IMMEDIATE 'delete from t1 where c1 = :a1' USING v1;
END;
/
#INSERT
C1=1, C2=abcdef
#UPDATE
#SELECT
C1=1, C2=xyz
#DELETE

Anonymous PL block executed.

EXIT Statement

Function

It exits a loop statement which has the given label among superordinate loop statements, then performs the next statement.

Syntax

<exit statement> ::=
    EXIT [ label_name ] [ WHEN condition ]
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

Examples

gSQL> DECLARE
  V1 INTEGER := 1;
BEGIN
  <<AAA>>
  WHILE V1 <= 10 LOOP
    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
    EXIT;
    V1 := V1 + 1;
  END LOOP AAA;
END;
/
V1 = 1 

Anonymous PL block executed.

Compatibility

It does not exist in the SQL standard.

Explicit Cursor Attribute

Function

It returns the status value of a cursor defined in PSM.

Syntax

<Explicit cursor attribute> ::=
    cursor_name '%' { ISOPEN | FOUND | NOTFOUND | ROWCOUNT }

Invocation and Access Rules

It can be used only in the body section of PSM.

Syntax Rules and Parameters

Description

Results according to the performing moment

Attribute name

Before OPEN

After OPEN

After FETCH

After CLOSE

ISOPEN

FALSE

TRUE

TRUE

FALSE

FOUND

NULL

NULL

TRUE/FALSE

NULL

NOTFOUND

NULL

NULL

TRUE/FALSE

NULL

ROWCOUNT

NULL

NULL

N (the number)

NULL

Examples

gSQL> CREATE TABLE T1 ( I1 INTEGER );

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> DECLARE
  CURSOR C1 IS SELECT * FROM T1; 
  V1 INTEGER := 0;
  V2 INTEGER := 0;
  TOTAL INTEGER := 0;
BEGIN
  FOR I IN 1..100 LOOP
    INSERT INTO T1 VALUES( I );
  END LOOP;

  COMMIT;

  IF NOT C1%ISOPEN THEN
    OPEN C1; 
  END IF; 

  LOOP
    FETCH C1 INTO V1; 
    EXIT WHEN C1%NOTFOUND;

    TOTAL := TOTAL + V1; 
    V2 := V1; 
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( 'COUNT = ' || C1%ROWCOUNT || ' TOTAL = ' || TOTAL );

  CLOSE C1; 

END;
/

COUNT = 100 TOTAL = 5050

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

Explicit Cursor Declaration and Definition

Function

It declares a cursor in DECLARE section of PSM.

Syntax

<cursor declaration> ::=
    CURSOR cursor_name [ <cursor param spec> ] RETURN rowtype
    ;

<cursor param spec> ::=
      ( <cursor param decl> [ , <cursor param decl> ] .. )

<cursor param decl> ::=
      param_name [ IN ] datatype [ { ':=' | DEFAULT } expression ]

<cursor definition> ::=
    CURSOR cursor_name [ <cursor param spec> ] [ RETURN rowtype ]
    IS <cursor query>
    ;

<cursor query> ::==
    select statement
    | select for update_statement
    | insert returning query statement
    | update returning query statement
    | delete returning query statement

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of a PL block.

Syntax Rules and Parameters

Cursor Name

It is a cursor name to be declared. 
The length of a cursor name should be shorter than 128 bytes.
It should be a unique name in that scope.

RowType

It defines a record type of a cursor.
The number of select targets specified when defining a cursor should be same, and the data type should be compatible.
If a rowtype is not specified, then a rowtype which is appropriate to a SELECT target of select_statement specified when defining a cursor is automatically specified.

Param Name

It is a name which distinguishes parameters within a specific cursor.
It should be a unique name in that cursor.
If the name is the same as a name of another variable which can be referenced within a scope, then a parameter of that cursor is preferentially referenced.

DataType

It specifies the data type of the corresponding parameter.
It can use all built-in types provided by GOLDILOCKS and types defined within PSM.
However, a statement which restricts a scope (precision/ scale) can not be specified in a built-in type, but it is internally specified as the maximum scope of the corresponding data type.

Cursor Query

Description

Examples

gSQL> CREATE TABLE t1( c1 INTEGER, c2 VARCHAR( 6 ) );

Table created.

gSQL> COMMIT;

Commit complete.
gSQL> 
DECLARE
   var1 t1%ROWTYPE;
   CURSOR cur1( p1 INTEGER, p2 varchar(6) ) RETURN t1%ROWTYPE 
      IS INSERT INTO t1 VALUES ( p1, p2 ) RETURNING *;
BEGIN
   OPEN cur1( 1, 'aaa' );
 
   FETCH cur1 INTO var1;
   DBMS_OUTPUT.PUT_LINE( 'var1.c1 = ' || var1.c1 || ' , var1.c2 = ' || var1.c2 );

   CLOSE cur1;
END;
/

var1.c1 = 1 , var1.c2 = aaa
Anonymous PL block executed.
gSQL>
DECLARE
   var1 t1%ROWTYPE;
   CURSOR cur1( p1 INTEGER, p2 varchar(6) ) RETURN t1%ROWTYPE IS 
      SELECT * FROM t1 WHERE c1 = p1 AND c2 = p2;
BEGIN
   OPEN cur1( 1, 'aaa' );

   FETCH cur1 INTO var1;
   DBMS_OUTPUT.PUT_LINE( 'var1.c1 = ' || var1.c1 || ' , var1.c2 = ' || var1.c2 );

   CLOSE cur1;
END;
/

var1.c1 = 1 , var1.c2 = aaa
Anonymous PL block executed.
gSQL> 
DECLARE
   var1 t1%ROWTYPE;
   CURSOR cur1( p1 INTEGER, p2 varchar(6) ) RETURN t1%ROWTYPE IS
      UPDATE t1 SET c1 = p1, c2 = p2 RETURNING *;
BEGIN
   OPEN cur1( 3, 'ccc' );
 
   FETCH cur1 INTO var1;
   DBMS_OUTPUT.PUT_LINE( 'var1.c1 = ' || var1.c1 || ' , var1.c2 = ' || var1.c2 );

   CLOSE cur1;
END;
/

var1.c1 = 3 , var1.c2 = ccc
Anonymous PL block executed.
gSQL> 
DECLARE
   var1 t1%ROWTYPE;
   CURSOR cur1( p1 INTEGER, p2 varchar(6) ) RETURN t1%ROWTYPE IS
      DELETE FROM t1 WHERE c1 = p1 AND c2 = p2 RETURNING *;
BEGIN
   OPEN cur1( 3, 'ccc' );
 
   FETCH cur1 INTO var1;
   DBMS_OUTPUT.PUT_LINE( 'var1.c1 = ' || var1.c1 || ' , var1.c2 = ' || var1.c2 );

   CLOSE cur1;
END;
/

var1.c1 = 3 , var1.c2 = ccc
Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

FETCH Statement

Function

It fetches a single record of OPEN cursor.

Syntax

<fetch statement> ::=
    FETCH cursor_name <into clause>
    ;

<into clause> ::=
    INTO { variable [ , variable ] .. | record }

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

It fetches a record from an open cursor, then copies the value to a variable specified in INTO clause. 
If a cursor is declared only but not defined, then an error occurs.
The cursor should be open.
A variable type given to INTO clause should be compatible with a data type of the fetched record result.
The number of variables given to INTO clause should be the same as the number of the cursor's SELECT targets.
However, if a variable given in INTO clause is a record type, then only a single variable should be specified.
Also, the number of the record variable fields should be the same as the number of SELECT targets.
If a fetch is called when a record to be fetched does not exist, then the value of target variables in INTO clause is not altered.

Examples

gSQL> CREATE TABLE T1 ( I1 INTEGER );

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> DECLARE
  CURSOR C1 IS SELECT * FROM T1; 
  V1 INTEGER := 0;
  V2 INTEGER := 0;
  CNT INTEGER := 0;
  TOTAL INTEGER := 0;
BEGIN
  FOR I IN 1..100 LOOP
    INSERT INTO T1 VALUES( I );
  END LOOP;

  COMMIT;

  OPEN C1; 

  LOOP
    FETCH C1 INTO V1; 

    CNT := CNT + 1;
    TOTAL := TOTAL + V1; 
    V2 := V1; 
    EXIT WHEN V1 = 100; 
  END LOOP;

  CLOSE C1; 

  DBMS_OUTPUT.PUT_LINE( 'CNT = ' || CNT || ' TOTAL = ' || TOTAL );
END;
/

CNT = 100 TOTAL = 5050

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

FOR LOOP Statement

Function

As long as an index variable has the given value scope, it performs internal statements by increasing or reversing the index variable by 1.

Syntax

<for loop statement> ::=
    FOR index_variable_name IN [ REVERSE ] lower_bound .. upper_bound
    LOOP { <SQL procedure statement> ; }... END LOOP [ loop_name ]
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

for loop statement performs an internal statement list by increasing or decreasing the index variable value.

Examples

gSQL> BEGIN
  FOR I IN 0 .. 5 LOOP
    DBMS_OUTPUT.PUT_LINE( 'I = ' || I );
  END LOOP;
END;
/

I = 0
I = 1
I = 2
I = 3
I = 4
I = 5
Anonymous PL block executed.


gSQL> BEGIN
  FOR I IN REVERSE 0 .. 5 LOOP
    DBMS_OUTPUT.PUT_LINE( 'I = ' || I );
  END LOOP;
END;
/

I = 5
I = 4
I = 3
I = 2
I = 1
I = 0
Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

Function Declaration and Definition

Function

It declares and defines a function.

Syntax

<function declaration> ::= 
        FUNCTION <function name> 
        [ ( <parameter list> ) ]
        <return clause>
        [ <function characteristics list> ]
        ; 
 
<function definition> ::= 
        FUNCTION <function name> 
        [ ( <parameter list> ) ]
        <return clause>
        [ <function characteristics list> ]
        { IS | AS }
        <routine body>
        ; 
        { IS | AS }
        <routine body>
        ; 

<parameter list> ::=
      <parameter> [ , ... ]

<parameter> ::=
      <parameter name>
      [ <parameter mode> ]
      <datatype>
      [ <parameter default> ]

<parameter mode> ::= 
      IN 
    | OUT 
    | IN OUT

<parameter default> ::= 
      { := | DEFAULT } <value expression>

<function characteristics list> ::=
      <function characteristics> [ ... ]

<function characteristics> ::=
      <deterministic characteristic>
    | <null-call clause>
    | <SQL-data access indication>

<routine body> ::=
      <SQL body>
    | <external body>

<SQL body> ::=
      [ <declare item> ]
      <body>

<external body> ::=
      <call specification>

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of a PL block.

Syntax Rules and Parameters

function name

It is a name of function to be created in a PL block, and it should be a unique name in a PL block.
In other words, PL item and function declared in PL block can not have the same name.
The length of a function name should be shorter than 128 bytes.

parameter name

It defines a parameter name of the function.
The name of each parameter should be unique in a function.
In other words, the function's parameter and PL item can not have the same name.
The length of a parameter name should be shorter than 128 bytes.
The maximum number of parameters available in a single function is limitless.

parameter mode

It sets each parameter mode. 
The parameter modes are IN, OUT, and IN OUT.
If the parameter mode is not specified, the default mode is IN.

parameter default

It is the default value of the parameter.
The parameter with the specified parameter default can be omitted when executing the function. 
If the parameter is not specified but omitted, then the default value is <value expression> specified when defining the parameter.
The datatype of <value expression> should be the datatype of the parameter.
All parameters defined after the parameter having <parameter default> should have <parameter default>.

return clause

It defines the return type of the function. 
It is defined as follows in <return clause>.

table function column list

It is the column name of the result set returned by the table function.
The length of a column name should be shorter than 128 bytes.
The number of columns are limitless.
Each column name is unique in <table function column list>.
The column name can be the same as the parameter name and the declare item name.
The column defined in <table function column list> can not be referenced in PL block of the function.

function characteristics

<function characteristics> specifies the characteristics of the function.
The redundant characteristics are not allowed.
For more information, refer to Routine Characteristics.

routine body

Description

It declares and defines the function as follows.
The usage of the function is the same as that of schema-level function.

Examples

gSQL> CREATE TABLE t_score( c_grade INTEGER, c_score INTEGER ); 
 
Table created. 
 
gSQL> INSERT INTO t_score VALUES ( 1 , 98 ) , ( 1 , 97 ) , ( 1 , 99 ), 
                                 ( 2 , 95 ) , ( 2 , 98 ) , ( 2 , 92 ), 
                                 ( 3 , 98 ) , ( 3 , 96 ) , ( 3 , 94 ); 
 
9 rows created. 
 
gSQL> COMMIT; 
 
Commit complete.
gSQL> 
DECLARE  
  v_max_score INTEGER; 
   
  FUNCTION nestedfunc RETURN INTEGER; 
   
  FUNCTION nestedfunc RETURN INTEGER AS 
    v_max INTEGER; 
  BEGIN 
    SELECT MAX( c_score )
      INTO v_max 
      FROM t_score;
     
    RETURN v_max; 
  END; 
BEGIN 
  v_max_score := nestedfunc; 
 
  DBMS_OUTPUT.PUT_LINE( 'Max Score : ' || v_max_score ); 
END; 
/ 

Max Score : 99
Anonymous PL block executed.
gSQL>  
CREATE OR REPLACE PACKAGE pkg1 AS 
  -- Declare Package Public Function 
  FUNCTION func1( p_grade INTEGER, p_option VARCHAR ) RETURN INTEGER;
END; 
/ 
 
Package created. 

gSQL>
CREATE OR REPLACE PACKAGE BODY pkg1 AS 
  -- Declare Package Private Function 
  FUNCTION func2( p_grade INTEGER, p_option VARCHAR ) RETURN INTEGER;
 
  -- Define Package Public Function 
  FUNCTION func1( p_grade INTEGER, p_option VARCHAR ) RETURN INTEGER AS
    var INTEGER; 
  BEGIN 
    var := func2( p_grade, p_option ); 
 
    RETURN var; 
  END; 
 
  -- Define Package Private Function 
  FUNCTION func2( p_grade INTEGER, p_option VARCHAR ) RETURN INTEGER AS
    var INTEGER; 
  BEGIN 
    IF p_option = 'MIN' THEN 
      SELECT MIN(c_score) INTO var FROM t_score WHERE c_grade = p_grade; 
    ELSIF p_option = 'MAX' THEN 
      SELECT MAX(c_score) INTO var FROM t_score WHERE c_grade = p_grade; 
    ELSIF p_option = 'AVG' THEN 
      SELECT AVG(c_score) INTO var FROM t_score WHERE c_grade = p_grade; 
    ELSE 
      var := -1; 
    END IF; 
 
    RETURN var; 
  END; 
END; 
/ 
 
Package created. 
 
gSQL> SELECT pkg1.func1(  1 , 'MAX' ) FROM DUAL; 
 
PKG1.FUNC1(  1 , 'MAX' ) 
------------------------ 
                      99 
 
1 row selected.

Compatibility

It is the same as a schema-level function.

For More Information

Refer to CREATE FUNCTION.

GOTO Statement

Function

It tries to jump into the nearest statement which has a given label among statements accessible from the current location.

Syntax

<goto statement> ::=
    GOTO label_name
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

It starts performing by jumping into a statement which has the corresponding label name.
If multiple candidate statements exist, then it jumps into the nearest statement.
It can jump only to a statement (exist in a nested scope) which is visible in the current location.
Both forward jump and backward jump are possible.

Examples

gSQL> DECLARE
  V1 INTEGER := 0;
BEGIN
  <<LABEL1>>
  IF V1 > 0 THEN
    GOTO LABEL2;
  END IF; 
  V1 := V1 + 1;
  DBMS_OUTPUT.PUT_LINE('a');
  GOTO LABEL1;
  DBMS_OUTPUT.PUT_LINE('b');
  <<LABEL2>>
  DBMS_OUTPUT.PUT_LINE('c');
END;
/
a
c

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

IF Statement

Function

It performs a statement list corresponding to the condition returning TRUE among the given conditions.

Syntax

<if statement> ::=
    IF <search condition> <if statement then clause>
    [ <if statement elsif clase> ]
    [ <if statement else clase> ]
    END IF
    ;

<if statement then clause> ::=
    THEN <executable statement list>

<if statement elsif clause> ::=
    ELSIF <search condition> THEN <executable statement list>

<if statement elsif clause> ::=
    ELSE <executable statement list>

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

Like as CASE statement, it performs statement lists of IF, ELSIF clauses returning TRUE by evaluating conditions.
If it can not satisfy any condition and <if statement else clause> exists, then it performs the corresponding statement. 
ELSIF clauses evaluates conditional expressions in order, and stops evaluating after finding the TRUE conditional clause.

Examples

gSQL> DECLARE
  V1 INTEGER := 10;
BEGIN
  IF V1 > 0 THEN
    DBMS_OUTPUT.PUT_LINE( 'POSITIVE' );
  ELSIF V1 = 0 THEN
    DBMS_OUTPUT.PUT_LINE( 'ZERO' );
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'NEGATIVE' );
  END IF;
END;
/
POSITIVE

Anonymous PL block executed.

Compatibility

<if statement> statement is the same as a syntax and an operation of the SQL standard.

SQL standard compatibility

Feature ID

Description

Compatibility

P002

Computational completeness

O

Implicit Cursor Attribute

Function

It returns the status value of an implicit cursor defined in PSM.

Syntax

<Implicit cursor attribute> ::=
    SQL '%' { ISOPEN | FOUND | NOTFOUND | ROWCOUNT }

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Description

Examples

DECLARE
V1 INTEGER;
BEGIN
    SELECT COUNT(*) INTO V1 FROM T1;
    DBMS_OUTPUT.PUT_LINE('COUNT RET    = ' || V1);
    DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN   = ' || SQL%ISOPEN);
    DBMS_OUTPUT.PUT_LINE('SQL%FOUND    = ' || SQL%FOUND);
    DBMS_OUTPUT.PUT_LINE('SQL%NOTFOUND = ' || SQL%NOTFOUND);
    DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT = ' || SQL%ROWCOUNT);
END;
/
COUNT RET    = 0
SQL%ISOPEN   = FALSE
SQL%FOUND    = TRUE
SQL%NOTFOUND = FALSE
SQL%ROWCOUNT = 1

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

INSERT Statement Extension

Function

It is an extended feature of an insert statement to input data by specifying record type variable supported in PSM in VALUES clause.

Syntax

<PSM Insert Statement Extension Statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
           <Insert_source>
           [ <Returning_into_clause> ]
    ;

<Insert_source> ::=
      <value-list>
    | <from_subquery>
    | <from_default>


<from subquery> ::=
    <query_expression>


<from default> ::=
    DEFAULT VALUES


<Value-List> ::=
      VALUES <Value_item> [ , ...]


<value-Item> ::=
     PSM-Record-Type-Variable
     |  ( { <value expression> | DEFAULT } [, ...] ) 


<Returning_into_clause> ::=
      [ RETURN | RETURNING ] { * | { <value_expression> [ [AS] alias_name ] } [, ...] INTO variable_name [, ...]

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.
A PSM insert extension statement can not be used in an original SQL statement of EXECUTE IMMEDIATE.

Syntax Rules and Parameters

It is operated the same as the basic syntax of an insert statement. However, a feature specifying PSM record type variables are added other than a feature consecutively listing existing value expressions in parentheses in value item.
A PSM record type variable should be specified when using a variable without parentheses in Value_Item. 
When using it in an insert extension statement form, a variable which is not a record type can not be used being mixed.

Description

It stores a record by using a record type variable of PSM other than a general insert statement, or obtains a result through returning into. 
For more information about insert, refer to the following example.

Examples

gSQL> DECLARE
  rec t1%ROWTYPE;
BEGIN
    rec.i1 := 'AAA';
    rec.i2 := 'BBB';
    rec.i3 := 'CCC';

    INSERT INTO t1 (i1, i2, i3) VALUES rec ;
END;
/

Anonymous PL block executed.


gSQL> SELECT * FROM t1;

I1  I2  I3 
--- --- ---
AAA BBB CCC

Compatibility

It is not defined in the SQL standard.

INSERT INTO ... UPDATE Statement Extension

Function

It is the extended function of insert into .. update statement. It creates a new row in a table by specifying the record type variable supported by PSM in a values clause, or updates the row by specifying the variable in a set clause.

Syntax

<PSM Upsert Statement Extention Statement > ::=    
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
        [ <Returning_into_clause> ]
    ;

<insert source> ::=
      <values-list>
    | <from subquery>
    | <from_default>

<Value-List> ::=
      VALUES <Value_item> [ , ...]

<value-Item> ::=
         PSM-Record-Type-Variable
     |  ( { <value expression> | DEFAULT } [, ...] ) 

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE <target-list>

<target-List> ::=
        SET <set clause> [, ...]
      | SET ROW = <psm_variable>

<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

<returning_into_clause> ::=
      [ RETURN | RETURNING ] { * | { <value_expression> [ [AS] alias_name ] } [, ...] INTO variable_name [, ...]

Invocation and Access Rules

It can be used only within PSM. (e.g. procedure, function, package)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

Returning into statement in an upsert statement stores the inserted result when insert is executed, and it stores the updated result when update is executed.

Examples

gSQL> CREATE TABLE t1( c1 INTEGER UNIQUE, c2 INTEGER, c3 INTEGER );

Table created.

gSQL> DECLARE
  v_rec t1%ROWTYPE;
BEGIN
  v_rec.c1 := 1;
  v_rec.c2 := 1;
  v_rec.c3 := 1;
  
  INSERT INTO t1 VALUES v_rec ON DUPLICATE KEY UPDATE SET c1 = c1 + 1;  
END;
/

Anonymous PL block executed.

gSQL> SELECT * FROM t1; 

C1 C2 C3
-- -- --
 1  1  1

1 row selected.
gSQL> CREATE TABLE t1( c1 INTEGER UNIQUE, c2 INTEGER, c3 INTEGER );

Table created.

gSQL> INSERT INTO t1 VALUES ( 1 , 1 , 1 );

1 row created.

gSQL> DECLARE
  v_rec t1%ROWTYPE;
BEGIN
  v_rec.c1 := 2;
  v_rec.c2 := 2;
  v_rec.c3 := 2;

  INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE SET ROW = v_rec;
END;
/

gSQL> SELECT * FROM t1;

C1 C2 C3
-- -- --
 2  2  2

1 row selected.
gSQL> CREATE TABLE t1( c1 INTEGER UNIQUE, c2 INTEGER, c3 INTEGER );

Table created.

gSQL> DECLARE
  v_rec1 t1%ROWTYPE;
  v_rec2 t1%ROWTYPE;
BEGIN
  v_rec1.c1 := 1;
  v_rec1.c2 := 1;
  v_rec1.c3 := 1;

  INSERT INTO t1 VALUES v_rec1 ON DUPLICATE KEY UPDATE SET c1 = c1 + 1 RETURNING * INTO v_rec2;
  DBMS_OUTPUT.PUT_LINE( 'v_rec2.c1 : ' || v_rec2.c1 );
  DBMS_OUTPUT.PUT_LINE( 'v_rec2.c2 : ' || v_rec2.c2 );
  DBMS_OUTPUT.PUT_LINE( 'v_rec2.c3 : ' || v_rec2.c3 );
END;
/

v_rec2.c1 : 1
v_rec2.c2 : 1
v_rec2.c3 : 1
Anonymous PL block executed.

gSQL> SELECT * FROM t1; 

C1 C2 C3
-- -- --
 1  1  1

1 row selected.
gSQL> CREATE TABLE t1( c1 INTEGER UNIQUE, c2 INTEGER, c3 INTEGER );

Table created.

gSQL> INSERT INTO t1 VALUES ( 1 , 1 , 1 );

1 row created.

gSQL> DECLARE
  v_rec1 t1%ROWTYPE;
  v_rec2 t1%ROWTYPE;
BEGIN
  v_rec1.c1 := 2;
  v_rec1.c2 := 2;
  v_rec1.c3 := 2;

  INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE SET ROW = v_rec1 RETURNING * INTO v_rec2;
  DBMS_OUTPUT.PUT_LINE( 'v_rec2.c1 : ' || v_rec2.c1 );
  DBMS_OUTPUT.PUT_LINE( 'v_rec2.c2 : ' || v_rec2.c2 );
  DBMS_OUTPUT.PUT_LINE( 'v_rec2.c3 : ' || v_rec2.c3 );
END;
/

v_rec2.c1 : 2
v_rec2.c2 : 2
v_rec2.c3 : 2
Anonymous PL block executed.

gSQL> SELECT * FROM t1;

C1 C2 C3
-- -- --
 2  2  2

1 row selected.

Compatibility

It is not defined in the SQL standard.

Merge Statement Extension

Function

It is the extended merge statement, and it inserts, updates or deletes the table record according to the condition by using the variables supported by PSM.

Syntax

<merge statement> ::=
    MERGE [ <hint clause> ] INTO <target table name> [ [ AS ] <target alias> ]
    USING <source relation>
    ON <merge join condition>
    <merge operation specification>
    ;

<target table> ::=
    <table name>

<target alias> ::=
    <correlation name>
    
<source relation> ::=
    {
        <table name> [ [ AS ] <source alias> ]
      | <table subquery> [ [ AS ] <source alias> ]    
    }

<source alias> ::=
    <correlation name>

<merge join condition> ::=
    <search condition>

<merge operation specification> ::=
    <merge when clause> [...]

<merge when clause> ::=
      <merge when matched clause>
    | <merge when not matched clause>

<merge when matched clause> ::=
    WHEN MATCHED [ AND <search condition> ] 
    THEN { <merge update> | <merge delete> | <merge do nothing> }

<merge when not matched clause> ::=
    WHEN NOT MATCHED [ AND <search condition> ] 
    THEN { <merge insert> | <merge do nothing> }

<merge update> ::=
    UPDATE <target list>

<target list> ::=
      SET <set clause> [, ...]
    | SET ROW = <PSM record type variable>

<set clause> ::=
      <column name> = { <value expression> | DEFAULT }
    | ( <column name> [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )

<merge delete> ::=
    DELETE

<merge insert> ::=
    INSERT [ (  column name [, ...] ) ] <insert source>

<insert source> ::=
      VALUES <value item> 
    | DEFAULT VALUES

<value item> ::=
      <PSM record type variable>
    | ( { <value expression> | DEFAULT } [, ...] )

<merge do nothing> ::=
    DO NOTHING

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Generally, it is the same as the syntax rules of the merge statement, but the following are added.

Description

It is the extended merge statement to use the record type variable supported by PSM.
For more information, refer to Merge Statement.

Examples

gSQL> CREATE TABLE t1 ( c1 INTEGER , c2 INTEGER , c3 INTEGER );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 , 1 , 1 ) , ( 3 , 3 , 3 ) , ( 5 , 5 , 5 );

3 rows created.

gSQL> COMMIT;

Commit complete.

gSQL> CREATE TABLE t2( c1 INTEGER , c2 INTEGER , c3 INTEGER );

Table created.

gSQL> INSERT INTO t2 VALUES( 1 , 1 , 1 ) , ( 2 , 2 , 2 ) , ( 3 , 3 , 3 ) , ( 4 , 4 , 4 ) , ( 5 , 5 , 5 );

5 rows created.

gSQL> COMMIT;

Commit complete.

gSQL> 
DECLARE
  TYPE rec IS RECORD( f1 INTEGER , f2 INTEGER , f3 INTEGER );
  v_rec rec;
BEGIN
  v_rec.f1 := 10;
  v_rec.f2 := 20;
  v_rec.f3 := 30;  
  
  MERGE INTO t1   
  USING t2
  ON t1.c1 = t2.c1
  WHEN MATCHED AND t1.c1 = 1 THEN UPDATE SET ROW = v_rec
  WHEN MATCHED AND t1.c1 = 3 THEN DELETE
  WHEN MATCHED AND t1.c1 = 5 THEN DO NOTHING
  WHEN NOT MATCHED AND t2.c1 = 2 THEN INSERT VALUES v_rec
  WHEN NOT MATCHED AND t2.c1 = 4 THEN DO NOTHING;
END;
/

Anonymous PL block executed.

gSQL> SELECT * FROM t1;

C1 C2 C3
-- -- --
10 20 30
 5  5  5
10 20 30

3 rows selected.

Compatibility

The SQL standard does not define it.

NULL Statement

Function

It is a statement without any feature.

Syntax

<null statement> ::=
    NULL
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Description

It is a statement without any feature, and used to set a label of a specific location.

Examples

gSQL> BEGIN
  FOR i in 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE( i );
    IF i > 5 THEN
      GOTO label1;
    END IF;
  END LOOP;
  <<label1>>
  NULL;
END;
/
1
2
3
4
5
6

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

OPEN Statement

Function

It executes SELECT statement of a cursor defined in PSM.

Syntax

<open statement> ::=
    OPEN cursor_name [ <actual param spec> ]
    ;

<actual param spec> ::=
      ( expression [ , expression ] .. )

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

It executes SELECT or SELECT ... FOR UPDATE statement of a defined cursor.
If a cursor is declared only but is not defined, then an error occurs.
Values of actual parameters should be compatible with the data type of those parameters.
The number of actual parameters should be same as the number of parameters of a cursor.
If it is smaller than the number of parameters of a cursor, then the default value should be specified in all other parameters.

Examples

gSQL> CREATE TABLE T1 ( I1 INTEGER, I2 VARCHAR(10) );

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> INSERT INTO T1 VALUES( 1, 'AAA' );

1 row created.

gSQL> INSERT INTO T1 VALUES( 2, 'BBB' );

1 row created.

gSQL> INSERT INTO T1 VALUES( 3, 'CCC' );

1 row created.

gSQL> COMMIT;

Commit complete.

gSQL> DECLARE
  CURSOR C1( A1 INTEGER := 1, A2 VARCHAR DEFAULT 'AAA') IS SELECT * FROM T1 WHERE I1 = A1 AND I2 = A2;
  V1 INTEGER;
  V2 VARCHAR(10);
BEGIN
  OPEN C1( 1 );
  FETCH C1 INTO V1, V2;
  DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 || ' V2 = ' || V2 );

  CLOSE C1;
END;
/

V1 = 1 V2 = AAA

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

OPEN FOR Statement

Function

It opens a single cursor by executing SELECT statement through a cursor variable defined in PSM.

Syntax

<open statement> ::=
    OPEN cursor_variable_name FOR <cursor_query>
    ;

<cursor_query> ::==
      <static_cursor_query>
    | <dynamic_cursor_query> [ <using_clause> ]

<static_cursor_query> ::==
      select_statement
    | select_for_update_statement
    | insert_returning_query_statement
    | update_returning_query_statement
    | delete_returning_query_statement

<dynamic_cursor_query>
      single_quote_string
    | psm_variable

<using_clause> ::=
    USING [ <bind_type> ] <expression> [ {, [ <bind_type> ] <expression>} ... ]

<bind_type> ::=
     IN
   | OUT
   | IN OUT

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

It executes the cursor query of the defined cursor variable. 
If the cursor variable is previously open, then that cursor is executed after it is automatically closed and reopened.

Examples

gSQL> CREATE TABLE t1( c1 INTEGER, c2 VARCHAR( 6 ) );

Table created.

gSQL> COMMIT;

Commit complete.
gSQL> 
DECLARE
   var1 t1%ROWTYPE;
   curvar1 SYS_REFCURSOR;
BEGIN
   OPEN curvar1 FOR INSERT INTO t1 VALUES ( 1 , 'aaa' ) RETURNING *;

   FETCH curvar1 INTO var1;
   DBMS_OUTPUT.PUT_LINE( 'var1.c1 = ' || var1.c1 || ' , var1.c2 = ' || var1.c2 );

   CLOSE curvar1;
END;
/

var1.c1 = 1 , var1.c2 = aaa
Anonymous PL block executed.
gSQL> 
DECLARE
   var1 t1%ROWTYPE;
   curvar1 SYS_REFCURSOR;
BEGIN
   OPEN curvar1 FOR SELECT * FROM t1;
 
   FETCH curvar1 INTO var1;
   DBMS_OUTPUT.PUT_LINE( 'var1.c1 = ' || var1.c1 || ' , var1.c2 = ' || var1.c2 );

   CLOSE curvar1;
END;
/

var1.c1 = 1 , var1.c2 = aaa
Anonymous PL block executed.
gSQL> 
DECLARE
   var1 t1%ROWTYPE;
   curvar1 SYS_REFCURSOR;
BEGIN
   OPEN curvar1 FOR UPDATE t1 SET c1 = 3, c2 = 'ccc' RETURNING *;
 
   FETCH curvar1 INTO var1;
   DBMS_OUTPUT.PUT_LINE( 'var1.c1 = ' || var1.c1 || ' , var1.c2 = ' || var1.c2 );

   CLOSE curvar1;
END;
/

var1.c1 = 3 , var1.c2 = ccc
Anonymous PL block executed.
gSQL> 
DECLARE
   var1 t1%ROWTYPE;
   curvar1 SYS_REFCURSOR;
BEGIN
   OPEN curvar1 FOR DELETE FROM t1 RETURNING *; 

   FETCH curvar1 INTO var1;
   DBMS_OUTPUT.PUT_LINE( 'var1.c1 = ' || var1.c1 || ' , var1.c2 = ' || var1.c2 );

   CLOSE curvar1;
END;
/

var1.c1 = 3 , var1.c2 = ccc
Anonymous PL block executed.
gSQL> INSERT INTO t1 VALUES( 100 , 'BBB' );
gSQL> COMMIT;

gSQL> 
DECLARE
  v_int_in     INTEGER;
  v_varchar_in VARCHAR(6);
  v_out        t1%ROWTYPE;
  v_sql        VARCHAR(1024);
  cv           SYS_REFCURSOR;
BEGIN
  v_sql := 'SELECT * FROM t1 WHERE c1 = ? AND c2 = ?';

  v_int_in := 100;
  v_varchar_in := 'BBB';
  
  OPEN cv FOR v_sql USING IN v_int_in, v_varchar_in;
  
  FETCH cv INTO v_out;
  DBMS_OUTPUT.PUT_LINE( 'c1 : ' || v_out.c1 || ' , v_out.c2 : ' || v_out.c2 );
  
  CLOSE cv;
END;
/

c1 : 100 , v_out.c2 : BBB
Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

Procedure Call

Function

It calls a user-defined procedure, a built-in procedure or a nested procedure.

Syntax

<Procedure call> ::=
    proc_name [ ( expr { , expr } ... ) ]
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Format

Syntax

Description

Single identifier

procedure_name

It calls the procedure of the given name.


It is searched in the following order.

1. nested procedure

2. schema-level procedure

identifier chain

label_name.procedure_name

It calls a nested procedure.

schema_name.procedure_name

It calls a schema-level procedure.

package_name.procedure_name

It calls a built-in procedure.

If the given the number and type of arguments which were given in the procedure found first are wrong when searching with the given name (proc_name), then it does not search for another procedure but causes an error.

Description

It calls a user-defined procedure, a built-in procedure or a nested procedure which was defined an advance.
The argument value in which the default value is defined can be omitted when calling.
When using a procedure variable or bind parameter (?, :V1) in an argument which were defines as OUT or IN-OUT, then the returned value is obtained.

Examples

gSQL> DECLARE
  PROCEDURE PROC1( A1 INTEGER )
    IS  
    BEGIN
      PUT_LINE( 'A1 = ' || A1 );
    END;
BEGIN
  PROC1( 100 );
END;
/
A1 = 100

Anonymous PL block executed.

Compatibility

The SQL standard requires to use <call statement>.

Procedure Declaration and Definition

Function

It declares and defines a procedure.

Syntax

<procedure declaration> ::=
      PROCEDURE <procedure name>
      [ ( <parameter list> ) ]
      [ <procedure characteristics list> ]
      ;
 
<procedure definition> ::= 
      PROCEDURE <procedure name>
      [ ( <parameter list> ) ]
      [ <procedure characteristics list> ]
      { IS | AS }
      <routine body>
      ; 

<parameter list> ::=
      <parameter> [ , ... ]

<parameter> ::=
      <parameter name>
      [ <parameter mode> ]
      <datatype>
      [ <parameter default> ]

<parameter mode> ::= 
      IN 
    | OUT 
    | IN OUT

<parameter default> ::= 
      { := | DEFAULT } <value expression>

<procedure characteristics list> ::=
      <procedure characteristics> [ ... ]

<procedure characteristics> ::=
      <deterministic characteristic>
    | <SQL-data access indication>

<routine body> ::=
      <SQL body>
    | <external body>

<SQL body> ::=
      [ <declare item> ]
      <body>

<external body> ::=
      <call specification>

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function) 
It can be used only in the declaration section of a PL block.

Syntax Rules and Parameters

procedure name

It is a name of procedure to be created in a PL block, and it should be a unique name in a PL block. 
In other words, PL item and procedure declared in PL block can not have the same name. 
The length of a procedure name should be shorter than 128 bytes.

parameter name

It defines a parameter name of the procedure.
The name of each parameter should be unique in a procedure.
In other words, the procedure's parameter and PL item can not have the same name.
The length of a parameter name should be shorter than 128 bytes.
The maximum number of parameters available in a single procedure is limitless.

parameter mode

It sets each parameter mode. 
The parameter modes are IN, OUT, and IN OUT.
If the parameter mode is not specified, the default mode is IN.

parameter default

It is the default value of the parameter.
The parameter with the specified parameter default can be omitted when executing the procedure. 
If the parameter is not specified but omitted, then the default value is <value expression> specified when defining the parameter.
The datatype of <value expression> should be the datatype of the parameter.
All parameters defined after the parameter having <parameter default> should have <parameter default>.

procedure characteristics

<procedure characteristics> specifies the characteristics of the procedure.
The redundant characteristics are not allowed.
For more information, refer to Routine Characteristics.

routine body

Description

It declares and defines the procedure as follows.
The usage of the procedure is the same as that of schema-level procedure.

Examples

gSQL> 
DECLARE
  PROCEDURE proc1( p1 INTEGER ) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( 'p1 = ' || p1 );
  END;
BEGIN
  proc1( 100 );
END;
/

p1 = 100
Anonymous PL block executed.
gSQL>  
CREATE OR REPLACE PACKAGE pkg1 AS 
  -- Declare Package Public Function 
  PROCEDURE proc1( p1 INTEGER );
END; 
/ 
 
Package created. 

gSQL>
CREATE OR REPLACE PACKAGE BODY pkg1 AS 
  -- Declare Package Private Function 
  PROCEDURE proc2( p1 INTEGER );
 
  -- Define Package Public Function 
  PROCEDURE proc1( p1 INTEGER ) AS
  BEGIN 
    DBMS_OUTPUT.PUT_LINE( 'p1 of proc1 : ' || p1 );
    
    proc2( p1 * p1 );
  END; 
 
  -- Define Package Private Function 
  PROCEDURE proc2( p1 INTEGER ) AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( 'p1 of proc2 : ' || p1 );
  END; 
END; 
/ 

Package created.

gSQL> CALL pkg1.proc1( 10 );

p1 of proc1 : 10
p1 of proc2 : 100
Procedure Call complete.

Compatibility

It is the same as a schema-level procedure.

For More Information

Refer to CREATE PROCEDURE.

RAISE Statement

Function

It explicitly generates a user-defined exception.

Syntax

<RAISE Statement> ::= 
    RAISE  <Exception-Name>
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

If it can not be processed in a PL block in which an exception occurred, then it is spread to the superordinate PL block.
If an exception to be raised does not exist in a PL block including RAISE statement, nor does exist in all exception handlers within a superordinate PL block, then an error occurs.
It is spread from a PL block in which RAISE exception occurred to a superordinate PL block until it is processed, and it can not be spread to an exception handler of subordinate PL block.
Propagating user exception

Raise exception

Exception

handler

SCOPE

Exception

handler

Whether to spread it

to superordinate

User exception without error code

Same scope

X

It spreads "unhandled exception" error to a superordinate scope.

User exception with error code

Superordinate scope

X

It spreads a user exception.

User exception with error code

Same scope

X

Itspreads a user defined error code.

User exception with error code

Superordinate scope

X

Itspreads a user defined error code.

Examples

gSQL> DECLARE
V1 INTEGER;
exception1   EXCEPTION;
exception100 EXCEPTION;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Step1');
    BEGIN
        RAISE exception1;
        DBMS_OUTPUT.PUT_LINE('Step2');
        EXCEPTION WHEN Exception100 THEN DBMS_OUTPUT.PUT_LINE('in Exception');
    END;
    DBMS_OUTPUT.PUT_LINE('Step3');
    EXCEPTION WHEN Exception1 THEN DBMS_OUTPUT.PUT_LINE('out Exception');
    DBMS_OUTPUT.PUT_LINE('Step4');
END;
/
Step1
out Exception
Step4

Anonymous PL block executed.

Compatibility

The SQL standard specifies <handler declaration> and <condition declaration>, but it does not support the syntax.

For More Information

Refer to the following.

Record Variable Declaration

Function

It declares a record type variable in DECLARE section.

Syntax

<declare record variable> ::=
    variable_name <recordType> 
    ;

<recordType> ::=
     <tableName>%ROWTYPE
   | USER_DEFINED_DATA_TYPE

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of a PL block.

Syntax Rules and Parameters

Description

Examples

gSQL> DECLARE
  TYPE MY_REC1 IS RECORD ( F1 INTEGER, F2 VARCHAR(10) );
  V1 MY_REC1;
BEGIN
  V1.F1 := 1;
  V1.F2 := 'AAA';
  INSERT INTO T1 VALUES( V1.F1, V1.F2 );
END;
/

Anonymous PL block executed.


gSQL> COMMIT;

Commit complete.


gSQL> SELECT * FROM T1;

I1 I2
-- ---
 1 AAA

1 row selected.

Compatibility

It is not defined in the SQL standard.

RETURN Statement

Function

It specifies the value of which a function returns, then terminates the function. A procedure does not specify the return value, but terminates the procedure.

Syntax

<return statement> ::=
    RETURN [ return_value_expr ]
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.
It can not be used in the PL block of the table function.

Syntax Rules and Parameters

Description

It terminates a currently performing procedure/ function.
For a function, if RETURN statement is terminated without being performed, or if RETURN statement does not have return_value_expr, then an error occurs.
It can not be used in the table function.

Examples

gSQL> CREATE OR REPLACE FUNCTION FUNC1( A1 INTEGER, A2 INTEGER )

  RETURN INTEGER
  IS
    V1 INTEGER;
  BEGIN

    SELECT COUNT(*)
      INTO V1
      FROM T1
      WHERE T1.I1 >= A1 AND T1.I1 <= A2;

    RETURN V1;
  END;
  /

Function created.

Compatibility

It is specified in the SQL standard, but conformance rules do not exist.

RETURN TABLE Statement

Function

It returns the result set of executing the cursor variable's cursor query or the select statement, then terminates the function.

Syntax

<return table statement> ::= 
    RETURN TABLE ( { <cursor variable name> | <select statement> } ) 
    ;

Invocation and Access Rules

It can be used only in the table function within PSM. 
It can be used only in the body section of a table function block.

Syntax Rules and Parameters

Description

It returns the result set of executing the cursor variable's cursor query or select statement specified in RETURN TABLE statement. 
RETURN TABLE statement is available only in the table function.

Examples

gSQL> CREATE TABLE t_score( c_grade INTEGER, c_score INTEGER ); 
 
Table created. 
 
gSQL> INSERT INTO t_score VALUES ( 1 , 98 ) , ( 1 , 97 ) , ( 1 , 99 ), 
                                 ( 2 , 95 ) , ( 2 , 98 ) , ( 2 , 92 ), 
                                 ( 3 , 98 ) , ( 3 , 96 ) , ( 3 , 94 ); 
 
9 rows created. 
 
gSQL> COMMIT; 
 
Commit complete.
gSQL>  
CREATE FUNCTION func_cursor_variable( p_option VARCHAR ) 
  RETURN TABLE( f_class NUMBER, f_score NUMBER ) 
AS 
  s_cur SYS_REFCURSOR; 
BEGIN 
  IF p_option = 'MIN' THEN 
    OPEN s_cur FOR SELECT c_grade, MIN(c_score) FROM t_score GROUP BY c_grade; 
  ELSIF p_option = 'MAX' THEN 
    OPEN s_cur FOR SELECT c_grade, MAX(c_score) FROM t_score GROUP BY c_grade; 
  ELSIF p_option = 'AVG' THEN 
    OPEN s_cur FOR SELECT c_grade, AVG(c_score) FROM t_score GROUP BY c_grade; 
  ELSE 
    OPEN s_cur FOR SELECT NULL, NULL FROM dual; 
  END IF; 
 
  RETURN TABLE( s_cur ); 
END; 
/ 
 
Function created. 
 
gSQL> COMMIT; 
 
Commit complete. 
 
-- Calculate the lowest score in each grade.
gSQL> SELECT * FROM TABLE( func_cursor_variable( 'MIN' ) ); 
 
F_CLASS F_SCORE 
------- ------- 
      1      97 
      2      92 
      3      94 
 
3 rows selected.
gSQL>  
CREATE FUNCTION func_select( p_option VARCHAR ) 
    RETURN TABLE( f_class NUMBER, f_score NUMBER ) 
AS 
BEGIN 
  IF p_option = 'MIN' THEN 
    RETURN TABLE ( SELECT c_grade, MIN(c_score) FROM t_score GROUP BY c_grade ); 
  ELSIF p_option = 'MAX' THEN 
    RETURN TABLE ( SELECT c_grade, MAX(c_score) FROM t_score GROUP BY c_grade ); 
  ELSIF p_option = 'AVG' THEN 
    RETURN TABLE ( SELECT c_grade, AVG(c_score) FROM t_score GROUP BY c_grade ); 
  ELSE 
    RETURN TABLE ( SELECT NULL, NULL FROM dual ); 
  END IF; 
END; 
/ 
 
Function created. 
 
-- Calculate the average score in each grade.
gSQL> SELECT * FROM TABLE( func_select( 'AVG' ) ); 
 
F_CLASS F_SCORE 
------- ------- 
      1      98 
      2      95 
      3      96 
 
3 rows selected.

Compatibility

The SQL standard describes it in <return statement> statement.
However, the SQL standard does not define the cursor variable.

RETURNING INTO clause

Function

The data processed in an insert/ update/ delete is returned to a PSM variable.

Syntax

<Insert, Delete Returning_into_clause> ::=
      [ RETURN | RETURNING ] { * | { <value_expression> [ [AS] alias_name ] } [, ...] INTO variable_name [, ...]

<Update returning into clause> ::=
    { RETURN | RETURNING } [ NEW | OLD ] { * | { <value expression> [ [AS] alias_name] } [, ...] } INTO Variable [, ...]

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

A record type variable can not be used being mixed with other types.

Description

It stores before/ after record of processing an insert/ update/ delete statement through returning into.

Examples

gSQL> DECLARE
  rec t1%ROWTYPE;
BEGIN
    INSERT INTO t1 VALUES (1, 2, 3) RETURNING * INTO rec ;
    UPDATE T1 SET ROW = rec RETURNING * INTO rec;
    DELETE FROM T1 RETURNING * INTO rec;
END;
/

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

Routine Characteristics

Function

It specifies the characteristics of the routine.

Syntax

<deterministic characteristic> ::=
      DETERMINISTIC
    | NOT DETERMINISTIC

 <null-call clause> ::=
      RETURN NULL ON NULL INPUT
    | CALLED ON NULL INPUT

<SQL-data access indication> ::=
      NO SQL
    | CONTAINS SQL
    | READS SQL DATA
    | MODIFIES SQL DATA

Invocation and Access Rules

It can be used in the following statements.

Syntax Rules and Parameters

deterministic characteristic

It determines whether to return the same result value if inserting the same parameter whenever executing the routine.

If <deterministic characteristic> is omitted, then the default value is NOT DETERMINISTIC.
<deterministic characteristic> is semantic characteristic, and it can not determine the result of executing <routine body>. 
Therefore, even when it is DETERMINISTIC, if pl statement of routine body is not deterministic, then the different results can be returned for the same parameter.

null-call clause

It determines whether to execute routine when any of the routine parameter is NULL.

SQL-data access indication

<SQL-data access indication> can classifies and indicates <pl statement> which can be executed in the routine.

If <SQL-data access indication> is omitted, then the default value is MODIFIES SQL DATA.
Whether pl statement is executable according to SQL-data access indication

pl statement

Level Of SQL Data Access

NO SQL

CONTAINS SQL

READS SQL DATA

WRITE SQL DATA

Assignment

Statement


Y

Y

Y

Basic Loop

Statement


Y

Y

Y

Block Statement


Y

Y

Y

Call Specification

Y

Y

Y

Y

Case Statement


Y

Y

Y

Close Statement



Y

Y

Collection Method

Invocation


Y

Y

Y

Continue

Statement


Y

Y

Y

Cursor For Loop

Statement



Y

Y

Delete Statement

Extension




Y

Execute Immediate

Statement




Y

Exit Statement


Y

Y

Y

Fetch Statement



Y

Y

For Loop Statement


Y

Y

Y

Goto Statement


Y

Y

Y

If Statement


Y

Y

Y

Insert Statement

Extension




Y

Insert Into ...

Update Statement

Extension




Y

Null Statement


Y

Y

Y

Open Statement



Y

Y

Open For

Statement




Y

Procedure Call

Statement


Y

Y

Y

Raise Statement


Y

Y

Y

Return Statement


Y

Y

Y

Return Table

Statement



Y

Y

Select Into

Statement



Y

Y

Sql Statement




Y

Update Statement

Extension




Y

While Loop

Statement


Y

Y

Y

Description

It specifies the characteristics of the routine.
Each characteristic should not be redundant.

Examples

gSQL>
CREATE OR REPLACE FUNCTION func1( p1 INTEGER,
                                  p2 INTEGER )
   RETURN INTEGER
   DETERMINISTIC
   RETURN NULL ON NULL INPUT
   CONTAINS SQL
AS
  var1 INTEGER;
BEGIN
  var1 := p1 + p2;
  RETURN var1;
END;
/

Function created.
gSQL>
CREATE OR REPLACE FUNCTION func1( p1 INTEGER,
                                  p2 INTEGER )
   RETURN INTEGER
   DETERMINISTIC
AS
  var1 INTEGER;
BEGIN
  var1 := p1 + p2;
  RETURN var1;
END;
/

Function created.

gSQL> SELECT func1( 1 , 1 ) FROM dual;

FUNC1( 1 , 1 )
--------------
             2

1 row selected.

gSQL> SELECT func1( 1 , 1 ) FROM dual;

FUNC1( 1 , 1 )
--------------
             2

1 row selected.
gSQL>
CREATE OR REPLACE FUNCTION func1( p1 INTEGER,
                                  p2 INTEGER )
   RETURN INTEGER
   DETERMINISTIC
AS
  var1 INTEGER;
BEGIN
  var1 := random( p1, p2 );
  RETURN var1;
END;
/

Function created.

gSQL> SELECT func1( 1 , 10 ) FROM dual;

FUNC1( 1 , 10 )
---------------
              3

1 row selected.

gSQL> SELECT func1( 1 , 10 ) FROM dual;

FUNC1( 1 , 10 )
---------------
              5

1 row selected.
gSQL>
CREATE OR REPLACE FUNCTION func1( p1 INTEGER,
                                  p2 INTEGER )
   RETURN INTEGER
   RETURN NULL ON NULL INPUT
AS
  var1 INTEGER;
BEGIN
  var1 := 0;

  IF p1 IS NOT NULL THEN
     var1 := var1 + p1;
  END IF;

  IF p2 IS NOT NULL THEN
     var1 := var1 + p2;
  END IF;

  RETURN var1;
END;
/

Function created.

gSQL> SELECT func1( NULL , 1 ) FROM dual;

FUNC1( NULL , 1 )
-----------------
             null

1 row selected.

gSQL> SELECT func1( 1 , 1 ) FROM dual;

FUNC1( 1 , 1 )
--------------
             2

1 row selected.
gSQL>
CREATE OR REPLACE FUNCTION func1( p1 INTEGER,
                                  p2 INTEGER )
   RETURN INTEGER
   CONTAINS SQL
AS
  var1 INTEGER;
BEGIN
  var1 := p1 + p2;
  RETURN var1;
END;
/

Function created.

gSQL> SELECT func1( 1 , 1 ) FROM dual;

FUNC1( 1 , 1 )
--------------
             2

1 row selected.
gSQL>
CREATE OR REPLACE FUNCTION func1( p1 INTEGER,
                                  p2 INTEGER )
   RETURN INTEGER
   CONTAINS SQL
AS
  var1 INTEGER;
BEGIN
  SELECT c1 INTO var1 FROM t1;
  RETURN var1;
END;
/

ERR-01000(16409): Warning: Routine(FUNC1) has compilation errors : 
(1) at (8:3): ERR-2F000(17130): routine sql data access indicator violated
Function created.

Compatibility

The following are supported among <routine characteristic> in SQL standard.

For More Information

Refer to the following.

%ROWTYPE Attribute

Function

When declaring a variable, it defines the structure and type the same as those of a specific table, a specific cursor or a result set of a cursor variable.

Syntax

<rowtype attribute> ::=
    <identifier chain> % ROWTYPE

Invocation and Access Rules

Syntax Rules and Parameters

Description

Examples

gSQL> CREATE TABLE T1 ( I1 INTEGER NOT NULL, I2 VARCHAR(10) );

Table created.

gSQL> INSERT INTO T1 VALUES( 123, '1234567890' );

1 row created.


gSQL> COMMIT;

Commit complete.


gSQL> DECLARE
  V1 T1%ROWTYPE;
BEGIN
  SELECT * INTO V1.I1, V1.I2 FROM T1; 
  DBMS_OUTPUT.PUT_LINE( 'V1.I1 = ' || V1.I1 || ' V1.I2 = ' || V1.I2 );
END;
/
V1.I1 = 123 V1.I2 = 1234567890

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

Scalar Variable Declaration

Function

It declares a scalar variable in the declaration section.

Syntax

<declare scalar variable> ::=
    variable_name <data type> [ <variable initialize clause> ]
    ;

<variable initialize clause> ::=
      [ NOT NULL ] { DEFAULT | := } <value expression>

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of a PL block.

Syntax Rules and Parameters

Description

Examples

gSQL> CREATE TABLE T1 ( I1 INTEGER, I2 VARCHAR(10) );

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> DECLARE
V1 INTEGER := 100;
V2 INTEGER := -100;
V3 VARCHAR(10) := 'ABC';
BEGIN
  IF V1 > 50 THEN
    INSERT INTO T1 VALUES ( V1, V3 );
  ELSE
    INSERT INTO T1 VALUES ( V2, V3 );
  END IF;
END;
/

Anonymous PL block executed.

gSQL> SELECT * FROM T1;

 I1 I2 
--- ---
100 ABC

1 row selected.

Compatibility

SELECT INTO Statement

Function

A single row is returned through SELECT.

Syntax

<select statement: single row> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list>
        INTO <select target list>
        <table expression>
    ;

<select target list> ::=
    variable_name [, ...]

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

The rules are the same as those of a select statement except the rules for INTO clause.

Description

Examples

gSQL> CREATE TABLE T1 (c1 VARCHAR(20), c2 VARCHAR(20));

Table created.

gSQL> INSERT INTO T1 VALUES ('AAA', 'BBB'), ('BBB', 'CCC');

2 rows created.

gSQL> DECLARE
  v1 VARCHAR(20);
  v2 VARCHAR(20);
BEGIN
  SELECT * INTO v1, v2 FROM T1 WHERE c1 = 'AAA';
  DBMS_OUTPUT.PUT_LINE('V1=' || v1 || ', v2=' || v2);
END;
/
V1=AAA, v2=BBB

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

SQLCODE Function

Function

It returns an error code of a statement which was performed just before in PSM.

Syntax

<SQLCODE function> ::= SQLCODE

Invocation and Access Rules

It can be used only within PSM.

Syntax Rules and Parameters

It does not have a separate argument.

Description

It returns an error code of a statement performed in PL/ SQL.
A user-defined exception which does not assign an error code returns to 1 at the time when it is processed by a handler. 
When the error is completely processed by an exception handler, it returns to 0.

Examples

gSQL> DECLARE
V1 INTEGER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('SQLCODE=[' || SQLCODE || ']');
    DBMS_OUTPUT.PUT_LINE('SQLERRM=[' || SQLERRM || ']');
END;
/
SQLCODE=[0]
SQLERRM=[[SUNJESOFT][PL/SQL][GOLDILOCKS]successful completion]

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

SQLERRM Function

Function

It returns an error message of a statement which was performed just before in PSM.

Syntax

<SQLERRM function> ::= SQLERRM

Invocation and Access Rules

It can be used only within PSM.

Syntax Rules and Parameters

It does not have a separate argument.

Description

It returns an error message of a statement performed in PL/ SQL.
A user-defined exception which does not assign an error code returns to a user-defined exception at the time when it is processed by a handler. 
When the error is completely processed by an exception handler, it outputs successful completion message.

Examples

gSQL> DECLARE

V1 INTEGER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('SQLCODE=[' || SQLCODE || ']');
    DBMS_OUTPUT.PUT_LINE('SQLERRM=[' || SQLERRM || ']');
END;
/
SQLCODE=[0]
SQLERRM=[[SUNJESOFT][PL/SQL][GOLDILOCKS]successful completion]

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

%TYPE Attribute

Function

When declaring a variable or defining a specific field of RECORD type, it defines the type the same as the column of a specific table or another variable.

Syntax

<type attribute> ::=
    <identifier chain> % TYPE

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the declaration section of a PL block.
It can be used only for <data type> section when declaring a variable or a field of a record type.

Syntax Rules and Parameters

Description

Reference scope

The reference scope according to the referenced object types are as follows.

NOT NULL Constraints Variables References

It does not refer to the initial value when referring to NOT NULL attribute variable, so a new initial value should be specified. 
Setting an initial value of a field is not supported when using a type attribute for the field of a current record type variable, so NOT NULL type field can not be referenced.

Examples

gSQL> DECLARE
  V1 NUMBER(5,2) := 100.01;
  V2 V1%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
  DBMS_OUTPUT.PUT_LINE( 'V2 = ' || V2 );
END;
/
V1 = 100.01
V2 = 

Anonymous PL block executed.

Compatibility

It is not defined in the SQL standard.

UPDATE Statement Extension

Function

A feature altering a record by using a record type variable is added other than a feature consecutively listing the altering target columns of UNDATE statement in PSM.
It stores the result by using a record type variable in UPDATE (searched) RETURNING INTO clause in PSM.

Syntax

<update Extension statement : searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        <target-list>
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        [ <returning into clause> ]
    ;

<update statement: positioned> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        <Target-List>
        WHERE CURRENT OF cursor_name
    ;

<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }

<returning into clause> ::=
    { RETURN | RETURNING } [ NEW | OLD ] { * | { <value expression> [ [AS] alias_name] } [, ...] } INTO Variable [, ...]

<target-List> ::=
        SET <set clause> [, ...]
      | SET ROW = <psm_variable>

<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

It alters the record or stores the result of RETURNING INTO through a record type variable in PSM.

Examples

gSQL> CREATE TABLE T1 (C1 VARCHAR(20), C2 VARCHAR(20));
Table created.

gSQL> INSERT INTO T1 VALUES ('AAA', 'BBB'), ('BBB', 'CCC'), ('CCC', 'DDD');
3 rows created.


gSQL> DECLARE
    v1 t1%ROWTYPE;  
    v2 t1%ROWTYPE;  
BEGIN

    v1.c1 := '1';
    v1.c2 := '2';

    UPDATE T1 SET ROW = v1 WHERE c1 = 'AAA' RETURNING * INTO v2;
    DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT=' || SQL%ROWCOUNT );
    DBMS_OUTPUT.PUT_LINE('v2.c1=' || v2.c1 || ', v2.c2=' || v2.c2);
END;
/
SQL%ROWCOUNT=1
v2.c1=1, v2.c2=2

Anonymous PL block executed.

gSQL> SELECT * FROM T1 ORDER BY C1;

C1  C2
--- ---
1   2
BBB CCC
CCC DDD

3 rows selected.

Compatibility

It is not defined in the SQL standard.

WHILE LOOP Statement

Function

It performs internal statements during <search condition> returns TRUE value.

Syntax

<while loop statement> ::=
    WHILE <search condition>
    LOOP { <SQL procedure statement> ; }... END LOOP [ loop_name ]
    ;

Invocation and Access Rules

It can be used only within PSM. (e.g. package, procedure, function)
It can be used only in the body section of a PL block.

Syntax Rules and Parameters

Description

while loop statement performs an internal statement list as long as the evaluation result of <search condition> is TRUE.

Examples

gSQL> DECLARE
V1 integer := 0;
BEGIN
  WHILE V1 < 10 LOOP
    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
    V1 := V1 + 1;
  END LOOP;
END;
/
V1 = 0
V1 = 1
V1 = 2
V1 = 3
V1 = 4
V1 = 5
V1 = 6
V1 = 7
V1 = 8
V1 = 9

Anonymous PL block executed.

Compatibility

<while loop statement> statement is defined as <while statement> in the SQL standard. 
<while statement> of the SQL standard performs loop statement as DO ... END WHILE, but GOLDILOCKS performs it as LOOP ... END LOOP.

For More Information

Refer to the following.