存储过程中的异常

Question

在存储过程中如何插入.更新.删除表中的数据，求例子？

Answer 1

CREATE OR REPLACE PROCEDURE HR.add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;--插入的实例!

Answer 2

CREATE OR REPLACE PROCEDURE HR.add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
    
     
exception
     when others then
       dbms_output.put_line('数据插入异常！');
END add_job_history;
在1楼的基础上加上异常处理

Answer 3

借用下一楼的例子
CREATE OR REPLACE PROCEDURE HR.add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
exception
--when 指定的异常 then
--...
when others then--处理所有异常,一般放在异常处理最后
dbms_output.put_line(sqlcode || sqlerrm);
--sqlcode是oracle错误号ORA-XXXXX,sqlerrm是错误信息
END add_job_history;

Answer 4

用oracle自带的调试工具单步调试即可
方法：打开pl/sql＝＝>找到该存储过程所在的包==>右键包头==>点击“添加调试信息”==>右键该存储过程==>调试==>输入你的参数(若有的话)==>按F9==>再按Ctl+N

Answer 5

CREATE OR REPLACE PROCEDURE LOAN_BOOK(B_ID IN NUMBER, U_ID IN NUMBER) IS  OR_ID   NUMBER(6);
  OR_NAME VARCHAR2(1000);
  BK_ID   NUMBER(12);
  BK_NAME VARCHAR2(1000);
  ST_DATE DATE;
  ED_DATE DATE;  H1 NUMBER(6);
  H2 NUMBER(6);
  H3 NUMBER(6);  L VARCHAR2(10);
  S VARCHAR2(20);BEGIN  SELECT STATUS INTO S FROM BOOK WHERE BOOK_ID = B_ID;  IF S = '在库' THEN
  
    SELECT HOLD1, HOLD2, HOLD3, LIMITED --查询
      INTO H1, H2, H3, L
      FROM EACH_OWNER
     WHERE OWNER_ID = U_ID;
  
    IF L = 'N' THEN
    
      ST_DATE := SYSDATE;
      ED_DATE := ST_DATE + 15;
    
      SELECT USER_ID, USER_NAME
        INTO OR_ID, OR_NAME
        FROM USERS
       WHERE USER_ID = U_ID;
      SELECT BOOK_ID, BOOK_NAME
        INTO BK_ID, BK_NAME
        FROM BOOK
       WHERE BOOK_ID = B_ID;
      INSERT INTO LOAN  --插入
      VALUES
        (SQ_LOAN_ID.NEXTVAL,
         OR_ID,
         OR_NAME,
         BK_ID,
         BK_NAME,
         ST_DATE,
         ED_DATE);
      UPDATE BOOK SET STATUS = '借出' WHERE BOOK_ID = B_ID;
      COMMIT;
    
      DBMS_OUTPUT.PUT_LINE('YOU HAVE BORROWED THE BOOK SUCCESSFULLY!');
    
      IF H1 = 0 THEN
        UPDATE EACH_OWNER SET HOLD1 = B_ID WHERE OWNER_ID = U_ID;
        DBMS_OUTPUT.PUT_LINE('11111111');
      ELSIF H2=0 THEN
        UPDATE EACH_OWNER SET HOLD2 = B_ID WHERE OWNER_ID = U_ID;--更新
        DBMS_OUTPUT.PUT_LINE('22222222');
      ELSIF H3=0 THEN
        UPDATE EACH_OWNER
           SET HOLD3 = B_ID
         WHERE OWNER_ID = U_ID;
        DBMS_OUTPUT.PUT_LINE('33333');
      
      END IF;
      
      SELECT HOLD1,HOLD2,HOLD3
      INTO H1,H2,H3 FROM EACH_OWNER
      WHERE OWNER_ID=U_ID;
      
      IF H1<>0 AND H2<>0 AND H3<>0 THEN
      UPDATE EACH_OWNER 
      SET LIMITED='Y'
      WHERE OWNER_ID=U_ID;
      END IF;
      COMMIT;
    
    ELSE
      DBMS_OUTPUT.PUT_LINE('YOU HAVE ALREADY OWNED 3 BOOKS!CANNOT OWN ANY MORE!');
    
    END IF;
  
  ELSE
    DBMS_OUTPUT.PUT_LINE('THE BOOK IS ALREADY OWNED BY SOMEONE.PLEASE MAKE SURE THE DETIAL!');
  
  END IF;EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('YOU MAY HAVE ENTERED A WRONG USERID OR BOOKID!');
  
END LOAN_BOOK;