1.创建存储过程
--1、使用gsql登录openGauss
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#
--2、创建表
omm=# create table item(c1 int, c2 int);
CREATE TABLE
--3、创建存储过程
omm=# create procedure procedure_insert(a int, b int)
omm-# as
omm$# begin
omm$# insert into item values(a, b);
omm$# end;
omm$# /
CREATE PROCEDURE
--4、查看存储过程
omm=# select * from pg_proc where proname = 'procedure_insert';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | pr
oleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proa
rgnames | proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable |
propackage | prokind
------------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+---
-----------+-------------+-----------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-----
--------+----------------+----------------------------------------+--------+-----------+--------+------------------+------------+--------------+
------------+---------
procedure_insert | 2200 | 10 | 11750 | 100 | 0 | 0 | - | f | f | f | f
| | | | | | | | | |
| f | f | v | 2 | 0 | 2278 | 23 23 | | | {a,b
} | | DECLARE +| | | | | f | f |
f | p
| | | | | | | | | | |
| | | | | | | | | | |
| | begin +| | | | | | |
|
| | insert into item values(a, b);+| | | | | | |
|
| | | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | end | | | | | | |
|
(1 row)
2.调用存储过程
--1、调用存储过程
omm=# select procedure_insert(1,100);
procedure_insert
------------------
(1 row)
--2、查询表
omm=# select * from item;
c1 | c2
----+-----
1 | 100
(1 row)
3.删除存储过程
omm=# drop procedure if exists procedure_insert;
DROP PROCEDURE
4.创建函数
--1、创建一个函数func_add_sql，计算两个整数的和，并返回结果
omm$# BEGIN
CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
omm-# AS
omm$# RETURN num1 + num2;
omm$# END;
omm$# /
CREATE FUNCTION
--2、查看函数定义
omm=# \sf func_add_sql
CREATE OR REPLACE FUNCTION public.func_add_sql(num1 integer, num2 integer)
RETURNS integer
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
BEGIN
RETURN num1 + num2;
END$function$;
omm=# select * from pg_proc where proname = 'func_add_sql';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | prolea
kproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargna
mes | proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind
--------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+-------
-------+-------------+-----------+-------------+----------+-----------------+------------+-------------+----------------+-------------+---------
----+----------------+---------------------+--------+-----------+--------+------------------+------------+--------------+------------+---------
func_add_sql | 2200 | 10 | 11750 | 100 | 0 | 0 | - | f | f | f | f
| f | f | v | 2 | 0 | 23 | 23 23 | | | {num1,nu
m2} | | DECLARE +| | | | | f | f | f | f
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
| | BEGIN +| | | | | | | |
| | | | | | | | | |
| | END | | | | | | | |
(1 row)
| | | | | | | | | |
| | RETURN num1 + num2;+| | | | | | | |
| | | | | | | | | | |
omm=#
--3、按参数值传递。
omm=# CALL func_add_sql(1, 3);
func_add_sql
--------------
4
(1 row)
--4、使用命名标记法传参。
omm=# CALL func_add_sql(num1 => 1,num2 => 3);
func_add_sql
--------------
4
(1 row)
omm=# CALL func_add_sql(num2 := 2, num1 := 3);
func_add_sql
--------------
5
(1 row)
--5、创建带出参的函数
omm=# CREATE FUNCTION func_increment_sql(num1 IN integer, num2 IN integer, res OUT integer)
omm-# RETURN integer
omm-# AS
omm$# BEGIN
omm$# omm$# /res := num1 + num2;
omm$# END;
CREATE FUNCTION
--6、出参传入常量
omm=# CALL func_increment_sql(1,2,1);
res
-----
3
(1 row)
--7、创建返回RECORD类型的函数
omm=# CREATE OR REPLACE FUNCTION func_increment_sql1(i int, out result_1 bigint, out result_2
omm(# bigint)
omm-# returns SETOF RECORD
omm-# as $$
omm$# begin
omm$# result_1 = i + 1;
omm$# result_2 = i * 10;
omm$# return next;
omm$# end;
omm$# $$language plpgsql;
CREATE FUNCTION
omm=# call func_increment_sql1(1, 0, 0);
result_1 | result_2
----------+----------
2 | 10
(1 row)
5.修改函数定义
--重命名
omm=# ALTER FUNCTION func_add_sql(INTEGER, INTEGER) RENAME TO add_two_number;
ALTER FUNCTION
6.删除函数
omm=# DROP FUNCTION add_two_number;
DROP FUNCTION
omm=# Drop function func_increment_sql1;
DROP FUNCTION
omm=# Drop function func_increment_sql;
DROP FUNCTION
练习：
课程作业
1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参
CREATE FUNCTION func_1(num1 IN integer, num2 IN integer, res OUT integer)
RETURN integer
AS
BEGIN
res := num1 + num2;
END;
/
omm=# CALL func_1(2,2,1);
res
-----
4
(1 row)
omm=# CALL func_1(num1 => 1,num2 => 1,1);
res
-----
2
(1 row)
omm=# CALL func_1(num1 := 5, num2 := 3,8);
res
-----
8
(1 row)
2.创建返回类型为record的函数2，重命名函数2
omm=# CREATE OR REPLACE FUNCTION func_2(i int, out result_1 bigint, out result_2
omm(# returns SETOF RECORD
omm-# as $$
bigint)
omm-# omm$# begin
omm$# result_1 = i + 1;
omm$# result_2 = i * 10;
omm$# return next;
omm$# end;
omm$# $$language plpgsql;
CREATE FUNCTION
omm=# ALTER FUNCTION func_2(int) RENAME TO func_3;
ALTER FUNCTION
3.使用\sf和系统函数查看函数定义
omm=# \sf func_1
CREATE OR REPLACE FUNCTION public.func_1(num1 integer, num2 integer, OUT res integer)
RETURNS integer
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
BEGIN
res := num1 + num2;
END$function$;
omm=# \sf func_3
CREATE OR REPLACE FUNCTION public.func_3(i integer, OUT result_1 bigint, OUT result_2 bigint)
RETURNS SETOF record
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$
begin
result_1 = i + 1;
result_2 = i * 10;
return next;
end;
$function$;
omm=# select * from pg_proc where proname = 'func_1';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | proleakproo
f | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargnames
| proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind
---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+------------
--+-------------+-----------+-------------+----------+-----------------+------------+-------------+----------------+-------------+--------------
---+----------------+---------------------+--------+-----------+--------+------------------+------------+--------------+------------+---------
func_1 | 2200 | 10 | 11750 | 100 | 0 | 0 | - | f | f | f | f
| f | f | v | 2 | 0 | 23 | 23 23 | {23,23,23} | {i,i,o} | {num1,num2,re
s} | | DECLARE +| | | | | f | f | f | f
| | | | | | | | | | |
| | | | | | | | | |
| | BEGIN +| | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
| | res := num1 + num2;+| | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
| | END | | | | | | | |
(1 row)
omm=# select * from pg_proc where proname = 'func_3';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | proleakproo
f | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargna
mes | proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propackage | proki
nd
---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+------------
--+-------------+-----------+-------------+----------+-----------------+------------+-------------+----------------+-------------+--------------
---------+----------------+--------------------+--------+-----------+--------+------------------+------------+--------------+------------+------
---
func_3 | 2200 | 10 | 11750 | 100 | 1000 | 0 | - | f | f | f | f
| f | t | v | 1 | 0 | 2249 | 23 | {23,20,20} | {i,o,o} | {i,result_1,r
esult_2} | | +| | | | | f | f | f | f
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
| | begin +| | | | | | | |
| | result_1 = i + 1; +| | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
--More-- | | | | | | | | | |
| | result_2 = i * 10;+| | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
| | return next; +| | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
| | end; +| | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
(1 row)
4.删除函数
omm=# DROP FUNCTION FUNC_1;
DROP FUNCTION
omm=# DROP FUNCTION FUNC_3;
DROP FUNCTION
最后修改时间：2021-12-15 22:25:08
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
