oracle子程序和触发器的区别

发布网友发布时间：2022-05-03 12:28

共2个回答

懂视网时间：2022-05-03 16:50

4、子程序

回顾

练习：

declare
 v_name emp.ename%type:=‘张三‘;
 v_job emp.job%type:=‘游客‘; 
begin
 for n in 1..3 loop
  insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values(n*10,v_name||n,v_job,8000,to_date(‘2020-9-16‘,‘yyyy-MM-dd‘),16000,10);
  end loop;
  commit; --提交事务
 exception
  when others then
   rollback; --回滚事务
 end;

学习目标

1、子程序

2、存储过程

3、函数

4、包

学习内容

1、子程序

plsql:过程化的sql语言；变量、常量、匿名块、分支语句、循环语句

子程序：命名的程序块，存储过程和函数。

2、存储过程

分析：完成一些指定的业务，单一的命令无法搞定；比如：转账，分页操作

存储过程：类似于java中的方法，通过多条sql命令，完成一个特定的任务。

优点：

1、执行效率高：预编译的，存储在服务器上，第一次创建好，系统会进行预编译；

2、减少网络流量：代码封装到过程中，只需要调用过程名就行。

3、安全性：A:小明 B：小红 C：小青

4、重用性：代码角度考虑。

存储过程中能写的命令：

1、insert,update,delete,select ...into 变量

2、过程控制命令(条件、循环)

3、不能写DDL

使用存储过程

1、定义过程

-- 创建过程 procedure:过程
create or replace procedure sp_test1(
 -- 参数
)
as
 -- 声明部分 （临时变量：过程中使用的变量）
  begin
   exception
  end;

2、调用过程

1 call 过程名(参数);

2、
begin
 过程名(参数);
end;

3、sqlplus:
execute 过程名();

栗子：

1、转账：无参数

create or replace procedure sp_zhuanzhang
as
 v_money bank.balance%type:=1000; --要转账的钱数
begin
  update bank set balance=balance-v_money where name=‘李四‘;
  update bank set balance=balance+v_money where name=‘张三‘;
  commit;
  exception
   when others then
    rollback;
 end;

调用:

call sp_zhuanzhang();

begin
 sp_zhuanzhang;
 end;

2、转账：带参数

--flag :1 ：张三---》李四 2：李四 --->张三 钱数

create or replace procedure sp_zhuanzhang2
(
 --输入参数
 --参数名 数据类型（变量声明）
  flag in number,
  money in number
)
as
begin
  if flag=1 then
  update bank set balance=balance-money where name=‘张三‘;
  update bank set balance=balance+money where name=‘李四‘;
  else
   update bank set balance=balance-money where name=‘李四‘;
  update bank set balance=balance+money where name=‘张三‘;
   end if;
 commit;
 exception
  when others then
  rollback;
 end;

调用：

declare
 flag number(1):=2;
 money number(5):=500;
begin
 sp_zhuanzhang2(flag,money);
 end;

3、转账：带输入和输出参数

create or replace procedure sp_zhuanzhang3
(
 --输入参数
 --参数名 数据类型（变量声明）
 flag in number,
 money in number,
  --输出参数：调用过程后，可以在外部获取到值的数据：类似于返回值，可以有多个
  balance1 out bank.balance%type,
  balance2 out bank.balance%type
)
as
begin
 if flag=1 then
  update bank set balance=balance-money where name=‘张三‘;
  update bank set balance=balance+money where name=‘李四‘;
 else
  update bank set balance=balance-money where name=‘李四‘;
  update bank set balance=balance+money where name=‘张三‘;
  end if;
 commit;
  select balance into balance1 from bank where name=‘张三‘;
   select balance into balance2 from bank where name=‘李四‘;
 exception
  when others then
  rollback;
 end;

