之前所有創(chuàng)建的PL/SQL程序都是匿名的侥啤，沒有為程序塊提供一個名詞洲炊， 這就無法被存儲，每次只需后不可以被重復(fù)使用， 每次允許匿名塊的時候都需要先編譯踪栋，在執(zhí)行，在很多時候為了提高系統(tǒng)的應(yīng)用性能浴鸿，需要數(shù)據(jù)庫保存程序塊盈匾，方便以后重復(fù)使用，這就意味著程序塊需要一個名詞
命名程序塊： 過程虹脯， 函數(shù)驴娃， 程序包， 觸發(fā)器循集。唇敞。。
存儲過程
存儲過程是用于執(zhí)行特定操作
1. 創(chuàng)建
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();
調(diào)用
exec
call修改
or replace刪除
drop
參數(shù)
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,'吃飯餓啊'咒彤，'新鄉(xiāng)');
1. 位置傳值
按照定義參數(shù)的順序依次為參數(shù)指定響應(yīng)的變量和值
call add_dept(50,loc => '你好')
select * from dept;
參數(shù)模式
- in： 輸入?yún)?shù)
- out： 輸出參數(shù)
- in out ： 輸入輸入出函數(shù)
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;
函數(shù) ：
函數(shù)用于返回特定的數(shù)據(jù)疆柔，如果在應(yīng)用程序中進程需要通過SQL語句來返回特定數(shù)據(jù)，可以創(chuàng)建函數(shù)和存儲過程镶柱，存儲過程
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
-- 私有函數(shù)只能在包體中訪問
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;
包的構(gòu)造
在包中定義全局變量旷档，有些時候，會話中可能還需要初始化全局變量歇拆，可以通過包構(gòu)造初始化
當在會話內(nèi)第一次調(diào)用公共組件的時候鞋屈，會自動執(zhí)行其構(gòu)造過程
聲明： 在包體的最后 添加begin關(guān)鍵字范咨， 后面編寫構(gòu)造內(nèi)容
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('構(gòu)造被執(zhí)行');
end pack_emptest;
begin
pack_emptest.update_sal(7788,1);
end;
觸發(fā)器
觸發(fā)器（tigger）是一種特殊的類型的pl/sql塊， 觸發(fā)器類似于過程和函數(shù)谐区， 也具有聲明部分湖蜕，執(zhí)行部分， 異常部分宋列， 觸發(fā)器是在數(shù)據(jù)庫事件發(fā)生時被隱式觸發(fā)昭抒，而且觸發(fā)器不能接受參數(shù)，不能像過程函數(shù)已有顯示調(diào)用
觸發(fā)器組成
觸發(fā)事件： 引起觸發(fā)器被觸發(fā)的事件炼杖，例如DML語句（insert灭返，update，delete）語句的時候?qū)Ρ砘蛞晥D進行數(shù)據(jù)操作的時候觸發(fā)坤邪， DDL事件（create熙含，alter， drop）艇纺，數(shù)據(jù)庫事件（啟動怎静，退出，異常錯誤）用戶事件（登錄黔衡，退出）
觸發(fā)的時間 ：設(shè)置觸發(fā)事件發(fā)生之前（before）還是之后（after）
觸發(fā)對象： 包括 表蚓聘，視圖，模式盟劫，數(shù)據(jù)庫夜牡， 只有在指定的對象上發(fā)生了特定的時間，才會被觸發(fā)
觸發(fā)條件： 是一個邏輯表達式
觸發(fā)頻率 ： 設(shè)置語句基本的觸發(fā)器還是行基本的觸發(fā)器
觸發(fā)器的類型
DML觸發(fā)器
dml 所包含的事件 侣签，insert塘装， update， delete DML語句觸發(fā)器可以為這些觸發(fā)事件創(chuàng)建影所， before（發(fā)生前）和after（發(fā)生后）觸發(fā)器蹦肴，DML觸發(fā)器可以在語句級別或行級別操作上唄觸發(fā)
語句級別是對每一個SQL語句只觸發(fā)一次，行級別對SQL影響的表中的每一行觸發(fā)一次DDL觸發(fā)器
create猴娩。alter阴幌。drop系統(tǒng)觸發(fā)器
login ， logoff serverror 胀溺。startup裂七， shutdown-
替代觸發(fā)器
在對視圖進行dml操作的時候執(zhí)行的觸發(fā)器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('現(xiàn)在還有'||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
數(shù)據(jù)庫觸發(fā)器
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)
);
登錄觸發(fā)
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;
退出觸發(fā)
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 觸發(fā)器
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;
