当前位置：首页 > article >正文

openGauss开源数据库实战十五

article 2024/11/5 4:54:58

文章目录

任务十五 openGauss逻辑结构:存储过程和函数管理
- 预备知识:存储过程和函数简介
- 任务目标
- 实施步骤
- - 一、创建存储过程
  - 二、查看存储过程的定义
  - 三、调用存储过程
  - 四、删除存储过程
  - 五、使用DECLARE语句声明变量variable
  - 六、复合SQL语句的控制结构
  - - 1.语句块 BEGIN…END
    - 2.分支语句
    - 3.循环语句
  - 七、空语句
  - 八、存储过程中的游标cursor
  - - 1.定义游标
    - 2.打开和关闭游标
    - 3.从游标中获取数据
  - 九、函数
  - - 1.创建函数
    - 2.调用函数
    - 3.删除函数
  - 十、修改存储过程和函数
  - 十二、查看某个数据库的某个模式下有哪些存储过程和函数
  - 十三、清理工作

任务十五 openGauss逻辑结构:存储过程和函数管理

预备知识:存储过程和函数简介

存储过程(Stored Procedure)是一组用于完成特定功能的SQL语句集,经编译后存储在数据库中。存储过程具有如下的优点:
1)增强了SQL语言的功能。在存储过程中,可以使用顺序语句、循环和分支控制语句,因此灵活性强,可以实现更为复杂的应用逻辑。
2)标准组件式编程。创建完存储过程后,可以在程序中多次调用该存储过程。对存储过程的修改可以由数据库专业人员进行,应用程序不需要做任何修改。
3)更快的执行速度。因为存储过程是预编译的,并进行了优化,其执行计划保存在数据库管理系统的数据字典中,每次执行时,只需要为执行计划提供参数就可以执行,省略了编译和优化的过程。
4)减少网络流量。在客户计算机上调用存储过程时,网络中只传送调用该存储过程的语句,并不需要传送该存储过程本身,因而能大大减少网络流量,降低了网络负载。
5)更好的安全控制机制。通过对执行某一存储过程的权限进行限制,能够实现对相应数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
函数与存储过程类似,它们之间的主要区别在于是否有返回值:存储过程不返回值,函数会返回一个值。

任务目标

掌握openGaussDBMS中存储过程和函数的管理。

实施步骤

一、创建存储过程

openGauss中使用CREATEPROCEDURE语句创建存储过程,语法如下:

CREATE OR REPLACE PROCEDURE
StoredProcedureName([[IN|OUT|INOUT]参数名 数据类型
[, [IN|OUT|INOUT]参数名 数据类型...]])
IS

过程体
其中:
1)参数:存储过程的参数可以有多个,用“,”分割开。每个参数可以是IN、OUT、INOUT类型。
①IN:必须在调用存储过程时指定该类型的参数,在存储过程中修改该类型的参数,值不能被返回,参数的默认类型为IN。
②OUT:在存储过程中修改该类型的参数,值可以被返回。
③INOUT:必须在调用存储过程时指定该类型的参数,在存储过程中修改该类型的参数,值可以被返回。
2)IS也可以用AS替代。
3)过程体:过程体的开始与结束使用BEGIN与END进行标识。
使用Linux用户omm,打开一个Linux终端窗口,执行下面的命令和SOL语句,使用用户student连接openGauss的数据库studentdb,创建一个名为myproc的存储过程:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

CREATE OR REPLACE PROCEDURE myproc(OUT s int)
IS
BEGIN
      SELECT COUNT(*) INTO s FROM student;
END
/

执行下面的SQL语句,也可以创建一个名为myproc的存储过程:

CREATE OR REPLACE PROCEDURE myproc(OUT s int)
AS
BEGIN
      SELECT COUNT(*) INTO s FROM student;
END
/

在创建存储过程时,不论是使用关键字1S还是使用关键字AS,效果是一样的

二、查看存储过程的定义

在gsql中执行下面的元命令,查看存储过程myproc的定义:

