关闭

ORACLE 基础知识以及基本函数

114人阅读 评论(0) 收藏 举报

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

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".

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:40323次
    • 积分:1386
    • 等级:
    • 排名:千里之外
    • 原创:100篇
    • 转载:19篇
    • 译文:0篇
    • 评论:0条
    文章分类