openGauss数据库存储过程语法详解

leitoyn

已于 2023-08-16 15:15:20 修改

阅读量4k

点赞数 2

文章标签：数据库 gaussdb

于 2023-08-15 15:57:46 首次发布

本文链接：https://blog.csdn.net/leitoyn/article/details/132300793

版权

openGauss数据库存储过程语法详解
参考openGauss使用手册 https://www.bookstack.cn/read/opengauss-3.1.1-zh/1359a61918bb8d01.md

1.存储过程的声明
创建一个存储过程可以通过CREATE PROCEDURE命令来实现，其主要形式如下：
CREATE [OR REPLACE] PROCEDURE 存储过程名(
   [IN|OUT] 参数1 数据类型,
   [IN|OUT] 参数2 数据类型,
   ...
)
AS
DECLARE
   变量1 数据类型;
   变量2 数据类型;
...
BEGIN
   存储过程的程序体
END;

示例：
CREATE OR REPLACE PROCEDURE get_name_by_id(p_id int, OUT result_name text)
AS
BEGIN
SELECT name INTO result_name FROM my_table WHERE id = p_id;
END;
上面是一个带参数的存储过程，参数可以为入参，也可以为出参，默认不声明的为入参；
上面的写法是创建或更新一个存储过程，如果没有此存储过程则为创建，如果已存在则更新；
如果仅创建存储过程（没有入参，也无返回int值）则可以简写如下：
CREATE PROCEDURE get_name_by_id()
AS
BEGIN
SELECT name FROM my_table WHERE id = 'admin';
END;

注意：语句结束要以';'来结尾

2.存储过程调用
CALL 存储过程名([参数,]);
示例：
CALL get_name_by_id(1, name_value);

3.存储过程参数
存储过程的参数有3种不同的输入／输出模式：IN、OUT、INOUT。

1)IN参数是存储过程的输入参数，它将存储过程外部的值传递给存储过程使用。
2)OUT参数是存储过程的输出参数，存储过程在执行时，会将执行的中间结果赋值给OUT参数，存储过程执行完毕后，外部用户可以通过OUT参数获得存储过程的执行结果。
3)INOUT参数则同时具有IN参数和OUT参数的性质，它既是存储过程的输入参数，同时在存储过程执行中也会通过INOUT参数将中间结果输出给外部用户。

示例：
CREATE PROCEDURE new_orders_insert(
   IN o_id INTEGER,
   OUT d_id INTEGER,
   INOUT w_msg varchar(50)
)
AS
BEGIN
   INSERT INTO new_orders VALUES(o_id, d_id, w_msg);
   d_id := 100;
   w_msg := '记录已添加。';
END;
/

4.关键字
openGauss继承了postgresql的众多语法特点，且又借鉴了一些其他SQL语法，所以关键字较多，使用时注意避开作为表列名，参数名，变量名等使用。
数据类型中引入了别名概念，所以一些语法兼容其他SQL，如整形可以写完整如integer,也可以简写 int

5.变量及类型
DECLARE
变量名1 类型1;
变量名2 类型2;

示例：
DECLARE
   P_ls_XH integer;
   P_ls_count int;
P_ls_rowcount int; --行数
P_i int; --取charindex时，定义
   P_index int;
   P_char_len int;
   lists VARCHAR[]; --定义一个字符串数组
   P_item varchar(100);
   name_value text;--定义大文本
cur refcursor;   -- 定义游标

row record; -- 定义record类型的变量来存储每行的返回值

6.自动提交关闭
执行过程中，自动提交默认为打开状态，如果放在事务中执行则可以手动将自动提交关闭
SET autocommit =OFF; --如果不关闭自动提交，则执行插入或更新会立即提交

7.事务
事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。openGauss数据库支持的事务控制命令有启动、设置、提交、回滚事务。openGauss数据库支持的事务隔离级别有READ COMMITTED、READ UNCOMMITTED、REPEATABLE READ和SERIALIZABLE，不推荐使用READ UNCOMMITTED，SERIALIZABLE等价于REPEATABLE READ。
1）启动事务
用户可以使用START TRANSACTION和BEGIN语法启动事务。

