Oracle常用内置Or自定义函数-SQL宝典

一 、函数使用说明　
  1.NLSSORT(列名,排序规则)
    在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值    
  　　SCHINESE_RADICAL_M   按照部首（第一顺序）、笔划（第二顺序）排序    
  　　SCHINESE_STROKE_M   按照笔划（第一顺序）、部首（第二顺序）排序    
  　　SCHINESE_PINYIN_M   按照拼音排序
    oracle9i中新增了按照拼音、部首、笔画排序功能
    拼音 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_PINYIN_M')
    笔划 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_STROKE_M')
    部首 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_RADICAL_M')

    Oracle9i之前，中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。
    1、设置NLS_SORT参数值
          SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
          SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
          SCHINESE_PINYIN_M 按照拼音排序
    2、Session级别的设置，修改ORACLE字段的默认排序方式：
          按拼音：alter session set nls_sort = SCHINESE_PINYIN_M;
          按笔画：alter session set nls_sort = SCHINESE_STROKE_M;
          按偏旁：alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M；
    3、语句级别设置排序方式：
          按照笔划排序
          select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
          按照部首排序
          select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
          按照拼音排序，此为系统的默认排序方式
          select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
    4、修改系统参数（数据库所在操作系统）：
           set NLS_SORT=SCHINESE_RADICAL_M ;
        export NLS_SORT （sh）

        setenv NLS_SORT SCHINESE_RADICAL_M (csh)

        HKLC\SOFTWARE\ORACLE\home0\NLS_SORT (win注册表)

 2. 提取分组后每组的前N条数据语句  
      

select * from (  
           select e.id,e.COTERIENAME , e.ISSORT, row_number() over (partition by e.ISSORT order by e.ISSORT asc)  rn     from   t_d_coterie e
              ) where rn <= N ;

3.排序后提取前N条记录（Oracle中不支持top关键字）
      select  id,objname,adddate,rownum from (select *  from t_d_appobj order by adddate asc) where  rownum <2;

4、oracle执行md5函数

--创建MD5函数(此方法得到的MD5字符串为大写，如果数据库内的MD5值为小写，请使用lower函数转换为小写)
CREATE OR REPLACE FUNCTION GET_MD5
( p_str in varchar2)
RETURN varchar2 IS
BEGIN
RETURN Utl_Raw.Cast_To_Raw(DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => P_Str));
END;

--执行查询

select  ur.username  from t_adminuser ur where ur.password=lower(GET_MD5('admin123'));

5、查找当前登录用户下的所有表名

select ta.TABLE_NAME as 表名 from user_tables  ta 

6、oracle排查异常sql

select count(*) from v$session;--查看数据库连接数

select count(*) from v$session where status='ACTIVE';--并发连接数

select value from v$parameter where name = 'processes';--数据库允许的最大连接数

select username,count(username) from v$session where username is not null group by username;--查看不同用户的连接数

--查看被锁的表
SELECT   s.username,
         decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
         o.owner,
         o.object_name,
         o.object_type,
         s.sid,
         s.serial#
    FROM gv$session s, gv$lock l, dba_objects o
   WHERE l.sid = s.sid
     AND l.id1 = o.object_id(+)
     AND s.username is NOT NULL
    AND O.OWNER='creator';

--查看到被死锁的语句
select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

7、查找某个表的所有的字段名称、字段类型、字段解释

SELECT t.colUMN_NAME as 字段名称,
       t.DATA_TYPE || '(' || t.DATA_LENGTH || ')' as 字段类型,
       t1.COMMENTS as 字段简介,
       t.table_name as  表名称
  FROM User_Tab_Cols t, User_Col_Comments t1
 WHERE t.table_name = t1.table_name
   AND t.column_name = t1.column_name
   and t.TABLE_NAME = 'T_D_NETWORKADAPTER';

8、修改oracle用户密码永不过期

    1、查看用户的proifle是哪个，一般是default：

    sql>SELECT username,PROFILE FROM dba_users;

    2、查看指定概要文件（如default）的密码有效期设置：

    sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

    3、将密码有效期由默认的180天修改成“无限制”：

    sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

    修改之后不需要重启动数据库，会立即生效。

    4、修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；

    已经被提示的帐户必须再改一次密码，举例如下：中华考试网

    $sqlplus / as sysdba

    sql> alter user smsc identified by <原来的密码> ----不用换新密码