การใช้ฟังก์ชันแถวเดียว

การใช้ฟังก์ชันแถวเดียวเพื่อปรับแต่งเอาต์พุต

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