个人中心
gpt4 book ai didi

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

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 27 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章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, 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
$$
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、调用存储过程 。

复制代码 代码如下:

--调用存储过程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中调用存储过程并返回数据集实例

最后此篇关于PostgreSQL中调用存储过程并返回数据集实例的文章就讲到这里了,如果你想了解更多关于PostgreSQL中调用存储过程并返回数据集实例的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

27 4 0
文章推荐： PostgreSQL中关闭死锁进程的方法
文章推荐： PostgreSql新手必学入门命令小结
文章推荐： 初识PostgreSQL存储过程
文章推荐： MongoDB的PHP驱动方法与技巧
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com