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.
