การใช้ฟังก์ชันแถวเดียว
การใช้ฟังก์ชันแถวเดียวเพื่อปรับแต่งเอาต์พุต
Oracle SQL จัดเตรียมไลบรารีของฟังก์ชันที่สร้างขึ้นซึ่งสามารถใช้สำหรับงานต่างๆ ความสามารถที่สำคัญของฟังก์ชันอาจเป็นการแปลงสตริงการดำเนินการในสตริงหรือสตริงย่อยการคำนวณทางคณิตศาสตร์กับข้อมูลตัวเลขและการดำเนินการวันที่กับค่าประเภทวันที่ ฟังก์ชัน SQL เป็นทางเลือกที่จะรับอาร์กิวเมนต์จากผู้ใช้และส่งคืนค่าอย่างมีนัยสำคัญ
ในหมวดหมู่ที่กว้างขึ้นมีฟังก์ชันสองประเภท: -
Single Row functions- ฟังก์ชันแถวเดียวคือฟังก์ชันที่ทำงานในแถวเดียวและส่งคืนหนึ่งเอาต์พุตต่อแถว ตัวอย่างเช่นฟังก์ชันการแปลงความยาวและกรณีเป็นฟังก์ชันแถวเดียว
Multiple Row functions - ฟังก์ชันหลายแถวทำงานตามกลุ่มของแถวและส่งกลับผลลัพธ์เดียวสำหรับชุดแถวที่สมบูรณ์ เรียกอีกอย่างว่าฟังก์ชันกลุ่ม
ฟังก์ชันแถวเดียว
ฟังก์ชันแถวเดียวอาจเป็นฟังก์ชันอักขระฟังก์ชันตัวเลขฟังก์ชันวันที่และฟังก์ชันการแปลง โปรดทราบว่าฟังก์ชันเหล่านี้ใช้เพื่อจัดการกับรายการข้อมูล ฟังก์ชันเหล่านี้ต้องการอาร์กิวเมนต์อินพุตตั้งแต่หนึ่งรายการขึ้นไปและทำงานในแต่ละแถวดังนั้นจึงส่งคืนค่าเอาต์พุตหนึ่งค่าสำหรับแต่ละแถว อาร์กิวเมนต์สามารถเป็นคอลัมน์ลิเทอรัลหรือนิพจน์ ฟังก์ชันแถวเดียวสามารถใช้ได้ในคำสั่ง SELECT WHERE และ ORDER BY clause ฟังก์ชันแถวเดียวสามารถ -
General functions- มักจะมีฟังก์ชันการจัดการ NULL ฟังก์ชันในหมวดนี้ ได้แก่ NVL, NVL2, NULLIF, COALESCE, CASE, DECODE
Case Conversion functions- ยอมรับการป้อนอักขระและส่งคืนค่าอักขระ ฟังก์ชั่นภายใต้หมวดหมู่ ได้แก่ UPPER, LOWER และ INITCAP
ฟังก์ชัน UPPER แปลงสตริงเป็นตัวพิมพ์ใหญ่
ฟังก์ชัน LOWER แปลงสตริงเป็นตัวพิมพ์เล็ก
ฟังก์ชัน INITCAP จะแปลงเฉพาะตัวอักษรเริ่มต้นของสตริงเป็นตัวพิมพ์ใหญ่
Character functions- ยอมรับการป้อนอักขระและส่งกลับค่าตัวเลขหรืออักขระ ฟังก์ชันภายใต้หมวดหมู่ ได้แก่ CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM และ REPLACE
ฟังก์ชัน CONCAT เชื่อมค่าสตริงสองค่าเข้าด้วยกัน
ฟังก์ชัน LENGTH ส่งกลับความยาวของสตริงอินพุต
ฟังก์ชัน SUBSTR ส่งคืนส่วนของสตริงจากจุดเริ่มต้นที่กำหนดไปยังจุดสิ้นสุด
ฟังก์ชัน INSTR ส่งคืนตำแหน่งตัวเลขของอักขระหรือสตริงในสตริงที่กำหนด
ฟังก์ชัน LPAD และ RPAD จะเพิ่มสตริงที่กำหนดให้มีความยาวที่กำหนดด้วยอักขระที่กำหนด
ฟังก์ชัน TRIM จะจดจ้องอินพุตสตริงจากจุดเริ่มต้นหรือจุดสิ้นสุด
ฟังก์ชัน REPLACE แทนที่อักขระจากสตริงอินพุตด้วยอักขระที่กำหนด
Date functions- วันที่การดำเนินการทางคณิตศาสตร์ส่งคืนวันที่หรือค่าตัวเลข ฟังก์ชันในหมวดหมู่นี้ ได้แก่ MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND และ TRUNC
ฟังก์ชัน MONTHS_BETWEEN จะส่งกลับจำนวนเดือนระหว่างวันที่สองวัน
ฟังก์ชัน ADD_MONTHS เพิ่มจำนวนเดือน 'n' ในวันที่ป้อนข้อมูล
ฟังก์ชัน NEXT_DAY ส่งคืนวันถัดไปของวันที่ที่ระบุ
ฟังก์ชัน LAST_DAY ส่งคืนวันสุดท้ายของเดือนของวันที่ป้อนข้อมูล
ฟังก์ชัน ROUND และ TRUNC ใช้ในการปัดเศษและตัดทอนค่าวันที่
Number functions- ยอมรับการป้อนข้อมูลตัวเลขและส่งกลับค่าตัวเลข ฟังก์ชั่นในหมวดหมู่นี้ ได้แก่ ROUND, TRUNC และ MOD
ฟังก์ชัน ROUND และ TRUNC ใช้เพื่อปัดเศษและตัดทอนค่าตัวเลข
MOD ใช้เพื่อส่งกลับส่วนที่เหลือของการดำเนินการหารระหว่างตัวเลขสองตัว
ภาพประกอบ
ฟังก์ชั่นทั่วไป
แบบสอบถาม SELECT ด้านล่างแสดงให้เห็นถึงการใช้ฟังก์ชัน NVL
SELECT first_name, last_name, salary, NVL (commission_pct,0)
FROM employees
WHERE rownum < 5;
FIRST_NAME LAST_NAME SALARY NVL(COMMISSION_PCT,0)
-------------------- ------------------------- ---------- ---------------------
Steven King 24000 0
Neena Kochhar 17000 0
Lex De Haan 17000 0
Alexander Hunold 9000 0ฟังก์ชันการแปลงกรณี
แบบสอบถาม SELECT ด้านล่างแสดงให้เห็นถึงการใช้ฟังก์ชันการแปลงกรณี
SELECT UPPER (first_name), INITCAP (last_name), LOWER (job_id)
FROM employees
WHERE rownum < 5;
UPPER(FIRST_NAME) INITCAP(LAST_NAME) LOWER(JOB_
-------------------- ------------------------- ----------
STEVEN King ad_pres
NEENA Kochhar ad_vp
LEX De Haan ad_vp
ALEXANDER Hunold it_progฟังก์ชั่นตัวละคร
แบบสอบถาม SELECT ด้านล่างแสดงให้เห็นถึงการใช้ฟังก์ชัน CONCAT เพื่อเชื่อมค่าสตริงสองค่าเข้าด้วยกัน
SELECT CONCAT (first_name, last_name)
FROM employees
WHERE rownum < 5;
CONCAT(FIRST_NAME,LAST_NAME)
--------------------------------
EllenAbel
SundarAnde
MozheAtkinson
DavidAustinแบบสอบถาม SELECT ด้านล่างแสดงให้เห็นถึงการใช้ฟังก์ชัน SUBSTR และ INSTR ฟังก์ชัน SUBSTR ส่งคืนส่วนของสตริงอินพุตจากตำแหน่งที่ 1 ไปยังตำแหน่งที่ 5 ฟังก์ชัน INSTR ส่งคืนตำแหน่งตัวเลขของอักขระ 'a' ในชื่อแรก
SELECT SUBSTR (first_name,1,5), INSTR (first_name,'a')
FROM employees
WHERE rownum < 5;
SUBST INSTR(FIRST_NAME,'A')
----- ---------------------
Ellen 0
Sunda 5
Mozhe 0
David 2แบบสอบถาม SELECT ด้านล่างแสดงให้เห็นถึงการใช้ LPAD และ RPAD เพื่อพิมพ์ข้อมูลพนักงานและงานได้อย่างสวยงาม
SELECT RPAD(first_name,10,'_')||LPAD (job_id,15,'_')
FROM employees
WHERE rownum < 5;
RPAD(FIRST_NAME,10,'_')||
-------------------------
Steven____________AD_PRES
Neena_______________AD_VP
Lex_________________AD_VP
Alexander_________IT_PROGฟังก์ชันตัวเลข
แบบสอบถาม SELECT ด้านล่างแสดงให้เห็นถึงการใช้ฟังก์ชัน ROUND และ TRUNC
SELECT ROUND (1372.472,1)
FROM dual;
ROUND(1372.472,1)
-----------------
1372.5
SELECT TRUNC (72183,-2)
FROM dual;
TRUNC(72183,-2)
---------------
72100วันที่การดำเนินการเลขคณิต
แบบสอบถาม SELECT ด้านล่างแสดงฟังก์ชันเลขคณิตวันที่ซึ่งความแตกต่างของวันที่จ้างพนักงานและ sysdate เสร็จสิ้น
SELECT employee_id, (sysdate - hire_date) Employment_days
FROM employees
WHERE rownum < 5;
EMPLOYEE_ID EMPLOYMENT_DAYS
----------- ---------------
100 3698.61877
101 2871.61877
102 4583.61877
103 2767.61877ฟังก์ชันวันที่
คำค้นหา SELECT ด้านล่างแสดงให้เห็นถึงการใช้ฟังก์ชัน MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY และ LAST_DAY
SELECT employee_id, MONTHS_BETWEEN (sysdate, hire_date) Employment_months
FROM employees
WHERE rownum < 5;
EMPLOYEE_ID EMPLOYMENT_MONTHS
----------- -----------------
100 121.504216
101 94.3751837
102 150.633248
103 90.9558289
SELECT ADD_MONTHS (sysdate, 5), NEXT_DAY (sysdate), LAST_DAY (sysdate)
FROM dual;
ADD_MONTH NEXT_DAY( LAST_DAY(
--------- --------- ---------
01-JAN-14 05-AUG-13 31-AUG-13