ORACLE 基础知识以及基本函数

原创 2016年05月31日 09:16:59

----------------今天是一年中的第几天,一个月中的第几天!

SELECT trunc(sysdate,'dd') - to_date(to_char(sysdate,'yyyymm')||'01','yyyymmdd') from dual;


SELECT trunc(sysdate,'dd') - to_date(to_char(sysdate,'yyyy')||'0101','yyyymmdd') from dual;--nian

 

-------------游标实例

declare    
     Cursor mycursor is  
     select fullname from zjpii_report.city_config; 
     
     a varchar2(64)  ;
Begin
    open mycursor ;
    Loop  
     Fetch mycursor into a;   
     Exit when mycursor%notfound;   
            
     Dbms_Output.put_line(a);   
   end loop;  

   close mycursor;
  
   Exception    
     when others then  
         Dbms_Output.put_line(Sqlerrm);      
end;   

 

 

一、oracle的简单应用

 

to_char(, 'FM990.00')保留两位小数(。00)(FM990)表示前面有一个0. 如下列子

 --select to_char(1/3,'FM990.00')||'%' from dual--

 

select to_char(TRIM(' r  334r rr  ')) from dual;--去掉两边空格
select to_char(LTRIM(' r  334r rr  ')) from dual;--去掉左边空格
select to_char(RTRIM(' r  334r rr  ')) from dual;--去掉右边空格
select to_char(TRIM('0' from '00989760000')) from dual; --去掉所有的r

 

decode函数,相当于if、、else

  select decode(instr(to_char(1/3), '0.'), 0, '0'||to_char(1/3)) from dual;


DECLARE 
    IDS VARCHAR(8000);
BEGIN
    IDS := '给变量赋值';
    DBMS_OUTPUT.PUT_LINE(IDS);
END
;

--一个if。。else 的应用!

