Oracle存储过程详解(一)

存储过程创建语法：

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;

注意事项：

存储过程参数不带取值范围，in表示传入，out表示输出
变量带取值范围，后面接分号
在判断语句前最好先用count(*)函数判断是否存在该条操作记录
用select … into … 给变量赋值
在代码中抛异常用 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 在等待资源时超时

基本语法

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存储过程详解(二)

无参存储过程：

create or replace procedure sayhello
as
--说明部分
begin
  dbms_output.put_line('hello world');
end;

命令窗口的两种调用方法

1.

SQL> set serveroutput on;   --第一次一定要开
SQL> exec sayhello
hello world
PL/SQL procedure successfully completed

2.

SQL> begin
  2  sayhello();
  3  sayhello();
  4  end;
  5  /
hello world
hello world
PL/SQL procedure successfully completed

pl/sql块 调试

BEGIN
  SAYHELLO();
--rollback; 
END;

SQL窗口调用(不会输出dbms_output的内容)

exec是sqlplus的命令，只能在sqlplus中使用(PLSQL Developer的命令窗口也是这种)。
call是sql命令，任何工具都可以使用。如果想返回结果就用 传递参数

call sayhello();

有参存储过程

案例：给员工涨薪水

create or replace procedure raisesalary(eno in number)
as
--定义一个变量用来保存涨薪前的薪水
psal emp.sal%TYPE;
begin
 --涨薪前
 select sal into psal from emp where empno = eno;

 --涨薪
 update emp set sal = sal + 100 where empno = eno;

 DBMS_OUTPUT.PUT_LINE('涨薪前:' ||psal|| '涨薪后:' || (psal+100));
 end;

PL/SQL块调试

DECLARE
  ENO NUMBER;
BEGIN
  ENO := 7499;

  RAISESALARY(
    ENO => ENO
  );
--rollback; 
END;

案例：查询员工信息

create or replace procedure queryempinfo
(
  eno in number,
  pname out varchar2,
  psal out number,
  pjob out varchar2
)
 as
 begin
   select ename,sal,job into pname,psal,pjob from emp where empno = eno;
end; 

PL/SQL块调试

DECLARE
  ENO NUMBER;
  PNAME VARCHAR2(200);
  PSAL NUMBER;
  PJOB VARCHAR2(200);
BEGIN
  ENO := 7521;

  QUERYEMPINFO(
    ENO => ENO,
    PNAME => PNAME,
    PSAL => PSAL,
    PJOB => PJOB
  );

DBMS_OUTPUT.PUT_LINE('PNAME = ' || PNAME);

  :PNAME := PNAME;

DBMS_OUTPUT.PUT_LINE('PSAL = ' || PSAL);

  :PSAL := PSAL;

DBMS_OUTPUT.PUT_LINE('PJOB = ' || PJOB);

  :PJOB := PJOB;
--rollback; 
END;

有参存储函数

案例：查询员工的年收入

create or replace function queryempincome(eno in number)
return number
as
psal emp.sal%TYPE;
pcomm emp.comm%TYPE;
BEGIN
  SELECT SAL,COMM INTO PSAL,PCOMM FROM EMP WHERE EMPNO = ENO;

  --返回年收入(这里一定要注意空值的情况)
  return psal*12+nvl(pcomm,0);
END;

PL/SQL块调试

DECLARE
  ENO NUMBER;
  v_Return NUMBER;
BEGIN
  ENO := 7839;

  v_Return := QUERYEMPINCOME(
    ENO => ENO
  );

DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

  :v_Return := v_Return;
--rollback; 
END;

查看存储过程的属性是输入还是输出的

SQL> DESC DBMS_OUTPUT;
Element   Type      
--------- --------- 
ENABLE    PROCEDURE 
DISABLE   PROCEDURE 
PUT       PROCEDURE 
PUT_LINE  PROCEDURE 
NEW_LINE  PROCEDURE 
GET_LINE  PROCEDURE 
CHARARR   TYPE      
GET_LINES PROCEDURE 

