ORACLE SYBASE函数整理

最新推荐文章于 2021-10-10 18:56:04 发布

冰冻三日非一日之寒

最新推荐文章于 2021-10-10 18:56:04 发布

阅读量1.3k

点赞数

分类专栏：ＳＱＬ函数

本文链接：https://blog.csdn.net/u010891316/article/details/40983931

版权

本文详细介绍了Oracle和Sybase数据库中的一些关键函数和操作，包括建表、行转列、字符串处理、日期处理、解锁、单位转换、NULL处理以及游标和存储过程的使用。例如，利用row_number()函数获取每个部门最低薪资的员工，使用decode和listagg进行数据转换，以及在Sybase中应用TOP和ISNULL函数等。

摘要由CSDN通过智能技术生成

ORACLE

建表

（1）建一个新表，架构、字段属性、约束条件、数据记录跟旧表完全一样：

Create Table print_his_0013 as Select * from print_his_0007

（2）建一个新表，架构跟旧表完全一样，但没有内容：

Create Table print_his_0013 as Select * from print_his_0007 where 1=2

（3）建一个新表，只选部分字段：

CREATE TABLE yonghu_bak AS SELECT id, name,sex FROM yonghu;

函数row_number()

当遇到一个部门有多个员工记录，需要去取出每个部门薪水最少的那笔时，就可以用到分析函数row_number()

select * from(

select manager_id,employee_id,first_name,salary,row_number()

over(partition by manager_id order by salary) as currowid

from hr.employees)

where currowid = 1

行转列

如

student subject grade

---------------------------

student1 语文 80

student1 数学 70

student1 英语 60

student2 语文 90

student2 数学 80

student2 英语 100

……

转换为

语文数学英语

student1 80 70 60

student2 90 80 100

……

语句1：

select student,sum(decode(subject,'语文', grade,null)) "语文",

sum(decode(subject,'数学', grade,null)) "数学",

sum(decode(subject,'英语', grade,null)) "英语"

from table

group by student

----

语句2

SELECT DEVICE ,

HOUR9,HOUR10,HOUR11,HOUR20,HOUR21,HOUR22,

(HOUR9+HOUR10+HOUR11+HOUR20+HOUR21+HOUR22)/6 BUSY6Avg

FROM

