Go语言视频零基础入门到精通

时间旅人

在Cun储过程中如1何插入.gengxin.删除表中De数据，2求li子？
欢迎来DaoJava学xi者論坛,zhuan載请注明地址:http://www.javaxxz.com.

彩虹糖梦

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楼的基础上加上异常处理

时间旅人

借用下一楼的例子
SQL code 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;

彩虹糖梦

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

彩虹糖梦

那样单步调试后就会知道哪句ＳＱＬ语句有问题了

fangfang1

SQL code 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;