暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

9个MogDB存储过程示例

590

存储过程是一组结构化的查询和语句,例如控制语句和声明。这里介绍9个在不同情况下很有用的存储过程示例。

创建测试表:

create table public.test1(id int,name varchar(10));

1. 使用存储过程插入数据

CREATE OR REPLACE PROCEDURE genre_insert_data(GenreId integer, Name character varying) AS begin INSERT INTO public.test1 VALUES (GenreId, Name); end;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE genre_insert_data(GenreId integer, Name character varying) openGauss-# AS openGauss$# begin openGauss$# INSERT INTO public.test1 VALUES (GenreId, Name); openGauss$# end; openGauss$# / CREATE PROCEDURE openGauss=# call genre_insert_data(1,'aaa'); genre_insert_data ------------------- (1 row) openGauss=# select * from test1; id | name ----+------ 1 | aaa (1 row)

2. 在屏幕上显示消息

CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT) AS BEGIN RAISE NOTICE 'Procedure Parameter: %', msg ; END ;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT) openGauss-# AS openGauss$# BEGIN openGauss$# RAISE NOTICE 'Procedure Parameter: %', msg ; openGauss$# END ; openGauss$# / CREATE PROCEDURE openGauss=# call display_message('test!!!'); NOTICE: Procedure Parameter: test!!! msg --------- test!!! (1 row)

3.使用事务控制

CREATE OR REPLACE PROCEDURE control_transaction() AS DECLARE BEGIN CREATE TABLE test2 (id int); INSERT INTO test2 VALUES (1); COMMIT; CREATE TABLE test3 (id int); INSERT INTO test2 VALUES (1); ROLLBACK; END;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE control_transaction() openGauss-# AS openGauss$# DECLARE openGauss$# BEGIN openGauss$# CREATE TABLE test2 (id int); openGauss$# INSERT INTO test2 VALUES (1); openGauss$# COMMIT; openGauss$# CREATE TABLE test3 (id int); openGauss$# INSERT INTO test2 VALUES (1); openGauss$# ROLLBACK; openGauss$# END; openGauss$# / CREATE PROCEDURE openGauss=# select * from test2; ERROR: relation "test2" does not exist on dn_6001 LINE 1: select * from test2; ^ openGauss=# call control_transaction(); control_transaction --------------------- (1 row) openGauss=# select * from test2; id ---- 1 (1 row) openGauss=# select * from test3; ERROR: relation "test3" does not exist on dn_6001 LINE 1: select * from test3; ^

在这里我们可以看到提交之前的数据是可用的,但是没有提交和回滚的数据会从数据库中删除。

4.使用列数据类型

CREATE OR REPLACE PROCEDURE genre_id_max() AS DECLARE Genreid test1.Id%type; BEGIN select max(Id) into Genreid from public.test1; RAISE NOTICE 'Maximum of GenreId is : %', Genreid ; END;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE genre_id_max() AS openGauss$# DECLARE openGauss$# Genreid test1.Id%type; openGauss$# BEGIN openGauss$# select max(Id) into Genreid from public.test1; openGauss$# RAISE NOTICE 'Maximum of GenreId is : %', Genreid ; openGauss$# END; openGauss$# / CREATE PROCEDURE openGauss=# call genre_id_max(); NOTICE: Maximum of GenreId is : 1 genre_id_max -------------- (1 row)

5. 发出NOTICE、WARING和 INFO 消息

CREATE OR REPLACE PROCEDURE raise_warning() AS DECLARE warn INT := 10; BEGIN RAISE NOTICE 'value of warn : % at %: ', warn, now(); warn := warn + 10; RAISE WARNING 'value of warn : % at %: ', warn, now(); warn := warn + 10; RAISE INFO 'value of warn : % at %: ', warn, now(); END;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE raise_warning() AS openGauss$# DECLARE openGauss$# warn INT := 10; openGauss$# BEGIN openGauss$# RAISE NOTICE 'value of warn : % at %: ', warn, now(); openGauss$# warn := warn + 10; openGauss$# RAISE WARNING 'value of warn : % at %: ', warn, now(); openGauss$# warn := warn + 10; openGauss$# RAISE INFO 'value of warn : % at %: ', warn, now(); openGauss$# END; openGauss$# / CREATE PROCEDURE openGauss=# call raise_warning(); NOTICE: value of warn : 10 at 2022-05-07 14:35:24.810364+08: WARNING: value of warn : 20 at 2022-05-07 14:35:24.810364+08: INFO: value of warn : 30 at 2022-05-07 14:35:24.810364+08: raise_warning --------------- (1 row)

