ORACLE编程详解

作者: 慢清尘 | 来源:发表于2017-03-24 18:37 被阅读89次

0 . 查看系统表空间

select * from dba_tablespaces

1 . 查看表结构

desc table_name

2 . 查询数据库SID

select name from v$database;

3 . 用 B 表的数据更新 A 表数据 ( 有关联的字段 )

UPDATE A SET name=(SELECT name FROM B WHERE
A.id=B.id) WHERE B.id IS NOT NULL ；

4 . 随机数函数 `DBMS_RANDOM.RANDOM`

SQL> select dbms_random.random from dual

5 . 如何在字符串里加回车？

SQL> select 'line1'||chr(10)||'line2' from dual;

6 . 如何使 select 语句使查询结果自动生成序号 ?

SQL> select rownum, fieldname from table;

7 . 怎么可以快速做一个和原表一样的备份表 ?

create table new_table as (select * from old_table) ；

8 . 如何单独备份一个或多个表？

exp 用户 / 密码 tables=( 表 1 ， … ，表 2)

9. 如何单独备份一个或多个用户？

exp system/manager owner=( 用户 1 ，用户 2 ， … ，用户 n) file= 导出文件

10 . 如何执行脚本 SQL 文件 ?

SQL>@$PATH/filename.sql ;

11. 如何快速清空一个大表 ?

SQL>truncate table table_name ;

12 . 字符串的连接

select concat(col1 ， col2) from table ;
select col1||col2 from table ;

13 . 怎么把 select 出来的结果导到一个文本文件中？

SQL> spool c:\test.txt;
SQL> select * from emp;
SQL> spool off ;

14. 如何测试 SQL 语句执行所用的时间 ?

SQL>set timing on ;
SQL>select * from tablename ;

15 . 改变字段大小 ?

-- 改大行，改小不行（除非都是空的）
SQL>alter table table_name modify (field_name varchar2(100)) ;

16 . 如何查询某天的数据 ?

-- datefield为要查询的日期字段
SQL>select * from table_name where trunc( datefield ) ＝ to_date('2003-05-02' ，'yyyy-mm-dd') ;

17. 如何修改表名 ?

SQL> alter table old_table_name rename to new_table_name;

18 . 如何搜索出前 N 条记录？

SQL> SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;

-- 搜索一个范围的数据 
SQL> SELECT * FROM (SELECT ROWNUM RN,EMPNO,ENAME FROM EMP) WHERE RN > 5 AND RN < 10 ORDER BY empno;

19 . 怎样用 Sql 语句实现查找一列中第 N 大值？

SQL> select * from (select t.* , dense_rank() over (order by sal) rank from employee) where rank = N ；

20 . 如何在给现有的日期加上 2 年？

SQL> select add_months(sysdate ， 24) from dual ；

21 . 返回大于等于 N 的最小整数值 ?

SQL> SELECT CEIL(N) FROM DUAL ；

22 . 返回小于等于 N 的最小整数值 ?

SQL> SELECT FLOOR(N) FROM DUAL ；

23 . 返回当前月的最后一天 ?

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL ；

24 . 如何不同用户间数据导入 ?

IMP SYSTEM/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y INDEXES=Y ；

25 . 如何找数据库表的主键字段的名称 ?

SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME' ；

26 . 两个结果集互加的函数 ?

SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW ；
SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;
SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW ；

27 . 两个结果集互减的函数 ?

SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW ；

28. 如何配置 Sequence?

创建sequence

create sequence seq_cust start 1 incrememt by

建表

create table cust { 
    cust_id smallint not null,
    ...
}

insert 数据

insert into table cust values( seq_cust.nextval, ... )
insert into table cust values( seq_cust.currval, ... )

29 . 日期各部分的写法