2）设置事务
用户可以使用SET TRANSACTION或者SET LOCAL TRANSACTION语法设置事务特性，详细操作请参考SET TRANSACTION。

3）提交事务
用户可以使用COMMIT或者END完成提交事务的功能，即提交事务的所有操作，详细操作请参考COMMIT | END。

4）回滚事务
回滚是在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，详细操作请参考ROLLBACK。

示例：
begin
   SET autocommit =OFF; --如果不关闭自动提交，则执行插入或更新会立即提交
   START TRANSACTION; --开启事务
   update users set name='admin' where id=1;
   COMMIT;
   EXCEPTION
   WHEN OTHERS THEN
   RAISE NOTICE '错误:第'+to_char(PG_EXCEPTION_HINT)+'行,内容:'+MESSAGE_TEXT+' 详细:'+PG_EXCEPTION_DETAIL;
rollback;
end;
/

8.异常处理
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
condition：异常名字，有两种使用方式:
.异常名：WHEN division_by_zero THEN，规律：小写下划线连接关键字
.异常码：WHEN SQLSTATE '22012' THEN ...，规律：5位字符，数字和任意字母组成
.OTHERS：匹配一些异常
handler_statements：异常处理语法块，如果这里面再产生异常不会被当前的EXCEPTION捕获，会直接抛到上层。
特殊变量：SQLSTATE、SQLERRM只在EXCEPTION语法块中生效，可以打印错误码和错误信息。
例如：division_by_zero异常处理时，sqlerrm="division by zero"，sqlstate=22012
DECLARE
user_id int := 100;
BEGIN
user_id = user_id / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
END;
--系统异常
DECLARE
user_id int := 100;
BEGIN
user_id = user_id / 0;
EXCEPTION
WHEN SQLSTATE '22012' THEN
RAISE NOTICE 'caught division_by_zero';
END;
--主动产生异常：没给错误码使用SQLSTATE P0001
DECLARE
user_id int := 100;
text_var1 text;
text_var2 text;
text_var3 text;
text_var4 text;
text_var5 text;
BEGIN
RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user ID';
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
text_var2 = PG_EXCEPTION_DETAIL,
text_var3 = PG_EXCEPTION_HINT,
text_var4 = PG_EXCEPTION_CONTEXT,
text_var5 = RETURNED_SQLSTATE;
raise notice '%', text_var1;
raise notice '%', text_var2;
raise notice '%', text_var3;
raise notice '%', text_var4;
raise notice '%', text_var5;
END;
--特殊变量：注意sqlerrm会被异常文本替换掉，sqlstate永远是预定义好的错误码
DECLARE
user_id int := 100;
BEGIN
RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user ID';
EXCEPTION
WHEN OTHERS THEN
raise notice 'sqlstate: %', sqlstate;
raise notice 'sqlerrm: %', sqlerrm;
END;

示例：
create or replace procedure proc2() as
$$
declare
begin
raise notice '--begin to drop table t2,time=%',clock_timestamp();
   drop table if exists t2;
   raise notice '--drop table t2,time=%',clock_timestamp();
raise notice '--begin to alter table t1 rename to t2,time=%',clock_timestamp();
alter table t1 rename to t2;
raise notice '--alter table t1 rename to t2,time=%',clock_timestamp();

raise notice '--do something...,time=%',clock_timestamp();
perform pg_sleep(30);

raise notice '--begin to commit,time=%',clock_timestamp();
commit;
raise notice '-- commit over,time=%',clock_timestamp();

raise notice 'job is over,time=%',clock_timestamp();

exception when others then
raise notice 'sqlstate=%,sqlerrm=%', sqlstate,sqlerrm;
end;
$$ language plpgsql;

9.从异常中捕获系统信息
DECLARE
text_var1 text;
text_var2 text;
text_var3 text;
BEGIN
-- some processing which might cause an exception
...
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
text_var2 = PG_EXCEPTION_DETAIL,
text_var3 = PG_EXCEPTION_HINT;
END;

