Oracle常用SQL函數整理

来源:http://www.cnblogs.com/tstttos/archive/2016/12/20/first_001A.html
-Advertisement-
Play Games

--返回ASCII碼select ASCII('A') "A的ASCII碼" ,ASCII('a') "a的ASSCII碼" from dual ; --反向select CHR(65) from dual;--連接兩個字元串,如果一個為空則返回有值的select concat('Oracle',' ...


--返回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
----------------------------------------------------------


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL常見面試題
    1. 主鍵 超鍵 候選鍵 外鍵 主 鍵： 資料庫表中對儲存數據對象予以唯一和完整標識的數據列或屬性的組合。一個數據列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。 超 鍵： 在關係中能唯一標識元組的屬性集稱為關係模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一 ...
  • 超詳細mysql left join,right join,inner join用法分析
    下麵是例子分析表A記錄如下： aID aNum 1 a20050111 2 a20050112 3 a20050113 4 a20050114 5 a20050115 表B記錄如下: bID bName 1 2006032401 2 2006032402 3 2006032403 4 2006032 ...
  • mysql刪除重覆記錄語句的方法
    例如： id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id是主鍵 要求得到這樣的結果 id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii 方法1 del ...
  • mysql開啟慢查詢日誌及查詢--windows
    MySQL慢查詢配置 1. 慢查詢有什麼用? 它能記錄下所有執行超過long_query_time時間的SQL語句, 幫你找到執行慢的SQL, 方便我們對這些SQL進行優化. 2. 如何開啟慢查詢? 首先我們先查看MYSQL伺服器的慢查詢狀態是否開啟.執行如下命令: show variables l ...
  • SQL中字元串拼接
    1. 概述 在SQL語句中經常需要進行字元串拼接，以sqlserver，oracle，mysql三種資料庫為例，因為這三種資料庫具有代表性。 sqlserver： oracle： mysql： 註意：SQL Server中沒有concat函數。oracle和mysql中雖然都有concat，但是or ...
  • 你可能不知道的技術細節：存儲過程參數傳遞的影響
    前言 很多人認為資料庫其實很簡單，也沒什麼大深入的細節去研究，但是真正的一些細節問題決定著你的是否是專家。 本文主要講述一下存儲過程參數傳遞的一些小細節，很多人知道參數嗅探，本例也可以理解成參數嗅探的威力加強版++ 小例子 1 創建測試表 2 SELECT IDENTITY(INT,1,1) AS ...
  • 遠程連接mysql 1130錯誤解決方法
    ...
  • oracle 誤刪數據恢復
    1.根據時間點查系統版本號scn: select timestamp_to_scn(to_timestamp('2013-01-07 11:20:00','YYYY-MM-DD HH:MI:SS')) from dual 2.查看被誤刪數據的表scn時間點的數據是否要恢復的數據：select cou ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類