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；