10.正确使用异常
1) RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
2) RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
3) RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
4) RAISE [ level ] USING option = expression [, ... ];
5) RAISE ; -- 特殊，只能在EXCEPTION语法块中使用，重新抛出catch的异常。
level
可选DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
其中DEBUG到WARNING都不会产生错误，只会打印日志，日志级别按level输出，由log_min_messages、client_min_messages参数决定是否输出。
其中EXCEPTION会产生错误，中断程序执行，错误如果不被捕获会被抛到上一层。
抛出指定类型的异常:
方式一：RAISE SQLSTATE
方式二：RAISE condition_name
方式三：RAISE 'text' USING ERRCODE = 'unique_violation'

抛出指定类型异常：
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE SQLSTATE '22012';

示例：
DECLARE
user_id int := 100;
BEGIN
RAISE EXCEPTION 'Nonexistent ID --> %', user_id
USING HINT = 'Please check your user ID';
END;
示例2：
CREATE OR REPLACE PROCEDURE TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK()
AS
BEGIN
DROP TABLE IF EXISTS TEST_COMMIT;
CREATE TABLE TEST_COMMIT(A INT, B INT);
INSERT INTO TEST_COMMIT SELECT 1, 1;
COMMIT;
CREATE TABLE TEST_ROLLBACK(A INT, B INT);
RAISE EXCEPTION 'RAISE EXCEPTION AFTER COMMIT';
EXCEPTION
WHEN OTHERS THEN
INSERT INTO TEST_COMMIT SELECT 2, 2;
ROLLBACK;
END;
/
示例3：
create or replace procedure proc1() as
$$
declare
begin
raise notice '--begin to drop table t2,time=%',clock_timestamp();
   drop table if exists t2;
   raise notice '--drop table t2,time=%',clock_timestamp();
raise notice '--begin to alter table t1 rename to t2,time=%',clock_timestamp();
alter table t1 rename to t2;
raise notice '--alter table t1 rename to t2,time=%',clock_timestamp();

raise notice '--do something...,time=%',clock_timestamp();
perform pg_sleep(30);

raise notice '--begin to commit,time=%',clock_timestamp();
commit;
raise notice '-- commit over,time=%',clock_timestamp();

raise notice 'job is over,time=%',clock_timestamp();

11.使用游标
游标的使用方法：
定义游标，通过open对一个指定的查询语句打开游标，通过fetch next来将每一条信息存到record类型的变量row中。

示例：
DECLARE
-- 定义游标
cur refcursor;
-- 定义record类型的变量来存储每行的返回值
row record;
BEGIN
-- 打开游标
OPEN cur FOR SELECT id, name FROM my_table;

-- 获取并打印每行返回值
LOOP
FETCH NEXT FROM cur INTO row;
EXIT WHEN NOT FOUND; -- 当找不到时退出
-- 打印每行的返回值
RAISE NOTICE 'id: %, name: %', row.id, row.name;
END LOOP;

-- 关闭游标
CLOSE cur;
END;
/

12.通过for in select的方式来返回结果
示例：
CREATE OR REPLACE PROCEDURE process_data()
LANGUAGE plpgsql
AS $$
DECLARE
result_value column_type;
BEGIN
FOR result_value IN SELECT column_name FROM table_name LOOP
-- 在这里对每个查询结果进行处理
-- 可以直接使用result_value变量访问查询结果
-- 例如：RAISE NOTICE 'column_value: %', result_value;
END LOOP;
END;
$$;

13.通过return query来按照select的方式返回结果
注意这里的话，我们需要创建的是函数
示例：
CREATE OR REPLACE FUNCTION get_multi_rows()
RETURNS TABLE (id int, name text)
LANGUAGE plpgsql
AS $$
BEGIN
-- 返回查询结果集
RETURN QUERY SELECT id, name FROM my_table;
END;
$$;
那么之后只需要调用函数即可
SELECT * FROM get_multi_rows();

