Oracle存儲過程和自定義函數(shù)詳解

1、create or replace procedure PrintStudents(p_staffName in xgj_test.username%type) ascursor c_testData isselect t.sal, m from xgj_test t where t.username = p_staffName;beginfor v_info in c_testData loopDBMS_OUTPUT.PUT_LINE(v_info.sal | | v_m);end loop;end PrintStudents;beginPrintStudents(Computer Scie

2、nce);PrintStudents(Match);end;/exec PrintStudents(Computer Science);exec PrintStudents(Match);pl/sqlsqlcreate or replace procedure procedure_name( argument IN | OUT | IN OUT type,.argument IN | OUT | IN OUT type ) IS | ASprocedure_body/*參數(shù)的存過打印hello world調存儲過程：1. exec sayhelloworld();2 beginsayhello

3、world();end;/*/create or replace procedure sayhelloworldas說明部分begindbms_output.put_line(hello world);end sayhelloworld;SQL set serveroutput on ;SQL exec sayhelloworld();hello worldPL/SQL procedure successfully completedSQL begin2 sayhelloworld();3 sayhelloworld();4 end;5 /hello worldhello worldPL/SQ

4、L procedure successfully completed/*創(chuàng)建個帶參數(shù)的存儲過程給指定的員增加資，并打印增長前后的資*/create or replace procedure addSalary(staffName in xgj_test.username%type )as定義個變量保存調整之前的薪oldSalary xgj_test.sal%type;begin查詢員漲之前的薪select t.sal into oldSalary from xgj_test t where t.username=staffName;調整薪update xgj_test t set t.sal

