ORACLE 基础知识以及基本函数

最新推荐文章于 2022-11-21 21:38:57 发布

老何_低调

最新推荐文章于 2022-11-21 21:38:57 发布

阅读量619

点赞数

本文链接：https://blog.csdn.net/heqinghua217/article/details/51543038

版权

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

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 ;

游标和存储过程demo如下：


CREATE OR REPLACE PROCEDURE yzgl.pro_update_taf_heqh
is 
 v_zfxx_uuid varchar(50);
 v_gx_uuid varchar(50);
 v_zfz_uuid varchar(50);
 v_gydw varchar(50);
 v_flag varchar(50);

begin
	
	v_flag = to_char(sysdate, 'yyyymmddhh24miss');
	
	for v_obj in (
		with nt_a as(
	     select cpzh,  sp_lx, max(zf_bh) zf_bh
			from "YZGL"."T_YZGL_DK_XFXX_LCCP" 
			where "SF_SX" = '1' 
			group by   cpzh,  sp_lx 
			having count(1) > 1
		)
		select a.*, b.cpzh 
		from  yzgl.t_yzgl_dk_jbxx a ,  "YZGL"."T_YZGL_DK_XFXX_LCCP" b
		where a.bh = b.zf_bh 
		and b.sf_sx = '1'
		and exists(
			select 1 from nt_a ia where ia.cpzh = b.cpzh and ia.sp_lx = b.sp_lx 
		)
		and a.bh not in (
			select zf_bh from yzgl.t_yzgl_dk_fzxx_zfxx  where zf_bh is not null
		)
	 )
    loop 
    	v_zfxx_uuid = SYS_GUID();
    	v_gx_uuid = SYS_GUID();
    	v_gydw = '';
    	
    	select organize_name into v_gydw from auth.t_org where  organize_code  = v_obj.JY_ID ;
    	
    	-- 1 
    	insert into "YZGL"."T_YZGL_DK_FZXX_ZFXX"("ID", "ZF_BH", "XM", "XB", "CS_RQ", "ZJ_LX", "ZJ_HM", "ZM", "XZ", "YPXQ", "PJZH", "GYDW", "JTDZ_SSQ", "JTDZ_DZ", "C_CJR", "C_CJ_RQ",  "STATUE", "ZF_ZT1") 
		VALUES(v_zfxx_uuid, v_obj.bh, v_obj.xm, v_obj.xb, v_obj.cs_rq, v_obj.zj_lx, v_obj.zj_hm, v_obj.zm, v_obj.xz, v_obj.XQ, v_obj.CPZH, v_gydw , v_obj.HJDZ_SSQ, v_obj.HJDZ_DZ,'heqh'||v_flag, sysdate, '0', v_obj.zf_zt1);
    
    	-- 2 
    	insert into "YZGL"."T_YZGL_DK_FZXX_ZFZ_ZF_GX"("ID", "ZFZ_ID", "ZFXX_ID", "ZF_BH", "STATUE") 
		VALUES(v_gx_uuid, v_obj.CPZH, v_zfxx_uuid, v_obj.bh, '');

    end loop;
    
    --3、  
 	for v_obj in (select PJZH  from "YZGL"."T_YZGL_DK_FZXX_ZFXX"  where c_cjr='heqh'||v_flag group by PJZH )
 	 loop 
 	 	v_zfz_uuid = SYS_GUID();
 	 	insert into "YZGL"."T_YZGL_DK_FZXX_ZFZ"("ID", "ZFZ_LX") VALUES(v_zfz_uuid, '1'); 
 	 	
 	 	--修改关系表中的罪犯组id
 	 	update "YZGL"."T_YZGL_DK_FZXX_ZFZ_ZF_GX" set ZFZ_ID = v_zfz_uuid where ZFZ_ID = v_obj.PJZH;
 	 end loop;
 	 
 	 commit;
 	
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

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

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

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

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

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.

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

Week of year (1-52 or 1-53) ｂａｓｅd on the ISO standard.

Day of week (1-7).

DAY

Name of day.

Day of month (1-31).

DDD

Day of year (1-366).

Abbreviated name of day.

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

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

Minute (0-59).

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

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'