程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle常用SQL函數整理,oraclesql函數

Oracle常用SQL函數整理,oraclesql函數

編輯:Oracle教程

Oracle常用SQL函數整理,oraclesql函數


--返回ASCII碼
select  ASCII('A') "A的ASCII碼" ,ASCII('a') "a的ASSCII碼" from dual ;
--反向
select   CHR(65)  from dual;

--連接兩個字符串,如果一個為空則返回有值的

select concat('Oracle','11g') "合並後" from dual;

--返回字符串中 每個  單詞首字母大寫

select initcap('oracle universal installer') "首字母大寫後" from dual;

--返回一個字符在字符串'j'中從第6個開始第3次出現的位置

select INSTR ('jjjjjireutoilkjsdflkdsjfoiuewrlkdsjflkoieuroiu','j',-6,3) from dual ;
--返回字符串長度
select length('oituoireutoilkjsdflkdsjfoiuewrlkdsj') from dual; --6為正數時,j的位置
select length('jjjjjireutoilkj') from dual;                     --6為負數時,j的位置,從左往右數,但是返回值是正數位置

--去掉一個字符串從左邊開始,包含'iuyu'的字符
select LTRIM('iuuuuyyuyuyuyuyuidsfdysytu','iuyu') from dual;

--替代字符串中指定的字符為指定的字符
select replace ('opopiiuopoijiuu','o','A') from Dual;

-------------------------------------------數字類函數
--返回大於或等於n的 最小整數

select ceil(9.02) from dual; --向上取整

--返回小於或等於n 的 最大整數

select floor(8.6345)  from dual;--向下取整


--sign 若正 返回1 若負 返回-1

select sign(9) from dual; --可以根據狀態,比較大小


FLOOR——對給定的數字取整數位--向下取整
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
CEIL-- 返回大於或等於給出數字的最小整數--向上取整
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
              4
ROUND——按照指定的精度進行四捨五入
SQL> select round(3.1415926,4) from dual;
ROUND(3.1415926,4)
------------------
            3.1416
TRUNC——按照指定的精度進行截取一個數
SQL> select trunc(3.1415926,4) from dual;
ROUND(3.1415926,4) 3.1415
------------------
           
------------------------------------------------------------------
 select sysdate  from   dual;

--取結果集前51條
select *
  from (select a.factorvalue,'1','105A'
          from productdata.femriskfactorlistb a
         where 1 = 1
           and a.riskcode = '106001003'
           and a.factortype = 'TextAge'
         order by a.factorvalueorder)
 where rownum <= 51;

----正則替換
 select regexp_replace('87Y','[^0-9]') from dual;----87
 
 --查詢重復記錄並刪除
DELETE        SDBANKMAPPINGTEST WHERE ID IN(
SELECT  (comcode) from SDBANKMAPPINGTEST
group by (comcode) having count(*)>1 );
-- Add/modify columns
            
            
alter table test_daoru rename column NEW_FIELD_NAME to name;

 

--刪除一個字段

alter table test_daoru
drop column ;

---快讀插入N條數據-----------------------------------------------------------------------------
    declare  
    i integer; --定義變量  
    begin  
    i := 18;  
    loop  
    /* 插入數據 feeyear繳費期限   period保險期間 */  
    insert into sh_1044_nv(textage,sex,feeyear,period,amnt,cod1)  
    values(
    TO_CHAR(i||'Y'),'1','1000Y','10Y','','10000'
    );
    /* 參數遞增 */  
    i := i + 1;  
    /* 停止條件 */  
    exit when i > 60;  
    end loop;  
    commit;  
    end;  


---Oracle自動序列號--------------------------------------------
  create sequence <序列名稱>
  start with <起始數>
  increment by <增長量>
  [maxvalue 值]
  [minvalue 值]
  [cycle 當到達最大值的時候,將繼續從頭開始]
  [Nocycle -- 一直累加,不循環]
  [Cache ]

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

