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

Bởi: Fiona Nâu Fiona Nâu
Hours cập nhật

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

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 đối tượng cơ sở dữ liệu. Chúng cũng có thể được gọi hoặc giới thiệu bên trong các khối khác.

Ngoài ra, chúng tôi sẽ đề cập đến những khác biệt chính giữa hai chương trình con này. Ngoài ra, chúng ta sẽ 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 là một phần của các chương trình con này. Dưới đây là các thuật ngữ mà chúng ta sẽ thảo luận.

Tham số

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

  • 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.
  • Các tham số này được bao gồm trong câu lệnh gọi của các chương trình con này để tương tác các giá trị 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.
  • Kích thước của kiểu dữ liệu không nên đề cập tại thời điểm khai báo tham số, vì kích thước của kiểu dữ liệu này là động.

Dựa trên các thông số mục đích của chúng được phân loại là

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

TRONG Tham số

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

RA Tham Số

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

Thông số IN OUT

  • Tham số này được sử dụng cho cả việc đưa đầu vào và nhận đầu ra từ các chương trình con.
  • Nó là một biến đọc-ghi bên trong các chương trình con. Giá trị của chúng có thể được thay đổi bên trong các chương trình con.
  • Trong câu lệnh gọi, các tham số này phải luôn là một biến để giữ giá trị từ các chương trình con.

Loại tham số này cần được đề cập tại thời điểm tạo chương trình con.

TRỞ VỀ

RETURN là từ khóa hướng dẫn trình biên dịch chuyể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à đ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 từ khóa RETURN trong chương trình con, mã sau đó sẽ bị bỏ qua.

Thông thường, khối cha hoặc khối chính sẽ gọi các chương trình con và sau đó điều khiển sẽ chuyển từ khối cha đó sang các chương trình con được gọi. RETURN trong chương trình con sẽ trả lại quyền điều khiển cho khối cha của chúng. Trong trường hợp hàm RETURN, câu lệnh cũng trả về giá trị. Kiểu dữ liệu của giá trị này luôn được đề cập tại thời điểm khai báo hàm. Kiểu dữ liệu có thể thuộc bất kỳ kiểu dữ liệu PL/SQL hợp lệ nào.

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

A Thủ tục trong PL/SQL 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 trong PL/SQL có tên duy nhất riêng để có thể tham chiếu và gọi nó. Đơn vị chương trình con này trong Oracle cơ sở dữ liệu được lưu trữ dưới dạng đối tượng cơ sở dữ liệu.

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

Dưới đây là các đặc điểm của đơn vị chương trình con Thủ tục trong PL/SQL:

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

cú pháp

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • TẠO THỦ TỤC hướng dẫn trình biên dịch tạo thủ tục mới trong Oracle. Từ khóa 'HOẶC THAY THẾ' hướng dẫn trình biên dịch thay thế quy trình hiện có (nếu có) bằng quy trình hiện tại.
  • Tên thủ tục phải là duy nhất.
  • Từ khóa 'IS' sẽ được sử dụng khi thủ tục được lưu trữ trong Oracle được lồng vào một số khối khác. Nếu quy trình này độc lập thì 'AS' sẽ đượ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 thủ tục và gọi nó bằng EXEC

Trong ví dụ này, chúng ta sẽ tạo một Oracle thủ tục lấy tên làm đầu vào và in thông báo chào mừng làm đầu ra. Chúng ta sẽ sử dụng lệnh EXEC để gọi thủ tục.

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

Giải thích mã:

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

NHỮNG BÀI VIẾT LIÊN QUAN

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 PL/SQL, các hàm có một tên duy nhất để có thể tham chiếu đến nó. Chúng được lưu trữ dưới dạng đối tượng cơ sở dữ liệu PL/SQL. Dưới đây là một số đặc điểm của hàm.

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

Các hàm trong PL/SQL

cú pháp

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END;
  • CREATE FUNCTION hướng dẫn trình biên dịch tạo một hàm mới. Từ khóa '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.
  • Kiểu dữ liệu RETURN nên được đề cập.
  • Từ khóa 'IS' sẽ được sử dụng khi thủ tục được lồng vào một số khối khác. Nếu quy trình này độc lập thì 'AS' sẽ đượ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 hàm và gọi hàm bằng khối ẩn danh

Trong chương trình này, chúng ta sẽ tạo một hàm lấy tên làm đầu vào và trả về thông báo chào mừng làm đầu ra. Chúng ta sẽ sử dụng khối ẩn danh và câu lệnh chọn để gọi hàm.

Các hàm trong PL/SQL

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
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;

Giải thích mã:

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

Điểm tương đồng giữa thủ tục và chức năng

  • Cả hai đều có thể được gọi từ các khối PL/SQL khác.
  • Nếu ngoại lệ nêu ra trong chương trình con không được xử lý trong chương trình con xử lý ngoại lệ phần này, sau đó nó sẽ truyền tới 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.

Thủ tục Vs. Chức năng: Sự khác biệt chính

Thủ tục Chức năng
Được sử dụng chủ yếu để thực hiện một quy trình nhất định Được sử dụng chủ yếu để thực hiện một số phép tính
Không thể gọi trong câu lệnh SELECT Hàm không chứa câu lệnh DML có thể được gọi trong câu lệnh SELECT
Sử dụng tham số OUT để trả về giá trị Sử dụng RETURN để trả về giá trị
Không bắt buộc phải trả về giá trị Bắt buộc phải trả về giá trị
RETURN sẽ đơn giản thoát khỏi điều khiển từ chương trình con. RETURN sẽ thoát khỏi điều khiển từ chương trình con và cũng trả về giá trị
Kiểu dữ liệu trả về sẽ không được chỉ định tại thời điểm tạo Kiểu dữ liệu trả về là bắt buộc tại thời điểm tạo

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

