ORACLE數(shù)據(jù)庫 ?PL/SQL語句基礎(chǔ)知識點(diǎn)??適合有SQL基礎(chǔ)的人群。?禁止轉(zhuǎn)載！

內(nèi)置函數(shù)
字符串函數(shù)
?? ?lower(列名|字符串)函數(shù)用于返回字符串的小寫形式。
?? ??? ?eg.SELECT ename,sal FROM emp WHERE ename=lower('&ename');

?? ?upper(列名|字符串)函數(shù)用于返回字符串的大寫形式。
?? ??? ?eg.SELECT ename,sal FROM emp WHERE ename=upper('&ename');

?? ?initcap(列名|字符串)函數(shù)將單詞的首字母大寫。
?? ??? ?SELECT initcap('big') FROM dual;

?? ?lpad(字符串，長度，填充字符)函數(shù)用于左補(bǔ)全字符串。?
?? ??? ?SELECT lpad('21','6','0') stock_code FROM dual;?? ??? ?//輸出 ?000021
?? ??? ?SELECT lpad('1234567',6,'0') stock_code FROM dual; ?? ?//輸出 ?123456 當(dāng)原字符串的長度大于預(yù)期長度時(shí)，實(shí)際進(jìn)行的是截取字符串操作。
?? ?
?? ?rpad(字符串，長度，填充字符)函數(shù)用于右補(bǔ)全字符串。?
?? ??? ?SELECT rpad('abc', 10, '*') FROM dual;?? ??? ??? ?//輸出 ?abc*******
?? ??? ?SELECT rpad('abcdefg', 6, '*') FROM dual;?? ??? ?//輸出 ?abcdef?? ?截取字符串時(shí)，都是從左端開始截取!

?? ?length(列名|字符串)函數(shù)用于返回字符串的長度。
?? ??? ?SELECT length('abcde ') FROM dual;?? ??? ??? ?//輸出 ?6
?? ??? ?SELECT length('') FROM dual;?? ??? ??? ??? ?//輸出 ?null
?? ??? ?SELECT length(12.51) FROM dual;?? ??? ??? ?//輸出 ?5
?? ?
?? ?substr(列名|字符串，截取字符串開始位置，[截取長度])函數(shù)用于截取字符串。
?? ??? ?SELECT substr('1234567890', 5, 4) FROM dual;?? ??? ?//輸出 ?5678
?? ?
?? ?instr(父字符串, 子字符串, 起始位置, 匹配序號)函數(shù)用于獲得子字符串在父字符串中出現(xiàn)的位置。
?? ??? ?SELECT instr('big big tiger', 'big') FROM dual;?? ??? ?//輸出 ?1
?? ??? ?SELECT instr('big big tiger', 'big', 2) FROM dual; ?? ??? ?//輸出 ?3?? ? ? 以命令該函數(shù)從指定位置開始搜索。
?? ??? ?SELECT instr('big big tiger', 'big', 2, 2) FROM dual;?? ?//輸出 ?0 ? ? 指定是第幾次搜索到子字符串

?? ?trim()函數(shù)可用于刪除首尾空白符。
?? ?ltrim() 函數(shù)用于刪除字符串左端的空白符。 rtrim()函數(shù)用于刪除字符串右端空白符。
?? ??? ?SELECT trim(' ? abc ? ? ?') FROM dual;?? ??? ??? ?//輸出abc

數(shù)學(xué)函數(shù)
?? ?round(列名|數(shù)值，保留小數(shù)位)函數(shù)用于返回某個(gè)數(shù)字的四舍五入值。
?? ??? ?SELECT round(2745.173, 2) FROM dual; ?? ??? ?//輸出2745.17
?? ??? ?SELECT round(2745.173) FROM dual; ?? ??? ??? ?//輸出2745 ?? ?默認(rèn)精確到整數(shù)
?? ??? ?SELECT round(2745, -1) FROM dual; ?? ??? ??? ?//輸出2750?? ?負(fù)數(shù)將數(shù)值精確到小數(shù)點(diǎn)之前的位數(shù)。
?? ??? ?SELECT round(-15.53, 1), round(-15.55, 1) FROM dual;?? ?//輸出-15.5 ? ?-15.6

