Quy trình Lưu trữ Oracle PL / SQL & Các hàm với các ví dụ

Mục lục:

Anonim

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à hàm).

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.

Ngoài ra, chúng tôi sẽ đề cập đến những điểm 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ề các hàm tích hợp sẵn của Oracle.

Trong hướng dẫn Thủ tục lưu trữ Oracle này, bạn sẽ học-

  • Các thuật ngữ trong chương trình con PL / SQL
  • Thủ tục trong PL / SQL là gì?
  • Chức năng là gì?
  • Điểm giống nhau giữa Thủ tục và Chức năng
  • Quy trình Vs. Chức năng: Sự khác biệt chính
  • Các hàm tích hợp trong PL / SQL

Các thuật ngữ trong chương trình con PL / SQL

Trước khi tìm hiểu về 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 trình giữ chỗ của bất kỳ kiểu dữ liệu PL / SQL hợp lệ nào mà 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 nên đượ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 n 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 là động đối với kiểu này.

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

  1. Tham số IN
  2. Tham số OUT
  3. Tham số IN OUT

Tham số IN:

  • 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ị 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ố là kiểu IN.

Tham số OUT:

  • 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.

Tham số IN OUT:

  • Tham số này được sử dụng cho cả đầ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 nên được đề cập tại thời điểm tạo các 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, đoạn mã sau từ khóa này sẽ bị bỏ qua.

Thông thường, khối mẹ 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 mẹ đó sang các chương trình con được gọi. RETURN trong chương trình con sẽ trả lại điều khiển trở lại khối cha của chúng. Trong trường hợp của các hàm, câu lệnh RETURN 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ì?

Một thủ tục trong PL / SQL là một đơn vị Tiểu chương bao gồm một nhóm các báo cáo PL / SQL có thể được gọi bằng tên. Mỗi thủ tục trong PL / SQL có tên riêng của nó mà nó có thể được tham chiếu và gọi. Đơn vị chương trình con này trong cơ sở dữ liệu Oracle được lưu trữ dưới dạng một đố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 được tạo, chúng sẽ được lưu trữ dưới dạng các đố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.
  • Việc gọi các thủ tục PLSQL này có thể được thực hiện bằng cách tham chiếu đến 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 tiến trình trong PL / SQL.
  • Nó có thể có các khối lồng nhau, hoặc nó có thể được định nghĩa và lồng vào 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 thủ tục Oracle 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ả lại điều khiển cho khối đang 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
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE 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 là độc lập thì 'AS' sẽ được sử dụng. Khác vớ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 thủ tục Oracle lấy tên làm đầu vào và in thông báo chào mừng dưới dạng đầ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)ISBEGINdbms_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 với 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'. Thủ tục được thực hiện và thông báo được in ra dưới dạng "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 PL / SQL, các hàm có một tên duy nhất mà nó có thể được tham chiếu. Chúng được lưu trữ dưới dạng các đối tượng cơ sở dữ liệu PL / SQL. Dưới đây là một số đặc điểm của chức năng.

  • 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 từ khóa này được xác định tại thời điểm tạo.
  • Một Hàm phải trả về một giá trị hoặc nâng cao ngoại lệ, tức là trả về là bắt buộc trong các 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ó hoạt động 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 nó có thể được định nghĩa và lồng vào 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 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 hàm PLSQL cũng có thể trả về giá trị thông qua các tham số OUT khác với việc sử dụng RETURN.
  • Vì nó sẽ luôn trả về giá trị, nên trong câu lệnh gọi, nó luôn đi kèm với toán tử gán để điền các biến.

