2006年01月17日

declare
  v_sal varchar2(20);
begin
  select sal into v_sal from emp;
exception
  when too_many_rows then
    dbms_output.put_line('Return too many rows!');
end;
/

declare
  v_sal varchar2(20);
begin
  select sal into v_sal from emp where 1=0;
exception
  when too_many_rows then
    dbms_output.put_line('Return too many rows!');
  when no_data_found then
    dbms_output.put_line('No rows!');
end;
/

declare
  v_sal varchar2(20);
begin
  --select sal into v_sal from emp where 1 = 0;
  insert into emp
    select * from emp;
exception
  when dup_val_on_index then
    dbms_output.put_line('违反约束!');
  when too_many_rows then
    dbms_output.put_line('Return too many rows!');
  when no_data_found then
    dbms_output.put_line('No rows!');
end;
/
---20 5 
declare
  v_count  number;
  v_deptno dept.deptno%type := &p_deptno;--需要用户输入
  e_emp exception;
  e_dept exception;
  pragma exception_init(e_emp, -2292);--将oracle异常传递到自定义的异常中
begin
  select 1 into v_count from dept where deptno = v_deptno;
  if v_deptno < 10 then
    raise e_dept;
  end if;
  delete from dept where deptno = v_deptno;
EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('No Such a Department');
  WHEN e_dept THEN
    DBMS_OUTPUT.PUT_LINE('This department can not be removed.');
  WHEN e_emp THEN
    DBMS_OUTPUT.PUT_LINE('Cannot remove dept ' || TO_CHAR(v_deptno) ||
                         '.  Employees exist. ');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
/

 

create  table log_error
(
error_time date,
error_code number,
error_message varchar2(200));

 

create table t12(
   a number);

declare
  v_sqlcode number;
  v_sqlerrm varchar2(2000);
begin
  insert into t12 values ('a');
exception
  when others then
    v_sqlcode := sqlcode;
    v_sqlerrm := sqlerrm;
    insert into log_error values (sysdate, v_sqlcode, v_sqlerrm);
end;
/

declare
  v_count  number;
  v_deptno dept.deptno%type := &p_deptno; --需要用户输入
  e_emp exception;
  e_dept exception;
  pragma exception_init(e_emp, -2292); --将oracle异常传递到自定义的异常中
begin
  select 1 into v_count from dept where deptno = v_deptno;
  if v_deptno < 70 then
    --输入40，触发该异常
    raise_application_error(-20002, 'aaa');
  end if;
  delete from dept where deptno = v_deptno;
EXCEPTION
  -- WHEN no_data_found THEN
  --  DBMS_OUTPUT.PUT_LINE('No Such a Department');
  WHEN e_emp THEN
    DBMS_OUTPUT.PUT_LINE('Cannot remove dept ' || TO_CHAR(v_deptno) ||'.  Employees exist. ');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM); --捕捉-20002的异常
 -- when others then
  --  DBMS_OUTPUT.PUT_LINE('others');--代替-20002触发
end;
/

 CREATE OR REPLACE PROCEDURE raise_salary
 (v_id in emp.empno%TYPE)
     IS
 v_sal number;
 BEGIN
    select sal
     into v_sal
    from emp_t
    where empno=v_id;
    if v_sal<1200 then
     UPDATE emp_t
     SET    sal = sal * 1.20
     WHERE  empno = v_id;
    elsif v_sal<2000 then
     UPDATE emp_t
     SET    sal = sal * 1.10
     WHERE  empno = v_id;
    elsif v_sal<5000 then
     UPDATE emp_t
     SET    sal = sal * 1.05
     WHERE  empno = v_id;
    end if;
    commit;
 END;
/
--
create or replace procedure raise_salary
(v_id in emp.empno%type)
is
begin
 update emp set sal=sal*1.2 where empno=v_id;
 commit;
end raise_salary;
/

create sequence seq_deptno start with 50 increment by 10;

create or replace procedure add_dept
(v_name in dept.dname%type default 'unknown',
v_loc in dept.loc%type default 'unknown')
is
begin
 insert into dept_t(deptno,dname,loc) values (seq_deptno.nextval,v_name,v_loc);
 commit;
end;
/
exec add_dept('itd','jiguanlou');
select * from dept_t;

create or replace procedure process_emps is
  cursor emp_cursor is
    select empno from emp;
begin
  for emp_rec in emp_cursor loop
    raise_salary(emp_rec.empno);
  end loop;
  commit;
end;
/

DECLARE
   CURSOR bin_cur(part_number NUMBER) IS
      SELECT amt_in_bin FROM bins
         WHERE part_num = part_number AND amt_in_bin > 0
         ORDER BY bin_num
         FOR UPDATE OF amt_in_bin;
   bin_amt        bins.amt_in_bin%TYPE;
   total_so_far   NUMBER(5) := 0;
   amount_needed  CONSTANT NUMBER(5) := 1000;
   bins_looked_at NUMBER(3) := 0;
BEGIN
   OPEN bin_cur(5469);
   WHILE total_so_far < amount_needed LOOP
      FETCH bin_cur INTO bin_amt;
      EXIT WHEN bin_cur%NOTFOUND;
         -- if we exit, there's not enough to fill the order
      bins_looked_at := bins_looked_at + 1;
      IF total_so_far + bin_amt < amount_needed THEN
         UPDATE bins SET amt_in_bin = 0
            WHERE CURRENT OF bin_cur;
               -- take everything in the bin
         total_so_far := total_so_far + bin_amt;
      ELSE  -- we finally have enough
         UPDATE bins SET amt_in_bin = amt_in_bin
            - (amount_needed - total_so_far)
            WHERE CURRENT OF bin_cur;
         total_so_far := amount_needed;
      END IF;
   END LOOP;

   CLOSE bin_cur;
   INSERT INTO temp
      VALUES (NULL, bins_looked_at, '<- bins looked at');
   COMMIT;
END;
/