存储过程

概念：

完成特定功能的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;