Oracle存储过程实例-mysql教程-PHP中文网

首页

数据库

mysql教程

Oracle存储过程实例

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:44 PM

oracle存储实例过程

createorreplaceprocedureGetRecords(name_outoutvarchar2,age_ininvarchar2)as begin selectNAMEintoname_outfromtestwhereAGE=age_in; end; createorreplaceprocedureinsertRecord(UserIDinvarchar2,UserNameinvarchar2,UserAgeinvarchar2)is begin insert

create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as
begin
select NAME into name_out from test where AGE = age_in;
end;
create or replace procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is
begin
insert into test values (UserID, UserName, UserAge);
end;

首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：

Java代码 Oracle存储过程实例

create sequence TEST_SEQ
minvalue 100
maxvalue 999
start with 102
increment by 1
nocache;

语法应该是比较易懂的，最小最大值分别用minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了2），increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号，用test_seq.currval访问当前的序列号。

定义完了Sequence，接下来就是创建一个存储过程InsertRecordWithSequence：

--这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。

Java代码 Oracle存储过程实例

create or replace procedure InsertRecordWithSequence(UserID out number,UserName in varchar2,UserAge in number)
is
begin insert into test(id, name, age) --插入一条记录，PK值从Sequece获取
values(test_seq.nextval, UserName, UserAge);
/*返回PK值。注意Dual表的用法*/
select test_seq.currval into UserID from dual;
end InsertRecordWithSequence;

为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同！并且还要用到Oracle中“包”（Package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。

关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为TestPackage的包，包头是这么定义的：

Java代码 Oracle存储过程实例

create or replace package TestPackage is
type mycursor is ref cursor; -- 定义游标变量
procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数
end TestPackage;
包体是这么定义的：
create or replace package body TestPackage is
/*过程体*/
procedure GetRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end GetRecords;
end TestPackage;

小结：

包是Oracle特有的概念，Sql Server中找不到相匹配的东西。在我看来，包有点像VC++的类，包头就是.h文件，包体就是.cpp文件。包头只负责定义，包体则负责具体实现。如果包返回多个游标，则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用DataReader的NextResult()方法前进到下一个游标。

Java代码

create or replace package TestPackage is
type mycursor is ref cursor;
procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);
procedure SelectRecords(ret_cursor out mycursor);
procedure DeleteRecords(id_in in number);
procedure InsertRecords(name_in in varchar2, age_in in number);
end TestPackage;

包体如下：

Java代码 Oracle存储过程实例

create or replace package body TestPackage is
procedure UpdateRecords(id_in in number, newName in varchar2, newAge in number) as
begin
update test set age = newAge, name = newName where id = id_in;
end UpdateRecords;
procedure SelectRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end SelectRecords;
procedure DeleteRecords(id_in in number) as
begin
delete from test where id = id_in;
end DeleteRecords;
procedure InsertRecords(name_in in varchar2, age_in in number) as
begin
insert into test values (test_seq.nextval, name_in, age_in);
--test_seq是一个已建的Sequence对象，请参照前面的示例
end InsertRecords;
end TestPackage;

TestPackage.SelectRecords

-------------------------------------------------------------------------------------------------------------------------------------------------------------

oracle 存储过程的基本语法

1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字

(

参数1 IN NUMBER,

参数2 IN NUMBER

) IS

变量1 INTEGER :=0;

变量2 DATE;

BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条

记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子：

BEGIN

SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;

EXCEPTION

WHEN NO_DATA_FOUND THEN

xxxx;

END;

...

3.IF 判断

IF V_TEST=1 THEN

BEGIN

do something

END;

END IF;

4.while 循环

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END;

END LOOP;

5.变量赋值

V_TEST := 123;

6.用for in 使用cursor

...

CURSOR cur IS SELECT * FROM xxx;

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM :=cur_result.列名1+cur_result.列名2

END;

END LOOP;

END;

7.带参数的cursor

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

OPEN C_USER(变量值);

LOOP

FETCH C_USER INTO V_NAME;

EXIT FETCH C_USER%NOTFOUND;

do something

END LOOP;

CLOSE C_USER;

8.用pl/sql developer debug

连接数据库后建立一个Test WINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

-------------------------------------------------------------------------------------------------------------------------------------------------------------

oracle存储过程一例

By 凌云志发表于 2007-4-18 17:01:00

最近换了一个项目组，晕，要写oracle的存储过程，幸亏写过一些db2的存储过程，尚且有些经验，不过oralce的pl/sql不大一样，花费了一下午的时间写了一个出来，测试编译通过了，是为记，以备以后查阅。