\sf myproc

三、调用存储过程

可以使用CALL语句来调用指定的存储过程:

call myproc(:studentNum);

四、删除存储过程

执行下面的SOL语句,删除存储过程myproc:

drop procedure myproc;

五、使用DECLARE语句声明变量variable

在存储过程和函数中,声明变量的语法格式如下:
DECLARE var1[,var2…] datat_type [DEFAULT value];
其中,datat_type是openGauss数据库支持的数据类型;如果没有DEFAULT子句,则变量的初始值为NULL。
执行下面的SQL语句,创建存储过程testproc,测试为存储过程定义变量:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc(OUT s1 int,OUT s2 int)
AS
DECLARE
    var1 int;
    var2 int default 0;
BEGIN
      var1:=5;
      var2:=10;
      s1:=var1;
      s2:=var2;
END
/

执行下面的SQL语句,调用刚刚创建的存储过程testproc:

-- 调用存储过程testproc
call testproc(:t1,:t2);

六、复合SQL语句的控制结构

1.语句块 BEGIN…END

语句块的语法格式1:

BEGIN
	statement-list
END:

语句块的语法格式2:

<<Label>>
	BEGIN
		statement-list
	END;

这两者之间的区别:语句块的语法格式2为语句块定义了一个标号。
下面的例子中,使用这两种语法格式,创建一个存储过程:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc()
IS
BEGIN
   -- 语句块的例子1
      BEGIN
        SELECT * FROM student;
      END;

   -- 语句块的例子2：带标号
   << showInstrictor >> 
      BEGIN
        SELECT * FROM instructor;
      END;
END
/

2.分支语句

(1)IF语句 IF语句的语法格式如下:

IF(expr1)THEN
	statement list1
ELSEIF(eXPr2)THEN
	statement list2
ELSE
	statement list3
END IF

下面是创建存储过程的一个例子,存储过程使用了IF语句:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc(IN s int)
IS
    BEGIN
        IF(s=1) THEN
           raise info 'Input is 1';
        ELSEIF(s=2) THEN 
           raise info 'Input is 2';
        ELSE
           raise info 'Input is s:%',s;
        END IF;
    END
/

执行下面的调用语句,测试刚刚创建的存储过程:

call testproc(1);
call testproc(2);
call testproc(3);

(2)CASE语句 CASE语句的第1种语法格式:

CASE expr
	WHEN value1 THEN statement list
	WHEN value2 THEN statement_list
	WHEN valueN THEN statement list
	ELSE statement list
END CASE;

下面是创建存储过程的一个例子,存储过程使用了CASE语句的第1种语法:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc(IN s int)
IS
    BEGIN
        CASE  s
           WHEN 1 THEN
               raise info 'Input is 1';
           WHEN 2 THEN
               raise info 'Input is 1';
            ELSE
               raise info 'Input is not 1 or 2';
        END CASE;
    END
/

执行下面的调用语句,测试刚刚创建的存储过程:

call testproc(1);
call testproc(2);
call testproc(3);

CASE语句的第2种语法格式:

CASE
	WHEN expr_condition1 THEN statement list
	WHEN expr condition2 THEN statement list
	WHEN expr conditionN THEN statement list
	ELSE statement list
END CASE:

下面是创建存储过程的一个例子,存储过程使用了CASE语句的第2种语法:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc(IN s int)
IS
    BEGIN
        CASE 
           WHEN s=1 THEN 
               raise info 'Input is 1';
           WHEN s=2 THEN
               raise info 'Input is 2';
           ELSE
               raise info 'Input is not 1 or 2';
        END CASE;
    END
/

执行下面的调用语句,测试刚刚创建的存储过程:

call testproc(1);
call testproc(2);
call testproc(3);

(3)GOTO语句GOTO语句可以无条件跳转到指定标号的位置开始执行。标号的定义方法如下:假如我们想知道,从1开始求和,到哪个整数开始和刚刚超过4000。下面是完成这个任务的存储过程:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc()
AS 
DECLARE
    v1  int;
    S   int;
