oracle-存储过程的基本语法-及注意事项.doc

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存储过程的若干问题备忘
1.在oracle中，数据表别名不能加as，如：
select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误
 也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。
  select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正确编译
  select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation 
  Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。
   可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...
4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过但在运行阶段会报错
 select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提示
ORA-01422:exact fetch returns more than requested number of rows
5.在存储过程中，关于出现null的问题
假设有一个表A，定义如下：
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键 
);
如果在存储过程中，使用如下语句：
select sum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
if fcount is null then
    fcount:=0;
end if;
这样就一切ok了。
6.Hibernate调用oracle存储过程
        this.pnumberManager.getHibernateTemplate().execute(
                new HibernateCallback() ...{
                    public Object doInHibernate(Session session)
                            throws HibernateException, SQLException ...{
                        CallableStatement cs = session
                                .connection()
                                .prepareCall("{call modifyapppnumber_remain(?)}");
                        cs.setString(1, foundationid);
                        cs.execute();
                        return null;
                    }
                });

Oracle存储过程中循环遍历数组的实际应用
时间 ： 2010-07-06 作者：网络 编辑：huyang629 点击： 7 [ 评论 ]
-
-
综合 资源 电子书 社区
如果你是Oracle存储过程方面的新手，对Oracle存储过程的相关实际应用不是很了解的话，以下的文章会给你提供更全面的知识。下面就是文章的具体介绍，望你浏览完以下的内容会有所收获。

循环遍历数组

create or replace procedure test(varArray in myPackage.TestArray) as (输入参数varArray 是自定义的数组类型，定义方式见标题6)

i number; begin i := 1;
Oracle存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张

表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历

for i in 1..varArray.count LOOP dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i)); end LOOP; end test;
While 循环

while 条件语句 LOOP

begin end; end LOOP; E.g create or replace procedure test(i in number) as begin while i < 10 LOOP begin ii:= i + 1; end; end LOOP; end test;
上述的相关内容就是对Oracle存储过程总结的部分描述，希望会给你带来一些帮助在此方面。

存储过程 包含三部分： 声明，执行部分，异常。
可以有无参数程序和带参数存储过程。
无参程序语法
1 create or replace procedure NoParPro
2 as ;
3 begin
4 ;
5 exception
6 ;
7 end;
8

带参存储过程实例
1 create or replace procedure queryempname(sfindno emp.empno%type) as
2 sName emp.ename%type;
3 sjob emp.job%type;
4 begin
5 ....
7 exception
....

14 end;
15

带参数存储过程含赋值方式
1 create or replace procedure runbyparmeters (isal in emp.sal%type,
sname out varchar,sjob in out varchar)
2 as icount number;
3 begin
4 select count(*) into icount from emp where sal>isal and job=sjob;
5 if icount=1 then
6 ....
9 else
10 ....
12 end if;
13 exception
14 when too_many_rows then
15 DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16 when others then
17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
18 end;
19

过程调用
方式一
1 declare
2 realsal emp.sal%type;
3 realname varchar(40);
4 realjob varchar(40);
5 begin
6 realsal:=1100;
7 realname:='';
8 realjob:='CLERK';
9 runbyparmeters(realsal,realname,realjob); －－必须按顺序
10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
11 END;
12

方式二
1 declare
2 realsal emp.sal%type;
3 realname varchar(40);
4 realjob varchar(40)
5 begin
6 realsal:=1100;
7 realname:='';
8 realjob:='CLERK';
9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); －－指定值对应变量顺序可变
10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
11 END;

本文来自CSDN博客，转载请标明出处：