14.如何判断字段是否为空
if COALESCE(P_item,'-1')='-1' then
           EXIT;
if COALESCE(P_ls_XH,0)=0 then
       P_ls_XH:=1;
   ELSE
       P_ls_XH :=P_ls_XH+1;
   END if;
select * from public.pe_bgmb where bz is not null

如果查询出的数据为空，则设为0 或 ‘’
COALESCE(p.paytype,0) as paytype,
COALESCE(p.bankcard, '') as bankcard

15.获取GUID，UUID
创建函数获取GUID，然后调用
CREATE OR REPLACE FUNCTION public.new_guid()
RETURNS character varying
LANGUAGE plpgsql
SECURITY DEFINER NOT FENCED NOT SHIPPABLE
AS $$

DECLARE

v_seed_value varchar(32);

BEGIN

select

md5(

inet_client_addr()::varchar ||

timeofday() ||

inet_server_addr()::varchar ||

to_hex(inet_client_port())

)

into v_seed_value;

return (substr(v_seed_value,1,8) || '-' ||

substr(v_seed_value,9,4) || '-' ||

substr(v_seed_value,13,4) || '-' ||

substr(v_seed_value,17,4) || '-' ||

substr(v_seed_value,21,12));

END;

$$;
/

调用方法：
select REPLACE(new_guid(), '-', '') --可以把间隔符-去掉

16.数组和间隔符拆分
DECLARE
lists VARCHAR[]; --数据类型[] 即为数组
lists = string_to_array(P_KSID,',',null); --string_to_array常用字符串分割函数
在使用数组之前，需要自定义一个数组类型。
在存储过程中紧跟AS关键字后面定义数组类型。定义方法如下。
TYPE array_type IS VARRAY(size) OF data_type;
.array_type：要定义的数组类型名。
.VARRAY：表示要定义的数组类型。
.size：取值为正整数，表示可以容纳的成员的最大数量。
.data_type：要创建的数组中成员的类型。

数组操作符
1）= 描述：两个数组是否相等
SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] AS RESULT ; --返回真
2）<> 描述：两个数组是否不相等
SELECT ARRAY[1,2,3] <> ARRAY[1,2,4] AS RESULT; --返回真

间隔符拆分函数：
1）SPLIT_PART
SPLIT_PART() 函数通过指定分隔符分割字符串，并返回第N个子串。语法：
SPLIT_PART(string, delimiter, position)
SELECT SPLIT_PART('A,B,C', ',', 2); -- 返回B
下面我们利用该函数分割日期，获取年月日：
select split_part( current_date::text,'-',1) as year ,
split_part( current_date::text,'-',2) as month,
split_part( current_date::text,'-',3) as day
2）STRING_TO_ARRAY
该函数用于分割字符串至数组元素，请看语法：

string_to_array(string, delimiter [, null string])
该函数用于分割字符串至数组元素，请看语法：

string_to_array(string, delimiter [, null string])
string : 待分割的字符串
delimiter：指定分割字符串
null string : 设定空串的字符串
示例：
SELECT string_to_array('xx~^~yy~^~zz', '~^~'); -- {xx,yy,zz}
SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy'); -- {xx,,zz}

示例2：
下面示例中用到的表定义如下：
openGauss=# \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) |
--演示在函数中对数组进行操作。
openGauss=# CREATE OR REPLACE FUNCTION regress_record(p_w VARCHAR2)
RETURNS
VARCHAR2 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。
employer := NULL;
raise info 'employer name: % , epno: %',employer.name, employer.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;
--调用该函数。
openGauss=# CALL regress_record('abc');
--删除函数。
openGauss=# DROP FUNCTION regress_record;

17.赋值语法
变量名 := 值；
示例：
P_li_code := -2;
P_ls_message := '操作员账号或工号已存在';

select P_ls_rowcount=COUNT(*) from QX_CZYXX where deleted='0';