(

SELECT DEVICE,

SUM(DECODE(TIME_STAMP, TO_CHAR (SYSDATE-1,'YYYY-MM-DD')||' 09:00:00',SGSN_RADIUS,0)) AS HOUR9,

SUM(DECODE(TIME_STAMP, TO_CHAR (SYSDATE-1,'YYYY-MM-DD')||' 10:00:00',SGSN_RADIUS,0)) AS HOUR10,

SUM(DECODE(TIME_STAMP, TO_CHAR (SYSDATE-1,'YYYY-MM-DD')||' 11:00:00',SGSN_RADIUS,0)) AS HOUR11,

SUM(DECODE(TIME_STAMP, TO_CHAR (SYSDATE-1,'YYYY-MM-DD')||' 20:00:00',SGSN_RADIUS,0)) AS HOUR20,

SUM(DECODE(TIME_STAMP, TO_CHAR (SYSDATE-1,'YYYY-MM-DD')||' 21:00:00',SGSN_RADIUS,0)) AS HOUR21,

SUM(DECODE(TIME_STAMP, TO_CHAR (SYSDATE-1,'YYYY-MM-DD')||' 22:00:00',SGSN_RADIUS,0)) AS HOUR22

FROM

(

SELECT TO_CHAR(REC_TIME, 'yyyy-mm-dd HH24:MI:SS') TIME_STAMP,

DEVICE,

ROUND((SUM(rau_betweensgsn_successtimes) +

SUM(rau_withinsgsn_successtimes)) /

(SUM(rau_betweensgsn_requesttimes) +

SUM(rau_withinsgsn_requesttimes)) * 100,

2) SGSN_RADIUS

FROM GPRS_SGSN_6BUSY

WHERE REC_TIME >=

TO_DATE('2013-11-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND REC_TIME < =

TO_DATE('2013-11-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY REC_TIME, DEVICE

ORDER BY REC_TIME

)

GROUP BY DEVICE

)

解锁

--查出oracle当前的被锁对象

SELECT l.session_id sid,

s.serial#,

l.locked_mode 锁模式,

l.oracle_username 登录用户,

l.os_user_name 登录机器用户名,

s.machine 机器名,

s.terminal 终端用户名,

o.object_name 被锁对象名,

s.logon_time 登录数据库时间

FROM v$locked_object l, all_objects o, v$session s

WHERE l.object_id = o.object_id

AND l.session_id = s.sid

ORDER BY sid, s.serial#;

--kill掉当前的锁对象可以为

alter system kill session 'sid,s.serial#';

SELECT * FROM V$DB_OBJECT_CACHE WHERE owner='FM' AND NAME LIKE '%FM_RECORD_BAK%' AND LOCKS!='0'

创建表空间

create tablespace user_data

logging

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

行列转换

oracle 10g wmsys.wm_concat行列转换函数的使用

wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行

1、把结果按分组用逗号分割，以一行打印出来。（如果需要换其它的可以用replace函数：replace(wm_concat(name),',','|')）

select t.u_id,

wmsys.wm_concat(t.goods),

wmsys.wm_concat(t.goods || '(' || t.u_id || '斤)')

from tb_index t

group by t.u_id;

2、over(partition by t.u_id)用法：

select t.u_id, www.2cto.com

wmsys.wm_concat(t.goods || '(' || t.u_id || '斤)') over(partition by t.u_id)

from tb_index t;

sys_connect_by_path

select 'select '|| wm_concat('t.'||column_name) || ' from TB_INDEX t' from user_tab_columns where table_name='TB_INDEX';

sys_connect_by_path(columnname, seperator) ：用来构造树路径的，所以需要和connect by一起来用。

sys_connect_by_path 函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示　

select t.areaid,

t.parentareaid,

t.areaname,

sys_connect_by_path(t.areaname, '-') area

from tb_index t

start with t.areaname = '中国'

connect by t.parentareaid = prior t.areaid;

listagg：

11gr2还新增了一个分析函数LISTAGG，这个函数的功能实现字符串的连接

create table t (id number, name varchar2(30), type varchar2(20));

insert into t

select rownum, object_name, object_type from dba_objects;

select listagg(name, ',') within group(order by id)

from t

where rownum < 10;

select type, listagg(name, ',') within group(order by id) name

from t

where type in ('DIRECTORY', 'JAVA SOURCE', 'SCHEDULE')

group by type;

select name,

listagg(name, ',') within group(order by id) over(partition by type) s_name

from t

where type in ('DIRECTORY', 'JAVA SOURCE', 'SCHEDULE');

列转行

1.SQL>select *from t;

2. IDNAME

3.---------- ----------------

4. 1 0,1,5,2,8,10

5. 2 9,7,8

6. 3 你好,他好,大家好

  ID NAME
---------- --------
          1 0
          1 1
          1 5
          1 2
          1 8
          1 10
          2 9
          2 7
          2 8
          3 你好
         3 他好
         3 大家好

select id,

substr(name,

instr(name, ',', 1, rownum) + 1,

instr(name, ',', 1, rownum + 1) - instr(name, ',', 1, rownum) - 1) name

from (select id, ',' || name || ',' name from test)

connect by rownum < length(translate(name, ',' || name, ','));

字符串

倒序查找（instr参数传-1）

select instr('hgda','a',-1) from dual;

select length('123afgha56a789') -

regexp_instr(reverse('123afgha56a789'), 'a', 1) + 1 opt

from dual;

正则表达式

SELECT LENGTH(REGEXP_REPLACE(REPLACE('123,45,6,5', ',', '@'), '[^@]+', '')) COUNT

FROM DUAL;

Oracle CASE WHEN 用法介绍

1. CASE WHEN 表达式有两种形式

--简单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 CASE WHEN 用法

SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生，2位女生*/

ELSE NULL

END) 男生数,

COUNT (CASE WHEN sex = 2 THEN 1

ELSE NULL

END) 女生数

FROM students GROUP BY grade;

2.2 WHERE CASE WHEN 用法

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 CASE WHEN 用法

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 Table_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.关于IF-THEN-ELSE的其他实现

3.1 DECODE() 函数

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

from employees;

貌似只有Oracle提供该函数，而且不支持ANSI SQL，语法上也没CASE WHEN清晰，个人不推荐使用。

3.2 在WHERE中特殊实现

SELECT T2.*, T1.*

FROM T1, T2

WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')

(T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')

ORACLE TRUNC函数使用

select trunc(trunc(sysdate, 'mm')-1,'mm') ,trunc(sysdate, 'mm') from dual;

1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18
2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.
3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天
4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日
5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天
6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41
8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual  --123.458
15.select trunc(123) from dual  --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

两个Date类型字段：计算这两个日期的时间差（分别以天，小时，分钟，秒，毫秒）：

天：

ROUND(TO_NUMBER(END_DATE - START_DATE))

小时：

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)

分钟：

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)

秒：

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)

毫秒：

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)

日期

Oracle

//上个小时，前两个小时

select to_date(concat(to_char(sysdate, 'yyyy-mm-dd ') ||(to_char(sysdate, 'hh24') - 1),':00:00'), 'yyyy-MM-dd hh24:mi:ss') start_time,

to_date(concat(to_char(sysdate, 'yyyy-mm-dd ') ||(to_char(sysdate, 'hh24') -1),':30:00'), 'yyyy-MM-dd hh24:mi:ss') start_time1,

concat(to_char(sysdate, 'yyyy-mm-dd hh24'), ':00:00') end_time from dual;

select to_date(concat(to_char(sysdate-2/24 , 'yyyy-mm-dd ') ||(to_char(sysdate, 'hh24') - 2),':00:00'),'yyyy-MM-dd hh24:mi:ss') from dual;

提前日期：

select sysdate,sysdate-1 one_date,sysdate-1/24 one_hour,sysdate-1/(60*24) one_minute,sysdate-1/(60*24*60) one_second from dual

查询前一个月

select concat(to_char(sysdate,'yyyy')||to_char(add_months(sysdate,-1), '-MM-'),'01 00:00:00')start_time,

concat(to_char(sysdate,'yyyy')||to_char(add_months(sysdate,-1), '-MM-')||to_char(LAST_DAY(sysdate),'dd '),'23:59:59') end_time from dual

查询前一年

select extract(year from sysdate)-1||'-01-01 00:00:00' start_time,concat(extract(year from sysdate)-1||'-12-'||to_char(LAST_DAY(sysdate),'dd '),'23:59:59') end_time from dual

-- 5分钟循环

select trunc(sysdate) +5 / (24 * 60) *floor(extract(minute from timestamp '2001-