1.知识点
--第一个存储过程 /* 打印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程序中测试存储过程/存储函数
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类
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;//垃圾回收：是否可以通过代码干预垃圾回收？ } } } }
