Oracle Thủ tục và hàm lưu trữ PL/SQL kèm ví dụ

Chương trình con PL/SQL là gì?

Trong hướng dẫn này, bạn sẽ thấy mô tả chi tiết về cách tạo và thực thi các khối, thủ tục và hàm được đặt tên.

Thủ tục và hàm là các chương trình con có thể được tạo và lưu trong cơ sở dữ liệu dưới dạng các đối tượng cơ sở dữ liệu. Chúng cũng có thể được gọi hoặc tham chiếu bên trong các khối khác.

Chúng tôi cũng đề cập đến những điểm khác biệt chính giữa hai chương trình con này và thảo luận về... Oracle Chức năng tích hợp sẵn.

Thuật ngữ trong chương trình con PL/SQL

Trước khi tìm hiểu về các chương trình con PL/SQL, chúng ta sẽ thảo luận về các thuật ngữ khác nhau liên quan đến các chương trình con này.

Tham số

Tham số là một biến hoặc chỗ giữ chỗ của bất kỳ giá trị hợp lệ nào. Kiểu dữ liệu PL/SQL Thông qua đó chương trình con PL/SQL trao đổi giá trị với mã chính. Tham số này cho phép nhập liệu vào các chương trình con và xuất liệu.tracviệc chuyển giao các giá trị từ chúng.

• Các tham số này phải được xác định cùng với các chương trình con tại thời điểm tạo.
• Chúng được đưa vào câu lệnh gọi để tương tác với các chương trình con.
• Kiểu dữ liệu của tham số trong chương trình con và câu lệnh gọi phải giống nhau.
• Không nên đề cập đến kích thước của kiểu dữ liệu khi khai báo tham số, vì kích thước này là động.

Dựa trên mục đích sử dụng, các tham số được phân loại như sau:

1. TRONG Tham số
2. RA Tham Số
3. Thông số IN OUT

TRONG Tham số

• Được sử dụng để cung cấp dữ liệu đầu vào cho các chương trình con.
• Đây là biến chỉ đọc bên trong các chương trình con; giá trị của nó không thể thay đổi bên trong chương trình con.
• Trong câu lệnh gọi, nó có thể là một biến, một giá trị cố định hoặc một biểu thức, chẳng hạn như '5*8' hoặc 'a/b'.
• Theo mặc định, các tham số có kiểu dữ liệu IN.

RA Tham Số

• Được sử dụng để lấy kết quả đầu ra từ các chương trình con.
• Đây là một biến có thể đọc và ghi bên trong các chương trình con; giá trị của nó có thể được thay đổi bên trong các chương trình con đó.
• Trong câu lệnh gọi, nó luôn phải là một biến để lưu trữ giá trị từ chương trình con.

Thông số IN OUT

• Được sử dụng để cả cung cấp dữ liệu đầu vào và nhận dữ liệu đầu ra từ các chương trình con.
• Đây là một biến có thể đọc và ghi bên trong các chương trình con; giá trị của nó có thể được thay đổi bên trong các chương trình con đó.
• Trong câu lệnh gọi, nó luôn phải là một biến để lưu trữ giá trị từ chương trình con.

Kiểu tham số cần được chỉ định khi tạo các chương trình con.

TRỞ VỀ

Từ khóa RETURN hướng dẫn trình biên dịch chuyển quyền điều khiển từ chương trình con sang câu lệnh gọi. Trong chương trình con, RETURN đơn giản có nghĩa là quyền điều khiển cần thoát khỏi chương trình con; khi bộ điều khiển tìm thấy RETURN, đoạn mã sau đó sẽ bị bỏ qua.

Thông thường, khối chính (khối cha) sẽ gọi các chương trình con, và quyền điều khiển sẽ chuyển từ khối cha sang chương trình con được gọi. Lệnh RETURN trong chương trình con sẽ trả lại quyền điều khiển cho khối cha. Trong trường hợp hàm, lệnh RETURN cũng trả về một giá trị, kiểu dữ liệu của giá trị đó được chỉ định tại thời điểm khai báo hàm.

