Oracle SQL常用函數(shù)

概述

SQL函數(shù)有單行函數(shù)和多行函數(shù),其區(qū)別為：

• 單行：輸入一行,返回一行,如字符、數(shù)字、轉(zhuǎn)換、通用函數(shù)等
• 多行：輸入多行,返回一行,也稱(chēng)為分組函數(shù)、組函數(shù)、聚合函數(shù),且多行函數(shù)會(huì)自動(dòng)濾空

單行函數(shù)

字符函數(shù)

• CONCAT(X,Y)： 連接字符串X和Y
• INSTR(X,STR)： 后面STR在前面字符串X第一次出現(xiàn)的位置,一般用于判斷STR是否存在于X中,若存在,則結(jié)果肯定大于0,否則結(jié)果為0表示不存在
• LOWER(X)： X轉(zhuǎn)換成小寫(xiě)
• UPPER(X)： X轉(zhuǎn)換成大寫(xiě)
• INITCAP(X)： X轉(zhuǎn)換首字母大寫(xiě)

數(shù)字函數(shù)

• ROUND(X[,Y])： 四舍五入
  • 在缺省y時(shí),默認(rèn)y=0;比如：ROUND(3.56)=4
  • y是正整數(shù),就是四舍五入到小數(shù)點(diǎn)后y位。ROUND(5.654,2)=5.65
  • y是負(fù)整數(shù),四舍五入到小數(shù)點(diǎn)左邊|y|位。ROUND(351.654,-2)=400
• TRUNC(x[,y]): 直接截取取整,不進(jìn)行四舍五入
  • 在缺省y時(shí),默認(rèn)y=0;比如：TRUNC(3.56)=3
  • Y是正整數(shù),就是四舍五入到小數(shù)點(diǎn)后y位。TRUNC (5.654,2)=5.65
  • y是負(fù)整數(shù),四舍五入到小數(shù)點(diǎn)左邊|y|位。TRUNC (351.654,-2)=300

轉(zhuǎn)換函數(shù)

轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類(lèi)型轉(zhuǎn)換為另外一種數(shù)據(jù)類(lèi)型。常見(jiàn)的轉(zhuǎn)換函數(shù)有

• TO_CHAR(d|n[,fmt]): 把日期和數(shù)字轉(zhuǎn)換為制定格式的字符串。Fmt是格式化字符串
  • TO_CHAR對(duì)日期的處理
    • SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;
• TO_DATE(X,[,fmt]): 把一個(gè)字符串以fmt格式轉(zhuǎn)換成一個(gè)日期類(lèi)型
• TO_NUMBER(X,[,fmt]): 把一個(gè)字符串以fmt格式轉(zhuǎn)換為一個(gè)數(shù)字

通用函數(shù)

• NVL函數(shù): 將空值轉(zhuǎn)換為已知值,可以使用的數(shù)據(jù)類(lèi)型為：數(shù)字、日期、字符,數(shù)據(jù)類(lèi)型必須匹配
• NVL2(expr1,expr2,expr3): 如果參數(shù)1非空not null,則返回參數(shù)2的值,否則返回參數(shù)3的值
• 條件表達(dá)式:在使用if-then-else邏輯時(shí)可以通過(guò)如下兩種方式表示
  • decode: 條件表達(dá)式,在使用if-then-else邏輯時(shí)可以通過(guò)decode(col/expression,search1,result1,search2,result2,....,,.........,default)
  • case expr when comparison_expr1 then return_expr1 when comparison_expr2 then return_expr2... else_expr

多行函數(shù)

嵌套函數(shù)distinct

distinct可用來(lái)過(guò)濾掉多余的重復(fù)記錄只保留一條,但往往只用它來(lái)返回不重復(fù)記錄的條數(shù),而不是用它來(lái)返回顯示不重記錄的所有值。一般和count配合使用,作為統(tǒng)計(jì)非空且不重復(fù)的記錄數(shù)

SELECT COUNT(DISTINCT(Column)) FROM TableName;