--創建一個方法通過生日得到年齡
create or replace function GetAge(brithday varchar2) return number as
  v_Age number(3);
begin
  select to_char(sysdate, 'yyyy') -
         to_char(to_date(brithday, 'yyyy-mm-dd'), 'yyyy') +
         decode(sign(to_char(sysdate, 'mmdd') -
                     to_char(to_date(brithday, 'yyyy-mm-dd'), 'mmdd')),
                -1,
                -1,
                0,
                0,
                1,
                0)
    into v_Age
    from dual;
    return v_Age;
end;

----------------------------------------------------------
--3.查出鎖定表的sid, serial#,os_user_name, machine_name, terminal,鎖的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s,v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
----------------------------------------------------------    
            
--鎖表查詢
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid;
--==================
--釋放SESSION SQL:
--alter system kill session 'sid, serial#';
ALTER system kill session '23, 1647';
----------------------------------------------------------    
---查看數據庫空間大小
select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space                                       
         group by tablespace_name) b                                
 where a.tablespace_name = b.tablespace_name                        
 order by ((a.bytes - b.bytes) / a.bytes) desc;                     
 ----------------------------------------------------------            
--修改字段長度                                                            
 alter table test_feilv_01 modify ( textage varchar2(10))           
----------------------------------------------------------            
 --匯總
select decode(grouping(a.scene_id) + grouping(a.enter_id),
                            1,'小計',
                            2,'總計',                 
                            a.enter_id) "公司編碼",
             decode(grouping(a.scene_id), 1, count(*) || '條', a.scene_id) "報文編號"
--sum(f_pagesnumber) sum_pagesnumbers    
    from tft_xsl_template a
 group by rollup(a.enter_id, a.scene_id);                                                                 
----------------------------------------------------------
--oracle 中的替換
regexp_replace(1,2,3,4,5,6)

語法說明:1:字段   2:替換的字段  3:替換成什麼  4:起始位置(默認從1開始)  5:替換的次數(0是無限次)  6:不區分大小寫
                                                                  
----------------------------------------------------------
/*1. 利用游標循環更新、刪除MemberAccount表中的數據*/
DECLARE My_Cursor CURSOR --定義游標
FOR (SELECT * FROM dbo.MemberAccount) --查出需要的集合放到游標中
OPEN My_Cursor; --打開游標
FETCH NEXT FROM My_Cursor ; --讀取第一行數據
WHILE @@FETCH_STATUS = 0     
  BEGIN         
    --UPDATE dbo.MemberAccount SET UserName = UserName + 'A' WHERE CURRENT OF My_Cursor; --更新         
    --DELETE FROM dbo.MemberAccount WHERE CURRENT OF My_Cursor; --刪除         
      FETCH NEXT FROM My_Cursor; --讀取下一行數據     
  END
  CLOSE My_Cursor; --關閉游標
  DEALLOCATE My_Cursor; --釋放游標
    GO

/*2. 利用游標循環更新MemberService表中的數據(更新每個用戶所購買服務的時間)*/
 
DECLARE @UserId varchar(50)  
DECLARE My_Cursor CURSOR --定義游標
FOR (SELECT UserId FROM dbo.MemberAccount) --查出需要的集合放到游標中
OPEN My_Cursor; --打開游標
FETCH NEXT FROM My_Cursor INTO @UserId; --讀取第一行數據(將MemberAccount表中的UserId放到@UserId變量中)
WHILE @@FETCH_STATUS = 0     BEGIN         PRINT @UserId; --打印數據(打印MemberAccount表中的UserId)         
UPDATE dbo.MemberService SET ServiceTime = DATEADD(Month, 6, getdate()) WHERE UserId = @UserId; --更新數據         
FETCH NEXT FROM My_Cursor INTO @UserId; --讀取下一行數據(將MemberAccount表中的UserId放到@UserId變量中)     
END CLOSE My_Cursor; --關閉游標
DEALLOCATE My_Cursor; --釋放游標
GO
----------------------------------------------------------

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved