金宝搏平台:PostgreSQL中调用存储过程并返回数据集实例

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

1、首先准备数据表复制代码
代码如下://member_categorycreate 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”);

//membercreate table member(id serial, member_num text, name 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, discount_rate, base_integral)
values(‘高级会员’, 7.5, 1000);insert into member_category(name,
discount_rate, base_integral) values(‘中级会员’, 8.5, 100);insert into
member_category(name, discount_rate, 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’, 0, 1, ‘B3-440’, ‘超白金会员,一切免单’, ‘2014-01-15’,
1000000, 12345, ‘华南理工门店’);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(‘1000002’, ‘李小路’, 2, 1000.00, 100000, 188141177234,
‘1990-12-12’, 12345678, ‘123456@qq.com’, 0, 1, ‘B3-444’, ‘…’,
‘2014-01-15’, 1000000, 12345, ‘华南理工门店’);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(‘1000003’, ‘洪金包’, 3,
1000.00, 100000, 18814117234, ‘1990-12-12’, 12345678, ‘123456@qq.com’,
0, 1, ‘B3-443’, ‘…’, ‘2014-01-15’, 1000000, 12345,
‘华南理工门店’);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(‘1000004’, ‘成龙’, 4, 100.00, 100000, 18814117723, ‘1990-12-12’,
12345678, ‘123456@qq.com’, 0, 1, ‘B3-442’, ‘…’, ‘2014-01-15’, 1000000,
12345, ‘华南理工门店’);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(‘1000005’, ‘范兵兵’, 4, 100.00, 100000, 18814117327,
‘1990-12-12’, 12345678, ‘123456@qq.com’, 0, 1, ‘B3-441’, ‘…’,
‘2014-01-15’, 1000000, 12345, ‘华南理工门店’);3、创建存储过程复制代码
代码如下:–调用存储过程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$$declarerec 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’;4、调用存储过程复制代码
代码如下:–调用存储过程f_get_member_info示例select * from
f_get_member_info(4, 1, ”, ‘华南理工门店’) as member(member_num
text,mname text,name text,discount_rate real,account
numeric(16,2),integral int,phone text,birthday date,qq int,email
text,onAccount int,status int,address text,tip text,start_date
date,valid_date int,store_nam text);5、测试结果

发表评论

电子邮件地址不会被公开。 必填项已用*标注