Oracle存储过程学习 （转） oracle 存储过程的基本语法 及注意事项

原链接：http://www.cnblogs.com/chuncn/archive/2009/01/29/1381291.html

 

存储过程创建语法：

       create or replace procedure 存储过程名（param1 in type，param2 out type）

as

变量1 类型（值范围）;

变量2 类型（值范围）;

Begin

    Select count(*) into 变量1 from 表A where列名=param1；

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名=param1；

       Dbms_output。Put_line(‘打印信息’);

    Elsif (判断条件) then

       Dbms_output。Put_line(‘打印信息’);

    Else

       Raise 异常名（NO_DATA_FOUND）;

    End if;

Exception

    When others then

       Rollback;

End;

 

注意事项：

1，  存储过程参数不带取值范围，in表示传入，out表示输出

2，  变量带取值范围，后面接分号

3，  在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4，  用select 。。。into。。。给变量赋值

5，  在代码中抛异常用 raise+异常名

 

 

以命名的异常

命名的系统异常                          产生原因

ACCESS_INTO_NULL                   未定义对象

CASE_NOT_FOUND                     CASE 中若未包含相应的 WHEN ，并且没有设置

ELSE 时

COLLECTION_IS_NULL                集合元素未初始化

CURSER_ALREADY_OPEN          游标已经打开

DUP_VAL_ON_INDEX                   唯一索引对应的列上有重复的值

INVALID_CURSOR                 在不合法的游标上进行操作

INVALID_NUMBER                       内嵌的 SQL 语句不能将字符转换为数字

NO_DATA_FOUND                        使用 select into 未返回行，或应用索引表未初始化的 

 

TOO_MANY_ROWS                      执行 select into 时，结果集超过一行

ZERO_DIVIDE                              除数为 0

SUBSCRIPT_BEYOND_COUNT     元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT       使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR                             赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED                           PL/SQL 应用程序连接到 oracle 数据库时，提供了不

正确的用户名或密码

NOT_LOGGED_ON                       PL/SQL 应用程序在没有连接 oralce 数据库的情况下

访问数据

PROGRAM_ERROR                       PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL

系统包

ROWTYPE_MISMATCH                宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL                             使用对象类型时，在 null 对象上调用对象方法

STORAGE_ERROR                        运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID                         无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE         Oracle 在等待资源时超时 
---------------------------------------------------------------------------------------------------------------------

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;
                    }
                });

 

 

 

0. sql语句的提交和回滚

a. 可直接执行增删改，只用在最后提交时使用

strsql=\'insert into....";

execute immediate str_sql;

commit; 所有的语句会一起提交

b. 在程序一开始就设置回滚点

savepoint save_point1;   --定义回滚保存点

c 执行sql语句后提交/回滚

execute immediate str_sql;
     exception
      when others then
          rollback to save_point1; /*异常处理，保存点下面的操作都不会被执行*/
          Out_msg :=\'操作失败！\'; --输出变量
    commit;

 

1. 输出

--DBMS_OUTPUT.PUT_LINE(sqlstr);

2. 输出参数（返回数据集）

参数： returnResult OUT DATA_OPRSTORE.selectoprcursor ) --返回的结果集

返回： OPEN returnResult FOR sqlstr;            --返回结果集

   System.Data.OracleClient.OracleParameter dbPara = new System.Data.OracleClient.OracleParameter();
            dbPara.ParameterName = @“returnValue”;
            dbPara.OracleType = OracleType.Cursor;
            dbPara.Direction = ParameterDirection.Output;

3. 参数条件判断

if 有值 else 空值，对应存储过程的default null，不满足查询提交时可传null值或不传值

   if (!string.IsNullOrEmpty(_model.ss))
            {
                pa = OracleHelper.GetParameter(@":ss", _model.ss, _model.ss.Length, ParameterDirection.Input, OracleDbType.Varchar2, OracleCollectionType.None);
                            }
            else
            {
                pa = OracleHelper.GetParameter(@":ss", null, _model.ss.Length, ParameterDirection.Input, OracleDbType.Varchar2, OracleCollectionType.None);
                         }

ss IN VARCHAR2 DEFAULT NULL,         

4. 日期参数

UpToDate2 IN DATE DEFAULT NULL,         --销售时间

sqlstr:=sqlstr||\' AND ssm.stockindate <=\'\'\'||UpToDate1||\'\'\'\';

5. 获得sql返回值（agencyName 为定义的变量 agencyName VARCHAR2(1000);）

select (SELECT sa.agencyname FROM agency sa WHERE sa.agencyid=4444) into agencyName from dual;

6. 获取数据库中的某一行作为实体类返回

a. 定义变量： ShopInfo shop_ss%rowtype;      --shop_ss必须和表的名称一致

b. select * into ShopInfo from shop_ss where id=12 ;
IF ShopInfo.cid IS NOT NULL THEN
    DBMS_OUTPUT.put_line(\'实体类成功！\');
   END IF ;

7. 轮询数据库中的每一行

a. 定义类型和变量

type ShopCur is table of shop%rowtype index by binary_integer;     --定义类型（shop 必须和表名一致）
S_Read     ShopCur;      --定义变量

b. 使用

   execute immediate \'select * from shop\'
     bulk collect into S_Read;
    if S_Read is not null then
   i := S_Read.count;
                while i > 0 loop
                      if S_Read(i).var1 <= 44 then
                          tempVar:= S_Read(i).var2;
                          exit;
                      end if;
                i := i -1;
                end loop;
   END IF ;

8. 存储过程嵌套调用传递参数

Shop_Upgrade(S_C => S_C1,S_A => S_A);

Shop_Upgrade为被调用的存储过程名称，S_C ，S_A为两个存储过程都有的变量

---S_C参数名 S_C1 参数值