注意:DISTINCT關(guān)鍵字效率會(huì)比較低,如果僅僅是為了顯示不重復(fù)的記錄,建議使用group by,
因?yàn)閐istinct只有用二重循環(huán)查詢(xún)來(lái)解決,而這樣對(duì)于一個(gè)數(shù)據(jù)量非常大的表來(lái)說(shuō),無(wú)疑是會(huì)直接影響到效率的

PLSQL

概述

PL/SQL(Procedure Language/Structured Query Language)是一種高級(jí)數(shù)據(jù)庫(kù)程序設(shè)計(jì)語(yǔ)言,同時(shí)PL/SQL也是塊結(jié)構(gòu)語(yǔ)言;也是對(duì)SQL語(yǔ)言存儲(chǔ)過(guò)程語(yǔ)言的擴(kuò)展,也稱(chēng)為過(guò)程處理語(yǔ)言。專(zhuān)門(mén)用于在各種環(huán)境下對(duì)ORACLE數(shù)據(jù)庫(kù)進(jìn)行訪(fǎng)問(wèn),該語(yǔ)言已經(jīng)集成于數(shù)據(jù)庫(kù)服務(wù)器中,所以PL/SQL代碼可以對(duì)數(shù)據(jù)進(jìn)行快速高效的處理

PL/SQL塊結(jié)構(gòu)和組成元素

塊結(jié)構(gòu)

PL/SQL程序由三個(gè)塊組成,即聲明部分DECLARE、執(zhí)行部分BEGIN(必須存在)、異常處理部分EXCEPION、結(jié)束END

• DECLARE: 以關(guān)鍵字DECLARE開(kāi)頭,屬于一個(gè)可選部分,在此聲明PL/SQL用到的變量、類(lèi)型、游標(biāo)，以及局部的存儲(chǔ)過(guò)程和函數(shù)
• BEGIN: 包含在關(guān)鍵字BEGIN和END之間,這是一個(gè)強(qiáng)制性部分。由程序的可執(zhí)行PL/SQL語(yǔ)句組成,并且應(yīng)該有至少一個(gè)可執(zhí)行代碼行,它可以只是一個(gè)NULL命令,表示不執(zhí)行任何操作
• EXCEPION: 以關(guān)鍵字EXCEPTION開(kāi)頭。也是一個(gè)可選部分,作用于錯(cuò)誤異常處理

基本語(yǔ)法

變量命名

為了開(kāi)發(fā)過(guò)程中統(tǒng)一規(guī)范,在系統(tǒng)設(shè)計(jì)階段需要開(kāi)發(fā)人員共同遵守要求,變量命名方法建議采取如下

變量類(lèi)型

PL/SQL中常用的變量類(lèi)型包括標(biāo)量、記錄類(lèi)型(記錄類(lèi)型是把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單元存儲(chǔ)起來(lái),稱(chēng)為PL/SQL RECORD的域,其作用就是存放互不相同但邏輯相關(guān)的信息)、游標(biāo)變量等

DECLARE
	v_deptname  VARCHAR2(10);		--定義標(biāo)量變量
	v_loopcounter  BINARY_INTEGER;  --使用PL/SQL類(lèi)型定義標(biāo)量變量
	--定義記錄類(lèi)型
	TYPE t_employee IS RECORD(
		empname VARCHAR2(20),
		empno NUMBER(7),
		job VARCHAR2(20)
	);
	v_employee t_employee;		--定義記錄類(lèi)型變量
	TYPE csor IS REF CURSOR;		--定義游標(biāo)變量
	v_date DATE NOT NULL DEFAULT SYSDATE;    --定義變量并指定默認(rèn)值
BEGIN
	NULL;
END;

流程控制

條件判斷

• if...then elsif then...else...end if;
• case...when...then...end

循環(huán)結(jié)構(gòu)

• loop...exit when...end loop
• while...loop...end loop
• for i in ...loop...end loop

其他

