oracle包、触发器2

select * from dept;

declare
    -- 所谓游标，其实就是指向查询结果的引用
    cursor dept_cursor is
    select * from dept;
    
    v_deptno dept.deptno%type;
    v_dname dept.dname%type;
    v_loc dept.loc%type;
begin
    -- 打开游标，执行查询
    open dept_cursor;
    
    -- 每次抓一行
    loop
        -- 每次fetch时，记录指针会自动下移。
        fetch dept_cursor
        into v_deptno , v_dname , v_loc;
        -- 如果上一次没有抓到记录，已经结束了
        exit when dept_cursor%notfound;
        dbms_output.put_line(v_deptno || '--' 
           || v_dname || '--' || v_loc);
    end loop;
end;

declare
    -- 所谓游标，其实就是指向查询结果的引用
    cursor dept_cursor is
    select * from dept;
    
    -- 使用record变量可以更方便地去抓一条记录。
    v_dept_record dept%rowtype;
begin
    -- 打开游标，执行查询
    open dept_cursor;
    
    -- 每次抓一行
    loop
        -- 每次fetch时，记录指针会自动下移。
        fetch dept_cursor
        into v_dept_record;
        -- 如果上一次没有抓到记录，已经结束了
        exit when dept_cursor%notfound;
        dbms_output.put_line(v_dept_record.deptno || '--' 
           || v_dept_record.dname || '--' || v_dept_record.loc);
    end loop;
    
    -- 关闭游标
    close dept_cursor;
end;

declare
    -- 带参数的游标
    -- 一旦在声明游标时候指定了参数，那接下来就可以在查询语句中使用参数了。
    cursor dept_cursor(p_no varchar2 , p_name varchar2) is 
    select * from dept
    where to_char(deptno) not like p_no and dname like p_name;
    
    -- 使用record变量可以更方便地去抓一条记录。
    v_dept_record dept%rowtype;    
begin
    -- 动态传入了参数
    open dept_cursor('%0' , '新%');

    -- 每次抓一行
    loop
        -- 每次fetch时，记录指针会自动下移。
        fetch dept_cursor
        into v_dept_record;
        -- 如果上一次没有抓到记录，已经结束了
        exit when dept_cursor%notfound;
        dbms_output.put_line(v_dept_record.deptno || '--' 
           || v_dept_record.dname || '--' || v_dept_record.loc);
    end loop;
    
    close dept_cursor ;
    
    -- 带参数的游标，很像Java里PreparedStatement的用法。
    
    -- 动态传入了参数
    open dept_cursor('8%' , 'a%');

    -- 每次抓一行
    loop
        -- 每次fetch时，记录指针会自动下移。
        fetch dept_cursor
        into v_dept_record;
        -- 如果上一次没有抓到记录，已经结束了
        exit when dept_cursor%notfound;
        dbms_output.put_line(v_dept_record.deptno || '--' 
           || v_dept_record.dname || '--' || v_dept_record.loc);
    end loop;
    
    close dept_cursor ;
end;

select * from dept;

declare
    -- 所谓游标，其实就是指向查询结果的引用
    cursor dept_cursor is
    select * from dept;
    
begin    
    -- 每次抓一行
    for v_dept_record in dept_cursor loop
        dbms_output.put_line(v_dept_record.deptno || '--' 
           || v_dept_record.dname || '--' || v_dept_record.loc);
    end loop;    
end;

declare
    -- 带参数的游标
    -- 一旦在声明游标时候指定了参数，那接下来就可以在查询语句中使用参数了。
    cursor dept_cursor(p_no varchar2 , p_name varchar2) is 
    select * from dept
    where to_char(deptno) not like p_no and dname like p_name;
      
begin

    -- 每次抓一行
    for v_dept_record in dept_cursor('%0' , '新%') loop
        dbms_output.put_line(v_dept_record.deptno || '--' 
           || v_dept_record.dname || '--' || v_dept_record.loc);
    end loop;
    
    -- 带参数的游标，很像Java里PreparedStatement的用法。
    
    -- 每次抓一行
    for v_dept_record in dept_cursor('%8' , 'a%') loop
        dbms_output.put_line(v_dept_record.deptno || '--' 
           || v_dept_record.dname || '--' || v_dept_record.loc);
    end loop;
    
end;

declare
    -- 查询记录，并尝试对结果集进行修改
    cursor dept_cursor(v_deptno varchar2) is
    select * from dept 
    where to_char(deptno) not like v_deptno
    for update;

begin
    -- 每次抓一行
    for v_dept_record in dept_cursor('%0') loop
        update dept
        set dname = '我部门'
        where current of dept_cursor;
    end loop;
  
end;

select * from dept;

rollback;

declare
    -- 定义了一个游标引用类型
    type fkjava_cursor_type is ref cursor;
    
    -- 定义了游标引用  变量
    v_dept_cursor_ref fkjava_cursor_type;
begin
    open v_dept_cursor_ref for
    select * from dept;
    
    -- PL/SQL不会立即读取游标引用里的结果。
    -- PL/SQL需要立即取出查询结果，应该使用普通游标，而不是游标引用。
   
end;

create or replace procedure get_dept_data(p_deptno dept.deptno%type
     , p_data out first_package.fkjava_cursor_type)
is
begin
    -- 通过这种把查询写在存储过程中，
    -- 可以充分利用数据库特性，从而提高程序查询性能。
    
    -- 坏处：导致该程序过分依赖特定数据库，从而降低可移植性。
    open p_data for
    select * from dept
    where deptno > p_deptno;
end;

select * from dept;

declare
     v_dept_record dept%rowtype;
begin
     select *
     into v_dept_record
     from dept;
exception 
     -- 异常捕捉，就是根据名字来捕捉的。
     when too_many_rows then
          dbms_output.put_line('查询得到的记录太多！');
     when others then
          dbms_output.put_line('其他异常！');
end;

declare
     -- 定义了一个异常。
     e_not_unique exception;
     
     -- 把异常代码与异常名字关联起来。
     -- 实质就是：为指定异常起个名字。
     PRAGMA EXCEPTION_INIT(
            e_not_unique, -1);

begin
     insert into dept tables 
     values(20 , 'aa' , 'bbb');
exception
     when e_not_unique then
          dbms_output.put_line('数据重复');
     when others then
          dbms_output.put_line('其他异常');
end;

create or replace trigger emp_sal_trigger
before
-- 只有对sal这列进行插入、修改时才会触发
insert or update of sal on emp
for each row
declare
    -- 定义异常
    e_illegal_sal exception;
begin
    -- 如果插入、修改的sal不在1000～3500之间
    if :new.sal >3500 or :new.sal < 1000 then
        -- 通过抛出异常，可以阻止用户的修改！ 
        raise e_illegal_sal;
    end if;
end;