PL / SQL chứa nhiều hàm dựng sẵn khác nhau để làm việc với chuỗi và kiểu dữ liệu ngày. Ở đây chúng ta sẽ xem các chức năng thường được sử dụng và cách sử dụng chúng.

Hàm chuyển đổi

Các hàm dựng sẵn này được sử dụng để chuyển đổi một kiểu dữ liệu này sang kiểu dữ liệu khác.

Tên chức năng Sử dụng Ví dụ
TO_CHAR Chuyển đổi kiểu dữ liệu khác thành kiểu dữ liệu ký tự TO_CHAR(123);
TO_DATE (chuỗi, định dạng) Chuyển đổi chuỗi đã cho thành ngày. Chuỗi phải khớp với định dạng.

TO_DATE('2015-JAN-15', 'YYYY-MON-DD');

Đầu ra: 1 / 15 / 2015
TO_NUMBER (văn bản, định dạng)

Chuyển đổi văn bản thành loại số có định dạng nhất định.

Informat '9' biểu thị số chữ số

 Chọn TO_NUMBER('1234′,'9999') từ kép;

Đầu ra: 1234

Chọn TO_NUMBER('1,234.45′,'9,999.99') từ kép;

Đầu ra: 1234

Hàm chuỗi

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

Tên chức năng Sử dụng Ví dụ
INSTR(văn bản, chuỗi, bắt đầu, lần xuất hiện) Cung cấp vị trí của văn bản cụ thể trong chuỗi đã cho.

  • văn bản – Chuỗi chính
  • chuỗi - văn bản cần tìm kiếm
  • bắt đầu - vị trí bắt đầu tìm kiếm (tùy chọn)
  • phù hợp - sự xuất hiện của chuỗi tìm kiếm (tùy chọn)
 Chọn INSTR('AEROPLANE','E',2,1) từ kép

Đầu ra: 2

Chọn INSTR('AEROPLANE','E',2,2) từ kép

Đầu ra: 9 (2nd sự xuất hiện của E)
SUBSTR (văn bản, bắt đầu, độ dài) Cung cấp giá trị chuỗi con của chuỗi chính.

  • văn bản – chuỗi chính
  • bắt đầu - vị trí bắt đầu
  • chiều dài - chiều dài được xâu chuỗi phụ
 chọn substr('máy bay',1,7) từ kép

Đầu ra: hàng không
TRÊN ( văn bản ) Trả về chữ hoa của văn bản được cung cấp Chọn phần trên('guru99') từ kép;

Đầu ra: GURU99
LOWER ( văn bản ) Trả về chữ thường của văn bản được cung cấp Chọn thấp hơn ('AerOpLane') từ kép;

Đầu ra: Máy bay
INITCAP (văn bản) Trả về văn bản đã cho với chữ cái bắt đầu bằng chữ in hoa. Chọn ('guru99') từ kép

Đầu ra: Guru99

Chọn ('câu chuyện của tôi') từ kép

Đầu ra: Câu chuyện của tôi
CHIỀU DÀI (văn bản) Trả về độ dài của chuỗi đã cho Chọn LENGTH ('guru99') từ kép;

Đầu ra: 6
LPAD (văn bản, độ dài, pad_char) Đệm chuỗi ở bên trái theo độ dài nhất định (tổng chuỗi) bằng ký tự đã cho Chọn LPAD('guru99', 10, '$') từ kép;

Đầu ra: $$$$guru99
RPAD (văn bản, độ dài, pad_char) Đệm chuỗi ở bên phải theo độ dài nhất định (tổng chuỗi) bằng ký tự đã cho Chọn RPAD('guru99′,10,'-') từ kép

Đầu ra: guru99—-
LTRIM ( văn bản ) Cắt bớt khoảng trắng ở đầu văn bản Chọn LTRIM('Guru99') từ kép;

Đầu ra: Guru99
RTRIM ( văn bản ) Cắt bớt khoảng trắng ở cuối văn bản Chọn RTRIM('Guru99 ') từ kép;

Đầu ra; Guru99

Chức năng ngày

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

Tên chức năng Sử dụng Ví dụ
ADD_MONTHS (ngày, số tháng) Thêm các tháng đã cho vào ngày ADD_MONTH('2015-01-01',5);

Đầu ra: 05 / 01 / 2015
HỆ THỐNG Trả về ngày giờ hiện tại của máy chủ Chọn SYSDATE từ kép;

Đầu ra: 10/4/2015 2:11:43 chiều
TRÚC Làm tròn biến ngày thành giá trị thấp hơn có thể chọn sysdate, TRUNC(sysdate) từ kép;

Đầu ra: 10/4/2015 2:12:39 chiều 10/4/2015
ROUND Làm tròn ngày đến giới hạn gần nhất cao hơn hoặc thấp hơn Chọn sysdate, ROUND(sysdate) từ kép

Đầu ra: 10/4/2015 2:14:34 chiều 10/5/2015
MONTHS_BETWEEN Trả về số tháng giữa hai ngày Chọn MONTHS_BETWEEN (sysdate+60, sysdate) từ kép

Đầu ra: 2

Tổng kết

Trong chương này, chúng ta đã học những nội dung sau.

  • Cách tạo Thủ tục và các cách gọi khác nhau
  • Cách tạo Hàm và các cách gọi khác nhau
  • Điểm giống và khác nhau giữa Thủ tục và Chức năng
  • Các tham số và RETURN các thuật ngữ phổ biến trong chương trình con PL/SQL
  • Các hàm tích hợp phổ biến trong Oracle PL / SQL