SQL> DESC DBMS_OUTPUT.put_line;
Parameter Type     Mode Default? 
--------- -------- ---- -------- 
A         VARCHAR2 IN   

out 集合(使用光标)

--包头
create or replace package mypackage as
 type empcursor is ref cursor; --声明一个光标类型
 procedure queryEmpList(dno in number,empList out empcursor);
end;

--包体
create or replace package body mypackage as

 procedure queryEmpList(dno in number,empList out empcursor) as
   begin
     --打开光标
     open empList for select * from emp where deptno=dno;
   end;
end;

命令窗口(查看程序包的结构)

SQL> desc mypackage

Element      Type      
------------ --------- 
EMPCURSOR    TYPE      
QUERYEMPLIST PROCEDURE 

SQL> desc mypackage.queryEmpList
Parameter Type       Mode Default? 
--------- ---------- ---- -------- 
DNO       NUMBER     IN            
EMPLIST   REF CURSOR OUT 

应用程序中访问存储过程和存储函数

访问存储过程

//获取数据库的连接
略
//sql语句
String sql = "{call queryEmpinfo(?,?,?,?)}";
//通过连接创建statment
CallableStatement call = conn.prepareCall(sql);

//对于IN参数需要赋值
call.setInt(1,7839);

//对于OUT参数需要先申明
call.registerOutParameter(2,OracleTypes.VARCHAR);
call.registerOutParameter(3,OracleTypes.NUMBER);
call.registerOutParameter(4,OracleTypes.VARCHAR);

//执行调用
call.execute();

//取出结果
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);

访问存储函数

//获取数据库的连接
略
//sql语句
String sql = "{?=call queryempincome(?)}";
//通过连接创建statment
CallableStatement call = conn.prepareCall(sql);

//对于输出参数需要声明
call.setInt(1,OracleTypes.NUMBER);

//对于输入参数需要赋值
call.registOutParameterr(2,7839);

//执行调用
call.execute();

//取出年收入
double income = call.getDouble(1);

访问有光标的存储过程

//获取数据库的连接
略
//sql语句 (一定要写上包名)
String sql = "{call mypackage.queryEmpList(?,?)}";
//通过连接创建statment
CallableStatement call = conn.prepareCall(sql);

//对于IN参数需要赋值
call.setInt(1,10);

//对于OUT参数需要先申明
call.registerOutParameter(2,OracleTypes.CURSOR);

//执行调用
call.execute();

//取出该部门中所有员工信息(注意这里)
ResultSet rs = ((OracleCallableStatement)call).getCursor(2);

while(rs.next()){
    //可以取出sql语句中查询的所有字段(这里只取几个演示下)
    int empno = rs.getInt("empno");
    String ename = rs.getString("ename");
    double sal = rs.getDouble("sal");
}

公司用的最基本的两个存储过程：

删除表

create or replace procedure drop_table(tablename in VARCHAR2)
is

  vn_ctn number(2);
  Begin

  select count(*) into vn_ctn from user_all_tables a where a.table_name = upper(tablename); 

   if vn_ctn > 0 then 
       --execute immediate不管表在与不成都通过编译或执行
      execute immediate 'drop table ' || tablename;  
   end if;

EXCEPTION  
  WHEN OTHERS THEN 

    dbms_output.put_line(SQLCODE || '::'||SUBSTR(SQLERRM, 1, 400));
End drop_table;

删除序列

create or replace procedure drop_sequence(sequenceName in VARCHAR2)
is

  vn_ctn number(2);
  Begin

  select count(*) into vn_ctn from user_sequences a where a.sequence_name = upper(sequenceName); 

   if vn_ctn > 0 then 
      execute immediate 'drop sequence ' || sequenceName;  
   end if;

EXCEPTION  
  WHEN OTHERS THEN 

    dbms_output.put_line(SQLCODE || '::'||SUBSTR(SQLERRM, 1, 400));
End drop_sequence;