PostgreSQL存储过程返回数据集实例

这里用一个实例来演示PostgreSQL存储过程如何返回数据集.

1 首先准备数据表

//member_category
create table member_category(id serial,name text,discount_rate real,base_integral integer);
alter table member_category add primary key(id);
alter table member_category add check(name<>'');

//member
create table member(id serial,member_num text,category_id integer,account numeric(16,2),integral integer,phone text,birthday date,qq integer,email text,status integer,address text,tip text,start_date date,valid_date integer,password text,creator integer,store_name text);
alter table member add primary key(id);
alter table member add foreign key(creator) references employee;
alter table member add foreign key(category_id) references member_category;
alter table member add  onaccount int;

alter table member add onaccount int;
alter table member add store_name text;

2 插入测试数据

insert into member_category(name,discount_rate,base_integral) values('白金会员',6.5,10000);
insert into member_category(name,base_integral) values('高级会员',7.5,1000);
insert into member_category(name,base_integral) values('中级会员',8.5,100);
insert into member_category(name,base_integral) values('普通会员',9.5,10);

insert into member(member_num,name,category_id,account,integral,phone,birthday,qq,email,onaccount,status,address,tip,start_date,valid_date,password,store_name) values('1000001','wuyilun',1,100000.00,100000,18814117777,'1990-12-12',12345678,'123456@qq.com','B3-440','超白金会员,一切免单','2014-01-15',1000000,12345,'华南理工门店');
insert into member(member_num,store_name) values('1000002','李小路',2,1000.00,188141177234,'B3-444','...',store_name) values('1000003','洪金包',3,18814117234,'B3-443',store_name) values('1000004','成龙',4,100.00,18814117723,'B3-442',store_name) values('1000005','范兵兵',18814117327,'B3-441','华南理工门店');

--调用存储过程f_get_member_info,返回会员的所有信息
--memberType:会员类型	status:会员状态	  findCondition:查询条件(卡号/电话/姓名)   store_name:商店名称			
create or replace function f_get_member_info(memberType int,status int,findCondition text,store_name text) returns setof record as
$$
declare
rec record;
begin
  for rec in EXECUTE 'select m.member_num,m.name,m_t.name,m_t.discount_rate,m.account,m.integral,m.phone,m.birthday,m.qq,m.email,m.onAccount,m.status,m.address,m.tip,m.start_date,m.valid_date,m.store_name from member m,member_category m_t where m.category_id = m_t.id and m_t.id = '|| memberType ||' and m.status = '|| status ||' and m.store_name = '''|| store_name ||''' and (m.member_num like ''%'|| findCondition ||'%'' or m.name like ''%'|| findCondition ||'%'' or m.phone like ''%'|| findCondition ||'%'');' loop
    return next rec;
  end loop;
return;
end
$$
language 'plpgsql';

--调用存储过程f_get_member_info示例
select * from f_get_member_info(4,'','华南理工门店') as member(member_num text,mname text,integral int,qq int,onAccount int,valid_date int,store_nam text);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!