Oracle数据库（十一）存储过程及Java程序调用存储过程

存储过程procedure

--打印Hello World
/*
调用存储过程:
1. exec sayhelloworld();2. beginsayhelloworld();sayhelloworld();end;/*/
create or replace procedure sayhelloworld
as--说明部分
begindbms_output.put_line('Hello World');
end;
/

--给指定的员工涨100，并且打印涨前和涨后的薪水
create or replace procedure raiseSalary(eno in number)
as--定义变量保存涨前的薪水psal emp.sal%type;
beginselect sal into psal from emp where empno=eno;update emp set sal = sal+100 where empno=eno;--要不要commit？ 一般不要dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));end;
/

--查询某个员工的姓名 月薪和职位/*
1. 查询某个员工的所有信息  --> out参数太多
2. 查询某个部门中所有员工的所有信息  ---> 集合
*/
create or replace procedure queryempinfo(eno in number,pename out varchar2,psal       out number,pjob      out varchar2)
as                                                                  
beginselect ename,sal,empjob into pename,psal,pjob from emp where empno=eno;end;
/

存储过程中out参数如何返回集合

2. 查询某个部门中所有员工的所有信息  ---> 集合包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS type empcursor is ref cursor;procedure queryEmpList(dno in number,empList out empcursor);END MYPACKAGE;包体
CREATE OR REPLACE PACKAGE BODY MYPACKAGE ASprocedure queryEmpList(dno in number,empList out empcursor) ASBEGINopen empList for select * from emp where deptno=dno;END queryEmpList;END MYPACKAGE;

存储函数function

--查询某个员工的年收入
create or replace function queryempincome(eno in number)
return number
as--月薪和奖金psal emp.sal%type;pcomm emp.comm%type;
beginselect sal,comm into psal,pcomm from emp where empno=eno;--返回年收入return psal*12+nvl(pcomm,0);
end;
/

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

JAVA程序中调用存储过程

package demo.oracle.utils;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:@192.168.26.11:1521:orcl";private static String user = "scott";private static String password = "tiger";static{try {Class.forName(driver);   //DriverManager.registerDriver(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* * 技术方向：* 1. 性能调优  --> tomcat* 2. 故障诊断 --> 死锁(JDK ThreadDump)*              win: ctrl+break*              linux: kill -3 pid*/public static void release(Connection conn,Statement st,ResultSet rs){if(rs!=null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}finally{rs = null; //   ----> java GC}}if(st!=null){try {st.close();} catch (SQLException e) {e.printStackTrace();}finally{st = null;}}if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}finally{conn = null;}}}
}

package demo.oracle.testdemo;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;import org.junit.Test;import demo.oracle.utils.JDBCUtils;public class TestOracle {
/** create or replace procedure queryempinfo(eno in number,pename out varchar2,psal       out number,pjob      out varchar2)*/@Testpublic 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);//对于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);System.out.println(name+"\t"+sal+"\t"+job);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}/** create or replace function queryempincome(eno in number)
return number	*/@Testpublic 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);//对于out参数，申明call.registerOutParameter(1, OracleTypes.NUMBER);//对于in参数，赋值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);}		}@Testpublic 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, 20);//申明outcall.registerOutParameter(2, OracleTypes.CURSOR);//执行call.execute();//取出结果rs = ((OracleCallableStatement)call).getCursor(2);while(rs.next()){String name = rs.getString("ename");double sal = rs.getDouble("sal");System.out.println(name+"\t"+sal);}}catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, rs);}	}
}