-- 取时间点的年份的写法：
SELECT TO_CHAR(SYSDATE ， 'YYYY') FROM DUAL ；
-- 取时间点的月份的写法：
SELECT TO_CHAR(SYSDATE ， 'MM') FROM DUAL ；
-- 取时间点的日的写法：
SELECT TO_CHAR(SYSDATE ， 'DD') FROM DUAL ；
-- 取时间点的时的写法：
SELECT TO_CHAR(SYSDATE ， 'HH24') FROM DUAL ；
-- 取时间点的分的写法：
SELECT TO_CHAR(SYSDATE ， 'MI') FROM DUAL ；
-- 取时间点的秒的写法：
SELECT TO_CHAR(SYSDATE ， 'SS') FROM DUAL ；
-- 取时间点的日期的写法：
SELECT TRUNC(SYSDATE) FROM DUAL ；
-- 取时间点的时间的写法：
SELECT TO_CHAR(SYSDATE ， 'HH24 ： MI ： SS') FROM DUAL ；
-- 日期，时间形态变为字符形态：
SELECT TO_CHAR(SYSDATE) FROM DUAL ；
-- 将字符串转换成日期或时间形态：
SELECT TO_DATE('2003/08/01') FROM DUAL ；
-- 返回参数的星期几的写法：
SELECT TO_CHAR(SYSDATE ， 'D') FROM DUAL ；
-- 返回参数一年中的第几天的写法：
SELECT TO_CHAR(SYSDATE ， 'DDD') FROM DUAL ；
-- 返回午夜和参数中指定的时间值之间的秒数的写法：
SELECT TO_CHAR(SYSDATE ， 'SSSSS') FROM DUAL ；
-- 返回参数中一年的第几周的写法：
SELECT TO_CHAR(SYSDATE ， 'WW') FROM DUAL ；

30 . ROWNUM

-- 按设定排序的行的序号
SELECT * FROM emp WHERE ROWNUM < 10 ；

31. 如何查找重复记录？

SELECT * FROM TABLE_NAME WHERE ROWID!=( 
    SELECT MAX(ROWID) 
        FROM TABLE_NAME D 
        WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2
    );

32 . 如何删除重复记录？

DELETE FROM TABLE_NAME WHERE ROWID!=( 
    SELECT MAX(ROWID) 
        FROM TABLE_NAME D 
        WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2
    );

33 . 赋予权限

GRANT
  CONNECT,                
  RESOURCE,               
  --DBA,                  
  --unlimited tablespace,
  CREATE  SESSION,         
  CREATE ANY SEQUENCE,     
  CREATE ANY TABLE,        
  CREATE ANY VIEW ,        
  CREATE ANY INDEX,        
  CREATE ANY PROCEDURE,    
  CREATE ANY DIRECTORY,    
  ALTER  SESSION, 
  ALTER ANY SEQUENCE,     
  ALTER ANY TABLE,        
  --ALTER ANY VIEW ,        --不能修改视图
  ALTER ANY INDEX,        
  ALTER ANY PROCEDURE,    
  --ALTER ANY DIRECTORY,    --不能修改目录
  --DROP  SESSION,       --不能删除Session
  DROP ANY SEQUENCE,     
  DROP ANY TABLE,        
  DROP ANY VIEW ,        
  DROP ANY INDEX,        
  DROP ANY PROCEDURE,    
  DROP ANY DIRECTORY,    
  SELECT ANY TABLE, 
  SELECT ANY DICTIONARY,
  INSERT ANY TABLE, 
  UPDATE ANY TABLE, 
  DELETE ANY TABLE,
  DEBUG ANY PROCEDURE,
  DEBUG CONNECT SESSION,
  exp_full_database,  
  imp_full_database     
TO user;

34 . 导出视图数据

  create table v_table as( select * from view_table);

35 . 解决Temp01.dbf不断变大的问题

第一步：
alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' drop;

第二步：
alter tablespace temp add tempfile
'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF'
size 2048M reuse autoextend on next 100M;

第三步：
select d.file_name, d.file_id, d.tablespace_name, d.bytes 
from dba_temp_files d;

第四步：
alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' autoextend off;

36 . 行转列的多记录合并

id	name
1	a
1	b
1	c
2	aa
2	bb

1 . 使用 WM_CONCAT 函数

SQL> select wmsys.wm_concat(distinct name) from t; 
SQL> select id,wmsys.wm_concat(distinct name) con_name from t group by id;  

ID   CON_NAME  
--------------  
1     a,b,c  
2     aa,bb

2 . 使用 STRAGG

SQL> SELECT TRIM(',' FROM SYS.STRAGG(NAME||NVL2(NAME,',',''))) CON_NAME FROM T;  
  
CON_NAME  
----------  
a,b,c,aa,bb

3 . 使用 partition by

select id,sys_connect_by_path(name,'>')  con_name  
    from ( select id,name,row_number() over( partition by id order by name) rn  from (select id,name from t )) t  
start 
    with t.rn=1  
connect by 
    t.id=prior t.id and t.rn-1=prior t.rn  

    ID CON_NAME  
