下面的sql语句实现没有表table_1,那么则建立表talbe_1,请问下在sql * plus里执行后为什么dbms_output.put_line('create 1 table successfully'); 这一句没有打印出来?
declare
v_count number;
sqlstr varchar2(100);
begin
select count(1) into v_count from user_all_tables
where table_name=upper('table_1');
if v_count=0 then
sqlstr:= 'create table table_1(a varchar2(10))';
execute immediate sqlstr;
dbms_output.put_line('create 1 table successfully');
end if;
end ;
/
declare
v_count number;
sqlstr varchar2(100);
begin
select count(1) into v_count from user_all_tables
where table_name=upper('table_1');
if v_count=0 then
sqlstr:= 'create table table_1(a varchar2(10))';
execute immediate sqlstr;
dbms_output.put_line('create 1 table successfully');
end if;
end ;
/
1,table_1表已经存在,所以没有走你的if里面的打印语句。
2,select count(1) into v_count 这里面是不能赋值的,因为如果查询到的值为null的话,是无法赋值给v_count的,会报data_not_found异常的。
else dbms_output.put_line(v_count);
添上这句再试试
v_count number;
sqlstr varchar2(100);
begin
select count(1) into v_count from user_all_tables
where table_name=upper('table_1');
if v_count=0 then
sqlstr:= 'create table table_1(a varchar2(10))';
execute immediate sqlstr;
dbms_output.put_line('create 1 table successfully');
end if;
--这里但into异常的时候,对data_not_found异常进行处理。
exception
when others then
sqlstr:= 'create table table_1(a varchar2(10))';
execute immediate sqlstr;
dbms_output.put_line('create 1 table successfully');
end ;
/