Oracle基础知识总结【1】
1.Case When语句
case 变量 when 值 then 返回值1 else 返回值2 end
2.Oracle中字符串连接符为||
nvl(字段名,替换值),NVL函数用来处理空串(即null值)
3. 使用Select distinct时在Order by中不能出现非Select结果之外的字段
select replace('T0004,T0003',',',''',''') from sys.dual;
4.系统数据库(sys)下的常用视图表
User_Tab_Cols
User_Tab_Columns
User_Tab_Comments
User_Tables
举例1,查询表名的备注
select A.TABLE_NAME, B.COMMENTS
from user_tables A, user_tab_comments B
where A.TABLE_NAME = B.TABLE_NAME and B.COMMENTS like '%商品%'
order by A.TABLE_NAME desc;
举例2:
select * from all_tab_columns where table_name like '%TESGROUP%';
select * from all_tab_columns where table_name like '%GROUP%';
select * from all_col_comments where owner = upper('YYGL') and table_name = upper('SPTABLE');
举例3:
select column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable,
data_default
from user_tab_columns
where table_name = 'T_SAMPLE'
and lower(column_name) like '%'|| (lower('FD_K'))||'%'
order by column_id;
5.模糊查询小技巧(like '%%'或like '%'可以查到全部记录)
1)Select * from 表名
where 字段名1 like NVL(trim(值1), '%') || '%'
AND 字段名2 like '%'|| NVL(trim(值2), '%') || '%'
2)Select * from 表名
where nvl(字段名1,' ') like '%' ||'值1'|| '%'
AND nvl(字段名2,' ') like '%'|| '值2' || '%'
3)Select * from 表名
where nvl(字段名1,' ') like nvl(值1, '%' )
AND nvl(字段名2,' ') like nvl(值2, '%%' )
6.一些基本函数
SELECT to_char(sysdate, 'yyyymmddhh24miss') as stat_time,substr('201009', 1, 6) as stat_month
from 表名;
7.常用SQL语句
1)Insert into Table2(field1,field2,...) select value1,value2,... from Table1
注意事项:要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。
2)SELECT 字段列表 INTO 新表的名成(比如Table2) FROM 已存在的表(比如Table1),该语句创建新表Table2并复制数据
举例,select a,c INTO Table2 from Table1
3)先创建临时表,然后删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
4)oracle分页模板
1)
SELECT * FROM
(
SELECT A.*,ROWNUM RN
FROM (SELECT * FROM TABLENAME) A
WHERE ROWNUM <=40
)
WHERE RN>=21
2)最小页码为1,则
SELECT * FROM
(
SELECT A.*,ROWNUM RN
FROM (SELECT * FROM TABLENAME) A
WHERE ROWNUM <=PageNo*PageSize
)
WHERE RN>=((PageNo-1)*PageSize+1)
5)查询重复
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1
)
-------------------------------------
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1
6)日期格式转换
to_date('2010-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
8.Varchar和NVarchar的字符集设置(查询命令)
NAME VALUE$ COMMENT$
------------------------------ --------------------------- ------------------------------------------
NLS_CHARACTERSET ZHS16GBK Character set
NLS_NCHAR_CHARACTERSET UTF8 NCHAR Character set
9.特殊字符相关
【1】在进行字符串拼接时,若想要使英文单引号被当做普通字符解析,则要使用两个单引号表示;
【2】在拼接动态SQL时,欲使列命中包含的英文括号等特殊字符被当做普通字符解析时,则应用英文双引号把列名括起来
【3】关于&和'的解释如下:
解释1: 用chr(38)表示&,用''表示'
验证1:
SET SERVEROUTPUT ON;
DECLARE
i NUMBER;
BEGIN
FOR i IN 3..24 LOOP
dbms_output.put_line(i||chr(38)||''''||i);
END LOOP;
END;
结果为:
3&'3
4&'4
5&'5
6&'6
7&'7
8&'8
验证2:
select 'ABC'||'&'||'123' from dual;
select 'ABC'||chr(38)||'123' from dual;
select ascii('&') from dual;
select chr(38) from dual;
疑问1:
INSERT INTO SYS_CD ( CAIDANBIANHAO, CAIDANMINGCHENG, CAIDANMIAOSHU, CAIDANLIANJIE,
CHENGXULIEBIAO,XITONGBAOLIU, SHIFOUQIYONG )
VALUES (40099, '单位基础信息', '查询', '/nbxt/query?actionRes=cx_nbdw_index&funcId=0',NULL, '1', '0')
想把‘&'号保存到数据库中;
回答1:
INSERT INTO SYS_CD ( CAIDANBIANHAO, CAIDANMINGCHENG, CAIDANMIAOSHU, CAIDANLIANJIE, CHENGXULIEBIAO, XITONGBAOLIU, SHIFOUQIYONG )
VALUES ( 40099, '单位基础信息', '查询', '/nbxt/query?actionRes=cx_nbdw_index'||'&'||'funcId=0', NULL , '1', '0')
---------------------------------------------------------------------------------
解释2:
在sqlplus环境中,设置set define off 可以将&加入到记录中;
【4】Oracle 过滤特殊字符
项目中遇到以下问题。页面传来一个url地址。根据这个url地址去查找记录。
关键是url地址含有特殊字符。如单引号',地址符&等。
%和_等符号可以不考虑,因为使用的是= 如果使用的是like,那么就要对%和_进行转义了。
以前转义用下列语句:like '\%ab\_' escape '\'
escape指定\为转义符号,转义%,_特殊符号。
试了试,用escape不能在 = '' 形式的语句中转移。那么如何处理呢。
v_url := replace(p_url,'''',''''''); -- 将url中包含的每个引号全部替换为2个。
&是个让人纠结的特殊字符。如果不进行转义,则在运行中会提示你输入字符。网上找了下,用如下方法进行转义:
v_url := replace(v_url,'&','''||chr(38)||'''); -- 即将&替换成chr(38);
那么一个url : http://www.baidu.org.tw/ab%27cd&ef 经过上述步骤转化为:
http://www.baidu.org.tw/ab%27%27cd&ef
http://www.baidu.org.tw/ab%27%27cd%27%27%7C%7Cchr%2838%29%7C%7C%27%27ef
即可在数据库中正常查询了。
- select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob字段名))content from 表名 where 条件
posted on 2010-11-01 00:36 LeeXiaoLiang 阅读(247) 评论(0) 编辑 收藏 举报