• goto:PL/SQL編程語(yǔ)言中的GOTO語(yǔ)句在同一子程序中提供從GOTO到標(biāo)記語(yǔ)句的無(wú)條件跳轉(zhuǎn),但不推薦使用GOTO語(yǔ)句,因?yàn)樗y以追蹤程序的控制流程,使程序難以理解和難以修改
• exit
  • 當(dāng)循環(huán)中遇到EXIT語(yǔ)句時(shí),循環(huán)將立即終止,繼而執(zhí)行循環(huán)后面的下一個(gè)語(yǔ)句
  • 如果使用嵌套循環(huán)(即在另一個(gè)循環(huán)中有一個(gè)循環(huán)),則EXIT語(yǔ)句將停止執(zhí)行最內(nèi)循環(huán),轉(zhuǎn)而執(zhí)行最外層循環(huán)的下一次循環(huán)

游標(biāo)(類(lèi)似于java中的Iterator)

在PL/SQL程序中,對(duì)于處理多行記錄的事務(wù)經(jīng)常使用游標(biāo)來(lái)實(shí)現(xiàn),游標(biāo)是一個(gè)指向上下文的句柄或指針,通過(guò)游標(biāo)PL/SQL可以單獨(dú)操縱結(jié)果集中的每一行,即游標(biāo)可以把集合操作轉(zhuǎn)換成對(duì)單個(gè)記錄進(jìn)行不同處理的方式

顯示游標(biāo)

• 定義游標(biāo):?CURSOR cursor_name is select_statement --關(guān)聯(lián)SQL語(yǔ)句
• 打開(kāi)游標(biāo): OPEN cursor_name(PL/SQL程序不能用OPEN語(yǔ)句重復(fù)打開(kāi)一個(gè)游標(biāo))
• 提取游標(biāo)數(shù)據(jù): FETCH cursor_name INTO var_name(fetch會(huì)自動(dòng)移動(dòng)游標(biāo)指針)
• 關(guān)閉游標(biāo):?CLOSE cursor_name

  /**
  該P(yáng)L/SQL語(yǔ)句含義為:通過(guò)定義一個(gè)存儲(chǔ)過(guò)程DeleteStationDRI,根據(jù)輸入的PMILESOTNEID刪除TBLSTATIONDRI表中對(duì)應(yīng)的記錄
  1.定義了一個(gè)游標(biāo) C_1,用于查詢(xún)滿(mǎn)足條件FMILESONEID = PMILESOTNEID的記錄的FSTATIONDRIID;并聲明一個(gè)變量V_STATIONDRIID用于存儲(chǔ)查詢(xún)結(jié)果
  2.打開(kāi)游標(biāo)C1,獲取id并根據(jù)id刪除表中對(duì)應(yīng)記錄。
  注意:如果刪除操作發(fā)生異常，忽略異常并繼續(xù)循環(huán)
  **/
  
  create or replace procedure DeleteStationDRI(PMILESOTNEID in  NUMBER) is
    CURSOR C_1 IS
    SELECT T.FSTATIONDRIID
    FROM TBLSTATIONDRI T
     WHERE T.FMILESONEID =PMILESOTNEID;
    VSTATIONDRIID NUMBER;
  begin
    OPEN C_1 ;
      LOOP
         FETCH C_1 INTO VSTATIONDRIID;
         EXIT WHEN C_1%NOTFOUND;
         BEGIN 
            DELETE FROM TBLSTATIONDRI T
            WHERE T.FSTATIONDRIID = VSTATIONDRIID;
         EXCEPTION WHEN OTHERS THEN
             NULL;
         END;
      END LOOP;
      CLOSE C_1;
    RETURN;
  end DeleteStationDRI;

隱式游標(biāo)

BEGIN
FOR c IN (SELECT * FROM employees WHERE ROWNUM<=10) LOOP
DBMS_OUTPUT.PUT_LINE(c.employee_id||' '||c.first_name||' '||c.last_name);
END LOOP;
END;

異常處理

異常情況處理是用來(lái)處理正常執(zhí)行過(guò)程中未預(yù)料的事件,包括異常處理預(yù)定義錯(cuò)誤(大約24個(gè),由ORACLE自動(dòng)觸發(fā))和自定義錯(cuò)誤,由于PL/SQL程序塊一旦產(chǎn)生異常而沒(méi)有指出如何處理時(shí),程序就會(huì)自動(dòng)終止整個(gè)程序運(yùn)行

