openGauss每日一练第15天 | 存储过程和函数的学习

今天学习openGauss存储过程和函数，对于数据库开发这个功能是比较有份量的，语法看存储过程跟oracle相近，函数有些不一样。

存过语法：https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-PROCEDURE.html

CREATE [ OR REPLACE ] PROCEDURE procedure_name
    [ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ]
    [
       { IMMUTABLE | STABLE | VOLATILE }
       | { SHIPPABLE | NOT SHIPPABLE }
       | {PACKAGE}
       | [ NOT ] LEAKPROOF
       | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
       | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER}
       | COST execution_cost
       | SET configuration_parameter { TO value | = value | FROM CURRENT }
    ][ ... ]
 { IS | AS } 
plsql_body 
/
复制

函数语法：两种风格，贴的是O风格 https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-FUNCTION.html

CREATE [ OR REPLACE  ] FUNCTION function_name 
    ( [  { argname [ argmode  ] argtype [  { DEFAULT | := | =  } expression  ] }  [, ...]  ] )
    RETURN rettype [ DETERMINISTIC  ]
    [ 
        {IMMUTABLE  | STABLE  | VOLATILE } 
        | {SHIPPABLE | NOT SHIPPABLE}
        | {PACKAGE}
        | {FENCED | NOT FENCED}
        | [ NOT  ] LEAKPROOF  
        | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } 
        | {[ EXTERNAL  ] SECURITY INVOKER  | [ EXTERNAL  ] SECURITY DEFINER |
AUTHID DEFINER | AUTHID CURRENT_USER
} 
        | COST execution_cost  
        | ROWS result_rows  
        | SET configuration_parameter { {TO | =} value  | FROM CURRENT
    
    ][...] 
    
    { 
      IS  | AS
} plsql_body
/
复制

学习笔记：

•     存储过程：

1.     查看存过视图：pg_proc;
2.     调用存过：除了原本常规的调用方式，opengauss 还可以使用select 存过；虽然oracle也支持，但两者应该是有区别，oracle不建议在select过程中使用存过，opengauss也只是单纯的select 调用；
3.     删除存过：可以使用if exists关键字，来避免删除无该对象的报错。drop procedure if exists <procedure>;

• 函数：

1. 创建函数：有个$$符号，目前不是很清楚作用，可以不用但不确定是否会引起其他问题；
2. 查看函数：可用 \sf 函数名 或pg_proc
3. 调用函数：call 函数名，传参支持使用命名标记法传参 CALL func_add_sql(num2 := 2, num1 := 3); 或 CALL func_add_sql(num1 => 1,num2 => 3);
4. 返回recode形式：关键语法returns SETOF RECORD
5. 重命名：ALTER FUNCTION [FUNC] () RENAME TO [NEWFUNC];

课后练习：

1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参

openGauss=# create function func(a in integer,b in integer,res out integer) return integer as begin res:=a*b; end;
openGauss$# /
CREATE FUNCTION
openGauss=# \sf func 
CREATE OR REPLACE FUNCTION public.func(a in integer,b in integer,res out integer)
 RETURN integer NOT FENCED NOT SHIPPABLE
AS  DECLARE  begin res:=a*b; end;
/

openGauss=# call func(2,9,c);
 res 
-----
  18
(1 row)

openGauss=# call func(2,9,a);
 res 
-----
  18
(1 row)
openGauss=# call func(a=>2,b=>3,c);
 res 
-----
   6
(1 row)

openGauss=# 
复制

2.创建返回类型为record的函数2，重命名函数2

openGauss=# create function fun2(i int,out r1 int,out r2 int) returns setof record as $$ begin r1=i*2;r2=i*3;return next; end; $$language plpgsql;
/
CREATE FUNCTION
openGauss=# call fun2(1,0,0);
 r1 | r2 
----+----
  2 |  3
(1 row)

openGauss=# 
复制

openGauss=# alter function fun2(int) rename to func2; --注意参数只要入参，出参不用
ALTER FUNCTION
复制

3.使用\sf和系统函数查看函数定义

openGauss=# \sf func2
CREATE OR REPLACE FUNCTION public.func2(i integer, OUT r1 integer, OUT r2 integer)
 RETURNS SETOF record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ begin r1=i*2;r2=i*3;return next; end; $function$;
openGauss=# select * from pg_proc where proname='func2';
 proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | pr
oleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmod
es | proargnames | proargdefaults |                 prosrc                  | probin | proconfig | proacl | prodefaultargpos | fencedmo
de | proshippable | propackage | prokind | proargsrc | propackageid | proisprivate | proargtypesext | prodefaultargposext 
---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+---
-----------+-------------+-----------+-------------+----------+-----------------+------------+-------------+----------------+----------
---+-------------+----------------+-----------------------------------------+--------+-----------+--------+------------------+---------
---+--------------+------------+---------+-----------+--------------+--------------+----------------+---------------------
 func2   |         2200 |       10 |   11900 |     100 |    1000 |           0 | -            | f        | f           | f         | f 
           | f           | t         | v           |        1 |               0 |       2249 | 23          | {23,23,23}     | {i,o,o}  
   | {i,r1,r2}   |                |  begin r1=i*2;r2=i*3;return next; end;  |        |           |        |                  | f       
   | f            | f          | f       |           |            0 | f            |                | 
(1 row)

openGauss=# 
复制

4.删除函数

openGauss=# drop function func;
DROP FUNCTION
openGauss=# drop function func2;
DROP FUNCTION
openGauss=# 
复制