华为云用户手册

集合MULTISET函数 MULTISET UNION [ALL | DISTINCT] 参数：nesttable类型 返回值：nesttable类型 功能描述：两个集合变量的并集，ALL不去除重复元素，DISTINCT去除重复元素。 示例： gaussdb=# declaregaussdb-# type nest is table of int;gaussdb-# a nest := nest(1,2);gaussdb-# b nest := nest(2,3);gaussdb-# begingaussdb$# a := a MULTISET UNION ALL b;gaussdb$# raise info '%', a;gaussdb$# end;gaussdb$# /INFO: {1,2,2,3}ANONYMOUS BLOCK EXECUTE gaussdb=# declaregaussdb-# type nest is table of int;gaussdb-# a nest := nest(1,2);gaussdb-# b nest := nest(2,3);gaussdb-# begingaussdb$# a := a MULTISET UNION DISTINCT b;gaussdb$# raise info '%', a;gaussdb$# end;gaussdb$# /INFO: {1,2,3}ANONYMOUS BLOCK EXECUTE MULTISET EXCEPT [ALL | DISTINCT] 参数：nesttable类型 返回值：nesttable类型 功能描述：两个集合变量的差集。如A MULTISET EXCEPT B：ALL表示去除A中与B重复的元素；DISTINCT表示先对A进行去重操作，然后去除与B中有重复的元素。 示例： gaussdb=# declaregaussdb-# type nest is table of int;gaussdb-# a nest := nest(1,2,2);gaussdbs-# b nest := nest(2,3);gaussdb-# begingaussdb$# a := a MULTISET EXCEPT ALL b;gaussdb$# raise info '%', a;gaussdb$# end;gaussdb$# /INFO: {1,2}ANONYMOUS BLOCK EXECUTE gaussdb=# declaregaussdb-# type nest is table of int;gaussdb-# a nest := nest(1,2,2);gaussdb-# b nest := nest(2,3);gaussdb-# begingaussdb$# a := a MULTISET EXCEPT DISTINCT b;gaussdb$# raise info '%', a;gaussdb$# end;gaussdb$# /INFO: {1}ANONYMOUS BLOCK EXECUTE MULTISET INTERSECT [ALL | DISTINCT] 参数：nesttable类型 返回值：nesttable类型 功能描述：两个集合变量的交集。如 A MULTISET INTERSECT B：ALL表是取A与B所有重复的元素；DISTINCT表示取A与B中重复元素，且去除重复元素。 示例： gaussdb=# declaregaussdb-# type nest is table of int;gaussdb-# a nest := nest(1,2,2);gaussdbs-# b nest := nest(2,2,3);gaussdb-# begingaussdb$# a := a MULTISET INTERSECT ALL b;gaussdb$# raise info '%', a;gaussdb$# end;gaussdb$# /INFO: {2,2}ANONYMOUS BLOCK EXECUTE gaussdb=# declaregaussdb-# type nest is table of int;gaussdb-# a nest := nest(1,2,2);gaussdb-# b nest := nest(2,2,3);gaussdb-# begingaussdb$# a := a MULTISET INTERSECT DISTINCT b;gaussdb$# raise info '%', a;gaussdb$# end;gaussdb$# /INFO: {2}ANONYMOUS BLOCK EXECUTE

处理步骤 显式游标处理需六个PL/SQL步骤： 定义静态游标：就是定义一个游标名，以及与其相对应的SELECT语句。 定义静态游标的语法图，请参见图1。 图1 static_cursor_define::= 或 图2 static_cursor_define::= 参数说明： cursor_name：定义的游标名。 parameter：游标参数，只能为输入参数，其格式为： parameter_name datatype select_statement：查询语句。 根据执行计划的不同，系统会自动判断该游标是否可以用于以倒序的方式检索数据行。 语法上支持parameter为输出参数，但其行为与输入参数保持一致。 定义动态游标：指ref游标，可以通过一组静态的SQL语句动态的打开游标。首先定义ref游标类型，然后定义该游标类型的游标变量，在打开游标时通过OPEN FOR动态绑定SELECT语句。 定义动态游标的语法图，请参见图3和图4。 图3 cursor_typename::= GaussDB支持sys_refcursor动态游标类型，函数或存储过程可以通过sys_refcursor参数传入或传出游标结果集合，函数也可以通过返回sys_refcursor来返回游标结果集合。 图4 dynamic_cursor_define::= 打开静态游标：就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。 打开静态游标的语法图，请参见图5。 图5 open_static_cursor::= 打开动态游标：可以通过OPEN FOR语句打开动态游标，动态绑定SQL语句。 打开动态游标的语法图，请参见图6。 图6 open_dynamic_cursor::= PL/SQL程序不能用OPEN语句重复打开一个游标。 提取游标数据：检索结果集合中的数据行，放入指定的输出变量中。 提取游标数据的语法图，请参见图7。 图7 fetch_cursor::= 对该记录进行处理。 继续处理，直到活动集合中没有记录。 关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH语句获取其中数据。关闭后的游标可以使用OPEN语句重新打开。 关闭游标的语法图，请参见图8。 图8 close_cursor::=