?? ?trunc(列名|數(shù)值)函數(shù)用于截取部分?jǐn)?shù)字。該函數(shù)不對數(shù)值做四舍五入處理，而是直接截取。
?? ??? ?SELECT trunc(2745.575, 2) FROM dual; ?? ??? ??? ?//輸出2745.57
?? ??? ?SELECT trunc(2745.575) FROM dual;?? ??? ??? ?//輸出2745
?? ??? ?當(dāng)保留位數(shù)小于0時(shí)，表示保留到小數(shù)點(diǎn)之前的位數(shù)。?? ?//輸出2740

?? ?mod(列1|數(shù)值1，列2|數(shù)值2)函數(shù)，第一個(gè)參數(shù)為被除數(shù)，第二個(gè)參數(shù)為除數(shù)。獲得兩數(shù)相除之后的余數(shù)。
?? ??? ?SELECT mod(5,2) FROM dual;?? ??? ??? ??? ?//輸出1

?? ?floor(列名|數(shù)值)函數(shù)用于返回小于等于某個(gè)數(shù)值的最大整數(shù)，向下取整。
?? ??? ?SELECT floor(21.897),floor(-21.897) FROM dual;?? ??? ?//輸出21?? ?-22
?? ?
?? ?ceil(|列名|數(shù)值)， 函數(shù)將參數(shù)向上取整，以獲得大于等于該參數(shù)的最小整數(shù)。
?? ??? ?SELECT ceil(21.897) , ceil(-21.897) FROM dual;?? ??? ?//輸出22?? ?-21

?? ?sign(列名|數(shù)值)函數(shù)返回?cái)?shù)字的正負(fù)性,若正，返回值為1; 若負(fù)，返回值為-1；若0，返回值為0
?? ??? ?SELECT sign(8), sign(-8) , sign(0) FROM dual; ?? ??? ?//輸出1,-1,0

日期函數(shù)
?? ?日期+數(shù)字n=日期（n天后的日期）
?? ?日期-數(shù)字n=日期（n天前的日期）
?? ?日期1-日期2=n（天）
?? ?
?? ??? ?eg1.查詢emp表獲得所有員工的雇用年數(shù)。
?? ??? ?SELECT ename,sysdate-hiredate FROM emp;

?? ?months_between(日期1，日期2)函數(shù)用于獲取兩個(gè)日期所間隔的月數(shù)。該函數(shù)的返回值是一個(gè)實(shí)數(shù)。
?? ??? ?eg2.查詢emp表獲得所有員工的雇用年數(shù)。
?? ??? ?SELECT empno,hiredate,trunc(months_between(sysdate,hiredate)/12) as 雇傭年限 FROM emp;
?? ?
?? ?add_months(日期，數(shù)字)函數(shù)將為日期添加特定月份，并獲得新的日期。
?? ??? ?SELECT to_char(add_months(sysdate, 20), 'yyyy-mm-dd') result FROM dual;?? ??? ?//返回2023-6-12

?? ?last_day(日期) 該函數(shù)獲得該月最后一天的日期。
?? ??? ?eg3.查詢emp表獲得雇用日期是月末倒數(shù)第二天的所有員工的信息。
?? ??? ??? ?SELECT empno,hiredate,last_day(hiredate) FROM emp WHERE hiredate = last_day(hiredate)-2;

?? ?to_char(日期|數(shù)字|列名，轉(zhuǎn)換格式)函數(shù)用于將其他數(shù)據(jù)類型的數(shù)據(jù)轉(zhuǎn)換為字符型。
?? ??? ?eg4.查詢出所有1987年雇用的員工姓名、職位和薪水。
?? ??? ??? ?SELECT ename,job,sal FROM emp WHERE to_char(hiredate,'yyyy') = '1987';
?? ?
?? ?to_date(字符串，轉(zhuǎn)換格式)函數(shù)用于將字符串轉(zhuǎn)換為日期。被轉(zhuǎn)換的字符串必須符合特定的日期格式。轉(zhuǎn)換格式為某種日期格式
?? ??? ?SELECT to_date('12/02/09', 'mm/dd/yy') result FROM dual;?? ??? ?// 返回02-12月-09

?? ?to_number(字符串)函數(shù)可以將字符串轉(zhuǎn)換為數(shù)值型。
?? ??? ?SELECT to_number('257.90') result FROM dual;?? ??? ??? ?//如果不是數(shù)字，ORACLE會報(bào)錯(cuò)

