PostgreSQL中调用存储过程并返回数据集实例

PostgreSQL中调用存储过程并返回数据集实例中调用存储过程并返回数据集实例

主要介绍了PostgreSQL中调用存储过程并返回数据集实例,本文给出一创建数据表、插入测试数据、创建存储过程、调用创建存储过程和运行效果完整例子,需要

的朋友可以参考下

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

1、首先准备数据表、首先准备数据表

复制代码 代码如下:

//member_category

create table member_category(id serial, name text, discount_rate real, base_integral integer);

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(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:商店名称

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、调用存储过程、调用存储过程