部分有特色的Oracle函数

Identify and Count Characters 

This tip comes from Aui de la Vega, DBA, in Makati, Philippines.

This function provides the number of times a pattern occurs in a string (VARCHAR2).

SQL> CREATE FUNCTION NUM_CHARS(INSTRING VARCHAR2, INPATTERN VARCHAR2)
RETURN NUMBER
IS
COUNTER NUMBER;
NEXT_INDEX NUMBER;
STRING VARCHAR2(2000);
PATTERN VARCHAR2(2000);
BEGIN
COUNTER := 0;
NEXT_INDEX := 1;
STRING := LOWER(INSTRING);
PATTERN := LOWER(INPATTERN);
FOR I IN 1 .. LENGTH(STRING) LOOP
IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)
AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN
COUNTER := COUNTER+1;
END IF;
NEXT_INDEX := NEXT_INDEX+1;
END LOOP;
RETURN COUNTER;
END;

Function created.



     
     

How Many Seconds 计算经过了多少秒
This tip comes from Mir Mirhashimaali, Oracle Systems Manager, Rice University in Houston, Texas. 
Here's a function to calculate the number of seconds elapsed in a given period of time. 
create or replace function how_many_seconds (
p_startdate date,
p_enddate date,
p_starttime varchar2,
p_endtime varchar2
)
return number
is
--
mv_sysdate varchar(8) := to_char(sysdate, 'rrrrmmdd');
mn_return_value number;
mn_seconds_per_day number;
mn_total_days number;
mn_seconds_in_start_day number;
mn_seconds_in_end_day number;
--
begin
--
mn_seconds_per_day := to_char(to_date(mv_sysdate || p_endtime,
'rrrrmmddhh24mi'), 'sssss') -
to_char(to_date(mv_sysdate || p_starttime, 'rrrrmmddhh24mi'),
'sssss');
mn_total_days := (trunc(p_enddate) - trunc(p_startdate)) - 1;
mn_seconds_in_start_day := to_char(to_date(mv_sysdate ||
p_endtime, 'rrrrmmddhh24mi'), 'sssss') -
to_char(p_startdate, 'sssss');
mn_seconds_in_end_day := to_char(p_enddate, 'sssss') -
to_char(to_date(mv_sysdate || p_starttime, 'rrrrmmddhh24mi'),
'sssss');
--
if mn_total_days < 0 then
mn_total_days := 0;
mn_seconds_in_start_day := 0;
end if;
--
if mn_seconds_in_start_day < 0 then
mn_seconds_in_start_day := 0;
end if;
--
if mn_seconds_in_end_day < 0 then
mn_seconds_in_end_day := 0;
end if;
--
mn_return_value := mn_seconds_in_start_day + (mn_total_days *
mn_seconds_per_day) + mn_seconds_in_end_day;
--
return mn_return_value;
--
end;

      
      

Find Numeric and Non-Numeric Records 找出数字和非数字字段
This tip comes from Ilya Petrenko, Senior Oracle DBA, Open Distributed Solutions, Inc., in Jamison, Pennsylvania. 
This script uses two Oracle functions—TRANSLATE and Length—to identify if data includes numeric or non-numeric characters. 

create table TMP_DATA
(ROW_SEQ# NUMBER,
STRING# VARCHAR2(100)
);

INSERT INTO TMP_DATA
SELECT ROWNUM,
OBJECT_NAME
||DECODE( MOD(ROWNUM,4), 0, NULL,to_char(ROWNUM))
||OBJECT_TYPE
from user_objects
where object_name like 'T%'
UNION ALL
select 100, ' 87 ' from dual
UNION ALL
select 100, '0123' from dual
UNION ALL
select 100, '911' from dual
;

col STRING# for a60
select * from TMP_DATA;
Find ALL rows where you have numeric characters only: 