属性 游标的属性用于控制程序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。显式游标的属性为： %FOUND布尔型属性：当最近一次读记录时成功返回，则值为TRUE。 %NOTFOUND布尔型属性：与%FOUND相反。 %ISOPEN布尔型属性：当游标已打开时返回TRUE。 %ROWCOUNT数值型属性：返回已从游标中读取的记录数。

示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627282930313233343536373839 BEGINFOR ROW_TRANS IN SELECT first_name FROM hr.staffs LOOP DBE_OUTPUT.PRINT_LINE (ROW_TRANS.first_name ); END LOOP;END;/--创建表CREATE TABLE integerTable1( A INTEGER) DISTRIBUTE BY hash(A);CREATE TABLE integerTable2( B INTEGER) DISTRIBUTE BY hash(B);INSERT INTO integerTable2 VALUES(2);--多游标共享游标属性的标量DECLARE CURSOR C1 IS SELECT A FROM integerTable1;--声明游标 CURSOR C2 IS SELECT B FROM integerTable2; PI_A INTEGER; PI_B INTEGER;BEGIN OPEN C1;--打开游标 OPEN C2; FETCH C1 INTO PI_A; ---- C1%FOUND 和 C2%FOUND 值为 FALSE FETCH C2 INTO PI_B; ---- C1%FOUND 和 C2%FOUND 的值都为 TRUE --判断游标状态 IF C1%FOUND THEN IF C2%FOUND THEN DBE_OUTPUT.PRINT_LINE('Dual cursor share paremeter.'); END IF; END IF; CLOSE C1;--关闭游标 CLOSE C2;END;/--删除临时表DROP TABLE integerTable1;DROP TABLE integerTable2;

示例 1 2 3 4 5 6 7 8 91011121314151617181920212223 --删除EMP表中某部门的所有员工，如果该部门中已没有员工，则在DEPT表中删除该部门。CREATE TABLE hr.staffs_t1 AS TABLE hr.staffs;CREATE TABLE hr.sections_t1 AS TABLE hr.sections;CREATE OR REPLACE PROCEDURE proc_cursor3() AS DECLARE V_DEPTNO NUMBER(4) := 100; BEGIN DELETE FROM hr.staffs WHERE section_ID = V_DEPTNO; --根据游标状态做进一步处理 IF SQL%NOTFOUND THEN DELETE FROM hr.sections_t1 WHERE section_ID = V_DEPTNO; END IF; END;/CALL proc_cursor3();--删除存储过程和临时表DROP PROCEDURE proc_cursor3;DROP TABLE hr.staffs_t1;DROP TABLE hr.sections_t1;

游标概述 为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系。游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。 当游标作为存储过程的返回值时，如果使用JDBC调用该存储过程，返回的游标将不可用。 在分布式下，当SQL语句中调用的存储过程参数为refcursor类型或返回值为refcursor类型时，不支持下推。 游标的使用分为显式游标和隐式游标。对于不同的SQL语句，游标的使用情况不同，详细信息请参见表1。 表1 游标使用情况 SQL语句 游标 非查询语句 隐式的 结果是单行的查询语句 隐式的或显式的 结果是多行的查询语句 显式的 父主题： 游标

