美文网首页
Oracle学习笔记（六）

Oracle学习笔记（六）

作者: dev_winner | 来源:发表于2019-09-29 23:25 被阅读0次
    • 数据库对象：表、视图、索引、序列、同义词、存储过程、存储函数、包头、包体。
    • 存储过程和存储函数是指在数据库中提供所有用户程序调用的子程序。
    • 相同点：完成特定功能的程序。区别：存储函数可以使用return语句返回值，而存储过程没有返回值。
    • 使用CREATE PROCEDURE命令建立存储过程
    -- 只能创建或替换一个存储过程，不能修改，AS关键字相当于PL/SQL语法中的declare关键字。
CREATE  [OR REPLACE] PROCEDURE 过程名(参数列表) AS PL/SQL子程序体

-- 若此存储过程不存在，则create新的存储过程。
-- 若此存储过程已存在，则replace旧的存储过程。
/*
调用存储过程有2种方式：
1、execute sayhelloworld(); -- execute 简写为exec
2、begin
    sayhelloworld();
    sayhelloworld();
   end;
   /
*/
CREATE OR REPLACE PROCEDURE sayhelloworld
AS 
    -- 说明部分， AS关键字不能省略
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/

-- SQL plus执行过程如下：
SQL> connect scott/123456
已连接。
SQL> exec sayhelloworld();

PL/SQL 过程已成功完成。

SQL> SET SERVEROUTPUT ON;
SQL> exec sayhelloworld();
Hello World!

PL/SQL 过程已成功完成。

SQL> begin
  2      sayhelloworld();
  3      sayhelloworld();
  4     end;
  5  /
Hello World!
Hello World!

PL/SQL 过程已成功完成。

-- 创建一个带参数的存储过程：
-- 给指定员工涨100块钱的工资，并且打印涨前和涨后的薪水,IN关键字表示一个输入参数
/*
如何调用：
BEGIN 
    raisesalary(7839);
    raisesalary(7566);
    COMMIT; -- 这样就保证这些调用都在同一个事务当中
END;
/
*/
CREATE OR REPLACE PROCEDURE raisesalary(eno IN NUMBER) -- 用IN关键字指明一个输入参数
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或者rollback提交回滚事务
    DBMS_OUTPUT.PUT_LINE('涨前：' || psal || '      涨后：' || (psal + 100));
end;
/

-- SQL plus执行过程如下：
SQL> BEGIN
  2      raisesalary(7839);
  3      raisesalary(7566);
  4      COMMIT; -- 这样就保证这些调用在同一个事务当中
  5  END;
  6  /
涨前：8086      涨后：8186
涨前：5124.53      涨后：5224.53

PL/SQL 过程已成功完成。
    • 在调试存储过程中出现一个错误，如下所示；解决方案是使用sys管理员赋予scott用户调试功能的权限，执行命令： GRANT DEBUG CONNECT SESSION, DEBUG ANY PROCEDURE TO SCOTT;
    报错：scott用户没有调试的权限 使用sys管理员授予权限 调试过程
    • java 应用程序调用并执行存储过程和存储函数
    • jdbc.properties配置文件
    url=jdbc:oracle:thin:@localhost:1521:orcl
user=scott
password=123456
driver=oracle.jdbc.OracleDriver
    • 编写存储过程queryempinformation
    -- 测试OUT参数：查询员工姓名、月薪和职位
