Oracle-存储过程(procedure、function、package、tigger)-阿里云开发者社区

开发者社区> pnodnineteen> 正文

Oracle-存储过程(procedure、function、package、tigger)

简介: 过程、函数、包、触发器
+关注继续查看

存储过程

概念:

完成特定功能的SQL语句集合,经过编译存储在数据库中
编译后sql语句,可以通过调用过程来实现功能,不需要重新写sql语句

优点:

模块化程序编程
减少网络流通量
提高安全性
提高可移植性
执行速度快

过程procedure

创建过程

create [ or replace ] procedure 过程名称 [ ( 参数列表) ]  { is | as }

----声明变量

begin

----执行代码

end [过程名称];

例如:

create or replace procedure myproc

as

m number;

begin

  m:=100;

  dbms_output.put_line(m);

end;

调用存储过程:

exec 存储过程名称 [ (参数) ]


execute 存储过程名称 [ (参数) ]


begin

----存储过程名称 [ (参数) ]

end

例如:

declare

begin

  myproc;

end;

删除过程

drop procedure 过程名称

获取过程返回值

问题:给一个用户,判断用户是否存在

--声明一个参数ret,是输出参数out,是number类型

create or replace procedure myproc2(pid in emp.eid%type,ret out number)

as

flag number;

begin

  select count(1) into flag from emp where eid=pid;

  if flag=1 then

    dbms_output.put_line(pid||'用户存在');

    ret:=1; -- 如果用户存在就把ret设为1

  else

    dbms_output.put_line(pid||'用户不存在');

    ret:=0; -- 如果用户不存在就把ret设为0

  end if;

end;

-- 调用过程

declare

 input varchar(50):=('&input');

 ret number; -- 声明一个变量ret,类型是number,用来存储过程的输出值

begin

  myproc2(input,ret); -- 获取到过程的输出值存储在ret中

  dbms_output.put_line(ret);

end;

注意:创建过程中,无论参数是输入参数还是输出参数,有几个参数,在调用过程时就要写多少个参数

函数function

函数的主要特征是必须有一个返回值,通过return来指定函数的返回类型,在函数的任何地方可以通过return expression语句从函数返回,返回类型必须和声明的返回类型一致。

创建函数

create [ or replace ] function 函数名称 [ (参数列表) ] return 返回值类型

{ is | as }

----声明变量

begin

----执行代码

end [函数名称];

返回大值:

创建函数

create or replace function func_max(num1 in number, num2 in number)

return number

is

begin

  if num1>=num2 then

    return num1;

  else

    return num2;

  end if;

end;
执行函数

declare

begin

  dbms_output.put_line(func_max(78,77));

end;

调用函数


declare

----创建变量存储函数调用返回值

begin

----调用函数赋值给变量

end;

删除函数

drop function 函数名称
函数和过程区别

至少返回一个变量的限制。而存储过程可以返回多个,也可以不返回。而函数是可以嵌入在sql中使用的,可以在select中调用,而存储过程不行。执行的本质都一样。

函数限制比较多,比如不能用临时表,只能用表变量.还有一些函数都不可用等等.而存储过程的限制相对就比较少

一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。

对于存储过程来说可以返回参数,而函数只能返回值或者表对象。

存储过程一般是作为一个独立的部分来执行(EXEC执行),而函数可以作为查询语句的一个部分来调用(SELECT调用),由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。

当存储过程和函数被执行的时候,SQL Manager会到procedure cache中去取相应的查询语句,如果在procedure cache里没有相应的查询语句,SQL Manager就会对存储过程和函数进行编译。

包和包体

创建包

create [ or replace ] package 包名称 is | as

----定义公用常量、变量、过程、函数等(不能有具体实现)

end [ 包名称 ];

创建包体

create [ or replace ] package body 包名称 is | as

----定义公用常量、变量、过程、函数等

----实现公用过程和函数

end [ 包名称 ];

调用包

declare

----定义变量

begin

----包名.元素名称(参数)

end

删除包或者包体

drop package [ body ] [ user. ] 包名

触发器tigger

某个条件成立时,触发器里面定义的语句会被自动执行

创建触发器

create [or replace] tigger 触发器名 { before | after } { insert | update | delete }

on 表名  ---- 数据库触发器所在的表。

[for each row] ---- 对表的每一行触发器执行一次。如果没有这一选项,则只对整个表执行一次。

begin

----pl/sql语句

end

删除触发器

