HANA SQL Script学习(3):Logic Container
3Logic Container
/*
2.Logic Container
逻辑代码块分类:1.Procedure;2.User-Defined Function
User-Defined Function分类:Scalar User-Defined Function and Table User-Defined Function.
3.1 Procedure
--2.1创建procedure
语法:
CREATE [OR REPLACE] PROCEDURE
[(
[LANGUAGE
[SQL SECURITY
[DEFAULT SCHEMA
[READS SQL DATA ]
[WITH ENCRYPTION]
[AUTOCOMMIT DDL ON|OFF]
AS
BEGIN
[SEQUENTIAL EXECUTION]
END
默认IN
|DECIMAL | SMALLDECIMAL | REAL | DOUBLE
| VARCHAR | NVARCHAR | ALPHANUM | VARBINARY | CLOB | NCLOB | BLOB | ST_GEOMETRY | ARRAY
LANGUAGE
指定语言,默认SQLSCRIPT
SQL SECURITY
指定安全模式,默认DEFINER.DEFINER:以procedure创建者权限执行;INVOKER:以procedure执行时的权限执行;
DEFAULT SCHEMA
默认当前schema
READS SQL DATA
只读,没有DDL(数据库表视图的增删改)和DML(数据记录的增删改)语句
AUTOCOMMIT DDL ON|OFF
默认:OFF,某些语句必须ON才能使用,例如:IMPORT
SEQUENTIAL EXECUTION
强制顺序执行,不会并行
| | | | BEGIN [ [ END ; [SEQUENTIAL EXECUTION ] | [AUTONOMOUS TRANSACTION] | [PARALLEL EXECUTION] | | CARDINALITY ( : | TRIM_ARRAY ( : | ARRAY ( | | | | | CE_LEFT_OUTER_JOIN ( | CE_RIGHT_OUTER_JOIN ( | CE_FULL_OUTER_JOIN ( | CE_JOIN ( | CE_UNION_ALL ( | CE_COLUMN_TABLE ( | CE_JOIN_VIEW ( | CE_CALC_VIEW ( | CE_OLAP_VIEW ( | CE_PROJECTION ( | CE_PROJECTION ( | CE_AGGREGATION ( | CE_CONVERSION ( | CE_VERTICAL_UNION ( | CE_COMM2R ( WITH ORDINALTIY:添加ordinal列 返回表名,列名 if 语句 [SEQUENTIAL EXECUTION][ [ [ [ END IF ; [SEQUENTIAL EXECUTION] [ [ [SEQUENTIAL EXECUTION] [ [ loop 语句 LOOP [SEQUENTIAL EXECUTION] [ [ END LOOP ; while 语句 WHILE [SEQUENTIAL EXECUTION] [ [ END WHILE ; for 语句 FOR [SEQUENTIAL EXECUTION] [ [ END FOR ; foreach 语句: FOR [SEQUENTIAL EXECUTION] [ [ END FOR ; 跳出循环语句: 跳过本次循环,继续下次: 定义signal,显式抛出异常 定义resignal,捕获异常 sql语句 | | | 查询语句 [ [ [ [{ [ [ Cursor操作 调用procedure 执行调用动态SQL 返回procedure值 /* 2.1.2 调用procedure CALL [WITH OVERVIEW] 当返回值为表数据集时,将数据插入对应OUT参数表;如果OUT ?,临时表保存返回数据 procedure内部调用procedure CALL ::= 创建procedure时,设置默认值 IN 当参数类型为table时,可以设置为empty (IN|OUT) */ /* 2.1.3删除procedure DROP PROCEDURE CASCADE:级联删除,当procedure被其他procedure调用,级联删除 RESTRICT:当procedure被其他procedure调用,删除时报错 */ /* 2.1.4修改procedure ALTER PROCEDURE [DEFAULT SCHEMA [READS SQL DATA] AS BEGIN [SEQUENTIAL EXECUTION] END ALTER PROCEDURE 通过生成更新执行计划触发重新编译 */ /* 2.1.5 procedure信息 Table:SYS.PROCEDURES 可用的procedure信息 表字段说明: SCHEMA_NAME:shema名称 PROCEDURE_NAME:procedure名称 PROCEDURE_OID:procedure ID SQL_SECURITY:SQL安全设置'DEFINER' / 'INVOKER' DEFAULT_SCHEMA_NAME:Schema name of the unqualified objects in the procedure INPUT_PARAMETER_COUNT:输入参数数量 OUTPUT_PARAMETER_COUNT:输出参数数量 INOUT_PARAMETER_COUNT:INOUT参数数量 RESULT_SET_COUNT:result set 计数 IS_UNICODE:是否包含 Unicode or not: 'TRUE'/ 'FALSE' DEFINITION:procedure定义文本 PROCEDURE_TYPE:procedure类型 READ_ONLY:whether the procedure is read-only or not: 'TRUE'/ 'FALSE' IS_VALID:Specifies whether the procedure is valid or not. This becomes 'FALSE' when its base objects are changed or dropped: 'TRUE'/ 'FALSE' IS_HEADER_ONLY:Specifies whether the procedure is header-only procedure or not: 'TRUE'/'FALSE' HAS_TRANSACTION_CONTROL_STATEMENTS:Specifies whether the procedure has transaction control statements or not:'TRUE'/'FALSE' OWNER_NAME:Name of the owner of the procedure TABLE:SYS.PROCEDURE_PARAMETERS procedure的参数信息 SCHEMA_NAME:Schema name of the stored procedure PROCEDURE_NAME:Name of the stored procedure PROCEDURE_OID:Object ID of the stored procedure PARAMETER_NAME:Parameter name DATA_TYPE_ID:Data type ID DATA_TYPE_NAME: Data type name LENGTH: Parameter length SCALE:Scale of the parameter POSITION :Ordinal position of the parameter TABLE_TYPE_SCHEMA:Schema name of table type if DATA_TYPE_NAME is TABLE_TYPE TABLE_TYPE_NAME: Name of table type if DATA_TYPE_NAME is TABLE_TYPE IS_INPLACE_TYPE VARCHER(5) Specifies whether the tabular parameter type is an inplace table type: 'TRUE'/'FALSE' PARAMETER_TYPE: Parameter mode: 'IN', 'OUT', 'INOUT' HAS_DEFAULT_VALUE:Specifies whether the parameter has a default value or not: 'TRUE', 'FALSE' IS_NULLABLE:Specifies whether the parameter accepts a null value: 'TRUE', 'FALSE' TABLE:SYS.OBJECT_DEPENDENCIES 对象之间的依赖关系,例如,引用特定表的视图 BASE_SCHEMA_NAME : Schema name of the base object BASE_OBJECT_NAME : Object name of the base object BASE_OBJECT_TYPE : Type of the base object DEPENDENT_SCHEMA_NAME : Schema name of the dependent object DEPENDENT_OBJECT_NAME : Object name of the dependent object DEPENDENT_OBJECT_TYPE : Type of the base dependent DEPENDENCY_TYPE :ype of dependency between base and dependent object. Possible values are: 0: NORMAL (default) 1: EXTERNAL_DIRECT (direct dependency between dependent object and base object) 2: EXTERNAL_INDIRECT (indirect dependency between dependent object und base object) 5: REFERENTIAL_DIRECT (foreign key dependency between tables) TABLE:PROCEDURE_PARAMETER_COLUMNS 显示procedure中定义的参数 SCHEMA_NAME:Schema name of the procedure PROCEDURE_NAME:Name of the procedure PROCEDURE_OID:Object ID of the procedure PARAMETER_NAME:Parameter name PARAMETER_POSITION : Ordinal position of the parameter COLUMN_NAME : Name of the column of the parameter type POSITION : Ordinal position of the column in a record DATA_TYPE_NAME :SQL data type name of the column LENGTH : Number of chars for char types, number of max digits for numeric types; number of chars for datetime types, number of bytes for LOB types SCALE :Numeric types: the maximum number of digits to the right of the decimal point; time, timestamp: the decimal digits are defined as the number of digits to the right of the decimal point in the second's component of the data IS_NULLABLE :Specifies whether the column is allowed to accept null value: 'TRUE'/'FALSE' */ /* 2.2 User-Defined Function 两种类型: Table User-Defined Functions Scalar UserDefined Functions 差异比较: 1.Functions Calling A table UDF can only be called in the FROM-clause of an SQL statement in the same parameter positions as table names. For example, SELECT * FROM myTableUDF(1) A Scalar UDF can be called in SQL statements in the same parameter positions as table column names. That takes place in the SELECT and WHERE clauses of SQL statements. For example, SELECT myScalarUDF(1) AS myColumn FROM DUMMY 2.Input Parameter (输入类型参数) table UDF: Primitive SQL type Table types Scalar UDF: Primitive SQL type Table types (with limitations) 3.Output table UDF: Must return a table whose type is defined in Scalar UDF: Must return scalar values specified in 4.Supported functionality table UDF: The function is tagged as read only by default. DDL and DML are not allowed and only other read-only functions can be called. Scalar UDF: The function is tagged as a read-only function by default. */ /* 2.2.1 创建Function 语法: CREATE [OR REPLACE] FUNCTION [LANGUAGE [SQL SECURITY [DEFAULT SCHEMA [DETERMINISTIC]] [WITH ENCRYPTION] AS BEGIN END | DECIMAL | SMALLDECIMAL | REAL | DOUBLE | VARCHAR | NVARCHAR | VARBINARY | CLOB | NCLOB | BLOB | ST_GEOMETRY| ARRAY Scalar UDF可以使用 SUDFs with table parameters can be used like any other SUDF with following exceptions: 1.Aliases (in FROM or WITH clauses) are not allowed. 2.Parameterized views, scripted calculation views or TUDFs as input are not supported. 3.ANY TABLE TYPE parameters are not supported. 4.SQLScript internal types, such as cursor variables or ROW types, are not supported. Table UDF可以使用 Scalar UDF返回值: Table UDF返回table: 设置语言 LANGUAGE 设置安全模式 SQL SECURITY 设置默认schema DEFAULT SCHEMA | | | | | | A table function must contain a return statement. 必须要有return语句 */ /* 2.2.2 修改function ALTER FUNCTION RETURNS [LANGUAGE [DEFAULT SCHEMA BEGIN END */ /* 2.2.3删除function DROP FUNCTION 如果没有指定 cascade:级连删除; restrict:删除function不能有依赖function,如果有报错; */ /* 2.2.4 function参数 Table user-defined functions Can have a list of input parameters and must return a table whose type is defined in Input parameters must be explicitly typed and can have any of the primitive SQL type or a table type. Scalar user-defined functions Can have a list of input parameters and must returns scalar values specified in Input parameters must be explicitly typed and can have any primitive SQL type. Using a table as an input is not allowed. */ /* 2.2.5 Consistent Scalar Function Result procedure中调用function,保持数据一致性。 */ /* 2.2.6 Function Metadata SYS.FUNCTIONS:系统可用function; SCHEMA_NAME:NVARCHAR(256) Schema name of the function FUNCTION_NAME:NVARCHAR(256) Name of the function FUNCTION_OID:BIGINT Object ID of the function SQL_SECURITY:VARCHAR(7) SQL Security setting of the function:'DEFINER'/'INVOKER' DEFAULT_SCHEMA_NAME:NVARCHAR(256) Schema name of the unqualified objects in the function INPUT_PARAMETER_COUNT:INTEGER Input type parameter count RETURN_VALUE_COUNT:INTEGER Return value type parameter count IS_UNICODE:VARCHAR(5) Specifies whether the function contains Unicode or not: 'TRUE', 'FALSE' DEFINITION:NCLOB Query string of the function FUNCTION_TYPE:VARCHAR(10) Type of the function FUNCTION_USAGE_TYPE:VARCHAR(9) Usage type of the function:'SCALAR', 'TABLE', 'AGGREGATE','WINDOW' IS_VALID:VARCHAR(5) Specifies whether the function is valid or not. This becomes 'FALSE' when its base objects are changed or dropped: 'TRUE', 'FALSE' IS_HEADER_ONLY:VARCHAR(5) Specifies whether the function is header-only function or not: 'TRUE'/'FALSE' OWNER_NAME:NVARCHAR(256) Name of the owner of the function SYS.FUNCTION_PARAMETERS:function的参数表 SCHEMA_NAME: NVARCHAR(256) Schema name of the function FUNCTION_NAME: NVARCHAR(256) Name of the function FUNCTION_OID: BIGINT Object ID of the function PARAMETER_NAME: NVARCHAR(256) Parameter name DATA_TYPE_ID: INTEGER Data type ID DATA_TYPE_NAME: VARCHAR(16) Data type name LENGTH: INTEGER Parameter length SCALE: INTEGER Scale of the parameter POSITION: INTEGER Ordinal position of the parameter TABLE_TYPE_SCHEMA: NVARCHAR(256) Schema name of table type if DATA_TYPE_NAME is TABLE_TYPE TABLE_TYPE_NAME: NVARCHAR(256) Name of table type if DATA_TYPE_NAME is TABLE_TYPE IS_INPLACE_TYPE: VARCHAR(5) Specifies whether the tabular parameter type is an inplace table type: 'TRUE'/'FALSE' PARAMETER_TYPE: VARCHAR(7) Parameter mode: IN, OUT, INOUT HAS_DEFAULT_VALUE: VARCHAR(5) Specifies whether the parameter has a default value or not: 'TRUE', 'FALSE' IS_NULLABLE: VARCHAR(5) Specifies whether the parameter accepts a null value: 'TRUE', 'FALSE' FUNCTION_PARAMETER_COLUMNS:正在使用的table type SCHEMA_NAME: NVARCHAR(256) Schema name of the function FUNCTION_NAME: NVARCHAR(256) Name of the function FUNCTION_OID: BIGINT Object ID of the function PARAMETER_NAME: NVARCHAR(256) Parameter name PARAMETER_POSITION: INTEGER Ordinal position of the parameter COLUMN_NAME: NVARCHAR(256) Name of the column in the table parameter POSITION: INTEGER Ordinal position of the column in the table parameter DATA_TYPE_NAME: VARCHAR(16) SQL data type name of the column LENGTH: INTEGER Number of chars for char types, number of max digits for numeric types; number of chars for datetime types, number of bytes for LOB types SCALE INTEGER: Numeric types: the maximum number of digits to the right of the decimal point; time, timestamp: the decimal digits are defined as the number of digits to the right of the decimal point in the second's component of the data IS_NULLABLE: VARCHAR(5) Specifies whether the column is allowed to accept null values: 'TRUE'/'FALSE' 设置function参数默认值 IN */ /* 2.2.7 SQL嵌入function */ /* 2.2.8 Deterministic Scalar Functions Deterministic Scalar Functions相同条件总是返回确定值 2.2.9 Procedure Result Cache PRC:过程结果缓存,以输入参数作为key,输出参数作为值; 注意:PRC is enabled only for deterministic procedures */ /* 2.3 User-Defined Libraries 定义一组variable、procedures,functions; 创建Library CREATE [OR REPLACE] LIBRARY [LANGUAGE SQLSCRIPT] [DEFAULT SCHEMA BEGIN [ END; 修改Library ALTER LIBRARY [LANGUAGE SQLSCRIPT] [DEFAULT SCHEMA BEGIN [ END; 删除Library DROP LIBRARY BEGIN [SEQUENTIAL EXECUTION] END BEGIN END 两种类型Library:built-in libraries:系统提供; user-defined libraries:用户定义; Library仅可以使用在Procedures,functions. Library Members: | | | CALL LIBRARIES 所有有效的Libraries SCHEMA_NAME: Schema name of the library LIBRARY_NAME: Name of the library LIBRARY_OID: Object ID of the library OWNER_NAME: Owner name of the library DEFAULT_SCHEMA_NAME: Schema of the unqualified objects in the library DEFINITION: Definition of the library LIBRARY_TYPE: Language type of the library IS_VALID: Specifies whether the library is valid or not. This becomes false when its base objects are changed or dropped. CREATE_TIME: Creation time LIBRARY_MEMBERS Library members信息 SCHEMA_NAME: Schema name of the library LIBRARY_NAME: Name of the library LIBRARY_OID: Object ID of the library MEMBER_NAME: Name of the library member MEMBER_TYPE: Type of the library member: 'VARIABLE', 'PROCEDURE', 'FUNCTION' ACCESS_MODE: Access mode of the library member: 'PUBLIC', 'PRIVATE' DEFINITION: Definition string of the library member UDL Member Procedure Call Without SQLScript Artifacts | CALL | CALL Library Member Functions and Variables Library member functions and variables can be used directly in SQL or expressions in SQLScript. 可以直接在SQL中使用 */ 当创建Function,Procedure时,使用OR REPLACE创建或者替换Function, Procedure。 示例: /* 2.4CREATE OR REPLACE 创建Function,Procedure时,使用OR REPLACE修改function,procedure. CREATE [OR REPLACE] FUNCTION [( RETURNS [LANGUAGE [SQL SECURITY [DEFAULT SCHEMA [DETERMINISTIC] [WITH ENCRYPTION] AS { BEGIN [WITH [ CACHE RETENTION [OF [FILTER CREATE [OR REPLACE] PROCEDURE [( [LANGUAGE [SQL SECURITY [DEFAULT SCHEMA [{ ROUTE TO REMOTE SOURCE | ROUTE TO VOLUME | ROUTE BY TABLE [DETERMINISTIC] [WITH ENCRYPTION] AS { BEGIN "测试时环境不支持,OR REPLACE */ 创建匿名代码块 示例: /* 2.5Anonymous Block 匿名代码块 语法: DO [( BEGIN [SEQUENTIAL EXECUTION] END WITH HINT (...) */ 示例: /* 2.6 SQLScript Encryption 定义时使用[WITH ENCRYPTION] [CREATE | ALTER] PROCEDURE [LANGUAGE [SQL SECURITY [DEFAULT SCHEMA [READS SQL DATA ] [ [WITH ENCRYPTION] AS BEGIN ... END; [CREATE | ALTER] FUNCTION [LANGUAGE [SQL SECURITY [DEFAULT SCHEMA [READS SQL DATA] [ [DETERMINISTIC] [WITH ENCRYPTION] AS BEGIN ... END; "已经创建的Procedure,function,设置加密 ALTER PROCEDURE ALTER FUNCTION *//*
实例1:创建procedure,显示信息
//创建table:message_box
*/
CREATE TABLE test_message_box (p_msg VARCHAR(200), tstamp TIMESTAMP);
CREATE PROCEDURE insert_msg_proc (p_msg VARCHAR(200))
LANGUAGE SQLSCRIPT AS
BEGIN
INSERT INTO test_message_box VALUES (:p_msg, CURRENT_TIMESTAMP);
END;/*
实例:2
//创建cursor,调用cursor。
*/
CREATE PROCEDURE test_proc1
LANGUAGE SQLSCRIPT AS
BEGIN
DECLARE v_id STRING;
DECLARE v_name STRING;
DECLARE v_price DECIMAL(10,2);
DECLARE v_msg VARCHAR(200);
DECLARE CURSOR c_cursor1 (p_price DECIMAL(10,2)) FOR
SELECT "Product_ID","Product_Name","Price" FROM "MyProducts"
WHERE "Price" > :p_price
ORDER BY "Product_ID" ASC;
OPEN c_cursor1(40);
FETCH c_cursor1 INTO v_id, v_name, v_price;
v_msg = :v_name || ' (id ' || :v_id || ') sell ' || :v_price || ' $.';
CALL insert_msg_proc(:v_msg);
CLOSE c_cursor1;
END;
CALL test_proc1();
/*创建test procedure 表*/
CREATE TABLE TEST_PROC( id INT,name VARCHAR(10),val DECIMAL(10,2) );
CREATE TABLE TEST_PROC_TEMP( id INT,name VARCHAR(10),val DECIMAL(10,2) );
INSERT INTO TEST_PROC VALUES( 1, 'test1', 4.32 );
INSERT INTO TEST_PROC VALUES( 2, 'test2', 1.43 );
INSERT INTO TEST_PROC VALUES( 3, 'test3', 20.63 );
/*创建table类型*/
CREATE TYPE TYPETABLE AS TABLE ( id INTEGER,name VARCHAR(10),val DECIMAL(10,2) );
/*创建procedure2*/
CREATE PROCEDURE test_proc2(IN id INTEGER,OUT resTab TYPETABLE)
LANGUAGE SQLSCRIPT AS
BEGIN
resTab = SELECT * FROM TEST_PROC WHERE id = :id;
END;
/*调用procedure*/
CALL test_proc2(1,?);
CALL test_proc2(2,?) WITH OVERVIEW;
CALL test_proc2(2+1,?);
/*使用物理表作为参数时,必须使用with overview,会将返回结果写入物理表中*/
CALL test_proc2(1,test_proc_temp) WITH OVERVIEW;
/*直接通过参数名调用procedure,这样可以忽略参数顺序*/
CALL test_proc2(resTab=>?,id=>2);
/*创建procedure3*/
CREATE PROCEDURE test_proc3(int1 INTEGER,intab TYPETABLE)
LANGUAGE SQLSCRIPT AS
BEGIN
SELECT int1 AS "变量" FROM DUMMY;
SELECT * FROM :intab;
END;
/*传入参数,表*/
CALL test_proc3(1,TEST_PROC);
CALL test_proc3(2,"TEST_PROC");
/*创建procedure4*/
CREATE PROCEDURE test_proc4( IN intab TABLE( id INTEGER) DEFAULT EMPTY)
LANGUAGE SQLSCRIPT AS
BEGIN
IF IS_EMPTY(:intab) THEN
SELECT 'input is empty' FROM DUMMY;
ELSE
SELECT 'input is not empty' FROM DUMMY;
END IF;
END;
CALL test_proc4();
DROP PROCEDURE test_proc1;
SELECT * FROM SYS.PROCEDURES WHERE PROCEDURE_NAME = 'TEST_PROC2';
SELECT * FROM SYS. PROCEDURE_PARAMETERS WHERE PROCEDURE_NAME = 'TEST_PROC2';
/*依赖procedure创建*/
CREATE PROCEDURE test_proc_dep(IN a INTEGER,IN b INTEGER)
LANGUAGE SQLSCRIPT AS
BEGIN
SELECT a+b AS "sum" FROM DUMMY;
END;
CREATE PROCEDURE test_proc_dep1()
LANGUAGE SQLSCRIPT AS
BEGIN
CALL test_proc_dep(10,100);
END;
SELECT * FROM SYS.OBJECT_DEPENDENCIES WHERE DEPENDENT_OBJECT_NAME = 'TEST_PROC_DEP';
SELECT * FROM PROCEDURE_PARAMETER_COLUMNS WHERE PARAMETER_NAME = 'INTAB';
3.2 User-Defined Function
--创建a table function
--返回table
CREATE FUNCTION scale (val INT)
RETURNS TABLE (a NVARCHAR(20), b INT)
LANGUAGE SQLSCRIPT AS
BEGIN
RETURN SELECT "Product_Name" as a, :val * "Quantity" AS b FROM "MyProducts";
END;
--调用table function
SELECT * FROM scale(10);
SELECT * FROM scale(10) AS a,scale(20) as b where a.a = b.a;
--创建a scalar function
--返回两个值的和,乘积
CREATE FUNCTION func_add_mul(x Double, y Double)
RETURNS result_add Double, result_mul Double
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
result_add = :x + :y;
result_mul = :x * :y;
END;
--调用scalar function
SELECT "Product_ID","Quantity","Price",func_add_mul("Quantity","Price").result_mul AS "Payment" FROM "MyProducts";
--可以在其他scalar function中调用function
CREATE FUNCTION func_multipy(x Double,y Double)
RETURNS output1 double
LANGUAGE SQLSCRIPT AS
BEGIN
output1 = func_add_mul(:x,:y).result_add * func_add_mul(:x,:y).result_mul;
END;
--调用function
SELECT func_multipy(2,2) as RESULT FROM dummy;
--获取function
CREATE FUNCTION GET_FUNCTIONS
RETURNS TABLE(schema_name NVARCHAR(256), name NVARCHAR(256))
AS
BEGIN
RETURN SELECT schema_name AS schema_name,function_name AS name FROM FUNCTIONS;
END;
--调用function
SELECT * FROM GET_FUNCTIONS();
--修改function
ALTER FUNCTION GET_FUNCTIONS
RETURNS TABLE(schema_name NVARCHAR(256), name NVARCHAR(256))
AS
BEGIN
RETURN SELECT schema_name AS schema_name,function_name AS name FROM FUNCTIONS WHERE IS_VALID = 'TRUE';
END;
DROP FUNCTION GET_FUNCTIONS;
CREATE TABLE test_consistent(C1 VARCHAR(20));
CREATE FUNCTION func_count RETURNS v_result INTEGER AS
BEGIN
SELECT COUNT(*) INTO v_result FROM test_consistent;
END;
CREATE PROCEDURE proc_insert_delete AS
BEGIN
INSERT INTO test_consistent VALUES ('test');
SELECT 'TRACE 1: COUNT AFTER INSERT', COUNT(*) FROM test_consistent;
SELECT 'TRACE 2: COUNT DURING FUNCTION CALL', func_count() FROM DUMMY;
DELETE FROM test_consistent;
SELECT 'TRACE 3: COUNT AFTER DELETE', COUNT(*) FROM test_consistent;
COMMIT;
END;
--调用procedure
CALL proc_insert_delete;
SELECT * FROM FUNCTIONS;
--
SELECT * FROM FUNCTION_PARAMETERS;
--
SELECT * FROM FUNCTION_PARAMETER_COLUMNS;
--设置默认值,默认输入参数,table类型
CREATE TABLE TEST_DEFAULT( s_type VARCHAR(10),s_name VARCHAR(20) );
INSERT INTO TEST_DEFAULT VALUES('type1','tom');
INSERT INTO TEST_DEFAULT VALUES('type2','jon');
CREATE FUNCTION func_default_value(
IN INTAB TABLE(s_type VARCHAR (10), s_name VARCHAR (20)) DEFAULT "TEST_DEFAULT",
IN delimiter VARCHAR(10) DEFAULT ':')
RETURNS TABLE(fullstring VARCHAR(40)) AS
BEGIN
RETURN SELECT s_type||:delimiter||s_name AS fullstring FROM :INTAB;
END;
--调用function
SELECT * FROM func_default_value();
--不使用默认值
SELECT * FROM func_default_value(delimiter=>',');
--SQL语句使用table function
CREATE FUNCTION TEMP_FUNC()
RETURNS TABLE (A INT, B INT, C INT) AS
BEGIN
DECLARE buffer TABLE (A INT, B INT, C INT);
:buffer.insert(( 1, 2, 3));
:buffer.insert(( 1, 2, 4));
:buffer.insert(( 2, 3, 2));
:buffer.insert(( 2, 3, 4));
:buffer.insert(( 2, 5, 7));
RETURN :buffer;
END;
SELECT A, B, SUM(C) FROM TEMP_FUNC()
GROUP BY A, B
ORDER BY A, B;
--SQL嵌入table function
SELECT A, B, SUM(C) FROM
SQL FUNCTION
RETURNS TABLE (A INT, B INT, C INT)
BEGIN
DECLARE buffer TABLE (A INT, B INT, C INT);
:buffer.i nsert((1, 2, 3));
:buffer.i nsert((1, 2, 4));
:buffer.i nsert((2, 3, 2));
:buffer.i nsert((2, 3, 4));
:buffer.i nsert((2, 5, 7));
RETURN :buffer;
END
GROUP BY A, B
ORDER BY A, B;
-- input parameter
SELECT a FROM
SQL FUNCTION (IN a INT => 1)
RETURNS TABLE(a INT)
BEGIN
RETURN SELECT :a AS a FROM dummy;
END;
-- nested SQL FUNCTION clause
SELECT a FROM SQL FUNCTION
RETURNS TABLE (a int)
BEGIN
RETURN SELECT * FROM SQL FUNCTION
RETURNS TABLE (a int)
BEGIN
RETURN SELECT 1 AS a FROM dummy;
END;
END;
--创建deterministic function
--deterministic??不支持
/*
CREATE FUNCTION func_deterministic(IN a INT)
RETURNS ret INT deterministic AS
BEGIN
ret = :a;
END;
SELECT func_deterministic("A") FROM "TEST";
*/
--创建deterministic procedure
--deterministic??不支持
/*
create procedure add (in a int, in b int, out c int) deterministic as
begin
c = :a + :b;
end
*/
3.3 User-Defined Libraries
/*--创建Library*/
--for 语句创建表插入数据
create table test_data_table(col1 int);
do begin
declare idx int = 0;
for idx in 1..200 do
insert into test_data_table values (:idx);
end for;
end;
--创建library
create LIBRARY lib_test1 as begin
public variable maxval constant int = 100;
public function bound_with_maxval(i int) returns x int as begin
x = case when :i > :maxval then :maxval else :i end;
end;
public procedure get_data(in size int, out result table(col1 int)) as begin
result = select top :size col1 from test_data_table;
end;
end;
--procedure使用library
create procedure proc_test1 (in inval int) as
begin
using lib_test1 as mylib;
declare var1 int = mylib:bound_with_maxval(:inval);
if :var1 > mylib:maxval then
select 'unexpected' from dummy;
else
declare tv table (col1 int);
call mylib:get_data(:var1, tv);
select count(*) from :tv;
end if;
end;
/*Libraries相关信息*/
--LIBRARIES信息
SELECT * FROM LIBRARIES;
--不存在?
SELECT * FROM LIBRARY_MEMBERS;
/*UDL Member Procedure Call Without SQLScript Artifacts*/
--调用library时,必须在匿名block或者procedure,function中,现在可以直接调用
create library mylib as begin
public procedure memberproc(in i int, out tv table(col1 nvarchar(10))) as begin
tv = select :i * 100 as col1 from dummy;
end;
end;
do (in iv int => 1, out otv table(col1 nvarchar(10)) => ?) begin using mylib as mylib;
call mylib:memberproc(:iv, otv);
end;
--直接调用library
create library mylib as begin
public procedure memberproc(in i int, out tv table(col1 nvarchar(10))) as begin
tv = select :i * 100 as col1 from dummy;
end;
end;
call mylib:memberproc(1, ?);
/*Library Member Functions and Variables*/
--创建表插入数据
create table test_r_tab (r decimal);
insert into test_r_tab values (50);
insert into test_r_tab values (100);
--创建library
create library lib_test2 as begin
public variable pi constant decimal = 3.14;
public function circumference(r decimal) returns a int as begin
a = 2 * :pi * :r;
end;
public function circumference_table(r_table table(r decimal)) returns table(c decimal) as begin
return select 2 * :pi * r as c from :r_table;
end;
end;
--直接SQL调用
select lib_test2:circumference(10) from dummy;
3.4 Create or Replace
CREATE PROCEDURE test_proc5 as
begin
select * from dummy;
end;
call test_proc5();
--报错:number of parameters cannot be changed with ALTER command
--可以更改中内容
ALTER PROCEDURE test_proc5() as
begin
select 1 from dummy;
end;
call test_proc5();
--replace procedure
-- new parameter
-- add default parameter value
-- change the number of parameter and name of parameter
--?报错,不支持CREATE OR REPLACE
CREATE OR REPLACE PROCEDURE test_proc5(IN a INT) as
begin
select a from dummy;
end;
call test_proc5(1);
3.5 Anonymous Block
DO (IN in_var NVARCHAR(20)=> 'P1',OUT outtab TABLE (A NVARCHAR(20),B NVARCHAR(20)) => ?)
BEGIN
T1 = SELECT "Product_ID" AS A,"Product_Name" AS B FROM "MyProducts" WHERE "Product_ID" = in_var;
outtab = SELECT * FROM :T1;
END
--block with hint
--报错不支持
DO BEGIN
DECLARE i INT;
FOR i in 1..5 DO
SELECT * FROM dummy;
END FOR;
END WITH HINT(dev_se_use_llvm)
--block创建table,插入数据
DO BEGIN
DECLARE I INTEGER;
CREATE TABLE MYTAB4(I INTEGER);
FOR I IN 1..10 DO
INSERT INTO MYTAB4 VALUES (:I);
END FOR;
END
3.6 SQLScript Encryption
--没有IS_ENCRYPTED列
SELECT * FROM PROCEDURES;
SELECT * FROM FUNCTIONS;
--?with encryption报错
create procedure test_encry_proc with encryption as
begin
select 1 as i from dummy;
end;