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 /
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;
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
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
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
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是否成功执行
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-667028/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-667028/