不可能吧。
SQL> declare
2 a varchar2(10):='fdfd';
3 begin
4 if a is not null then
5 DBMS_OUTPUT.PUT_LINE('a is not null;');
6 end if;
7 if a is null then
8 DBMS_OUTPUT.PUT_LINE('a is null;');
9 end if;
10 end;
11 /
a is not null;PL/SQL procedure successfully completed.你还是在仔细看一下你的程序吧。
SQL> declare
2 a varchar2(10):='fdfd';
3 begin
4 if a is not null then
5 DBMS_OUTPUT.PUT_LINE('a is not null;');
6 end if;
7 if a is null then
8 DBMS_OUTPUT.PUT_LINE('a is null;');
9 end if;
10 end;
11 /
a is not null;PL/SQL procedure successfully completed.你还是在仔细看一下你的程序吧。
解决方案 »
- 为什么sys用户的很多系统权限对应的ADMIN_OPTION为no还能给其他用户授权
- oracle10导入数据求教?
- 关于远程导入dmp文件的问题
- 关于 oracle rollup命令的讨论
- 求一循环更新数据的语句或存储过程
- 菜鸟问,我在oms中用SYSTEM登录数据库时,为何连接身份只能选 normal.
- 有什么标准的sql语句来insert ,update lob对象?
- 高分求购win2000下定时备份数据库的代码,在线
- 请教,一次查询取得的数据有几万条时,用什么方法可以提高效率!*在线等待*
- [求助]DataGuard备库需要哪些文件?
- 200分问一个高难度问题,不知这样的功能能否用脚本实现?UP有分
- oracle8。17 的Management server (oms ) 的配置文件在哪,怎么配置
v_sql:='SELECT '||v_hzlx||' FROM '||v_table_list||' WHERE '||v_conditions||'';
DBMS_OUTPUT.PUT_LINE('not null:'||v_sql);
END IF;
IF v_conditions IS NULL THEN
v_sql:='SELECT '||v_hzlx||' FROM '||v_table_list||'';
DBMS_OUTPUT.PUT_LINE('null'||v_sql);
END IF;
create or replace procedure SP_BIG_REPORT(
V_BBMC IN STRING,
V_SZDWBH IN STRING
) IS
/*----------------------------------------------------------------------------------
功能:得到通用统计报表的数据,存入表T_BBSJB
编写:廖志强
日期:2002年9月11日
-----------------------------------------------------------------------------------*/
TYPE c_curtype IS REF CURSOR;
c_cur c_curtype;
str STRING(2000):='';
sql_str STRING(10000):='';
v_table_list STRING(1000):='';--所有关联的表
v_hbh t_bbhb.hbh%TYPE;--行编号
v_lbh t_bblb.lbh%TYPE;--列编号
v_hmc t_bbhb.hmc%TYPE;--行名称
v_tmp STRING(1000):='';--临时变量
v_gltj STRING(2000):='';--相关条件
v_htj STRING(1000):='';--行条件
v_ltj STRING(1000):='';--列条件
v_conditions STRING(4000):='';--总条件
v_select STRING(1000):='';
v_jsgs t_bbhb.tjhjsgs%TYPE;--计算公式
v_jsjb t_bbhb.hjsjbpx%TYPE;--计算级别
v_hzlx t_bblb.hzlx%TYPE;--汇总类型
v_sql STRING(9000):='';
sub_str STRING(100);
i INTEGER:=1;
k INTEGER;
v_now STRING(14);
CURSOR c_record IS SELECT TRIM(hbh) FROM t_bbhb WHERE bbmc=''||v_bbmc||'' ORDER BY hjsjbpx;
CURSOR c_column IS SELECT TRIM(lbh) FROM t_bblb WHERE bbmc=''||v_bbmc||'' ORDER BY ljsjbpx;
BEGIN --Get "select ..."
v_conditions:='fffffff';
v_jsjb:=1;
IF v_jsjb=1 THEN
v_select:='count(*)';
IF v_hzlx IS NULL THEN
v_hzlx:=' COUNT(*) ';
END IF;
IF TRIM(v_conditions) IS NOT NULL THEN
v_sql:='SELECT '||v_hzlx||' FROM '||v_table_list||' WHERE '||v_conditions;
END IF;
IF TRIM(v_conditions) IS NULL THEN
v_sql:='SELECT '||v_hzlx||' FROM '||v_table_list;
END IF;
ELSIF instrb(v_jsgs,'SUM')>0 THEN
v_select:='SUM('||v_lbh||')';
v_tmp:=ltrim(v_jsgs,'SUM,');
v_tmp:=REPLACE(v_tmp,',',''',''');
v_tmp:=''''||v_tmp||'''';
v_sql:='SELECT '||v_select||' FROM T_BBSJB'||
' WHERE XMBH IN ('||v_tmp||')'||
' AND HZRQ='''||v_now||''' AND SZDWBH='''||v_szdwbh||'''';
ELSIF instrb(v_jsgs,'AVG')>0 THEN
v_tmp:=ltrim(v_jsgs,'AVG,');
str:=REPLACE(v_tmp,',','+');
i:=lengthb(v_tmp)-length(REPLACE(v_tmp,'+',''))+1;
v_select:='SUM('||v_lbh||')/'||i||'';
v_tmp:=REPLACE(v_tmp,',',''',''');
v_tmp:=''''||v_tmp||'''';
v_sql:='SELECT '||v_select||' FROM T_BBSJB '||
'WHERE XMBH IN ('||v_tmp||')'||
' AND HZRQ='''||v_now||''' AND SZDWBH='''||v_szdwbh||'''';
ELSIF instrb(v_jsgs,'分子')>0 THEN
v_select:='SUM('||v_lbh||')';
v_tmp:=substrb(v_jsgs,(instrb(v_jsgs,'子,')+3),(instrb(v_jsgs,',分母')-6));
v_tmp:=REPLACE(v_tmp,',',''',''');
v_tmp:=''''||v_tmp||'''';
v_sql:='(SELECT '||v_select||' FROM T_BBSJB'||
' WHERE XMBH IN ('||v_tmp||')'||
' AND HZRQ='''||v_now||''' AND SZDWBH='''||v_szdwbh||''')';
v_tmp:=substrb(v_jsgs,(instrb(v_jsgs,',分母')+6),(length(v_jsgs)+6));
v_tmp:=REPLACE(v_tmp,',',''',''');
v_tmp:=''''||v_tmp||'''';
v_sql:='SELECT TRUNC(('||v_sql||'/(SELECT '||v_select||' FROM T_BBSJB WHERE XMBH IN ('||v_tmp||')'||
' AND HZRQ='''||v_now||''' AND SZDWBH='''||v_szdwbh||''')'||
'),4)*100 FROM DUAL';
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;end SP_BIG_REPORT;
create or replace procedure SP_BIG_REPORT(
V_BBMC IN STRING,
V_SZDWBH IN STRING
) IS TYPE c_curtype IS REF CURSOR;
c_cur c_curtype;
str STRING(2000):='';
sql_str STRING(10000):='';
v_table_list STRING(1000):='';--所有关联的表
v_hbh t_bbhb.hbh%TYPE;--行编号
v_lbh t_bblb.lbh%TYPE;--列编号 v_tmp STRING(1000):='';--临时变量 v_conditions STRING(4000):='';--总条件
v_select STRING(1000):='';
v_jsgs t_bbhb.tjhjsgs%TYPE;--计算公式
v_jsjb t_bbhb.hjsjbpx%TYPE;--计算级别
v_hzlx t_bblb.hzlx%TYPE;--汇总类型
v_sql STRING(9000):=''; v_now STRING(14); BEGIN --Get "select ..."
v_conditions:='fffffff';
v_jsjb:=1;
IF v_jsjb=1 THEN
v_select:='count(*)';
IF v_hzlx IS NULL THEN
v_hzlx:=' COUNT(*) ';
END IF;
IF TRIM(v_conditions) IS NOT NULL THEN
v_sql:='SELECT '||v_hzlx||' FROM '||v_table_list||' WHERE '||v_conditions;
END IF;
IF TRIM(v_conditions) IS NULL THEN
v_sql:='SELECT '||v_hzlx||' FROM '||v_table_list;
END IF;
ELSIF instrb(v_jsgs,'SUM')>0 THEN
v_select:='SUM('||v_lbh||')';
v_tmp:=ltrim(v_jsgs,'SUM,');
v_tmp:=REPLACE(v_tmp,',',''',''');
v_tmp:=''''||v_tmp||'''';
v_sql:='SELECT '||v_select||' FROM T_BBSJB'||
' WHERE XMBH IN ('||v_tmp||')'||
' AND HZRQ='''||v_now||''' AND SZDWBH='''||v_szdwbh||'''';
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;end SP_BIG_REPORT;
我测试了一个过程:
程序1:
create or replace procedure sp_liao IS
v_tmp STRING(100);
v_sql STRING(100);
v_conditions INTEGER;
BEGIN
v_sql:='ddd';
v_conditions:=1;
IF v_conditions=1 THEN
IF TRIM(v_sql) IS NOT NULL THEN
v_tmp:='lll';
END IF;
IF TRIM(v_sql) IS NULL THEN
v_tmp:='kkk';
END IF;
ELSIF v_conditions=2 THEN
NULL;
END IF;
end sp_liao;
------------
v_tmp:='lll'; 和v_tmp:='kkk';都会执行但最后v_tmp='lll'程序2:
create or replace procedure sp_liao IS
v_tmp STRING(100);
v_sql STRING(100);
v_conditions INTEGER;
BEGIN
v_sql:='ddd';
v_conditions:=1;
IF v_conditions=1 THEN
IF TRIM(v_sql) IS NULL THEN
v_tmp:='lll';
END IF;
IF TRIM(v_sql) IS NOT NULL THEN
v_tmp:='kkk';
END IF;
ELSIF v_conditions=2 THEN
NULL;
END IF;
end sp_liao;
只执行 v_tmp:='kkk';,最后结果v_tmp='kkk'
呵呵,好纳闷
2 a varchar2(10):=' ';
3 begin
4 if trim(a) is not null then
5 dbms_output.put_line('not null');
6 end if;
7 if trim(a) is null then
8 dbms_output.put_line('is null');
9 end if;
10 end;
11 /
is nullPL/SQL 过程已成功完成。SQL> declare
2 a varchar2(10):=' aa ';
3 begin
4 if trim(a) is not null then
5 dbms_output.put_line('not null');
6 end if;
7 if trim(a) is null then
8 dbms_output.put_line('is null');
9 end if;
10 end;
11 /
not nullPL/SQL 过程已成功完成。SQL>
肯定不是判断is null的问题,你多加几个put_line,可能是别的地方的问题.
SQL> create or replace procedure sp_liao IS
2 v_tmp STRING(100);
3 v_sql STRING(100);
4 v_conditions INTEGER;
5 BEGIN
6 v_sql:='ddd';
7 v_conditions:=1;
8 IF v_conditions=1 THEN
9 IF TRIM(v_sql) IS NOT NULL THEN
10 v_tmp:='lll';
11 dbms_output.put_line('is not null:'||v_tmp);
12 END IF;
13 IF TRIM(v_sql) IS NULL THEN
14 v_tmp:='kkk';
15 dbms_output.put_line('is null:'||v_tmp);
16 END IF;
17 ELSIF v_conditions=2 THEN
18 NULL;
19 END IF;
20 end sp_liao;
21 /过程已创建。SQL> exec sp_liao;
is not null:lllPL/SQL 过程已成功完成。只执行了一个判断.
当我看到好像两个if都执行时,事实是只执行了一个,因为我加了
断点后,程序并没有停下来
唉,白忙了半天
other error
up?
这个PL/SQL DEVELOPER害我不浅