Oracle总结【PLSQL学习】（三）

2021-09-20 06:44:11

存储过程和函数的语法

过程的语法：

create [or replace] procedure 过程名[(参数列表)]  
as
        PLSQL程序体；【begin…end;/】

函数的语法：

CREATE [OR REPLACE] FUNCTION 函数名【(参数列表) 】
 RETURN  返回值类型
AS
PLSQL子程序体；
【begin…end;/】

无论是过程还是函数，as关键字都代替了declare关键字。

创建第一个过程：

CREATE OR REPLACE PROCEDURE hello
AS
  BEGIN
    dbms_output.put_line('hello world');
  END;

调用过程的三种方式：

exec过程名【SQLPLUS中使用】
PLSQL程序调用
Java调用

PLSQL调用

BEGIN
  hello();
END;

创建有参存储过程raiseSalary(编号)，为7369号员工涨10%的工资，演示in的用法，默认in，大小写不敏感

CREATE or REPLACE PROCEDURE bb(pempno in NUMBER)
  AS
  BEGIN
    UPDATE EMP
    SET sal = sal * 1.2
    WHERE empno = pempno;
  END;

调用：

BEGIN
    bb(7369);
  END;

创建有参存储过程findEmpNameAndSalAndJob(编号)，查询7788号员工的的姓名，职位，月薪，返回多个值，演示out的用法

创建过程：在过程中的参数，默认值是IN，如果是输出的话，那么我们要指定为OUT。

CREATE OR REPLACE PROCEDURE find(pempno IN NUMBER, psal OUT VARCHAR2, pename OUT VARCHAR2, pjob OUT VARCHAR2)
AS
  BEGIN
    SELECT
      ename,
      sal,
      job
    INTO pename, psal, pjob
    FROM emp
    WHERE empno = pempno;
  END;

调用：在调用的时候，使用到的psal，pname，pjob在调用的时候都没有定义的，因此我们需要先定义变量后使用！

DECLARE
  psal   emp.sal%TYPE;
  pename emp.ename%TYPE;
  pjob   emp.job%TYPE;
BEGIN
  find(7369, psal, pename, pjob);
  dbms_output.put_line(psal || pename || pjob);
END;/

创建有参存储函数findEmpIncome(编号)，查询7369号员工的年收入，演示in的用法，默认in

CREATE OR REPLACE FUNCTION findEmpIncome(pempno IN NUMBER)
  --这里指定的是返回值类型
  RETURN NUMBER
AS
  income NUMBER;
  BEGIN
    SELECT sal * 12
    INTO income
    FROM emp
    WHERE empno = pempno;
    /*在PLSQL中一定要有return语句*/
    RETURN income;
  END;

调用：在PLSQL中，赋值的语句不是直接“=”，而是:=

DECLARE
  income number;
BEGIN
  income := findEmpIncome(7369);
  dbms_output.put_line(income);
END;/

如果写的是=号，那么就会出现以下的错误：

[2017-07-11 13:58:14] [65000][6550] ORA-06550: 第 4 行, 第 10 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
ORA-06550: 第 4 行, 第 31 列: 
PLS-00103: 出现符号 ";"在需要下列之一时：
 . ( ) , * % & -
   + / at mod remainder rem <an exponent (**)> and or ||
   multiset
ORA-06550: 第 7 行, 第 4 列: 
PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
 end
   not pragma final instantiable order overriding static member
   constructor map

创建有参存储函数findEmpNameAndJobAndSal(编号)，查询7788号员工的的姓名(return)，职位(out)，月薪(out)，返回多个值

CREATE OR REPLACE FUNCTION findEmpNameAndJobAndSal(pempno IN NUMBER, pjob OUT VARCHAR2, income OUT NUMBER)
  --这里指定的是返回值类型
  RETURN VARCHAR
AS
  /*查询出来的字段与列名相同，就使用列名相同的类型就行了。*/
  pename emp.ename%TYPE;
  BEGIN
    SELECT
      sal,
      ename,
      job
    INTO income, pename, pjob
    FROM emp
    WHERE empno = pempno;
    /*在PLSQL中一定要有return语句*/
    RETURN pename;
  END;

