Oracle存储过程和自定义函数详解

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/*参数的存过打印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/*创建个带参数的存储过程给指定的员增加资，并打印增长前后的资*/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;/*根据员姓名，查询员的全部信息*/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 / 设定数据库驱动，数据库连接地址端名称，户名，密码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;/* 注册数据库驱动*/static try Class.forName(driver); catch (ClassNotFoundException e) throw new ExceptionInInitializerError(e.getMessage();/* 获取数据库连接*/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 ;/*根据员姓

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个参数 1个参 3个出

15、参String sql = call QueryStaffInfo(?,?,?,?);try / 获取连接conn = DBUtils.getConnection();/ 通过连接获取到CallableStatementcallableStatement = conn.prepareCall(sql);/ 对于in 参数，需要赋值callableStatement.setString(1, xiao);/ 对于out 参数，需要声明callableStatement.registerOutParameter(2, OracleTypes.NUMBER); / 第个 ？callableState

16、ment.registerOutParameter(3, OracleTypes.NUMBER);/ 第三个 ？callableStatement.registerOutParameter(4, OracleTypes.VARCHAR);/ 第四个 ？/ 执调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 参数，需要声明call.registerOutParameter(1, OracleTyp

20、es.NUMBER);/ in 参数，需要赋值call.setString(2, gong);/ 执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