BEGIN
    v1  := 0;
    S  := 0;
        LOOP
        EXIT WHEN v1 >= 100;
                S  := S+v1;
                v1 := v1 + 1;
                if S > 4000 THEN
                   GOTO pos1;
                END IF;
        END LOOP;
<<pos1>>
v1:=v1-1;
raise info 'v1 is %. ', v1;
raise info 'S  is %. ', S;
END;
/

执行下面的调用语句,测试刚刚创建的存储过程:

call testproc();

3.循环语句

(1)LOOP语句LOOP语句的语法格式为:

LOOP
	statement list
ENDLOOP

EXIT语句用来退出LOOP,但只能出现在带有给定标签的构造的内部。EXIT语句的语法格式为:

LEAVE Iable_name

下面是创建存储过程的一个例子,存储过程使用了LOOP语句和EXIT语句,会根据调用的输人参数n,显示n次:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc(IN s int)
IS
    -- 声明变量
    DECLARE var1 int;
BEGIN
      var1:=1;
      LOOP 
               raise info 'temp';
               var1:=var1+1;
               IF(var1>s) THEN
                   EXIT;
               END IF;
      END LOOP;
END
/

执行下面的调用语句,测试刚刚创建的存储过程:

call testproc(1);
call testproc(2);
call testproc(3);

(2)WHILE-LOOP语句WHILE语句的语法格式为:

WHILE eXPr LOOP
	statement_ list
ENDLOOP

这条语句用来创建一个执行循环。当表达式cxpr的值为真时,循环内的语句将反复执行。下面是创建存储过程的一个例子,存储过程使用了WHILE-LOOP语句,会根据调用的输入参数n,显示n次:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc(IN s int)
IS
    -- 声明变量
    DECLARE var1 int;
    BEGIN
      var1 :=1;
      WHILE (var1<=s) LOOP
        raise info 'temp';
        var1:=var1+1;
      END LOOP;
    END
/

执行下面的调用语句,测试刚刚创建的存储过程:

call testproc(2);
call testproc(5);

(3)FOR-LOOP(integer变量)语句FOR-LOOP(integer变量)语句的语法格式为:

FOR varName IN [REVERSE]Iow_bound..upper_bound BY step LOOP
	statements
END LOOP;

这条语句用来创建一个执行循环。varName的值必须在下限值low_bound和上限值upper_bound之间。当使用REVERSE时,low_bound的值必须大于或等于upper_bound。循环的每次步长调整值为step
该语句相当于C语言的以下for循环语句:

for (varName=low_bound; varName <= upper_Bound; varName = varName + step) {
	statements
}

下面是创建存储过程的一个例子,存储过程使用了FOR-LOOP(integer变量)语句:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc()
IS
-- 声明变量
DECLARE
    var1 int;
BEGIN
    var1:=8;
    FOR  var1 IN 1..5 BY 1 LOOP
        raise info 'var1=%',var1;
    END LOOP;
END
/

执行下面的调用语句,测试刚刚创建的存储过程:

call testproc();

(4)FOR-LOOP-query语句FOR-LOOP-query语句的语法格式为:

FOR target IN query LOOP
	statements
END LOOP:

这条语句用来创建一个执行循环。target会自动定义,且只在该循环范围内有效。对于查询得到的每个值,都会进行一次循环。
下面是创建存储过程的一个例子。首先创建测试表:

drop table IF exists test CASCADE;
create table test(col int);
insert into test values(1);
insert into test values(3);
insert into test values(5);

执行下面的语句,创建存储过程,存储过程使用了FOR-LOOP-query语句:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc()
IS
BEGIN
    FOR target IN SELECT col From test LOOP
        raise info 'target=%',target;
    END LOOP;
END
/

执行下面的调用语句,测试刚刚创建的存储过程:

call testproc();

(5)FORALL-DML语句FORALL-DML语句的语法格式为:

FORALL index IN [REVERSE] Iow_bound..upper bound DML;

其中的index会自动定义,且只在该循环范围内有效。
该语句相当于C语言的以下for循环语句:

for(index = low_bound; index <= upper_Bound; index = index + 1) {
	DML
}

下面是创建存储过程的一个例子。首先创建测试表:

drop table IF exists test;
create table test(col int);
insert into test values(1);
insert into test values(2);
insert into test values(3);

执行下面的语句,创建存储过程,存储过程使用了FORALL-DML语句:

-- 创建存储过程testproc
CREATE OR REPLACE PROCEDURE testproc()
IS
BEGIN
    FORALL i IN 5..7
        update test set col=col+i;
END
/

这个存储过程需要循环3次(变量i的值分别等于5、6、7),每次都要用变量i的值来更新表的值,其新值等于旧值加上i的值。
执行刚刚创建的存储过程:

call testproc();

查看执行结果:

select * from test;

七、空语句

在PL/SQL程序中,可以用NULL语句来说明“不用做任何事情”,相当于一个占位符,可以
使某些语句变得有意义,提高程序的可读性。示例如下:

DECLARE
    …
BEGIN
    …
    IF v_num IS NULL THEN
        NULL; -- 不需要处理任何数据。
    END IF;
END;
/

八、存储过程中的游标cursor

查询语句可能返同多条记录(集合),由于在存储过程和储存函数的代码中一次只能处理一条记录,因此需要使用游标cursor来逐条读取查询结果集中的记录。
使用游标前首先需要定义游标,然后才可以打开、使用和关闭这个定义的游标。游标必须在声明处理程序之前被定义,并且变量和条件还必须在定义游标或声明处理程序之前被声明。

1.定义游标

openGauss定义游标的语法如下:

CURSOR cursor_name
[BINARY][NO SCROLL[ {WITH|WITHOUT} HOLD]
FOR query

其中,cursor_name是游标的名字;查询语句query用于创建游标的结果集。
执行下面的语句,创建一个定义了游标的存储过程:

CREATE OR REPLACE PROCEDURE p_testCursor(OUT s1 int,OUT s2 int)
IS
DECLARE
--     定义游标
     CURSOR myCur FOR
        select * from student;
BEGIN

END
/

2.打开和关闭游标

打开游标相当于执行该游标所对应的查询,从而产生结果集,其语法为:

OPEN cursor_name;

游标使用完毕后,需要关闭游标,回收相关的计算机资源,其语法为:

CLOSE cursor_name;

执行下面的语句,创建一个存储过程,在其中定义了一个游标,并在存储过程中打开和关闭该游标:

CREATE OR REPLACE PROCEDURE p_testCursor(OUT s1 int,OUT s2 int)
IS
DECLARE
     -- 定义游标 
     CURSOR myCur FOR
          select * from student;
BEGIN
     -- 打开游标 
      OPEN myCur;
     -- 关闭游标 
      CLOSE myCur;
END
/

3.从游标中获取数据

从游标中获取数据的语法如下:

FETCH cursor name INTO var1, [var2,…,varN]

其中,cursor_name是游标的名字。打开游标会执行该游标中的SELECT查询语句,获得一个查询结果集。执行FETCH语句将获取结果集中由游标所指的那一行数据,保存到变量var1,var2,…,varN上。
执行下面的语句,创建一个存储过程,在其中定义了一个游标,并在存储过程中打开该游标,从中获取数据:

CREATE OR REPLACE PROCEDURE p_testCursor(OUT studentId varchar(5), 
OUT studentDeptName varchar(20),
OUT studentName varchar(20),
OUT studentCredit decimal(3,0) )
IS
DECLARE
    -- 定义游标 
       CURSOR myCur FOR
       select * from student;
    -- 定义变量，获取游标数据 
       stuId varchar(5);
       deptName varchar(20);
       name varchar(20);
       totalCred decimal(3,0);
BEGIN
     -- 打开游标 
      OPEN myCur;
     -- 从游标中获取数据
     FETCH myCur INTO stuId,deptName,name,totalCred; 
     -- 从存储过程返回数据 
     studentID:=stuId;
     studentDeptName:=deptName;
     studentName:=name;
     studentCredit:=totalCred;
     -- 关闭游标 
      CLOSE myCur;
END
/

调用刚刚创建的存储过程,使用游标获取数据:

call p_testCursor(:stuid,:studept,:stuname,:stucredit);

九、函数

1.创建函数

使用CREATEFUNCTION语句来创建函数。
创建一个示例函数:如果输入是正数,返回1;如果输入是负数,返同-1;如果输入是0.返回0

CREATE OR REPLACE FUNCTION myfunction(s INT)
RETURN INT
AS
BEGIN
        IF(s>0) THEN
           RETURN 1;
        ELSEIF(s<0) THEN
           RETURN -1;
        ELSE
           RETURN 0;
        END IF;
END
/

2.调用函数

下面是调用刚刚创建的函数的例子:

-- 调用函数
select myfunction(5),myfunction(-5),myfunction(0);

3.删除函数

执行下面的语句,删除刚刚创建的函数。

drop function myfunction;

十、修改存储过程和函数

要修改存储过程和函数,可以直接使用CREATEOR REPLACE语句,例如:

CREATE OR REPLACE PROCEDURE p_testCursor(OUT s1 int,OUT s2 int)
IS
DECLARE
--     定义游标
     CURSOR myCur FOR
        select * from student;
BEGIN
END
/

十一、错误捕获语句
缺省时,PL/SQL函数在执行过程中发生错误时会退出函数执行,并且周围的事务也会回滚。可以用一个带有EXCEPTION子句的BEGIN块捕获错误并且从中恢复。其语法是正常的BEGIN块语法的一个扩展:

[<<label>>]
[DECLARE
    declarations]
BEGIN
    statements
EXCEPTION
    WHEN condition [OR condition ...] THEN
        handler_statements
    [WHEN condition [OR condition ...] THEN
        handler_statements
    ...]
END;

如果没有发生错误,这种形式的块只是简单地执行所有语句,然后转到END之后的下一个语句。但是如果在执行的语句内部发生了一个错误,则这个语句将会回滚,然后转到EXCEPTION列表。寻找匹配错误的第一个条件。若找到匹配,则执行对应的handler_statements,然后转到END之后的下一个语句。如果没有找到匹配,则会向事务的外层报告错误,和没有EXCEPTION子句一样。也就是说,该错误可以被一个包围块用EXCEPTION子句捕获,如果没有包围块,则进行退出函数处理。
condition的名称可以是用SQL标准错误码编号说明的任意值。特殊的条件名OTHERS匹配除了QUERY_CANCELED之外的所有错误类型。
如果在选中的handler_statements里发生了新错误,则不能被这个EXCEPTION子句捕获,而是向事务的外层报告错误。一个外层的EXCEPTION子句可以捕获它。
如果一个错误被EXCEPTION子句捕获,PL/SQL函数的局部变量保持错误发生时的原值,但是所有该块中想写入数据库中的状态都回滚。
下面的例子取自官方文档:

drop table if exists mytab;
CREATE TABLE mytab(id INT,firstname VARCHAR(20),lastname VARCHAR(20)) ;
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');

CREATE OR REPLACE FUNCTION fun_exp() RETURNS INT
AS $$
DECLARE
    x INT :=0;
    y INT;
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;$$
LANGUAGE plpgsql;

官方例子首先更新了表mytab中的行(将lastname=Jones’行的firstname更新为Joe),但是接下来的执行发生了被0除的异常,于是需要回滚刚才的UPDATE语句,表mytab的数据将保持不变,并引发被0除的异常。在RETURN语句里返回的数值将是x的增量值。下面的执行证明了这一点:

call fun_exp();
select * from mytab;

执行下面的语句,清理测试环境:

DROP FUNCTION fun_exp();
DROP TABLE mytab;

下面是另外一个示例——UPDATE/INSERT异常,也是取自官方文档。这个例子根据使用异常处理器执行恰当的UPDATE或INSERT。

drop table if exists db;
CREATE TABLE db (a INT, b TEXT);
CREATE OR REPLACE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
--第一次尝试更新key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
--不存在，所以尝试插入key，
--如果其他人同时插入相同的key，我们可能得到唯一key失败。
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
        --什么也不做，并且循环尝试再次更新。
        END;
     END LOOP;
END;
$$
LANGUAGE plpgsql;


-- 调用存储过程，第一次插入key=1的记录，名字为david，然后正常退出存储过程
-- 检查表db可以看到插入的记录。
SELECT merge_db(1, 'david');
SELECT * from db;

-- 调用存储过程，第二次插入key=1的记录，引发key相同的异常，该异常啥也不干。
-- 继续在存储过程中执行下次循环，找到key=1的记录，进行更新后退出存储过程。
-- 检查表db可以看到已经对key=1的记录进行更新，名字已经变为dennis。
SELECT merge_db(1, 'dennis');
SELECT * from db;

执行下面的SQL语句,清理测试环境:

--删除FUNCTION和TABLE
DROP FUNCTION merge_db;
DROP TABLE db ;

十二、查看某个数据库的某个模式下有哪些存储过程和函数

执行下面的SQL语句,查看studentdb的public模式下有哪些存储过程和函数:

select routine_name 
from information_schema.routines
where routine_catalog = 'studentdb'
and routine_schema = 'public'
order by routine_name;

十三、清理工作

DROP PROCEDURE fun_exp;
DROP PROCEDURE testproc;
DROP PROCEDURE p_testcursor;

查看全文

http://www.kler.cn/a/379666.html

react18中redux-promise搭配redux-thunk完美简化异步数据操作

《C++最新标准下字符串字面量类型的深度剖析》

AI打造超写实虚拟人物：是科技奇迹还是伦理挑战？

物联网赋能的人工智能图像检测系统

哔哩哔哩车机版2.7.0|专为司机打造的车机版B站，内容丰富，功能齐全

Centos8安装软件失败更换镜像源

企业数据泄露安全演练（分享）

飞牛OS在Docker中安装ODOO ERP系统

书签管理工具使用技巧

Transformer和BERT的区别

Springboot 整合 Java DL4J 实现情感分析系统

SQL 视图：概念、应用与最佳实践

教程：使用 InterBase Express 访问数据库（四）

C++在游戏开发中的应用与实践

[前端面试]计算机网络

C语言案例——青蛙跳台阶问题

js-18-防抖、节流源码以及如何使用

git将本地项目上传到远程空仓库里

k8s中Deployment和StatefulSet两种控制器之间的比较

【华为HCIP实战课程二十八】中间到中间系统协议IS-IS邻居关系排错，网络工程师

网络设置：静态IP与动态IP，何去何从？

Odoo：免费开源的医药流通行业信息化解决方案

视频号带货书籍，一天佣金1200+（附视频教程）

LangGPT结构化提示词编写实践（L1G3000 浦语提示词工程实践）

聊一聊Qt中的按钮

(代码随想录)BEllman_ford算法及其优化 SPFA

文章目录

任务十五 openGauss逻辑结构:存储过程和函数管理

预备知识:存储过程和函数简介

任务目标

实施步骤

一、创建存储过程

二、查看存储过程的定义

三、调用存储过程

四、删除存储过程

五、使用DECLARE语句声明变量variable

六、复合SQL语句的控制结构

1.语句块 BEGIN…END

2.分支语句

3.循环语句

七、空语句

八、存储过程中的游标cursor

1.定义游标

2.打开和关闭游标

3.从游标中获取数据

九、函数

1.创建函数

2.调用函数

3.删除函数

十、修改存储过程和函数

十二、查看某个数据库的某个模式下有哪些存储过程和函数

十三、清理工作

相关文章：