6. 引发异常

CREATE OR REPLACE PROCEDURE genre_id_exception() AS DECLARE Genreid test1.Id%type ; BEGIN select max(Id) into Genreid from public.test1; RAISE EXCEPTION 'Maximum of GenreId is : %', Genreid USING HINT = 'Test For Raising exception.'; END;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE genre_id_exception() AS openGauss$# DECLARE openGauss$# Genreid test1.Id%type ; openGauss$# BEGIN openGauss$# select max(Id) into Genreid from public.test1; openGauss$# RAISE EXCEPTION 'Maximum of GenreId is : %', Genreid USING HINT = 'Test For Raising exception.'; openGauss$# END; openGauss$# / CREATE PROCEDURE openGauss=# call genre_id_exception(); ERROR: Maximum of GenreId is : 1

7. 使用 FOR 循环遍历表中的数据

CREATE OR REPLACE PROCEDURE genre_traverse() AS DECLARE genre_rec record; BEGIN for genre_rec in (select Id,Name from public.test1 order by 1) loop RAISE NOTICE 'Id is : % , Name is : %', genre_rec.Id,genre_rec.Name; end loop; END;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE genre_traverse() AS openGauss$# DECLARE openGauss$# genre_rec record; openGauss$# BEGIN openGauss$# for genre_rec in (select Id,Name from public.test1 order by 1) openGauss$# loop openGauss$# RAISE NOTICE 'Id is : % , Name is : %', genre_rec.Id,genre_rec.Name; openGauss$# end loop; openGauss$# END; openGauss$# / CREATE PROCEDURE openGauss=# call genre_traverse(); NOTICE: Id is : 1 , Name is : aaa NOTICE: Id is : 1 , Name is : <NULL> genre_traverse ---------------- (1 row)

8. 使用 SECURITY INVOKER

SECURITY INVOKER 指示该过程将以调用它的用户的权限执行。这是默认设置。

CREATE OR REPLACE PROCEDURE genre_traverse() SECURITY INVOKER AS DECLARE genre_rec record; BEGIN for genre_rec in (select Id,Name from public.test1 order by 1) loop RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.Id,genre_rec.Name; end loop; END;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE genre_traverse() SECURITY INVOKER openGauss-# AS openGauss$# DECLARE openGauss$# genre_rec record; openGauss$# BEGIN openGauss$# for genre_rec in (select Id,Name from public.test1 order by 1) openGauss$# loop openGauss$# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.Id,genre_rec.Name; openGauss$# end loop; openGauss$# END; openGauss$# / CREATE PROCEDURE openGauss=# \c - test Password for user test: Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "postgres" as user "test". openGauss=> call genre_traverse(); ERROR: permission denied for relation test1 DETAIL: N/A CONTEXT: PL/pgSQL function genre_traverse() line 4 at FOR over SELECT rows

9. 使用SECURITY DEFINER

SECURITY DEFINER 指定该过程将以拥有它的用户的权限执行。SECURITY DEFINER 过程不能执行事务控制语句(例如,COMMIT 和 ROLLBACK,取决于语言)。

在此示例中,我们使用用户“postgres”创建了一个存储过程,并使用无权访问该表的“test”用户调用它。

CREATE OR REPLACE PROCEDURE genre_traverse() SECURITY DEFINER AS DECLARE genre_rec record; BEGIN for genre_rec in (select Id,Name from public.test1 order by 1) loop RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.Id,genre_rec.Name; end loop; END;

测试:

openGauss=# CREATE OR REPLACE PROCEDURE genre_traverse() SECURITY DEFINER openGauss-# AS openGauss$# DECLARE openGauss$# genre_rec record; openGauss$# BEGIN openGauss$# for genre_rec in (select Id,Name from public.test1 order by 1) openGauss$# loop openGauss$# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.Id,genre_rec.Name; openGauss$# end loop; openGauss$# END; openGauss$# / CREATE PROCEDURE openGauss=# \c - test Password for user test: FATAL: Invalid username/password,login denied. Previous connection kept openGauss=# \c - test Password for user test: Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "postgres" as user "test". openGauss=> call genre_traverse(); NOTICE: Genre Id is : 1 , Name is : aaa NOTICE: Genre Id is : 1 , Name is : <NULL> genre_traverse ---------------- (1 row)
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论