CREATE OR REPLACE PROCEDURE queryempinformation(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;
    • TestProcedure.java测试代码如下：
    package com.zzw.test;

import com.zzw.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
import org.junit.Test;

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

/**
 * 调用存储过程
 */

public class TestProcedure {

    @Test
    public void testProcedure() {
        Connection conn = null;
        CallableStatement call = null;

        try {
            //1、获取数据库连接
            conn = JDBCUtils.getConnection();
            //2、定义调用存储过程的sql语句： {call <procedure-name>[(<arg1>,<arg2>, ...)]}
            String sql = "{call queryempinformation(?,?,?,?)}";
            //3、获取执行sql对象
            call = conn.prepareCall(sql);
            //4、对于IN参数，需要赋值
            call.setInt(1, 7839);
            //5、对于OUT参数，需要申明为输出参数类型
            call.registerOutParameter(2, OracleTypes.VARCHAR);
            call.registerOutParameter(3, OracleTypes.NUMBER);
            call.registerOutParameter(4, OracleTypes.VARCHAR);
            //6、执行调用
            call.execute();
            //7、取出结果
            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.close(null, call, conn);
        }
    }
}
    执行存储过程的结果
    • 存储函数：函数（FUNCTION）为一命名的存储程序，可带参数，并返回一个计算值，函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。
    -- 语法：创建一个存储函数
CREATE [OR REPLACE] FUNCTION 函数名(参数列表) RETURN 返回值类型 AS ... PL/SQL子程序体;

-- 创建一个存储函数，用于查询某个员工的年收入
CREATE OR REPLACE FUNCTION queryempincome(eno IN number) RETURN NUMBER
AS
    -- 定义2个变量分别保存员工的薪水和奖金
    psal emp.sal%type;
    pcomm emp.comm%type;
BEGIN
    SELECT sal, comm into psal, pcomm FROM emp WHERE empno = eno;
    -- 若一个表达式中含有空值，则表达式的值就为空
    -- NVL(expr1,expr2)函数是一个空值转换函数，若expr1不为空值则输出本身，否则返回指定值expr2。
    RETURN psal * 12 + NVL(pcomm, 0);    
END;
/
    成功查询某个员工的年收入
    • TestFunction.java测试编写如下：
    package com.zzw.test;

import com.zzw.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
import org.junit.Test;

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

/**
 * 调用存储函数
 */

public class TestFunction {
    @Test
    public void testFunction() {
        Connection conn = null;
        CallableStatement call = null;
        try {
            //1、获取数据库连接
            conn = JDBCUtils.getConnection();
            //2、定义调用存储函数的sql语句： {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
            String sql = "{?=call queryempincome(?)}";
            //3、获取执行sql对象
            call = conn.prepareCall(sql);
            //4、对于OUT参数（返回值），需要申明为输出参数类型
            call.registerOutParameter(1, OracleTypes.NUMBER);
            //5、对于IN参数，需要赋值
            call.setInt(2, 7839);
            //6、执行调用
            call.execute();
            //7、取出某个员工的年收入
            double income = call.getDouble(1);
            System.out.println("该员工的年收入是：" + income);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, call, conn);
        }
    }
}
    执行存储函数的结果
    • 存储过程和存储函数都可通过out关键字指定一个或多个输出参数。
    • 存储过程可通过out参数来实现返回值。
    • 使用原则：若只有一个返回值，则用存储函数；否则就用存储过程。
    成功查询员工号为7839的一些信息
    • 在OUT参数中使用光标，需要声明包结构（包头只负责声明，包体只负责实现）
    -- 语法：包头
CREATE OR REPLACE PACKAGE 包名 AS ... END 包名;

-- 声明包头
CREATE OR REPLACE 
PACKAGE MYPACKAGE AS 
    -- 创建一个变量empcursor，它引用了游标类型，除了empcursor变量名外，其余全是关键字。
    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
    -- 打开光标，关键字for代表某个关键集合
    OPEN empList for SELECT * FROM emp WHERE deptno = dno;
  END queryEmpList;
END MYPACKAGE;
    • TestCursor.java测试编写如下：
    package com.zzw.test;

import com.zzw.utils.JDBCUtils;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import org.junit.Test;

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

/**
 * 查询出结果集合
 */

public class TestCursor {

    @Test
    public void testCursor() {
        Connection conn = null;
        CallableStatement call = null;
        ResultSet rs = null;

        try {
            //1、获取数据库连接
            conn = JDBCUtils.getConnection();
            //2、定义调用存储过程的sql语句： {call <procedure-name>[(<arg1>,<arg2>, ...)]}
            String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
            //3、获取执行sql对象
            call = conn.prepareCall(sql);
            //4、对于IN参数，需要赋值
            call.setInt(1, 20);
            //5、对于OUT参数，需要申明输出参数的类型
            call.registerOutParameter(2, OracleTypes.CURSOR);
            //6、执行调用
            call.execute();
            //7、从结果集循环取出每条记录
            rs = ((OracleCallableStatement) call).getCursor(2);
            while(rs.next()) {
                //取出该员工的员工号，姓名，薪水和职位
                int empno = rs.getInt("empno");
                String name = rs.getString("ename");
                double salary = rs.getDouble("sal");
                String job = rs.getString("empjob");
                System.out.println(empno + "\t" + name + "\t" + salary + "\t" + job);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, call, conn);
        }
    }
}
    执行某个包中存储过程的结果
    • 触发器(trigger)是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
    -- 每当成功插入新员工信息后，自动打印插入成功信息
SQL> CREATE TRIGGER saynewemp
  2  AFTER INSERT ON emp
  3  DECLARE
  4  BEGIN
  5      DBMS_OUTPUT.PUT_LINE('成功插入一名新员工！');
  6  END;
  7  /

触发器已创建

