存储过程
概念：
完成特定功能的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;
