1. 通过存储过程返回一个结果集，就是依据一些逻辑条件返回一个SELECT结果；
/*例子用到的表*/
CREATE TABLE student(SID INTEGER, SNAME CHAR(30));
INSERT INTO student VALUES(1, 'Tom');
INSERT INTO student VALUES(2, 'Jack');
/* 使用refcursor返回查询结果集，创建一个返回类型为 REFCURSOR 的用户自定义函数 */
CREATE OR REPLACE FUNCTION return_cursor(id int) RETURNS REFCURSOR AS
DECLARE
c1 refcursor;
BEGIN
OPEN c1 as FOR SELECT * FROM student where sid=id;
RETURN c1;
END;
/*使用返回的结果集*/
CREATE OR REPLACE PROCEDURE use_f_returnCursor() AS
DECLARE
c2 REFCURSOR;
rs RECORD;
BEGIN
c2 := return_cursor(1);
FETCH c2 INTO rs;
RAISE NOTICE 'name:%', rs.sname;
END;
2. 如何在存储过程里使用临时表，包括创建和销毁，要求只对当前连接生效的临时表；
/*在存储过程中创建临时表*/
create procedure test_temptab() as
begin
create temporary table temp1(a int,b int);
end;
/*调用存储过程建立临时表temp1*/
call test_temptab();
/*查看临时表是否建立成功*/
/*退出当前会话，重新连接数据库，临时表自动消失*/
3. 如何在存储过程里进行多表关联的更新，如三张表关联（包括实体表、临时表），按条件更新其中的某一张表中的字段；
create table banjifenshu (
ID int,
banji varchar(20), -- 班级
kecheng varchar(20), -- 课程
fenshu decimal(10,2) -- 班级平均分
);
create table xueshengfenshu (
ID INT,
xuesheng varchar(20), -- 学生
kecheng varchar(20), -- 课程
fenshu decimal(20) -- 分数
);
create table banjixuesheng (
ID int,
xuesheng varchar(20), -- 学生
banji varchar(20) -- 所在班级
);
create or replace procedure updateAvgFengShu() as
declare
cursor c_bjfs for select banji,kecheng from banjifenshu;
bj varchar(20);
kc varchar(20);
avg_fs decimal:=0;
begin
for bj,kc in c_bjfs loop
-- raise notice 'bj=%,kc=%',bj,kc;
select avg(fenshu) into avg_fs from xueshengfenshu where xuesheng in (select xuesheng from BANJIXUESHENG where banji=bj) and kecheng=kc group by kecheng;
-- raise notice '%_% avg fs=%',bj,kc,avg_fs;
update banjifenshu set FENSHU=avg_fs where banji=bj and kecheng=kc;
end loop;
end;
测试数据：
表BANJIXUESHENG
· 表XUESHENGFENSHU
· 调用存储过程后的表BANJIFENSHU
4. 如果在存储过程里使用事务，返回自定义异常信息；
/*用户自定义异常使用*/
CREATE OR REPLACE PROCEDURE P_USERDEFINED_EXCEPTION()
AS
DECLARE
EXP_USERDEFINED EXCEPTION;
BEGIN
RAISE EXP_USERDEFINED;
EXCEPTION
WHEN EXP_USERDEFINED THEN
BEGIN
RAISE NOTICE 'EXP_USERDEFINED CATCHED';
END;
END;
/*测试自定义异常*/
5. 存储过程中的游标使用；
/* 所需表结构 */
CREATE TABLE temp(col1 CHAR(4), col2 CHAR(20));
CREATE TABLE leader(lno CHAR(4),lname CHAR(20));
insert into leader values(’1’,’aa’);
insert into leader values(’2’,’bb’);
/*使用游标获取leader表中数据，逐条插入temp表中*/
CREATE PROCEDURE proc_fetch() AS
DECLARE
c_no CHAR(4);
c_name CHAR(20);
CURSOR mycursor FOR SELECT lno,lname FROM leader;
BEGIN
OPEN mycursor;
FETCH mycursor INTO c_no, c_name;
WHILE (mycursor%FOUND) LOOP
INSERT INTO temp VALUES(c_no,c_name);
FETCH mycursor INTO c_no, c_name;
END LOOP;
CLOSE mycursor;
END;
call proc_fetch();
测试结果：
6. 如何通过自定义函数返回一个表，或一个数值（数字、文本、等）；
/*返回值为 table 类型示例*/
CREATE TABLE student(SID INTEGER, SNAME CHAR(30));
INSERT INTO student VALUES(1, 'Tom');
INSERT INTO student VALUES(2, 'Jack');
CREATE OR REPLACE function return_type() RETURNS SETOF student AS
DECLARE
CURSOR cc for SELECT * FROM student;
rs RECORD;
BEGIN
OPEN cc;
LOOP
FETCH cc INTO rs;
EXIT WHEN SQL%NOTFOUND;
RETURN NEXT rs;
END LOOP;
CLOSE cc;
END;
测试结果：
/*返回值为 SETOF integer 类型示例*/
CREATE OR REPLACE FUNCTION increment_multi(i INTEGER)
RETURNS SETOF INTEGER AS
DECLARE
v1 INTEGER;
v2 INTEGER;
v3 INTEGER;
BEGIN
v1 := i + 1;
v2 := i + 2;
v3 := i + 3;
return next v1;
return next v2;
return next v3;
END;
测试结果：