调用:

 declare
  --声明变量：用来接受输出参数的值
  b1 bank.balance%type;
    b2 bank.balance%type;
 begin
   sp_zhuanzhang3(2,1000,balance2=>b1,balance1=>b2);
    dbms_output.put_line(‘张三:‘||b2||‘李四:‘||b1);
end;

过程调用中的参数传递

1、按照位置传递

2、按照名字传值：顺序无关

参数名=>变量 参数名=>变量 参数名=>变量

3、组合传递

位置传递+名字传递；位置传递（靠前）

3、函数

本质上跟过程一样：必须有返回值

定义函数：

create or replace function 名字(
 -- 参数
  --名字 类型
) return 数据类型
as
begin
  return 结果;
end;

例子：

1、根据部门编号查询部门名称

create or replace function f_getDname(
-- 传入参数 ：部门编号
 dno dept.deptno%type 
) return varchar2 --返回字符串
as
 v_name dept.dname%type;
begin
  select dname into v_name from dept where deptno=dno;
  return v_name; --返回结果
end;

--调用

select empno,ename,deptno,f_getdname(deptno) 部门 from emp 
where f_getdname(deptno)=‘外交部‘;

2、根据部门编号统计部门人数

create or replace function f_getdeptcount(
 --参数
 dno number
)return number
as
 v_count number(5);
begin
  select count(*) into v_count from emp where deptno=dno;
  return v_count;
 end;

--调用
select deptno,dname,f_getdeptcount(deptno) 人数 from dept;

3、生成随机数字的函数

create or replace function getrandomvalue return number
as
 n number(20);
begin
 n:=dbms_random.value(1,10);
 return n;
end;

--调用

select getrandomvalue() from dual ;

调用：

declare
 n number(5);
begin
 n:=getrandomvalue();
 dbms_output.put_line(n);
end;

4、包

包：package

一系列的过程和函数

可以根据业务的不同创建不同的包：声明变量+过程+函数；

包：

1、包规范：类似于java中的接口

2、包体：类似于实现类

创建包规范：

create or replace package 名字
is
 --声明 变量 过程 函数
end 名字;

创建包体：

create or replace package body 名字
is
 --实现包规范定义好的过程和函数
end;

栗子：

create or replace package myfirstpackage
is
 --声明 全局的变量
  dno dept.deptno%type;
  --过程：向员工表中录入数据，不能录入重名的
  procedure add_emp(vno in number,vname in varchar2);
  --过程：根据部门编号，删除部门信息，只能删除没有员工的部门
  procedure del_dept(vno in number);
end package;

--包体
create or replace package body myfirstpackage
is
 --过程实现
 procedure add_emp(vno in number,vname in varchar2)
   as
    v_count number(2);-- 满足条件的员工的数量
   begin
    select count(*) into v_count from emp where ename=vname;
     if v_count>0 then
     --提示错误消息
     --dbms_output.put_line(‘‘);
     --raise_appliction_error:抛出一个自定义的错误消息，错误号：-20000-
     raise_application_error(-20001,‘名字重复‘);
     else
      insert into emp(empno,ename) values(vno,vname);
      commit;
     end if;
   end;

   --过程实现:根据部门编号，删除部门信息，只能删除没有员工的部门
   procedure del_dept(vno in number)
    as
     v_count number(3);
    begin
     --根据部门编号查询该部门对应的人数
     select count(*) into v_count from emp where deptno=vno;
      if v_count=0 then
      delete from dept where deptno=vno;
      commit;
      else
       raise_application_error(-20002,‘该部门下有员工,无法删除‘);
      end if;  
    end;
end myfirstpackage;

总结

1、存储过程

2、函数

相同点：

1、预编译、执行效率高、安全性、减少网络流量

2、输入参数、输出参数

不同点：

1、函数必须指定return（返回值）

3、包：包规范和包体（实现）；

Oracle就业课之子程序

标签：bms comm lan 必须 tde select 数据类型创建过程接受

热心网友时间：2022-05-03 13:58

oracle中触发器有三种1.DML触发器ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。2.替代触发器由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发