FORALL批量查询语句 语法图 图5 forall::= 变量index会自动定义为integer类型并且只在此循环里存在。index的取值介于low_bound和upper_bound之间。 示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627282930 CREATE TABLE TEST_t1 ( title NUMBER(6), did VARCHAR2(20), data_period VARCHAR2(25), kind VARCHAR2(25), interval VARCHAR2(20), time DATE, isModified VARCHAR2(10))DISTRIBUTE BY hash(did);INSERT INTO TEST_t1 VALUES( 8, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK' );CREATE OR REPLACE PROCEDURE proc_forall()AS BEGIN FORALL i IN 100..120 update TEST_t1 set title = title + 100*i;END; /--调用函数CALL proc_forall();--查询存储过程调用结果SELECT * FROM TEST_t1 WHERE title BETWEEN 100 AND 120;--删除存储过程和表DROP PROCEDURE proc_forall;DROP TABLE TEST_t1;

FOR_LOOP（integer变量）语句 语法图 图3 for_loop::= 变量name会自动定义为integer类型并且只在此循环里存在。变量name介于lower_bound和upper_bound之间。 当使用REVERSE关键字时，lower_bound必须大于等于upper_bound，否则循环体不会被执行。 示例 1 2 3 4 5 6 7 8 9101112131415 --从0到5进行循环CREATE OR REPLACE PROCEDURE proc_for_loop()AS BEGIN FOR I IN 0..5 LOOP DBE_OUTPUT.PRINT_LINE('It is '||to_char(I) || ' time;') ; END LOOP;END;/--调用函数CALL proc_for_loop();--删除存储过程DROP PROCEDURE proc_for_loop;

FOR_LOOP查询语句 语法图 图4 for_loop_query::= 变量target会自动定义，类型和query的查询结果的类型一致，并且只在此循环中有效。target的取值就是query的查询结果。 示例 1 2 3 4 5 6 7 8 910111213141516 --循环输出查询结果。CREATE OR REPLACE PROCEDURE proc_for_loop_query()AS record VARCHAR2(50);BEGIN FOR record IN SELECT spcname FROM pg_tablespace LOOP dbe_output.print_line(record); END LOOP; END; /--调用函数CALL proc_for_loop_query();--删除存储过程DROP PROCEDURE proc_for_loop_query;

示例 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031 CREATE OR REPLACE PROCEDURE proc_case_branch(pi_result in integer, pi_return out integer)AS BEGIN CASE pi_result WHEN 1 THEN pi_return := 111; WHEN 2 THEN pi_return := 222; WHEN 3 THEN pi_return := 333; WHEN 6 THEN pi_return := 444; WHEN 7 THEN pi_return := 555; WHEN 8 THEN pi_return := 666; WHEN 9 THEN pi_return := 777; WHEN 10 THEN pi_return := 888; ELSE pi_return := 999; END CASE; raise info 'pi_return : %',pi_return ; END; /CALL proc_case_branch(3,0);--删除存储过程DROP PROCEDURE proc_case_branch;

OPEN FOR 动态查询语句还可以使用OPEN FOR打开动态游标来执行。 语法参见图3。 图3 open_for::= 参数说明： cursor_name：要打开的游标名。 dynamic_string：动态查询语句。 USING value：在dynamic_string中存在占位符时使用。 游标的使用请参考游标。 示例 1 2 3 4 5 6 7 8 9101112131415161718192021 gaussdb=# DECLARE name VARCHAR2(20); phone_number VARCHAR2(20); salary NUMBER(8,2); sqlstr VARCHAR2(1024); TYPE app_ref_cur_type IS REF CURSOR; --定义游标类型 my_cur app_ref_cur_type; --定义游标变量 BEGIN sqlstr := 'select first_name,phone_number,salary from hr.staffs where section_id = :1'; OPEN my_cur FOR sqlstr USING '30'; --打开游标, using是可选的 FETCH my_cur INTO name, phone_number, salary; --获取数据 WHILE my_cur%FOUND LOOP dbe_output.print_line(name||'#'||phone_number||'#'||salary); FETCH my_cur INTO name, phone_number, salary; END LOOP; CLOSE my_cur; --关闭游标END;/

EXECUTE IMMEDIATE 语法图请参见图1。 图1 EXECUTE IMMEDIATE dynamic_select_clause::= using_clause子句的语法图参见图2。 图2 using_clause::= 对以上语法格式的解释如下： define_variable，用于指定存放单行查询结果的变量。 USING IN bind_argument，用于指定存放传递给动态SQL值的变量，即在dynamic_select_string中存在占位符时使用。 USING OUT bind_argument，用于指定存放动态SQL返回值的变量。 查询语句中，into和out不能同时存在； 占位符命名以“:”开始，后面可跟数字、字符或字符串，与USING子句的bind_argument一一对应； bind_argument只能是值、变量或表达式，不能是表名、列名、数据类型等数据库对象，即不支持使用bind_argument为动态SQL语句传递模式对象。如果存储过程需要通过声明参数传递数据库对象来构造动态SQL语句（常见于执行DDL语句时），建议采用连接运算符“||”拼接dynamic_select_clause； 动态PL/SQL块允许出现重复的占位符，即相同占位符只能与USING子句的一个bind_argument按位置对应。当设置guc参数behavior_compat_options值为dynamic_sql_compat时，会按照占位符的顺序依次匹配USING子句bind_argument，重复的占位符不会再识别为同一个占位符。 示例 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829 --从动态语句检索值（INTO 子句）：gaussdb=# DECLARE staff_count VARCHAR2(20);BEGIN EXECUTE IMMEDIATE 'select count(*) from hr.staffs' INTO staff_count; dbe_output.print_line(staff_count);END;/--传递并检索值（INTO子句用在USING子句前）：gaussdb=# CREATE OR REPLACE PROCEDURE dynamic_procAS staff_id NUMBER(6) := 200; first_name VARCHAR2(20); salary NUMBER(8,2);BEGIN EXECUTE IMMEDIATE 'select first_name, salary from hr.staffs where staff_id = :1' INTO first_name, salary USING IN staff_id; dbe_output.print_line(first_name || ' ' || salary);END;/--调用存储过程gaussdb=# CALL dynamic_proc();--删除存储过程gaussdb=# DROP PROCEDURE dynamic_proc;

示例 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334353637 gaussdb=# CREATE TABLE t1(a int);gaussdb=# INSERT INTO t1 VALUES(1),(10);--RETURN NEXTgaussdb=# CREATE OR REPLACE FUNCTION fun_for_return_next() RETURNS SETOF t1 AS $$DECLARE r t1%ROWTYPE;BEGIN FOR r IN select * from t1 LOOP RETURN NEXT r; END LOOP; RETURN;END;$$ LANGUAGE PLPGSQL;gaussdb=# call fun_for_return_next(); a--- 1 10(2 rows)-- RETURN QUERYgaussdb=# CREATE OR REPLACE FUNCTION fun_for_return_query() RETURNS SETOF t1 AS $$DECLARE r t1%ROWTYPE;BEGIN RETURN QUERY select * from t1;END;$$language plpgsql;gaussdb=# call fun_for_return_next(); a--- 1 10(2 rows)

语法 创建函数时需要指定返回值SETOF datatype。 return_next_clause::= return_query_clause::= 对以上语法的解释如下： 当需要函数返回一个集合时，使用RETURN NEXT或者RETURN QUERY向结果集追加结果，然后继续执行函数的下一条语句。随着后续的RETURN NEXT或RETURN QUERY命令的执行，结果集中会有多个结果。函数执行完成后会一起返回所有结果。 RETURN NEXT可用于标量和复合数据类型。 RETURN QUERY有一种变体RETURN QUERY EXECUTE，后面还可以增加动态查询，通过USING向查询插入参数。

语法 语法请参见图1。 图1 call_procedure::= using_clause子句的语法参见图2。 图2 using_clause::= 对以上语法格式的解释如下： CALL procedure_name，调用存储过程。 [:placeholder1，:placeholder2，…]，存储过程参数占位符列表。占位符个数与参数个数相同。 USING [IN|OUT|IN OUT] bind_argument，用于指定存放传递给存储过程参数值的变量。bind_argument前的修饰符与对应参数的修饰符一致。

示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627282930 --创建存储过程proc_add。gaussdb=# CREATE OR REPLACE PROCEDURE proc_add( param1 in INTEGER, param2 out INTEGER, param3 in INTEGER)ASBEGIN param2:= param1 + param3;END;/gaussdb=# DECLARE input1 INTEGER:=1; input2 INTEGER:=2; statement VARCHAR2(200); param2 INTEGER;BEGIN --声明调用语句 statement := 'call proc_add(:col_1, :col_2, :col_3)'; --执行语句 EXECUTE IMMEDIATE statement USING IN input1, OUT param2, IN input2; dbe_output.print_line('result is: '||to_char(param2));END;/--删除存储过程gaussdb=# DROP PROCEDURE proc_add;

示例 1 2 3 4 5 6 7 8 910111213141516171819 --创建存储过程dynamic_procgaussdb=# CREATE OR REPLACE PROCEDURE dynamic_procAS staff_id NUMBER(6) := 200; first_name VARCHAR2(20); salary NUMBER(8,2);BEGIN --执行匿名块 EXECUTE IMMEDIATE 'begin select first_name, salary into :first_name, :salary from hr.staffs where staff_id= :dno; end;' USING OUT first_name, OUT salary, IN staff_id; dbe_output.print_line(first_name|| ' ' || salary);END;/--调用存储过程gaussdb=# CALL dynamic_proc();--删除存储过程gaussdb=# DROP PROCEDURE dynamic_proc;

语法 语法请参见图1。 图1 call_anonymous_block::= using_clause子句的语法参见图2。 图2 using_clause::= 对以上语法格式的解释如下： 匿名块程序实施部分，以BEGIN语句开始，以END语句停顿，以一个分号结束。 USING [IN|OUT|IN OUT] bind_argument，用于指定存放传递给存储过程参数值的变量。bind_argument前的修饰符与对应参数的修饰符一致。 匿名块中间的输入输出参数使用占位符来指明，要求占位符个数与参数个数相同，并且占位符所对应参数的顺序和USING中参数的顺序一致。 目前GaussDB在动态语句调用匿名块时，EXCEPTION语句中暂不支持使用占位符进行输入输出参数的传递。

语法 语法请参见图1。 图1 noselect::= using_clause子句的语法参见图2。 图2 using_clause::= 对以上语法格式的解释如下： USING IN bind_argument用于指定存放传递给动态SQL值的变量，在dynamic_noselect_string中存在占位符时使用，即动态SQL语句执行时，bind_argument将替换相对应的占位符。要注意的是，bind_argument只能是值、变量或表达式，不能是表名、列名、数据类型等数据库对象。如果存储过程需要通过声明参数传递数据库对象来构造动态SQL语句（常见于执行DDL语句时），建议采用连接运算符“||”拼接dynamic_select_clause。另外，动态语句允许出现重复的占位符，相同占位符只能与唯一一个bind_argument按位置一一对应。当设置guc参数behavior_compat_options值为dynamic_sql_compat时，会按照占位符的顺序依次匹配USING子句bind_argument，重复的占位符不会再识别为同一个占位符。

示例 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334 --创建表gaussdb=# CREATE TABLE sections_t1( section NUMBER(4) , section_name VARCHAR2(30), manager_id NUMBER(6), place_id NUMBER(4) )DISTRIBUTE BY hash(manager_id);--声明变量gaussdb=# DECLARE section NUMBER(4) := 280; section_name VARCHAR2(30) := 'Info support'; manager_id NUMBER(6) := 103; place_id NUMBER(4) := 1400; new_colname VARCHAR2(10) := 'sec_name';BEGIN --执行查询 EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :4)' USING section, section_name, manager_id,place_id; --执行查询（重复占位符） EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :1)' USING section, section_name, manager_id; --执行ALTER语句（建议采用“||”拼接数据库对象构造DDL语句） EXECUTE IMMEDIATE 'alter table sections_t1 rename section_name to ' || new_colname;END; /--查询数据gaussdb=# SELECT * FROM sections_t1;--删除表gaussdb=# DROP TABLE sections_t1;

变量作用域 变量的作用域表示变量在代码块中的可访问性和可用性。只有在它的作用域内，变量才有效。 变量必须在declare部分声明，即必须建立BEGIN-END块。块结构也强制变量必须先声明后使用，即变量在过程内有不同作用域、不同的生存期。 同一变量可以在不同的作用域内定义多次，内层的定义会覆盖外层的定义。 在外部块定义的变量，可以在嵌套块中使用。但外部块不能访问嵌套块中的变量。 示例 1 2 3 4 5 6 7 8 91011121314 gaussdb=# DECLARE emp_id INTEGER :=7788; --定义变量并赋值 outer_var INTEGER :=6688; --定义变量并赋值BEGIN DECLARE emp_id INTEGER :=7799; --定义变量并赋值 inner_var INTEGER :=6688; --定义变量并赋值 BEGIN dbe_output.print_line('inner emp_id ='||emp_id); --显示值为7799 dbe_output.print_line('outer_var ='||outer_var); --引用外部块的变量 END; dbe_output.print_line('outer emp_id ='||emp_id); --显示值为7788END;/

变量声明 变量声明语法请参见图1。 图1 declare_variable::= 对以上语法格式的解释如下： variable_name，为变量名。 type，为变量类型。 value，是该变量的初始值（如果不给定初始值，则初始为NULL）。value也可以是表达式。 示例 123456 gaussdb=# DECLARE emp_id INTEGER := 7788; --定义变量并赋值BEGIN emp_id := 5*7784; --变量赋值END;/

示例 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334353637383940414243444546474849 --创建存储过程proc_staffsgaussdb=# CREATE OR REPLACE PROCEDURE proc_staffs(section NUMBER(6),salary_sum out NUMBER(8,2),staffs_count out INTEGER)ISBEGINSELECT sum(salary), count(*) INTO salary_sum, staffs_count FROM hr.staffs where section_id = section;END;/--创建存储过程proc_return.gaussdb=# CREATE OR REPLACE PROCEDURE proc_returnASv_num NUMBER(8,2);v_sum INTEGER;BEGINproc_staffs(30, v_sum, v_num); --调用语句dbe_output.print_line(v_sum||'#'||v_num);RETURN; --返回语句END;/--调用存储过程proc_return.gaussdb=# CALL proc_return();--清除存储过程gaussdb=# DROP PROCEDURE proc_staffs;gaussdb=# DROP PROCEDURE proc_return;--创建函数func_return.gaussdb=# CREATE OR REPLACE FUNCTION func_return returns voidlanguage plpgsqlAS $$DECLAREv_num INTEGER := 1;BEGINdbe_output.print_line(v_num);RETURN; --返回语句END $$;-- 调用函数func_returngaussdb=# CALL func_return();-- 清除函数gaussdb=# DROP FUNCTION func_return;

示例 gaussdb=# DECLARE my_id integer;BEGIN select id into my_id from customers limit 1; -- 赋值END;/gaussdb=# DECLARE type id_list is varray(6) of customers.id%type; id_arr id_list;BEGIN select id bulk collect into id_arr from customers order by id DESC limit 20; -- 批量赋值END;/ BULK COLLECT INTO 只支持批量赋值给数组或集合。数组类型合理使用LIMIT字段避免操作过量数据导致性能下降。 INTO/BULK COLLECT INTO 只支持4层以下Record类型直接嵌套。 对于数组变量，小括号"()"将优先识别为下标，因此对于带括号的表达式，不支持写在数组变量后面。如对于select (1+3) into va(5)，不支持写为select into va(5) (1+3)或select into va[5] (1+3)。

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99100101102103 下面存储过程中用到的表定义如下：gaussdb=# \d emp_rec Table "public.emp_rec" Column | Type | Modifiers ----------+--------------------------------+----------- empno | numeric(4,0) | not null ename | character varying(10) | job | character varying(9) | mgr | numeric(4,0) | hiredate | timestamp(0) without time zone | sal | numeric(7,2) | comm | numeric(7,2) | deptno | numeric(2,0) | --演示在存储过程中对数组进行操作。gaussdb=# CREATE OR REPLACE FUNCTION regress_record(p_w VARCHAR2)RETURNSVARCHAR2 AS $$DECLARE --声明一个record类型. type rec_type is record (name varchar2(100), epno int); employer rec_type; --使用%type声明record类型 type rec_type1 is record (name emp_rec.ename%type, epno int not null :=10); employer1 rec_type1; --声明带有默认值的record类型 type rec_type2 is record ( name varchar2 not null := 'SCOTT', epno int not null :=10); employer2 rec_type2; CURSOR C1 IS select ename,empno from emp_rec order by 1 limit 1; BEGIN --对一个record类型的变量的成员赋值。 employer.name := 'WARD'; employer.epno = 18; raise info 'employer name: % , epno:%', employer.name, employer.epno; --将一个record类型的变量赋值给另一个变量。 employer1 := employer; raise info 'employer1 name: % , epno: %',employer1.name, employer1.epno; --将一个record类型变量赋值为NULL。 employer1 := NULL; raise info 'employer1 name: % , epno: %',employer1.name, employer1.epno; --获取record变量的默认值。 raise info 'employer2 name: % ,epno: %', employer2.name, employer2.epno; --在for循环中使用record变量 for employer in select ename,empno from emp_rec order by 1 limit 1 loop raise info 'employer name: % , epno: %', employer.name, employer.epno; end loop; --在select into 中使用record变量。 select ename,empno into employer2 from emp_rec order by 1 limit 1; raise info 'employer name: % , epno: %', employer2.name, employer2.epno; --在cursor中使用record变量。 OPEN C1; FETCH C1 INTO employer2; raise info 'employer name: % , epno: %', employer2.name, employer2.epno; CLOSE C1; RETURN employer.name;END;$$LANGUAGE plpgsql;--调用该存储过程。gaussdb=# CALL regress_record('abc');--删除存储过程。gaussdb=# DROP PROCEDURE regress_record;-- 打开兼容性参数proc_outparam_override时，返回已定义的record类型，函数可以不需out参数create type rec_type is (c1 int, c2 int);create or replace function func(a in int) return rec_typeas declarer rec_type;beginr.c1:=1;r.c2:=2;return r;end;/call func(0);-- 打开兼容性参数proc_outparam_override时，函数直接返回未定义的record类型时，至少需要带有一个out参数create or replace function func(a out int) return recordas declaretype rc is record(c1 int);r rc;beginr.c1:=1;a:=1;return r;end;/call func(1);

语法格式 查询数据 1 2 3 4 5 6 7 8 9101112131415 [ WITH [ RECURSIVE ] with_query [, ...] ]SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] { * | {expression [ [ AS ] output_name ]} [, ...] } [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] [ WINDOW {window_name AS ( window_definition )} [, ...] ] [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ] [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ] [ LIMIT { [offset,] count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT N]} [...] ]TABLE { ONLY { (table_name) | table_name } | table_name [ * ]}; condition和expression中可以使用targetlist中表达式的别名。 只能同一层引用。 只能引用targetlist中的别名。 只能是后面的表达式引用前面的表达式。 不能包含volatile函数。 不能包含Window function函数。 不支持在join on条件中引用别名。 targetlist中有多个要应用的别名则报错。 其中子查询with_query为： 12 with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} ) 其中指定查询源from_item为： 123456789 {[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ][ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]|( select ) [ AS ] alias [ ( column_alias [, ...] ) ]|with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]|function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]|function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )|from_item unpivot_clause|from_item pivot_clause|from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]} 其中group子句为： 123456 ( )| expression| ( expression [, ...] )| ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )| CUBE ( { expression | ( expression [, ...] ) } [, ...] )| GROUPING SETS ( grouping_element [, ...] ) 其中指定分区partition_clause为： 12 PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } 指定分区只适合普通表。 其中设置排序方式nlssort_expression_clause为： 12 NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' )其中，第二个参数可选generic_m_ci，仅支持纯英文不区分大小写排序。 简化版查询语法，功能相当于select * from table_name。 1 TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};