Thủ tục trong PL/SQL là gì?

A Thủ tục Trong PL/SQL, thủ tục là một đơn vị chương trình con bao gồm một nhóm các câu lệnh PL/SQL có thể được gọi bằng tên. Mỗi thủ tục có tên riêng biệt và được lưu trữ trong... Oracle cơ sở dữ liệu như một đối tượng cơ sở dữ liệu.

Lưu ý: Chương trình con thực chất là một thủ tục, và nó cần được tạo thủ công theo yêu cầu. Sau khi được tạo, nó được lưu trữ dưới dạng đối tượng trong cơ sở dữ liệu.

Các đặc điểm của một đơn vị chương trình con thủ tục trong PL/SQL là:

• Các thủ tục là các khối độc lập có thể được lưu trữ trong... cơ sở dữ liệu.
• Chúng có thể được gọi bằng tên của chúng để thực thi các câu lệnh PL/SQL.
• Chúng chủ yếu được sử dụng để thực thi một quy trình.
• Chúng có thể chứa các khối lồng nhau, hoặc được lồng bên trong các khối hoặc gói khác.
• Chúng bao gồm phần khai báo (tùy chọn), phần thực thi và phần xử lý ngoại lệ (tùy chọn).
• Các giá trị có thể được truyền vào hoặc lấy ra từ một thủ tục thông qua các tham số.
• Các tham số này nên được bao gồm trong câu lệnh gọi.
• Một thủ tục có thể có câu lệnh RETURN để trả lại quyền điều khiển cho khối gọi, nhưng nó không thể trả về bất kỳ giá trị nào thông qua RETURN.
• Các thủ tục không thể được gọi trực tiếp từ câu lệnh SELECT; chúng có thể được gọi từ một khối lệnh khác hoặc thông qua từ khóa EXEC.

cú pháp

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• Lệnh CREATE PROCEDURE hướng dẫn trình biên dịch tạo một thủ tục mới. Từ khóa 'OR REPLACE' hướng dẫn trình biên dịch thay thế thủ tục hiện có (nếu có) bằng thủ tục hiện tại.
• Tên thủ tục phải là duy nhất.
• Từ khóa 'IS' được sử dụng khi thủ tục lưu trữ được lồng ghép bên trong một khối mã khác. Nếu thủ tục là độc lập, 'AS' được sử dụng. Ngoài tiêu chuẩn mã hóa này, cả hai đều có cùng ý nghĩa.

Ví dụ 1: Tạo một thủ tục và gọi nó bằng lệnh EXEC. Trong ví dụ này, chúng ta tạo ra một Oracle Thủ tục này nhận một tên làm đầu vào và in ra một thông báo chào mừng, sử dụng lệnh EXEC để gọi nó.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Giải thích:

• Code dòng 1: Tạo thủ tục có tên 'welcome_msg' và một tham số 'p_name' thuộc kiểu 'IN'.
• Code dòng 4: In ra thông báo chào mừng bằng cách nối chuỗi tên đầu vào.
• Quy trình đã được biên dịch thành công.
• Code dòng 7: Gọi thủ tục bằng lệnh EXEC với tham số 'Guru99'. Thủ tục được thực thi và in ra “Chào mừng Guru99. "

Chức năng là gì?

Hàm là một chương trình con PL/SQL độc lập. Giống như thủ tục, hàm có một tên duy nhất và được lưu trữ dưới dạng đối tượng cơ sở dữ liệu PL/SQL. Các đặc điểm của hàm là:

• Hàm là các khối độc lập được sử dụng chủ yếu để tính toán.
• Một hàm sử dụng từ khóa RETURN để trả về một giá trị, kiểu dữ liệu của giá trị đó được xác định tại thời điểm tạo hàm.
• Một hàm phải trả về một giá trị hoặc ném ra một ngoại lệ; câu lệnh `return` là bắt buộc trong các hàm.
• Một hàm không chứa câu lệnh DML có thể được gọi trực tiếp trong truy vấn SELECT, trong khi một hàm có chứa câu lệnh DML chỉ có thể được gọi từ các khối PL/SQL khác.
• Nó có thể chứa các khối lồng nhau, hoặc được lồng bên trong các khối hoặc gói khác.
• Nó bao gồm phần khai báo (tùy chọn), phần thực thi và phần xử lý ngoại lệ (tùy chọn).
• Các giá trị có thể được truyền vào hoặc lấy ra từ hàm thông qua các tham số.
• Các tham số này nên được bao gồm trong câu lệnh gọi.
• Ngoài việc sử dụng lệnh RETURN, một hàm cũng có thể trả về giá trị thông qua các tham số OUT.
• Vì nó luôn trả về một giá trị, nên câu lệnh gọi luôn sử dụng toán tử gán để gán giá trị cho một biến.

cú pháp

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• Lệnh 'CREATE FUNCTION' hướng dẫn trình biên dịch tạo một hàm mới. Lệnh 'OR REPLACE' hướng dẫn trình biên dịch thay thế hàm hiện có (nếu có) bằng hàm hiện tại.
• Tên hàm phải là duy nhất.
• Cần phải nêu rõ kiểu dữ liệu trả về.
• Từ khóa 'IS' được sử dụng khi hàm được lồng bên trong một khối lệnh khác. Nếu hàm đứng độc lập, từ khóa 'AS' được sử dụng.

Ví dụ 1: Tạo một hàm và gọi hàm đó bằng khối ẩn danh. Trong chương trình này, chúng ta tạo một hàm nhận tên làm đầu vào và trả về lời chào mừng, sử dụng khối lệnh ẩn danh và câu lệnh SELECT để gọi hàm đó.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Giải thích:

• Code dòng 1: Tạo hàm có tên 'welcome_msg_func' và một tham số 'p_name' thuộc kiểu 'IN'.
• Code dòng 2: Khai báo kiểu trả về là VARCHAR2.
• Code dòng 5: Trả về giá trị được nối chuỗi 'Welcome' và giá trị tham số.
• Code dòng 8: Chặn ẩn danh để gọi hàm trên.
• Code dòng 9: Khai báo biến với kiểu dữ liệu giống với kiểu dữ liệu trả về của hàm.
• Code dòng 11: Gọi hàm và gán giá trị trả về vào biến 'lv_msg'.
• Code dòng 12: In ra giá trị của biến. Kết quả đầu ra là “Welcome” Guru99. "
• Code dòng 14: Gọi cùng một hàm thông qua câu lệnh SELECT. Giá trị trả về được chuyển hướng đến đầu ra chuẩn.

Những điểm tương đồng giữa quy trình và hàm

• Cả hai đều có thể được gọi từ các khối PL/SQL khác.
• Nếu một ngoại lệ phát sinh trong chương trình con không được xử lý trong chương trình mẹ của nó xử lý ngoại lệ phần đó, nó lan truyền đến khối gọi.
• Cả hai đều có thể có nhiều tham số theo yêu cầu.
• Cả hai đều được coi là đối tượng cơ sở dữ liệu trong PL/SQL.

Quy trình so với chức năng: Những điểm khác biệt chính

Các hàm tích hợp trong PL/SQL

PL / SQL Chứa nhiều hàm tích hợp sẵn để làm việc với các kiểu dữ liệu chuỗi và ngày tháng. Dưới đây là các hàm thường dùng và cách sử dụng chúng.

Hàm chuyển đổi

Các hàm tích hợp sẵn này chuyển đổi một kiểu dữ liệu sang kiểu dữ liệu khác.

Hàm chuỗi

Các hàm này được sử dụng trên kiểu dữ liệu ký tự.

Chức năng ngày

Các hàm này được sử dụng để thao tác với ngày tháng.

Câu Hỏi Thường Gặp