存儲(chǔ)函數(shù)(有返回值)/存儲(chǔ)過(guò)程(無(wú)返回值)

概述

ORACLE提供可以把PL/SQL程序存儲(chǔ)在數(shù)據(jù)庫(kù)中,并可以在任何地方來(lái)運(yùn)行它,稱(chēng)為存儲(chǔ)函數(shù)或者存儲(chǔ)過(guò)程,它們統(tǒng)稱(chēng)為PL/SQL子程序且屬于被命名的PL/SQL塊,它們都是為了完成特定功能的程序;過(guò)程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回?cái)?shù)據(jù),而過(guò)程則不返回?cái)?shù)據(jù)

• 如果存儲(chǔ)過(guò)程或存儲(chǔ)函數(shù)帶參數(shù)的話(huà)我們需要指明是輸入?yún)?shù)(in,默認(rèn)值)還是輸出參數(shù)(out)
• 存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)都可以通過(guò)out參數(shù)指定一個(gè)或多個(gè)輸出參數(shù),我們可以利用out參數(shù)在過(guò)程或函數(shù)中實(shí)現(xiàn)返回一個(gè)或多個(gè)值(即存儲(chǔ)過(guò)程本來(lái)不能有返回值,但利用out參數(shù)我們就可以實(shí)現(xiàn)存儲(chǔ)過(guò)程返回值)
• 一般如果需要返回多個(gè)值,我們優(yōu)先使用存儲(chǔ)過(guò)程,如果只要返回一個(gè)值我們優(yōu)先使用存儲(chǔ)函數(shù)

存儲(chǔ)函數(shù)

create [or replace] function 函數(shù)名(paramName in|out typeName,...)
	return 函數(shù)值類(lèi)型
	as|is--相當(dāng)于PL/SQL塊的declare，這里不可省略
	PL/SQL子程序體;

創(chuàng)建

create or replace function queryEmpIncome(eno in number)
return number
as
    --定義變量保存員工的薪水和獎(jiǎng)金
    v_sal emp.sal%type;
    v_bonus emp.comm%type;
begin
    --得到員工的月薪和獎(jiǎng)金
    select sal,comm into v_sal,v_bonus from emp where empno=eno;
    --直接返回年收入
    return v_sal*12+nvl(v_bonus,0);
end;

調(diào)用

declare
    v_sal number;
begin
    --得到員工7891的年收入
    v_sal:=queryEmpIncome(7891);
    dbms_output.put_line(v_sal);
end;

存儲(chǔ)過(guò)程

--創(chuàng)建或替換一個(gè)存儲(chǔ)過(guò)程參數(shù)列表需要指明輸入或者輸出參數(shù)
create [or replace] procedure 過(guò)程名(paramName in|out typeName,...)
	as|is--相當(dāng)于PL/SQL塊的declare，這里不可省略
	PL/SQL子程序體;

創(chuàng)建

create or repalce procedure raisesalary(eno in number) 
    as
        --定義一個(gè)變量保存漲前的薪水
        v_sal emp.sal%type;
    begin
        --得到員工的漲前的薪水
        select sal into v_sal emp where empno=eno;
        --給員工漲100
        update emp set sal = sal+100 where empno = eno;
        --這里進(jìn)行了update,一般不在存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)中進(jìn)行提交事務(wù),一般由調(diào)用者進(jìn)行提交
        --打印漲前和漲后的薪水
        dbms_output.put_line('漲前:'||v_sal||'漲后'||(psal+100));
    end;

調(diào)用

begin
    raisesalary(7839); --員工號(hào)為7839漲工資
    raisesalary(7566); --員工號(hào)為7566漲工資
end;

觸發(fā)器Trigger

概述

