oracle 存储过程 存储函数

一：

定义：指存储在数据库中供所有用户程序调用的子程序，叫存储过程、存储函数。

相同点：完成特定功能的程序

不同点：是否用return语句返回值，存储过程无return返回值，存储函数有return返回值。

二：创建和调用存储过程

创建：create or replace procedure 命令

语法：create or replace procedure 过程名（参数列表）

           as

          PLSQL子程序体

调用：两种方式

           1.exec 过程名（参数列表）;

           2.begin

                   过程名（参数列表）;

                   过程名（参数列表）;

               end;

                /

例子：1 无参存储过程，打印hellow world

create or replace procedure sayhellowworld

as

begain

   dbms_output.put_line("hellowworld");

end;

/

commit;

调用： 1.exec sayhellowworld();

           2.begin

                   sayhellowworld();

                   sayhellowworld();

               end;

                /

2 有参in存储过程：给某个员工加100工资，应打印出涨前涨后薪水。

create or replace procedure raisesalary(eno in number)

as

--定义一个变量psal保存涨前薪水，变量类型引用员工表的sal字段类型

psal emp.sal%type；

begin

  --得到涨前薪水,并赋值给变量psal

select sal into psal  from emp where empno=eno;

update emp set sal=sal+100 where empno=eno;

--注意：一般不在存储过程或者存储函数中commit和rollback，原则谁调用谁提交，保证事务的原子性

dbms_output.put_line(’涨前‘||psal||‘；涨后’||(psal+100))；

end；

/

commit；

调用: begin

             raisesalary(12);

             raisesalary(45);

             commit;

         end;

           /

三：创建和调用存储函数

定义：function为命名的一存储程序，可带参数，并返回一计算值。

与存储过程类似，但必须有一返回值return。

语法：create or replace function 函数名（参数列表）

return 函数值类型

as

begin

end；

/

例子：查询某个员工的年收入

create or replace function queryempincome(eno in number)

retrun number

as

--定义变量保存员工的薪水和奖金

psal emp.sal%type;

pcomm emp.comm%type;

begain

--得到员工的月薪和奖金

select sal,comm into psal, pcomm from emp where empno=eno;

--直接返回年收入

return sal*12+nvl(comm,0);

end;

/

四：in out 参数

一般来讲，存储过程没有返回值，存储函数有一个返回值；存储过程和存储函数都可以通过out来制定一或多个输出参数，这样就可以实现多个返回值。

1 都可以有out  2可以有多个out 3存储过程通过out可以实现返回值

原则：只有一个返回值，用存储函数，有多个返回值用存储过程。

create or replace procedure queryempinform(eno in number, penname out varchar2, pensal out number)

as

begin

select name，sal into penname,pensal from emp where empno=eno;

end;

/

五.应用程序调用存储过程

1 数据库连接类

public class JDBCUtil{
        private static String driver = "oracle.jdbc.OracleDriver";
        private static String url = "jdbc:oracle:thin:@192.168.56.12:1521:orcl";
        private static String user = "scott";
        private static String password = "tiger";
        
        //注册数据库的驱动
        static{
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                throw new ExceptionInInitializerError(e);
            }
        }
        //获取数据库链接
        public static Connection getConnection(){
            try {
                return DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
        //释放数据库资源
        public static void release(Connection conn, Statement state, ResultSet rs){
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    rs=null;
                }
            }
            if(state!=null){
                try {
                    state.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    state=null;
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    conn=null;
                }
            }
        }
    }

2 调用测试类

public class TestProcedure{
        /*create or replace procedure queryempinform(eno in number, penname out varchar2, pensal out number)*/
        @Test
        public void testProcedure(){
            String sql = "{call queryempinform(?,?,?,?)}";
            Connection conn= null;
            CallableStatement call = null;
            try {
                //得到一个链接
                conn = JDBCUtil.getConnection();
                //通过链家而船建statement
                call = conn.prepareCall(sql);
                //对in参数赋值
                call.setInt(1,234);
                //对out参数申明
                call.registerOutParameter(2, OracleTypes.VARCHAR);
                call.registerOutParameter(3, OracleTypes.NUMBER);
                //执行调用
                call.execute();
                //却出结果
                String name = call.getString(2);
                Double sal = call.getDouble(3);
            } catch (Exception e) {
                e.printStackTrace();
            } finally{
                JDBCUtil.release(conn, call, null);
            }   
        }
    }

六.应用程序调用存储函数

public class TestFunction{
        /*create or replace function queryempincome(eno in number)*/
        @Test
        public void testFunction(){
            String sql = "{?=call queryempinform(?)}";
            Connection conn= null;
            CallableStatement call = null;
            try {
                //得到一个链接
                conn = JDBCUtil.getConnection();
                //通过链家而船建statement
                call = conn.prepareCall(sql);
               //对于输出参数，申明

               call.registerOutParameter(1, OracleTypes.NUMBER);

                //对于输入参数，赋值
                call.setInt(2,254);
                //执行调用
                call.execute();
                //却出结果
                Double sal = call.getDouble(3);
            } catch (Exception e) {
                e.printStackTrace();
            } finally{
                JDBCUtil.release(conn, call, null);
            }   
        }
    }

七 在out参数中使用光标（光标代表集合）

返回集合：声明包结构（包头和包体）

包头：create or replace package mypackage

           as

                --声明 自定义类型empcursor  指向光标

                type empcursor is ref cursor；

                 --存储过程queryEmpList 返回参数的类型为自定义的empcursor

                procedure queryEmpList (dno in number, empList  out empcursor );

            end mypackage;

包体：需要实现包头中申明的所有方法

           create or replace package body mypackage

           as

                procedure queryEmpList (dno in number, empList  out empcursor )

                as

                begin

                   --打开光标（返回参数empList是个光标，光标在使用前需要open， 为 select * from emp where deptno = dno的结果集）

                   open empList  for select * from emp where deptno = dno;

                end  queryEmpList ;

          end  mypackage;

八 应用程序调用包下的存储过程(必须加包名)

public class TestCursor{
          /*

          create or replace package mypackage

           as

                type empcursor is ref cursor；

                procedure queryEmpList (dno in number, empList  out empcursor );

            end mypackage;

        */
        @Test
        public void testCursor(){
            String sql = "{call mypackage.queryEmpList (?,?)}";
            Connection conn= null;
            CallableStatement call = null;

            ResultSet rs = null；
            try {
                //得到一个链接
                conn = JDBCUtil.getConnection();
                //通过链家而船建statement
                call = conn.prepareCall(sql);
                //对in参数赋值
                call.setInt(1,10);
                //对out参数申明 光标
                call.registerOutParameter(2, OracleTypes.CURSOR);
                //执行调用
                call.execute();
                //却出结果

                rs = ((OracleCallableStatement)call).getCursor(2);  --返回值为第二个参数

                while(rs.next !=null){

                      String name = rs.getString("ename");

                     Doublesal= rs.getDouble("sal");

                     Int empno= rs.getInt("empno");

                } 
            } catch (Exception e) {
                e.printStackTrace();
            } finally{
                JDBCUtil.release(conn, call, null);
            }   
        }
    }