Sử dụng các hàm một hàng

Sử dụng các chức năng hàng đơn để tùy chỉnh đầu ra

Oracle SQL cung cấp một thư viện phong phú các chức năng tích hợp sẵn có thể được sử dụng cho các tác vụ khác nhau. Các khả năng thiết yếu của một hàm có thể là chuyển đổi trường hợp của chuỗi, phép toán trong chuỗi hoặc chuỗi con, phép tính toán học trên dữ liệu số và phép toán ngày trên giá trị kiểu ngày. Các hàm SQL tùy ý nhận các đối số từ người dùng và bắt buộc trả về một giá trị.

Ở phạm vi rộng hơn, có hai loại chức năng: -

Single Row functions- Hàm hàng đơn là hàm làm việc trên một hàng và trả về một đầu ra cho mỗi hàng. Ví dụ, các hàm chuyển đổi độ dài và trường hợp là các hàm hàng đơn.

Multiple Row functions - Chức năng nhiều hàng hoạt động dựa trên nhóm hàng và trả về một kết quả cho toàn bộ các hàng. Chúng còn được gọi là Hàm nhóm.

Các chức năng hàng đơn

Các hàm hàng đơn có thể là hàm ký tự, hàm số, hàm ngày tháng và hàm chuyển đổi. Lưu ý rằng các hàm này được sử dụng để thao tác các mục dữ liệu. Các hàm này yêu cầu một hoặc nhiều đối số đầu vào và hoạt động trên mỗi hàng, do đó trả về một giá trị đầu ra cho mỗi hàng. Đối số có thể là một cột, chữ hoặc một biểu thức. Các hàm hàng đơn có thể được sử dụng trong câu lệnh SELECT, mệnh đề WHERE và ORDER BY. Các chức năng hàng đơn có thể là -

  • General functions- Thường chứa các hàm xử lý NULL. Các chức năng trong danh mục là NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.

  • Case Conversion functions- Chấp nhận nhập ký tự và trả về giá trị ký tự. Các chức năng trong danh mục là UPPER, LOWER và INITCAP.

    • Hàm UPPER chuyển đổi một chuỗi thành chữ hoa.

    • Hàm LOWER chuyển đổi một chuỗi thành chữ thường.

    • Hàm INITCAP chỉ chuyển đổi các bảng chữ cái đầu tiên của một chuỗi thành chữ hoa.

  • Character functions- Chấp nhận nhập ký tự và trả về giá trị số hoặc ký tự. Các chức năng trong danh mục là CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM và REPLACE.

    • Hàm CONCAT nối hai giá trị chuỗi.

    • Hàm LENGTH trả về độ dài của chuỗi đầu vào.

    • Hàm SUBSTR trả về một phần của chuỗi từ điểm bắt đầu cho trước đến điểm kết thúc.

    • Hàm INSTR trả về vị trí số của một ký tự hoặc một chuỗi trong một chuỗi nhất định.

    • Các hàm LPAD và RPAD đệm chuỗi đã cho lên đến một độ dài cụ thể với một ký tự nhất định.

    • Hàm TRIM cắt đầu vào chuỗi từ đầu hoặc cuối.

    • Hàm REPLACE thay thế các ký tự từ chuỗi đầu vào bằng một ký tự nhất định.

  • Date functions- Các phép toán số học ngày trả về ngày tháng hoặc các giá trị số. Các chức năng trong danh mục là MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND và TRUNC.

    • Hàm MONTHS_BETWEEN trả về số tháng giữa hai ngày.

    • Hàm ADD_MONTHS thêm 'n' số tháng vào ngày nhập.

    • Hàm NEXT_DAY trả về ngày tiếp theo của ngày được chỉ định.

    • Hàm LAST_DAY trả về ngày cuối cùng của tháng của ngày nhập.

    • Các hàm ROUND và TRUNC được sử dụng để làm tròn và cắt bớt giá trị ngày tháng.

  • Number functions- Chấp nhận đầu vào số và trả về giá trị số. Các chức năng trong danh mục là ROUND, TRUNC và MOD.

    • Hàm ROUND và TRUNC được sử dụng để làm tròn và cắt bớt giá trị số.

    • MOD được sử dụng để trả về phần còn lại của phép chia giữa hai số.

Hình minh họa

Chức năng chung

Truy vấn SELECT bên dưới thể hiện việc sử dụng hàm 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

Chức năng chuyển đổi trường hợp

Truy vấn SELECT bên dưới thể hiện việc sử dụng các hàm chuyển đổi trường hợp.

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

Các hàm ký tự

Truy vấn SELECT bên dưới thể hiện việc sử dụng hàm CONCAT để nối hai giá trị chuỗi.

SELECT CONCAT (first_name, last_name) 
FROM employees
WHERE rownum < 5;

CONCAT(FIRST_NAME,LAST_NAME)
--------------------------------
EllenAbel
SundarAnde
MozheAtkinson
DavidAustin

Truy vấn SELECT bên dưới thể hiện việc sử dụng các hàm SUBSTR và INSTR. Hàm SUBSTR trả về một phần của chuỗi đầu vào từ vị trí thứ nhất đến vị trí thứ năm. Hàm INSTR trả về vị trí số của ký tự 'a' trong tên.

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

Truy vấn SELECT bên dưới minh họa việc sử dụng LPAD và RPAD để in đẹp thông tin nhân viên và công việc.

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

Hàm số

Truy vấn SELECT bên dưới thể hiện việc sử dụng các hàm ROUND và TRUNC.

SELECT ROUND (1372.472,1)
FROM dual;

ROUND(1372.472,1)
-----------------
           1372.5

SELECT TRUNC (72183,-2)
FROM dual;

TRUNC(72183,-2)
---------------
          72100

Các phép toán số học ngày tháng

Truy vấn SELECT bên dưới hiển thị một hàm số học ngày, nơi thực hiện sự khác biệt giữa ngày thuê nhân viên và ngày cung cấp.

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

Hàm ngày tháng

Truy vấn SELECT bên dưới minh họa việc sử dụng các hàm MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY và 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