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学习(十二)：存储过程/存储函数

最新文章

热门文章