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;//垃圾回收：是否可以通過代碼干預垃圾回收？
}
}
}
}
