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;
相关文章
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(上)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
7月前
|
存储 Oracle 关系型数据库
oracle服务器存储过程中调用http
通过配置权限、创建和调用存储过程,您可以在Oracle数据库中使用UTL_HTTP包发起HTTP请求。这使得Oracle存储过程可以与外部HTTP服务进行交互,从而实现更复杂的数据处理和集成。在实际应用中,根据具体需求调整请求类型和错误处理逻辑,以确保系统的稳定性和可靠性。
317 0
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(下)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
存储 SQL Oracle
oracle 存储过程导出sql语句 导出为文件
oracle 存储过程导出sql语句 导出为文件
243 0
|
SQL 存储 分布式计算
DataWorks产品使用合集之在DataWorks中调用存储过程(PROCEDURE)如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
324 0
|
4月前
|
存储 Java 关系型数据库
java调用mysql存储过程
在 Java 中调用 MySQL 存储过程主要借助 JDBC(Java Database Connectivity)。其核心原理是通过 JDBC 与 MySQL 建立连接,调用存储过程并处理结果。具体步骤包括:加载 JDBC 驱动、建立数据库连接、创建 CallableStatement 对象、设置存储过程参数并执行调用。此过程实现了 Java 程序与 MySQL 数据库的高效交互。
|
3月前
|
存储 关系型数据库 MySQL
【YashanDB知识库】MySQL返回结果集的存储过程的改写方法
本文介绍了将MySQL存储过程改写至YashanDB的解决方案。由于MySQL存储过程可直接返回结果集,而YashanDB需通过返回SYS_REF_CURSOR的函数实现类似功能,因此需要对代码进行转换。示例中展示了如何将MySQL存储过程`proc1`改写为YashanDB函数,并调整JDBC应用代码以适配REF_CURSOR输出参数,从而正确获取查询结果。此方法确保了跨数据库场景下的兼容性与功能性。
|
7月前
|
存储 SQL NoSQL
|
8月前
|
存储 SQL 关系型数据库
MySql数据库---存储过程
MySql数据库---存储过程
105 5
|
8月前
|
存储 关系型数据库 MySQL
MySQL 存储过程返回更新前记录
MySQL 存储过程返回更新前记录
143 3

推荐镜像

更多