# **PL/SQL**
## 🔷 Mục lục
- **[Tổng quan](#-tổng-quan)**
- **[Kiểm soát luồng thực thi trong PL/SQL](#-kiểm-soát-luồng-thực-thi-trong-plsql)**
- **[Package](#-package)**
- **[PL/SQL với Genexus](#-plsql-với-genexus)**
- **[Error Management](#-error-management)**
- **[Tài liệu tham khảo](#-tài-liệu-tham-khảo)**
## 🔷 Tổng quan
### Procedural Language/Structured Query Language (PL/SQL)
- **Procedural Language/Structured Query Language (PL/SQL)** là một ngôn ngữ cấu trúc theo dạng block do **Oracle** phát triển, nó cung cấp một tập hợp các lệnh thủ tục (câu lệnh IF, vòng lặp, phép gán), được tổ chức trong các block, có thể bổ sung và mở rộng phạm vi của SQL. PL/SQL được phát triển để nâng cao khả năng của SQL và cho phép xử lý lỗi nâng cao, tính toán phức tạp và kiểm soát lập trình đối với các thao tác dữ liệu
### Các tính năng chính của PL/SQL
- Một số tính năng quan trọng nhất của PL/SQL bao gồm:
- **Block Structure (cấu trúc khối)** PL/SQL có thể thực hiện nhiều truy vấn trong một block bằng một lệnh duy nhất
- **Procedural Contructs (cấu trúc thủ tục)** Có thể tạo một đơn vị PL/SQL như procedures, functions, triggers, package và types, được lưu trữ trong CSDL để các ứng dụng có thể tái sử dụng
- **Error Handling** PL/SQL cung cấp một tính năng cho phép xử lý các ngoại lệ trong các block được gọi là Exception handling block
- **Performance** Giảm lưu lượng mạng bằng cách thực thi nhiều câu lệnh SQL trong một block duy nhất
### Ưu và nhược điểm của PL/SQL
- **Ưu điểm**
- Cung cấp hiệu suất tốt hơn
- Có năng suất cao hơn
- Có hỗ trợ các khái niệm lập trình hướng đối tượng
- Có khả năng mở rộng và quản lý
- Hỗ trợ nhiều công cụ phát triển với nhiều ứng dụng web khác nhau
- **Nhược điểm**
- Phụ thuộc vào Oracle, không đa nền tảng
- Khó debug so với code ứng dụng
- Không dùng được trong MySQL, SQL Server,…
- Yêu cầu bộ nhớ cao
- Thiếu chức năng gỡ lỗi trong các procedures được lưu trữ
### Ví dụ cụ thể
- **Tổng hợp doanh thu hàng ngày**
```sql
CREATE OR REPLACE PROCEDURE GENERATEDAILYREVENUE IS
BEGIN
INSERT INTO DAILYREVENUE (
DATES,
TOTAL
)
SELECT
TRUNC(ORDERDATE),
SUM(AMOUNT)
FROM
ORDERS
WHERE
TRUNC(ORDERDATE) = TRUNC(SYSDATE)
GROUP BY
TRUNC(ORDERDATE);
END GENERATEDAILYREVENUE;
```
- **Không cho phép xóa dòng trong bảng Employees**
```sql
CREATE OR REPLACE TRIGGER TRG_NO_DELETE_EMPLOYEES BEFORE
DELETE ON EMPLOYEES
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Không được phép xóa nhân viên!');
END;
```
- **Ghi log khi cập nhật lương nhân viên**
```sql
CREATE OR REPLACE TRIGGER TRG_LOG_SALARY_UPDATE AFTER
UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
BEGIN
INSERT INTO SALARYLOG (
EMPID,
OLDSALARY,
NEWSALARY,
UPDATEDAT
) VALUES ( :OLD.EMPID,
:OLD.SALARY,
:NEW.SALARY,
SYSDATE );
END;
```
### Block Structure
- Đơn vị cơ bản trong PL/SQL là một block và tất cả chương trình PL/SQL đều được tạo thành từ các block, có thể lồng nhau. Một block được định nghĩa bằng các từ khoá `DECLARE`, `BEGIN`, `EXCEPTION` và `END`, chia block thành 3 phần:
```sql
DECLARE
declaration statements;
BEGIN
exectutable statements
EXCEPTIONS
exception handling statements
END;
```
```sql
DECLARE
L_MESSAGE VARCHAR2(100) := 'Hello world!';
BEGIN
DBMS_OUTPUT.PUT_LINE(L_MESSAGE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
```
- Phần khai báo bắt đầu bằng từ khoá `DECLARE`, trong đó các biến, hằng, bản ghi có thể được khai báo dưới dạng con trỏ để lưu trữ dữ liệu tạm thời. Về cơ bản, phần này bao gồm định nghĩa các định danh PL/SQL. Phần code này là tuỳ chọn
- Phần thực thi bắt đầu bằng `BEGIN` và kết thúc bằng từ khoá `END`. Đây là phần bắt buộc và logic chương trình được viết ở đây để thực hiện bất kì tác vụ nào như vòng lặp, câu lệnh điều kiện, ... Nó cũng hỗ trợ các lệnh Data Manipulation Language (ngôn ngữ thao tác dữ liệu - SELECT, INSERT, UPDATE, DELETE), lệnh Data Definition Language (ngôn ngữ định nghĩa dữ liệu - CREATE, ALTER, DROP, TRUNCATE), ...
- Phần ngoại lệ bắt đầu bằng từ khoá `EXCEPTION`, phần này là tuỳ chọn, chứa các câu lệnh được thực thi khi xảy ra lỗi runtime. Bất kì exception nào cũng có thể được xử lý trong phần này
### PL/SQL Identifiers (Định danh trong PL/SQL)
- **PL/SQL Variables**
```sql
var_name datatype := <value>
Ex: l_message VARCHAR2(100) := 'Hello world!'
```
- **PL/SQL Function**
```sql
CREATE OR REPLACE FUNCTION F_HELLO (
L_MESSAGE IN VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
RETURN 'Hello ' || L_MESSAGE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN NULL;
END F_HELLO;
/ -- Yêu cầu SQL*Plus thực thi khối trên
DECLARE
L_PRINT VARCHAR2(100);
BEGIN
L_PRINT := F_HELLO('Quan');
DBMS_OUTPUT.PUT_LINE(L_PRINT); -- Hello Quan
END;
```
- **PL/SQL Procedures**
```sql
CREATE OR REPLACE PROCEDURE P_HELLO (
L_MESSAGE IN VARCHAR2
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(F_HELLO(L_MESSAGE));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END P_HELLO;
/
DECLARE
L_PRINT VARCHAR2(100);
BEGIN
L_PRINT := 'Quan';
P_HELLO(L_PRINT); -- Hello Quan
END;
```
- **Các quy tắc để xây dựng định danh hợp lệ**
- Độ dài tối đa là 30 kí tự
- Kí tự đầu phải là một chữ cái, nhưng mỗi kí tự sau có thể là chữ cái, chữ số, dấu dola (`$`), dấu gạch dưới (`_`) hoặc dấu thăng (`#`). Ví dụ
```sql
-- Valid
hello$world
hello#
-- Invalid
1hello_world
hello%world
```
- Các định danh không phân biệt chữ hoa chữ thường
```sql
HELLO_WORLD = Hello_World = hello_world
```
- Ngooài ra, để tăng tính linh hoạt, OracleDB cho phép bỏ qua các hạn chế ở quy tắc 2 và 3 bằng cách đặt định danh trong dấu ngoặc kép. Quoted Identifier (định danh trích dẫn) có thể chứa bất kì chuỗi kí tự nào có thể in được ngoại trừ dấu ngoặc kép, sự khác biệt về chữ hoa và chữ thường cũng sẽ được giữ nguyên
```sql
"123"
"hello world"
```
Những quy tắc tương tự này áp dụng cho tên của các đối tượng DB như tables, views hoặc procedures và trừ khi dặt dấu ngoặc kép quanh tên của đối tượng DB đó, OracleDB sẽ lưu trữ chúng dưới dạng chữ in hoa
```sql
BEGIN
p_hello('Quan'); -- Hello Quan
P_HELLO('Quan'); -- Hello Quan
"P_HELLO"('Quan'); -- Hello Quan
"P_Hello"('Quan'); -- Error: identifier 'P_Hello' must be declared
END;
```
## 🔷 Kiểm soát luồng thực thi trong PL/SQL
### Condition Branching (phân nhánh có điều kiện)
- Khả năng điều hướng luồng thực thi trong chương trình dựa trên một điều kiện được thực hiện thông qua câu lệnh IF-THEN-ELSE và CASE
- Ngoài ra còn có các biểu thức CASE, mặc dù nó không giống như các câu lệnh CASE nhưng đôi khi có thể sử dụng để loại bỏ hoàn toàn nhu cầu sử dụng câu lệnh IF hoặc CASE
- Câu lệnh IF thường có 3 dạng
- **IF THEN END IF;** Đây là dạng cơ bản nhất của câu lệnh IF. Điều kiện giữa IF và THEN xác định liệu tập hợp các câu lệnh giữa THEN và END IF có được thực thi hay không. Nếu điều kiện đánh giá là FALSE hoặc NULL, code sẽ không được thực thi
```sql
DECLARE
L_A INTEGER := 5;
L_B INTEGER := 10;
BEGIN
IF L_A < L_B THEN
DBMS_OUTPUT.PUT_LINE('L_A less than L_B');
END IF;
END;
```
- **IF THEN ELSE END IF;** Tổ hợp này thực hiện either/or logic (thực hiện 1 trong 2). Dựa trên các điều kiện giữa các từ khoá IF THEN, thực thi code giữa THEN và ELSE hoặc là thực thi giữa ELSE và END IF. Một trong 2 phần câu lệnh này sẽ được thực thi
```sql
DECLARE
L_A INTEGER := 15;
L_B INTEGER := 10;
BEGIN
IF L_A < L_B THEN
DBMS_OUTPUT.PUT_LINE('L_A less than L_B');
ELSE
DBMS_OUTPUT.PUT_LINE('L_A greater than L_B');
END IF;
END;
```
- **IF THEN ELSIF THEN ELSE END IF;** Là dạng phức tạp nhất của câu lệnh IF, nó chọn một điều kiện TRUE từ một chuỗi các điều kiện loại trừ lẫn nhau, sau đó thực thi tập hợp các câu lệnh liên quan đến điều kiện đó. Đề xuất nên sử dùng các câu lệnh CASE để thay thế.
```sql
DECLARE
L_A INTEGER := 15;
L_B INTEGER := 15;
BEGIN
IF L_A < L_B THEN
DBMS_OUTPUT.PUT_LINE('L_A less than L_B');
ELSIF L_A > L_B THEN
DBMS_OUTPUT.PUT_LINE('L_A greater than L_B');
ELSE
DBMS_OUTPUT.PUT_LINE('L_A equals L_B');
END IF;
END;
```
- **CASE - Giải pháp thay thế hữu ích cho IF**
- **Simple CASE statement** Câu lệnh này liên kết từng chuỗi câu lệnh PL/SQL với một giá trị và chọn chuỗi câu lệnh nào được thực thi, dựa trên một biểu thức trả về một trong các giá trị đó
```sql
DECLARE
L_TASK_STATE VARCHAR(100);
BEGIN
L_TASK_STATE := &TASK_STATE;
CASE L_TASK_STATE
WHEN 'OPEN' THEN
DBMS_OUTPUT.PUT_LINE('Please choose the task!');
WHEN 'IN_PROGRESS' THEN
DBMS_OUTPUT.PUT_LINE('Please complete the task!');
WHEN 'IN_REVIEW' THEN
DBMS_OUTPUT.PUT_LINE('Check and fix comments if any!');
WHEN 'RESOLVED' THEN
DBMS_OUTPUT.PUT_LINE('Congratulations on completing the task!');
ELSE
DBMS_OUTPUT.PUT_LINE('Please select from options including OPEN, IN_PROGRESS, IN_REVIEW, RESOLVED options');
END CASE;
END;
```
- **Searched CASE statement** Câu lệnh này chọn một hoặc nhiều chuỗi các câu lệnh PL/SQL để thực thi bằng cách đánh giá một danh sách các điều kiện Boolean. Chuỗi các câu lệnh liên kết với điều kiện đầu tiên được đánh giá là TRUE sẽ được thực thi
```sql
DECLARE
L_SALARY VARCHAR2(100);
BEGIN
L_SALARY := &L_SALARY;
CASE
WHEN L_SALARY BETWEEN 10000 AND 20000 THEN
DBMS_OUTPUT.PUT_LINE('Bonus 10% = '
|| L_SALARY * 10 / 100
|| '$');
WHEN L_SALARY < 10000 THEN
DBMS_OUTPUT.PUT_LINE('Bonus 5% = '
|| L_SALARY * 5 / 100
|| '$');
ELSE
DBMS_OUTPUT.PUT_LINE('Bonus 15% = '
|| L_SALARY * 15 / 100
|| '$');
END CASE;
END;
```
Mệnh đề WHEN được đánh giá theo thứ tự nên có thể tối ưu hiệu năng bằng cách liệt kê các mệnh đề WHEN có khả năng xảy ra từ cao đến thấp. Ngoài ra, nếu các mệnh đề WHEN đòi hỏi nhiều chu kỳ CPU và bộ nhớ, thì có thể liệt kê chúng cuối cùng để giảm thiểu khả năng chúng được thực hiện
- Câu lệnh CASE thường sẽ có một mệnh đề ELSE, nhưng không bắt buộc. Khi không chỉ định rõ mệnh đề ELSE thì PL/SQL sẽ ngầm hiểu rằng
```sql
ELSE
RAISE CASE_NOT_FOUND; -- Error: ORA-06592: CASE not found while executing CASE statement
```
Nói cách khác, nếu không chỉ định mệnh đề ELSE và không có kết quả nào trong các mệnh đề WHEN khớp với kết quả của biểu thức CASE, PL/SQL sẽ báo lỗi là CASE_NOT_FOUND
- **Biểu thức CASE** Nó trả về một giá trị duy nhất, là kết quả của bất kì `result_expression` được chọn. Mỗi mệnh đề WHEN phải liên kết với đúng một biểu thức. Không sử dụng dấu `;` hoặc END CASE để đánh giấu kết thúc biểu thức CASE, mà chỉ đơn giản là sử dụng END.
```sql
DECLARE
L_SALARY INTEGER;
L_BONUS INTEGER;
BEGIN
L_SALARY := &L_SALARY;
L_BONUS :=
CASE
WHEN L_SALARY BETWEEN 10000 AND 20000 THEN
10
WHEN L_SALARY < 10000 THEN
5
ELSE 15
END;
DBMS_OUTPUT.PUT_LINE('Bonus '
|| L_BONUS
|| '% = '
|| L_SALARY * L_BONUS / 100
|| '$');
END;
```
Không giống câu lệnh CASE, không có lỗi nào được phát sinh trong trường hợp không có mệnh WHEN nào được chọn trong biểu thức CASE, thay vào đó, nó sẽ trả về NULL
### Xử lý lặp lại với vòng lặp
- PL/SQL cung cấp 3 loại cấu trúc vòng lặp:
- **The FOR loop (numeric and cursor)** OracleDB cung cấp cả vòng lặp FOR cho số và con trỏ.
```sql
-- Numeric FOR loop
DECLARE
L_COUNT INTEGER;
BEGIN
L_COUNT := &L_COUNT;
FOR I IN 1..L_COUNT LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;
```
Với Cursor FOR loop, nó có cùng cấu trúc cơ bản nhưng cung cấp một con trỏ rõ ràng hoặc câu lệnh SELECT thay cho phạm vi số nguyên
```sql
-- Cursor FOR loop
BEGIN
FOR I IN (
SELECT
*
FROM
HR.COUNTRIES
) LOOP
DBMS_OUTPUT.PUT_LINE(I.COUNTRY_NAME);
END LOOP;
END;
```
- **Simple (or infinite) loop** Nó bắt đầu đơn giản bằng từ khoá LOOP và kết thúc bằng câu lệnh END LOOP. Vòng lặp này sẽ kết thúc nếu thực thi một trong các lệnh EXIT, EXIT WHEN, hoặc RETURN trong thân vòng lặp (hoặc nếu ngoại lệ xảy ra)
```sql
-- Simple loop
DECLARE
L_COUNT INTEGER := 1;
BEGIN
LOOP
EXIT WHEN L_COUNT > 10;
DBMS_OUTPUT.PUT_LINE(L_COUNT);
L_COUNT := L_COUNT + 1;
END LOOP;
END;
```
```sql
-- Cursor simple loop
DECLARE
CURSOR COUNTRIES IS
SELECT
*
FROM
HR.COUNTRIES;
COUNTRY HR.COUNTRIES%ROWTYPE; -- set structure as a row of HR.COUNTRIES table
BEGIN
OPEN COUNTRIES; -- open cursor
LOOP
FETCH COUNTRIES INTO COUNTRY; -- get data line by line
EXIT WHEN COUNTRIES%NOTFOUND; -- return true if data cannot be fetched
DBMS_OUTPUT.PUT_LINE(COUNTRY.COUNTRY_NAME);
END LOOP;
CLOSE COUNTRIES; -- close cursor
END;
```
- **The WHILE loop** Nó cũng tương tự như Simple loop nhưng điểm khác biệt quan trọng ở đây là nó kiểm tra điều kiện dừng ngay từ đầu. WHILE loop bao gồm một điều kiện (biểu thức Boolean) và loop body. Trước mỗi lần lặp của loop body, OracleDB đánh giá điều kiện, nếu điều kiện được đánh giá là TRUE thì loop body sẽ được thực thi. Ngược lại, nếu điều kiện đánh giá là FALSE hoặc NULL, loop sẽ kết thúc
```sql
DECLARE
L_COUNT INTEGER := 1;
BEGIN
WHILE ( L_COUNT < 10 ) LOOP
DBMS_OUTPUT.PUT_LINE(L_COUNT);
L_COUNT := L_COUNT + 1;
END LOOP;
END;
```
## 🔷 Package
- **Package** là một cách để tổ chức và đóng gói các procedure, function, variable, trigger và các mục PL/SQL liên quan thành một đơn vị chương trình được đặt tên và lưu trữ trong database. Mỗi package bao gồm specification (đặt tả), định nghĩa các package item có thế được tham chiếu từ bên ngoài package và phần body, thực hiện các chương trình con (bao gồm procedure và function) bên trong package, nó có thể chứa các mục private (biến, kiểu dữ liệu,...) hoặc khởi tạo - thực hiện các thiết lập phức tạp và validation cho package
```sql
-- Specification package
CREATE OR REPLACE PACKAGE PKG_MATH AS
FUNCTION PROD (
A_IN IN INTEGER,
B_IN IN INTEGER
) RETURN INTEGER;
END PKG_MATH;
```
```sql
-- Package body
CREATE OR REPLACE PACKAGE BODY PKG_MATH AS
FUNCTION PROD (
A_IN IN INTEGER,
B_IN IN INTEGER
) RETURN INTEGER IS
BEGIN
IF A_IN = B_IN THEN
RETURN PROD(A_IN);
END IF;
RETURN A_IN * B_IN;
END;
END PKG_MATH;
```
```sql
-- Using package
DECLARE
L_A INTEGER;
L_B INTEGER;
BEGIN
L_A := &L_A;
L_B := &L_B;
DBMS_OUTPUT.PUT_LINE('Product of '
|| L_A
|| ' and '
|| L_B
|| ' is '
|| PKG_MATH.PROD(L_A, L_B));
END;
```
### Ưu điểm khi sử dụng package
- **Group related functionality (Nhóm các chức năng liên quan)** Một trong những lợi ích cơ bản là đóng gói các chức năng liên quan và cung cấp chức năng đó thông qua API, giúp dễ dàng phát triển và bảo trì mã nguồn
- **Ability to hide implementation detail (Khả năng ẩn chi tiết triển khai)** Có thể chọn những gì cần đưa vào specification để các developer xem và sử dụng, và những gì cần đưa vào phần body, phần này chỉ hiển thị với developer phát triển package đó. Với khả năng này, nó mang lại hai lợi ích quan trọng
- Thứ nhất, có thể thay đổi triển khai mà không cần thay đổi specification, điều này có nghĩa là tất cả các chương trình gọi procedure và function của packeage đó không cần thay đổi. Tính năng này giúp tăng cường tính linh hoạt của mã nguồn đồng thời hạn chế tác động đến ứng dụng
- Thứ hai, có thể ẩn chi tiết triển khai không muốn developer sử dụng package biết đến
- **Improved performance (Hiệu suất được cải thiện)** Với lần đầu gọi phần tử trong packeage, OracleDB sẽ tải toàn bộ package và bộ nhớ. Các tham chiếu tiếp theo đến các phần tử khác trong cùng một package không yêu cầu thêm các thao tác disk I/O (là quá trình đọc/ghi và nhập/xuất dữ liệu vào một ổ đĩa vật lý). Ngoài ra các package-level variable có thể được sử dụng làm session-level cache, cung cấp nhiều cách khác nhau để giảm thời gian truy cập dữ liệu
- **Minimized program unit recompilation (Giảm thiểu việc biên dịch lại đơn vị chương trình)** Các chương trình bên ngoài (không được định nghĩa trong package) chỉ có thể gọi các chương trình con được liệt kê trong specification. Nếu thay đổi và biên dịch lại package body, những chương trình bên ngoài đó sẽ không bị vô hiệu hoá. Việc giảm thiểu nhu cầu biên dịch lại mã là một yếu tố quan trọng trong việc quản lý logic block của ứng dụng lớn
### Package-level data
- **Package-level data** bao gồm các biến và hằng số được định nghĩa ở cấp độ package, nghĩa là nó sẽ không nằm trong một function hoặc một procedure cụ thể nào trong package
```sql
CREATE OR REPLACE PACKAGE PKG_MATH AS
PI CONSTANT NUMBER := 3.14159265359; -- declare π const
FUNCTION PROD (
A_IN IN INTEGER,
B_IN IN INTEGER
) RETURN INTEGER;
FUNCTION PROD (
NUM_IN IN INTEGER
) RETURN INTEGER;
END PKG_MATH;
```
- Khi khai báo một biến trong một chương trình con hoặc một block, nó là biến cục bộ. Phạm vi của nó bị giới hạn trong khối lệnh đó, và khi khối lệnh kết thúc, bộ nhớ cho biến đó sẽ được giải phóng. Tuy nhiên, với package-level data, phạm vi của nó là toàn bộ package. Trong kiến trúc PL/SQL runtime, cấu trúc package-level data tồn tại trong suốt phiên làm việc (thay vì trong suốt thời gian thực thi của một chương trình cụ thể), và chỉ biến mất khi phiên làm việc kết thúc (logout OracleDB, ngắt kết nối DB, ...)
- Package-level data bao gồm:
- **Private data** Khai báo bên trong package body, nó sẽ tồn tại trong session nhưng chỉ có thể được truy cập bởi các thành phần được định nghĩa trong chính package đó
```sql
CREATE OR REPLACE PACKAGE BODY PKG_MATH AS
PI CONSTANT NUMBER := 3.14159265359; -- declare π const in package body
...
END PKG_MATH;
BEGIN
DBMS_OUTPUT.PUT_LINE(PKG_MATH.PI); -- Error: PLS-00302: component 'PI' must be declared
END;
```
- **Public data** Khai báo bên trong specification package, nó sẽ tồn tại trong session và có thể truy cập trực tiếp (đọc - ghi) bởi bất kỳ chương trình nào có quyền EXCUTE trên package đó
```sql
CREATE OR REPLACE PACKAGE PKG_MATH AS
PI CONSTANT NUMBER := 3.14159265359; -- declare π const in specification body
...
END PKG_MATH;
BEGIN
DBMS_OUTPUT.PUT_LINE(PKG_MATH.PI); -- 3.14159265359
END;
```
### Subprogramming Overloading
```sql
-- Specification package
CREATE OR REPLACE PACKAGE PKG_MATH AS
FUNCTION PROD (
A_IN IN INTEGER,
B_IN IN INTEGER
) RETURN INTEGER;
/* Overloading */
FUNCTION PROD (
NUM_IN IN INTEGER
) RETURN INTEGER;
END;
```
```sql
-- Package body
CREATE OR REPLACE PACKAGE BODY PKG_MATH AS
FUNCTION PROD (
A_IN IN INTEGER,
B_IN IN INTEGER
) RETURN INTEGER IS
BEGIN
IF A_IN = B_IN THEN
RETURN PROD(A_IN);
END IF;
RETURN A_IN * B_IN;
END;
FUNCTION PROD (
NUM_IN IN INTEGER
) RETURN INTEGER IS
BEGIN
RETURN NUM_IN * NUM_IN;
END;
END;
```
```sql
-- Using package
DECLARE
L_A INTEGER;
L_B INTEGER;
BEGIN
L_A := &L_A;
L_B := &L_B;
DBMS_OUTPUT.PUT_LINE('Product of '
|| L_A
|| ' and '
|| L_B
|| ' is '
|| PKG_MATH.PROD(L_A, L_B));
DBMS_OUTPUT.PUT_LINE('Square of '
|| L_A
|| ' is '
|| PKG_MATH.PROD(L_A));
END;
```
## 🔷 PL/SQL với Genexus
### Thực thi PL/SQL trong Genexus
- Để có thể thực thi PL/SQL với Genexus, bao gồm các bước cơ bản sau
- Tạo file và viết code PL/SQL
- Import file PL/SQL vào GeneXus
- Tạo expternal object và sử dụng external object như bình thường
> **Lưu ý**
>
> - Stored Function sẽ không được GeneXus hỗ trợ vì GeneXus không hỗ trợ dạng return. Vì vậy, hãy convert kiểu Stored Function sang kiểu Stored Procedure có inout hoặc out parameters.
> - Đối số của Stored Procedure không được là null
## 🔷 Error Management
### Phân loại exception
- Có 3 loại exception trong PL/SQL, bao gồm
- **Internally Defined Exception** là exception được phát sinh nội bộ bởi một quy trình OracleDB, nó luôn có mã lỗi nhưng không có tên trừ khi được PL/SQL hoặc developer gán. Ví dụ như mã lỗi `ORA-00060: Deadlock detected while waiting for resource`
- **Predefined Exception** là Internally Defined Exception và được PL/SQL gán tên. Hầu hết các exception này đều được định nghĩa trong STANDARD Package (là package do OracleDB cung cấp, định nghĩa nhiều thành phần lập trình phổ biến của PL/SQL) và nằm trong số những exception thường gặp nhất. Ví dụ như mã lỗi `ORA-00001: Unique constraint violated` được gán tên DUP_VAL_ON_INDEX
- **User Defined Exception** là exception mà người dùng đã khai báo trong phần khai báo của chương trình, nó có thể được liên kết với một Internally Defined Exception (nghĩa là có thể đặt tên cho một exception chưa được đặt tên) hoặc với một lỗi cụ thể của ứng dụng
### Raising exception
- PL/SQL cung cấp 2 cơ chế để đưa ra ngoại lệ
- **RAISE Statement** Sử dụng để tạo exception do người dùng tự định nghĩa hoặc Predefined Exception
```sql
CREATE OR REPLACE PROCEDURE GET_EMPLOYEE (
EMPLOYEE_ID_IN IN PLS_INTEGER
) IS
BEGIN
IF EMPLOYEE_ID_IN IS NULL THEN
RAISE VALUE_ERROR;
END IF;
END;
/
BEGIN
GET_EMPLOYEE(&EMPLOYEE_ID_IN);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ID is not null!');
END;
```
## 🔷 Tài liệu tham khảo
- [Live SQL - PL/SQL online](https://livesql.oracle.com/next/)
- [PL/SQL for Developers](https://www.oracle.com/database/technologies/appdev/plsql.html)
Sign in with Wallet
Connect another wallet