Quản trị stored procedure trong Oracle Database

Quản trị stored procedure trong Oracle Database liên quan đến việc tạo, quản lý và bảo trì các thủ tục lưu trữ (stored procedures). Stored procedure là các chương trình SQL được lưu trữ trong cơ sở dữ liệu và có thể được thực thi khi cần thiết. Stored procedure giúp tái sử dụng mã, cải thiện hiệu suất và quản lý giao dịch hiệu quả.

Tạo Stored Procedure

Stored procedure trong Oracle được tạo bằng lệnh CREATE PROCEDURE. Một stored procedure bao gồm một khối PL/SQL chứa các khai báo (declarations), câu lệnh thực thi (execution statements), và các xử lý ngoại lệ (exception handling).

Cú pháp

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter_name [IN | OUT | IN OUT] datatype [, …]) ]

IS

BEGIN

— Khối PL/SQL

— Khai báo biến và câu lệnh

EXCEPTION

— Khối xử lý ngoại lệ

END procedure_name;

Ví dụ

Dưới đây là ví dụ về việc tạo một stored procedure để thêm một bản ghi vào bảng employees:

CREATE OR REPLACE PROCEDURE add_employee (

p_employee_id IN employees.employee_id%TYPE,

p_first_name IN employees.first_name%TYPE,

p_last_name IN employees.last_name%TYPE,

p_email IN employees.email%TYPE,

p_hire_date IN employees.hire_date%TYPE,

p_job_id IN employees.job_id%TYPE

) IS

BEGIN

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)

VALUES (p_employee_id, p_first_name, p_last_name, p_email, p_hire_date, p_job_id);

EXCEPTION

WHEN OTHERS THEN

— Xử lý ngoại lệ

RAISE_APPLICATION_ERROR(-20001, ‘Error adding employee: ‘ || SQLERRM);

END add_employee;

Gọi Stored Procedure

Stored procedure có thể được gọi bằng lệnh EXEC hoặc thông qua một khối PL/SQL.

Gọi trực tiếp

EXEC add_employee(101, ‘John’, ‘Doe’, ‘john.doe@example.com’, SYSDATE, ‘IT_PROG’);

Gọi từ PL/SQL

BEGIN

add_employee(101, ‘John’, ‘Doe’, ‘john.doe@example.com’, SYSDATE, ‘IT_PROG’);

END;

Sửa đổi Stored Procedure

Để sửa đổi một stored procedure, bạn có thể sử dụng lệnh CREATE OR REPLACE PROCEDURE. Điều này sẽ thay thế stored procedure hiện tại mà không cần phải xóa và tạo lại.

Ví dụ

CREATE OR REPLACE PROCEDURE add_employee (

p_employee_id IN employees.employee_id%TYPE,

p_first_name IN employees.first_name%TYPE,

p_last_name IN employees.last_name%TYPE,

p_email IN employees.email%TYPE,

p_hire_date IN employees.hire_date%TYPE,

p_job_id IN employees.job_id%TYPE

) IS

BEGIN

— Thêm bản ghi vào bảng employees

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)

VALUES (p_employee_id, p_first_name, p_last_name, p_email, p_hire_date, p_job_id);

— Cập nhật bản ghi nếu đã tồn tại

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

UPDATE employees

SET first_name = p_first_name,

last_name = p_last_name,

email = p_email,

hire_date = p_hire_date,

job_id = p_job_id

WHERE employee_id = p_employee_id;

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20001, ‘Error adding employee: ‘ || SQLERRM);

END add_employee;

Xóa Stored Procedure

Để xóa một stored procedure, sử dụng lệnh DROP PROCEDURE.

Ví dụ

DROP PROCEDURE add_employee;

Quyền Truy Cập

Để tạo và sử dụng stored procedure, bạn cần các quyền thích hợp:

• CREATE PROCEDURE: Quyền tạo procedure.
• EXECUTE: Quyền thực thi procedure.

Cấp quyền

GRANT CREATE PROCEDURE TO user_name;

GRANT EXECUTE ON procedure_name TO user_name;

Kiểm tra và Debug Stored Procedure

Oracle cung cấp các công cụ và lệnh để kiểm tra và debug stored procedure:

• 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 PROCEDURE add_employee (

p_employee_id IN employees.employee_id%TYPE,

p_first_name IN employees.first_name%TYPE,

p_last_name IN employees.last_name%TYPE,

p_email IN employees.email%TYPE,

p_hire_date IN employees.hire_date%TYPE,

p_job_id IN employees.job_id%TYPE

) IS

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Adding employee: ‘ || p_first_name || ‘ ‘ || p_last_name);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)

VALUES (p_employee_id, p_first_name, p_last_name, p_email, p_hire_date, p_job_id);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLERRM);

RAISE_APPLICATION_ERROR(-20001, ‘Error adding employee: ‘ || SQLERRM);

END add_employee;

Stored procedure là một phần quan trọng của Oracle Database, giúp quản lý các tác vụ phức tạp một cách hiệu quả. Việc hiểu và sử dụng stored procedure đúng cách sẽ giúp bạn tối ưu hóa hiệu suất và bảo trì hệ thống cơ sở dữ liệu của mình.