存储过程，存储函数和触发器

一.存储过程

1.定义

指存储在数据库中供所有用户程序调用的子程序叫存储过程，存储函数。

2.语法

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

AS   PLSQL子程序体;

3.实例

1).入门程序--创建存储过程

create or replace PROCEDURE helloworld
AS
BEGIN  
 dbms_output.put_line('Hello,world');
END;

调用存储过程:

BEGIN
    HELLOWORLD();
END;

2)例：给员工涨工资

CREATE OR REPLACE
PROCEDURE raiseSalary(eno IN NUMBER) IS
    psal emp.sal%TYPE;
BEGIN
        SELECT sal INTO psal FROM emp WHERE empno = eno;
        UPDATE emp SET sal=sal+100 WHERE empno = eno;
        dbms_output.put_line('涨前:'||psal||'    ,涨后:    '||(psal+100));
END raiseSalary;

二.存储函数

 例):查询某个员工的年收入

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 queryEmpIncome;

存储过程和函数的IN和OUT

在过程和函数中可以通过out指定一个或多个输出参数，利用out参数返回多个值

使用原则:若只返回一个值用函数，若返回多个值用存储过程out返回

三.存储过程与存储函数的区别

四.Java程序调用存储过程

1.调用存储函数

 public void testProcedure(){
        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+",薪水:"+sal+",职位:"+job);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,call,null);
        }

    }

2.调用存储函数

 public void testFun(){
        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.包头

CREATE OR REPLACE 
PACKAGE mypackage AS 
    TYPE empcursor IS REF CURSOR;
    PROCEDURE queryEmpList(dno IN NUMBER,empList OUT empcursor);
end mypackage; 

2.包体

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;
end MYPACKAGE; 

3.java调用

public void packageTest(){
        String sql = "{call mypackage.queryEmpList(?,?)}";
        Connection conn = null;
        CallableStatement call = null;

        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);
            call.setInt(1,10);
            call.registerOutParameter(2,OracleTypes.CURSOR);
            call.execute();//执行
            OracleCallableStatement oraCall = (OracleCallableStatement) call;
            ResultSet rs = oraCall.getCursor(2);
            while (rs.next()){
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                System.out.println("姓名:"+ename+",工资:"+sal);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,call,null);
        }
    }

 六.触发器

1.定义

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

2.触发器的应用

1）数据确认

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

3)  做审计,跟踪表上所做的数据操作等

4)  数据的备份和同步

3.触发器的语法

CREATE [OR REPLACE] TRIGGER 触发器名

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE  [OF 列名称]}

ON 表名

[FOR EACH ROW [WHERE条件]]

PLSQL块

4.触发器的类型

1）语句级触发器

在指定操作之前或之后执行一次，不管这条语句影响了多少行

2）行级触发器

触发语句作用的每一条记录都被触发，在行级触发器中使用:old和:new伪记录变量。

5.入门程序:插入后打印

CREATE trigger firstTrigger
AFTER INSERT
ON emp
DECLARE
BEGIN
    dbms_output.put_line('成功插入新员工');
END;