Oracle0815

最新推荐文章于 2024-07-13 18:02:33 发布

一头拱白菜的猪ll

最新推荐文章于 2024-07-13 18:02:33 发布

阅读量364

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_45772393/article/details/126351741

版权

4.3 过程

4.3.1 创建过程

create [or replace] procedure <过程名>
(
<参数1>, [方式1]<数据类型1>，
<参数2>， [方式2]<数据类型2>
...)
is/as
PL/SQL过程体;

过程创建示例

set serveroutput on format wrapped
create or replace procedure count_num(
in_sex in teachers.sex%type) --输入参数
as 
    out_num number;
begin 
   if in_sex = 'M' then
      select count(sex) into out_num
      from teachers 
      where sex = 'M';
      dbms_output.put_line('NUMBER of Male Teachers:'||out_num);
    else
       select count(sex) into out_num
       from teachers
       where sex = 'F';
       dbms_output.put_line('NUMBER of Female Teachers:'||out_num);
    end if;
end count_num;

4.3.2 调用过程

调用过程的命令是EXECUTE

execute count_num('M');
execute count_num('F');

4.3.2 删除过程

drop procedure count_num;

重新定义

create or replace procedure count_num;

4.3.4 过程的参数类型及传递

in参数类型

输入类型参数，表示这个参数值输入给过程，供过程使用

create or replace procedure double --完成将一个数加倍
(
   in_num in number,
   out_num out number
)
   as
begin
   out_num:= in_num*2
end double;

out参数类型

输出类型的参数，表示这个参数在这个过程中被赋值，可以传给过程体以外的部分或环境。

in out参数类型

既向过程体传值，在过程体中也被赋值而传向过程体外。

create or replace procedure double --完成将一个数加倍
(
   in_out_num in out number
)
as
begin
   out_num:= in_num*2
end double;

4.4 函数

4.4.1 创建函数

语法表达式

create [or replace] function <>
(<参数1>, [方式1]<数据类型1>, 
 <参数2>, [方式2]<数据类型2>
...)
return <表达式>
is|as
PL/SQL程序体   --其中必须要有一个return子句

return在声明部分需要定义一个返回参数的类型，而在函数体必须有一个return语句。

create or replace function count_num
(in_sex in teachers.sex%type)
return number
as  
   out_num number;
begin 
   if in_sex = 'M' then 
      select count(sex) into out_num
      from teachers
      where sex = 'M'
   else 
      select count(sex) into out_num 
      from teachers
      where sex = 'F';
    end if;
    return(out_num);
end count_num;

4.4.2 调用函数

调用函数时可以用全局变量接受其返回值

vareable man_num number
vareable woman_num number
execute man_num:=count_num('m')
execute woman_num:=count_num('f')

程序中调用函数示例

declare 
    m_num number;
    f_num number;
begin 
    m_num:=count_num('M');
    f_num:=count_num('F');
end;

4.4.3 删除函数

drop function count_num;

重新定义

create or replace function count_num;

4.5 程序包

说明部分
- 包与应用程序之间的接口，只是过程、函数、游标等名称或首部
包体部分
- 是这些过程、函数、游标等的具体体现

4.5.2 创建包

格式:

包说明部分

create package <包名>
is
变量、常量及数据类型定义;
游标定义头部；
函数、过程的定义和参数列表以及返回类型;
end <包名>;

包体部分

是包的说明部分中的游标、函数及过程的具体定义

create package body <包名>
as
游标、函数、过程的具体定义;
end<包名>;

创建一个包说明部分

create package my_package
is  
   man_num number;        --定义了两个全局变量
   woman_num number;
   cursor teacher_cur;    --定义了一个游标
   create function F_count_num(in_sex in teachers.sex%type)
   return number;          --定义了一个函数
   create procedure P_count_num
   (in_sex in teachers.sex%type, out_num outnumber);                --定义了一个过程
end my package;

对应包体的定义

SQL> create package body my_package
  2  as
  3    cursor teacher_cur is         --游标具体定义
  4       select TID, TNAME, TITLE, SEX
  5       from teachers
  6       where TID < 117;
  7    function F_count_num          --函数具体定义
  8      (in_sex in teachers.sex%type)
  9    return number
 10    as
 11       out_num number;
 12    begin
 13       if in_sex = 'm' then
 14          select count(sex) into out_num
 15          from teachers
 16          where sex = 'm';
 17       else
 18          select count(sex) into out_num
 19          from teachers
 20          where sex = 'f';
 21       end if;
 22       return(out_num);
 23       procedure P_count_num    --过程具体定义
 24          (in_sex in teachers.sex%type, out_num out number)
 25     as
 26     begin
 27        if in_sex = 'm' then
 28             select count(sex) into out_num
 29             from teachers
 30             where sex = 'm';
 31        else
 32             select count(sex) into out_num
 33             from teachers
 34             where sex = 'f';
 35        end if;
 36     end P_count_num;
 37  end my_package;              --包体定义结束
 38  /