SQL> SET SERVEROUTPUT ON;
SQL> INSERT INTO emp(empno, ename, sal, deptno) VALUES(1001, 'Tom', 3000, 10);
成功插入一名新员工！

已创建 1 行。
    • 触发器的具体应用场景：复杂的安全性检查；数据的确认；数据库的审计；数据的备份和同步。触发器是同步备份，快照是异步备份。
    CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE | [OF 列名]}
ON 表名
-- 有下面这条语句就是行级触发器
[FOR EACH ROW [WHEN(condition)]]
PL/SQL块
    • 触发器的类型：语句级触发器和行级触发器。
    • 语句级触发器：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行，针对的是表。
    • 行级触发器：触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量，识别值的状态，针对的是行。
    -- 实施安全性检查：（语句级触发器）
-- 测试例子：禁止在非工作时间内添加新员工
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
    -- 禁止insert新员工，error_number_in的值允许为-20999到-20000，这样就不会与ORACLE的任何错误代码发生冲突
    RAISE_APPLICATION_ERROR(-20001, '禁止在非工作时间内添加新员工！');
    END IF;
END;
/

-- 数据确认：（行级触发器）
-- 测试例子：涨后的薪水不能少于涨前的薪水
-- :OLD 和 :NEW  代表同一条记录
-- :OLD 表示操作该条记录之前的值，:NEW 表示操作该条记录之后的值
CREATE OR REPLACE TRIGGER checksalary
BEFORE UPDATE
ON emp
FOR EACH ROW 
BEGIN
    IF :NEW.sal < :OLD.sal THEN
        -- 用RAISE_APPLICATION_ERROR(error_number_in IN NUMBER, error_msg_in IN VARCHAR2)函数来自定义异常信息，参数包括错误代码和错误提示信息
        RAISE_APPLICATION_ERROR(-20002, '涨后的薪水不能少于涨前的薪水，涨后的薪水：' || :NEW.sal || '，涨前的薪水：' || :OLD.sal);
    END IF;
END;
/

-- sqlplus执行过程：
SQL> UPDATE emp SET sal = sal - 1 WHERE empno = 7839;
UPDATE emp SET sal = sal - 1 WHERE empno = 7839
*
第 1 行出现错误:
ORA-20002: 涨后的薪水不能少于涨前的薪水，涨后的薪水：8186，涨前的薪水：8187
ORA-06512: 在 "SCOTT.CHECKSALARY", line 3
ORA-04088: 触发器 'SCOTT.CHECKSALARY' 执行过程中出错

-- 数据库的审计：（行级触发器）  ---> 基于值的审计功能
-- 测试例子：给员工涨工资，当涨后的薪水超过6000块钱时，审计该员工的信息
-- 创建一张新表，用于保存审计信息
SQL> CREATE TABLE audit_info
  2  (
  3      information varchar2(200)
  4  );

表已创建。

-- 创建一个触发器
CREATE OR REPLACE TRIGGER do_audit_emp_salary 
AFTER UPDATE
ON emp
FOR EACH ROW 
BEGIN
    IF :NEW.sal > 6000 THEN 
        INSERT INTO audit_info VALUES(:NEW.empno || ' ' || :NEW.ename || ' ' || :NEW.sal);
    END IF;
END;
/

-- sqlplus执行过程：
SQL> UPDATE emp SET sal = sal + 2000;

已更新16行。

SQL> COMMIT;

提交完成。
    基于值的审计测试结果 
    -- 数据的备份和同步：（行级触发器）
-- 测试例子：当给员工涨完工资后，自动备份新的工资到备份表中
CREATE OR REPLACE TRIGGER sync_salary
AFTER UPDATE
ON emp
FOR EACH ROW
BEGIN
    -- 当主表更新后，自动更新备份表
    UPDATE emp_back SET sal = :NEW.sal WHERE empno = :NEW.empno;
END;
/

-- sqlplus 执行过程：
SQL> SELECT sal FROM emp WHERE empno = 7839;
       SAL
----------
     10187

SQL> SELECT sal FROM emp_back WHERE empno = 7839;
       SAL
----------
     10187

SQL> UPDATE emp set sal = sal + 10 WHERE empno = 7839;
已更新 1 行。

SQL> COMMIT;
提交完成。

SQL> SELECT sal FROM emp WHERE empno = 7839;
       SAL
----------
     10197

SQL> SELECT sal FROM emp_back WHERE empno = 7839;
       SAL
----------
     10197

    相关文章

      网友评论

          本文标题：Oracle学习笔记（六）

          本文链接：https://www.haomeiwen.com/subject/rwiqpctx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|Oracle学习笔记（六）|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！