Oracle学习：存储过程/存储函数

来源：互联网发布：长沙少儿编程培训机构编辑：程序博客网时间：2024/06/17 23:07

1.知识点

[sql] view plain copy
--第一个存储过程  
/*  
打印Hello World  
create [or replace] PROCEDURE 过程名(参数列表)    
AS   
        PLSQL子程序体；  
调用存储过程:  
1. exec sayHelloWorld();  
2. begin  
      sayHelloWorld();  
      sayHelloWorld();  
   end;  
   /  
*/  
create or replace procedure sayHelloWorld<span style="white-space:pre">   </span>--sayHelloWorld为过程名  
as  
--declare  
--变量说明  
begin  
  dbms_output.put_line('Hello World');  
end;  
/  
----------------------------------------------  
/*  
给指定的员工涨100的工资，并打印涨前和涨后的薪水  
  
create [or replace] PROCEDURE 过程名(参数列表)  --in为输入参数，out为输出参数  
AS   
        PLSQL子程序体；  
          
SQL> begin  
  2     raiseSalary(7839);  
  3     raiseSalary(7566);  
  4     commit;  
  5  end;  
  6  /  
涨前:7986  涨后:8086  
涨前:5024.53  涨后:5124.53  
  
PL/SQL 过程已成功完成。          
*/  
create or replace procedure raiseSalary(eno in number)<span style="white-space:pre">  </span>--带输入参数的存储过程  
as  
  --变量  
  psal emp.sal%type;  
begin  
  --得到涨前薪水  
  select sal into psal from emp where empno=eno;  
    
  --涨工资  
  update emp set sal=sal+100 where empno=eno;  
  --问题：要不要commit？？答：不需要，因为整个事务还没有结束，等调用此存储过程后，再commit  
  
  --打印  
  dbms_output.put_line('涨前:'||psal||'  涨后:'||(psal+100));  
    
end;  
/  
-------------------------------------------------------------------  
/*  
查询某个员工的年收入  
  
CREATE [OR REPLACE] FUNCTION 函数名(参数列表)   
 RETURN  函数值类型  
AS  
PLSQL子程序体；  
  
*/  
create or replace function queryEmpIncome(eno in number)  
return number<span style="white-space:pre">   </span>  
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;  
/  
---------------------------------------------------------------------  
--OUT参数  
/*  
查询某个员工的姓名 月薪 职位  
  
*/  
create or replace procedure queryEmpInfo(eno in number,  
                                         pename out varchar2,  
                                         psal   out number,  
                                         pjob   out varchar2)  
as  
begin  
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;  
  
end;  
/  
--------------------------------------------------------------------  
--查询某个部门中所有员工的所有信息  
  
--1.创建一个包：MYPACKAGE   
--2.在该包中定义一个自定义类型：empcursor 类型为游标，一个存储过程：queryemp  
  
CREATE OR REPLACE   
PACKAGE MYPACKAGE AS   
    
  type empcursor is ref cursor; --empcursor是一个自定义类型：引用cursor的类型作为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  
        
      open empList for select * from emp where deptno=dno;  
      
  END queryEmpList;  
  
END MYPACKAGE;  
  
------------------------------------------------------------------  

2.JAVA程序中测试存储过程/存储函数

[java] view plain copy
package demo.util;  
  
import java.sql.CallableStatement;  
import java.sql.Connection;  
import java.sql.ResultSet;  
  
import oracle.jdbc.OracleCallableStatement;  
import oracle.jdbc.OracleTypes;  
  
import org.junit.Test;  
  
/* 
 *  
 * 性能: 
 * Statement < PreparedStatement  < CallableStatement 
 * PreparedStatement：预编译SQL语句，执行的时候告诉参数，至少编辑一次 
 * CallableStatement：完成对存储过程/存储函数的调用，没有编译过程，直接调用 
 */  
public class TestOracle {  
      
    /* 
     * create or replace 
procedure queryEmpInfo(eno in number, 
                                         pename out varchar2, 
                                         psal   out number, 
                                         pjob   out varchar2) 
     */  
    @Test  
    public void testProcedure(){  
        //调用存储过程  
        //{call <procedure-name>[(<arg1>,<arg2>, ...)]}  
        String sql = "{call queryEmpInfo(?,?,?,?)}";  
        Connection conn=null;  
        CallableStatement call = null;  
        try{  
            conn = JDBCUtils.getConnection();  
            call = conn.prepareCall(sql);  
            //赋值  
            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);  
            System.out.println(name);  
            System.out.println(sal);  
            System.out.println(job);  
        }catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            JDBCUtils.release(conn, call, null);  
        }  
    }  
      
  
    /* 
     * create or replace 
function queryEmpIncome(eno in number) 
return number 
     */  
    @Test  
    public void testFunction(){  
        //存储函数例子  
        //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}  
        String sql = "{?=call queryEmpIncome(?)}";  
        Connection conn = null;  
        CallableStatement call = null;  
        try{  
            conn = JDBCUtils.getConnection();  
            call = conn.prepareCall(sql);  
              
            call.registerOutParameter(1, OracleTypes.NUMBER);  
            call.setInt(2, 7839);  
              
            //执行  
            call.execute();  
              
            //取出年收入  
            double income = call.getDouble(1);  
            System.out.println(income);  
        }catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            JDBCUtils.release(conn, call, null);  
        }  
    }  
      
    /* 
     * 问题： 
     * 1. 光标是否被关？:是，结构集关掉后光标就关掉了。 
     * 2. 是否能在MYSQL上执行？：不能 
     */  
    @Test  
    public void testCursor(){  
        String sql = "{call MYPACKAGE.queryEmpList(?,?)}";  
        Connection conn = null;  
        CallableStatement call = null;  
        ResultSet rs  =null;  
        try {  
            conn = JDBCUtils.getConnection();  
            call = conn.prepareCall(sql);  
              
            call.setInt(1, 10);  
            call.registerOutParameter(2, OracleTypes.CURSOR);  
              
            //执行  
            call.execute();  
              
            //取出集合  
            rs = ((OracleCallableStatement)call).getCursor(2);  
            while(rs.next()){  
                String name = rs.getString("ename");  
                String job = rs.getString("job");  
                System.out.println(name+"的职位是"+job);  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            JDBCUtils.release(conn, call, rs);  
        }  
    }  
}  

3.用到的JDBC类

[java] view plain copy
package demo.util;  
  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
  
public class JDBCUtils {  
    private static String driver="oracle.jdbc.OracleDriver";  
    private static String url="jdbc:oracle:thin:@localhost: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;  
    }  
      
    /* 
     * 执行java程序 
     * java -Xms100M -Xmx200M HelloWorld 
     */  
    public static void release(Connection conn,Statement stmt,ResultSet rs){  
        if(rs!=null){  
            try {  
                rs.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }finally{  
                rs=null;//垃圾回收：是否可以通过代码干预垃圾回收？  
            }  
        }  
          
        if(stmt!=null){  
            try {  
                stmt.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }finally{  
                rs=null;//垃圾回收：是否可以通过代码干预垃圾回收？  
            }  
        }  
          
        if(conn!=null){  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }finally{  
                rs=null;//垃圾回收：是否可以通过代码干预垃圾回收？  
            }  
        }  
          
    }  
}  

阅读全文

0 0