18.判断语法
五种形式的IF：
1) IF bool THEN statements END IF;
2) IF bool THEN statements ELSE statements END IF;
3) IF sex = 'm' THEN
pretty_sex := 'man';
ELSE
IF sex = 'f' THEN
pretty_sex := 'woman';
END IF;
END IF;
4)IF number_tmp = 0 THEN
result := 'zero';
ELSIF number_tmp > 0 THEN
result := 'positive';
ELSIF number_tmp < 0 THEN
result := 'negative';
ELSE
result := 'NULL';
END IF;
5)IF i > 0 THEN
raise info 'i:% is greater than 0. ',i;
ELSIF i < 0 THEN
raise info 'i:% is smaller than 0. ',i;
ELSE
raise info 'i:% is equal to 0. ',i;
END IF;

19.获取操作影响的行数
利用系统参数ROW_COUNT
get diagnostics P_ls_rowcount := ROW_COUNT ;

示例：
insert into QX_CZYXX(HOSPITALID,CZYID,CZYMC,CZYBM,PASS,BZ,CZYZT ,XH,SYSID,xtyhbs,deleted)
               VALUES(P_HOSPITALID,P_CZYID,P_CZYMC,P_CZYBM,P_PASS,P_BZ,P_CZYZT,P_ls_XH,'TJXT','0','0');
   get diagnostics P_ls_rowcount := ROW_COUNT ;
   if P_ls_rowcount = 0 then
           P_li_code := -4;
           P_ls_message := '新增操作员失败。'   ;

raise exception '%',P_ls_message ;

end if;

20.字符串函数
1）算字符串字符数和字符串长度的函数：char_length(str)和length(str)
2）合并字符串函数：concat(s1,s2,,,,)、concat_ws(x,s1,s2,,,,)
concat(s1,s2,,,)返回结果为连接参数产生的字符串。任何一个参数为null，返回值就为null。如果所有参数为非二进制字符串，那么结果为非二进制字符串。如果自变量中含有任一二进制字符串，那么结果就为一个二进制字符串。
concat_ws(x,s1,s2,,,)x是与其他参数的分隔符。

3）获取指定长度的字符串的函数：left(s,n)和right(s,n)
left(s,n)返回字符串s开始的最左边n个字符。
4）填充字符串的函数：lpad(s1,len,s2)和rpad(s1,len,s2)
lpad(s1,len,s2)返回字符串s1，其左边由字符s2填充，填充长度为len，加入s1的长度大于len，则返回值被缩短至len字符。
5）删除空格的函数：ltrim(s)、rtrim(s)和trim(s)
ltrim(s)返回字符串s，字符串左侧空格字符被删除。
6）删除指定字符串的函数：trim(s1 from s)
trim(s1 from s)删除字符串s中两端所有的子字符串s1.s1为可选项，在未指定情况下删除空格。
7）重复生成字符串的函数：repeat(s,n)
repeat(s,n)返回一个由重复的字符串s组成的字符串，n表示重复生成的次数。若n<=0,则返回一个空字符串；若s或n为null，则返回null。
8）替换函数：replace(s,s1,s2)
replace(s,s1,s2)使用字符串s2替代字符串s中所有字符串s1.
9）获取子串的函数：substring(s,n,len)
substring(s,n,len)表示从字符串s返回一个长度为len的子字符串，起始于位置n。也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n的字符，即倒数第n个字符。
10）匹配子串开始位置的函数：position(str1 in str)
position(str1 in str)函数的作用是返回子字符串str1在字符串str中的开始位置。
11）字符串逆序函数：reverse(s)
reverse(s)将字符串s反转，返回的字符串的顺序和s字符顺序相反。

21.日期时间
获取当前日期的函数&&获取当前时间的函数
1、获取当前日期的函数和获取当前时间的函数：current_date和current_time
2、调用该函数时不需要在函数名后加括号。该日期是服务器的日期，不是客户端的日期。
注意：
上面所有的函数，包括 CURRENT_DATE，返回的都是当前事务开始的时间。在同一个事务期间，多次调用相同的函数将会返回相同的值，结果不会随着时间增加。这一点与其他数据库的实现可能不同。

