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 0Chứ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_progCá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
DavidAustinTruy 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 2Truy 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_PROGHà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)
---------------
72100Cá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.61877Hà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