------- --------------------  
     1 >a  
     1 >a>b  
     1 >a>b>c  
     2 >aa  
     2 >aa>bb  
     2 >aa>bb>china  
     2 >aa>bb>china>china  

select id,substr(max(sys_connect_by_path(name,'>')),2)  con_name  
  from (select id,name,row_number() over(partition by id order by name) rn  from (select id,name from t ))t  
  start with t.rn=1  
  connect by t.id=prior t.id and t.rn-1=prior t.rn  
  group by id  

   ID CON_NAME  
------- --------------------  
    1 a>b>c  
    2 aa>bb>china>china

4 . 使用 XMLAGG 函数

RTRIM(XMLAGG(XMLPARSE(CONTENT field || ',' WELLFORMED)) .GETCLOBVAL(), ',')

5 . 自定义类型 VARCHAR2

-- create function of strcat 
CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type; 

-- create type define of strcat_type
create or replace type strcat_type as object 
( 
    currentstr varchar2(4000), 
    currentseprator varchar2(8), 
    static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number, 
    member function ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number, 
    member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number, 
    member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number 
) 

-- create type body of strcat_type
create or replace type body strcat_type is 
      static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is 
      begin 
        sctx := strcat_type('',','); 
        return ODCIConst.Success; 
      end; 
      member function ODCIAggregateIterate(self IN OUT strcat_type, value IN VARCHAR2) return number is 
      begin 
        if self.currentstr is null then 
           self.currentstr := value; 
        else 
          self.currentstr := self.currentstr ||currentseprator || value; 
        end if; 
        return ODCIConst.Success; 
      end; 
      member function ODCIAggregateTerminate(self IN strcat_type, returnValue OUT VARCHAR2, flags IN number) return number is 
      begin 
        returnValue := self.currentstr; 
        return ODCIConst.Success; 
      end; 
      member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN strcat_type) return number is 
      begin 
        if ctx2.currentstr is null then 
          self.currentstr := self.currentstr; 
        elsif self.currentstr is null then 
          self.currentstr := ctx2.currentstr; 
        else 
          self.currentstr := self.currentstr || currentseprator || ctx2.currentstr; 
        end if; 
        return ODCIConst.Success; 
      end; 
      end;

6 . 自定义类型 clob

-- create function of strcat 
CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN clob PARALLEL_ENABLE AGGREGATE USING strcat_type;

-- create type define of strcat_type
create or replace type strcat_type as object
(
  CURR_STR clob,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT strcat_type) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT strcat_type, P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN strcat_type, RETURNVALUE OUT clob, FLAGS IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT strcat_type, SCTX2 IN strcat_type) RETURN NUMBER
)

-- create type body of strcat_type
create or replace type body strcat_type is
  static function ODCIAggregateInitialize(sctx IN OUT strcat_type)
    return number is
  BEGIN
    SCTX := strcat_type(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT strcat_type,P1 IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN strcat_type,  RETURNVALUE OUT clob,  FLAGS IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT strcat_type, SCTX2 IN strcat_type) RETURN NUMBER IS
  BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;

37 . 去掉重复字符串

  /****************************************************
  ** Oracle去掉重复字符串
  ** 函数名称：RemoveSameStr
  ** 参    数：【名称】         【类型 】      【说明】
  **                 oldStr           varchar2       要处理的字符串
  **                 sign               varchar2       字符串分隔符
  ** 返 回 值： result          varchar2        不包含重复子串的记录
  ****************************************************/

create or replace function rm_same_str(oldStr clob, sign varchar2)
  return varchar2 is
    str          clob;
    currentIndex number;
    startIndex   number;
    endIndex     number;
    type str_type is table of varchar2(1000) index by binary_integer;
    arr str_type;
    Result varchar2(4000);
