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);