oracle函数基础知识,ORACLE 基础知识以及基本函数

----------------今天是一年中的第几天，一个月中的第几天！

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.日期格式参数 含义说明

D一周中的星期几

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

DD月中的第几天

DDD年中的第几天

DY天的简写名

IW ISO标准的年中的第几周

IYYY ISO标准的四位年份

YYYY四位年份

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

HH小时，按12小时计

HH24小时，按24小时计

MI分

SS秒

MM月

Mon月份的简写

Month月份的全名

W该月的第几个星期

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 ｂａｓｅd 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) ｂａｓｅd 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".