begin
    -- null string
    if oldStr is null then
    return('');
    end if;

    --string is too long
    if length(oldStr) > 32000 then
    return(oldStr);
    end if;
    str := oldStr;

    currentIndex := 0;
    startIndex   := 0;

    loop
    currentIndex := currentIndex + 1;
    endIndex     := instr(str, sign, 1, currentIndex);
    if (endIndex <= 0) then
      exit;
    end if;

    arr(currentIndex) :=substr(str,startIndex + 1,  endIndex - startIndex - 1);

    -- arr(currentIndex) := trim(substr(str,startIndex + 1,endIndex - startIndex - 1));
    startIndex := endIndex;
    end loop;

    --get the last string
    arr(currentIndex) := substr(str, startIndex + 1, length(str));

    --remove the same string
    for i in 1 .. currentIndex - 1 loop
    for j in i + 1 .. currentIndex loop
      if arr(i) = arr(j) then
        arr(j) := '';
      end if;
    end loop;
    end loop;

    str := '';
    for i in 1 .. currentIndex loop
    if arr(i) is not null then
      str := str || sign || arr(i);
      --set arrar to null:
      arr(i) := '';
    end if;
    end loop;

    --remove the prev mark
    Result := substr(str, 2, length(str));

    return(Result);
end rm_same_str;

38 . 主要函数

INITCAP(CHAR)：将字符串 CHAR 的第一个字符为大写,其余为小写。

SELECT INITCAP('ABCDE') FROM DUAL ;

LENGTH(CHAR) : 取一字符串 CHAR 的长度。

SELECT LENGTH('ABCDE') FROM DUAL ;

LOWER(CHAR) ：将字符串 CHAR 全部变为小写。

SELECT LOWER('ABCDE') FROM DUAL ;

LPAD(CHAR1 , N , CHAR2) ：用字符串 CHAR2 包括的字符左填 CHAR1 ,使其长度为 N 。

SELECT LPAD('ABCDEFG' , 10'123') FROM DUAL ;
-- 结果: '123ABCDEFG'

LTRIM(CHAR , SET) ：从字符串 CHAR 的左边移去字符串 SET 中的字符,直到第一个不是 SET 中的字符为

SELECT ('CDEFG' , 'CD') FROM DUAL ;
-- 结果: 'EFG'

NLS_INITCAP(CHAR)：取字符 CHAR 的第一个字符大写,其余字符为小写。

SELECT NLS_INITCAP('ABCDE') FROM DUAL ;

NLS_LOWER(CHAR)：将字符串 CHAR 包括的字符全部小写。

SELECT NLS_LOWER('AAAA') FROM DUAL ;

NLS_UPPER(CHAR)：将字符串 CHAR 包括的字符全部大写。

SELECT NLS_UPPER('AAAA') FROM DUAL ;

REPLACE(CHAR1 , CHAR2 , CHAR3)：用字符串 CHAR3 代替每一个列值为 CHAR2 的列,其结果放在 CHAR1 中。

SELECT REPLACE(EMP_NO , '123' , '456') FROM DUAL ;

RPAD(CHAR1 , N , CHAR2)：用字符串 CHAR2 右填字符串 CHAR1 ,使其长度为 N 。

SELECT RPAD('234' , 8 , '0') FROM DUAL ;

RTRIM(CHAR , SET)：移去字符串 CHAR 右边的字符串 SET 中的字符,直到最后一个不是 SET 中的字符为止。

SELECT RTRIM('ABCDE' , 'DE') FROM DUAL ;

SUBSTR(CHAR , M , N)：得到字符串 CHAR 从 M 处开始的 N 个字符 . 双字节字符,一个汉字为一个字符的。

SELECT SUBSTR('ABCDE' , 2 , 3) FROM DUAL ;

SUBSTRB(CHAR , M , N)：得到字符串 CHAR 从 M 处开始的 N 个字符 . 双字节字符,一个汉字为二个字符的。

SELECT SUBSTRB('ABCDE' , 2 , 3) FROM DUAL ;

TRANSLATE(CHAR1 , CHAR2 , CHAR3)：将 CHAR1 中的 CHAR2 的部分用 CHAR3 代替。

SELECT TRANSLATE('ABCDEFGH' , 'DE' , 'MN') FROM DUAL ;

UPPER(CHAR)：将字符串 CHAR 全部为大写。
ADD_MONTHS(D , N)：将 N 个月增加到 D 日期。

SELECT ADD_MONTHS(SYSDATE , 5) FROM DUAL ;

LAST_DAY(D)：得到包含 D 日期的月份的最后的一天的日期。

SELECT LAST_DAY(SYSDATE) FROM DUAL ;

MONTH_BETWEEN(D1 , D2)：得到两个日期之间的月数。

SELECT MONTH_BETWEEN(D1 , D2) FROM DUAL ;

NEXT_DAY(D , CHAR)：得到比日期 D 晚的由 CHAR 命名的第一个周日的日期。

