ORACLE数据库 —— PL/SQL知识点3

Touw1n_

已于 2023-06-13 17:07:45 修改

阅读量803

点赞数

分类专栏： ORACLE 文章标签： oracle 数据库 sql

于 2023-06-12 22:51:58 首次发布

本文链接：https://blog.csdn.net/Beatrice_lovely/article/details/131178549

版权

ORACLE 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

ORACLE数据库 PL/SQL语句基础知识点适合有SQL基础的人群。 禁止转载！

存储过程
   声明部分：包括类型、变量和游标
   执行部分：完成功能而编写的SQL语句或是PL/ SQL代码块
   异常处理部分

创建存储过程基本语法：
   CREATE [OR REPLACE] PROCEDURE procedure_name
       [(parameter_name [IN|OUT|IN OUT] datetype
       [DEFAULT datavalue]
       [,...])]
       {IS|AS}
       [variable type [,...]]
   BEGIN
       procedure_body
   EXCEPTION
   END [procedure_name];

删除过程基本语法
DROP PROCEDURE procedure_name;

调用过程基本方式
可以使用EXECUTE指令调用过程，也可以在PL/SQL代码块中直接调用过程(不加EXECUTE)。

eg.1通过存储过程查询不同部门的人员数。
CREATE OR REPLACE PROCEDURE pro_count_dept(var_deptno emp.deptno%type)

AS var_count NUMBER; //带参数的存储过程

BEGIN
   SELECT count(*) INTO var_count FROM emp WHERE
   deptno = var_deptno; //存储过程自带参数var_deptno DECLARE部分已经声明
   DBMS_OUTPUT.PUT_LINE('部门号'||var_deptno||'的员工人数是'||var_count);
   EXCEPTION WHEN NO_DATA_FOUND then
   dbms_output.put_line('没有数据');
END;
/

执行存储过程
   法1.使用 EXECUTE pro_count_dept(10) //自带参数10
   法2.PL/SQL代码块中直接调用过程
       BEGIN
           pro_count_dept(10)   //自带参数10
       END;

参数基本语法说明
   IN：向存储过程传递参数，只能将实参的值传递给形参。
   OUT：从存储过程输出参数，存储过程结束时形参的位会赋   给实参。
   IN OUT：具有前面两种模式的特性，调用时，实参的位传递给形参，结束时，形参的值传递给实参。

eg2.通过存储过程，利用雇员编号查询雇员雇用日期（out举例）
CREATE OR REPLACE PROCEDURE pro_query_hiredate
(var_empno empno%type,
var_hiredate out hiredate%type)
AS
BEGIN
SELECT hiredate INTO var_hiredate FROM emp WHERE empno = varempno;
END;

pro_query_hiredate(v_empno,v_hiredate)的调用过程：
DECLARE
   v_empno empno%type
   v_hiredate hiredate%type
BEGIN
   v_empno:=&empno; //手动输入empno
   pro_query_hiredate(v_empno,v_hiredate); //调用存储过程
   dbms_output.put_line('编号为'||v_empno||'的员工雇佣日期是'|| to_char(v_hiredate,'yyyy-mm-dd'));
   EXCEPTION WHEN NO_DATA_FOUND THEN
       dbms_output.put_line('查无此人');

END;
/

eg3.通过存储过程，交换两个数据的值（in out举例）
DECLARE OR REPLACE PROCEDURE pro_swap
   (var_num1 IN OUT NUMBER,
   var_num2 IN OUT NUMBER)
AS
   temp number;
BEGIN
   temp:=var_num1;
   var_num1:=var_num2;
   var_num2:=temp;
END;

pro_swap(var_num1,var_num2)的调用过程：
DECLARE
   var_num1 number;
   var_num2 number;
BEGIN
   var_num1:=&var_num1;
   var_num2:=&var_num2;
   pro_swap(var_num1,var_num2);
   dbms_output.put_line('new_num1值为'||v_num1||';new_num2值为'||v_num2);
END;
/

函数
创建函数基本语法：
   CREATE [OR REPLACE] FUNCTION function_name
       [(parameter_name [IN|OUT|IN OUT] type [,...])]
   RETURN type //函数的返回类型不可缺少！！！
   {IS|AS}
   [variable type [,...]]
   BEGIN
       function_body
   END function_name;

删除函数语法：
DROP FUNCTION function_name;

eg4.创建一个函数，以雇员编号查询雇员薪水
CREATE OR REPLACE FUNCTION ft_query_sal(var_empno empno%type)
RETURN NUMBER AS        //函数的返回类型是NUMBER
var_sal NUMBER;
BEGIN
   SELECT sal INTO var_sal FROM emp WHERE empno=var_empno;
   RETURN var_sal;
END;

函数ft_query_sal(v_empno)的调用过程：
DECLARE
   v_empno empno%type;
   v_sal sal%type;
BEGIN
   v_empno:=&empno;
   --使用声明变量接收函数返回值
   v_sal:=ft_query_sal(v_empno);
   dbms_output.put_line('编号为'||v_empno||'的员工薪水是'||To_char(v_sal));
   EXCEPTION
       WHEN NO_DATA_FOUND THEN
           dbms_output.put_line('查无此人');
END;
/

包 ----------包是由存储在一起的相关对象组成的PL/SQL结构。用于逻辑组合相关的自定义类型、变量、游标、过程和函数。
--------包分为包头(包规范)和包体，缺一不可。

包规范基本语法：
   CREATE [OR REPLACE] PACKAGE package_name
   {IS|AS}
       公用类型或变量常量的声明；
       公用过程或函数的声明；
   END package_name;

包主体基本语法：
   CREATE [OR REPLACE] PACKAGE BODY package_name
   {IS|AS}
       私有类型或变量常量的声明；
       公有过程或函数的声明；
   END package_name;

调用包中的函数和过程时，应该调用中使用包名：
语法：package_name.type_name;

eg5.将过程pro_count_dept、函数ft_query_sal放入包pa_mypack中，以便对代码进行管理和调用，如何实现？

   1. 包规范的书写（类似于C语言中的.h文件）
       CREATE OR REPLACE PACKAGE pa_mypack
       AS
           PROCEDURE pro_count_dept(var_deptno deptno%type);
           FUNCTION ft_query_sal(var empno empno%type) RETURN NUMBER;
       END pa_mypack;

   2. 包体的书写（类似于C语言中的.c文件）
       CREATE OR REPLACE PACKAGE BODY pa_mypack
       AS
           -- 实现过程pro_count_dept
           PROCEDURE pro_count_dept(var_deptno emp.deptno%type) AS var_count NUMBER;
           BEGIN
               SELECT count(*) INTO var_count FROM emp WHERE deptno = var_deptno;
               dbms_output.put_line('部门号'||var_deptno||'的员工人数是'||var_count);
           EXCEPTION
               WHEN NO_DATA_FOUND then dbms_output.put_line('没有数据');
           END;
           -- 实现函数ft_query_sal
           FUNCTION ft_query_sal(var_empno empno%type) RETURN NUMBER
           AS   var_sal number;
           BEGIN
               SELECT sal INTO var_sal FROM emp WHERE empno=var_empno;
               RETURN var_sal;
END;
       END pa_mypack;

调用pa_mypack：
   DECLARE
       v_empno emp.empno%type;
       v_sal emp.sal%type;
       v_deptno emp.deptno%type;
   BEGIN
       v_empno:=&empno;
       v_deptno:=&deptno;
       pro_count_dept(v_deptno);
       v_sal:=pa_mypack.ft_query_sal(v_empno);
       dbms_output.put_line('编号为'||v_empno||'的员工薪水是'||To_char(v_sal));
       EXCEPTION WHEN NO_DATA_FOUND THEN
           dbms_output.put_line('无此人薪水信息');
   END;
   /

查看过程，函数和包的有关信息：
DESC user_procedures;
SELECT * FROM user_procedure WHERE object_name = 'PRO_QUERY_HIREDATE';

查看过程或函数代码：
SELECT text FROM user_source WHERE name='PRO_COUNT_DEPT';

查看包代码：
SELECT text FROM user_source WHERE name='PA_MYPACK' AND type='PACKAGE';

触发器 -----是一种特殊的过程。当特定的事件发生时，触发器被自动执行。

建立DML触发器的基本语法：
   CREATE [OR REPLACE] TRIGGER trigger_name
   {BEFORE|AFTER} trigger_event //trigger_event 有 insert、update、delete
   ON table_name //table_name 就是被操作的那个表的名字
   [REFERENCING [OLD AS old_name][NEW AS new_name]]
   [WHEN trigger_condition]
   [FOR EACH ROW]
   [DECLARE]
   BEGIN
       trigger_body
   END trigger_name;

eg6.记录下所有更改newemp表的用户名，操作类型和更改时间。
   CREATE OR REPLACE TRIGGER tr_iud_newemp
   BEFORE insert or update or delete
       ------插入、更新、删除前调用
   ON newemp;

-------对newemp进行操作的时候
   DECLARE
       v_operate scott.log_iud_newemp.operate_name%type;
   BEGIN
       IF inserting then
           v_operate:='insert';
       END IF;
       IF updating then
           v_operate:='update';
       END IF;
       IF deleting then
           v_operate:='delete';
       END IF;
       INSERT INTO log_iud_newemp VALUES (user,to_char(sysdate,'YYYY-MM-DD HH-MI-SS'),v_operate);
   END;
   /

触发器的禁用、启用与删除：
   ALTER TRIGGER my_trig {DISABLE|ENABLE};           //禁用/启用单个触发器
   ALTER TABLE trig_table {DISABLE|ENABLE} ALL TRIGGERS;       //禁用/启用trig_table表的所有触发器
   DROP TRIGGER my_trig;                   //删除

查看触发器信息：
   SELECT trigger_name,table_name FROM user_triggers;

行级触发器：执行DML语句时，每操作一行，触发器就执行一次。行级触发器使用WHEN条件，用于控制触发器的执行。
For each row

行级触发器引入两个标识符：:old和:new
:old表示原有的数据 :new表示新修改的数据

eg7.创建一个行级触发器，为表的主键列生成自增序列号
   1、生成一个表。
       CREATE TABLE employee(id number(10) primary key,name varchar2(20));
   2、生成一个序列。
       CREATE SEQUENCE id_seq;
   3、创建行级触发器。
       CREATE OR REPLACE TRIGGER tr_insert_employee_for_pk
       BEFORE insert
       on employee
       for each row
       BEGIN
           SELECT id_seq.nextval into :new.id FROM dual;
       END;

instead of 触发器：只能定义在视图上，用于执行一个替代操作来代替对视图的操作，对视图操作最终会转换为基本表的操作。
instead of 触发器必须是行级触发器。

创建基本语法：
   CREATE [OR REPLACE] TRIGGER trigger_name
   INSTEAD OF trigger_event
   ON view_name
   [REFERENCING [OLD AS old_name][NEW AS new_name]]
   [WHEN trigger_condition]
   [FOR EACH ROW]
   [DECLARE]
   BEGIN
       trigger_body
   END trigger_name;

eg8.Instead of 触发器更新一个视图
   --创建instead of触发器
   CREATE OR REPLACE TRIGGER tr_ins_view_newemp_newdept
   INSTEAD OF insert ON view_newemp_newdept
   FOR EACH ROW
   DECLARE
       rowcat number;
   BEGIN
       SELECT count(*) into rowcat FROM newdept WHERE
       deptno=:new.deptno;
       IF rowcat=0 THEN
           begin
               INSERT INTO newdept(deptno) VALUES (:new.deptno);
               INSERT INTO newemp (empno,ename,job,deptno) VALUES
               (:new.empno,:new.ename,:new.job,:new.deptno);
           end;
       ELSE
           INSERT INTO newemp (empno,ename,job,deptno)VALUES(:new.empno,:new.ename,:new.job,:new.deptno);
       END IF;
   END;
   /

系统触发器：
   系统触发器的激活基于两种不同的事件：
       DDL事件： CREATE、 ALTER、 DROP、TRUNCATE，…
       数据库事件：数据库服务器的启动/关闭事件、用户的登录/断开事件、服务器错误

显示错误:查看出现的错误,可以使用SHOW ERRORS命令，这只适用于当前只编译了一个存储过程的情况。
SHOW ERRORS

在编译了多个存储过程或函数的情况下，可以把SHOW ERRORS更为细化:
   SHOW ERRORS PROCEDURE proc_name;   //存储过程
   SHOW ERRORS FUNCTION my_func;       //函数
   SHOW ERRORS TRIGGER my_trig;       //触发器
   SHOW ERRORS PACKAGE my_pack       //包