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;

注意事项：

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 在等待资源时超时 

实例学习：

1.    建立一个存储过程  一个入参，一个出参

create or replace procedure test_xg_p5(a in number,x out varchar2) is
begin
 if a=0 then
  select count(1) into x from log;
  else
  select count(1) into x from id_table;
   end if;
   exception
    when others then
    ROLLBACK;
end test_xg_p5;

在sql 模式下运行 以上存储过程

调用方式 3种

a.    在 windows 客户端 使用PLSQL Developer 的 command模式 执行

 

b.在安装 oracle 的linux 客户机上使用 oracle 用户在控制台 使用sqlpuls 执行存贮过程

图 pd2

c. 使用java客户端

    /*掉oracle存储过程 返回一个 数字*/

       Connection conn=null;

       CallableStatement proc = null;

       conn= OracleJDBCTest.getConn();

       proc = conn.prepareCall("{call test_xg_p5(?,?)}");

       //传递0 返回1 传递1 返回106 两个表的记录数目不同

       proc.setInt(1,1);

       proc.registerOutParameter(2, Types.VARCHAR);

       proc.execute();

       System.out.println(proc.getString(2));

       conn.close();

 

2 建立一个存储过程  返回一个结果集合

  步骤：

  第一步： 首先建立一个包，定义游标类型

CREATE OR REPLACE PACKAGE TYPES AS
    TYPE BJ_CURSOR IS REF CURSOR;
 END;

 第二步： 建立 中间存贮过程过程 返回游标
CREATE OR REPLACE PROCEDURE BJ_TEST_PROC
        (
               V_TEMP OUT TYPES.BJ_CURSOR,
                PID IN VARCHAR
        )
        AS
        BEGIN
                OPEN V_TEMP FOR select * from id_table t where t.quantity=PID;
      END BJ_TEST_PROC;

 

调用方式2 种

a.     在sqlplus 控制台下 (貌似不能在 commond 模式下执行,不然var a refcursor会报错)

 

var a refcursor 

call BJ_TEST_PROC(:a,1); 

  print a;

 

图 pb3

 

b.     使用java客户端

   首先看下表名 为id_table的表结构

 

图pd4

 

调用代码

    Connection conn=null;

       CallableStatement proc = null;

       conn= OracleJDBCTest.getConn();

       proc = conn.prepareCall("{call BJ_TEST_PROC(?,?)}");

       proc.setString(2,"1");

       proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

       proc.execute();  

          ResultSet rs = (ResultSet) proc.getObject(1);  

          while (rs.next()) {  

            System.out.println(rs.getString("TABLE_NAME"));   

          } 

       conn.close();

 

 

 

一个在eusp 项目中自己写的存储过程 以及 客户端调用代码

 

功能：给学校初始化 客户管理菜单

 

create or replace procedure pr_initCustManageMenu(flag in varchar2,sysOrg in varchar2,outPut out number) is

 

begin

-- 增加菜单 flag=0,删除菜单 flag=1

if flag='0' then

  insert into xs_menu (menu_name,menu_type,menu_icon,SYS_ORGANIZATION) values('客户管理','系统类','customer.gif',sysOrg);

  insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)

    select menu_id,'教职工管理','系统类','tecOrgCustPanel',sysOrg

    from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;

  insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)

    select menu_id,'临时人员管理','系统类','worOrgCustPanel',sysOrg

    from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;

  insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)

    select menu_id,'学生管理','系统类','stuOrgCustPanel',sysOrg

    from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;

  insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)

    select menu_id,'营业组管理','系统类','shoOrgCustPanel',sysOrg

    from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;

  insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)

    select menu_id,'设备管理','系统类','xxTermPanel',sysOrg

    from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;

 

 

 --- 添加权限

insert into XS_LEVEL_SYSMODULE(MENU_ID,ROLE_ID)

 select m.menu_id,(select role_id from xs_role where name='ROLE_admin' and SYS_ORGANIZATION=sysOrg)

 from xs_menu m  where m.sys_organization=sysOrg start with

   m.menu_id=(select m.menu_id from xs_menu m where m.menu_name='客户管理' and m.sys_organization=sysOrg)

connect by prior m.menu_id = m.upper_menu_id;

 

 -- 添加成功返回

 outPut:=0;

  commit;

 

  else

 

   -- 删除权限

   delete  from XS_LEVEL_SYSMODULE ls where exists

 (

 select menu_id from (

 (select m.menu_id from xs_menu m where m.sys_organization=sysOrg start with

   m.menu_id=(select m.menu_id from xs_menu m where m.menu_name='客户管理' and m.sys_organization=sysOrg)

connect by prior m.menu_id = m.upper_menu_id)

) a where a.menu_id=ls.menu_id);

  

   -- 删除菜单

 

 delete  from xs_menu ls where exists

 (

 select menu_id from (

 (select m.menu_id from xs_menu m where m.sys_organization=sysOrg start with

   m.menu_id=(select m.menu_id from xs_menu m where m.menu_name='客户管理' and m.sys_organization=sysOrg)

connect by prior m.menu_id = m.upper_menu_id)

) a where a.menu_id=ls.menu_id and ls.sys_organization=sysOrg);

  

  

  --删除成功返回

   outPut:=0;

   commit;

   end if;

exception

  when others then

  ROLLBACK;

  --失败

    outPut:=1;

end pr_initCustManageMenu;

 

 

客户端调用代码：使用了spring的 JdbcDaoSupport

@Override

    public int initCustManageMenu(final String flag, final String sysOrg) {

       String sql = "{call pr_initCustManageMenu(?,?,?)}";  

        Object obj=getJdbcTemplate().execute(sql,new CallableStatementCallback(){  

            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {  

                cs.setString(1,flag);

                cs.setString(2,sysOrg);

                cs.registerOutParameter(3,Types.INTEGER);

                cs.execute();

                return  cs.getInt(3);

            }     

        });

        return Integer.valueOf(obj.toString());

    }