SELECT NEXT_DAY(TO_DATE('2003/09/20') , 'SATDAY') FROM DUAL ;

ROUNT(D , FMT)：得到按指定的模式 FMT 舍入到的最进的日期。

SELECT ROUNT('2003/09/20' , MONTH) FROM DUAL ;

SYSDATE：得到当前系统的日期和时间。

SELECT SYSDATE FROM DUAL ;

TO_CHAR(D , FMT)：将日期 D 转换为 FMT 的字符串。

SELECT TO_CHAR(SYSDATE , 'YYYY/MM/DD') FROM DUAL ;

TO_DATE(CHAR , FMT)：将字符串 CHAR 按 FMT 的格式转换为日期。

SELECT TO_DATE('2003/09/20' , 'YYYY/MM/DD') FROM DUAL ;

ABS(N)：得到 N 的绝对值。

SELECT ABS(-6) FROM DUAL ;

EXP(N)：得到 N 的 E 的 N 次幂。

SELECT EXP(1) FROM DUAL ;

MOD(M , N)：得到 M 除以 N 的余数。

SELECT MOD(100 , 7) FROM DUAL ;

POWER(M , N)：得到 M 的 N 幂。

SELECT POWER(4 , 3) FROM DUAL ;

ROUND(N , M)：将 N 舍入到小数点后 M 位。

SELECT (78.87653 , 2) FROM DUAL ;

SIGN(N)：当 N<0 时,得到 -1 ;当 N>0 时,得到 1 ;当 N=0 时,得到 0 ;

SELECT SIGN(99) FROM DUAL ;

TRUNC(N , M)：得到在 M 位截断的 N 的值。

SELECT TRUNC(7.7788 , 2) FROM DUAL ;

COUNT()：计算满足条件的记录数。

SELECT COUNT(*) FROM TABLE1 WHERE COL1='AAA' ;

MAX()：对指定的列求最大值。

SELECT MAX(COL1) FROM TABLE1 ;

MIN()：对指定的列求最小值。

SELECT MIN(COL1) FROM TABLE1 ;

AVG()：对指定的列求平均值。

SELECT AVG(COL1) FROM TABLE1 ;

SUM()：计算列的和。

SELECT SUM(COL1) FROM DUAL ;

TO_NUMBER(CHAR)：将字符转换为数值。

SELECT TO_NUMBER('999') FROM DUAL ;

empty_b|clob()：返回一个空的 LOB 定位符 , 用在初始化 LOB 变量 , 或用在 INSERT 及 UPDATE 声明去初始化
LOB 列或将其属性置为空。

INSERT INTO TABLE1 VALUES(EMPTY_BLOB()) ;
UPDATE TABLE1 SET CLOB_COL=EMPTY_BLOB() ;

NVL(EXPR1 , EXPR2)：若 EXPR1 是 NULL ,则返回 EXPR2 ,否则返回 EXPR1 。

SELECT NAME , NVL(TO_CHAR(COMM) , 'NOT APPLICATION') FROM TABLE1 ;

39. CASE WHEN 和 DECODE

1 . case表达式

--简单Case函数  
CASE sex  
    WHEN '1' THEN '男'  
    WHEN '2' THEN '女'  
    ELSE '其他' 
END  

--Case搜索函数  
CASE
    WHEN sex = '1' THEN '男'  
    WHEN sex = '2' THEN '女'  
    ELSE '其他' 
END

2 . CASE WHEN 在语句中不同位置的用法

2.1 SELECT 用法