觸發(fā)器是許多關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)都提供的一項(xiàng)技術(shù),在ORACLE系統(tǒng)里,觸發(fā)器類(lèi)似過(guò)程和函數(shù),都有聲明、執(zhí)行、異常處理過(guò)程的PL/SQL塊。觸發(fā)器在數(shù)據(jù)庫(kù)里以獨(dú)立的對(duì)象存儲(chǔ),它不同于存儲(chǔ)過(guò)程通過(guò)其他程序啟動(dòng),觸發(fā)器是由一個(gè)事件來(lái)觸發(fā)運(yùn)行,即觸發(fā)器是當(dāng)某個(gè)事件發(fā)生時(shí)自動(dòng)地隱式運(yùn)行,常用于數(shù)據(jù)庫(kù)表在進(jìn)行INSERT、UPDATE、DELETE操作或者對(duì)視圖進(jìn)行類(lèi)似操作

項(xiàng)目開(kāi)發(fā)實(shí)戰(zhàn)之oracle使用序列和觸發(fā)器來(lái)實(shí)現(xiàn)主鍵自動(dòng)遞增的功能

創(chuàng)建TBLROOM表

create table TBLROOM
(
  room_id     INTEGER not null,
  floor_id    INTEGER,
  room_name   NVARCHAR2(20),
)
tablespace XXXX
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns
comment on column TBLROOM.room_id
  is '附房ID。主鍵，自增';
comment on column TBLROOM.floor_id
  is '樓層位置ID。外鍵，連TblFloor';
comment on column TBLROOM.room_name
  is '附房名稱(chēng)。';
alter table TBLROOM
  add constraint PK_TBLROOM primary key (ROOM_ID)
  using index
  tablespace XXXX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

創(chuàng)建表序列

create sequence S_ROOM_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 221
increment by 1
cache 20;

?創(chuàng)建表的觸發(fā)器(每次向?tblroom?表插入新數(shù)據(jù)之前,自動(dòng)生成一個(gè)唯一的roomID)

CREATE OR REPLACE TRIGGER TIB_TblRoom
  before insert on tblroom
  for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
begin
    select S_Room_ID.NEXTVAL INTO :new.room_id from dual;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);-- PL/SQL內(nèi)置的存儲(chǔ)過(guò)程,用于拋出一個(gè)應(yīng)用程序錯(cuò)誤
end TIB_TblRoom;

Mybatis.xml調(diào)用實(shí)現(xiàn)添加記錄時(shí)主鍵自增?

<insert id="add">	
    <selectKey keyProperty="roomId" resultType="java.lang.Long" order="AFTER">
        SELECT S_ROOM_ID.CURRVAL FROM DUAL
    </selectKey>	
    INSERT INTO TBLROOM
      (
        FLOOR_ID,
        ROOM_NAME,
      )
    VALUES
      (
        #{floorEntity.floorID},
        #{roomName},
      )
</insert>

其他

開(kāi)發(fā)擴(kuò)展

• || 連接符,在連接符中的字符串或者日期必須使用單引號(hào),不能使用雙引號(hào);
• escape轉(zhuǎn)義字符,用于特殊字符查詢(xún)
  • select * form table where column like '%#_%' escape '#‘,用于查詢(xún)包含_字符的數(shù)據(jù)

• 在Oracle中,join=inner join,left join=left outer join,right join=right outer join文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-687807.html

Oracle與Mysql區(qū)別

• 數(shù)據(jù)類(lèi)型方面
  • Oracle支持更多的數(shù)據(jù)類(lèi)型,包括blob、clob、nclob、bfile等,MySQL則沒(méi)有這些類(lèi)型
• 存儲(chǔ)引擎方面
  • Oracle支持多種存儲(chǔ)引擎,MySQL也是如此,但是Oracle默認(rèn)的存儲(chǔ)引擎是ACID-compliant的,而MySQL默認(rèn)使用的是不具備ACID特性的MyISAM存儲(chǔ)引擎
• 查詢(xún)優(yōu)化方面
  • Oracle具有更加成熟的查詢(xún)優(yōu)化器,能夠更好的處理復(fù)雜查詢(xún),MySQL則需要手動(dòng)進(jìn)行查詢(xún)優(yōu)化

到了這里，關(guān)于數(shù)據(jù)庫(kù)(二) Oracle篇的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！