Java代码 Oracle存储过程实例

CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2 AS
------------------------------------------------------------------------
-- Oracle 包
---国航支付平台VISA退款
-- 游标定义：
--
-- 存储过程定义：
-- PY_WEBREFUND_VISA_PREPARE ： VISA退款准备
-- 最后修改人：dougq
-- 最后修改日期：2007.4.17
------------------------------------------------------------------------
PROCEDURE PY_WEBREFUND_VISA_PREPARE (
in_serialNoStr IN VARCHAR2, --用"|"隔开的一组网上退款申请流水号
in_session_operatorid IN VARCHAR2, --业务操作员
out_return_code OUT VARCHAR2, --存储过程返回码
out_visaInfoStr OUT VARCHAR2
);
END PY_PCKG_REFUND2;
/
CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2 AS
PROCEDURE PY_WEBREFUND_VISA_PREPARE (
in_serialNoStr IN VARCHAR2, --用"|"隔开的一组网上退款申请流水号
in_session_operatorid IN VARCHAR2,--业务操作员
out_return_code OUT VARCHAR2, --存储过程返回码
out_visaInfoStr OUT VARCHAR2
) IS
--变量声明
v_serialno VARCHAR2(20);--网上退款申请流水号
v_refserialno VARCHAR2(20);--支付交易流水号
v_tobankOrderNo VARCHAR2(30);--上送银行的订单号
v_orderDate VARCHAR2(8);--订单日期
v_businessType VARCHAR2(10);--业务类型
v_currType VARCHAR2(3);--订单类型（ET－电子机票）
v_merno VARCHAR2(15);--商户号
v_orderNo VARCHAR2(20);--商户订单号
v_orderState VARCHAR2(2);
v_refAmount NUMBER(15,2);--退款金额
v_tranType VARCHAR(2);--交易类型
v_bank VARCHAR2(10);--收单银行
v_date VARCHAR2 (8);--交易日期
v_time VARCHAR2 (6);--交易时间
v_datetime VARCHAR2 (14);--获取的系统时间
v_index_start NUMBER;
v_index_end NUMBER;
v_i NUMBER;
BEGIN
-- 初始化参数
out_visaInfoStr := '';
v_i := 1;
v_index_start := 1;
v_index_end := INSTR(in_serialNoStr,'|',1,1);
v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end-1);
v_datetime := TO_CHAR (SYSDATE, 'yyyymmddhh24miss');
v_date := SUBSTR (v_datetime, 1, 8);
v_time := SUBSTR (v_datetime, 9, 14);
--从退款请求表中查询定单信息（商户号、商户订单号、退款金额）
WHILE v_index_end > 0 LOOP
SELECT
WEBR_MERNO,
WEBR_ORDERNO,
WEBR_AMOUNT,
WEBR_SERIALNO,
WEBR_REFUNDTYPE
INTO
v_merno,
v_orderNo,
v_refAmount,
v_serialno,
v_tranType
FROM
PY_WEB_REFUND
WHERE
WEBR_REFREQNO = v_refserialno;
--将查询到的数据组成串
out_visaInfoStr := out_visaInfoStr || v_merno || '~' || v_orderNo || '~' || v_refAmount + '|';
--为下次循环做数据准备
v_i := v_i + 1;
v_index_start := v_index_end + 1;
v_index_end := INSTR(in_serialNoStr,'|',1,v_i);
IF v_index_end > 0 THEN
v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end - 1);
END IF;
--根据原支付流水号在流水表中查询该订单的信息，包括原上送银行或第三方的订单号：WTRN_TOBANKORDERNO
SELECT
WTRN_TOBANKORDERNO,
WTRN_ORDERNO,
WTRN_ORDERDATE,
WTRN_BUSINESSTYPE,
WTRN_ACCPBANK,
WTRN_TRANCURRTYPE
INTO
v_tobankOrderNo,
v_orderNo,
v_orderDate,
v_businessType,
v_bank,
v_currType
FROM PY_WEBPAY_VIEW
WHERE WTRN_SERIALNO = v_serialno;
--记录流水表(退款)
INSERT INTO PY_WEBPAY_TRAN(
WTRN_SERIALNO,
WTRN_TRANTYPE,
WTRN_ORIGSERIALNO,
WTRN_ORDERNO,
WTRN_ORDERDATE,
WTRN_BUSINESSTYPE,
WTRN_TRANCURRTYPE,
WTRN_TRANAMOUNT,
WTRN_ACCPBANK,
WTRN_TRANSTATE,
WTRN_TRANTIME,
WTRN_TRANDATE,
WTRN_MERNO,
WTRN_TOBANKORDERNO
)VALUES(
v_refserialno, --和申请表的流水号相同，作为参数传人
v_tranType,
v_serialno, --原交易流水号，查询退款申请表得到
v_orderNo,
v_orderDate,
v_businessType,
v_currType,
v_refAmount,
v_bank,
'1',
v_time,
v_date,
v_merno,
v_tobankOrderNo --上送银行的订单号，查询流水表得到
);
--更新网上退款申请表
UPDATE PY_WEB_REFUND
SET
WEBR_IFDISPOSED = '1',
WEBR_DISPOSEDOPR = in_session_operatorid,
WEBR_DISPOSEDDATE = v_datetime
WHERE
WEBR_REFREQNO = v_refserialno;
--更新定单表
IF v_tranType = '2' THEN
v_orderState := '7';
ELSE
v_orderState := '10';
END IF;
UPDATE PY_ORDER
SET
ORD_ORDERSTATE = v_orderState
WHERE
ORD_ORDERNO = v_orderNo
AND ORD_ORDERDATE = v_orderDate
AND ORD_BUSINESSTYPE = v_businessType;
END LOOP;
-- 异常处理
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
out_return_code := '14001';
RETURN;
END;
END PY_PCKG_REFUND2;
/

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