通用函數(shù)
?? ?nvl(列名，數(shù)值)函數(shù)用于處理某列的值。?? ?nvl(comm,0)用于處理空值，如果其值不為空，返回comm，如果為空，則返回第二個(gè)參數(shù)的值0。
?? ?eg5.查詢每個(gè)雇員的年薪（基本工資+傭金）
?? ??? ?SELECT ename,sal,comm,sal*12+nvl(comm,0) 年薪 FROM emp;

?? ?decode（列名，值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值）函數(shù)用于多值判斷。其執(zhí)行過程類似于解碼操作。
?? ?eg6.查詢每個(gè)雇員的工作崗位中文名稱
?? ??? ?SELECT ename, job, decode(job, 'CLERK','辦事員','SALESMAN','銷售員',
?? ??? ??? ?'MANAGER','經(jīng)理','ANALYST','分析員','主席') 中文崗位 FROM emp;

Oracle會根據(jù)操作符來自動(dòng)進(jìn)行數(shù)據(jù)類型的轉(zhuǎn)換(隱式轉(zhuǎn)換)
?? ?* 字符串到數(shù)值。
?? ?* 數(shù)值到字符串。
?? ?* 字符串到日期。
?? ?* 日期到字符串。
?? ?eg7. SELECT '123' + 200, '123' || 200 FROM dual;
?? ?eg8. SELECT months_between(sysdate , '01-1月-20') result1, '今天的日期是' || sysdate result2 FROM dual;

游標(biāo)
?? ?游標(biāo)是一種PL/SQL控制結(jié)構(gòu)，用來處理使用select語句從數(shù)據(jù)庫中檢索到的多行記錄的工具，可以對多行數(shù)據(jù)逐條進(jìn)行處理。游標(biāo)是一個(gè)返回?cái)?shù)據(jù)集的指針。
使用過程：1.聲明游標(biāo)?? ?2.打開游標(biāo)?? ?3.檢索數(shù)據(jù)?? ?4.關(guān)閉游標(biāo)

游標(biāo)定義語法：
?? ?CURSOR cursor_name [(parameter[, parameter]…)] [RETURN return_type] IS select statement;

游標(biāo)的屬性：
%FOUND ?? ??? ?檢驗(yàn)FETCH語句是否指向了記錄
%ISOPEN ?? ??? ?檢查游標(biāo)當(dāng)前是否處在打開狀態(tài)
%NOTFOUND ?? ??? ?%FOUND的相反屬性
%ROWCOUNT ?? ??? ?檢測任意給定的時(shí)刻，已從游標(biāo)中獲取的記錄行數(shù)
%BULK_EXCEPTIONS ?? ?為批操作或Bulk Collect操作中產(chǎn)生的異常提供相關(guān)信息
%BULK_ROWCOUNT ?? ?提供Bulk操作過程中更改的行數(shù)信息

顯式游標(biāo)的使用方法：
?? ?定義游標(biāo)
??? ??? ?語法：CURSOR cur_name IS ..
?? ?打開游標(biāo)
??? ??? ?語法：OPEN cur_name
?? ?取值游標(biāo)
?? ??? ?語法： FETCH cur_name INTO variable_list
??? ??? ??? ?variable_list必須與從游標(biāo)提取的結(jié)果集類型相同
?? ?關(guān)閉游標(biāo)
??? ??? ?語法：CLOSE cur_name
??? ??? ??? ?關(guān)閉游標(biāo)后，所有資源都將被釋放，且不能在此被打開。

eg9.使用游標(biāo)查詢每位雇員的編號和姓名
DECLARE
?? ?CURSOR curTest IS SELECT * FROM emp;
?? ?recEMP emp%ROWTYPE;
BEGIN
?? ?OPEN curTest;
?? ?FETCH curTest INTO recEMP;
?? ?WHILE curTest%FOUND LOOP
?? ??? ?DBMS_OUTPUT.PUT_LINE(recEmp.empno||'號員工的姓名是'||recEmp.ename);
?? ??? ?FETCH curTest INTO recEMP;
?? ?END LOOP;
?? ?CLOSE curTest;
END;
/

