Quản trị functions trong Oracle Database

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.