Quản trị hàm (functions) trong Oracle Database bao gồm việc tạo, sử dụng và quản lý các hàm PL/SQL. Hàm (function) trong Oracle là một khối mã PL/SQL có thể thực hiện các tính toán, thao tác dữ liệu và trả về một giá trị duy nhất. Các hàm giúp tái sử dụng mã, cải thiện hiệu suất và đảm bảo tính nhất quán trong các ứng dụng cơ sở dữ liệu.
Tạo Function
Hàm trong Oracle được tạo bằng lệnh CREATE FUNCTION. Một hàm bao gồm phần khai báo (declarations), phần thân (body) với các câu lệnh thực thi, và phần xử lý ngoại lệ (exception handling).
Cú pháp
CREATE [OR REPLACE] FUNCTION function_name (parameter_name [IN | OUT | IN OUT] datatype [, ...]) RETURN return_datatype IS | AS -- Khai báo biến (declarations) BEGIN -- Câu lệnh thực thi (execution statements) RETURN return_value; EXCEPTION -- Xử lý ngoại lệ (exception handling) END function_name;
Ví dụ
Dưới đây là ví dụ về việc tạo một hàm để tính toán tổng số lượng đơn hàng của một khách hàng dựa trên customer_id:
CREATE OR REPLACE FUNCTION get_total_orders (
p_customer_id IN orders.customer_id%TYPE
) RETURN NUMBER
IS
v_total_orders NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO v_total_orders
FROM orders
WHERE customer_id = p_customer_id;
RETURN v_total_orders;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, ‘Error calculating total orders: ‘ || SQLERRM);
END get_total_orders;
Gọi Function
Hàm có thể được gọi từ các câu lệnh SQL, PL/SQL hoặc từ các ứng dụng khách.
Gọi từ PL/SQL
DECLARE
v_total NUMBER;
BEGIN
v_total := get_total_orders(101);
DBMS_OUTPUT.PUT_LINE(‘Total orders: ‘ || v_total);
END;
Gọi từ SQL
SELECT get_total_orders(101) FROM dual;
Sửa đổi Function
Để sửa đổi một hàm, bạn có thể sử dụng lệnh CREATE OR REPLACE FUNCTION, cho phép bạn thay thế hàm hiện tại mà không cần phải xóa và tạo lại.
Ví dụ
CREATE OR REPLACE FUNCTION get_total_orders (
p_customer_id IN orders.customer_id%TYPE
) RETURN NUMBER
IS
v_total_orders NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO v_total_orders
FROM orders
WHERE customer_id = p_customer_id;
RETURN v_total_orders;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, ‘Error calculating total orders: ‘ || SQLERRM);
END get_total_orders;
Xóa Function
Để xóa một hàm, sử dụng lệnh DROP FUNCTION.
Ví dụ
DROP FUNCTION get_total_orders;
Quyền Truy Cập
Để tạo và sử dụng hàm, bạn cần các quyền thích hợp:
- CREATE FUNCTION: Quyền tạo hàm.
- EXECUTE: Quyền thực thi hàm.
Cấp quyền
GRANT CREATE FUNCTION TO user_name;
GRANT EXECUTE ON function_name TO user_name;
Kiểm tra và Debug Function
Oracle cung cấp các công cụ và lệnh để kiểm tra và debug hàm:
- DBMS_OUTPUT.PUT_LINE: In thông báo để kiểm tra.
- EXCEPTION: Xử lý và debug ngoại lệ.
Ví dụ sử dụng DBMS_OUTPUT.PUT_LINE
CREATE OR REPLACE FUNCTION get_total_orders (
p_customer_id IN orders.customer_id%TYPE
) RETURN NUMBER
IS
v_total_orders NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Calculating total orders for customer: ‘ || p_customer_id);
SELECT COUNT(*)
INTO v_total_orders
FROM orders
WHERE customer_id = p_customer_id;
DBMS_OUTPUT.PUT_LINE(‘Total orders: ‘ || v_total_orders);
RETURN v_total_orders;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLERRM);
RAISE_APPLICATION_ERROR(-20001, ‘Error calculating total orders: ‘ || SQLERRM);
END get_total_orders;
Sử dụng Function trong Các Câu Lệnh SQL và PL/SQL
Hàm có thể được sử dụng trong các câu lệnh SELECT, WHERE, và các câu lệnh SQL khác, cũng như trong các khối PL/SQL và trigger.
Ví dụ sử dụng trong câu lệnh SQL
SELECT customer_id, get_total_orders(customer_id) AS total_orders
FROM customers;
Ví dụ sử dụng trong trigger
CREATE OR REPLACE TRIGGER update_order_count
AFTER INSERT OR DELETE ON orders
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE customers
SET order_count = get_total_orders(:NEW.customer_id)
WHERE customer_id = :NEW.customer_id;
ELSIF DELETING THEN
UPDATE customers
SET order_count = get_total_orders(:OLD.customer_id)
WHERE customer_id = :OLD.customer_id;
END IF;
END;
Function là một phần quan trọng trong Oracle Database, giúp tăng cường khả năng tái sử dụng mã, tối ưu hóa hiệu suất và đảm bảo tính nhất quán trong các ứng dụng cơ sở dữ liệu. Việc hiểu và sử dụng function đúng cách sẽ giúp bạn quản lý và phát triển hệ thống cơ sở dữ liệu hiệu quả hơn.
