postgres的存储过程与存储函数

16.存储过程管理
支持通过图形化方式创建、修改、删除和查看存储过程，支持存储过程的调用与运行
支持存储过程中游标的定义、打开、拨动、关闭
支持存储过程中使用动态SQL语句

命令行模式：
16.1无参存储过程
创建：
create table t_p1 (id int,name varchar(20) );

create or replace procedure p_ins as
begin
for I in 1..100 loop
 insert into t_p1 values (i, 'abcdfeg');
end loop;
end;
/

修改：
语句与创建一样。

查看：
select * from pg_proc where proname ='p_ins';

调用：
call P_INS();

删除：
Drop procedure p_ins;

16.2有参存储过程
创建：
create table t_p2 (id_i int,name_o varchar(20) );
Insert into t_p2 values (1, 'abc');

create or replace procedure p_ins2(id_i int)
as
begin
 insert into t_p2 values (id_i, 'abc');
end;
/

修改：
语句与创建一样。

查看：
select * from pg_proc where proname ='p_ins2';

调用：
call P_INS2(2);

删除：
Drop procedure p_ins2;

16.3存储过程中游标的定义、打开、拨动、关闭
create table t_p3 (
    id int,
    name varchar(20),
    age int
);

create table t_p3_2 (
    id int,
    name varchar(20),
    age int
);

insert into t_p3(id,name, age) 
values (1, 'zs', 11),
(1, 'zs2', 112),
(2, 'ls', 22),
(3, 'ww', 33),
(4, 'zl', 44);

create or replace procedure p_ins3(id_i int) as
declare
    uId int;
    uName varchar(20);
    uAge int;
    -- 声明游标 
    cursor curs for select id,name,age from t_p3 where id = id_i;
begin
   -- 打开游标
   open curs;
   -- 循环读取游标数据
   loop
        -- 使用游标
        fetch curs into uId,uName,uAge;
        exit when not found;
        insert into t_p3_2(id,name, age) values(uId,uName,uAge);
   end loop;
   -- 关闭游标
  close curs;
end;
/

调用
call p_ins3(1);

16.4支持存储过程中使用动态SQL语句
create or replace procedure p_ins4(id_i int) 
as
begin
    execute 'insert into t_p3_2(id) values($1)' using $1; 
end;
/

call p_ins4(3);

17.存储函数管理
支持创建、修改、删除和查看存储函数，支持存储函数的调用与运行
支持存储函数中游标的定义、打开、拨动、关闭
支持存储函数中使用动态SQL语句

命令行模式：
17.1无参存储函数
创建：
create table t_f1 (id int,name varchar(20) );
insert into t_f1(id,name) values (1,'abc'),(1,'aaa'),(2,'bbb'),(3,'ccc');

CREATE OR REPLACE FUNCTION f_sel1 ()  
RETURNS integer AS $total$  
declare  
    total integer;  
BEGIN  
   SELECT count(*) into total FROM t_f1;  
   RETURN total;  
END;  
$total$ LANGUAGE plpgsql;

修改：
语句与创建一样。

查看：
select * from pg_proc where proname ='f_sel1';

调用：
select f_sel1();

删除：
Drop FUNCTION f_sel1;

17.2有参存储函数
创建：
CREATE OR REPLACE FUNCTION f_sel2 (id_i int)  
RETURNS integer AS $total$  
declare  
    total integer;  
BEGIN  
   SELECT count(*) into total FROM t_f1 where id =id_i;  
   RETURN total;  
END;  
$total$ LANGUAGE plpgsql;

修改：
语句与创建一样。

查看：
select * from pg_proc where proname ='f_sel2';

调用：
select f_sel2(1);

删除：
Drop FUNCTION f_sel2;

17.3存储函数中游标的定义、打开、拨动、关闭

建表：
create table f_p3 (
    id int,
    name varchar(20),
    age int
);

create table f_p3_2 (
    id int,
    name varchar(20),
    age int
);

insert into f_p3(id,name, age) 
values (1, 'zs', 11),
(1, 'zs2', 112),
(2, 'ls', 22),
(3, 'ww', 33),
(4, 'zl', 44);

建立存储过程

create or replace FUNCTION f_sel3 (id_i int) 
returns integer AS 
 $$
declare
    uId int;
    uName varchar(20);
    uAge int;
    sumAge int := 0;
    -- 声明游标 
    cursor curs for select id,name,age from f_p3 where id = id_i;
begin
   -- 打开游标
   open curs;
   -- 循环读取游标数据
   loop
        -- 使用游标
        fetch curs into uId,uName,uAge;
        exit when not found;
        insert into f_p3_2(id,name, age) values(uId,uName,uAge);
        sumAge = sumAge + uAge;
   end loop;
   -- 关闭游标
  close curs;
  return sumAge;
end
$$
language 'plpgsql';

调用
select f_sel3(1);

17.4支持存储函数中使用动态SQL语句
create or replace FUNCTION f_sel4 (id_i int) 
returns integer AS 
$$
declare
    v_count int;
begin
    execute 'select count(*) from f_p3 where id = $1' into v_count using $1; 
    return v_count; 
end
$$
language 'plpgsql';

select f_sel4(1);