今天学习到函数了，如果有了数据库的基础，掌握起来还是比较容易的，如果没有基础，还真得好好学习一下了，直接上操作练习步骤：
1.登录数据库环境
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
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=# create table item(c1 int, c2 int);
CREATE TABLE
2.创建存储过程
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
3.查看存储过程定义
omm=# select * from pg_proc where proname = 'procedure_insert';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | pr
osecdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargty
pes | proargmodes | proargnames | proargdefaults | prosrc | probin | proconfig | proacl | prodefault
argpos | 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)
4.执行存储过程
select procedure_insert(1,100);
procedure_insert
------------------
(1 row)
omm=# select * from item;
(1 row)
omm=# c1 | c2
----+-----
1 | 100
、
5.删除存储过程
omm=# drop procedure if exists procedure_insert;
DROP PROCEDURE
6.创建函数
omm=# CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
omm-# AS
omm$# BEGIN
omm$# RETURN num1 + num2;
omm$# END;
omm$# /
CREATE FUNCTION
7.执行函数
omm=# \sf func_add_sql
创建函数
omm=# 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$;
查看函数信息
select * from pg_proc where proname = 'func_add_sql';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosec
def | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes
| proargmodes | proargnames | proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpos | fencedmode | p
roshippable | propackage | prokind
--------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-------
----+--------------+-------------+-----------+-------------+----------+-----------------+------------+-------------+----------------
+-------------+-------------+----------------+---------------------+--------+-----------+--------+------------------+------------+--
------------+------------+---------
func_add_sql | 2200 | 10 | 11750 | 100 | 0 | 0 | - | f | f | f
| f | f | f | v | 2 | 0 | 23 | 23 23 |
| | {num1,num2} | | DECLARE +| | | | | f | f
| | | | | | | | |
| | | | BEGIN +| | | | | |
| |
| f | f
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | |
| | | | RETURN num1 + num2;+| | | | | |
| |
| | | | | | | | | |
| | | | | | | | |
| | | | END | | | | | |
| |
(1 row)
执行函数
omm=# CALL func_add_sql(1, 3);
func_add_sql
--------------
4
(1 row)
--使用命名标记法传参。
CALL func_add_sql(num1 => 1,num2 => 3);
func_add_sql
--------------
4
(1 row)
omm=# CALL func_add_sql(num2 := 2, num1 := 3);
omm=# func_add_sql
--------------
5
(1 row)
8.创建函数带有输入输出参数的函数
CREATE FUNCTION func_increment_sql(num1 IN integer, num2 IN integer, res OUT integer)
omm$# omm-# RETURN integer
omm-# AS
omm$# BEGIN
res := num1 + num2;
omm$# END;
omm$# /
CREATE FUNCTION
omm=#
omm=#
omm=# CALL func_increment_sql(1,2,1);
res
-----
3
(1 row)
omm=# CREATE OR REPLACE FUNCTION func_increment_sql1(i int, out result_1 bigint, out result_2
omm(# bigint)
returns SETOF RECORD
omm-# omm-# as $$
omm$# begin
omm$# result_1 = i + 1;
omm$# result_2 = i * 10;
omm$# end;
omm$# return next;
omm$# $$language plpgsql;
CREATE FUNCTION
omm=# call func_increment_sql1(1, 0, 0);
result_1 | result_2
----------+----------
2 | 10
(1 row)
ALTER FUNCTION func_add_sql(INTEGER, INTEGER) RENAME TO add_two_number;
ALTER FUNCTION
9.删除函数
omm=# DROP FUNCTION add_two_number;
omm=# DROP FUNCTION
Drop function func_increment_sql1;
DROP FUNCTION
omm=# Drop function func_increment_sql;
DROP FUNCTION
作业
1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参
omm=# CREATE FUNCTION function1(num1 IN integer, num2 IN integer, res OUT integer)
omm-# RETURN integer
omm$# BEGIN
omm-# AS
omm$# res := num1 + num2;
omm$# END;
omm$# /
CREATE FUNCTION
omm=# CALL function1(1, 3);
ERROR: function "function1" with 2 parameters doesn't exist
omm=# CALL function1(1, 3,1);
res
-----
4
(1 row)
omm=# CALL function1(num1 => 1,num2 => 3,res =>1);
res
-----
4
(1 row)
omm=# CALL function1(num2 := 2, num1 := 3,res:=1);
res
-----
5
(1 row)
2.创建返回类型为record的函数2，重命名函数2
CREATE OR REPLACE FUNCTION function2(i int, out result_1 bigint, out result_2 bigint)
returns SETOF RECORD
as $$
begin
result_1 = i + 1;
result_2 = i * 10;
return next;
end;
$$language plpgsql;
call function2(1, 0, 0);
omm=# ALTER FUNCTION function2(i int, out result_1 bigint, out result_2 bigint) RENAME TO function2_new;
ALTER FUNCTION
3.使用\sf和系统函数查看函数定义
omm=# \sf function1
CREATE OR REPLACE FUNCTION public.function1(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=# select * from pg_proc where proname = 'function2';
argmodes | proargnames | proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | prop
ackage | prokind
---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+
--------------+-------------+-----------+-------------+----------+-----------------+------------+-------------+----------------+----
---------+-------------+----------------+--------+--------+-----------+--------+------------------+------------+--------------+-----
-------+---------
(0 rows)
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef |
proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | pro
omm=#
4.删除函数
omm=# drop function function1;
DROP FUNCTION
