Oracle使用（二）---存储过程，存储函数，触发器

存储过程：

1.语法：

create [or replace] procedure 过程名(参数列表)

as

PLSQL子程序体

2.例子：

create or replace procedure queryStudent(stid in number)
as
stname STUDENT.SNAME%type;
stscore STUDENT.SCORE%type;
begin
select sname,score into stname,stscore from student where sid=stid;
dbms_output.put_line('the name is '||stname||', score is '||stscore);
end;
/

带有out参数的例子：

create or replace procedure queryStudentWithOut(stid in number,stname out varchar2,stscore out number)
as
begin
select sname,score into stname,stscore from student where sid=stid;
end;
/

调用存储过程有两种方式：

1）exec queryStudent(1);
2）在begin---end程序块中执行。

对于带有out参数的存储过程，调用例子：

DECLARE
  STID NUMBER;
  STNAME VARCHAR2(200);
  STSCORE NUMBER;
BEGIN
  STID := 1;
  QUERYSTUDENTWITHOUT(
    STID => STID,
    STNAME => STNAME,
    STSCORE => STSCORE
  );
DBMS_OUTPUT.PUT_LINE('STNAME = ' || STNAME);
DBMS_OUTPUT.PUT_LINE('STSCORE = ' || STSCORE);
END;

java中调用存储过程的例子：

	public static void main(String[] args) {
		
		String sql = "{call queryStudentWithOut(?,?,?)}";
		Connection conn = null;
		CallableStatement call = null;
		
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			call.setInt(1, 1);
			call.registerOutParameter(2, OracleTypes.VARCHAR);
			call.registerOutParameter(3, OracleTypes.NUMBER);
			
			call.execute();
			
			String name = call.getString(2);
			double score = call.getDouble(3);
			System.out.println(name);
			System.out.println(score);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, null);
		}
		
	}

存储函数：

语法：

create [or replace] function 函数名(参数列表)

return 函数返回值类型

as

PLSQL子程序体

例子：

create or replace function queryStudentScore(stid in number)
return number
as
tscore student.score%type;
begin
select score into tscore from student where sid=stid;
return tscore*100;
end;
/

在begin--end语句块中可调用。

java中调用存储函数的例子：

	public static void main(String[] args) {
		
		String sql = "{?=call queryStudentScore(?)}";
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			call.registerOutParameter(1, OracleTypes.NUMBER);
			call.setInt(2, 1);
			
			call.execute();
			
			double score = call.getDouble(1);
			System.out.println(score);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, null);
		}
		
	}

注意java调用存储过程和存储函数时sql的不同。

在out类型的参数存在的情况下，存储过程与存储函数基本没什么区别，但是在使用中应遵循只有一个返回参数使用存储函数，有多个返回参数使用存储过程（只是因为大家都这样使用）。

在oracle sql developer中可以进行调试存储过程与存储函数，需要的权限有debug connect session和debug any procedure, 查看某个用户具有的权限的命令为：

select * from dba_sys_privs where grantee='HURRICANE';

赋予相应权限的命令为：grant debug connect session,debug any procedure to hurricane;

调试应先“编译已进行调试”。

重点：在存储过程返回的结果集有成百上千行时，再使用普通形式的存储过程，需要相应的声明成百上千的out参数，显然是不合适的，在这种情况下，需要结合游标来获取结果集。

在out参数中使用游标：

1）：创建一个包：mypackage

2)在该包中定义一个自定义类型：mycursor 类型为游标（cursor）

  一个存储过程：querystu

create or replace package mypackage as
    type mycursor is ref cursor;
    procedure querystu(stid in number,stout out mycursor);
end mypackage;

create or replace
package body mypackage as
procedure querystu(stid in number,stout out mycursor) as
begin
 open stout for select * from student where sid=stid;
end querystu;
end mypackage;

java调用的代码为：

	public static void main(String[] args) {
		String sql = "{call mypackage.querystu(?,?)}";
		Connection conn = null;
		CallableStatement call = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			call.setInt(1, 1);
			call.registerOutParameter(2, OracleTypes.CURSOR);

			call.execute();

			rs = ((OracleCallableStatement)call).getCursor(2);
			while (rs.next()) {
				System.out.println(rs.getString("sname")+"   "+rs.getDouble("score"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, rs);
		}
	}

触发器：

数据库触发器是一个与表关联的，存储的PL/SQL程序。每当一个特定的数据操作语句（insert，update，delete）在指定的表上发出时，oracle自动地执行触发器中定义的语句序列。

触发器的类型有：

1）语句级触发器，在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行；

 2）行级触发器，触发器语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量，识别值得状态。

语法：

create [or replace] trigger 触发器名

{before|after}

{delete|insert|update[ of 列名]}

on 表名

[for each row [when (条件)]]

PL/SQL块

例子：

create or replace trigger beforeInsertStudent
before insert
on student
for each row
begin
if :new.score>150 then
    raise_application_error(-20002,'分数不能大于150， 分数为'||:new.score);
end if;
end;
/

触发器应用场景：

1）：数据确认

2）：实施复杂的安全性检查

3）：做审计，跟踪表上所做的数据操作等

4）：数据的备份和同步

查询触发器，过程，函数：

select * from user_triggers;

select * from user_source;

触发器案例：

案例一：

/*
示例1：限制非工作时间向数据库插入数据

周末：to_char(sysdate,'day') in ('星期六','星期日')
<9点或者 > 18点：  to_number(to_char(sysdate,'hh24')) not between 9 and 18
*/
create or replace trigger securityEmp
before insert
on emp
begin

  if to_char(sysdate,'day') in ('星期六','星期日','星期三') or 
      to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
      raise_application_error(-20001,'不能在非工作时间插入数据');
  end if;    
end;
/

案例二：

/*
确认数据：涨后的工资不能少于涨前的工资
*/
create or replace trigger checksal
before update
on emp
for each row
begin

  if :old.sal > :new.sal then
    raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水。涨前：'||:old.sal||'  涨后：'||:new.sal);    
  end if;

end;
/

java链接oracle需要的jar包可在 ..\oracle\product\11.2.0\dbhome_1\jdbc\lib下找到

链接路径为：jdbc:oracle:thin:@localhost:1521:实例名

驱动类：oracle.jdbc.OracleDriver

注意JDBCUtils 中链接，结果集等的关闭方式，用try包裹，finally中将其置为空。

package demo.utils;

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  运行HelloWorld最小分配100M内存，
	 */
	public static void release(Connection conn, Statement st,ResultSet rs){
		if(rs != null){
			try {
				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; //??????
			}
		}		
	}
}