建立儲存過程小提示tip_plsql_小經驗

SQL> create or replace procedure p_find_question_by_title(v_title varchar2,o_result out sys_refcursor)
  2  --線上答疑顯示所有問題
  3 
  4  --顯示的列：學段，科目,題目內容,提問者，瀏覽數，狀態，更新時間
  5  --涉及到的表：問題資訊,問題統計資訊,學段科目資訊
  6  as
  7  v_sqlstr varchar2(4000);
  8  begin
  9  v_sqlstr:='select
 10                  xk.teachstage,--學段
 11                  xk.subjectname,--科目
 12                  qi.question_title,--題目內容
 13                  qi.question_id,--提問者
 14                  qs.browse_count,--瀏覽數
 15                  qi.question_status,--狀態
 16                  qs.latest_update_time --更新時間
 17            from Q_QUESTIONINFO qi,Q_QUESTION_STAT qs,E_SUBJECTINFO xk
 18            where qi.questionid=qs.questionid and qi.subject_id=xk.subjectid
 19            where question_title like ":title"
 20            ';
 21  open o_result for v_sqlstr using v_title;
 22           
 23  end;
 24  /

Procedure created.

SQL> var x refcursor;
SQL> exec p_find_question_title('zxy',:x);
BEGIN p_find_question_title('zxy',:x); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'P_FIND_QUESTION_TITLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> exec p_find_question_title('zxy',:x);
BEGIN p_find_question_title('zxy',:x); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'P_FIND_QUESTION_TITLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> exec p_find_question_by_title('zxy',:x);
BEGIN p_find_question_by_title('zxy',:x); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "PD.P_FIND_QUESTION_BY_TITLE", line 21
ORA-06512: at line 1

 

 

小結：

   1，建立儲存過程成功，只是說明建立儲存過程的語法是成功的

   2，不會驗證儲存過程內部的業務處理邏輯的sql是否正常,是否可以成功執行

 

   3,所以一定在建立成功後，要手工呼叫，進行除錯排錯，最好顯式執行下儲存過程的sql是否成功執行