5. Imperative SQLScript Logic
5.1 Scalar Variables
/*
4. Imperative SQLScript Logic
4.1 Scalar Variables
DECLARE <sql_identifier> [{,<sql_identifier> }...] [CONSTANT] <type> | AUTO [NOT NULL] <proc_default>
<proc_default> ::= (DEFAULT | '=' ) <value>|<expression>
<value> !!= An element of the type specified by <type>
定义常量
*/
CREATE PROCEDURE test_proc13(OUT z INT) LANGUAGE SQLSCRIPT READS SQL DATA
AS BEGIN
DECLARE a int;
DECLARE b int = 0;
DECLARE c int DEFAULT 0;
t = select * from mytab4;
select count(*) into a from :t;
b = :a + 1;
z = :b + :c;
end;
5.2 SELECT INTO with DEFAULT Values
/*
4.2 SELECT INTO with DEFAULT Values
SELECT <select_list> INTO <var_name_list> [DEFAULT <scalar_expr_list>] <from_clause>
[<where_clause>] [<group_by_clause>]
[<having_clause>] [{<set_operator> <subquery>, ... }]
[<order_by_clause>] [<limit>] ;
[EXEC | EXECUTE IMMEDIATE] <string_expression>
[ INTO <var_name_list> [DEFAULT <scalar_expr_list>] ]
[ USING <scalar_expr_list> ]
<var_name_list> ::= <var_name>[{, <var_name>}...]
<var_name> ::= <identifier> | <identifier> '[' <index> ']'
SQL in Scalar Expressions
SQL子查询语句当查询返回单个值时，可以作为常量计算
*/
--指定default错误？？？
CREATE TABLE TEST_SEL_DEFAULT(A INT NOT NULL, B VARCHAR(10));
DO BEGIN
DECLARE A_COPY INT ARRAY;
DECLARE B_COPY VARCHAR(10) ARRAY;
SELECT A, B INTO A_COPY[1],B_COPY[1] DEFAULT -2+1, NULL FROM TEST_SEL_DEFAULT;
--(A_COPY[1],B_COPY[1]) = (-1,?), use default value
SELECT :A_COPY[1], :B_COPY[1] from dummy;
--使用EXEC执行sql语句指定default值
EXEC 'SELECT A FROM TEST_SEL_DEFAULT' INTO A_COPY[1] DEFAULT 2;
--(A_COPY[1]) = (2), exec into statement with default value
SELECT :A_COPY[1], :B_COPY[1] from dummy;
INSERT INTO TEST_SEL_DEFAULT VALUES (0, 'sample0');
SELECT A, B INTO A_COPY[1], B_COPY[1] DEFAULT 5, NULL FROM TEST_SEL_DEFAULT;
--(A_COPY[1],B_COPY[1]) = (0,'sample0'), executed as-is
SELECT :A_COPY[1], :B_COPY[1] from dummy;
END;
--指定default语句错误
DO BEGIN
DECLARE A_COPY INT;
DECLARE B_COPY VARCHAR(10);
CREATE ROW TABLE TEST_SEL_DEFAULT(A INT NOT NULL, B VARCHAR(10));
--不能指定default???
SELECT A, B INTO A_COPY, B_COPY DEFAULT -2+1, NULL FROM TEST_SEL_DEFAULT;
--(A_COPY,B_COPY) = (-1,?), use default value
EXEC 'SELECT A FROM TEST_SEL_DEFAULT' INTO A_COPY DEFAULT 2;
--(A_COPY) = (2), exec into statement with default value
INSERT INTO TEST_SEL_DEFAULT VALUES (0, 'sample0');
SELECT A, B INTO A_COPY, B_COPY DEFAULT 5, NULL FROM TEST_SEL_DEFAULT;
--(A_COPY,B_COPY) = (0,'sample0'), executed as-is
END;
--子查询计算
--feature not supported: Subquery is not allowed
DO BEGIN
DECLARE n INT;
n = ( select count(*) from mytab4 ) + 1;
END
-- fetch returns more than requested number of rows
do begin
declare n int;
select i into n from mytab4;
end;
5.3 Table Variables
/*
4.3 Table Variables
1.Table Variable Operators
通过下标方式获取表值
<table_variable>.<column_name>[<index>]
2.Inserting Data Records into Table Variables
:<table_variable>.INSERT((<value1,…, <valueN), [, <index> ])
限制插入的column
:<table_variable>.(<column1>,…, <column>).INSERT((<value1>,…, <valueN>), [ <index> ])
3.Inserting Table Variables into Other Table Variables
:<target_table_var>[.(<column_list>)].INSERT(:<source_table_var>[, <position>])
4.Updating Data Records in Table Variables
:<table_variable>.UPDATE((<value1>,…, <valueN), <index>)
<table_variable>[<index>] = (<value1>,…, <valueN>)
5.Deleting Data Records from Table Variables
:<table_variable>.DELETE([ <index> ])
--删除范围
:<table_variable>.DELETE(<start index>..<end index>)
6.UNNEST FUNCTION
<variable_name> = UNNEST(<unnest_param> [ {, <unnest_param>} ...] )
[WITH ORDINALITY]
[AS (<column_specifier>
[ {, <column_specifier>}... ]) ]
<unnest_param> ::= :table_variable
| :array_variable
| :array_function
<column_specifier> ::= '*'
| '(' <projection_aliasing_list> ')'
| <column_name>
<projection_aliasing_list> ::= <column_name> [AS <column_name>] [, <projection_aliasing_list>]
7.Emptiness Check for Tables and Table Variables
检查是否table参数为空
IS_EMPTY( <table_name> | <table_variable> )
8.Get Number of Records for Tables and Table Variables
获取表参数记录数
RECORD_COUNT( <table_name> | <table_variable> )
9.Search in Table Variables
position = <tabvar>.SEARCH((<column_list>), (<value_list>) [, <start_position>])
<column_list>,<value_list>大小需对应，
<start_position>默认1
10.SQL DML Statements on Table Variables
使用DML限制：
DML statements on table variables cannot be used in autonomous transactions and parallel execution blocks.
Neither input, nor output procedure or function parameters can be manipulated with DML statements.
11.Sorted Table Variables
定义sorted table variables
--定义search key，排序字段
CREATE TYPE <name> AS TABLE (<column list>) SQLSCRIPT SEARCH KEY(<key list>)
DECLARE <name> TABLE(<column list>) SEARCH KEY(<key list>)
DECLARE <name> <table type> SEARCH KEY(<key list>)
--定义procedure指定排序key
CREATE PROCEDURE <proc> (IN <param> TABLE(<column list>) SEARCH KEY(<key list>))
CREATE PROCEDURE <proc> (IN <param> <table type> SEARCH KEY(<key list>))
注意：使用<table type>中不能嵌套定义search key
*/
--1.Table Variable Operators
--创建table
create table intab( a integer,b varchar(20));
create table outtab( a integer,b varchar(20));
--这里select * form :intab,必须加：,不然对intab数据操作无效
--select * from intab,默认直接使用输入参数，之前对intab数据操作无效
create procedure test_proc13(
IN intab TABLE( a integer,b varchar(20)),
OUT outtab TABLE(a integer,b varchar(20))
)
AS BEGIN
intab.A[2] = 5;
outtab = select * from :intab;
END;
--intab插入数据
insert into intab values(1,'test1');
insert into intab values(2,'test2');
insert into intab values(3,'test3');
--调用procedure
do begin
declare outtab_temp table(a integer,b varchar(20));
intab_temp = select * from intab;
--call test_proc13(:intab_temp,:outtab_temp);
--call test_proc13(:intab_temp,?);
call test_proc13(:intab_temp,outtab=>outtab_temp);
select * from :outtab_temp;
end;
--2.Inserting Data Records into Table Variables
--创建procedure
create procedure test_proc14(
IN intab table(a integer,b varchar(20)),
OUT outtab table(a integer,b varchar(20))
)
as begin
declare i int;
declare max_count int = 10;
declare idx int;
declare str_temp varchar(20);
for i in 1..max_count DO
str_temp = :intab.b[i] || 'ins';
idx = max_count + i;
--这句insert有问题
--:intab.INSERT((:idx,:str_temp));
--限制column
--:intab.(a,b).insert((:idx,:str_temp));
end for;
--outtab = select * from :intab;
--直接插入目标table variables
:outtab.insert(:intab,1);
end;
/*
文档示例出错:OT1,incorrect syntax
CREATE TABLE SOURCE(K VARCHAR(20), PCT DECIMAL(5, 2), V DECIMAL(10, 2));
CREATE TABLE TARGET(K VARCHAR(20), V DECIMAL(10, 2));
INSERT INTO SOURCE VALUES ('A', 5.99, 734.42);
INSERT INTO SOURCE VALUES ('A', 50.83, 422.26);
INSERT INTO SOURCE VALUES ('B', 75.07, 362.53);
INSERT INTO SOURCE VALUES ('C', 87.21, 134.53);
INSERT INTO SOURCE VALUES ('C', 80.72, 2722.49);
CREATE PROCEDURE SPLIT(IN IT SOURCE, OUT OT1 TARGET, OUT OT2 TARGET) AS BEGIN
DECLARE IDX INT;
DECLARE MAXIDX INT = RECORD_COUNT(:IT);
FOR IDX IN 1..MAXIDX DO
DECLARE V1 DECIMAL(10, 2) = :IT.V[IDX] * :IT.PCT[IDX] / 100;
DECLARE V2 DECIMAL(10, 2) = :IT.V[IDX] - V1;
:OT1.INSERT((:IT.K[IDX], V1));
:OT2.INSERT((:IT.K[IDX], V2));
END FOR;
END;
CALL SPLIT(SOURCE, ?, ?);
*/
--4.Updating Data Records in Table Variables
--创建procedure
create procedure test_proc15(
IN intab table(a integer,b varchar(20)),
OUT outtab table(a integer,b varchar(20))
)
as begin
declare i int;
declare max_count int = 2;
for i in 1..max_count DO
str_temp = :intab.b[i] || 'ins';
idx = max_count + i;
--update语句有问题
:intab.(a,b).update((:idx,:str_temp),i);
end for;
outtab = select * from :intab;
end;
--5.Deleting Data Records from Table Variables
--创建procedure
create procedure test_proc16(
IN intab table(a integer,b varchar(20)),
OUT outtab table(a integer,b varchar(20))
)
as begin
--delete语句报错
:intab.delete(1);
outtab = select * from ：intab;
end;
--6.unnest function
--创建procedure
create procedure test_proc17(OUT rst outtab)
AS BEGIN
DECLARE arr_id INTEGER ARRAY= ARRAY(1,2);
DECLARE arr_name VARCHAR(20) ARRAY = ARRAY('name1', 'name2', 'name3');
rst = UNNEST(:arr_id, :arr_name) AS (a,b);
END;
--调用procedure
--结果显示
--1 name1
--2 name2
--? name3
call test_proc17(?);
--使用ordinary
--ordinality,添加排序列
create procedure test_proc18(OUT rst table(a integer,b varchar(20),"order_column" integer))
AS BEGIN
DECLARE arr_id INTEGER ARRAY= ARRAY(1,2,5,3,7);
DECLARE arr_name VARCHAR(20) ARRAY = ARRAY('name1', 'name2', 'name3','name4','name5');
rst = UNNEST(:arr_id, :arr_name) with ordinality AS (a,b,"order_column");
END;
--调用procedure
/*
执行结果:
A B order_column
1 name1 1
2 name2 2
5 name3 3
3 name4 4
7 name5 5
*/
call test_proc18(?);
--使用*代表所有列
--语法有错*
create procedure test_proc19(OUT rst table(a integer,b varchar(20)))
AS BEGIN
DECLARE arr_id INTEGER ARRAY= ARRAY(1,2,7);
DECLARE arr_name VARCHAR(20) ARRAY = ARRAY('name1', 'name2', 'name3');
rst = UNNEST(:arr_id, :arr_name) AS (*, (a as "id", b as "text"));
END;
--7.Emptiness Check for Tables and Table Variables
--is_empty()判断表参数是否为空
create procedure test_proc20(IN itab intab,OUT rst outtab)
AS BEGIN
if IS_EMPTY(:itab) THEN
RETURN;
ELSE
rst = select * from :itab;
END IF;
END;
--调用procedure
do begin
declare intab_temp table( a integer ,b varchar(20) );
declare outtab_temp table( a integer ,b varchar(20) );
intab_temp = select * from intab;
call test_proc20(:intab_temp,outtab_temp);
select * from :outtab_temp;
end
--8.Get Number of Records for Tables and Table Variables
--RECORD_COUNT()不存在？
create procedure test_proc21(IN itab intab)
AS BEGIN
declare i int;
IF RECORD_COUNT(:itab) > 0 THEN
select '大于0' from dummy;
END IF;
for i in 1 .. RECORD_COUNT(:itab) DO
select * from :itab where a = :i;
END FOR;
END;
--9.Search in Table Variables
--search有问题
create procedure test_proc22(IN itab intab)
AS BEGIN
declare i int;
i = :itab.search((a, b), (1, "test1"));
END;
--10.DML statements on Table Variables
--INSERT
--UPDATE
--DELETE
create procedure test_proc23(OUT otab outtab)
AS BEGIN
declare itab_temp table(a integer,b varchar(20));
--有问题？
INSERT INTO :itab_temp VALUES(4,"test4");
--UPDATE :itab_temp SET b = 'test udp' where a = 2;
--DELETE FROM :itab_temp where a = 1;
outtab = select * from :itab_temp;
END;
--创建表类型table types
create type tt_test1 as table( a int primary key,b int not null,c varchar(20));
--直接使用table types
do begin
declare t_test tt_test1;
--这句有问题
declare t_test2 table(a int,b int,c varchar(20) not null,primary key(a,b));
end
5.4 Auto Type Derivation
/*
4.4 Auto Type Derivation
自动类型派生
不显示指定声明数据类型，让SQL Script自动确定类型
可以用于scalar variables,tables和arrays
DECLARE <var> AUTO = <value>
必须指定默认值
scalar variables:
DECLARE <sql_identifier> [{,<sql_identifier> }...] [CONSTANT] AUTO [NOT NULL] <proc_default>
talbes:
DECLARE <sql_identifier> [{,<sql_identifier> }...] [CONSTANT] AUTO <proc_table_default>
使用限制：
Auto type cannot be used inside a trigger
Auto type cannot be used for row-type variables
Auto type cannot be used,
if the default value contains one of the following:
System variables
Scalar access of any table or auto-type table
*/
--示例：
--创建了AUTO表
create table auto (a bigint);
do begin
--可以定义AUTO表类型变量
declare tab1 "AUTO" = select 1 a from dummy;
--这里AUTO相等于关键词，自动类型派生？
-- return type mismatch: TAB2[ A:INT B:INT ] != expected result [ A:BIGINT ]
--这里关键词和定义表名有冲突
--declare tab2 AUTO = select 1 a,2 b from dummy;
end
do begin
--不支持auto
declare var1 auto = 1.0;
declare arr1 auto = array(1, 2);
declare tab1 auto = select 1 as x from dummy;
end
5.5 Global Session Variables
/*
4.5 Global Session Variables
全局会话变量
SET <key> = <value>;
<key> ::= <string_literal> | <string_variable>
<value> ::= <scalar_expression>
查询全局会话变量
SESSION_CONTEXT (<key>)
重置全局会话变量
UNSET <key>
注意：全局会话变量
max_session_variables参数配置最大数量全局会话变量，默认1024；
全局会话变量不能再只读function，procedure中使用；
*/
--procedure 设置全局会话变量
CREATE PROCEDURE CHANGE_SESSION_VAR (IN NEW_VALUE NVARCHAR(50)) AS
BEGIN
SET 'MY_VAR' = :new_value;
END
--function 获取全局会话变量
CREATE FUNCTION GET_SESSION_VALUE ()
RETURNS var NVARCHAR(50) AS
BEGIN
var = SESSION_CONTEXT('MY_VAR');
END;
do begin
call change_session_var('hello world');
select get_session_value() as a from dummy;
end
5.6 Variable Scope Nesting
/*
4.6 Variable Scope Nesting
变量作用域
本地变量可以定义在嵌套代码块中，只在本代码块中生效
*/
CREATE PROCEDURE test_proc_nested_block(OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
DECLARE a INT = 1;
BEGIN
DECLARE a INT = 2;
BEGIN
DECLARE a INT;
a = 3;
END;
val = a;
END;
END;
--out = 2
--因为val赋值等于第二个代码块的a
call test_proc_nested_block(?);
CREATE PROCEDURE test_proc_nested_block1(OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
DECLARE a INT = 1;
BEGIN
DECLARE a INT = 2;
BEGIN
a = 3;
END;
val = a;
END;
END;
--out = 3
--子作用域没有定义同名变量，就会去父作用域中找
call test_proc_nested_block1(?);
--if语句作用域
CREATE PROCEDURE test_proc_nested_block_if(IN inval INT, OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
DECLARE a INT = 1;
BEGIN
val = :inval;
END;
IF :a = 1 THEN
declare a INT = 2;
val = :a;
END IF;
END;
--out = 2
--if语句相当于一个代码块
call test_proc_nested_block_if(10,?);
--while loop语句
CREATE PROCEDURE test_proc_nested_block_while(OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
DECLARE v int = 2;
DECLARE a int = 10;
val = 0;
WHILE v > 0 DO
DECLARE a INT = 0;
a = :a + 1;
val = :val + :a;
v = :v - 1;
END WHILE;
END;
--out = 2
--while相等于一个代码块
call test_proc_nested_block_while(?);
--for循环语句
CREATE PROCEDURE test_proc_nested_block_for(OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
DECLARE a1 INT default 0;
--mytab4多笔记录字段 I 1~10
DECLARE CURSOR C FOR SELECT * FROM mytab4;
FOR R as C DO
--注意用于计算的变量必须初始化默认值
a1 = :a1 + R.I;
END FOR;
val = :a1;
END;
--out = 55
call test_proc_nested_block_for(?);
--loop循环语句
CREATE PROCEDURE test_proc_nested_block_loop(OUT val INT)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
DECLARE a1 int default 0;
DECLARE i1 int default 0;
DECLARE CURSOR C FOR SELECT * FROM mytab2;
LOOP
--cursor只赋值第一笔记录
DECLARE CURSOR C FOR SELECT * FROM mytab4;
OPEN C;
FETCH C into a1;
CLOSE C;
--循环结束条件，一定要有不然死循环
i1 = :i1 + 1;
IF :i1 = 5 THEN
BREAK;
END IF;
END LOOP;
val = :a1;
END;
--out = 1
--mytab4的第一笔记录，mytab2第一笔记录为2
call test_proc_nested_block_loop(?);
5.7 Control Structures
/*
4.7 Control Structures
控制结构
if,while,loop等控制语句
比较条件不支持between and
1.Conditionals
条件语句
语法格式：
IF <bool_expr1> THEN <then_stmts1>
[{ELSEIF <bool_expr2> THEN <then_stmts2>}...]
[ELSE <else_stmts3>]
END IF
<bool_expr1> ::= <condition>
<bool_expr2> ::= <condition>
<condition> ::= <comparison> | <null_check>
<comparison> ::= <comp_val> <comparator> <comp_val>
<null_check> ::= <comp_val> IS [NOT] NULL
所有本地变量的默认值都为NULL，所以定义变量用于计算时，需要初始化；
2.While Loop
while循环语句
语法格式：
WHILE <condition> DO
<proc_stmts>
END WHILE
3.For Loop
for循环语句
语法格式：
FOR <loop-var> IN [REVERSE] <start_value> .. <end_value> DO
<proc_stmts>
END FOR
4.跳转控制语句
BREAK
跳出循环
CONTINUE
跳过本次循环
5.Operators
操作符
IN NOTIN使用示例：
-- single expression on the left-hand side
IF :i IN (1, 2, 3, 6) THEN [...] END IF;
-- multiple expressions on the left-hand side
IF (:key, :val) NOT IN ((1, 'H2O'), (2, 'H2O'), (3, 'abc')) THEN [...] END IF;
-- subquery on the right-hand side
IF :i NOT IN (SELECT a FROM mytable) THEN [...] END IF;
-- subquery using table variable
IF (:a, :b, :c) IN (SELECT id, city, date from :lt where :id < :d) THEN [...] END IF;
-- subquery using table function
FOR i IN 1 .. CARDINALITY(:arr) DO
IF :arr[:i] IN (SELECT b FROM tfunc()) THEN [...] END IF;
END FOR;
注：测试时，目前不支持IN
EXISTS使用示例：
当查询语句返回非空子集时为true，否则false
IF EXISTS (SELECT * FROM mytab WHERE date = :d) THEN ... END IF;
IF NOT EXISTS (SELECT * FROM SYS.TABLES WHERE schema_name = :schema AND table_name = :table) THEN ... END IF;
WHILE :i < 100 AND EXISTS (SELECT * FROM mytab WHERE a = :i) DO i = :i + 1; ... END WHILE;
WHILE NOT EXISTS (SELECT * FROM mytab WHERE a > sfunc(:z).r2) DO ... END WHILE;
BETWEEN使用示例：
IF :i [NOT] BETWEEN 1 AND 2 THEN ... END IF;
WHILE :I [NOT] BETWEEN 1 AND 2 DO ... END WHILE;
注：测试时，目前不支持between
*/
--示例1:
--SUDF:scalar user defined function
CREATE FUNCTION test_func_sudf(a integer)
RETURNS res_a integer
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
--原样返回
res_a = :a;
END;
--创建procedure,if语句
CREATE PROCEDURE test_proc_control_if (in input1 INTEGER, out output1 table(a integer))
AS BEGIN
DECLARE i INTEGER DEFAULT :input1;
IF test_func_sudf(:i) = 1 THEN
output1 = SELECT a FROM mytab1;
ELSEIF test_func_sudf(:i) = 2 THEN
output1 = SELECT a FROM mytab2;
ELSE
output1 = SELECT i as a FROM mytab4;
END IF;
END;
--通过不同参数到不同if分支逻辑，查询不同表
call test_proc_control_if(1,?);
call test_proc_control_if(2,?);
call test_proc_control_if(3,?);
--示例2：
--创建procedure,while语句
--不支持between and语句
CREATE PROCEDURE test_proc_control_while (in input1 INTEGER, out output1 table(a integer))
AS BEGIN
DECLARE i INTEGER DEFAULT :input1;
--feature not supported: Range comparison is not supported
--WHILE test_func_sudf(:i) between 1 and 5 DO
WHILE test_func_sudf(:i) = 1 DO
output1 = SELECT a FROM mytab1;
i = :i - 1;
END WHILE;
END;
call test_proc_control_while(1,?);
--示例3
--创建procedure,for loop语句
CREATE PROCEDURE test_proc_control_for (in input1 INTEGER, out output1 INTEGER)
AS BEGIN
DECLARE i INTEGER;
DECLARE var1 INTEGER default 0;
FOR i IN 1..test_func_sudf(:input1) DO
var1 = :var1 + :i;
END FOR;
output1 = :var1;
END;
call test_proc_control_for(10,?);
--示例4
--创建procedure,in,exists等语句
CREATE PROCEDURE test_proc_control_operator (in input1 INTEGER,out output1 INTEGER,out output2 INTEGER)
AS BEGIN
DECLARE i INTEGER default :input1;
DECLARE var1 INTEGER default 0;
DECLARE var2 INTEGER default 0;
--报错：Subquery is not allowed
--IF i IN (select I from mytab4) THEN
-- var1 = :i;
--END IF;
--报错：feature not supported: IN comparison is not supported in SQLScript.
--IF I IN (1,2,3,4,5) THEN
-- var1 = :i;
--END IF;
--output1 = :var1;
--报错：feature not supported: Subquery is not allowed
IF EXISTS (select * from mytab4 where i = :i ) THEN
var2 = :i;
END IF;
output2 = :var2;
END;
5.8 Cursors
/*
4.8Cursors
光标，用来逐行遍历查询结果集
Cursors are used to fetch single rows from the result set returned by a query.
When a cursor is declared, it is bound to the query.
It is possible to parameterize the cursor query.
1.define cursor
定义游标
语法结构：
DECLARE CURSOR <cursor_name> [({<param_def>{,<param_def>} ...)] [<holdability> HOLD]
FOR <select_stmt>
<cursor_name> ::= <identifier>
<param_def> = <param_name> <param_type>
<param_type> ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT
| SMALLINT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL
| REAL | DOUBLE | VARCHAR | NVARCHAR | ALPHANUM
| VARBINARY | BLOB | CLOB | NCLOB
<holdability> := WITH | WITHOUT
光标逐行访问查询结果集，是不支持更新光标的
2.open cursor
OPEN <cursor_name>[(<argument_list>)]
准备光标，如果有参数，这里执行计算查询操作
3.获取将光标当前行值，然后光标到下一行
FETCH <cursor_name> INTO <variable_list>
4.close cursor
CLOSE <cursor_name>
关闭之前打开游标，释放关联资源
5.cursor attributes
cursor参数属性：
c_cursor1::ISCLOSED 返回true,c_cursor1 closed;
c_cursor1::NOTFOUND 返回true,没有有效行;
c_cursor1::ROWCOUNT 返回目前光标所在行数;
6.Looping Over Result Sets
循环使用光标访问数据
这种循环方法，可以隐式打开，关闭光标，比显式控制更加方便安全
语法结构：
FOR <row_var> AS <cursor_name>[(<argument_list>)] DO
<proc_stmts> | {<row_var>.<column>}
END FOR
7.Updatable Cursor
使用光标数据更新数据库表
语法结构：
UPDATE <target_table> [ [ AS ] <correlation_name> ]
SET <set_clause_list>
WHERE CURRENT OF <cursor_name>
DELETE FROM <target_table> [ [ AS ] <correlation_name> ]
WHERE CURRENT OF <cursor_name>
注意：
1.The cursor has to be declared with a SELECT statement having the FOR UPDATE clause
in order to prevent concurrent WRITE on tables (without FOR UPDATE, the cursor is not updatable)
2.The updatable cursor may be used only for UPDATE and DELETE operations.
3.Using an updatable cursor in a single query instead of SQLScript is prohibited.
4.Only persistent tables (both ROW and COLUMN tables) can be updated with an updatable cursor.
5.UPDATE or DELETE operations performed on a table by means of an updatable cursor are allowed only one time per row.
8.Cursor Holdability
语法结构：
DECLARE CURSOR cursor_name [(<parameter>)] [<holdability> HOLD] FOR ...
<holdability> := WITH | WITHOUT
在定义时指定Holdability，比配置时指定的优先级更高；
DECLARE CURSOR cursor_name WITH HOLD FOR …
Declares a cursor with holdability for both commit and rollback
DECLARE CURSOR cursor_name WITHOUT HOLD FOR …
Declares a cursor without holdability for both commit and rollback
DECLARE CURSOR cursor_name FOR …
Declares a cursor with holdability for commit and without holdability for rollback
*/
--示例1：
--创建游标
CREATE PROCEDURE test_proc_cursor
AS BEGIN
DECLARE i INT;
--WITH HOLD不支持
--DECLARE CURSOR mycur WITH HOLD FOR SELECT * FROM mytab1;
DECLARE CURSOR mycur FOR SELECT * FROM mytab1;
OPEN mycur;
FETCH mycur INTO i;
CLOSE mycur;
SELECT :i FROM DUMMY;
END;
call test_proc_cursor;
--示例2：
--获取多值
CREATE PROCEDURE test_proc_cursor1
AS BEGIN
DECLARE v_id string;
DECLARE v_name string;
DECLARE v_price DECIMAL(10,2);
DECLARE v_category string;
DECLARE v_quantity INTEGER;
DECLARE CURSOR mycur FOR SELECT * FROM "MyProducts";
OPEN mycur;
--默认第一行数据
--参数数量和表列数保持一致
--报错:wrong number of values in the INTO list of a FETCH statement
FETCH mycur INTO v_id,v_name,v_category,v_quantity,v_price;
CLOSE mycur;
SELECT :v_id,:v_name,:v_category,:v_quantity,:v_price FROM DUMMY;
END;
call test_proc_cursor1;
--示例3：
--循环获取查询结果集值
CREATE PROCEDURE test_proc_cursor2(IN input_category string)
AS BEGIN
DECLARE v_id string;
DECLARE v_name string;
DECLARE v_price DECIMAL(10,2);
DECLARE v_category string default :input_category;
DECLARE v_quantity INTEGER;
DECLARE CURSOR mycur(in_category string) FOR SELECT * FROM "MyProducts"
WHERE "Category" = :in_category;
FOR cur_row AS mycur(:v_category) DO
v_id = cur_row."Product_ID";
v_name = cur_row."Product_Name";
v_category = cur_row."Category";
v_quantity = cur_row."Quantity";
v_price = cur_row."Price";
SELECT :v_id,:v_name,:v_category,:v_quantity,:v_price FROM DUMMY;
END FOR;
END;
call test_proc_cursor2('Clothes');
--示例4：通过cursor数据更新数据库表
--目前不支持
CREATE PROCEDURE test_proc_cursor3
AS BEGIN
DECLARE CURSOR mycur FOR SELECT * FROM "MyProducts";
FOR cur_row AS mycur DO
IF cur_row."Product_Name" = 'Coats' THEN
UPDATE "MyProducts" SET "Price" = "Price" + 100 WHERE CURRENT OF mycur;
END IF;
IF cur_row."Product_Name" = 'Purse' THEN
DELETE "MyProducts" WHERE CURRENT OF mycur;
END IF;
END FOR;
END;
call test_proc_cursor3;
--示例5：
--holdability
CREATE PROCEDURE test_proc_cursor4
AS BEGIN
DECLARE i INT;
--WITH HOLD不支持
DECLARE CURSOR mycur WITH HOLD FOR SELECT * FROM mytab1;
OPEN mycur;
ROLLBACK;
FETCH mycur INTO i;
CLOSE mycur;
SELECT :i as i FROM DUMMY;
END;
5.9 Autonomous Transaction
/*
4.9 Autonomous Transaction
自主事务，独立于主进程事务，保留所做更改，隐式提交
语法结构：
<proc_bloc> :: =
BEGIN AUTONOMOUS TRANSACTION
[<proc_decl_list>]
[<proc_handler_list>]
[<proc_stmt_list>]
END;
*/
--autonomous transaction
CREATE PROCEDURE test_proc_auto_transaction
AS BEGIN
DECLARE a INTEGER default 0;
DECLARE b INTEGER default 1;
BEGIN AUTONOMOUS TRANSACTION
INSERT INTO MYTAB1 (A) VALUES(10);
END;
--出现exception
b = :b / :a;
END;
--报错：division by zero undefined: cannot be divided by zero
--但是数据已经插入进去了
call test_proc_auto_transaction;
5.10 Transactional Statements
/*
4.10 Transactional Statements
1.COMMIT OR ROLLBACK
提交事务，回滚事务
注意：
1.If you used DSQL in the past to execute these commands (for example, EXEC ‘COMMIT’, EXEC ’ROLLBACK’),
SAP recommends that you replace all occurrences with the native commands COMMIT/ROLLBACK because they are more secure.
2.The COMMIT/ROLLBACK commands are not supported in Scalar UDF or in Table UDF.
一旦有Rollback语句，之前所有没有Commit的操作都会回滚！
*/
--commit or rollback
CREATE PROCEDURE test_proc_commit_rollback
AS BEGIN
--插入操作提交了
INSERT INTO MYTAB1 (A) VALUES(11);
COMMIT;
--插入操作回滚
INSERT INTO MYTAB1 (A) VALUES(12);
ROLLBACK;
END;
call test_proc_commit_rollback;
5.11 SAVEPOINT
/*
4.11 SAVEPOINT
SQLScript支持savepoint
语法规则：
the definition of a SAVEPOINT: SAVEPOINT <name>
the rollback to a specific SAVEPOINT: ROLLBACK TO SAVEPOINT <name>
the releasing of a SAVEPOINT: RELEASE SAVEPOINT <name>
*/
--savepoint
--目前不支持
CREATE PROCEDURE test_proc_savepoint
AS BEGIN
--插入操作提交了
INSERT INTO MYTAB1 (A) VALUES(15);
SAVEPOINT save1;
--插入操作回滚
INSERT INTO MYTAB1 (A) VALUES(16);
ROLLBACK TO SAVEPOINT save1;
--释放save1
RELEASE SAVEPOINT save1;
SELECT * FROM mytab1;
END;
call test_proc_savepoint;
5.12 Dynamic SQL
/*
4.12 Dynamic SQL
动态SQL语句，能够在执行期间构造SQL语句
缺点：
1.Opportunities for optimizations are limited.
2.The statement is potentially recompiled every time the statement is executed.
3.You cannot use SQLScript variables in the SQL statement.
4.You cannot bind the result of a dynamic SQL statement to an SQLScript variable.
5.You must be very careful to avoid SQL injection bugs that might harm the integrity or security of the database.
EXEC 执行SQL,不返回select结果集
EXECUTE IMMEDIATE 执行SQL，返回select结果集
语法规则：
EXEC '<sql-statement>'
[INTO <var_name_list> [DEFAULT <scalar_expr_list>]]
[USING <expression_list>]
[READS SQL DATA]
语法规则：
EXECUTE IMMEDIATE '<sql-statement>'
[INTO <var_name_list> [DEFAULT <scalar_expr_list>]]
[USING <expression_list>]
[READS SQL DATA]
APPLY_FILTER:
语法规则：
<variable_name> = APPLY_FILTER(<table_or_table_variable>, <filter_variable_name>);
*/
--示例1：
--dynamic SQL
CREATE PROCEDURE test_proc_exec(IN i_tabname nvarchar(20))
AS BEGIN
DECLARE A INTEGER;
DECLARE B INTEGER DEFAULT 10;
--报错：incorrect syntax near "INTO"
--EXEC 'SELECT MAX(A) FROM MYTAB1' INTO A;
--SELECT :A FROM DUMMY;
--指定默认值,
--EXEC 'SELECT MAX(A) FROM MYTAB1' INTO A DEFAULT 2;
--USING,INTO都不支持
--EXECUTE IMMEDIATE 'SELECT MAX(A) FROM MYTAB1 WHERE A > ?' INTO A USING :B;
--没有执行结果
EXEC 'SELECT * FROM MYTAB1';
EXEC 'SELECT * FROM ' || :i_tabname;
--使用立即执行，可以看到查询结果
EXECUTE IMMEDIATE 'SELECT * FROM MYTAB2';
END;
call test_proc_exec('MYTAB1');
--示例2：
--filter
/*
表：PRODUCTSALES内容
Tee Shirt;Plain;21
Tee Shirt;Lettered;22
Tee Shirt;Team logo;30
Hoodie;Plain;60
Hoodie;Lettered;65
Hoodie;Team logo;80
Ballcap;Plain;8
Ballcap;Lettered;40
Ballcap;Team logo;27
*/
CREATE PROCEDURE test_proc_filter (
IN filter NVARCHAR(100),
OUT products table(prodname NVARCHAR(50),description NVARCHAR(20)))
AS BEGIN
temp_products = APPLY_FILTER(productsales,:filter);
products = SELECT prodname,description FROM :temp_products;
END;
call test_proc_filter('PRODNAME LIKE ''Tee%''',?);
5.13 Exception Handling
/*
4.13 Exception Handling
异常处理
1.DECLARE EXIT HANDLER
DECLARE EXIT HANDLER FOR <proc_condition_value> {,<proc_condition_value>}...] <proc_stmt>
<proc_condition_value> ::= SQLEXCEPTION
| SQL_ERROR_CODE <error_code>
| <condition_name>
系统View：M_ERROR_CODES
所有异常代码及描述
2.DECLARE CONTINUE HANDLER
支持处理错误后，继续执行程序
DECLARE CONTINUE HANDLER FOR <proc_condition_value> {,<proc_condition_value>}...] <proc_stmt>
<proc_condition_value> ::= SQLEXCEPTION
| SQL_ERROR_CODE <error_code>
| <condition_name>
3.DECLARE CONDITION
自定义error code
注：Please note the user-defined error codes must be within the range of 10000 to 19999.
语法规则：
DECLARE <condition name> CONDITION [ FOR SQL_ERROR_CODE <error_code> ];
4. SIGNAL and RESIGNAL
显示触发抛出异常
SIGNAL (<user_defined_condition> | SQL_ERROR_CODE <int_const> )[SET MESSAGE_TEXT = '<message_string>']
修改显示信息
RESIGNAL [<user_defined_condition > | SQL_ERROR_CODE <int_const> ] [SET MESSAGE_TEXT = '<message_string>']
*/
--异常代码及描述
SELECT * FROM M_ERROR_CODES;
--示例1：
--exception handler
CREATE PROCEDURE test_proc_exception_handler
AS BEGIN
DECLARE A INTEGER default 0;
DECLARE B INTEGER default 1;
--捕捉所有SQL EXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION
--只捕捉目标EXCEPTIOn
--DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 304
--两个系统参数，获取异常代码，描述
SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
B = :B / :A;
END;
--执行procedure，不会异常报错
--结果显示：
--:1 :2
--304 division by zero undefined: cannot be divided by zero
call test_proc_exception_handler;
--示例2：
--不支持continue handler
CREATE PROCEDURE test_proc_exception_handler1
AS BEGIN
DECLARE A INTEGER default 0;
DECLARE B INTEGER default 1;
--捕捉所有SQL EXCEPTION
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
--两个系统参数，获取异常代码，描述
SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
END;
B = :B / :A;
--捕获处理异常后，可以继续执行逻辑
SELECT * FROM MYTAB1;
END;
--执行procedure，不会异常报错
call test_proc_exception_handler1;
--示例3：
--自定义异常
DO BEGIN
--定义异常condition
DECLARE invalid_input CONDITION FOR SQL_ERROR_CODE 11111;
END
DO BEGIN
DECLARE a INTEGER default 2;
--异常捕捉
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 11111
SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
IF :a = 2 THEN
--抛出异常
SIGNAL SQL_ERROR_CODE 11111 SET MESSAGE_TEXT = 'invalid input param';
--SIGNAL invalid_input SET MESSAGE_TEXT = 'invalid input param';
END IF;
END
--使用resignal，重设message
DO BEGIN
DECLARE a INTEGER default 2;
--异常捕捉
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 11111
RESIGNAL SET MESSAGE_TEXT = 'error code:' || ::SQL_ERROR_CODE || ' error msg:invalid input param';
IF :a = 2 THEN
--抛出异常
--error exception: error code:11111 error msg:invalid input param
SIGNAL SQL_ERROR_CODE 11111 SET MESSAGE_TEXT = 'invalid input param';
END IF;
END
5.14 Array Variables
/*
4.14 Array Variables
单个数据类型索引数组
1.定义array类型
语法结构：
DECLARE <variable_name> <sql_type> ARRAY;
<sql_type> ::=
DATE | TIME| TIMESTAMP | SECONDDATE | TINYINT
| SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL
| REAL | DOUBLE | VARCHAR | NVARCHAR | VARBINARY | CLOB | NCLOB |BLOB
不支持对ARRAY指定静态大小，最大数组2^31次方
使用Array构造方法定义Array
DECLARE <variable_name> [{, <variable_name>}...] <sql_type> ARRAY
= ARRAY ( <value_expression> [{, <value_expression>}...] );
2.设置array值
<array_variable>’[’ <array_index> ’]’ = <value_expression>
3.获取array值
必须使用:访问到
:<array_variable_name> ‘[‘ <array_index>’]’;
4.ARRAY_AGG function
<array_variable_name> = ARRAY_AGG ( :<table_variable_name>.<column_name>
[ORDER BY { <expression> [ {, <expression>}… ]
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ] , ... } ] )
5.TRIM_ARRAY Function
移除array后面<trim_quantity>记录
TRIM_ARRAY”(“:<array_variable>, <trim_quantity>”)”
<array_variable> ::= <identifier>
<trim_quantity> ::= <unsigned_integer>
6.CARDINALITY Function
返回当前array的size,
CARDINALITY(:<array_variable>)
7. Concatenate Two Arrays
:<array_variable_left> “||” :<array_variable_right>
| CONCAT'(':<array_variable_left> , :<array_variable_right> ')'
两个array连接
8.Array Parameters for Procedures and Functions
在procedure，function中使用数组类型参数
1.Array input/output/inout parameter for procedures
2.Array input parameter for SUDF/TUDF
3.Array return type for SUDF
4.Array parameter for library procedures/functions
5.Array input parameter for anonymous block/embedded SQL function
6.Array variables in DML/queries.
语法结构：
CREATE [OR REPLACE] PROCEDURE <proc_name>
[(<parameter_clause>)]
[LANGUAGE <lang>]
[SQL SECURITY <mode>]
[DEFAULT SCHEMA <default_schema_name>]
[READS SQL DATA ]
[WITH ENCRYPTION]
AS BEGIN
[SEQUENTIAL EXECUTION]
<procedure_body>
END
<parameter_clause> ::= <parameter> [{,<parameter>}...]
<parameter> ::= [IN | OUT | INOUT] <param_name> <param_type>
<param_type> ::= <sql_type> [ARRAY] | <table_type> | <table_type_definition>
语法结构：
CREATE FUNCTION <func_name>
[(<parameter_clause>)]
RETURNS <return_type>
[LANGUAGE <lang>]
[SQL SECURITY <mode>]
[DEFAULT SCHEMA <default_schema_name> [DETERMINISTIC]]
[WITH ENCRYPTION]
AS BEGIN
<function_body>
END
<parameter_clause> ::= <parameter> [{,<parameter>}...]
<parameter> ::= [IN] <param_name> <param_type>
<param_type> ::= <sql_type> [ARRAY] | <table_type> | <table_type_definition>
<return_type> ::= <return_parameter_list>
<return_parameter_list> ::= <return_parameter>[{, <return_parameter>}...]
<return_parameter> ::= <parameter_name> <sql_type> [ARRAY]
使用array参数限制：
1.LOB type array parameter is not supported.
2.DEFAULT VALUE for an array parameter is not supported.
3.Using an array parameter in the USING clause of Dynamic SQL is not supported.
*/
--创建function,两个数相加
CREATE FUNCTION test_funct_sudf_add(IN in_val1 INTEGER,IN in_val2 INTEGER)
RETURNS res_val INTEGER
AS BEGIN
res_val = :inval1 + :inval2;
END;
DO BEGIN
--定义array值
DECLARE array1 INTEGER ARRAY;
DECLARE array2 INTEGER ARRAY = ARRAY(1,2,3);
DECLARE tab table(I INTEGER);
DECLARE numb INTEGER;
--设置array值
array1[1] = 10;
array1[test_func_sudf_add(1,2)] = 20;
--获取array值
select :array1[3] FROM dummy;
--array_agg
tab = SELECT * FROM MYTAB4;
--array1 = ARRAY_AGG(:tab.I);
--使用ORDER BY,
array1 = ARRAY_AGG(:tab.I ORDER BY I DESC);
--trim array
--移除后面5个array记录
array1 = TRIM_ARRAY(:array1,5);
--获取当前array size
numb = CARDINALITY(:array1);
--连接两个array
array1 = :array1 || :array2;
--array1 = CONCAT(:array1,:array2);
select :numb from dummy;
--UNNEST function，将array转换为table
tab = UNNEST(:array1) AS (I);
select * from :tab;
END;
5.15 SQL Injection Prevention Functions
/*
4.15 SQL Injection Prevention Functions
SQL注入检查function
ESCAPE_SINGLE_QUOTES(string_var)
ESCAPE_DOUBLE_QUOTES(string_var)
IS_SQL_INJECTION_SAFE(string_var[, num_tokens])
检查字符串中单词数是否符合num_tokens,以空格分隔算一个单词
num_tokens:允许在string_var中最大tokens目标字符数，默认为1；
没有SQL注入返回1，否则返回0；
*/
DO BEGIN
SELECT ESCAPE_SINGLE_QUOTES('Str''ing') FROM DUMMY;
SELECT ESCAPE_DOUBLE_QUOTES('TAB"LE') FROM DUMMY;
END;
DO BEGIN
--return 0
SELECT IS_SQL_INJECTION_SAFE('select * from mytab1 where a = 1 or 1 = 1') FROM DUMMY;
--return 0
SELECT IS_SQL_INJECTION_SAFE('select * from mytab1 where a = 1') FROM DUMMY;
--return 1
SELECT IS_SQL_INJECTION_SAFE('able book',2) FROM DUMMY;
--return 1
SELECT IS_SQL_INJECTION_SAFE('able',2) FROM DUMMY;
END;
5.16 Explicit Parallel Execution
/*
4.16 Explicit Parallel Execution
显示并行执行
到目前为止，隐式并行化已经应用于表变量赋值以及相互独立的只读procedure调用。
DML语句和读写procedure调用必须按顺序执行。从现在起，可以使用并行执行块来并行执行独立的DML语句和读写procedure调用
语法规则：
BEGIN PARALLEL EXECUTION
<stmt>
END
DML语句并行执行只支持column类型table
注：只有以下操作可以在读写procedure，parallel execution块中使用
1.DML
2.Imperative logic
3.Autonomous transaction
4.Implicit SELECT and SELECT INTO scalar variable
*/
--创建column类型table
CREATE COLUMN TABLE TEST_COL_TAB1(A INT);
CREATE COLUMN TABLE TEST_COL_TAB2(A INT);
--插入数据
INSERT INTO TEST_COL_TAB1(A) VALUES(11);
INSERT INTO TEST_COL_TAB1(A) VALUES(10);
INSERT INTO TEST_COL_TAB2(A) VALUES(11);
INSERT INTO TEST_COL_TAB2(A) VALUES(12);
CREATE PROCEDURE test_proc_parallel_exec
AS BEGIN
BEGIN PARALLEL EXECUTION
--报错：Concurrently two or more write operations to the same object are not allowed
--对同一表不能有两个操作
--INSERT INTO TEST_COL_TAB2(A) VALUES(11);
--INSERT INTO TEST_COL_TAB2(A) VALUES(12);
--UPDATE TEST_COL_TAB2 SET A = A + 1;
--UPDATE TEST_COL_TAB2 SET A = A + 2;
UPDATE TEST_COL_TAB1 SET A = A + 1;
UPDATE TEST_COL_TAB2 SET A = A + 1;
END;
END;
5.17 Recursive SQLScript Logic
/*
4.17 Recursive SQLScript Logic
递归逻辑
SQLScript支持function,procedure递归调用本身
注：最大递归层数32
*/
--示例1：
--创建递归function
CREATE FUNCTION test_func_recursive(i INTEGER)
RETURNS j INTEGER
AS BEGIN
IF :i < 1 THEN
j = 1;
ELSE
i = :i - 1;
--不支持报错：invalid name of function or procedure: TEST_FUNC_RECURSIVE
j = :i + test_func_recursive(:i);
END IF;
END;
SELECT test_func_recursive(2) FROM DUMMY;
--示例2：
--使用function header 定义递归
--定义function header
CREATE FUNCTION test_func_recursive(i INTEGER) RETURNS j INTEGER AS HEADER ONLY;
--使用alter，实现function
--报错：recursive procedure/function not allowed
ALTER FUNCTION test_func_recursive(i INTEGER)
RETURNS j INTEGER
AS BEGIN
IF :i < 1 THEN
j = 1;
ELSE
i = :i - 1;
j = :i + test_func_recursive(:i);
END IF;
END;
SELECT test_func_recursive(2) FROM DUMMY;
2709
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