调用函数：

DECLARE
  /*输出的字段与列名的类型是相同的。*/
  income emp.sal%TYPE;
  pjob   emp.job%TYPE;
  pename emp.ename%TYPE;
BEGIN
  pename := findEmpNameAndJobAndSal(7369, pjob, income);
  dbms_output.put_line(pename || pjob || income);
END;/

过程与函数的使用场景

我们发现过程与函数的区别其实是不大的，一般我们都可以用函数来实现的时候，也可以使用过程来实现….

但是，总有些情况，使用函数比使用过程要好，使用过程比使用函数要好，那什么时候使用过程，什么时候使用函数呢？？？

不难发现的是，函数是必定要有一个返回值的，当我们在调用的时候，接受返回值就直接获取就行了。

也就是说

当返回值只有一个参数的时候，那么就使用存储函数！
当返回值没有参数或者多于一个参数的时候，那么就使用过程！

SQL与过程函数使用场景

【适合使用】过程函数：

》需要长期保存在数据库中
》需要被多个用户重复调用
》业务逻辑相同，只是参数不一样
》批操作大量数据，例如：批量插入很多数据

【适合使用】SQL：

》凡是上述反面，都可使用SQL
》对表，视图，序列，索引，等这些还是要用SQL

触发器

在PLSQL中也有个类似与我们Java Web中过滤器的概念，就是触发器…触发器的思想和Filter的思想几乎是一样的….

Oracle总结【PLSQL学习】（三）

值得注意的是：对于触发器而言，是不针对查询操作的。也就是说：触发器只针对删除、修改、插入操作！

触发器语法

CREATE  [or REPLACE] TRIGGER  触发器名
   {BEFORE | AFTER}
   { INSERT | DELETE|-----语句级
      UPDATE OF 列名}----行级
   ON  表名
    -- 遍历每一行记录
   [FOR EACH ROW]
   PLSQL 块【declare…begin…end;/】

Oracle总结【PLSQL学习】（三）

创建语句级触发器insertEmpTrigger，当对表【emp】进行增加【insert】操作前【before】，显示"hello world"

CREATE OR REPLACE TRIGGER insertempTiriger
BEFORE
INSERT
  ON EMP
  BEGIN
    dbms_output.put_line('helloword');
  END;

调用：

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (1, '2', '3', 4, NULL, NULL, NULL, 10);

结果：

Oracle总结【PLSQL学习】（三）

星期一到星期五，且9-20点能向数据库emp表插入数据，否则使用函数抛出异常,

语法：raise_application_error('-20000','例外原因')

CREATE OR REPLACE TRIGGER securityTrigger
BEFORE
INSERT
  ON EMP
  DECLARE
    pday  VARCHAR2(10);
    ptime NUMBER;
  BEGIN
    /*得到星期几*/
    SELECT to_char(sysdate, 'day')
    INTO pday
    FROM dual;
    /*得到时间*/
    SELECT to_char(sysdate, 'hh24')
    INTO ptime
    FROM dual;
    IF pday IN ('星期六', '星期日') OR ptime NOT BETWEEN 7 AND 23
    THEN
      RAISE_APPLICATION_ERROR('-20000', '非工作事件，请工作时间再来！');
    END IF;
  END;

插入数据、响应触发器：

Oracle总结【PLSQL学习】（三）

创建行级触发器checkSalaryTrigger，涨后工资这一列，确保大于涨前工资，语法：for each row/:new.sal/:old.sal

可以使用:new.sal/:old.sal来对比插入之前的值和插入之后的值

CREATE OR REPLACE TRIGGER checkSalTrigger
BEFORE
UPDATE OF sal
  ON EMP
FOR EACH ROW
  BEGIN
    IF :new.sal <= :old.sal
    THEN
      RAISE_APPLICATION_ERROR('-20001', '你涨的工资也太少了把！！！！');
    END IF;
  END;

调用：

Oracle总结【PLSQL学习】（三）

码农公寓

存储过程和函数的语法

过程与函数的使用场景

SQL与过程函数使用场景

触发器

触发器语法

相关文章