set serveroutput on 
declare 
cj number;
begin 
select avg(成绩字段) into cj from table_name; 
if cj > 75 then 
dbms_output.put_line('平均成绩>75'); 
else 
dbms_output.put_line('平均成绩<75'); 
end if; 
end;

 

  IF in_workcode IS NOT NULL
       THEN
          if(instr(custom_no, ',') = 0) then 
             custom_no := ''''|| custom_no || '''';   
          else 
            custom_no := REPLACE(custom_no, ',', ''''||','||'''');
            custom_no := ''''|| custom_no ||'''';
           end if; 
          conditionstr := conditionstr||' AND a.staffid in ('||  custom_no  ||')';
       END IF;

任何一句话后面都要加上逗号,否则报错,输出用DBMS_OUTPUT.PUT_LINE(要输出的变量);,赋值用“:=” 

二、oracle的截取字符串方法 substr(‘要截取的字符串’,‘从第几个位置开始截取’, ‘截取多少位’)

  substr('This is a test', 6, 2) would return 'is'
    substr('This is a test', 6) would return 'is a test'
    substr('TechOnTheNet', 1, 4) would return 'Tech'
    substr('TechOnTheNet', -3, 3) would return 'Net'
    substr('TechOnTheNet', -6, 3) would return 'The'
    substr('TechOnTheNet', -8, 2) would return 'On'

oracle没有charindex函数,但是有类似的函数 INSTR("字符串", "搜索的字符"),如果找不到返回0!找到返回第一次出现的位置!这里有点和sqlserver中不同的是,sqlserver中的charindex("搜索的字符","字符串" )顺序不同!

    INSTR('1,2,3,', ',') 返回的位置 2

    INSTR('CORPORATE FLOOR','OR', 3, 2)中,源字符串为'CORPORATE FLOOR', 目标字符串为'OR',起始位置为3,取第2个匹配项的位置。

    默认查找顺序为从左到右。当起始位置为负数的时候,从右边开始查找。

    所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的显示结果是

    Instring
    ——————
    14

四、oracle中的for循环 和 while循环的使用列子!

1、 for循环
declare
    i int := 0; 
begin
    for i in 0 .. 100 loop
      dbms_output.put_line(i);
    end loop;
end;

 2、while循环

declare
    ids int := 100;
begin
    while (ids > 1) loop
      ids := ids - 1;
      dbms_output.put_line(ids);
    end loop;
end;

 


DECLARE 
    IDS VARCHAR(8000);
    A VARCHAR2(8);
BEGIN
    IDS := '123,456,789';
    IDS := IDS || ',';    
    WHILE (INSTR(IDS, ',') > 0 )
    LOOP
        A := SUBSTR(IDS, 0, INSTR(IDS, ',') - 1);   
        IDS := SUBSTR(IDS, INSTR(IDS, ',') + 1);
        DBMS_OUTPUT.put_line(A);
    END LOOP;  
END ;


 五、oracle得到一个字符串的长度,length(字符串),sqlserver是len(字符串)

 

六、oracle中如果不小心死锁了一张表的数据,怎么查看死锁和杀掉死锁

--查询死锁的表
SELECT /*+ rule */ 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#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null

--kill掉死锁的数据
  alter system kill session '84,452'; ---84表示的sid列,452表示serial#列

alter system kill session 'sid,serial#';

 

24小时的形式显示出来要用HH24

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;

select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

 

to_date() function

1.日期格式参数 含义说明 

一周中的星期几 

DAY 天的名字,使用空格填充到9个字符 

DD 月中的第几天 

DDD 年中的第几天 

DY 天的简写名 

IW ISO标准的年中的第几周 

IYYY ISO标准的四位年份 

YYYY 四位年份 

YYY,YY,Y 年份的最后三位,两位,一位 

HH 小时,按12小时计 

HH24 小时,按24小时计 

MI  

SS  

MM  

Mon 月份的简写 

Month 月份的全名 

该月的第几个星期 

WW 年中的第几个星期     1.日期时间间隔操作

当前时间减去7分钟的时间

select sysdate,sysdate - interval '7' MINUTE from dual

当前时间减去7小时的时间

select sysdate - interval '7' hour from dual

当前时间减去7天的时间

select sysdate - interval '7' day from dual

当前时间减去7月的时间

select sysdate,sysdate - interval '7' month from dual

当前时间减去7年的时间

select sysdate,sysdate - interval '7' year from dual

时间间隔乘以一个数字

select sysdate,sysdate - 8 *interval '2' hour from dual

 

2.日期到字符操作

select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual

select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual

参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)

 

3. 字符到日期操作

select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual

具体用法和上面的to_char差不多。

 

4. trunk/ ROUND函数的使用

select trunc(sysdate ,'YEAR') from dual

select trunc(sysdate ) from dual

select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual

 

5.oracle有毫秒级的数据类型

--返回当前时间 年月日小时分秒毫秒

select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;

--返回当前 时间的秒毫秒,可以指定秒后面的精度(最大=9)

select to_char(current_timestamp(9),'MI:SSxFF') from dual;

 

6.计算程序运行的时间(ms)

declare

type rc is ref cursor;

l_rc rc;

l_dummy all_objects.object_name%type;

l_start number default dbms_utility.get_time;

begin

for I in 1 .. 1000

loop

open l_rc for

'select object_name from all_objects '||

'where object_id = ' || i;

fetch l_rc into l_dummy;

close l_rc;

end loop;

dbms_output.put_line

( round( (dbms_utility.get_time-l_start)/100, 2 ) ||

' seconds...' );

end;

 

to_char() function

The following are number examples for the to_char function.

to_char(1210.73, '9999.9')

would return '1210.7'

to_char(1210.73, '9,999.99')

would return '1,210.73'

to_char(1210.73, '$9,999.00')

would return '$1,210.73'

to_char(21, '000099')

would return '000021'

 

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.

 

Parameter

Explanation

YEAR

Year, spelled out

YYYY

4-digit year

YYY
YY
Y

Last 3, 2, or 1 digit(s) of year.

IYY
IY
I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

RM

Roman numeral month (I-XII; JAN = I).

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

D

Day of week (1-7).

DAY

Name of day.

DD

Day of month (1-31).

DDD

Day of year (1-366).

DY

