首页 > 代码库 > Oracle学习(十二):存储过程/存储函数

Oracle学习(十二):存储过程/存储函数

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;//垃圾回收:能否够通过代码干预垃圾回收?
			}
		}
		
	}
}



Oracle学习(十二):存储过程/存储函数