基本语句 在编写PL/SQL过程中，会定义一些变量，给变量赋值，调用其他存储过程等。介绍PL/SQL中的基本语句，包括定义变量、赋值语句、调用语句以及返回语句。 尽量不要在存储过程中调用包含密码的SQL语句，因为存储在数据库中的存储过程文本可能被其他有权限的用户看到导致密码信息被泄漏。如果存储过程中包含其他敏感信息也需要配置存储过程的访问权限，保证敏感信息不会泄漏。 定义变量 赋值语句 调用语句 父主题： 存储过程

带索引的集合类型 该集合类型将下标和对应成员值以键值对的方式存储在HASH表中，对该类型变量的所有操作实际就是对HASH表的操作。用户无需自行扩展或释放存储空间，仅需通过赋值或delete方式进行存储和删除成员。集合相关操作、说明如下： 类型定义 索引集合类型定义需同时指定成员类型data_type和下标类型indexby_type，其中下标类型仅支持integer和varchar。 变量声明和初始化 索引集合类型声明后存在3种初始化场景：未初始化、初始化为空、初始化指定下标和成员值。其中未初始化和初始化为空场景对变量的效果一致。未初始化或初始化为空后变量不为NULL，后续都可以对变量直接进行赋值。初始化指定下标和成员值场景会将指定的下标和成员值以键值对的形式保存到变量中。 变量赋值 索引集合类型变量赋值分为两种：成员赋值和整体赋值。成员赋值可通过指定下标方式对某个成员赋值，若该成员不存在则直接赋值，若存在则刷新该成员值。整体赋值则会将被赋值变量中原有成员都清空后重新保存新的成员值。整体赋值场景不能给变量赋NULL值，否则报错。 变量取值 通过指定下标方式可获取变量中对应下标的成员值，若通过下标找不到该成员则会返回no data found的错误信息。 非兼容ORA模式下（参数sql_compatibility值不为ORA），不支持创建带索引集合类型。 支持在匿名块、存储过程、自定义函数、package中定义带索引集合类型，其作用域各不相同。不支持在schema中定义带索引集合类型。 NOT NULL只支持语法不支持功能。 char、vachar、numeric、float、number等元素类型的范围约束语法创建集合类型，例如：“type t1 is table of numeric(5) index by int; ”只支持语法创建，不支持元素的范围约束功能，功能等同于“type t1 is table of numeric index by int;”。 data_type可以为基础数据类型，或存储过程内定义的record类型，集合类型，数组类型，不支持ref cursor类型。 indexby_type仅支持integer和varchar，其中varchar的长度暂不约束。 未初始化的带索引集合类型变量非NULL。 带索引集合类型变量不能赋NULL值，否则报错。 带索引集合类型变量作为入参不能赋NULL值或''。 不同的带索引集合类型的变量不能相互赋值。即使成员类型和下标类型相同，但集合类型名称不同，也是不同的集合类型。如 TYPE t1 IS TABLE OF int index by int; 和 TYPE t2 IS TABLE OF int index by int; 定义的两个集合类型，t1和t2是不同的集合类型，以其定义的变量不支持相互赋值（作为成员类型时该约束不保证生效，赋值逻辑受父类型影响）。 带索引集合类型不支持关系运算和算数运算操作。 select ... bulk collect into 方式赋值带索引集合类型变量时，只支持下标为integer类型的集合类型，下标为varchar类型集合不支持。 支持带索引集合类型变量作为函数的参数和返回值，此时要求参数或者返回值的类型是在package中定义的集合类型。 带索引的集合作为函数入参时，可以传入对应子元素类型相同的数组类型作为入参，不支持多维数组，不支持索引类型为varchar（过时的方法，不建议使用该功能。可执行“set behavior_compat_options = 'disable_rewrite_nesttable';”禁用）。 类型构造器目前仅支持集合类型，其参数个数的上限与用户自定义函数参数个数上限相同。对于带索引的集合类型，构造器在使用时索引的值仅支持为常量。 不支持对XML类型数据操作。 集合类型以及嵌套集合的类型不支持作为表中的一列来创建表。 集合类型的构造器不支持浮点数以及表达式作为下标。

示例 下面列举了基本的匿名块程序： 1 2 3 4 5 6 7 8 91011121314151617181920 --空语句块gaussdb=# BEGIN NULL; END;/--将信息打印到控制台：gaussdb=# BEGIN dbe_output.print_line('hello world!'); END; /--将变量内容打印到控制台：gaussdb=# DECLARE my_var VARCHAR2(30); BEGIN my_var :='world'; dbe_output.print_line('hello'||my_var); END; /

结构 PL/SQL块中可以包含子块，子块可以位于PL/SQL中任何部分。PL/SQL块的结构如下： 声明部分：声明PL/SQL用到的变量，类型及游标，以及局部的存储过程和函数。 DECLARE 不涉及变量声明时声明部分可以没有。 对匿名块来说，没有变量声明部分时，可以省去DECLARE关键字。 对存储过程来说，没有DECLARE， AS相当于DECLARE。即便没有变量声明的部分，关键字AS也必须保留。 执行部分：过程及SQL语句，程序的主要部分。必选。 BEGIN 执行异常部分：错误处理。可选。 EXCEPTION 结束 END;/ 禁止在PL/SQL块中使用连续的Tab，连续的Tab可能会造成在使用gsql工具带“-r”参数执行PL/SQL块时出现异常。