Java代码
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代码
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代码
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代码
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代码
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
...
IS
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代码
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);
--从退款请求