您什么时候应该使用复合索引与多个单列索引？Apr 11, 2025 am 12:06 AM

在数据库优化中，应根据查询需求选择索引策略：1.当查询涉及多个列且条件顺序固定时，使用复合索引；2.当查询涉及多个列但条件顺序不固定时，使用多个单列索引。复合索引适用于优化多列查询，单列索引则适合单列查询。

如何识别和优化MySQL中的慢速查询？（慢查询日志，performance_schema）Apr 10, 2025 am 09:36 AM

要优化MySQL慢查询，需使用slowquerylog和performance_schema：1.启用slowquerylog并设置阈值，记录慢查询；2.利用performance_schema分析查询执行细节，找出性能瓶颈并优化。

MySQL和SQL：开发人员的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统，SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎，SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询，如按条件过滤和排序。4.常见错误包括语法错误和性能问题，可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

描述MySQL异步主奴隶复制过程。Apr 10, 2025 am 09:30 AM

MySQL异步主从复制通过binlog实现数据同步，提升读性能和高可用性。1)主服务器记录变更到binlog；2)从服务器通过I/O线程读取binlog；3)从服务器的SQL线程应用binlog同步数据。

mysql：简单的概念，用于轻松学习Apr 10, 2025 am 09:29 AM

MySQL是一个开源的关系型数据库管理系统。1）创建数据库和表：使用CREATEDATABASE和CREATETABLE命令。2）基本操作：INSERT、UPDATE、DELETE和SELECT。3）高级操作：JOIN、子查询和事务处理。4）调试技巧：检查语法、数据类型和权限。5）优化建议：使用索引、避免SELECT*和使用事务。

MySQL：数据库的用户友好介绍Apr 10, 2025 am 09:27 AM

MySQL的安装和基本操作包括：1.下载并安装MySQL，设置根用户密码；2.使用SQL命令创建数据库和表，如CREATEDATABASE和CREATETABLE；3.执行CRUD操作，使用INSERT,SELECT,UPDATE,DELETE命令；4.创建索引和存储过程以优化性能和实现复杂逻辑。通过这些步骤，你可以从零开始构建和管理MySQL数据库。

InnoDB缓冲池如何工作，为什么对性能至关重要？Apr 09, 2025 am 12:12 AM

InnoDBBufferPool通过将数据和索引页加载到内存中来提升MySQL数据库的性能。1)数据页加载到BufferPool中，减少磁盘I/O。2)脏页被标记并定期刷新到磁盘。3)LRU算法管理数据页淘汰。4)预读机制提前加载可能需要的数据页。

MySQL：初学者的数据管理易用性Apr 09, 2025 am 12:07 AM

MySQL适合初学者使用，因为它安装简单、功能强大且易于管理数据。1.安装和配置简单，适用于多种操作系统。2.支持基本操作如创建数据库和表、插入、查询、更新和删除数据。3.提供高级功能如JOIN操作和子查询。4.可以通过索引、查询优化和分表分区来提升性能。5.支持备份、恢复和安全措施，确保数据的安全和一致性。

See all articles