oracle查询版本号
- select * from v$version;
- select * from product_component_version;
- SET SERVEROUTPUT ON; EXEC dbms_output.put_line( dbms_db_version.version );
oracle NULL与’'空字符串
- oracle中在可为null的字段上做逻辑关系运算要格外小心,如 <>,>,=,<,任何与null的运算结果都返回false.因此对于可能为null的字段运算判断要用 is null 来判断,或者使用函数nvl、decode处理后在判断,比如:where 字段名 is null 或者where nvl(字段名,0) = 0; 假设字段类型是数字
- Oracle中的空字符串基本上是被当成空NULL来处理的,例如:select nvl(’’,‘NULL’) from dual 返回’NULL’,select 1 from dual where ‘’ is null 返回1。
- ‘’ = ''返回的结果为假,而NULL = ‘‘将返回结果为假;’’ is NULL 不报错,而NULL is ‘’ 将发生找不到关键字NULL的错误
select case when ''='' then 'true' else 'false' end from dual;
select case when NULL=NULL then 'true' else 'false' end from dual;
select case when NULL='' then 'true' else 'false' end from dual;
select case when '' IS NULL then 'true' else 'false' end from dual;
select case when NULL IS NULL then 'true' else 'false' end from dual;
select case when '' IS '' then 'true' else 'false' end from dual;
select case when NULL IS '' then 'true' else 'false' end from dual;
- 判断两个字符串是否相等的函数
create or replace function isequal(var1 in varchar2, var2 in varchar2)
return number
is
begin
if (var1 is null and var2 is null or
var1 is not null and var2 is not null and var1 = var2) then
return 1;
else
return 0;
end if;
exception
when others then
return - 1;
end;