ORACLE数库之PL/SQL高级篇存储过程，函数，包，触发器的使用

最新推荐文章于 2024-06-26 15:41:07 发布

树先生

最新推荐文章于 2024-06-26 15:41:07 发布

阅读量3.5k

点赞数 2

文章标签：存储 oracle insert table application 数据库

本文链接：https://blog.csdn.net/xxrlily/article/details/4001295

版权

PL/SQL高级编程部分
无名块不存储在数据库中，并且不能从其他的PL/SQL块中调用
有名块存储在数据库数据字典中，可以在客户端与服务器端的任何工具和任何应用中运行
(1).存储过程 (2).存储函数 (3).包 (4).触发器
过程执行某一动作，函数计算一个值，包将相关的过程函数逻辑上捆绑在一起，触发器隐式执行某一动作
存储过程和函数是把一个PL/SQL块编译后存进数据库数据字典中，以后可以重复使用的模式对象
注意：在存储过程和函数中的形式参数及返回结果如果是字符型，不要指定指定长度

1.存储过程
(1).创建存储过程
create [OR REPLACE] PROCEDURE 过程名
[(参数名[IN|OUT|IN OUT]数据类型...)]
{IS | AS}
[说明部分]
BEGIN
语句系列
[EXCEPTION 出错处理]
END[过程名];
参数说明：IN OUT IN OUT如下：
IN 输入参数：用来从调用环境中向存储过程传递值，即IN模式参数不能出现在赋值语句式边
OUT输出参数：用来从存储过程中返回值给调用者，在过程体内，必须给OUT模式参数赋值，OUT模式参数
可以出现在赋值语句的左边.没有值的OUT模式参数不能出现赋值语句的右边.
IN OUT：输入参数，输出参数.即可从调用者向存储过程传递值，过程执行后又可返回改变后的值给调用者.
eg:
给某一指定的员工涨指定数量的工资，该存储过程有两个形式参数：emp_id和v_increase，没有指定参数
的模式，默认是IN 模式.
create procedure raise_salary(emp_id integer,v_increase real)
is
BEGIN
update emp set sal=sal+v_increase where empno=emp_id;
commit;
end raise_salary;
/
根据给定的员工号(通过IN模式参数代入)，用OUT模式参数返回员工的姓名,工资和奖金信息.
create or replace procedure query_emp
(v_emp_no IN emp.empno%type,v_emp_name out emp.ename%type,v_emp_sal out emp.sal%type
v_emp_comm out emp.comm%type
)
is
BEGIN
select ename,sal,comm into v_emp_name,v_emp_sal,v_emp_comm from emp
where empno=v_emp_no;
end query_emp;

2.存储函数
(1).创建存储函数的语法如下：
create [or replace ]function 函数名
[(参数名[in]数据类型...)]
return 数据类型
{is|as}
[说明部分]
BEGIN
语句序列
return (表达式)
[EXCEPTION 例外处理程序]
END 函数名;

/
编写一个函数，计算几个人的平均工资，并在函数体的循环过程中输出结果
create or replace function average_sal(v_n in number(3))
return number
is cursor c_emp is select empno,sal from emp;
v_total_sal emp.sal%type:=0;
v_counter number;
v_emp_no emp.empno%type
BEGIN
FOR r_emp in c_emp loop
   exit when c_emp% rowcount>v_n or c_emp%notfound;
   v_total_sal:=v_total_sal+r_emp.sal;
   v_counter:=c_emp%rowcount;
   v_emp_no:=r_emp.empno;
   dbms_output.put_line('loop='||v_counter||';empno='||v_emp_no);
end loop;
return (v_total_sal/v_counter);
end average_sal;

/
注意存储过程与函数的区别

1...返回值不同:存储函数有零个或多个参数，函数可以有IN参数，但不能有OUT参数。函数只返回一个值，函数值的返回是靠return 子句返回的
2...存储过程有零个或多个参数，地程可以有IN参数，在调用过程时通过参数列表接受IN参数的输入。过程不返回值，其返回值是靠OUT参数带出来的
过程可以有零个或多个OUT参数返回结果
调用过程的语句可以作为单独的可执行语句在PL/SQL块中单独出现
过程名(实际参数1，实际参数2....)
函数可以在任何表达式能够出现的地方被调用，调用函数的语句不能作为可执行语句单独出现在PL/SQL块中
变量名：函数名(实际参数1，实际参数2....);

存储过程的调用
(1).在PL/SQL块中的调用
eg:
DECLARE
V_empno emp.empno%type:=7777;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_comm emp.comm%type;
BEGIN
query_emp(v_empno,v_ename,v_sal,v_comm);
dbms_output.put_line(v_ename||' '||v_sal||' '||v_comm);
END;
/
函数的调用
DELCARE
v_empno number:=111;
v_sal nubmer;
BEGIN
v_sal:=get_sal(v_empno);
dbms_output.put_line(v_sal);
end;
/
(2).在SQL*PLUS中的调用
过程的调用
eg:
set serveroutput on/*激活DBMS_OUTPUT系统包*/
ACCEPT p_emp_no prompt 'please enter the employee number:';/*接受员工号*/
variable v_emp_name varchar2(14);/*定义存放OUT参数输出结果的变量 */
variable v_emp_sal number;
variable v_emp_comm number;
execute query_emp(&p_emp_no,:v_emp_name,:v_emp_sal,:v_emp_comm);
注意在SQL*PLUS中，用variable定义的变量在引用时，必须前面加昌号(:),用accept接收的变量在引用时，必须前面加& 符号
函数的调用
函数不能作为一条语句单独出现，只能出现在别的过程中作为别的过程的参数
SQL>EXECUTE dbms_output.put_line(get_sal(0000));
或者
SQL>SELECT get_sal(0000) from dual;