获取当前日期的函数
current_date函数：的作用是将当前日期按照“YYYY-MM-DD”格式的值返回，具体格式根据函数用在字符串或是数字语境中而定的。
select current_date;
select current_time;
select current_timestamp;

示例：
CREATE OR REPLACE sp_test
...
DECLARE
lts_systimestamp timestamp;
BEGIN;
lts_systimestamp := statement_timestamp();
...
RAISE NOTICE 'Step 1 take time: %', statement_timestamp() - lts_systimestamp;
...
END;

22.循环 LOOP
1）LOOP statements END LOOP;
示例：
CREATE OR REPLACE PROCEDURE proc_loop(i in integer, count out integer)
AS
BEGIN
count:=0;
LOOP
IF count > i THEN
raise info 'count is %. ', count;
EXIT;
ELSE
count:=count+1;
END IF;
END LOOP;
END;
/
CALL proc_loop(10,5);
2）WHILE_LOOP语句
示例：
CREATE TABLE integertable(c1 integer) ;
CREATE OR REPLACE PROCEDURE proc_while_loop(maxval in integer)
AS
DECLARE
i int :=1;
BEGIN
WHILE i < maxval LOOP
INSERT INTO integertable VALUES(i);
i:=i+1;
END LOOP;
END;
/
--调用函数
CALL proc_while_loop(10);
--删除存储过程和表
DROP PROCEDURE proc_while_loop;
DROP TABLE integertable;
3）FOR_LOOP（integer变量）语句 FOR target IN query LOOP statements END LOOP;
4）FORALL批量查询语句
示例：
CREATE TABLE hdfs_t1 (
title NUMBER(6),
did VARCHAR2(20),
data_peroid VARCHAR2(25),
kind VARCHAR2(25),
interval VARCHAR2(20),
time DATE,
isModified VARCHAR2(10)
);
INSERT INTO hdfs_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 hdfs_t1 set title = title + 100*i;
END;
/
--调用函数
CALL proc_forall();
--查询存储过程调用结果
SELECT * FROM hdfs_t1 WHERE title BETWEEN 100 AND 120;
--删除存储过程和表
DROP PROCEDURE proc_forall;
DROP TABLE hdfs_t1;

23.函数(存储过程)返回多条记录

示例：
--查询当前月份表是否存在
       v_tablename := 'rechargerecord'||to_char(p_creatat::timestamp,'YYMM');
       SELECT count(1) into v_count from pg_tables where tablename = v_tablename;
IF v_count<=0 THEN
--当前表不存在，基于初始表复制一张备份表
v_sql:='CREATE TABLE '||v_tablename||'
                       (LIKE rechargerecord1907 including constraints including indexes including defaults)';
EXECUTE v_sql;
end if;

--判断当前插入的id是否已存在
       v_sql:='SELECT count(1) from '||v_tablename||' WHERE id=$1';
       EXECUTE v_sql into v_count using p_id;
       IF v_count>0 THEN
               v_result :=1003; --数据已存在
               RAISE EXCEPTION '%',v_result;
       end if;

v_sql := 'INSERT into '||v_tablename||'(
           id,tradeno,uno,paychannel,payamount,amount,afterbalance,
           payfee,status,extra,thirdorder,completeat,payat,creatat,
           isdownload,downloadno,downloadat)
           VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)';

EXECUTE v_sql using p_id,p_tradeno,p_uno,p_paychannel,p_payamount,p_amount,p_afterbalance,
p_payfee,p_status,p_extra,p_thirdorder,
p_completeat::timestamp,p_payat::timestamp,p_creatat::timestamp,p_isdownload,p_downloadno,
p_downloadat::timestamp;
GET DIAGNOSTICS v_count := ROW_COUNT;
IF v_count>0 THEN
           v_result :=1; --插入成功
end if;

