存储过程与函数

存储过程：有名字的代码块 procedure
存储过程的硬代码是什么？
–没有参数的存储过程：
create or replace procedure 存储过程名字
as
变量的声明;
begin
执行的sql语句;
end;

–创建一个存储过程
create or replace procedure pro_p1
as
n1 number;
n2 number;
begin
n1:=100;
n2:=200;
dbms_output.put_line(n1+n2);
end;

–调用存储过程
call pro_p1();

创建一个有输入参数的存储过程
create or replace procedure 存储过程名字(输入参数名字 in 数据类型)
as
变量的声明;
begin
执行的sql语句;
end;

输入的参数是不能重新赋值的，是在调用的时候传入的信息。

create or replace procedure pro_p2(n1 in number,n2 in number)
as
n3 number;
begin
n3:=100;
dbms_output.put_line(n1+n2+n3);
end;

call pro_p2(2,3);

有输出参数的存储过程
create or replace procedure 存储过程名字(输入参数名字 out 数据类型)
as
变量的声明;
begin
执行的sql语句;
end;

create or replace procedure pro_p3(s out number)
as
n1 number;
n2 number;
begin
n1:=100;
n2:=200;
s:=n1+n2;
end;
有输出参数的存储过程，需要放入到匿名块中进行调用
declare
s number;
begin
pro_p3(s);
dbms_output.put_line(s);
end;

同时有输入和输出的存储过程：
create or replace procedure pro_p4(n1 in number,n2 in number,s out number)
as
begin
s:=n1+n2;
end;

declare
s number;
begin
pro_p4(1.5,1.8,s);
dbms_output.put_line(s);
end;

什么时候会使用到存储过程？一个固定的功能的代码块，并且这个代码需要经常的反复的运行
公司的常用的业务计算；
进行数据的增量、全量操作；
删除索引和恢复索引的操作；
每天自动的创建当天的新的分区；
日志的操作
…

如何使用存储过程，来每日创建新分区？
alter table 表名 add partition 分区名 values 设置的规则;
alter table sales_info add partition s20201127 values less than(date’2020-11-28’);
alter table 表名 drop partition 分区名;
alter table sales_info drop partition s20201127;
–先创建一个分区表
create table sales_info(
saleid number,
saleman varchar2(50),
goods varchar2(50),
price number,
saletime date
)partition by range(saletime)
(
partition s20201124 values less than(date’2020-11-25’),
partition s20201125 values less than(date’2020-11-26’),
partition s20201126 values less than(date’2020-11-27’)
);
–创建存储过程，自动添加分区
create or replace procedure pro_sales_add_partition
as
c number;
s varchar2(500);
begin
–检查有没有今天的新分区
select count(1) into c from user_tab_partitions
where table_name=‘SALES_INFO’ and partition_name=concat(‘S’,to_char(sysdate,‘yyyymmdd’));
if c=0 then
–创建一个新分区
s:=‘alter table sales_info add partition s’||to_char(sysdate,‘yyyymmdd’)||
’ values less than(date’‘’||to_char(sysdate+1,‘yyyy-mm-dd’)||‘’‘)’;
execute immediate s;
end if;
end;

call pro_sales_add_partition();

异常处理
在代码中捕获所有出现的异常和错误
create or replace procedure 过程名
as
–声明部分
begin
–执行部分
exception
when 异常的名字 then
对异常的处理
when 异常的名字 then
对异常的处理
when others then
对异常的处理
end;

others表示所有的错误。

数据库的函数：自己去定义数据运行的过程，最终返回这个过程结果的代码块
create or replace function 函数名(输入的参数 数据类型)
return 返回的数据类型
as
声明部分
begin
执行部分
end;

create or replace function qiuhe(n1 number,n2 number)
return number
as
s number;
begin
s:=nvl(n1,0)+nvl(n2,0);
return s;
end;

select empno,qiuhe(sal,comm) from emp;

实现一个和数据库power()相同的函数的功能，自己写一个求数字的次方的方法：
create or replace function cifang(n number,c number)
return number
as
s number;
begin
s:=1;
for i in 1…c loop
s:=s*n;
end loop;
return s;
end;

select cifang(2,3) from dual;

练习：数据库有个initcap()函数，这是首字母大写的函数，自己写一个最后一个字母大写的函数。
create or replace function endcap(str varchar2)
return varchar2
as
s varchar2(500);
begin
s:=concat(lower(substr(str,1,length(str)-1)),upper(substr(str,-1)));
return s;
end;

select endcap(‘allen’) from dual;

存储过程和函数的区别？
1.存储过程可以没有参数，也可以有输入和输出的参数；函数一定要有输入的参数和返回的值
2.存储过程通过call调用，函数通过sql语句运行
3.函数里面不能执行dml和ddl操作，存储过程可以
4.函数是用来进行数据计算的，存储过程是用来实现一个固定的功能

数据库的触发器：在做A这个事情的时候，自动的发生B这个事情 trigger
检查输入的数据；实时备份表格的数据；记录表格操作的日志。
触发器的类型：
create or replace trigger 触发器名字
before|after insert or update or delete on 表名
for each row
begin
执行的sql语句;
end;

前置触发器：before
删除emp表格的时候，禁止删除PRESIDENT
create or replace trigger jinzhi_del_boss
before delete on emp
for each row
begin
if :old.job=‘PRESIDENT’ then
raise_application_error(-20001,‘不能删除老板’);
end if;
end;

如果要去更新用户的工资，新增的用户，工资不能超过2000元，老用户涨工资不能超过原来工资的10%。
create or replace trigger ck_emp_sal
before insert or update on emp
for each row
begin
if inserting then
if :new.sal>2000 then
raise_application_error(-20001,‘新员工工资不能超过2000元’);
end if;
elsif updating then
if :new.sal>:old.sal*1.1 then
raise_application_error(-20002,‘老用户涨工资不能超过原来工资的10%’);
end if;
end if;
end;

后置触发器：after
表格数据的实时备份
create or replace trigger backup_dept
after insert or update or delete on dept
for each row
begin
if inserting then
insert into dept_old values(:new.deptno,:new.dname,:new.loc);
elsif updating then
update dept_old set deptno=:new.deptno,dname=:new.dname,loc=:new.loc
where deptno=:old.deptno;
elsif deleting then
delete from dept_old where deptno=:old.deptno;
end if;
end