3.包
(1).创建包的说明：
create [or replace] package 包名
   {is|as}
   公共变量的定义
   公共类型的定义
   公共出错处理的定义
   公共游标的定义
   函数说明
   过程说明

end;
/
eg:制作一个包的说明，生成一个管理雇员薪水的包sal_package,其中包括一个为雇员加薪的过程和降薪的过程
并且在包中还有两个记录所有雇员薪水增加和减少的全局变量
create package sal_package is
procedure raise_sal(v_empno emp.empno%type),
v_sal_increment emp.sal%type);
procedure reduce_sal(v_empno emp.empno%type,
v_sal_reduce emp.sal%type
);
v_raise_sal emp.sal%type:=0;
v_reduce_sal emp.sal%type:=0;
end;
/
创建包的主体
create [or replace] package BODY 包名
   {is|as}
   公共变量的定义
   公共类型的定义
   公共出错处理的定义
   公共游标的定义
   函数说明
   过程说明

end;
/
制作sal_package包的包主体
create or replace package body sal_package is
procedure raise_sal(v_empno emp.empno%type,
v_sal_increment emp.sal%type
)
is
BEGIN
   UPDATE emp set sal=sal+v_sal_increment where empno=v_empno;
   commit work;
   v_raise_sal:=v_raise_sal+v_sal_increment;
end;
procedure reduce_sal(v_empno emp.empno%type, v_sal_reduce emp.sal%type)
is
BEGIN
   update emp set sal=sal-v_sal_reduce where empno=v_empno;
commit work;
v_reduce_sal:=v_reduce_sal+v_sal+reduce;
end;
end;
/

包的调用
包名.过程名
SQL>EXECUTE sal_package.raise_sal(111,23423);

4.触发器
eg:
制作一个数据库触发器,将那些超过其工种工资范围的员工信息记录到audit_message表中,在sal_guide记录
了每一工种的工资范围
create or replace trigger check_sal
before insert or update of sal,job on emp
for each row
when (new.job<>'persident')
DECLARE
v_minsal sal_guide.minsal%type;
v_maxsal sal_guide.maxsal%type;
e_sal_out_of_range exception;
BEGIN
select minsal,maxsal into v_minsal,v_maxsal from sal_guide
   where job=:new.job;
if :new.sal<v_minsal or :new:sal>v_maxsal then
   raise e_sal_out_of range;
end if;
exception
   when e_sal_out_of_range then
    insert into audit_message(line_nr,line)
     values(1,'Salary'||TO_CHAR(:new.sal)||
   'is out of range for employee'||TO_CHAR(:new.empno)
   );
END;
/
(1).触发器的组成
触发时间：before after
触发事件：insert update delete
触发器类型：statement,row (语句级，行级)
触发器体(完整的PL/SQL块)
触发器可分语句级触发器，行级触发器
(2).语句级触发器
创建语法如下：
create [or replace] trigger trigger_name
{before|after} event1[or event2] on table_name
PL/SQL block
eg:
创建一个before 型语句级触发器，限制一周内向emp表插入数据的时间，如果是周六，周日
或晚上6点到第二天早上8点之间插入，则中断操作，并提示用户不允许在此时间向emp表插入
create or replace trigger secure_emp
before insert on emp
BEGIN
   IF(TO_CHAR(sysdate,'DY')IN('SAT','SUN'))
    or(TO_CHAR(sysdate,'HH24')NOT BETWEEN '8' and '18')then
    raise_application_error(-20500,
    'you may only insert into emp during normal hours.'
);
END IF;
END;
/
(3).使用触发器谓词(inserting,updating,deleting)
通过谓词可以创建一个包含多个触发事件的触发器
对上例进行扩展不但限制插入数据的时间，还限制进行数据修改和删除的时间
create or replace trigger secure_emp
before delete or insert or update on emp
BEGIN
   if (TO_CHAR(sysdate,'DY' IN('SAT','SUN'))
    OR (TO_NUMBER(sysdate,'HH24')NOT BETWEEN 8 AND 18 )THEN
    if deleting then
     raise_application_error(-20502,'You may only delete
     from emp during normal hours.
      ');
   elsif inserting then
   raise_application_error(-20500,
   'You may only insert into emp during mormal hours'
   );
   else
    RAISE_APPLICATION_ERROR(-20504,
   'You may only update emp table during normal hours.'
);
end if ;
end if ;
end;
/
(4).行级触发器
创建语法
create [or replace]trigger trigger_name
   {before|after} event1[or event2....] on table_name
   for each row [when restricting_condition]
PL/SQL
eg:创建一个行级触发器，将每个用户对数据库emp表进行数据库操纵(插入，更新，删除)的次数记录到
audit_table表中
   create or replace trigger audit_emp
after delete or insert or update on emp
   for each row
BEGIN
   IF DELETING THEN
    update audit_table set del=del+1 where user_name = user and table_name
='emp' and column_name is null;
elsif inserting then
   update audit_table set ins=ins+1
    where user_name=user and table_name='emp' and column_name is null;
else
   update audit_table set upd=upd+1
   where user_name=user and table_name='emp' and column_name is null;
end if;
end;
/
使用行级触发器的标识符(:OLD和:NEW)
在列名前加上：OLD标识符表示该列变化前的值，加上：NEW标识符表示变化后的值
eg:
在行级触发器加WHEN限制条件，根据销售员工资的改变自动计算销售员的奖金
create or replace trigger derive_comm
before update of sal on emp
for each row
   when (new.job='SALESMAN')
BEGIN
   :new.comm:=:old.comm*(:new.sal/:old.sal);
end;
/