示例2：
CREATE OR REPLACE FUNCTION "public"."proc_h_testselect"("p_type" int4, "p_id" int4, "p_offset" int4, "p_limit" int4)
RETURNS "pg_catalog"."refcursor" AS $BODY$
declare
resultset refcursor;--返回游标
v_rowcount int;
v_con varchar;
v_sql varchar;
begin
--查询单条记录
if p_type=1 then
open resultset for select 1 as recordcount,s.id,s.phoneno,s.idno,s.name,s.operatorid,s.creatat
from freetopolice s
where s.id=p_id;
--查询多条记录
else
v_con := ' where 1=1';
v_sql :=' select count(*) from freetopolice s '||v_con;
execute v_sql into v_rowcount;
v_sql :=' select '||v_rowcount||' as recordcount,s.id,s.phoneno,s.idno,s.name,s.operatorid,s.creatat from freetopolice s'||v_con||
'order by s.id desc offset '||p_offset||' limit '||p_limit;
open resultset for execute v_sql;
end if;
return resultset;

end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

24.CASE WHEN
case when..........then.......else
示例：
select sum(1) as a1
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE' and before_prefr_unit_price < 100

select sum(1) as a2
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE' and
(before_prefr_unit_price >= 100 and before_prefr_unit_price < 200 )

select sum(1) as a3
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE' and
(before_prefr_unit_price >= 200 and before_prefr_unit_price < 300 )

select sum(1) as a4
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE' and
(before_prefr_unit_price >= 300 )

示例2：
SELECT
           companyname,
           companyno,
           sum(case when issuercode = 'D1035510FFFFFFFF' then price else 0 end) as dtallprice,
           sum(case when issuercode = 'D1035510FFFFFFFF' then account else 0 end) as dtcount,
           sum(price) as allprice,
           sum(account) as count
FROM summerycompleterecord
where companyname!='' and companyno!=''
GROUP BY companyno,companyname
ORDER BY companyno,companyname
OFFSET 0 LIMIT 20

25.多行合并一行

SELECT array_to_string(array_agg(GNMKID),';') from QX_CZYQX where JSID='admin'

array_agg(expression) 聚集函数

描述：将所有输入值（包括空）连接成一个数组。

返回类型：参数类型的数组。

openGauss=# SELECT ARRAY_AGG(sr_fee) FROM tpcds.store_returns WHERE sr_customer_sk = 2;
array_agg
---------------
{22.18,63.21}
(1 row)

array_to_string 数组函数

array_to_string(anyarray, text [, text])

描述：使用第一个text作为数组的新分隔符，使用第二个text替换数组值为null的值。

返回类型：text

openGauss=# SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') AS RESULT;
result
-----------
1,2,3,*,5
(1 row)

26.添加操作员综合完整案例

CREATE OR REPLACE PROCEDURE public.xt_addczy(
IN P_HOSPITALID   varchar(100), --组织机构ID
   IN P_CZYBM   varchar(50),--操作员编码
   IN P_CZYID varchar(50),
   IN P_CZYMC varchar(50),
   IN P_PASS varchar(50),
   IN P_JSID varchar(32),--角色
   IN P_CREATERY varchar(20),--创建人
   IN P_KSID   varchar(8000),--操作员所在科室id
   IN P_CZYZT varchar(20),--操作员状态；启用，禁用
   IN P_BZ varchar(200),
   out P_li_code int, -- 成功错误代码
   out P_ls_message varchar(200) --错误信息

)
AS    DECLARE
   P_ls_XH integer;
   P_ls_count int;
P_ls_rowcount int; --行数
P_i int; --取charindex时，定义
   P_index int;
   P_char_len int;
   lists VARCHAR[];
   P_item varchar(100);

begin
   SET autocommit =OFF; --如果不关闭自动提交，则执行插入或更新会立即提交
   START TRANSACTION; --开启事务
--if P_KSID <> '' then
   -- P_KSID :=P_KSID+',' ;--用于，分隔循环获取
--end if;
lists = string_to_array(P_KSID,',',null);

