存储过程和存储函数

一.调用存储过程：
1. exec sayHelloWorld();
2. begin
sayHelloWorld();
sayHelloWorld();
end;
3.带参数的存储过程

 --给指定的员工涨100，并且打印涨前和涨后的薪水
 create or replace procedure raiseSalary(eno in number)
as
   --定义变量保存涨前的薪水
   psal emp.sal%type;
begin
   --得到涨前的薪水
   select sal into psal from emp where empno=eno;

   --涨100
   update emp set sal=sal+100 where empno=eno;

   --要不要commit？

   dbms_output.put_line('涨前：'||psal||'  涨后:'||(psal+100));
end;

4.存储函数

 --查询某个员工的年收入
 create or replace function queryEmpIncome(eno in number)
 return number
 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;

5.out参数

/*
查询某个员工的姓名 月薪 职位
*/
create or replace procedure queryEmpInformation(eno in number,
                                                pename out varchar2,
                                                psal   out number,
                                                pjob   out varchar2)
as
begin

   select ename,sal,job into pename,psal,pjob from emp where empno=eno;                                               
end;

问:什么时候用存储过程/存储函数？
原则：如果只有一个返回值，用存储函数；否则，就用存储过程。
5.在out参数中使用光标

 查询某个部门中的所有员工信息  ---> 返回集合

包头

CREATE OR REPLACE PACKAGE MYPACKAGE AS 

  type empcursor is ref cursor;
  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;

6.JDBCUtils工具类

package demo;

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:@192.168.137.129:1521/orcl";
    private static String user = "scott";
    private static String password = "tiger";

    static{
        //注册驱动
        try {
            Class.forName(driver);
            //DriverManager.registerDriver(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
     * 
     * 技术方向：
     * 1. 性能优化
     * 2. 故障诊断:死锁 ThreadDump
     */
    public static void release(Connection conn,Statement st,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                rs = null;//-----> Java GC
            }
        }
        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;
            }
        }       
    }
}

7.测试类

package demo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import org.junit.Test;

public class TestOracle {
/*
 * create or replace procedure queryEmpInformation(eno in number,
                                                pename out varchar2,
                                                psal   out number,
                                                pjob   out varchar2)
 */
    @Test
    public void testProcedure(){
        // {call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{call queryEmpInformation(?,?,?,?)}";

        Connection conn = null;
        CallableStatement call = null;
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);

            //对于in参数，赋值
            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+"\t"+sal+"\t"+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);

            //对于out参数，申明
            call.registerOutParameter(1, OracleTypes.NUMBER);

            //对于in参数，赋值
            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);
        }       
    }

    @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);

            //对于in参数，赋值
            call.setInt(1, 20);

            //对于out参数，申明
            call.registerOutParameter(2, OracleTypes.CURSOR);

            //执行
            call.execute();

            //取出结果
            rs = ((OracleCallableStatement)call).getCursor(2);
            while(rs.next()){
                String name = rs.getString("ename");
                double sal = rs.getDouble("sal");
                String job = rs.getString("job");
                System.out.println(name+"\t"+sal+"\t"+job);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, rs);
        }       
    }
}

存储过程的sql语句：

String sql = "{call queryEmp(?,?,?,?)}";

存储函数的sql语句：

String sql = "{?=call queryEmpIncome(?)}";

8.触发器
触发器
1、数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
2、触发器的类型
1）、语句级触发器
在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
2）、行级触发器（FOR EACH ROW）
触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。
创建触发器：
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
PLSQL 块

实例一：限制非工作时间向数据库中插入数据
构建触发器：
create or replace trigger insertEmp
  before insert on emp  
  for each row
declare
  -- local variables here
begin
  if to_char(sysdate,'day') in ('星期六') or to_number(to_char(sysdate,'hh24')) between 8 and 24
    then 
      raise_application_error(-20001,'星期六或者8-24不能插入数据');
      end if;
end insertEmp;

写sql语句触发
SQL> insert into emp(empno,deptno) values (1245,10);
结果：

insert into emp(empno,deptno) values (1245,10)
ORA-20001: 星期六或者8-24不能插入数据
ORA-06512: 在 "SCOTT.INSERTEMP", line 6
ORA-04088: 触发器 'SCOTT.INSERTEMP' 执行过程中出错

触发语句与伪记录变量的值
触发语句 ：old ：new
Insert 所有字段都是空话(null) 将要插入的数据
Update 更新以前该行的值 更新后的值
Delete 删除以前改行的值 所有字段都是空(null)

实例二：确认数据（检查emp表中sal的修改值不低于原值）
构建触发器
create or replace trigger updateEmp
  before update on emp  
  for each row
declare
  -- local variables here
begin
  if :new.sal<:old.sal then
    raise_application_error(-20002,'更改的数据不能小于原有的值');
    end if;
end updateEmp;

书写sql语句触发
SQL> update emp set sal=100 where empno=7369;

结果：
update emp set sal=100 where empno=7369
ORA-20002: 更改的数据不能小于原原有的值
ORA-06512: 在 "SCOTT.UPDATEEMP", line 5
ORA-04088: 触发器 'SCOTT.UPDATEEMP' 执行过程中出错

练习：限制每个部门只招聘5名职工，超过计划则报出错误报告
构建触发器
create or replace trigger insertEmp
  before insert on emp  
  for each row
declare
  -- local variables here
  cursor c1 is select count(*) from emp group by deptno;
  count1 number;
begin
  open c1;
  fetch c1 into count1;
   if 
     count1>=5
   then
     raise_application_error(-20003,'本部门不能再插入了');
   end if;

   --  dbms_output.put_line(count1);
    close c1;
end insertEmp;

书写sql语句触发：
SQL> insert into emp (empno,deptno) values(2435,30);

结果：
insert into emp (empno,deptno) values(2435,30)
ORA-20003: 本部门不能再插入了
ORA-06512: 在 "SCOTT.INSERTEMP", line 11
ORA-04088: 触发器 'SCOTT.INSERTEMP' 执行过程中出错

触发器总结：
1、 触发器可用于
a) 数据确认
b) 实施复杂的安全性检查
c) 做审计，跟踪表上所做的数据操作等
2、 查询触发器、过程及函数
a) Select * from user_triggers;
b) Select * from user_source;