之前所有创建的PL/SQL程序都是匿名的，没有为程序块提供一个名词， 这就无法被存储，每次只需后不可以被重复使用， 每次允许匿名块的时候都需要先编译，在执行，在很多时候为了提高系统的应用性能，需要数据库保存程序块，方便以后重复使用，这就意味着程序块需要一个名词
命名程序块： 过程， 函数， 程序包， 触发器。。。
存储过程
存储过程是用于执行特定操作
1. 创建
create [or replace] procedure procedure_name
[(paramater_name[in|out|in out] datatype[m],....])]
create or replace procedure out_time
is
begin
dbms_output.put_line('当前时间'||systimestamp);
end;
drop procedure out_time;
call out_time();
调用
exec
call修改
or replace删除
drop
参数
create or replace procedure add_dept
(dno number, dname varchar2 default null, loc varchar2 default null)
is
begin
insert into dept values(dno, dname,loc);
end;
--drop procedure add_dept;
--call add_dept(13,'吃饭饿啊'，'新乡');
1. 位置传值
按照定义参数的顺序依次为参数指定响应的变量和值
call add_dept(50,loc => '你好')
select * from dept;
参数模式
- in： 输入参数
- out： 输出参数
- in out ： 输入输入出函数
create or replace procedure pro_result (num1 in out number, num2 in out number)
is
v_result number(6,3);
v_reamin number(6);
begin
v_result := num1/num2;
v_reamin := mod(num1,num2);
num1 := v_result;
num2 := v_reamin;
end;
--drop procedure pro_result
declare
v_n1 number := 8;
v_n2 number := 3;
begin
pro_result(num1 => v_n1,num2 => v_n2);
dbms_output.put_line(v_n1||' '||v_n2);
end;
函数 ：
函数用于返回特定的数据，如果在应用程序中进程需要通过SQL语句来返回特定数据，可以创建函数和存储过程，存储过程
create or replace function get_name(eno number)
return varchar2
as
v_name emp.ename%type;
begin
select ename into v_name from emp where empno =eno;
return v_name;
end;
declare
str varchar2(30);
begin
str:= get_name(7788);
dbms_output.put_line('姓名： '||str);
end;
create or replace function get_workyear(hiredate in date)
return number
as
v_worlyear number := 0;
begin
v_worlyear := extract(year from sysdate) - extract(year from hiredate);
return v_worlyear;
end;
select e.*, get_workyear(hiredate) workd from emp e;
程序包
create or replace package pack_test
as
v_temp number ;-- 定义全局
procedure p1(x number);--定义存储过程
end;
create or replace package body pack_test
as
procedure p1(x number)
as
begin
v_temp :=x;
dbms_output.put_line(v_temp);
end;
end;
begin
pack_test.p1(44);
end;
create or replace package pack_emp is
procedure add_employee(eno number, name varchar2, salary number, dno number);
end;
create or replace package body pack_emp is
-- 私有函数只能在包体中访问
function validata_deptno(v_deptno number)
return boolean
is
v_temp int;
begin
select count(deptno) into v_temp from dept where deptno = v_deptno;
if v_temp >0 then
return true;
else
return false;
end if;
end validata_deptno;
procedure add_employee(eno number, name varchar2, salary number, dno number)
is
begin
if validata_deptno(dno) then
insert into emp(empno,ename,sal,deptno,hiredate) values(eno,name,salary,dno,sysdate);
else
raise_application_error(-20060,'部门不存在');
end if;
exception
when dup_val_on_index then
raise_application_error(-20430,'该员工已存在');
end add_employee;
end pack_emp;
begin
pack_emp.add_employee(eno => 1002,name => '依恋',salary => 3400,dno => 20);
end;
select * from emp ;
create or replace package pack_overload
is
function get_sal(eno number) return number;
function get_sal(name varchar2) return number;
/*procedure del_employee(eno number);
procedure del_employee(name varchar2);*/
end;
create or replace package body pack_overload
is
function get_sal(eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = eno;
return v_sal;
exception
when no_data_found then
raise_application_error(-2030,'该员工不存在');
end get_sal;
function get_sal(name varchar2) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename) = upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-2030,'该员工不存在');
end get_sal;
end pack_overload;
declare
v_sal number;
begin
v_sal:= pack_overload.get_sal('ALLEN');
dbms_output.put_line(v_sal);
end;
select * from emp;
包的构造
在包中定义全局变量，有些时候，会话中可能还需要初始化全局变量，可以通过包构造初始化
当在会话内第一次调用公共组件的时候，会自动执行其构造过程
声明： 在包体的最后 添加begin关键字， 后面编写构造内容
create or replace package pack_emptest is
minsal number(6,2);
maxsal number(6,2);
procedure update_sal(eno number, salary number);
end;
create or replace package body pack_emptest is
procedure update_sal(eno number, salary number)
is
begin
dbms_output.put_line(minsal||' '||maxsal);
end update_sal;
begin
select min(sal),max(sal) into minsal,maxsal from emp;
dbms_output.put_line('构造被执行');
end pack_emptest;
begin
pack_emptest.update_sal(7788,1);
end;
触发器
触发器（tigger）是一种特殊的类型的pl/sql块， 触发器类似于过程和函数， 也具有声明部分，执行部分， 异常部分， 触发器是在数据库事件发生时被隐式触发，而且触发器不能接受参数，不能像过程函数已有显示调用
触发器组成
触发事件： 引起触发器被触发的事件，例如DML语句（insert，update，delete）语句的时候对表或视图进行数据操作的时候触发， DDL事件（create，alter， drop），数据库事件（启动，退出，异常错误）用户事件（登录，退出）
触发的时间 ：设置触发事件发生之前（before）还是之后（after）
触发对象： 包括 表，视图，模式，数据库， 只有在指定的对象上发生了特定的时间，才会被触发
触发条件： 是一个逻辑表达式
触发频率 ： 设置语句基本的触发器还是行基本的触发器
触发器的类型
DML触发器
dml 所包含的事件 ，insert， update， delete DML语句触发器可以为这些触发事件创建， before（发生前）和after（发生后）触发器，DML触发器可以在语句级别或行级别操作上呗触发
语句级别是对每一个SQL语句只触发一次，行级别对SQL影响的表中的每一行触发一次DDL触发器
create。alter。drop系统触发器
login ， logoff serverror 。startup， shutdown-
替代触发器
在对视图进行dml操作的时候执行的触发器create or replace trigger emp_count after delete on emp declare v_con integer; begin select count(1) into v_con from emp; dbms_output.put_line('现在还有'||v_con||'行'); end; select * from emp; delete from emp where empno = 1002;
create or replace trigger tri_emp
before insert or update or delete on emp2
begin
if to_char(sysdate,'DY') in('星期六','星期日') then
case
when INSERTING then
raise_application_error(-20006,'不能在休息日添加员工信息');
when UPDATING then
raise_application_error(-20006,'不能在休息日修改员工信息');
when DELETING then
raise_application_error(-20006,'不能在休息日删除员工信息');
end case;
end if;
end;
delete from emp2 where deptno =20;
update emp2 set comm = 500 where empno = 7902;
create table emp2 as select * from emp;
select * from emp2
数据库触发器
create table event_table(
event varchar2(30),
time date
)
create or replace tirgger tir_startup
after startup on database
begin
insert into event_table values (ora_sysevent, sysdata);
end;
create or replace tirgger tir_shutdown
after shutdown on database
begin
insert into event_table values (ora_sysevent, sysdata);
end;
create table log_table(
username varchar(20),
log_time data,
logoff_time date,
address varchar(20)
);
登录触发
create ot replace trigger tir_login
after logon on database
begin
insert into log_table (username,log_time,address)
values(ora_login_user, sysdate,ora_client_ip_address)
end;
退出触发
create ot replace trigger tir_logoff
after logoff on database
begin
insert into log_table (username,logoff_time ,address)
values(ora_login_user, sysdate,ora_client_ip_address)
end;
DDL 触发器
create or replace trigger tir_ddl
after ddl on scott.schema
begin
...
insert into event_ddl values(ora_sysevent,ora_login_user,ora_obj_owner,ora_dirct_obj_type,sysdate);
end;
