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值
/*
实例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;
/*
2.1.2 调用procedure
CALL
[WITH OVERVIEW]
当返回值为表数据集时，将数据插入对应OUT参数表；如果OUT ?，临时表保存返回数据
procedure内部调用procedure
CALL
::=
创建procedure时，设置默认值
IN
当参数类型为table时，可以设置为empty
(IN|OUT)
*/
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();
/*
2.1.3删除procedure
DROP PROCEDURE
CASCADE:级联删除，当procedure被其他procedure调用，级联删除
RESTRICT:当procedure被其他procedure调用，删除时报错
*/
DROP PROCEDURE test_proc1;
/*
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'
*/
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
/*
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语句
*/
--创建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;
/*
2.2.2 修改function
ALTER FUNCTION
RETURNS
[LANGUAGE
[DEFAULT SCHEMA
BEGIN
END
*/
--获取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;
/*
2.2.3删除function
DROP FUNCTION
如果没有指定
cascade：级连删除；
restrict:删除function不能有依赖function，如果有报错；
*/
DROP FUNCTION GET_FUNCTIONS;
/*
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,保持数据一致性。
*/
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;
/*
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
*/
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=>',');
/*
2.2.7 SQL嵌入function
*/
--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;
/*
2.2.8 Deterministic Scalar Functions
Deterministic Scalar Functions相同条件总是返回确定值
2.2.9 Procedure Result Cache
PRC:过程结果缓存，以输入参数作为key，输出参数作为值；
注意：PRC is enabled only for deterministic procedures
*/
--创建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
/*
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中使用
*/
/*--创建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
当创建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
*/
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
创建匿名代码块
示例：
/*
2.5Anonymous Block
匿名代码块
语法：
DO [(
BEGIN [SEQUENTIAL EXECUTION]
END WITH HINT (...)
内容和procedure中一致
*/
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
示例：
/*
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
*/
--没有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;