oracle存储过程一例
By 凌云志 发表于 2007-4-18 17:01:00  
最近换了一个项目组，晕，要写oracle的存储过程，幸亏写过一些db2的存储过程，尚且有些经验，不过oralce的pl/sql不大一样，花费了一下午的时间写了一个出来，测试编译通过了，是为记，以备以后查阅。
Java代码
1. CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2 AS   
2. ------------------------------------------------------------------------   
3. -- Oracle 包   
4. ---国航支付平台VISA退款   
5. -- 游标定义：   
6. --   
7. -- 存储过程定义：   
8. -- PY_WEBREFUND_VISA_PREPARE   ： VISA退款准备   
9. -- 最后修改人：dougq   
10. -- 最后修改日期：2007.4.17  
11. ------------------------------------------------------------------------   
12.   
13. PROCEDURE PY_WEBREFUND_VISA_PREPARE (   
14.    in_serialNoStr    IN   VARCHAR2, --用"|"隔开的一组网上退款申请流水号   
15.    in_session_operatorid IN VARCHAR2, --业务操作员   
16.    out_return_code      OUT VARCHAR2, --存储过程返回码   
17.    out_visaInfoStr      OUT VARCHAR2   
18. );   
19.   
20. END PY_PCKG_REFUND2   
21. /   
22.   
23.   
24. CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2 AS   
25.   
26. PROCEDURE PY_WEBREFUND_VISA_PREPARE (   
27.    in_serialNoStr       IN   VARCHAR2, --用"|"隔开的一组网上退款申请流水号   
28.    in_session_operatorid IN VARCHAR2,--业务操作员   
29.    out_return_code      OUT VARCHAR2, --存储过程返回码   
30.    out_visaInfoStr      OUT VARCHAR2   
31. ) IS   
32.    --变量声明   
33.    v_serialno   VARCHAR2(20);--网上退款申请流水号   
34.    v_refserialno VARCHAR2(20);--支付交易流水号   
35.    v_tobankOrderNo VARCHAR2(30);--上送银行的订单号   
36.    v_orderDate   VARCHAR2(8);--订单日期   
37.    v_businessType VARCHAR2(10);--业务类型   
38.    v_currType   VARCHAR2(3);--订单类型（ET－电子机票）   
39.    v_merno    VARCHAR2(15);--商户号   
40.    v_orderNo   VARCHAR2(20);--商户订单号   
41.    v_orderState VARCHAR2(2);   
42.    v_refAmount      NUMBER(15,2);--退款金额   
43.    v_tranType   VARCHAR(2);--交易类型   
44.    v_bank    VARCHAR2(10);--收单银行   
45.    v_date    VARCHAR2 (8);--交易日期   
46.        v_time    VARCHAR2 (6);--交易时间   
47.        v_datetime   VARCHAR2 (14);--获取的系统时间   
48.    v_index_start NUMBER;   
49.    v_index_end   NUMBER;   
50.    v_i     NUMBER;   
51. BEGIN   
52.    -- 初始化参数   
53.    out_visaInfoStr := '';   
54.    v_i := 1;   
55.    v_index_start := 1;   
56.    v_index_end := INSTR(in_serialNoStr,'|',1,1);   
57.    v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end-1);   
58.    v_datetime := TO_CHAR (SYSDATE, 'yyyymmddhh24miss');   
59.    v_date := SUBSTR (v_datetime, 1, 8);   
60.    v_time := SUBSTR (v_datetime, 9, 14);   
61.   
62.    --从退款请求表中查询定单信息（商户号、商户订单号、退款金额）   
63.    WHILE v_index_end > 0 LOOP   
64.     SELECT   
65.      WEBR_MERNO,   
66.      WEBR_ORDERNO,   
67      WEBR_AMOUNT,   
68.      WEBR_SERIALNO,   
69.      WEBR_REFUNDTYPE   
70.     INTO   
71.      v_merno,   
72.      v_orderNo,   
73.      v_refAmount,   
74.      v_serialno,   
75.      v_tranType   
76.        FROM   
77.      PY_WEB_REFUND   
78.        WHERE   
79.      WEBR_REFREQNO = v_refserialno;   
80.       
81.     --将查询到的数据组成串   
82.     out_visaInfoStr := out_visaInfoStr || v_merno || '~' || v_orderNo || '~' || v_refAmount + '|';   
83.      
84.     --为下次循环做数据准备   
85.        v_i := v_i + 1;   
86.        v_index_start := v_index_end + 1;   
87.        v_index_end := INSTR(in_serialNoStr,'|',1,v_i);   
88.        IF v_index_end > 0 THEN   
89.          v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end - 1);         
90.        END IF;   
91.          
92.     --根据原支付流水号在流水表中查询该订单的信息，包括原上送银行或第三方的订单号：WTRN_TOBANKORDERNO   
93.     SELECT   
94.      WTRN_TOBANKORDERNO,   
95.      WTRN_ORDERNO,   
96.        WTRN_ORDERDATE,   
97.        WTRN_BUSINESSTYPE,   
98.      WTRN_ACCPBANK,   
99.      WTRN_TRANCURRTYPE   
100.     INTO   
101.      v_tobankOrderNo,   
102.      v_orderNo,   
103.      v_orderDate,   
104.      v_businessType,   
105.      v_bank,   
106.      v_currType   
107.     FROM PY_WEBPAY_VIEW   
108.      WHERE WTRN_SERIALNO = v_serialno;   
109.        
110.     --记录流水表(退款)   
111.        INSERT INTO PY_WEBPAY_TRAN(   
112.      WTRN_SERIALNO,   
113.      WTRN_TRANTYPE,   
114.      WTRN_ORIGSERIALNO,   
115.      WTRN_ORDERNO,   
116.      WTRN_ORDERDATE,   
117.      WTRN_BUSINESSTYPE,   
118.      WTRN_TRANCURRTYPE,   
119.      WTRN_TRANAMOUNT,   
120.      WTRN_ACCPBANK,   
121.      WTRN_TRANSTATE,   
122.      WTRN_TRANTIME,   
123.      WTRN_TRANDATE,   
124.      WTRN_MERNO,   
125.      WTRN_TOBANKORDERNO   
126.     )VALUES(   
127.      v_refserialno, --和申请表的流水号相同，作为参数传人   
128.      v_tranType,   
129.      v_serialno, --原交易流水号，查询退款申请表得到   
130.      v_orderNo,   
131.      v_orderDate,   
132.      v_businessType,   
133.      v_currType,   
134.      v_refAmount,   
135.      v_bank,   
136.      '1',   
137.      v_time,   
138.      v_date,   
139.      v_merno,   
140.      v_tobankOrderNo --上送银行的订单号，查询流水表得到   
141.     );   
142.   
143.     --更新网上退款申请表   
144.     UPDATE PY_WEB_REFUND   
145.     SET   
146.      WEBR_IFDISPOSED = '1',   
147.      WEBR_DISPOSEDOPR = in_session_operatorid,   
148.      WEBR_DISPOSEDDATE = v_datetime   
149.     WHERE   
150.      WEBR_REFREQNO = v_refserialno;   
151.       
152.     --更新定单表   
153.     IF v_tranType = '2' THEN   
154.      v_orderState := '7';   
155.     ELSE   
156.      v_orderState := '10';   
157.     END IF;   
158.   
159.     UPDATE PY_ORDER   
160.     SET   
161.      ORD_ORDERSTATE = v_orderState   
162.     WHERE   
163.       ORD_ORDERNO = v_orderNo   
164.      AND ORD_ORDERDATE = v_orderDate   
165.      AND ORD_BUSINESSTYPE = v_businessType;   
166.    END LOOP;   
167.   
168.    -- 异常处理   
169.    EXCEPTION   
170.     WHEN OTHERS THEN   
171.     ROLLBACK;   
172.     out_return_code := '14001';   
173.     RETURN;   
174. END;   
175.   
176. END PY_PCKG_REFUND2;   
177. /  
Oracle存储过程可以有无参数存储过程和带参数存储过程。 
一、无参程序过程语法
1 create or replace procedure NoParPro
2 as  ;
3 begin
4 ;
5 exception     //存储过程异常
6     ;
7 end;
8 

        二、带参存储过程实例
 1 create or replace procedure queryempname(sfindno emp.empno%type) as
 2        sName emp.ename%type;
 3        sjob emp.job%type;
 4 begin
 5        ....
 7 exception
          ....
14 end;
15 

    三、 带参数存储过程含赋值方式
 1 create or replace procedure runbyparmeters  (isal in emp.sal%type,
                            sname out varchar,sjob in out varchar)
 2  as icount number;
 3  begin
 4       select count(*) into icount from emp where sal>isal and job=sjob;
 5       if icount=1 then
 6         ....
 9       else
10         ....
12       end if;
13  exception
14       when too_many_rows then
15       DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16       when others then
17       DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
18  end;
19 

  四、在Oracle中对存储过程的调用
  过程调用方式一
 1 declare
 2        realsal emp.sal%type;
 3        realname varchar(40);
 4        realjob varchar(40);
 5  begin   //存储过程调用开始
 6        realsal:=1100;
 7        realname:='';
 8        realjob:='CLERK';
 9        runbyparmeters(realsal,realname,realjob);     －－必须按顺序
10        DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11  END;  //过程调用结束
12 

  过程调用方式二
 1 declare
 2       realsal emp.sal%type;
 3       realname varchar(40);
 4       realjob varchar(40);
 5 begin    //过程调用开始
 6       realsal:=1100;
 7       realname:='';
 8       realjob:='CLERK';
 9       runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);  －－指定值对应变量顺序可变
10       DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11 END;  //过程调用结束
12