PostgreSQL預存程序返回資料集執行個體

來源:互聯網
上載者:User

標籤:postgresql   預存程序   資料集   function   ubuntu   

這裡用一個執行個體來示範PostgreSQL預存程序如何返回資料集.

1 首先準備資料表

<pre name="code" class="sql">//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, '[email protected]', 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, '[email protected]', 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, '[email protected]', 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, '[email protected]', 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, '[email protected]', 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 測試結果





PostgreSQL預存程序返回資料集執行個體

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.