4.5.3 调用包

包的调用方式为：

包名.变量名(常量名)

包名.游标名

包名.函数名(过程名)

variable man_num nuumber
execute man_num := my_package.F_count_num('M')

4.5.4 删除包

drop package my_package;

重新定义

create or replace package my_package;

4.6 触发器

4.6.1 触发器的基本原理

触发器是当某些事件发生时，由Oracle自动执行，触发器的执行对用户来说是透明的。

触发器类型

包括三种
- DML触发器：对表或视图执行DML操作时触发
- INSTEAD OF触发器：只定义在视图上，用来替换实际的操作语句。
- 系统触发器：对数据库系统进行操作（如DDL语句、启动或关闭数据库等系统事件）时触发。
相关概念

（1）触发事件

引起触发器被触发的事件。

（2）触发条件

触发条件是由where子句指定的一个逻辑表达式。

（3）触发对象

包括表、视图、模式、数据库。

（4）触发操作

触发器所要执行的PL/SQL程序，即执行部分。（5）触发时机

触发时机指定触发器的触发时间
```
      - before ：表示在执行DML操作之前触发，以便防止                某些错误操作发生或实现某些业务规则；
      - after：则表示在DML操作之后触发，以便记录该操作或做某些事后处理。
```
（6）条件谓词

当在触发器中包含了多个触发事件（insert、update、delete）的组合时，为了分别针对不同的事件进行不同的处理，需要使用Oracle提供的如下条件谓词。
- inserting：当触发事件是insert时，取值为true，否则为false。
- updating[(column_1, column_2, … column_n)]：当触发事件是update时，如果修改了column_x列，则取值为true，否则为false。
（7）触发子类型
```
     - 行触发：即对每一行操作都要触发，一般进行SQL语句操作时都应是行触发。
     - 语句触发：只对这种操作触发一次，对整个表做安全检查（即防止非法操作）是时才用语句触发。
```

4.6.2 创建触发器

create or replace trigger <触发器名>
触发条件
触发体

示例

create trigger my_trigger      --定义一个触发器my_trigger
    before insert or update of TID, TNAME on TEACHERS
    for each row
    where(new.TNAME = 'David') --这一部分是触发条件
declare                        --下面这一部分是触发体
    teacher_id teachers.TID%type;
    insert_exist_teacher exception;
begin
    select TID into teacher_id
    from teachers
    where TNAME = new.TNAME;
    raise insert_exist_teacher;
    exception                  --处理也可用在这里
    where insert_exist_teacher then
    insert into error(TID,ERR)
    values(teacher_id, 'the teacher already exists!');
end my trigger;

4.6.3 执行触发器

要求：

对一张表上的触发器最好加以限制，否则会因为触发器过多而加重负载，影响性能。
最好将一张表的触发事件编写在一个触发体中，这也可以大大改善性能

把与表teachers有关的所有触发事件都放在触发器my_trigger1中。

create trigger my_trigger1
   after insert or update or delete on teachers
   from each row;
declare
   info char(10);
begin 
   if inserting then       --如果进行插入操作
      info := 'insert';
   elsif updating then     --如果进行修改操作
      info := 'update'     
   else                    --如果进行删除操作
      info := 'delete'；
   end if;
   insert into sql_info values(info);  --记录这次操作信息
end my_trigger;

4.6.4 删除触发器

drop trigger my_trigger;

重新定义

create or replace trigger my_trigger;

一头拱白菜的猪ll

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle0815

当在触发器中包含了多个触发事件（insert、update、delete）的组合时，为了分别针对不同的事件进行不同的处理，需要使用Oracle提供的如下条件谓词。return在声明部分需要定义一个返回参数的类型，而在函数体必须有一个return语句。触发器是当某些事件发生时，由Oracle自动执行，触发器的执行对用户来说是透明的。输出类型的参数，表示这个参数在这个过程中被赋值，可以传给过程体以外的部分或环境。是包的说明部分中的游标、函数及过程的具体定义。包名.函数名(过程名)...
复制链接

扫一扫