--判断操作员账号是否存在
   select P_ls_rowcount=COUNT(*) from QX_CZYXX
   where CZYBM=P_CZYBM and HOSPITALID=P_HOSPITALID and SYSID='TJXT' and deleted='0';
   if P_ls_rowcount > 0 then
       P_li_code := -2;
       P_ls_message := '操作员账号或工号已存在';
       raise exception '%',P_ls_message ;
   end if;
   select P_ls_jcount=COUNT(*) from QX_CZYJS where CZYID=P_CZYID and HOSPITALID=P_HOSPITALID ;
   if P_ls_jcount > 0 then
       P_li_code := -2;
       P_ls_message := '操作员账号角色已存在';
       raise exception '%',P_ls_message ;
   end if;
   select P_ls_count=COUNT(*) from QX_XTJSXX where JSID=P_JSID and HOSPITALID=P_HOSPITALID and SYSID='TJXT';
   if P_ls_count <= 0 then
       P_li_code := -2;
       P_ls_message := '角色不存在';
       raise exception '%',P_ls_message ;
   end if;

   select P_ls_XH=XH from QX_CZYXX where HOSPITALID=P_HOSPITALID and SYSID='TJXT' ORDER BY XH DESC limit 1;
   if COALESCE(P_ls_XH,0)=0 then
       P_ls_XH:=1;
   ELSE
       P_ls_XH :=P_ls_XH+1;
   END if;

   insert into QX_CZYXX(HOSPITALID,CZYID,CZYMC,CZYBM,PASS,BZ,CZYZT ,XH,SYSID,xtyhbs,deleted)
               VALUES(P_HOSPITALID,P_CZYID,P_CZYMC,P_CZYBM,P_PASS,P_BZ,P_CZYZT,P_ls_XH,'TJXT','0','0');
   get diagnostics P_ls_rowcount := ROW_COUNT ;
   if P_ls_rowcount = 0 then
           P_li_code := -4;
           P_ls_message := '新增操作员失败。'   ;

raise exception '%',P_ls_message ;

   end   if;

   insert into QX_CZYJS(HOSPITALID,CZYID,JSID,CREATESJ,CREATERY,czyjsid)
               VALUES(P_HOSPITALID,P_CZYID,P_JSID,current_timestamp,P_CREATERY,REPLACE(new_guid(), '-', '') );
   get diagnostics P_ls_rowcount := ROW_COUNT ;
   if P_P_ROWCOUNT = 0 then
       P_li_code := -4;
       P_ls_message := '新增操作员角色失败。'   ;

raise exception '%',P_ls_message ;
end if;

   if P_ksid<>'' then
       P_i:=0 ;

       WHILE 1=1 LOOP

       P_item = lists[P_i];
           if COALESCE(P_item,'-1')='-1' then
           EXIT;
           else
               select COUNT(*) into P_ls_count from pe_xtcs_fjks where fjksid=P_item;
               if P_ls_count <= 0 then
                   P_li_code := -2 ;
                   P_ls_message := '科室不存在';
                   raise exception '%',P_ls_message ;
               end if;

               insert into pe_xtcs_CZYfjks(CZYFJKSID,FJKSID,CZYID,CREATERY,CREATESJ)
                           VALUES(REPLACE(new_guid(), '-', ''),P_item,P_CZYID,P_CREATERY,current_timestamp);
               get diagnostics P_ls_rowcount := ROW_COUNT ;
               if P_ls_rowcount = 0 then
                   P_li_code := -4;
                   P_ls_message := '新增操作员分检科室失败。';
                   raise exception '%',P_ls_message ;
               end   if;

end if;
P_i := P_i +1;

END LOOP;
end if;

   commit;

   P_li_code := 0;
   P_ls_message := '新增操作员成功。';

EXCEPTION
WHEN OTHERS THEN
   P_li_code = -4;
P_ls_message='错误:第'+to_char(PG_EXCEPTION_HINT)+'行,内容:'+MESSAGE_TEXT+' 详细:'+PG_EXCEPTION_DETAIL;
rollback;

end;