TOP をテンプレートにして作成
ホーム
バックアップ
一覧
検索
最終更新
ヘルプ
ログイン
開始行
Stored Procedure Language (PSQL)
Table of Contents
PSQL Enhancements
PSQL Enhancements
Context Variable ROW_COUNT Enhanced
Explicit Cursors
Defaults for Stored Procedure Arguments
LEAVE <label> Syntax Support
OLD Context Variables Now Read-only
PSQL Stack Trace
Call a UDF as a Void Function (Procedure)
The following enhancements have been made to the PSQL lan...
Context Variable ROW_COUNT Enhanced
D. Yemanov
ROW_COUNT has been enhanced so that it can now return the...
For example, it can be used to check whether a singleton ...
..
BEGIN
SELECT COL FROM TAB INTO :VAR;
IF (ROW_COUNT = 0) THEN
EXCEPTION NO_DATA_FOUND;
END
..
See also its usage in the examples below for explicit PSQ...
Explicit Cursors
D. Yemanov
It is now possible to declare and use multiple cursors in...
Syntax pattern
DECLARE [VARIABLE] <cursor_name> CURSOR FOR ( <select...
OPEN <cursor_name>;
FETCH <cursor_name> INTO <var_name> [, <var_name> ...];
CLOSE <cursor_name>;
Examples
1.
DECLARE RNAME CHAR(31);
DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS );
BEGIN
OPEN C;
WHILE (1 = 1) DO
BEGIN
FETCH C INTO :RNAME;
IF (ROW_COUNT = 0) THEN
LEAVE;
SUSPEND;
END
CLOSE C;
END
2.
DECLARE RNAME CHAR(31);
DECLARE FNAME CHAR(31);
DECLARE C CURSOR FOR ( SELECT RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = :RNAME
ORDER BY RDB$FIELD_POSITION );
BEGIN
FOR
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
INTO :RNAME
DO
BEGIN
OPEN C;
FETCH C INTO :FNAME;
CLOSE C;
SUSPEND;
END
END
Note
Cursor declaration is allowed only in the declaration sec...
Cursor names are required to be unique in the given conte...
Positioned updates and deletes with cursors using the WHE...
Attempts to fetch from or close a FOR SELECT cursor are p...
Attempts to open a cursor that is already open, or to fet...
All cursors which were not explicitly closed will be clos...
The ROW_COUNT system variable can be used after each FETC...
Defaults for Stored Procedure Arguments
V. Horsun
Defaults can now be declared for stored procedure arguments.
The syntax is the same as a default value definition for ...
Arguments with default values must be last in the argumen...
For example, it is illegal to do something like this: s...
Substitution of default values occurs at run-time. If you...
However, it is still necessary to disconnect all client c...
Examples
CONNECT ... ;
SET TERM ^;
CREATE PROCEDURE P1 (X INTEGER = 123)
RETURNS (Y INTEGER)
AS
BEGIN
Y = X;
SUSPEND;
END ^
COMMIT ^
SET TERM ;^
SELECT * FROM P1;
Y
============
123
EXECUTE PROCEDURE P1;
Y
============
123
SET TERM ^;
CREATE PROCEDURE P2
RETURNS (Y INTEGER)
AS
BEGIN
FOR SELECT Y FROM P1 INTO :Y
DO SUSPEND;
END ^
COMMIT ^
SET TERM ;^
SELECT * FROM P2;
Y
============
123
SET TERM ^;
ALTER PROCEDURE P1 (X INTEGER = CURRENT_TRANSACTION)
RETURNS (Y INTEGER)
AS
BEGIN
Y = X;
SUSPEND;
END; ^
COMMIT ^
SET TERM ;^
SELECT * FROM P1;
Y
============
5875
SELECT * FROM P2;
Y
============
123
COMMIT;
CONNECT ... ;
SELECT * FROM P2;
Y
============
5880
Note
The source and BLR for the argument defaults are stored i...
LEAVE <label> Syntax Support
D. Yemanov
New LEAVE <label> syntax now allows PSQL loops to be mark...
Syntax pattern
<label_name>: <loop_statement>
...
LEAVE [<label_name>]
where <loop_statement> is one of: WHILE, FOR SELECT, FOR ...
Examples
1.
FOR
SELECT COALESCE(RDB$SYSTEM_FLAG, 0), RDB$RELATION_NAME
FROM RDB$RELATIONS
ORDER BY 1
INTO :RTYPE, :RNAME
DO
BEGIN
IF (RTYPE = 0) THEN
SUSPEND;
ELSE
LEAVE; -- exits current loop
END
2.
CNT = 100;
L1:
WHILE (CNT >= 0) DO
BEGIN
IF (CNT < 50) THEN
LEAVE L1; -- exists WHILE loop
CNT = CNT - l;
END
3.
STMT1 = 'SELECT RDB$RELATION_NAME FROM RDB$RELATIONS';
L1:
FOR
EXECUTE STATEMENT :STMT1 INTO :RNAME
DO
BEGIN
STMT2 = 'SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = ';
L2:
FOR
EXECUTE STATEMENT :STMT2 || :RNAME INTO :FNAME
DO
BEGIN
IF (RNAME = 'RDB$DATABASE') THEN
LEAVE L1; -- exits the outer loop
ELSE IF (RNAME = 'RDB$RELATIONS') THEN
LEAVE L2; -- exits the inner loop
ELSE
SUSPEND;
END
END
Note
Note that LEAVE without an explicit label means interrupt...
OLD Context Variables Now Read-only
D. Yemanov
The set of OLD context variables available in trigger mod...
Note
NEW context variables are now read-only in AFTER-triggers...
PSQL Stack Trace
V. Horsun
The API client can now extract a simple stack trace Error...
Additional items are appended to the status vector as fol...
isc_stack_trace, isc_arg_string, <string length>, <str...
isc_stack_trace is a new error code with value of 3355448...
Examples
Metadata creation
CREATE TABLE ERR (
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(16));
CREATE EXCEPTION EX '!';
SET TERM ^;
CREATE OR ALTER PROCEDURE ERR_1 AS
BEGIN
EXCEPTION EX 'ID = 3';
END ^
CREATE OR ALTER TRIGGER ERR_BI FOR ERR
BEFORE INSERT AS
BEGIN
IF (NEW.ID = 2)
THEN EXCEPTION EX 'ID = 2';
IF (NEW.ID = 3)
THEN EXECUTE PROCEDURE ERR_1;
IF (NEW.ID = 4)
THEN NEW.ID = 1 / 0;
END ^
CREATE OR ALTER PROCEDURE ERR_2 AS
BEGIN
INSERT INTO ERR VALUES (3, '333');
END ^
1. User exception from a trigger:
SQL" INSERT INTO ERR VALUES (2, '2');
Statement failed, SQLCODE = -836
exception 3
-ID = 2
-At trigger 'ERR_BI'
2. User exception from a procedure called by a trigger:
SQL" INSERT INTO ERR VALUES (3, '3');
Statement failed, SQLCODE = -836
exception 3
-ID = 3
-At procedure 'ERR_1'
At trigger 'ERR_BI'
3. Run-time exception occurring in trigger (division by z...
SQL" INSERT INTO ERR VALUES (4, '4');
Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string trunc...
-At trigger 'ERR_BI'
4. User exception from procedure:
SQL" EXECUTE PROCEDURE ERR_1;
Statement failed, SQLCODE = -836
exception 3
-ID = 3
-At procedure 'ERR_1'
5. User exception from a procedure with a deeper call stack:
SQL" EXECUTE PROCEDURE ERR_2;
Statement failed, SQLCODE = -836
exception 3
-ID = 3
-At procedure 'ERR_1'
At trigger 'ERR_BI'
At procedure 'ERR_2'
Call a UDF as a Void Function (Procedure)
N. Samofatov
In PSQL, supported UDFs, e.g. RDB$SET_CONTEXT, can be cal...
BEGIN
...
RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_VAR', '123');
...
END
最終行:
Stored Procedure Language (PSQL)
Table of Contents
PSQL Enhancements
PSQL Enhancements
Context Variable ROW_COUNT Enhanced
Explicit Cursors
Defaults for Stored Procedure Arguments
LEAVE <label> Syntax Support
OLD Context Variables Now Read-only
PSQL Stack Trace
Call a UDF as a Void Function (Procedure)
The following enhancements have been made to the PSQL lan...
Context Variable ROW_COUNT Enhanced
D. Yemanov
ROW_COUNT has been enhanced so that it can now return the...
For example, it can be used to check whether a singleton ...
..
BEGIN
SELECT COL FROM TAB INTO :VAR;
IF (ROW_COUNT = 0) THEN
EXCEPTION NO_DATA_FOUND;
END
..
See also its usage in the examples below for explicit PSQ...
Explicit Cursors
D. Yemanov
It is now possible to declare and use multiple cursors in...
Syntax pattern
DECLARE [VARIABLE] <cursor_name> CURSOR FOR ( <select...
OPEN <cursor_name>;
FETCH <cursor_name> INTO <var_name> [, <var_name> ...];
CLOSE <cursor_name>;
Examples
1.
DECLARE RNAME CHAR(31);
DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS );
BEGIN
OPEN C;
WHILE (1 = 1) DO
BEGIN
FETCH C INTO :RNAME;
IF (ROW_COUNT = 0) THEN
LEAVE;
SUSPEND;
END
CLOSE C;
END
2.
DECLARE RNAME CHAR(31);
DECLARE FNAME CHAR(31);
DECLARE C CURSOR FOR ( SELECT RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = :RNAME
ORDER BY RDB$FIELD_POSITION );
BEGIN
FOR
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
INTO :RNAME
DO
BEGIN
OPEN C;
FETCH C INTO :FNAME;
CLOSE C;
SUSPEND;
END
END
Note
Cursor declaration is allowed only in the declaration sec...
Cursor names are required to be unique in the given conte...
Positioned updates and deletes with cursors using the WHE...
Attempts to fetch from or close a FOR SELECT cursor are p...
Attempts to open a cursor that is already open, or to fet...
All cursors which were not explicitly closed will be clos...
The ROW_COUNT system variable can be used after each FETC...
Defaults for Stored Procedure Arguments
V. Horsun
Defaults can now be declared for stored procedure arguments.
The syntax is the same as a default value definition for ...
Arguments with default values must be last in the argumen...
For example, it is illegal to do something like this: s...
Substitution of default values occurs at run-time. If you...
However, it is still necessary to disconnect all client c...
Examples
CONNECT ... ;
SET TERM ^;
CREATE PROCEDURE P1 (X INTEGER = 123)
RETURNS (Y INTEGER)
AS
BEGIN
Y = X;
SUSPEND;
END ^
COMMIT ^
SET TERM ;^
SELECT * FROM P1;
Y
============
123
EXECUTE PROCEDURE P1;
Y
============
123
SET TERM ^;
CREATE PROCEDURE P2
RETURNS (Y INTEGER)
AS
BEGIN
FOR SELECT Y FROM P1 INTO :Y
DO SUSPEND;
END ^
COMMIT ^
SET TERM ;^
SELECT * FROM P2;
Y
============
123
SET TERM ^;
ALTER PROCEDURE P1 (X INTEGER = CURRENT_TRANSACTION)
RETURNS (Y INTEGER)
AS
BEGIN
Y = X;
SUSPEND;
END; ^
COMMIT ^
SET TERM ;^
SELECT * FROM P1;
Y
============
5875
SELECT * FROM P2;
Y
============
123
COMMIT;
CONNECT ... ;
SELECT * FROM P2;
Y
============
5880
Note
The source and BLR for the argument defaults are stored i...
LEAVE <label> Syntax Support
D. Yemanov
New LEAVE <label> syntax now allows PSQL loops to be mark...
Syntax pattern
<label_name>: <loop_statement>
...
LEAVE [<label_name>]
where <loop_statement> is one of: WHILE, FOR SELECT, FOR ...
Examples
1.
FOR
SELECT COALESCE(RDB$SYSTEM_FLAG, 0), RDB$RELATION_NAME
FROM RDB$RELATIONS
ORDER BY 1
INTO :RTYPE, :RNAME
DO
BEGIN
IF (RTYPE = 0) THEN
SUSPEND;
ELSE
LEAVE; -- exits current loop
END
2.
CNT = 100;
L1:
WHILE (CNT >= 0) DO
BEGIN
IF (CNT < 50) THEN
LEAVE L1; -- exists WHILE loop
CNT = CNT - l;
END
3.
STMT1 = 'SELECT RDB$RELATION_NAME FROM RDB$RELATIONS';
L1:
FOR
EXECUTE STATEMENT :STMT1 INTO :RNAME
DO
BEGIN
STMT2 = 'SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = ';
L2:
FOR
EXECUTE STATEMENT :STMT2 || :RNAME INTO :FNAME
DO
BEGIN
IF (RNAME = 'RDB$DATABASE') THEN
LEAVE L1; -- exits the outer loop
ELSE IF (RNAME = 'RDB$RELATIONS') THEN
LEAVE L2; -- exits the inner loop
ELSE
SUSPEND;
END
END
Note
Note that LEAVE without an explicit label means interrupt...
OLD Context Variables Now Read-only
D. Yemanov
The set of OLD context variables available in trigger mod...
Note
NEW context variables are now read-only in AFTER-triggers...
PSQL Stack Trace
V. Horsun
The API client can now extract a simple stack trace Error...
Additional items are appended to the status vector as fol...
isc_stack_trace, isc_arg_string, <string length>, <str...
isc_stack_trace is a new error code with value of 3355448...
Examples
Metadata creation
CREATE TABLE ERR (
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(16));
CREATE EXCEPTION EX '!';
SET TERM ^;
CREATE OR ALTER PROCEDURE ERR_1 AS
BEGIN
EXCEPTION EX 'ID = 3';
END ^
CREATE OR ALTER TRIGGER ERR_BI FOR ERR
BEFORE INSERT AS
BEGIN
IF (NEW.ID = 2)
THEN EXCEPTION EX 'ID = 2';
IF (NEW.ID = 3)
THEN EXECUTE PROCEDURE ERR_1;
IF (NEW.ID = 4)
THEN NEW.ID = 1 / 0;
END ^
CREATE OR ALTER PROCEDURE ERR_2 AS
BEGIN
INSERT INTO ERR VALUES (3, '333');
END ^
1. User exception from a trigger:
SQL" INSERT INTO ERR VALUES (2, '2');
Statement failed, SQLCODE = -836
exception 3
-ID = 2
-At trigger 'ERR_BI'
2. User exception from a procedure called by a trigger:
SQL" INSERT INTO ERR VALUES (3, '3');
Statement failed, SQLCODE = -836
exception 3
-ID = 3
-At procedure 'ERR_1'
At trigger 'ERR_BI'
3. Run-time exception occurring in trigger (division by z...
SQL" INSERT INTO ERR VALUES (4, '4');
Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string trunc...
-At trigger 'ERR_BI'
4. User exception from procedure:
SQL" EXECUTE PROCEDURE ERR_1;
Statement failed, SQLCODE = -836
exception 3
-ID = 3
-At procedure 'ERR_1'
5. User exception from a procedure with a deeper call stack:
SQL" EXECUTE PROCEDURE ERR_2;
Statement failed, SQLCODE = -836
exception 3
-ID = 3
-At procedure 'ERR_1'
At trigger 'ERR_BI'
At procedure 'ERR_2'
Call a UDF as a Void Function (Procedure)
N. Samofatov
In PSQL, supported UDFs, e.g. RDB$SET_CONTEXT, can be cal...
BEGIN
...
RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_VAR', '123');
...
END
ページ名:
新規
名前変更
ホーム
一覧
検索
最終更新
バックアップ
ヘルプ
最終更新のRSS