SELECT 
    grade,
    COUNT ( CASE WHEN sex = 1 THEN 1 ELSE NULL END) 男生数, 
    COUNT ( CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生数
FROM 
    students
GROUP BY  
    grade

2.2 WHERE 用法

SELECT 
    T2.*, T1.*
FROM 
    T1, T2
WHERE ( 
    CASE 
        WHEN T2.COMPARE_TYPE  = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%' THEN 1
        WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1
        ELSE 0
    END ) = 1

2.3 GROUP BY 用法

SELECT  
    CASE 
        WHEN salary <= 500 THEN '1'  WHEN salary > 500 AND salary <= 600  THEN '2'  
        WHEN salary > 600 AND salary <= 800  THEN '3'  
        WHEN salary > 800 AND salary <= 1000 THEN '4'  
        ELSE NULL 
    END salary_class, -- 别名命名
    COUNT(*)  
FROM    
    A  
GROUP BY  
    CASE 
        WHEN salary <= 500 THEN '1'  
        WHEN salary > 500 AND salary <= 600  THEN '2'  
        WHEN salary > 600 AND salary <= 800  THEN '3'  
        WHEN salary > 800 AND salary <= 1000 THEN '4'  
        ELSE NULL 
    END;

3 . DECODE() 函数

基本语法： decode(条件, 值1, 返回值1, 值2, 返回值2, ..., 值n, 返回值n, 缺省值)

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees;

40. 修改clob字段为varchar2

alter table tablename add (new_column varchar2(4000));
update tablename set new_column=dbms_lob.substr(old_column,1000,1);
alter table tablename drop column old_column;
alter table tablename rename column new_column to old_column;

41. 使用正则表达式查询

--非正整数 
select 字段 from 表 where regexp_replace(字段,'\d','') is not null;

--非数值类型
select 字段 from 表 where regexp_replace(字段,'^[-\+]?\d+(\.\d+)?$','') is not null;

--自定义函数,判断非值类型
create or replace function isnumber(col varchar2) return   i number;
begin
  i := to_number(col);
  return 1;
exception
  when others then
    return 0;
end;

select 字段 from 表 where isnumber(字段)=0;

42. 判断某個字段的值是不是数字

共有三种方法，分别是使用to_number()、regexp_like()和translate()三种函数来进行匹配。如果字段的值是数字，返回1，否则返回0，具体的实现如下，三种方法任选壹种即可。运行结果：如果字符串是数字格式则返回1，不是则返回0。

--1、利用 to_number
CREATE OR REPLACE FUNCTION isnumeric(str IN VARCHAR2)
    RETURN NUMBER
IS
    v_str FLOAT;
BEGIN
    IF str IS NULL
    THEN
       RETURN 0;
    ELSE
       BEGIN
          SELECT TO_NUMBER (str)
            INTO v_str
            FROM DUAL;
       EXCEPTION
          WHEN INVALID_NUMBER
          THEN
             RETURN 0;
       END;
       RETURN 1;
    END IF;
END isnumeric;
/

--2、利用 regexp_like
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
    RETURN NUMBER
IS
BEGIN
    IF str IS NULL
    THEN
       RETURN 0;
    ELSE
       IF regexp_like (str, '^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$')
       THEN
          RETURN 1;
       ELSE
          RETURN 0;
       END IF;
    END IF;
END isnumeric;
/

--3、利用 translate
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
    RETURN NUMBER
IS
    v_str VARCHAR2 (1000);
BEGIN
    IF str IS NULL
    THEN
       RETURN 0;
    ELSE
       v_str := translate(str, '.0123456789', '.');

       IF v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL
       THEN
          RETURN 1;
       ELSE
          RETURN 0;
       END IF;
    END IF;
END isnumeric;

由于 Oracle 数据库本身没有提供检测字段是否为数字的方法，因此我们需要自己创建壹個 isnumeric() 的函数，然后再调用它来进行判断。具体的调用方式如下：

SQL> select isnumeric('123a') from dual;
ISNUMERIC('123A')
-----------------
                0

SQL> select isnumeric('123.509') from dual;
ISNUMERIC('123.509')
--------------------
                1

SQL> select isnumeric('123.205.10.8') from dual;
ISNUMERIC('123.205.10.8')
-------------------------
                0

43. 重复记录查询

1、查找表中多余的重复记录，重复记录是根据单个字段 xxxId 来判断

select * from 
  xxx_table
where 
  xxxId in (select xxxId from  xxx_table group by xxxId having count(xxxId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段 xxxId 来判断，只留有rowid最小的记录

delete from 
  xxx_table 
where 
  xxxId in (select xxxId from xxx_table group by xxxId having count(xxxId) > 1) and 
  rowid not in (select min(rowid) from xxx_table group by xxxId having count(xxxId)>1)

3、查找表中多余的重复记录（多个字段）

select * from 
  xxx_table a
where 
  (a.xxxIdA,a.xxxIdB) in   (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from xxx_table a
where (a.xxxIdA,a.xxxIdB) in   (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1)
and rowid not in (select min(rowid) from xxx_table group by xxxIdA,xxxIdB having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from 
  xxx_table a
where 
  (a.xxxIdA,a.xxxIdB) in (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1) and
  rowid not in (select min(rowid) from xxx_table group by xxxIdA,xxxIdB having count(*)>1)