eg10.（帶參數(shù)） 使用游標(biāo)查詢某部門每位雇員姓名
DECLARE
?? ?CURSOR cur_with_Parammm(did number) IS SELECT * FROM emp WHERE deptno = did;
?? ?recemp emp%rowtype
BEGIN
?? ?open cur_with_Parammm(10);
?? ?FETCH cur_with_Parammm INTO recemp;
?? ?IF cur_with_Parammm%NOTFOUND = true THEN
?? ??? ?dbms_output.put_line('該部門不存在');
?? ?ELSE?
?? ??? ?LOOP
?? ??? ??? ?dbms_output.put_line(recemp.ename||'是部門的員工。');
?? ??? ??? ?FETCH cur_with_Parammm INTO recemp;
?? ??? ??? ?EXIT WHEN cur_with_Parammm%NOTFOUND;
?? ??? ?END LOOP;
?? ?dbms_output.put_line('部門共有員工'|| cur_with_Param%ROWCOUNT ||'名');
?? ?END IF;
?? ?CLOSE cur_with_Parammm;
END;
/

eg11.（帶參數(shù)） 使用循環(huán)游標(biāo)查詢每位雇員的姓名?? ??? ??? ?//使用for循環(huán)時(shí)，自動(dòng)打開游標(biāo)，而無需使用open語句；PL/SQL會自動(dòng)對變量進(jìn)行隱式聲明；當(dāng)循環(huán)結(jié)束后，游標(biāo)會自動(dòng)關(guān)閉。
DECLARE
?? ?CURSOR cur_with_param(did number) ISSELECT * from emp where deptno=did;
BEGIN
??? ?FOR recEmp IN cur_with_param(10) LOOP
??? ??? ?DBMS_OUTPUT.PUT_LINE(recEmp.ename||'是部門的員工。');
?? ?END LOOP;
END;
/

使用游標(biāo)更新表中數(shù)據(jù)
?? ?使用FOR UPDATE子句，對要修改或刪除的數(shù)據(jù)加行級鎖。
?? ?eg12.更新newemp表部門10的員工工資，使其工資增加100
?? ??? ?DEECLARE
?? ??? ??? ?CURSOR salcur(v_deptno number) IS?
?? ??? ??? ?SELECT sal FROM emp WHERE deptno = v_deptno ?FOR UPDATE;
?? ??? ?BEGIN
?? ??? ??? ?FOR vemp in salcur(10) LOOP
?? ??? ??? ??? ?UPDATE newemp SET sal = sal+100 WHERE CURRENT OF salcur;
?? ??? ??? ?END LOOP
?? ??? ?END;
?? ??? ?/
?? ?

游標(biāo)變量
顯示游標(biāo)用于命名一個(gè)工作區(qū)域，其中保存多行查詢的信息，而且該游標(biāo)始終指向工作區(qū)域的內(nèi)容。

創(chuàng)建游標(biāo)變量：
第一步：創(chuàng)建一個(gè)引用的游標(biāo)類型；
第二步：必須聲明一個(gè)具有引用游標(biāo)類型的游標(biāo)變量。
通用的語法如下：
?? ?TYPE cursortype_name is REF CURSOR [RETURN ?? ?return_type];
?? ?Cursorvarname cursortype_name;
打開游標(biāo)變量：
?? ?OPEN Cursorvarname FOR select statement;

eg13.查詢輸出emp表部門10的員工工資和dept表的所有信息
?? ?DECLARE
?? ??? ?TYPE v_cursortype is REF CURSOR;
?? ??? ?mycursort v_cursortype;
?? ??? ?recemp emp%rowtype;
?? ??? ?recdept dept%rowtype;
?? ?BEGIN
?? ??? ?OPEN mycursort FOR SELECT * FROM emp WHERE deptno = 10;
?? ??? ?FETCH mycursort INTO recemp;
?? ??? ?WHILE mycursort%FOUND LOOP
??? ??? ??? ?dbms_output.put_line('部門10的員工'||recemp.ename||'工資為: '|| recemp.sal);
??? ??? ??? ?FETCH mycursort INTO recemp;
??? ??? ?END LOOP;
??? ??? ?OPEN mycursort FOR SELECT * FROM dept;
??? ??? ?FETCH mycursort INTO recdept;
??? ??? ?WHILE mycursort%FOUND LOOP
?? ??? ??? ?dbms_output.put_line('部門'||recdept.deptno||'坐落于: '|| recdept.loc);
?? ??? ??? ?FETCH mycursort INTO recdept;
?? ??? ?END LOOP;
??? ??? ?CLOSE mycursort;
?? ?END;
?? ?/文章來源地址http://www.zghlxwxcb.cn/news/detail-480431.html

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