oracle 存储过程，包，方法，触发器，过程

存储过程：
--修改存储过程

create or replace procedure emp_proc(
       findname varchar2,
       salary1 number
)is
begin
update employees set salary=salary1 where last_name=findname;
end;

//调用存储过程，可以传参数(id,name)
exec emp_proc();
call emp_proc();
--输入员工名字,如果奖金不是0.15就加0.1，其他加0.3 if/else;
create or replace procedure emp_upCommis(findname varchar2)is
v_comm employees.commission_pct%type;
begin
  select distinct nvl(commission_pct,0) intov_comm from employees where last_name=findname;
  if v_comm<>0.15 then
    update employees set commission_pct=commission_pct+0.1 wherelast_name = findname;
  else
    update employees set commission_pct=commission_pct+0.3 wherelast_name = findname;
endif;    
end;




--输入输出参数

create or replace procedure dept_pro(deptno number,dname outvarchar2)
is
begin
 select department_name into dname fromdepartments where department_id = deptno;
end;
--写个块检测
declare
name2 varchar2(50);
begin
   dept_pro(10,name2);
  dbms_output.put_line('deptname:'||name2);
  
end;



异常例外,处理
> declare
  2   v_namevarchar2(10);
  3  v_sal number(8,2);
  4  begin
  5  select last_name,salary intov_name,v_sal from employees whereemployee_id=&empno;
  6 dbms_output.put_line('ename:'|| v_name||'sal'||v_sal);
  7  exception
  8  when no_data_foundthen
  9  dbms_output.put_line('youerror,zhoabudao .sdfjasjf ^^');
 10  end;
 11  /



-------------------函数//select 函数即可：

create function emp_fun(ename varchar2)
return number
is
yearSal number(8,2);
begin
   selectsalary*12+nvl(commission_pct,0)*12 into yearSal from employeeswhere last_name=ename;
   return yearSal;
end;


包：
create or replace package emp_package is

procedure update_sal(ename varchar2,newsal number );
function a_i(ename varchar2) return number;

end;
----包体


create or replace package body emp_package is


procedure update_sal(ename varchar2,newsal number )
is
begin
         update employees set salary=newsal where last_name=ename;
end;

function a_i(ename varchar2)
 return number
is
yearsal number(8,2);
begin

       select salary*12+nvl(commission_pct,0)*12 into yearsal fromemployees where last_name=ename;
       return yearsal;
end;


end;

三种循环：常见语法 is begin end；
--循环 loop / end loop 循环
create or replace procedure deptno_pro(dname varchar2)
is
v_num number:=2;
begin
      loop
     exit when v_num = 6; 
     insert intodepartments(department_id,department_name)values(v_num,dname||v_num);
     v_num:=v_num+1;
end loop;
end;

--循环 while条件 loop / end loop

create or replace procedure deptno_pro(dname varchar2)
is
v_num number:=2;
begin
      while v_num<=6
      loop 
      insert intodepartments(department_id,department_name)values(v_num,dname||v_num);
      v_num:=v_num+1;
end loop;
end;
--循环 for 变量 in reverse 1..10 loop/end loop 1到10循环

for i in reverse 1..10 loop
(department_id,department_name) values(i,dname||i);
      endloop;    goto


--输入员工姓名，员工工工资
create or replace procedure emp_upCommisTwo(findname varchar2)is
v_job employees.job_id%type;
 begin
  select nvl(job_id,'null') into v_job fromemployees where last_name=findname;
  if v_job='FI_MGR' then
    update employees set salary=salary+1000 where last_name =findname;
  elsif v_job='IT_PROG' then
    update employees set salary=salary+500 wherelast_name=findname;
  else
    update employees set salary=salary+100 where last_name=findname;
end if;
end;

--输入一个部门编号，显示这个部门下面的员工信息
create or replace package dept_package is
type test_cursor is ref cursor;
end dept_package;

--游标输出一个对象，为一个数据堆
create or replace procedure deptt_pro(deptno number,dept_cursor outdept_package.test_cursor) is

begin
   open dept_cursor for select *from employees where department_id = deptno;
end;


分页：
--如何筛选
select * from (select ee.*,rownum rn (select * from employees eorder by e.employee_id)ee where rn<10) where rn>6;
--分页过程实现 首先数据存在哪里我输入的table名数据存在游标中，游标在包里定义
--1,先写包    //游标存储一堆数据返回对象
create or replace package page2_package
as
type test_cursor is ref cursor;
end;
--2,再写分页细节 存储过程方便调用和重复利用
create or replace procedure page2_proc(
tablename varchar2,//表名
pagesize number,//一页多少条记录
pagecurrent number,//当前页数
rowtotal out number,//总记录
pagetotal out number,//总页数
emp_cursor out page2_package.test_cursor
)is
    V_SQLvarchar2(1200);
    v_beginnumber:=(pagecurrent-1)*pagesize+1;
    v_endnumber:=pagecurrent*pagesize;
begin
   v_sql:='select * from (select e.*,rownum rn from (select * from'||tablename||') e where rownum<='||v_end||') wherern>='||v_begin;
    openemp_cursor forv_sql;             //注意了表数据存于游标中

   v_sql:='select count(*) from '||tablename;
    executeimmediate v_sql into rowtotal;

    ifmod(rowtotal,pagesize)=0 then
   pagetotal:=rowtotal/pagesize;
    else
   pagetotal:=rowtotal/pagesize+1;
    endif;

end;


拦截器：
视图

视图和表的区别
表需要占用空间,视图不需要
视图不能添加索引.(视图要比表慢一点)
使用视图可以简化复杂的查询.
视图可以提高安全性(不同的用户可以看到不同的视图)



触发器.  //注意所操作的数据将存于old或new特定表中等待触发

创建,使用,级联(删除,插入,更新)

触发器像一个特殊的存储过程.


1,问题:删除作者,级联删除这个作者出版的书籍.
create or replace trigger del_trigger
after delete on AUTHOR
for each row

begin

    delete fromBOOK where AUTHORid=:old.AUTHORid;
end;


2,问题:增加作者,给一个提示显示在控制台
create or replace trigger del_trigger
after delete on AUTHOR
for each row

begin

    delete fromBOOK where AUTHORid=:old.AUTHORid;
end;


--create insert


create or replace trigger save_trigger
after insert on AUTHOR
for each row

begin

  dbms_output.put_line('author:'||:new.aname||'insert...success  :)');
end;
上.
//开启输出流 set serveroutput on；