drop tigger 触发器名称
触发器功能​​​

允许/限制对表的修改
自动生成派生列,比如自增字段
强制数据一致性
提供审计和日志记录
防止无效的事务处理
启用复杂的业务逻辑

例子:

--创建触发器,当星期四的时候不能修改emp表中的数据

create or replace trigger mytrigger

before insert or update or delete

on emp

begin

  if to_char(systimestamp,'DY')='星期四' then

    raise_application_error(-20001,'今天是星期四,不能修改'); --   -20000 到 -20999之间

  end if;

end;

使用触发器实现序号自增添加数据

------创建表

create table tbuser(

uuid number primary key,

uname varchar2(40),

upw varchar2(40)

);

------创建序列

create sequence myseq increment by 1

start with 1001

nomaxvalue

nocycle

cache 20;

------创建触发器

create or replace trigger mytrigger2

before insert

on tbuser

for each row

declare 

  uuuid number;

begin

  select myseq.nextval into uuuid from dual; -- 获取序列号

  :new.uuid:=uuuid; -- :new 表示将要插入的那条记录 -- :new.uuid 表示新插入记录的uuid

end;

写一个日志表当对tbuser进行增加删除修改的时候进行日志记录

------创建表

create table mylog(

l_name varchar(40),

l_type varchar(40),

l_cdate date

);

------创建触发器

create or replace trigger mytrigger3

after update or delete or insert

on tbuser

declare

c_type mylog.l_type%type;

begin

  if inserting then

    c_type:='insert';

    dbms_output.put_line('插入了数据');

  elsif deleting then

    c_type:='delete';

    dbms_output.put_line('删除了数据');

  elsif updating then

    c_type:='update';

    dbms_output.put_line('修改了数据');

  end if; 

  insert into mylog values(user,c_type,sysdate);

end;

创建触发器,用来记录删除的数据

------创建表

create table del_log(

uuid number primary key,

uname varchar2(40),

upw varchar2(40)

);

------创建触发器

create or replace trigger mytrigger4

after delete on tbuser

for each row

declare

begin

  insert into del_log values(:old.uuid,:old.uname,:old.upw); -- :old 表示当前记录  :new表示下一条记录

end;

:new 和 :old

:new --为一个引用最新的列值;

:old --为一个引用以前的列值; 这两个变量只有在使用了关键字 "for each row"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;

create or replace trigger uptsaly

after update on emp

for each row

begin

  if :old.esalary > :new.esalary then     ----注意 new 和 old ,new 表示修改后的数据, old 表示修改前的数据

    dbms_output.put_line('工资降低');

  elsif :old.esalary < :new.esalary then  ----注意 new 和 old ,new 表示修改后的数据, old 表示修改前的数据

    dbms_output.put_line('工资增加');

  else

    dbms_output.put_line('工资没变');

  end if;

  dbms_output.put_line('更新前的工资'||:old.esalary);

  dbms_output.put_line('更新后的工资'||:new.esalary);

end;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
OTL调用Oracle存储过程
OTL很早前用过,今天写东西要调存储过程,程序写完了,调试死活通不过,折腾了一早晨。 最后才发现错误,这里总结一下: 1、代码写的不规范。 有个参数后边少写了个“,”以至于总是抱错。而单独写的测试例子就没问题,后来一步一步跟踪了后才发现。
1345 0
大话存储系列7——文件系统和IO处理流程
1、文件系统 在早期的计算机系统中,每个程序都必须自己管理磁盘,在磁盘中放自己的数据,程序需要直接和磁盘控制器打交道。有多少个程序要利用磁盘,就有多少个磁盘交互启动接口。
1059 0
首度公开!OceanBase存储系统架构的演进历程及工程实践
OB君:作为一款100%自研的分布式数据库,OceanBase历经了近十年的发展历程。近十年来,OceanBase的存储架构经历了多次演进,以解决业务越来越复杂苛刻的存储需求。本文整理自赵裕众(花名陈群)在2019 SACC中国系统架构师大会中的演讲。
2995 0
Oracle存储过程基本语法
  1.基本结构   CREATE OR REPLACE PROCEDURE 存储过程名字   (   参数1 IN NUMBER,   参数2 IN NUMBER   ) IS   变量1 INTEGER :=0;   变量2 DATE;   BEGIN   END 存储过程名字   2.SELECT INTO STATEMENT   将select查询的结果存入到
1104 0
+关注
30
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载