李晓亮的博客

导航

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的字符集设置(查询命令)

select name, value$, comment$ from sys.props$ where name in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
 
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

即可在数据库中正常查询了。

10.sql查询blob转字符串

Sql代码  
  1. select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob字段名))content from 表名 where 条件   
  2.    

 

 

posted on 2010-11-01 00:36  LeeXiaoLiang  阅读(247)  评论(0编辑  收藏  举报