Procedures and Functions in PL/SQL

Introduction

Procedure và Function là các chương trình con có thể được tạo ra và được lưu trong cơ sở dữ liệu như các đối tượng cơ sở dữ liệu. Chúng được gọi trực tiếp hoặc gọi bên trong các khối khác.

Ở bài viết này tôi sẽ giới thiệu tới các bạn làm thế nào để tạo ra các procedure và cách gọi chúng, làm thế nào để tạo ra các function khác nhau. Hơn nữa tôi cũng sẽ phân tích cho các bạn thấy những điểm tương đồng và khác biệt giữa procedure và function. Ngoài ra bạn cũng được hiểu thêm một số thành phần parameter và return phổ biến được sử dụng trong các chương trình con PL/SQL.

Terminologies in PL/SQL Subprograms

Parameter

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

• Những parameter này cần được xác định cùng với các chương trình con tại thời điểm tạo ra.
• Parameters được đưa vào calling statement của các chương trình con, chúng tương tác các giá trị với những chương trình con này.
• Kiểu dữ liệu của parameter trong chương trình con và trong các calling statement là giống nhau.
• Kích thước của kiểu dữ liệu không nên đề cập đến tại thời điểm kê khai parameter, vì chúng có thể thay đổi kích thước trong quá trình sử dụng.

IN Parameter

• Parameter này được sử dụng làm đầu vào cho chương trình con.
• Nó là một biến chỉ đọc bên trong chương trình con, giá trị của chúng không có thể thay đổi được bên trong chương trình con.
• Khi gọi parameter này có thể là một biến hoặc một giá trị hoặc một biểu thức, ví dụ nó có thể là biểu thức số học như: '5 * 8' or 'a / b' với 'a' và 'b' là các biến.
• Mặc định, parameters là các loại IN.

OUT Parameter

• Parameter này được sử dụng để nhận giá trị xuất ra từ các chương trình con.
• Nó là một biến read-write bên trong các chương trình con, các giá trị của chúng có thể được thay đổi bên trong chương trình con.
• Sử dụng parameter này luôn luôn có một biến để giữ giá trị từ chương trình con.

IN OUT Parameter

• Parameter này được sử dụng cho cả IN và OUT, tức là truyền biến và lấy kết quả từ các chương trình con.
• Nó là một biến read-write bên trong các chương trình con, các giá trị của chúng có thể được thay đổi bên trong chương trình con.
• Việc gọi và sử dụng parameter này luôn luôn có một biến để giữ giá trị từ chương trình con.

RETURN

RETURN là từ khóa chỉ thị các trình biên dịch chuyển đổi điều khiển từ chương trình con trả về 1 kết quả. Trong chương trình con RETURN chỉ đơn giản có nghĩa là điều khiể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, các mã sau RETURN sẽ bị bỏ qua.

Thông thường khối chính sẽ gọi chương trình con và sau đó điều khiển sẽ chuyển từ những khối chính cho 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 chính. Trong trường hợp của các chức năng RETURN cũng trả về giá trị. Các kiểu dữ liệu của giá trị này luôn luôn được đề cập tại thời điểm khai báo hàm. Các kiểu dữ liệu có thể là bất kì kiểu dữ liệu PL/SQL miễn là phải hợp lệ.

Procedure

Procedure là một kiểu chương trình con, nó bao gồm một nhóm các lệnh PL/SQL. Mỗi thủ tục trong Oracle có tên riêng của nó mà nó có thể được gọi. đơn vị chương trình con này được lưu trữ như là một đối tượng cơ sở dữ liệu. Dưới đây là những đặc điểm của dạng chương trình con này.

• Procedure là một 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.
• Gọi đến các Procedure này có thể được thực hiện bằng cách đề cập đến tên của chúng.
• Nó chủ yếu được sử dụng để thực hiện một quá trình bên trong PL/SQL.
• Nó có thể có khối lồng nhau, hoặc nó có thể được xác định và lồng vào bên trong các khối hoặc các gói khác.
• Nó chứa một 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 thông qua vào các thủ tục hoặc lấy từ các thủ tục thông qua các tham số.
• Những thông số cần được đưa vào khai báo để sử dụng.
• Procedure có thể có RETURN để trả lại quyền kiểm soát vào khối gọi nó, nhưng nó không thể trả lại bất kỳ giá trị thông qua RETURN.
• 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 là từ khối khác hoặc thông qua từ khóa EXEC.

Syntax Explanation:

• CREATE PROCEDURE từ khóa tạo ra Procedure mới. Từ khoá 'OR REPLACE' chỉ thị biên dịch để thay thế các thủ tục hiện hành (nếu có).
• Tên thủ tục phải là duy nhất.
• Từ khoá 'IS' sẽ được sử dụng, khi thủ tục được lồng vào một số khối khác. Nếu thủ tục là độc rồi '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 một ý nghĩa.