5、= sal+1000 where t.username=staffName ;輸出dbms_output.put_line(調整之前的薪:| oldSalary | 調整之后的薪: | (oldSalary + 1000);end addSalary;beginaddSalary(xiao);addSalary(gong);commit;end ;/create or replace function function_name( argument IN | OUT | IN OUT type,.argument IN | OUT | IN OUT type ) RETURN IS | ASf

6、unction_body /*查詢員的年薪 （資*12 + 獎）*/create or replace function querySalaryInCome(staffName in varchar2)return number as定義變量保存員的資和獎pSalary xgj_test.sal%type;pComm xgj_m%type;begin查詢員的資和獎select t.sal, minto pSalary, pCommfrom xgj_test twhere t.username = staffName;直接返回年薪return pSalary * 12 + pComm;end q

7、uerySalaryInCome;nvl為create or replace function querySalaryInCome(staffName in varchar2)return number as定義變量保存員的資和獎pSalary xgj_test.sal%type;pComm xgj_m%type;begin查詢員的資和獎select t.sal, minto pSalary, pCommfrom xgj_test twhere t.username = staffName;直接返回年薪return pSalary * 12 + nvl(pComm,0);end querySa

8、laryInCome;/*根據(jù)員姓名，查詢員的全部信息*/create or replace procedure QueryStaffInfo(staffName in xgj_test.username%type,pSalout number,pComm out xgj_m%type,pJob out xgj_test.job%type)isbegin查詢該員的薪資，獎和職位select t.sal,m,t.job into pSal,pComm,pJob from xgj_test t where t.username=staffName;end QueryStaffInfo;Javaor

9、aclejar - - - it - - - - it - import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtils / 設定數(shù)據(jù)庫驅動，數(shù)據(jù)庫連接地址端名稱，戶名，密碼private static final String driver = oracle.jdbc.driver.OracleDriver;private static f

10、inal String url = jdbc:oracle:thin:ip:xxxx;private static final String username = xxxx;private static final String password = xxxx;/* 注冊數(shù)據(jù)庫驅動*/static try Class.forName(driver); catch (ClassNotFoundException e) throw new ExceptionInInitializerError(e.getMessage();/* 獲取數(shù)據(jù)庫連接*/public static Connection

11、getConnection() try Connection connection = DriverManager.getConnection(url, username, password);/ 成功，返回connectionreturn connection; catch (SQLException e) e.printStackTrace();/ 獲取失敗，返回nullreturn null;/* 釋放連接*/public static void cleanup(Connection conn, Statement st, ResultSet rs) if (rs != null) tr

12、y rs.close(); catch (SQLException e) e.printStackTrace(); finally rs = null;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;APIimport java.sql.CallableSta

13、tement;import java.sql.Connection;import java.sql.SQLException;import org.junit.Test;import com.turing.oracle.dbutil.DBUtils;import oracle.jdbc.OracleTypes;public class TestProcedure Testpublic void callProcedure()/ call (, .)Connection conn = null ;CallableStatement callableStatement = null ;/*根據(jù)員姓

14、名，查詢員的全部信息create or replace procedure QueryStaffInfo(staffName in xgj_test.username%type,pSalout number,pComm out xgj_m%type,pJob out xgj_test.job%type)isbegin-查詢該員的薪資，獎和職位select t.sal,m,t.job into pSal,pComm,pJob from xgj_test t where t.username=staffName;end QueryStaffInfo;*/ 我們可以看到該存過4個參數(shù) 1個參 3個出

15、參String sql = call QueryStaffInfo(?,?,?,?);try / 獲取連接conn = DBUtils.getConnection();/ 通過連接獲取到CallableStatementcallableStatement = conn.prepareCall(sql);/ 對于in 參數(shù)，需要賦值callableStatement.setString(1, xiao);/ 對于out 參數(shù)，需要聲明callableStatement.registerOutParameter(2, OracleTypes.NUMBER); / 第個 ？callableState

16、ment.registerOutParameter(3, OracleTypes.NUMBER);/ 第三個 ？callableStatement.registerOutParameter(4, OracleTypes.VARCHAR);/ 第四個 ？/ 執(zhí)調callableStatement.execute();/ 取出結果int salary = callableStatement.getInt(2);int comm = callableStatement.getInt(3);String job = callableStatement.getString(3);System.out.p

17、rintln(salary + t + comm + t + job); catch (SQLException e) e.printStackTrace();finally DBUtils.cleanup(conn, callableStatement, null);APIimport java.sql.CallableStatement;import java.sql.Connection;import org.junit.Test;import com.turing.oracle.dbutil.DBUtils;import oracle.jdbc.OracleTypes;public c

18、lass TestFuction Testpublic void callFuction()/?= call (, .)Connection conn = null;CallableStatement call = null;/* create or replace function querySalaryInCome(staffName in varchar2)return number as-定義變量保存員的資和獎pSalary xgj_test.sal%type;pComm xgj_m%type;begin-查詢員的資和獎select t.sal, minto pSalary, pCom

19、mfrom xgj_test twhere t.username = staffName;-直接返回年薪return pSalary * 12 + nvl(pComm,0);end querySalaryInCome;*/String sql = ?=call querySalaryInCome(?);try / 獲取連接conn = DBUtils.getConnection();/ 通過conn獲取CallableStatementcall = conn.prepareCall(sql);/ out 參數(shù)，需要聲明call.registerOutParameter(1, OracleTyp

20、es.NUMBER);/ in 參數(shù)，需要賦值call.setString(2, gong);/ 執(zhí)call.execute();/ 取出返回值 第個？的值double income = call.getDouble(1);System.out.println(該員的年收： + income); catch (Exception e) e.printStackTrace();finally DBUtils.cleanup(conn, call, null);create or replace package MyPackage is- Author : ADMINISTRATOR- Creat

21、ed : 2016-6-4 18:10:42- Purpose :- 使type關鍵字 is ref cursor說明是cursor類型type staffCursor is ref cursor;procedure queryStaffJob(pJob in xgj_test.job%type,jobStaffList out staffCursor);end MyPackage;create or replace package body MyPackage isprocedure queryStaffJob(pJob in xgj_test.job%type,jobStaffList o

22、ut staffCursor)asbeginopen jobStaffList for select * from xgj_test t where t.job=pJob;end queryStaffJob;end MyPackage;plsqlimport java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import org.junit.Test;import com.turing.oracle.dbutil.DBUtils;import oracle.jdbc.OracleTypes;import oracle.jdbc.driver.OracleCallableStatement;public class TestCursor Testpublic void testCursor()/* create or replace package MyPackage istype staffCursor is ref cursor;procedure queryStaffJob(pJob in xgj_test.jo