Cú pháp

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION hướng dẫn trình biên dịch tạo một hàm mới. Từ khóa 'HOẶC THAY THẾ' hướng dẫn trình biên dịch thay thế chức năng hiện có (nếu có) bằng chức năng hiện tại.
  • Tên hàm phải là duy nhất.
  • Loại dữ liệu RETURN nên được đề cập.
  • Từ khóa 'IS' sẽ được sử dụng, khi quy trình được lồng vào một số khối khác. Nếu quy trình là độc lập thì 'AS' sẽ được sử dụng. Khác vớ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 nó 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 dưới dạng đầu ra. Chúng tôi sẽ sử dụng khối ẩn danh và câu lệnh select để gọi hàm.

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_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 hàm Oracle với tên 'welcome_msg_func' và với một tham số 'p_name' kiểu '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ề cho biến 'lv_msg'.
  • Dòng mã 12 : In giá trị biến. Đầu ra 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 giống nhau 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ệ được nêu ra trong chương trình con không được xử lý trong phần xử lý ngoại lệ của chương trình con, thì nó sẽ truyền đến khối đang gọi.
  • Cả hai đều có thể có nhiều tham số theo yêu cầu.
  • Cả hai đều được coi là các đối tượng cơ sở dữ liệu trong PL / SQL.

Quy trình 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
  • Một 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ả lại giá trị
  • Bắt buộc phải trả lại 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 khỏi 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
  • Loại 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 tích hợp 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.

Chức năng chuyển đổi

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

Tên chức năng Sử dụng Thí 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'); Sản lượng: 1/15/2015
TO_NUMBER (văn bản, định dạng) Chuyển đổi văn bản thành kiểu số của đị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 Thí dụ
INSTR (văn bản, chuỗi, bắt đầu, 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 được tìm kiếm
  • bắt đầu - vị trí bắt đầu của tìm kiếm (tùy chọn)
  • phù hợp - sự xuất hiện của chuỗi được tìm kiếm (tùy chọn)
 Chọn INSTR ('AIRPLANE', 'E', 2,1) từ Đầu ra kép : 2 Chọn INSTR ('AIRPLANE', 'E', 2,2) từ Đầu ra kép: 9 (lần xuất hiện thứ 2 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 - độ dài được xâu chuỗi con
 chọn substr ('máy bay', 1,7) từ Đầu ra kép : aeropla
LÊN (văn bản) Trả về chữ hoa của văn bản đã cung cấp Chọn upper ('guru99') từ kép; Đầu ra : GURU99
LOWER (văn bản) Trả về chữ thường của văn bản đã cung cấp Chọn thấp hơn ('máy bay') 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ữ hoa. Chọn ('guru99') từ Đầu ra kép : Guru99 Chọn ('câu chuyện của tôi') từ Đầu ra kép : Câu chuyện của tôi
LENGTH (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) Dán chuỗi ở phía bên trái cho độ dài đã cho (tổng chuỗi) với ký tự đã cho Chọn LPAD ('guru99', 10, '$') từ kép; Đầu ra : $$$$ guru99
RPAD (văn bản, độ dài, pad_char) Dán chuỗi vào bên phải với độ dài đã cho (tổng chuỗi) với ký tự đã cho Chọn RPAD ('guru99', 10, '-') từ Đầu ra kép : guru99 ----
LTRIM (văn bản) Cắt bỏ 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ỏ 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à những hàm được sử dụng để thao tác với ngày tháng.

Tên chức năng Sử dụng Thí dụ
ADD_MONTHS (ngày, số tháng) Thêm các tháng nhất định vào ngày ADD_MONTH ('2015-01-01', 5); Đầu ra : 05/01/2015
SYSDATE Trả về ngày và giờ hiện tại của máy chủ Chọn SYSDATE từ kép; Đầu ra : 10/4/2015 2:11:43 PM
TRUNC Vòng của biến ngày đến giá trị thấp hơn có thể chọn sysdate, TRUNC (sysdate) from dual; Đầu ra : 10/4/2015 2:12:39 PM 10/4/2015
TRÒN Làm tròn ngày đến giới hạn gần nhất hoặc cao hơn hoặc thấp hơn Chọn sysdate, ROUND (sysdate) từ đầu ra kép : 10/4/2015 2:14:34 PM 10/5/2015
MONTHS_BETWEEN Trả về số tháng giữa hai ngày Chọn MONTHS_BETWEEN (sysdate + 60, sysdate) từ đầu ra kép : 2

Tóm lược

Trong chương này, chúng ta đã tìm hiểu những điều sau đây.

  • 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
  • Tham số và QUAY LẠI 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