泰涨知识 | 【openGauss详解】存储过程的应用

泰克Tech

【openGauss】数据库

存储过程的应用




一、

存储过程




存储过程是一组可以完成特定功能的SQL语句集合，经编译后存储在数据库中。存储过程的执行效率比逐条执行的SQL语句高很多，因为普通的SQL语句，每次都会对SQL进行解析、编译、执行，而存储过程只是在第一次执行时进行解析、编译、执行，以后都是对结果进行调用。




存储过程解析工作就是在SOL引擎模块中完成。




1

存储过程语法如下：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




OR REPLACE：存储过程没有alter procedure，但可以使用or replace替换原存储过程，create or replace当存储过程不存在时做创建，当存储过程存在时做替换。




DEFINER = user：用于指定当前存储过程定义者，默认DEFINER=CURRENT_USER。




argmode：参数模式，取值为：IN（输入参数）、OUT（输出参数）、INOUT（输入输出参数），默认是IN。




{ DEFAULT | := | = } ：可以给输入参数设置默认值，DEFAULT、:=、=作用是一样的。




IS、AS：语法格式要求，必须写其中一个。两个相同。




BEGIN、END：语法格式要求，必须写。




2

存储过程实例：




实例1：创建存储过程

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例2：调用存储过程

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




虽然第二个参数是输出参数，但是在调用存储过程的时候也需要传值，否则会报错。

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




正常调用执行结果如下所示：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例3：删除存储过程

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例4：在存储过程中执行判断




在存储过程编写过程中可能会出现需要根据不同情况执行不同操作的情况，那么此时可以使用流程控制即判断来实现，openGauss数据库支持if…else if…else…end if也支持case when…then…end case完成流程控制。




①　if…elif…else

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）

使用了几次if最终判断结束的时候就需要几个end if。




②　if…else if…else

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




使用了几次if最终判断结束的时候就需要几个end if，因为这个是if嵌套。




③　case when…then…end case

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例5：在存储过程中执行循环




在存储过程中如果想要分别获取数据集中的数据，此时就需要循环遍历数据集，或需要重复执行相同操作也可以使用循环实现，openGauss数据支持的循环有for、while、loop。

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




①　for循环

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）

startnum … endnum相当于生成了一个startnum开始到endnum结束的整数序列，如果startnum=1，endnum=10，那么此时整数序列为1-10（包含10），一共循环10次，i的取值分别是1,2,3,4,5,6,7,8,9,10。




②　while循环

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




③　loop循环

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例6：使用存储过程从表中查询一个具体值




当确定查询的内容仅一行一列时，可以将该单元格的数据赋值给输出参数进行输出。




①　创建表并插入数据

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




②　创建存储过程

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




③　调用存储过程

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




查询结果如下：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例7：使用存储过程查询表数据




当查询一个单元格的数据是可以使用输出参数，但当查询多行/多列/多行多列时需要借助于循环进行遍历。可以使用游标也可以不使用




①　不使用游标

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




raise notice表示已notice日志的形式输出数据，输出字符串。




'%，%'就是要输出的字符串，%为占位符，字符串后的第一个值传给第一个占位符，依次赋值。

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




②　使用游标，for遍历游标

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）

for会自动打开并关闭游标，不需要用户操作游标的打开、取值、关闭。




③　使用游标，按游标操作使用

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）







END




更多精彩内容 尽在泰克教育

请持续关注