Abbreviated name of day.

J

Julian day; the number of days since January 1, 4712 BC.

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

FF

Fractional seconds.


The following are date examples for the to_char function.

to_char(sysdate, 'yyyy/mm/dd');

would return '2003/07/09'

to_char(sysdate, 'Month DD, YYYY');

would return 'July 09, 2003'

to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'MON DDth, YYYY');

would return 'JUL 09TH, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'


You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

Oracle 中的 TO_DATE 和 TO_CHAR 函数 日期处理

Oracle 中的 TO_DATE 和 TO_CHAR 函数 oracle 中 TO_DATE 函数的时间格式,以 2008-09-10 23:45:56 为例 格式 说明 显示值 备注 ...
  • a327736051
  • a327736051
  • 2016年05月15日 21:34
  • 1615

Oracle中时间日期转化函数to_date和to_char用法总结

在实际的工作中会经常会用到to_char()、to_date()函数来对时间、日期进行处理。 1、to_char()函数的用法  1.1、将时间日期按照指定的格式输出,得到的是字符串,而非date...
  • dtjiawenwang88
  • dtjiawenwang88
  • 2017年06月15日 18:04
  • 503

oracle的to_date()与concat()函数使用实例

oracle的to_date()与concat()函数使用实例出于业务需求,和数据库历史原因,业务需要在表中寻找出在个时间前12个月的某些数据,然而数据库存储的时间的分开年月存储,而且是采用varch...
  • weixin_37620246
  • weixin_37620246
  • 2017年07月21日 14:44
  • 344

Oracle 中的 TO_DATE 和 TO_CHAR 函数 日期处理

Oracle 中的 TO_DATE 和 TO_CHAR 函数 日期处理 Oracle 中的 TO_DATE 和 TO_CHAR 函数 oracle 中 TO_DATE 函数的时间格式,以 2008...
  • jaray
  • jaray
  • 2014年11月26日 14:52
  • 1150

Oracle to_date/Trunc函数用法及date字段只精确到日期的问题

本文主要涉及三个问题:to_tate函数、Trunc函数的使用方法及date字段数据只获取日期不获取时间的问题 1、两个函数的使用方法: (1)to_date() 用法:TO_DATE('2016...
  • zcf_0923
  • zcf_0923
  • 2016年04月18日 18:45
  • 3113

往Oracle数据库中插入日期型数据(to_date的用法)

往Oracle数据库中插入日期型数据(to_date的用法) INSERT  INTO  FLOOR  VALUES  ( to_date ( '2007-12-20 18:31:34' , '...
  • qq_29663071
  • qq_29663071
  • 2016年05月25日 11:30
  • 4783

单行函数(日期函数)

伪列不存在的列,但是能显示 SELECT SYSDATE FROM dual;//时间 SELECT SYSTIMESTAMP FROM dual;//时间戳 公式: 日期+数字 = 日期(若...
  • a981583536
  • a981583536
  • 2016年06月15日 23:32
  • 465

2015第一博,Oracle时间段查询,TO_DATE和TO_CHAR使用对比

Oracle时间段查询,TO_DATE和TO_CHAR使用对比
  • JavaAlpha
  • JavaAlpha
  • 2015年02月06日 11:53
  • 4101

TRUNC函数的用法

TRUNC函数的用法   TRUNC函数用于对值进行截断。 用法有两种:TRUNC(NUMBER)表示截断数字,TRUNC(date)表示截断日期。 (1)截断数字: 格...
  • haiross
  • haiross
  • 2013年10月17日 14:38
  • 18252

Oracle dbms_random函数用法快速生成多条测试数据

做数据库开发或管理的人经常要创建大量的测试数据,动不动就需要上万条,如果一条一条的录入,那会浪费大量的时间,本文介绍了Oracle中如何通过一条SQL快速生成大量的测试数据的方法。 首先模拟一下100...
  • xiaokui_wingfly
  • xiaokui_wingfly
  • 2015年05月24日 10:29
  • 1857
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE 基础知识以及基本函数
举报原因:
原因补充:

(最多只允许输入30个字)