''和null
''是与null是有区别的,但是case里是一样的
select dump(null),dump('') from dual;
select case when '' is null then null else not null end from dual;
任何和null的操作都是null
select null+10 from dual;
NULL+10
----------
select null||'abc' from dual;
select concat(null,'abc') from dual;
null和比较运算符 都返回unknown
select * from dual where 10>null
DUMMY
----
相关函数
select replace('abc',null) from dual;
REPLACE('ABC',NULL)
-------------------
abc
select rtrim('abc_','_') from dual;
RTRIM('ABC_','_')
-----------------
abc
select rtrim('abc_',null) from dual;
RTRIM('ABC_',NULL)
------------------
类型转换
select nvl(null,0) from dual;
NVL(NULL,0)
-----------
0
select nvl('',0) from dual;
NVL('',0)
---------
0
select nvl(to_char(null),0) from dual;
NVL(TO_CHAR(NULL),0)
--------------------
0
nullif nvl2 nvl coalesce 用法
简单的行列转置
char的坏处
1.错误的使用绑定变量
2.浪费表字段和索引存储空间
create table work_rec(emp_no number(10),emp_name varchar2(100),done_type varchar2(100),done_count varchar2(10));
insert into work_rec values(1,'dj','测试量',10);
DECLARE
a CHAR(10):='abc';
b VARCHAR(10):='abc';
BEGIN
IF a=trim(rpad('abc',10)) THEN
dbms_output.put_line(10);
END IF;
IF a=rpad('abc',10) THEN
dbms_output.put_line(20);
END IF;
IF b=trim(rpad('abc',10)) THEN
dbms_output.put_line(30);
END IF;
IF b=rpad('abc',10) THEN
dbms_output.put_line(40);
END IF;
END;