Ví dụ về tạo thủ tục và gọi nó từ bẳng EXEC: Trong ví dụ này, chúng ta sẽ tạo một procedure mà có input và output in ra thông điệp chào mừng như đầu ra. Chúng ta sẽ sử dụng lệnh EXEC để gọi thủ tục.

Code Explanation:

• Line 1: Tạo Procdure với tên 'welcome_msg' và với một tham số 'p_name'.
• Line 4: in các thông điệp chào mừng bằng cách ghép tên đầu vào. Thủ tục này được biên soạn thành công.
• Line 7: gọi Procedure sử dụng lệnh EXEC với tham số 'Guru99'. Procedure thực thi, và thông điệp được in ra như "Welcome Guru99".

Function

Function cũng là một PL/SQL chương trình con. Giống như procedure PL/SQL, các chức năng có một tên duy nhất mà nó có thể được gọi. Chúng được lưu trữ như các đối tượng cơ sở dữ liệu PL/SQL. Dưới đây là một số đặc điểm của các Function.

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

Ví dụ: Tạo và gọi Function bằng cách sử Anonymous Block: Trong chương trình này, chúng ta sẽ tạo ra một Function với giá trị truyền vào và trả về 1 thông điệp. Chúng ta sẽ sử dụng khối ẩn danh và gọi hàm ra.

Comparison between Procedure and Function

Similarities

• Procedure and Function đều có thể được gọi từ các khối PL/SQL khác.
• Procedure and Function có thể có nhiều thông số theo yêu cầu.
• Procedure and Function đều được coi là đối tượng cơ sở dữ liệu trong PL/SQL

Difference

|_. Procedure |_. Function |
| Được sử dụng chủ yếu để thực hiện quy trình nhất định | Được sử dụng chủ yếu để thực hiện một số tính toán |
| Không thể gọi trong câu lệnh SELECT
 | Có thể được gọi là trong câu lệnh SELECT (không chứa DML)|
| Sử dụng tham số OUT để trả về giá trị | Sử dụng RETURN để trả về giá trị |
| Không bắt buộc trả về giá trị
 | Bắt buộc trả về giá trị |
| RETURN chỉ đơn giản là sẽ thoát khỏi sự kiểm soát từ chương trình con.
 | RETURN sẽ thoát khỏi sự kiểm soát từ chương trình con và cũng trả về giá trị
 |
| datatype trở lại sẽ không được quy định tại thời điểm tạo ra
|datatype là bắt buộc tại thời điểm tạo ra
 |

Built-in Functions in PL/SQL

PL/SQL có chứa nhiều function được xây dựng sẵn bên trong để làm việc với String và Date datatype. Ở đây chúng ta sẽ xem các function thường được sử dụng và cách dùng chúng.

Conversion Functions

Các chức năng này được xây dựng trong được sử dụng để chuyển đổi một kiểu dữ liệu đến kiểu dữ liệu khác.

• TO_CHAR(string) chuyển đổi datatype
• TO_DATE ( string, format ) chuyển đổi chuỗi date, string đưa vào phải đúng format
• TO_NUMBER (text, format) chuyển đổi sang dạng number theo format

String Functions

Đây là những chức năng được sử dụng trên các kiểu dữ liệu dạng string.

• INSTR(text, string, start, occurance) cung cấp vị trí string trong văn bản text.
• SUBSTR ( text, start, length) đưa ra giá trị chuỗi con từ vị trí start
• UPPER (text) trả về chữ hoa
• LOWER (text) trả về chữ thường
• INITCAP (text) chữ đầu tiên viết hoa
• LENGTH (text) độ dài của văn bản
• LPAD (text, length, pad_char) ghép chuỗi trái pad-char + text để đạt được độ dài length
• RPAD (text, length, pad_char) ghép chuỗi phải text + pad_char để đạt được độ dài length
• LTRIM (text) trim space bên trái
• RTRIM (text) trim space bên phải

Date Functions

Đây là chức năng được sử dụng để thao tác với datetime.

• ADD_MONTHS (date, no.of months) thêm tháng
• SYSDATE thời gian hiện tại của system
• TRUNC
• ROUND làm tròn ngày
• MONTHS_BETWEEN so sánh tháng giữa 2 datetime

Summary

Như vậy mình đã giới thiệu xong Proceduce và Function trong PL/SQL, các bạn có thể ứng dụng viết các function hoặc procedure theo cấu trúc trên. Đối với function thì đã có sẵn một số hàm có thể gọi ra dùng trực tiếp. Mong rằng bài này sẽ hữu ích đối với các bạn muốn dùng function, procedure trong PL/SQL.

Các bạn có thể tham khảo chi tiết hơn tại http://www.guru99.com/subprograms-procedures-functions-pl-sql.html