精通plsql2

存储过程,函数,触发器都可以存储在
Oracle中。
1.存储过程
create or replace procedure xxx
(变量名 类型...)
is
 --声明定义
begin
 --主处理
exception
 --异常处理
end;
/  
================
create or replace 
 procedure hello_pro1
is
begin
  dbms_output.put_line('Hello Procedure!');
end;
/
==============
//根据员工id显示工资和个税
create or replace procedure
  show_pro (v_id number)
is
v_salary emp.salary%type;
v_sal emp.salary%type;
v_tax emp.salary%type;
begin
  select salary into v_salary
  from emp where id=v_id;
  dbms_output.put_line(
      '工资为:'||v_salary);
  --按昨天个税计算方式计算个税输出
  v_sal := v_salary-3500;
  if v_sal <= 0 then
      v_tax := 0;
   elsif v_sal < 1500 then
      v_tax := v_sal*0.03;
   elsif v_sal < 4500 then
      v_tax := v_sal*0.1-105;
   elsif v_sal < 9000 then
      v_tax := v_sal*0.2-555;
   else
      v_tax := v_sal*0.25-1005;
   end if;
   dbms_output.put_line('应缴纳个税:'||v_tax);
end;
/


==============
 存储过程参数,可以分为传入型,传出型,
传入传出型。
 变量名 IN|OUT|INOUT 类型
 默认为IN类型
create or replace procedure mypro 
(a number,b number,c out number)
is
begin
  c:=(a+b)+(a-b)+a*b+a/b;
end;
/
测试
declare
  r number;
begin
  mypro(10,5,r);
  dbms_output.put_line('r='||r);
end;
/


2.函数
 create or replace function xxx
 (变量名 类型...)
 return 返回类型
 is
 begin
 end;
 /
===========
create or replace function mysign
(n number)
return number
is
begin
 if n>0 then
   return 1;
 elsif n<0 then
   return -1;
 else
   return 0;
 end if;
end;
/
select mysign(10),mysign(0),mysign(-100)
from dual;
==============
案例:编写一个函数,传入工资,传出个税
扣税规则：参考前面案例
create or replace function mytax
(v_salary number)
return number
is
  v_sal number(12,2);
  v_tax v_sal%type;
begin
  v_sal := v_salary-3500;
  if v_sal <= 0 then
      v_tax := 0;
   elsif v_sal < 1500 then
      v_tax := v_sal*0.03;
   elsif v_sal < 4500 then
      v_tax := v_sal*0.1-105;
   elsif v_sal < 9000 then
      v_tax := v_sal*0.2-555;
   else
      v_tax := v_sal*0.25-1005;
   end if;
   return v_tax;
end;
/
select mytax(7000) from dual;


3.存储过程和函数的区别
 a.过程可以使用DML语句;
   函数不允许使用DML语句;
 b.过程可以没有返回值,存储过程靠
   out参数返回;
   函数必须有返回,函数利用return
   返回;
 c.过程参数可以in,out等模式
   函数只有in模式
 d.过程利用call或exec命令调用;
   函数利用SQL调用
 使用建议：
  --如果涉及DML操作选择过程
  --如果不涉及DML,不许要返回结果选择过程
  --如果不涉及DML,需要返回结果选择函数


4.触发器
  触发器不需要显式调用，是由系统
事件或DML事件自动触发。
触发器可以分为系统触发和DML触发器
 DML触发器还可以分为
  语句级触发器和行级触发器两种。
两种触发器都是由DML事件触发,不同
点在于触发器调用次数不同。
  1）语句级触发器的使用
create or replace trigger xxx
BEFORE|AFTER insert|update|delete
ON 表名
declare
begin
end;
/
===================
案例：当EMP表记录改变时,统计员工
的平均工资
create trigger emp_trigger1
after 
insert or delete or update on emp
declare
  v_avg_sal number(7,2);
begin
  select avg(salary) into v_avg_sal 
  from emp;
  dbms_output.put_line(
      '平均工资'||v_avg_sal);
end;
/


提示：执行DML语句时会自动触发
触发器功能


 2)DML行级触发器
  DML行级触发器执行次数与DML语句
影响的记录行数相关。在行级触发器中
可以获取DML影响的记录信息。
  在行级触发器中，使用下面关键字
获取影响的记录.
 :NEW 表示sql执行后的新纪录状态
 :OLD 表示sql执行前的旧记录状态


 insert语句使用:NEW表示要插入的记录
 delete语句使用:OLD表示要删除的记录
 update语句:NEW表示更新后的数据状态
    :OLD表示更新前的数据状态
:OLD-->update-->:NEW
=====================
案例：将删除的EMP记录备份到
EMP_BAK表
create table emp_bak
as select * from emp where 1<>1;


create or replace trigger emp_trigger4
before delete on myemp 
for each row
declare
begin
  insert into emp_bak (
    id,name,salary,age,
    marry,birthday,last_login_time)
  values (:OLD.id,:OLD.name,
   :OLD.salary,:OLD.age,:OLD.marry,
   :OLD.birthday,:OLD.last_login_time);
end;
/


===================
触发器自动执行-->发送DML语句
存储过程执行-->call 存储过程
函数执行-->嵌在select中,在过程中调用
5.Java如何调用存储过程
  1)JDBC
    --CallableStatement
  2)Hibernate(以后介绍)
    --利用SQLQuery执行
======================
练习1：JDBC如何调用存储过程
练习2：NETCTOSS中有SERVICE和
SERVICE_UPDATE_BAK表
SERVICE:业务账号表
SERVICE_UPDATE_BAK:存储要变更
资费标准的业务账号信息
需求：编写一个存储过程,将
SERVICE_UPDATE_BAK表中记录
同步更新到SERVICE的COST_ID值。
create procedure update_service
is
begin
  --查询SERVICE_UPDATE_BAK所有记录
  --循环访问查询出来的记录
  --循环访问时利用SERVICE_ID
   和COST_ID信息去更新SERVICE中
   的COST_ID值
   update SERVICE 
   set COST_ID=记录.COST_ID
   where ID=记录.SERVICE_ID
  --最后清除SERVICE_UPDATE_BAK表所有信息
end;
/