openGauss每日一练第15天 | 定义存储过程和函数

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql: FATAL: database "omm" does not exist
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
omm=# omm-# as
omm$# begin
omm$# create procedure procedure_insert(a int, b int)
insert into item values(a, b);
omm$# end;
omm$# /
CREATE PROCEDURE
omm=# drop procedure procedure_insert;
DROP PROCEDURE
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
omm=# select * from pg_proc where proname = 'procedure_insert';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | prois
agg | proiswindow | prosecdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefau
lts | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults |
prosrc | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propack
age | prokind
procedure_insert | 2200 | 10 | 11750 | 100 | 0 | 0 | - | f
| f | f | f | f | f | v | 2 |
0 | 2278 | 23 23 | | | {a,b} | | DECLARE
------------------+--------------+----------+---------+---------+---------+-------------+--------------+------
----+-------------+-----------+--------------+-------------+-----------+-------------+----------+-------------
----+------------+-------------+----------------+-------------+-------------+----------------+----------------
------------------------+--------+-----------+--------+------------------+------------+--------------+--------
----+---------
| | | | | | | |
| | | | | | | |
| | | | | | | begin
+| | | | | f | f | f
| p
+| | | | | | |
|
| | | | | | | |
| | | | | | | insert
--More-- | | | | | | | |
Cancel request sent
omm=# select procedure_insert(1,100);
omm=# procedure_insert
------------------

(1 row)

select * from item;
c1 | c2
----+-----
omm=# 1 | 100
(1 row)


omm=# drop procedure if exists procedure_insert;
DROP PROCEDURE
omm=# CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
omm-# AS
omm$# omm$# RETURN num1 + num2;
omm$# END;
omm$# BEGIN
/
CREATE FUNCTION
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 | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults
| prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults | prosrc
| probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind
--------------+--------------+----------+---------+---------+---------+-------------+--------------+----------
| 23 | 23 23 | | | {num1,num2} | | DECLARE
+| | | | | f | f | f | f
+-------------+-----------+--------------+-------------+-----------+-------------+----------+-----------------
+------------+-------------+----------------+-------------+-------------+----------------+--------------------
-+--------+-----------+--------+------------------+------------+--------------+------------+---------
func_add_sql | 2200 | 10 | 11750 | 100 | 0 | 0 | - | f
| f | f | f | f | f | v | 2 | 0
| | | | | | | |
| | | | | | | |
| | | | | | | BEGIN
+| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | RETURN num1 + num2;
+| | | | | | | |
| | | | | | | |
| | | | | | | |
--More--| | | | | | | END
Cancel request sent
omm=# CALL func_add_sql(1, 3);
func_add_sql
--------------
4
(1 row)

omm=# --使用命名标记法传参。
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)

omm=# CREATE FUNCTION func_increment_sql(num1 IN integer, num2 IN integer, res OUT integer)
omm-# RETURN integer
omm-# AS
omm$# BEGIN
omm$# res := num1 + num2;
omm$# END;
omm$# /
CREATE FUNCTION
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)
omm-# as $$
omm$# returns SETOF RECORD
begin
omm-# 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);
omm=# result_1 | result_2
----------+----------
2 | 10
(1 row)


omm=# ALTER FUNCTION func_add_sql(INTEGER, INTEGER) RENAME TO add_two_number;
ALTER FUNCTION
omm=# DROP FUNCTION add_two_number;
DROP FUNCTION
omm=# Drop function func_increment_sql1;
DROP FUNCTION
omm=# Drop function func_increment_sql;
DROP FUNCTION

omm=#
CREATE FUNCTION func_olab(char1 IN char(10), char2 IN char(10), char3 OUT char(10))
RETURN char(10)
AS
BEGIN
char3 := char1 || char2;
END;
/
CREATE FUNCTION
传入全部参数
omm=# CALL func_olab(ol, ab);
ERROR: function "func_olab" with 2 parameters doesn't exist
omm=# CALL func_olab('ol', 'ab');
ERROR: function "func_olab" with 2 parameters doesn't exist
omm=# CALL func_olab(char1 =>'ol',char2 =>'ab',char3=> 'abc');
char3
-------
olab
(1 row)
omm=#
omm=# CALL func_olab('ol', 'ab','abc');
char3
-------
olab
(1 row)

omm=# CALL func_olab(char1 :='ol',char2 :='ab',char3:='abc');
omm=# char3
-------
olab
(1 row)

omm=# \sf func_olab
CREATE OR REPLACE FUNCTION public.func_olab(char1 character, char2 character, OUT char3 character)
RETURNS character
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
BEGIN
char3 := char1 || char2;
END$function$;

omm=# CREATE OR REPLACE FUNCTION func_olab2(a int, out res1 int, out res2 bigint)
omm-# as $$
omm$# begin
omm$# returns SETOF RECORD
omm-# res1 = a - 1;
omm$# res2 = a / 5;
omm$# return next;
omm$# end;
omm$# $$language plpgsql;
CREATE FUNCTION
omm=# call func_olab2(100, 0, 0);
res1 | res2
------+------
99 | 20
(1 row)

输入函数变量类型及输入输出类型
omm=# ALTER FUNCTION func_olab2 RENAME TO func_olab3;
ERROR: syntax error at or near "RENAME TO"
LINE 1: ALTER FUNCTION func_olab2 RENAME TO func_olab3;
^
omm=# ALTER FUNCTION func_olab2(int,int,bigint) RENAME TO func_olab3;
ERROR: function func_olab2(integer, integer, bigint) does not exist
omm=# ALTER FUNCTION func_olab2(int, out int, out bigint) RENAME TO func_olab3;
ALTER FUNCTION
omm=# select * from pg_proc where proname = 'func_olab3';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg |
proiswindow | prosecdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults |
prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults | prosrc | pro
bin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind
------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-
------------+-----------+--------------+-------------+-----------+-------------+----------+-----------------+-
-----------+-------------+----------------+-------------+---------------+----------------+---------------+----
----+-----------+--------+------------------+------------+--------------+------------+---------
func_olab3 | 2200 | 10 | 11750 | 100 | 1000 | 0 | - | f |
f | f | f | f | t | v | 1 | 0 |
2249 | 23 | {23,23,20} | {i,o,o} | {a,res1,res2} | | +|
| | | | f | f | f | f
| | | | | | | | |
| | | | | | | |
| | | | | | begin +|
| | | | | | |
| | | | | | | | |
| | | | | | | |
| | | | | | res1 = a - 1;+|
| | | | | | |
| | | | | | | | |
| | | | | | | |
| | | | | | res2 = a / 5;+|
Cancel request sent
omm=# drop function func_olab;
DROP FUNCTION
omm=# drop function func_olab3;
DROP FUNCTION
omm=#