select ROW_SEQ#, STRING#
from TMP_DATA
WHERE length(STRING#)
- Length( TRANSLATE(STRING#, CHR(1)||TRANSLATE(String#, CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) = 0
;


ROW_SEQ#   STRING#
---------- --------
100        0123
100        911

Find ALL rows where you have non-numeric characters only: 

select ROW_SEQ#, STRING#
from TMP_DATA
WHERE length(STRING#)
- Length( TRANSLATE(STRING#, CHR(1)||TRANSLATE(String#, CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) > 0
;


ROW_SEQ#   STRING#
---------- ------------------------------------
1          T0011TABLE
2          TEST22TABLE
3          TMP_DATA3TABLE
5          TRANS5TABLE
6          TRANS216TABLE
7          TRIV7TRIGGER
100        87


      
      

Row to Column Function   行到列的转换
This tip comes from Krishna Kondavadi, Consultant, RAPIDIGM Inc in East Hartford, Connecticut. 
The objective of this function is to transpose rows to columns. 
This RowToCol function is built with invoker-rights AUTHID CURRENT_USER. The function works on tables/views of the user who invokes the function, not on the owner of this function. 
RowToCol takes two parameters: 
1. SQL Statement: You can pass any valid SQL statement to this function.
2. Delimiter: You can pass any character as a delimiter. Default value is `,?
Examples on how to use RowToCol Function: 
Example 1: Where rows to be converted to a column string come from different table. 

     SELECT a.deptno, a.dname, a.loc, 

     rowtocol('SELECT DISTINCT job FROM emp WHERE deptno = ' ||a.deptno) as jobs

     FROM dept a;
Example 2: Where the content in the WHERE clause is characters, put it in Sting format. 
Notice, the main query and the passing query source is same table (EMP). So, use DISTINCT clause in the main query. 


     SELECT DISTINCT a.job

     ,rowtocol('SELECT ename FROM emp WHERE job = ' || '''' || a.job || '''' || ' ORDER BY ename')

     AS Employees

     FROM emp a;

Code Listing: 


     CREATE OR REPLACE 

     FUNCTION rowtocol( p_slct IN VARCHAR2,

     p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2 

     AUTHID CURRENT_USER AS

     /*

1) Column should be character type.
2) If it is non-character type, column has to be converted into character type. 
3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument.
4) If the SQL statement happened to return duplicate values, and if you don't want that to happen, put DISTINCT in the SELECT statement argument.


     TYPE c_refcur IS REF CURSOR;

     lc_str VARCHAR2(4000);

     lc_colval VARCHAR2(4000);

     c_dummy c_refcur;

     l number;


     BEGIN


     OPEN c_dummy FOR p_slct;



     LOOP

     FETCH c_dummy INTO lc_colval;

     EXIT WHEN c_dummy%NOTFOUND;

     lc_str := lc_str || p_dlmtr || lc_colval;

     END LOOP;

     CLOSE c_dummy;



     RETURN SUBSTR(lc_str,2);

     /* 

     EXCEPTION 

     WHEN OTHERS THEN



     lc_str := SQLERRM;

     IF c_dummy%ISOPEN THEN

     CLOSE c_dummy;

     END IF;

     RETURN lc_str;

     */
     END;

     /




        
        


Suppress Repeating Groups Using RANK (not ROWNUM) 
This tip comes from Mohammad Anwar, Oracle Developer, Tuskerdirect Ltd, in London, United Kingdom. 
This tip achieves the same output as the Tip for Week of August 23, 2004. In that tip, the author uses ROWNUM and inline views, whereas I use the analytic function RANK() to do the job. 
Here is the complete script: 

create table emp (
empno number(3),
dept number(3),
name varchar2(15)
);

insert into emp values(1,100,'Tracey Turner');
insert into emp values(10,100,'Peter Watson');
insert into emp values(99,200,'David Truman');
insert into emp values(80,200,'William Fender');
insert into emp values(2,300,'Sara Joshua');
insert into emp values(3,200,'Joseph Derner');
insert into emp values(60,100,'Sandra Harper');
Original Statement: 

select decode(rownum-min_sno,0,a.dept,null) dept,
decode(rownum-min_sno,0,1,rownum+1-min_sno) sno,
a.empno,name
from (select * from emp order by dept,empno ) a,
(select dept,min(rownum) min_sno
from ( select * from emp order by dept,empno )
group by dept ) b
where a.dept=b.dept
/
Modified Statement using RANK(): 

select case when rank() over (partition by dept order by empno) = 1 then dept else null end as dept,
rank() over (partition by dept order by empno) as sno,
empno,
name
from emp;
/
Output: 

DEPT       SNO        EMPNO      NAME
---------- ---------- ---------- ---------------
100        1          1          Tracey Turner
           2          10         Peter Watson
           3          60         Sandra Harper
200        1          3          Joseph Derner
           2          80         William Fender
           3          99         David Truman
300        1          2          Sara Joshua

        
        
Comma, Pipe, or Tab Delimited Output 
This tip comes from Galina Petrenko, Sr. IT Specialist, TPAS Systems Development in Voorhees, New Jersey. 
To generate comma or pipe delimited output for tools such as Excel, Access, and so on while spooling from SQL*Plus, use settings from SQL*Plus such as: 

SET COLSEP ,
or 

SET COLSEP |
Example: 

set pages 0 feed off
set colsep ,
select
object_name,
object_type,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;
Output: 

ALL_ALL_TABLES ,SYNONYM ,VALID ,01-APR-2004
ALL_ARGUMENTS ,SYNONYM ,VALID ,01-APR-2004
ALL_ASSOCIATIONS ,SYNONYM ,VALID ,01-APR-2004
ALL_CATALOG ,SYNONYM ,VALID ,01-APR-2004
ALL_CLUSTERS ,SYNONYM ,VALID ,01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS ,SYNONYM ,VALID ,01-APR-2004
ALL_COLL_TYPES ,SYNONYM ,VALID ,01-APR-2004
ALL_COL_COMMENTS ,SYNONYM ,VALID ,01-APR-2004
ALL_COL_PRIVS ,SYNONYM ,VALID ,01-APR-2004
For pipe delimited output: 

set colsep |

select
object_name,
object_type,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;
Output: 

ALL_ALL_TABLES |SYNONYM |VALID |01-APR-2004
ALL_ARGUMENTS |SYNONYM |VALID |01-APR-2004
ALL_ASSOCIATIONS |SYNONYM |VALID |01-APR-2004
ALL_CATALOG |SYNONYM |VALID |01-APR-2004
ALL_CLUSTERS |SYNONYM |VALID |01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS |SYNONYM |VALID |01-APR-2004
ALL_COLL_TYPES |SYNONYM |VALID |01-APR-2004
ALL_COL_COMMENTS |SYNONYM |VALID |01-APR-2004
ALL_COL_PRIVS |SYNONYM |VALID |01-APR-2004
For TAB delimited output, you can use the following: 

col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep "&TAB"


select
object_name,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;
Output: 

ALL_ALL_TABLES VALID 01-APR-2004
ALL_ARGUMENTS VALID 01-APR-2004
ALL_ASSOCIATIONS VALID 01-APR-2004
ALL_CATALOG VALID 01-APR-2004
ALL_CLUSTERS VALID 01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS VALID 01-APR-2004
ALL_COLL_TYPES VALID 01-APR-2004
ALL_COL_COMMENTS VALID 01-APR-2004
ALL_COL_PRIVS VALID 01-APR-2004