下面小编给大家整理PostgreSQL存储过程返回数据集实例，本文共5篇，希望大家喜欢！本文原稿由网友“推土机拉花”提供。
篇1：PostgreSQL存储过程返回数据集实例
这里用一个实例来演示PostgreSQL存储过程如何返回数据集.
1 首先准备数据表
'');“ class=”sql“ creator=”creator“ date=”date,“ discount_rate=”discount_rate“ email=”email“ employeealter=”employee;alter“ foreign=”foreign“ gt=”>“ int=”int;<“ integer=”integer,“ integeralter=”integer);alter“ integral=”integral“ keycategory_id=”key(category_id)“ keycreator=”key(creator)“ keyidalter=”key(id);alter“ member=”member“ member_category=”member_category“ member_categoryalter=”member_category;alter“ member_categorycreate=”member_categorycreate“ member_categoryid=”member_category(id“ member_num=”member_num“ membercreate=”membercreate“ memberid=”member(id“ name=”code“ numeric162=”numeric(16,2),“ onaccount=”onaccount“ password=”password“ phone=”phone“ pre=”pre“ primary=”primary“ qq=”qq“ real=”real,“ references=”references“ serial=”serial,“ start_date=”start_date“ status=”status“ store_name=”store_name“ table=”table“ text=”text,“ textalter=”text);alter“ tip=”tip“ valid_date=”valid_date“>
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', '超白金会员,一切免单', '-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 测试结果
篇2：初识PostgreSQL存储过程
这篇文章主要介绍了初识PostgreSQL存储过程,本文讲解了PostgreSQL中存储过程的语法,并给出了一个操作实例,需要的朋友可以参考下
什么是存储过程, 百度百科是这么定义的:存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
Postgresql的存储过程语法结构如下:
代码如下:
CREATE OR REPLACE FUNCTION 函数名(参数1，[整型 int4, 整型数组 _int4, …])
RETURNS 返回值类型 AS
$BODY$
DECLARE
变量声明
BEGIN
函数体
END;
$BODY$
LANGUAGE ‘plpgsql‘ VOLATILE;
下面的例子是要调用一个存储过程自动创建对应的一系列表:
代码如下:
CREATE OR REPLACE FUNCTION create_table_for_client(id int)
RETURNS integer AS
$BODY$
DECLARE
num int4 := 0;
sql ”varchar“;
BEGIN
sql := ‘create table _‘ || id || ‘_company(id int, name text)‘;
EXECUTE sql;
sql := ‘create table _‘ || id || ‘_employee(id int, name text)‘;EXECUTE sql;
sql := ‘create table _‘ || id || ‘_sale_bill(id int, name text)‘;EXECUTE sql;
.......
return num;
END;
$BODY$ LANGUAGE plpgsql VOLATILE
篇3：oracle 存储过程经典实例
/**
--将相同分类下的知识分配到不同的分类下面
-- whwu2
*/
create or replace procedure test_pro --定义存储过程名
is
row_num number := 1; --变量的声明与赋初始值
total_num varchar2（1000）； --变量的声明
tmp_kbcCode varchar2（1000）；
CURSOR myCusor IS --游标的定义
select tt.kbccode from knowledgebase_category tt;
begin
WHILE row_num <= 3 LOOP
dbms_output.put_line（'执行while循环'）； ---打印一些调试信息
row_num := row_num +1;
END LOOP;
--------------方法的执行体------------------------------
select to_number（count（1）） into total_num from knowledge; --对 total_num 进行赋值
dbms_output.put_line（'Total_num' || total_num）； ---打印一些调试信息
open myCusor; --打开游标
loop
fetch myCusor
into tmp_kbcCode; --循环遍历游标，并把游标里面的值依次赋给 tmp_kbcCode
exit when myCusor%notfound;
if （row_num + 10） < total_num then
-- if 条件
update knowledge tt
set tt.kbccode = tmp_kbcCode
where tt.kcode in （select kcode
from （select t.kcode, rownum rn from knowledge t）
where rn >= row_num
and rn <= row_num + 10）；
row_num := row_num + 10;
end if;
end loop;
commit;
end test_pro;
篇4：存储过程的一个简单实例
银行存款表（bankMoney）的内容如下
Id
userID
Sex
Money
001
Zhangsan
男
30
002
Wangwu
男
50
003
Zhangsan
男
40
要求1：查询表bankMoney的内容的存储过程
create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney
注* 在使用过程中只需要把中的SQL语句替换为存储过程名，就可以了很方便吧！
实例2（向存储过程中传递参数）：
加入一笔记录到表bankMoney，并查询此表中userID= Zhangsan的所有存款的总金额，
Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
with encryption ---------加密
as
insert bankMoney (id,userID,sex,Money)
Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID=‘Zhangsan‘
go
在SQL Server查询分析器中执行该存储过程的方法是：
declare @total_price int
exec insert_bank ‘004‘,‘Zhangsan‘,‘男‘,100,@total_price output
print ‘总余额为‘+convert(varchar,@total_price)
go
在这里再嗦一下存储过程的3种传回值（方便正在看这个例子的朋友不用再去查看语法内容）:
1.以Return传回整数
2.以output格式传回参数
3.Recordset
传回值的区别:
output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。
实例3：使用带有复杂 SELECT 语句的简单过程
下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘au_info_all‘ AND type = ‘P‘)
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
au_info_all 存储过程可以通过以下方法执行：
EXECUTE au_info_all
实例4：使用带有参数的简单过程
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
au_info 存储过程可以通过以下方法执行：
EXECUTE au_info ‘Dull‘, ‘Ann‘
-- Or
EXECUTE au_info @lastname = ‘Dull‘, @firstname = ‘Ann‘
-- Or
EXECUTE au_info @firstname = ‘Ann‘, @lastname = ‘Dull‘
实例5：使用带有通配符参数的简单过程
CREATE PROCEDURE au_info2
@lastname varchar(30) = ‘D%‘,
@firstname varchar(18) = ‘%‘
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
au_info2 存储过程可以用多种组合执行，
下面只列出了部分组合：
EXECUTE au_info2
-- Or
EXECUTE au_info2 ‘Wh%‘
-- Or
EXECUTE au_info2 @firstname = ‘A%‘
-- Or
EXECUTE au_info2 ‘[CK]ars[OE]n‘
-- Or
EXECUTE au_info2 ‘Hunter‘, ‘Sheryl‘
-- Or
EXECUTE au_info2 ‘H%‘, ‘S%‘
篇5：DELPHI 调用 Oracle 存储过程并返回数据集的例子.数据库教程
oracle|存储过程|数据
环境: Win2000 + Oracle92一、先在 Oracle 建包
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get(i_test INTEGER,p_rc OUT myrctype);
END pkg_test;
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get(i_test INTEGER,p_rc OUT myrctype) IS
BEGIN
IF i_test = 0 THEN
OPEN p_rc FOR SELECT SYSDATE FROM dual;
ELSE
OPEN p_rc FOR SELECT * FROM tab;
END IF;
END get;
END pkg_test;
二、用 Delphi 调用
建一个窗体,拖动控件 AdoConnection1 , ADOStoredProc1 和 Button1.
procedure TForm1.Button1Click(Sender: TObject);
begin
try
with ADOConnection1 do
begin
ConnectionString:=
'Provider=OraOLEDB.Oracle.1;'
+ 'Password=密码;'
+ 'Persist Security Info=True;'
+ 'User ID=用户名;'
+ 'Data Source=数据库名;'
+ 'Extended Properties=”PLSQLRSet=1;\"';
Open;
end;
except
showMessage('连接不成功');
exit;
end;
try
with ADOStoredProc1 do
begin
Connection := ADOConnection1;
Parameters.Clear;
ProcedureName:= 'pkg_test.get';
Parameters.CreateParameter('p1',ftInteger,pdInput,10,1);
Open;
end;
except
showMessage('无法执行过程.');
end;
end;
教你SQLSERVER扩展存储过程XPCMDSHELL的简单应用
- 动态创建SQL Server数据库、表、存储过程数据库教程2023-01-19
- 一次性压缩Sqlserver中所有库日志的存储过程2022-12-16
- 前段时间写的一个关于成本计算的存储过程!数据库教程2025-07-09
- 求解非线性化工过程鲁棒数据校正的新方法2022-12-11