快捷導(dǎo)航

PostgreSQL中調(diào)用存儲(chǔ)過(guò)程并返回?cái)?shù)據(jù)集實(shí)例

更新時(shí)間：2015年01月19日 11:19:12 投稿：junjie

這篇文章主要介紹了PostgreSQL中調(diào)用存儲(chǔ)過(guò)程并返回?cái)?shù)據(jù)集實(shí)例,本文給出一創(chuàng)建數(shù)據(jù)表、插入測(cè)試數(shù)據(jù)、創(chuàng)建存儲(chǔ)過(guò)程、調(diào)用創(chuàng)建存儲(chǔ)過(guò)程和運(yùn)行效果完整例子,需要的朋友可以參考下

這里用一個(gè)實(shí)例來(lái)演示PostgreSQL存儲(chǔ)過(guò)程如何返回?cái)?shù)據(jù)集。

1、首先準(zhǔn)備數(shù)據(jù)表

復(fù)制代碼代碼如下:

//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, 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、插入測(cè)試數(shù)據(jù)

復(fù)制代碼代碼如下:

insert into member_category(name, discount_rate, base_integral) values('白金會(huì)員', 6.5, 10000);
insert into member_category(name, discount_rate, base_integral) values('高級(jí)會(huì)員', 7.5, 1000);
insert into member_category(name, discount_rate, base_integral) values('中級(jí)會(huì)員', 8.5, 100);
insert into member_category(name, discount_rate, base_integral) values('普通會(huì)員', 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', '超白金會(huì)員,一切免單', '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、創(chuàng)建存儲(chǔ)過(guò)程

復(fù)制代碼代碼如下:

--調(diào)用存儲(chǔ)過(guò)程f_get_member_info, 返回會(huì)員的所有信息

--memberType:會(huì)員類型 status:會(huì)員狀態(tài)  findCondition:查詢條件(卡號(hào)/電話/姓名)  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';

4、調(diào)用存儲(chǔ)過(guò)程

復(fù)制代碼代碼如下:

--調(diào)用存儲(chǔ)過(guò)程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、測(cè)試結(jié)果