心得：
我的理解是对数据库语言进行编程调用，就像Java代码类编程写一个具有某种特定功能的方法去进行调用一样。数据库的存储过程是一组相关联的sql语句或函数，通过进行组合形成模块化，从而具有某种特定的功能。而这种存储过程创建后会被保存在数据库中，被编译，随时可以被数据库用户调用。
课程作业
1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参
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.
(创建带有入参和出参的函数1)
omm=# CREATE OR REPLACE FUNCTION func_1(num1 IN integer, num2 IN integer, sum OUT integer)
omm-#
omm-# RETURN integer
omm-#
omm-# AS
omm$#
omm$# BEGIN
omm$#
omm$# sum:= num1 - num2;
omm$# omm$#
END;
omm$#
omm$# /
CREATE FUNCTION
omm=#
omm=# call func_1(200, 100 , 0);
sum
-----
100
(1 row)
omm=#
omm=# CALL func_1(num1 => 300,num2 => 200,sum => 0);
sum
-----
100
(1 row)
omm=#
2.创建返回类型为record的函数2，重命名函数2
omm=# CREATE OR REPLACE FUNCTION func_record_sql1(num int, out result_1 bigint, out result_2 bigint)
omm-#
omm-# returns SETOF RECORD
omm-#
omm-# as $$
omm$#
omm$# begin result_1 = num+ 10000;
omm$#
omm$# result_2 = num * 100;
omm$#
omm$# return next;
omm$# end;
omm$#
omm$# omm$#
$$language plpgsql;
CREATE FUNCTION
comm=# call func_record_sql1(1, 0, 0);
result_1 | result_2
----------+----------
10001 | 100
(1 row)
omm=#
omm=#
omm=# ALTER FUNCTION func_record_sql1(INT) RENAME TO func_record_sql2;
ALTER FUNCTION
3.使用\sf和系统函数查看函数定义
omm=# \sf func_record_sql2
CREATE OR REPLACE FUNCTION public.func_record_sql2(num integer, OUT result_1 bigint, OUT result_2 bigint)
RETURNS SETOF record
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$
begin result_1 = num+ 10000;
result_2 = num * 100;
return next;
end;
$function$;
omm=#
omm=# select * from pg_proc where proname = 'func_record_sql2';
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
------------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+--------------+------------
-+-----------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-------------------------+----------------+----------
---------------------+--------+-----------+--------+------------------+------------+--------------+------------+---------
func_record_sql2 | 2200 | 10 | 11750 | 100 | 1000 | 0 | - | f | f | f | f | f
| t | v | 1 | 0 | 2249 | 23 | {23,20,20} | {i,o,o} | {num,result_1,result_2} | |
+| | | | | f | f | f | f
| | | | | | | | | | | |
| | | | | | | | | | |
+| | | | | | | |
| | | | | | | | | | | |
| | | | | | | | | | | begin res
ult_1 = num+ 10000; +| | | | | | | |
| | | | | | | | | | | |
| | | | | | | | | | |
+| | | | | | | |
| | | | | | | | | | | |
| | | | | | | | | | | result_2
= num * 100; +| | | | | | | |
| | | | | | | | | | | |
| | | | | | | | | | |
--More--
4.删除函数
Drop function func_1;
Drop function func_record_sql2;
