如果存储过程编译异常，用什么命令获得异常信息
日期：2014-05-16 浏览次数：20886 次
如果存储过程编译错误，用什么命令获得错误信息
create or replace procedure proc_count_age
as
count1 tb_stu_info%type;
begin
declare cursor c_count is select (case when a.age>=30 and a.age<=39 then '30-39'
when a.age>=40 and a.age<=50 then '40-50'
when a.age<30 then '<30'
else '>50' end) age,count(*)人数
from (select to_number(to_char(sysdate,'yyyy'))-to_number(substr(birth,1,4)) age
from tb_stu_info,dual)a group by
(case when a.age>=30 and a.age<=39 then '30-39'
when a.age>=40 and a.age<=50 then '40-50'
when a.age<30 then '<30'
else '>50' end);
begin
DBMS_OUTPUT.PUT_LINE('年龄段 人数');
for count1 in c_count loop
DBMS_OUTPUT.PUT_LINE(count1.age||' '||count1.人数);
end loop;
end;
end;
/
过程是这样的，哪不对呀
------解决方案--------------------
show errors
create or replace procedure proc_count_age
as
count1 tb_stu_info%type;
begin
declare cursor c_count is select (case when a.age>=30 and a.age<=39 then '30-39'
when a.age>=40 and a.age<=50 then '40-50'
when a.age<30 then '<30'
else '>50' end) age,count(*)人数
from (select to_number(to_char(sysdate,'yyyy'))-to_number(substr(birth,1,4)) age
from tb_stu_info,dual)a group by
(case when a.age>=30 and a.age<=39 then '30-39'
when a.age>=40 and a.age<=50 then '40-50'
when a.age<30 then '<30'
else '>50' end);
begin
DBMS_OUTPUT.PUT_LINE('年龄段 人数');
for count1 in c_count loop
DBMS_OUTPUT.PUT_LINE(count1.age||' '||count1.人数);
end loop;
end;
end;
/
过程是这样的，哪不对呀
------解决方案--------------------
show errors
- SQL code
SQL> create or replace procedure proc_count_age
2 as
3 count1 tb_stu_info%type;
4 begin
5 declare cursor c_count is select (case when a.age>=30 and a.age<=39 then '30-39'
6 when a.age>=40 and a.age<=50 then '40-50'
7 when a.age<30 then '<30'
8 else '>50' end) age,count(*)人数
9 from (select to_number(to_char(sysdate,'yyyy'))-to_number(substr(birth,1,4)) age
10 from tb_stu_info,dual)a group by
11 (case when a.age>=30 and a.age<=39 then '30-39'
12 when a.age>=40 and a.age<=50 then '40-50'
13 when a.age<30 then '<30'
14 else '>50' end);
15 begin
16 DBMS_OUTPUT.PUT_LINE('年龄段 人数');
17 for count1 in c_count loop
18 DBMS_OUTPUT.PUT_LINE(count1.age||' '||count1.人数);
19 end loop;
20 end;
21 end;
22 /
Warning: Procedure created with compilation errors
SQL> show errors
Errors for PROCEDURE SCOTT.PROC_COUNT_AGE:
LINE/COL ERROR
-------- ------------------------------------------------------
3/8 PLS-00201: identifier 'TB_STU_INFO' must be declared
3/8 PL/SQL: Item ignored
10/6 PL/SQL: ORA-00942: table or view does not exist
5/27 PL/SQL: SQL Statement ignored
18/22 PLS-00364: loop index variable 'COUNT1' use is invalid
18/1 PL/SQL: Statement ignored
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
