PL / SQL - Hướng dẫn nhanh
Ngôn ngữ lập trình PL / SQL được Oracle Corporation phát triển vào cuối những năm 1980 như là ngôn ngữ mở rộng thủ tục cho SQL và cơ sở dữ liệu quan hệ Oracle. Sau đây là một số thông tin đáng chú ý về PL / SQL -
PL / SQL là một ngôn ngữ xử lý giao dịch hoàn toàn di động, hiệu suất cao.
PL / SQL cung cấp một môi trường lập trình tích hợp, thông dịch và hệ điều hành độc lập.
PL / SQL cũng có thể được gọi trực tiếp từ dòng lệnh SQL*Plus interface.
Cuộc gọi trực tiếp cũng có thể được thực hiện từ các cuộc gọi ngôn ngữ lập trình bên ngoài đến cơ sở dữ liệu.
Cú pháp chung của PL / SQL dựa trên cú pháp của ngôn ngữ lập trình ADA và Pascal.
Ngoài Oracle, PL / SQL có sẵn trong TimesTen in-memory database và IBM DB2.
Các tính năng của PL / SQL
PL / SQL có các tính năng sau:
- PL / SQL được tích hợp chặt chẽ với SQL.
- Nó cung cấp khả năng kiểm tra lỗi rộng rãi.
- Nó cung cấp nhiều kiểu dữ liệu.
- Nó cung cấp nhiều cấu trúc lập trình khác nhau.
- Nó hỗ trợ lập trình có cấu trúc thông qua các hàm và thủ tục.
- Nó hỗ trợ lập trình hướng đối tượng.
- Nó hỗ trợ phát triển các ứng dụng web và các trang máy chủ.
Ưu điểm của PL / SQL
PL / SQL có những ưu điểm sau:
SQL là ngôn ngữ cơ sở dữ liệu tiêu chuẩn và PL / SQL được tích hợp mạnh mẽ với SQL. PL / SQL hỗ trợ cả SQL tĩnh và SQL động. SQL tĩnh hỗ trợ các hoạt động DML và kiểm soát giao dịch từ khối PL / SQL. Trong SQL động, SQL cho phép nhúng các câu lệnh DDL trong các khối PL / SQL.
PL / SQL cho phép gửi toàn bộ khối câu lệnh đến cơ sở dữ liệu cùng một lúc. Điều này làm giảm lưu lượng mạng và cung cấp hiệu suất cao cho các ứng dụng.
PL / SQL mang lại năng suất cao cho các lập trình viên vì nó có thể truy vấn, chuyển đổi và cập nhật dữ liệu trong cơ sở dữ liệu.
PL / SQL tiết kiệm thời gian thiết kế và gỡ lỗi nhờ các tính năng mạnh mẽ, chẳng hạn như xử lý ngoại lệ, đóng gói, ẩn dữ liệu và kiểu dữ liệu hướng đối tượng.
Các ứng dụng được viết bằng PL / SQL hoàn toàn có thể di động.
PL / SQL cung cấp mức độ bảo mật cao.
PL / SQL cung cấp quyền truy cập vào các gói SQL được xác định trước.
PL / SQL cung cấp hỗ trợ cho Lập trình hướng đối tượng.
PL / SQL cung cấp hỗ trợ để phát triển Ứng dụng Web và Trang Máy chủ.
Trong chương này, chúng ta sẽ thảo luận về Thiết lập Môi trường của PL / SQL. PL / SQL không phải là một ngôn ngữ lập trình độc lập; nó là một công cụ trong môi trường lập trình Oracle.SQL* Pluslà một công cụ tương tác cho phép bạn gõ các câu lệnh SQL và PL / SQL tại dấu nhắc lệnh. Các lệnh này sau đó được gửi đến cơ sở dữ liệu để xử lý. Khi các câu lệnh được xử lý, kết quả sẽ được gửi lại và hiển thị trên màn hình.
Để chạy các chương trình PL / SQL, bạn phải cài đặt Máy chủ Oracle RDBMS trong máy của mình. Điều này sẽ xử lý việc thực thi các lệnh SQL. Phiên bản gần đây nhất của Oracle RDBMS là 11g. Bạn có thể tải xuống phiên bản dùng thử của Oracle 11g từ liên kết sau:
Tải xuống phiên bản Oracle 11g Express
Bạn sẽ phải tải xuống phiên bản cài đặt 32 bit hoặc 64 bit tùy theo hệ điều hành của bạn. Thông thường có hai tệp. Chúng tôi đã tải xuống phiên bản 64-bit. Bạn cũng sẽ sử dụng các bước tương tự trên hệ điều hành của mình, không quan trọng đó là Linux hay Solaris.
win64_11gR2_database_1of2.zip
win64_11gR2_database_2of2.zip
Sau khi tải hai tập tin trên, bạn cần giải nén chúng trong một thư mục duy nhất database và theo đó bạn sẽ tìm thấy các thư mục con sau:
Bước 1
Bây giờ chúng ta hãy khởi chạy Trình cài đặt cơ sở dữ liệu Oracle bằng cách sử dụng tệp thiết lập. Sau đây là màn hình đầu tiên. Bạn có thể cung cấp ID email của mình và chọn hộp kiểm như được hiển thị trong ảnh chụp màn hình sau. Nhấn vàoNext cái nút.
Bước 2
Bạn sẽ được chuyển đến màn hình sau; bỏ chọn hộp kiểm và nhấp vàoContinue để tiếp tục.
Bước 3
Chỉ cần chọn tùy chọn đầu tiên Create and Configure Database bằng cách sử dụng nút radio và nhấp vào Next để tiếp tục.
Bước 4
Chúng tôi giả định rằng bạn đang cài đặt Oracle cho mục đích học tập cơ bản và bạn đang cài đặt nó trên PC hoặc Máy tính xách tay của mình. Do đó, hãy chọnDesktop Class và nhấp vào Next để tiếp tục.
Bước 5
Cung cấp vị trí, nơi bạn sẽ cài đặt Máy chủ Oracle. Chỉ cần sửa đổiOracle Basevà các vị trí khác sẽ tự động đặt. Bạn cũng sẽ phải cung cấp mật khẩu; điều này sẽ được sử dụng bởi hệ thống DBA. Khi bạn cung cấp thông tin cần thiết, hãy nhấp vàoNext để tiếp tục.
Bước 6
Một lần nữa, nhấp vào Next để tiếp tục.
Bước 7
Nhấn vào Finishnút để tiếp tục; điều này sẽ bắt đầu cài đặt máy chủ thực tế.
Bước 8
Quá trình này sẽ mất vài phút cho đến khi Oracle bắt đầu thực hiện cấu hình yêu cầu.
Bước 9
Tại đây, cài đặt Oracle sẽ sao chép các tệp cấu hình cần thiết. Việc này sẽ mất một chút thời gian -
Bước 10
Sau khi các tệp cơ sở dữ liệu được sao chép, bạn sẽ có hộp thoại sau. Chỉ cần nhấp vàoOK và đi ra.
Bước 11
Sau khi cài đặt, bạn sẽ có cửa sổ cuối cùng sau đây.
Bước cuối cùng
Bây giờ là lúc để xác minh cài đặt của bạn. Tại dấu nhắc lệnh, sử dụng lệnh sau nếu bạn đang sử dụng Windows:
sqlplus "/ as sysdba"
Bạn sẽ có lời nhắc SQL nơi bạn sẽ viết các lệnh và tập lệnh PL / SQL của mình -
Trình soạn thảo văn bản
Chạy các chương trình lớn từ dấu nhắc lệnh có thể khiến bạn vô tình làm mất một số công việc. Bạn nên sử dụng các tệp lệnh. Để sử dụng các tệp lệnh -
Nhập mã của bạn vào trình soạn thảo văn bản, như Notepad, Notepad+, hoặc là EditPlus, Vân vân.
Lưu tệp với .sql phần mở rộng trong thư mục chính.
Khởi chạy SQL*Plus command prompt từ thư mục mà bạn đã tạo tệp PL / SQL của mình.
Kiểu @file_name tại dấu nhắc lệnh SQL * Plus để thực thi chương trình của bạn.
Nếu bạn không sử dụng tệp để thực thi các tập lệnh PL / SQL, thì chỉ cần sao chép mã PL / SQL của bạn và nhấp chuột phải vào cửa sổ màu đen hiển thị lời nhắc SQL; sử dụngpasteđể dán mã hoàn chỉnh vào dấu nhắc lệnh. Cuối cùng, chỉ cần nhấnEnter để thực thi mã, nếu nó chưa được thực thi.
Trong chương này, chúng ta sẽ thảo luận về Cú pháp cơ bản của PL / SQL là một block-structuredngôn ngữ; điều này có nghĩa là các chương trình PL / SQL được chia và viết trong các khối mã logic. Mỗi khối bao gồm ba phần phụ -
S.Không | Phần & Mô tả |
---|---|
1 | Declarations Phần này bắt đầu với từ khóa DECLARE. Nó là một phần tùy chọn và xác định tất cả các biến, con trỏ, chương trình con và các phần tử khác sẽ được sử dụng trong chương trình. |
2 | Executable Commands Phần này nằm giữa các từ khóa BEGIN và ENDvà nó là một phần bắt buộc. Nó bao gồm các câu lệnh PL / SQL thực thi của chương trình. Nó phải có ít nhất một dòng mã thực thi, có thể chỉ là mộtNULL command để chỉ ra rằng không có gì nên được thực thi. |
3 | Exception Handling Phần này bắt đầu với từ khóa EXCEPTION. Phần tùy chọn này chứaexception(s) xử lý các lỗi trong chương trình. |
Mọi câu lệnh PL / SQL đều kết thúc bằng dấu chấm phẩy (;). Các khối PL / SQL có thể được lồng trong các khối PL / SQL khác bằng cách sử dụngBEGIN và END. Sau đây là cấu trúc cơ bản của một khối PL / SQL:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
Ví dụ về 'Hello World'
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
Các end;dòng báo hiệu sự kết thúc của khối PL / SQL. Để chạy mã từ dòng lệnh SQL, bạn có thể cần phải nhập / vào đầu dòng trống đầu tiên sau dòng cuối cùng của mã. Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Hello World
PL/SQL procedure successfully completed.
Số nhận dạng PL / SQL
Định danh PL / SQL là hằng số, biến, ngoại lệ, thủ tục, con trỏ và các từ dành riêng. Các mã nhận dạng bao gồm một chữ cái, theo sau là nhiều chữ cái, chữ số, dấu đô la, dấu gạch dưới và dấu số và không được vượt quá 30 ký tự.
Theo mặc định, identifiers are not case-sensitive. Vì vậy, bạn có thể sử dụnginteger hoặc là INTEGERđể biểu diễn một giá trị số. Bạn không thể sử dụng từ khóa dành riêng làm định danh.
Dấu phân cách PL / SQL
Dấu phân cách là một ký hiệu có ý nghĩa đặc biệt. Sau đây là danh sách các dấu phân cách trong PL / SQL:
Dấu phân cách | Sự miêu tả |
---|---|
+, -, *, / | Cộng, trừ / phủ định, nhân, chia |
% | Chỉ báo thuộc tính |
' | Dấu phân cách chuỗi ký tự |
. | Bộ chọn thành phần |
(,) | Dấu phân cách biểu thức hoặc danh sách |
: | Chỉ báo biến máy chủ |
, | Dấu tách mục |
" | Dấu phân cách số nhận dạng được trích dẫn |
= | Toán tử quan hệ |
@ | Chỉ báo truy cập từ xa |
; | Dấu chấm dứt câu lệnh |
:= | Toán tử chuyển nhượng |
=> | Nhà điều hành hiệp hội |
|| | Điều hành nối |
** | Toán tử lũy thừa |
<<, >> | Dấu phân cách nhãn (bắt đầu và kết thúc) |
/*, */ | Dấu phân cách nhận xét nhiều dòng (bắt đầu và kết thúc) |
-- | Chỉ báo bình luận một dòng |
.. | Toán tử phạm vi |
<, >, <=, >= | Toán tử quan hệ |
<>, '=, ~=, ^= | Các phiên bản khác nhau của NOT EQUAL |
Nhận xét PL / SQL
Nhận xét chương trình là các câu giải thích có thể được đưa vào mã PL / SQL mà bạn viết và giúp bất kỳ ai đọc mã nguồn của nó. Tất cả các ngôn ngữ lập trình đều cho phép một số hình thức nhận xét.
PL / SQL hỗ trợ các chú thích một dòng và nhiều dòng. Tất cả các ký tự có sẵn bên trong bất kỳ chú thích nào đều bị trình biên dịch PL / SQL bỏ qua. Các chú thích dòng đơn PL / SQL bắt đầu bằng dấu phân cách - (gạch nối đôi) và chú thích nhiều dòng được bao bởi / * và * /.
DECLARE
-- variable declaration
message varchar2(20):= 'Hello, World!';
BEGIN
/*
* PL/SQL executable statement(s)
*/
dbms_output.put_line(message);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Hello World
PL/SQL procedure successfully completed.
Đơn vị chương trình PL / SQL
Đơn vị PL / SQL là bất kỳ đơn vị nào sau đây:
- Khối PL / SQL
- Function
- Package
- Gói thân
- Procedure
- Trigger
- Type
- Nhập nội dung
Mỗi đơn vị này sẽ được thảo luận trong các chương sau.
Trong chương này, chúng ta sẽ thảo luận về các kiểu dữ liệu trong PL / SQL. Các biến, hằng số và tham số PL / SQL phải có kiểu dữ liệu hợp lệ, kiểu dữ liệu này chỉ định định dạng lưu trữ, các ràng buộc và phạm vi giá trị hợp lệ. Chúng tôi sẽ tập trung vàoSCALAR và LOBcác kiểu dữ liệu trong chương này. Hai kiểu dữ liệu còn lại sẽ được đề cập trong các chương khác.
S.Không | Danh mục & Mô tả |
---|---|
1 | Scalar Các giá trị đơn lẻ không có thành phần bên trong, chẳng hạn như NUMBER, DATE, hoặc là BOOLEAN. |
2 | Large Object (LOB) Con trỏ đến các đối tượng lớn được lưu trữ riêng biệt với các mục dữ liệu khác, chẳng hạn như văn bản, hình ảnh đồ họa, video clip và dạng sóng âm thanh. |
3 | Composite Các mục dữ liệu có các thành phần bên trong có thể được truy cập riêng lẻ. Ví dụ, bộ sưu tập và bản ghi. |
4 | Reference Con trỏ đến các mục dữ liệu khác. |
Kiểu dữ liệu vô hướng và kiểu con PL / SQL
Các kiểu dữ liệu vô hướng và kiểu con PL / SQL thuộc các danh mục sau:
S.Không | Loại ngày & Mô tả |
---|---|
1 | Numeric Các giá trị số mà các phép toán số học được thực hiện. |
2 | Character Giá trị chữ và số đại diện cho các ký tự đơn hoặc chuỗi ký tự. |
3 | Boolean Các giá trị logic mà các phép toán logic được thực hiện. |
4 | Datetime Ngày và giờ. |
PL / SQL cung cấp các kiểu con của kiểu dữ liệu. Ví dụ, kiểu dữ liệu NUMBER có một kiểu con gọi là INTEGER. Bạn có thể sử dụng các kiểu con trong chương trình PL / SQL của mình để làm cho kiểu dữ liệu tương thích với kiểu dữ liệu trong các chương trình khác trong khi nhúng mã PL / SQL vào chương trình khác, chẳng hạn như chương trình Java.
Kiểu dữ liệu số PL / SQL và kiểu con
Bảng sau liệt kê các kiểu dữ liệu số được xác định trước PL / SQL và các kiểu con của chúng -
S.Không | Loại dữ liệu và mô tả |
---|---|
1 | PLS_INTEGER Số nguyên có dấu trong phạm vi -2,147,483,648 đến 2,147,483,647, được biểu diễn bằng 32 bit |
2 | BINARY_INTEGER Số nguyên có dấu trong phạm vi -2,147,483,648 đến 2,147,483,647, được biểu diễn bằng 32 bit |
3 | BINARY_FLOAT Số dấu phẩy động định dạng IEEE 754 chính xác đơn |
4 | BINARY_DOUBLE Số dấu phẩy động định dạng IEEE 754 chính xác kép |
5 | NUMBER(prec, scale) Số dấu phẩy động hoặc dấu phẩy động có giá trị tuyệt đối trong phạm vi từ 1E-130 đến (nhưng không bao gồm) 1.0E126. Một biến NUMBER cũng có thể đại diện cho 0 |
6 | DEC(prec, scale) Loại điểm cố định cụ thể ANSI với độ chính xác tối đa là 38 chữ số thập phân |
7 | DECIMAL(prec, scale) Loại điểm cố định cụ thể của IBM với độ chính xác tối đa là 38 chữ số thập phân |
số 8 | NUMERIC(pre, secale) Loại nổi với độ chính xác tối đa là 38 chữ số thập phân |
9 | DOUBLE PRECISION Loại dấu phẩy động cụ thể ANSI với độ chính xác tối đa là 126 chữ số nhị phân (khoảng 38 chữ số thập phân) |
10 | FLOAT ANSI và kiểu dấu phẩy động cụ thể của IBM với độ chính xác tối đa là 126 chữ số nhị phân (khoảng 38 chữ số thập phân) |
11 | INT Loại số nguyên cụ thể ANSI với độ chính xác tối đa là 38 chữ số thập phân |
12 | INTEGER ANSI và kiểu số nguyên cụ thể của IBM với độ chính xác tối đa là 38 chữ số thập phân |
13 | SMALLINT ANSI và kiểu số nguyên cụ thể của IBM với độ chính xác tối đa là 38 chữ số thập phân |
14 | REAL Loại dấu phẩy động với độ chính xác tối đa là 63 chữ số nhị phân (khoảng 18 chữ số thập phân) |
Sau đây là một khai báo hợp lệ -
DECLARE
num1 INTEGER;
num2 REAL;
num3 DOUBLE PRECISION;
BEGIN
null;
END;
/
Khi đoạn mã trên được biên dịch và thực thi, nó tạo ra kết quả sau:
PL/SQL procedure successfully completed
Kiểu dữ liệu ký tự PL / SQL và kiểu con
Sau đây là chi tiết về các kiểu dữ liệu ký tự được xác định trước PL / SQL và các kiểu con của chúng:
S.Không | Loại dữ liệu và mô tả |
---|---|
1 | CHAR Chuỗi ký tự có độ dài cố định với kích thước tối đa là 32,767 byte |
2 | VARCHAR2 Chuỗi ký tự có độ dài thay đổi với kích thước tối đa là 32,767 byte |
3 | RAW Chuỗi nhị phân hoặc byte có độ dài thay đổi với kích thước tối đa là 32,767 byte, không được giải thích bằng PL / SQL |
4 | NCHAR Chuỗi ký tự quốc gia có độ dài cố định với kích thước tối đa là 32,767 byte |
5 | NVARCHAR2 Chuỗi ký tự quốc gia có độ dài thay đổi với kích thước tối đa là 32,767 byte |
6 | LONG Chuỗi ký tự có độ dài thay đổi với kích thước tối đa là 32.760 byte |
7 | LONG RAW Chuỗi nhị phân hoặc chuỗi byte có độ dài thay đổi với kích thước tối đa là 32,760 byte, không được giải thích bằng PL / SQL |
số 8 | ROWID Định danh hàng vật lý, địa chỉ của một hàng trong một bảng thông thường |
9 | UROWID Định danh hàng chung (định danh hàng vật lý, lôgic hoặc ngoại lai) |
Các kiểu dữ liệu Boolean PL / SQL
Các BOOLEANkiểu dữ liệu lưu trữ các giá trị logic được sử dụng trong các phép toán logic. Các giá trị logic là các giá trị BooleanTRUE và FALSE và giá trị NULL.
Tuy nhiên, SQL không có kiểu dữ liệu tương đương với BOOLEAN. Do đó, các giá trị Boolean không thể được sử dụng trong:
- Câu lệnh SQL
- Các hàm SQL tích hợp sẵn (chẳng hạn như TO_CHAR)
- Các hàm PL / SQL được gọi từ các câu lệnh SQL
PL / SQL kiểu ngày giờ và khoảng thời gian
Các DATEkiểu dữ liệu được sử dụng để lưu trữ lịch ngày dài cố định, bao gồm thời gian trong ngày tính bằng giây kể từ nửa đêm. Ngày có hiệu lực từ ngày 1 tháng 1 năm 4712 trước Công nguyên đến ngày 31 tháng 12 năm 9999 sau Công nguyên.
Định dạng ngày mặc định được đặt bởi tham số khởi tạo Oracle NLS_DATE_FORMAT. Ví dụ: mặc định có thể là 'DD-MON-YY', bao gồm một số có hai chữ số cho ngày trong tháng, chữ viết tắt của tên tháng và hai chữ số cuối cùng của năm. Ví dụ: 01-OCT-12.
Mỗi DATE bao gồm thế kỷ, năm, tháng, ngày, giờ, phút và giây. Bảng sau đây hiển thị các giá trị hợp lệ cho mỗi trường:
Tên trường | Giá trị ngày giờ hợp lệ | Giá trị khoảng thời gian hợp lệ |
---|---|---|
NĂM | -4712 đến 9999 (không bao gồm năm 0) | Mọi số nguyên khác không |
THÁNG | 01 đến 12 | 0 đến 11 |
NGÀY | 01 đến 31 (giới hạn bởi các giá trị của THÁNG và NĂM, theo quy tắc của lịch cho ngôn ngữ) | Mọi số nguyên khác không |
GIỜ | 00 đến 23 | 0 đến 23 |
PHÚT | 00 đến 59 | 0 đến 59 |
THỨ HAI | 00 đến 59,9 (n), trong đó 9 (n) là độ chính xác của giây phân số thời gian | 0 đến 59,9 (n), trong đó 9 (n) là độ chính xác của khoảng phân số giây |
TIMEZONE_HOUR | -12 đến 14 (phạm vi có thể thay đổi thời gian tiết kiệm ánh sáng ban ngày) | Không áp dụng |
TIMEZONE_MINUTE | 00 đến 59 | Không áp dụng |
TIMEZONE_REGION | Tìm thấy trong chế độ xem hiệu suất động V $ TIMEZONE_NAMES | Không áp dụng |
TIMEZONE_ABBR | Tìm thấy trong chế độ xem hiệu suất động V $ TIMEZONE_NAMES | Không áp dụng |
Kiểu dữ liệu đối tượng lớn PL / SQL (LOB)
Kiểu dữ liệu Đối tượng Lớn (LOB) đề cập đến các mục dữ liệu lớn như văn bản, hình ảnh đồ họa, video clip và dạng sóng âm thanh. Kiểu dữ liệu LOB cho phép truy cập hiệu quả, ngẫu nhiên, từng phần vào dữ liệu này. Sau đây là các kiểu dữ liệu PL / SQL LOB được xác định trước:
Loại dữ liệu | Sự miêu tả | Kích thước |
---|---|---|
NỀN | Được sử dụng để lưu trữ các đối tượng nhị phân lớn trong các tệp hệ điều hành bên ngoài cơ sở dữ liệu. | Phụ thuộc vào hệ thống. Không được vượt quá 4 gigabyte (GB). |
BÃI | Được sử dụng để lưu trữ các đối tượng nhị phân lớn trong cơ sở dữ liệu. | 8 đến 128 terabyte (TB) |
CLOB | Được sử dụng để lưu trữ các khối dữ liệu ký tự lớn trong cơ sở dữ liệu. | 8 đến 128 TB |
NCLOB | Được sử dụng để lưu trữ các khối dữ liệu NCHAR lớn trong cơ sở dữ liệu. | 8 đến 128 TB |
Các kiểu con do người dùng xác định PL / SQL
Kiểu con là một tập con của kiểu dữ liệu khác, được gọi là kiểu cơ sở của nó. Kiểu con có các phép toán hợp lệ giống như kiểu cơ sở của nó, nhưng chỉ là một tập hợp con các giá trị hợp lệ của nó.
PL / SQL xác định trước một số kiểu con trong gói STANDARD. Ví dụ: PL / SQL xác định trước các kiểu conCHARACTER và INTEGER như sau -
SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);
Bạn có thể xác định và sử dụng các kiểu phụ của riêng mình. Chương trình sau đây minh họa việc xác định và sử dụng kiểu con do người dùng xác định:
DECLARE
SUBTYPE name IS char(20);
SUBTYPE message IS varchar2(100);
salutation name;
greetings message;
BEGIN
salutation := 'Reader ';
greetings := 'Welcome to the World of PL/SQL';
dbms_output.put_line('Hello ' || salutation || greetings);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Hello Reader Welcome to the World of PL/SQL
PL/SQL procedure successfully completed.
NULL trong PL / SQL
Giá trị PL / SQL NULL đại diện missing hoặc là unknown datavà chúng không phải là một số nguyên, một ký tự hoặc bất kỳ kiểu dữ liệu cụ thể nào khác. Lưu ý rằngNULL không giống như một chuỗi dữ liệu trống hoặc giá trị ký tự rỗng '\0'. Một null có thể được gán nhưng nó không thể được đánh đồng với bất cứ thứ gì, kể cả chính nó.
Trong chương này, chúng ta sẽ thảo luận về các biến trong Pl / SQL. Một biến không là gì ngoài tên được đặt cho một vùng lưu trữ mà chương trình của chúng ta có thể thao tác. Mỗi biến trong PL / SQL có một kiểu dữ liệu cụ thể, kiểu dữ liệu này xác định kích thước và cách bố trí bộ nhớ của biến; phạm vi giá trị có thể được lưu trữ trong bộ nhớ đó và tập hợp các thao tác có thể được áp dụng cho biến.
Tên của một biến PL / SQL bao gồm một chữ cái theo sau là nhiều chữ cái, chữ số, dấu đô la, dấu gạch dưới và dấu số và không được vượt quá 30 ký tự. Theo mặc định, tên biến không phân biệt chữ hoa chữ thường. Bạn không thể sử dụng từ khóa PL / SQL dành riêng làm tên biến.
Ngôn ngữ lập trình PL / SQL cho phép xác định nhiều kiểu biến khác nhau, chẳng hạn như kiểu dữ liệu ngày giờ, bản ghi, tập hợp, v.v. mà chúng ta sẽ đề cập trong các chương tiếp theo. Đối với chương này, chúng ta hãy chỉ nghiên cứu các loại biến cơ bản.
Khai báo biến trong PL / SQL
Các biến PL / SQL phải được khai báo trong phần khai báo hoặc trong một gói như một biến toàn cục. Khi bạn khai báo một biến, PL / SQL cấp phát bộ nhớ cho giá trị của biến đó và vị trí lưu trữ được xác định bằng tên biến.
Cú pháp để khai báo một biến là:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Trong đó, tên_biến là mã định danh hợp lệ trong PL / SQL, kiểu dữ liệu phải là kiểu dữ liệu PL / SQL hợp lệ hoặc bất kỳ kiểu dữ liệu nào do người dùng xác định mà chúng ta đã thảo luận trong chương trước. Một số khai báo biến hợp lệ cùng với định nghĩa của chúng được hiển thị bên dưới:
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);
Khi bạn cung cấp giới hạn kích thước, tỷ lệ hoặc độ chính xác với loại dữ liệu, nó được gọi là constrained declaration. Khai báo bị ràng buộc yêu cầu ít bộ nhớ hơn so với khai báo không bị giới hạn. Ví dụ -
sales number(10, 2);
name varchar2(25);
address varchar2(100);
Khởi tạo các biến trong PL / SQL
Bất cứ khi nào bạn khai báo một biến, PL / SQL sẽ gán cho nó một giá trị mặc định là NULL. Nếu bạn muốn khởi tạo một biến có giá trị khác với giá trị NULL, bạn có thể làm như vậy trong khi khai báo, sử dụng một trong hai cách sau:
Các DEFAULT từ khóa
Các assignment nhà điều hành
Ví dụ -
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';
Bạn cũng có thể chỉ định rằng một biến không được có NULL giá trị bằng cách sử dụng NOT NULLhạn chế. Nếu bạn sử dụng ràng buộc NOT NULL, bạn phải chỉ định rõ ràng một giá trị ban đầu cho biến đó.
Thực hành lập trình tốt là khởi tạo các biến đúng cách, nếu không, đôi khi các chương trình sẽ tạo ra kết quả không mong muốn. Hãy thử ví dụ sau sử dụng nhiều loại biến khác nhau:
DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
/
Khi đoạn mã trên được thực thi, nó tạo ra kết quả sau:
Value of c: 30
Value of f: 23.333333333333333333
PL/SQL procedure successfully completed.
Phạm vi biến trong PL / SQL
PL / SQL cho phép lồng các khối, tức là mỗi khối chương trình có thể chứa một khối bên trong khác. Nếu một biến được khai báo trong một khối bên trong, thì nó sẽ không thể truy cập vào khối bên ngoài. Tuy nhiên, nếu một biến được khai báo và có thể truy cập vào một khối bên ngoài, thì biến đó cũng có thể truy cập được đối với tất cả các khối bên trong lồng nhau. Có hai loại phạm vi thay đổi -
Local variables - Các biến được khai báo trong một khối bên trong và không thể truy cập vào khối bên ngoài.
Global variables - Các biến được khai báo trong khối ngoài cùng hoặc một gói.
Ví dụ sau cho thấy việc sử dụng Local và Global các biến ở dạng đơn giản của nó -
DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
/
Khi đoạn mã trên được thực thi, nó tạo ra kết quả sau:
Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185
PL/SQL procedure successfully completed.
Gán kết quả truy vấn SQL cho các biến PL / SQL
Bạn có thể dùng SELECT INTOcâu lệnh SQL để gán giá trị cho các biến PL / SQL. Đối với mỗi mục trongSELECT list, phải có một biến tương ứng, tương thích với kiểu trong INTO list. Ví dụ sau minh họa khái niệm này. Hãy để chúng tôi tạo một bảng có tên CUSTOMERS -
(For SQL statements, please refer to the SQL tutorial)
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
Table Created
Bây giờ chúng ta hãy chèn một số giá trị vào bảng -
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
Chương trình sau chỉ định các giá trị từ bảng trên cho các biến PL / SQL bằng cách sử dụng SELECT INTO clause của SQL -
DECLARE
c_id customers.id%type := 1;
c_name customers.name%type;
c_addr customers.address%type;
c_sal customers.salary%type;
BEGIN
SELECT name, address, salary INTO c_name, c_addr, c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line
('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/
Khi đoạn mã trên được thực thi, nó tạo ra kết quả sau:
Customer Ramesh from Ahmedabad earns 2000
PL/SQL procedure completed successfully
Trong chương này, chúng ta sẽ thảo luận về constants và literalstrong PL / SQL. Một hằng giữ một giá trị đã từng được khai báo, không thay đổi trong chương trình. Một khai báo hằng chỉ định tên, kiểu dữ liệu và giá trị của nó và phân bổ dung lượng cho nó. Tuyên bố cũng có thể áp đặtNOT NULL constraint.
Khai báo một hằng số
Một hằng số được khai báo bằng cách sử dụng CONSTANTtừ khóa. Nó yêu cầu một giá trị ban đầu và không cho phép thay đổi giá trị đó. Ví dụ -
PI CONSTANT NUMBER := 3.141592654;
DECLARE
-- constant declaration
pi constant number := 3.141592654;
-- other declarations
radius number(5,2);
dia number(5,2);
circumference number(7, 2);
area number (10, 2);
BEGIN
-- processing
radius := 9.5;
dia := radius * 2;
circumference := 2.0 * pi * radius;
area := pi * radius * radius;
-- output
dbms_output.put_line('Radius: ' || radius);
dbms_output.put_line('Diameter: ' || dia);
dbms_output.put_line('Circumference: ' || circumference);
dbms_output.put_line('Area: ' || area);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Radius: 9.5
Diameter: 19
Circumference: 59.69
Area: 283.53
Pl/SQL procedure successfully completed.
Chữ viết PL / SQL
Một ký tự là một giá trị số, ký tự, chuỗi hoặc Boolean rõ ràng không được đại diện bởi một mã định danh. Ví dụ, TRUE, 786, NULL, 'tutorialspoint' đều là các ký tự kiểu Boolean, số hoặc chuỗi. PL / SQL, các chữ có phân biệt chữ hoa chữ thường. PL / SQL hỗ trợ các loại chữ sau:
- Chữ số
- Ký tự chữ
- Chuỗi chữ
- Chữ viết của BOOLEAN
- Chữ viết ngày và giờ
Bảng sau đây cung cấp các ví dụ từ tất cả các loại giá trị chữ này.
S.Không | Loại & Ví dụ theo nghĩa đen |
---|---|
1 | Numeric Literals 050 78 -14 0 +32767 6,66667 0,0 -12,0 3,14159 +7800,00 6E5 1,0E-8 3,14159e0 -1E38 -9,5e-3 |
2 | Character Literals 'A' '%' '9' '' 'z' '(' |
3 | String Literals 'Chào thế giới!' 'Điểm hướng dẫn' '19 -NOV-12 ' |
4 | BOOLEAN Literals TRUE, FALSE và NULL. |
5 | Date and Time Literals NGÀY '1978-12-25'; TIMESTAMP '2012-10-29 12:01:01'; |
Để nhúng các dấu ngoặc kép trong một chuỗi ký tự, hãy đặt hai dấu nháy đơn cạnh nhau như được hiển thị trong chương trình sau:
DECLARE
message varchar2(30):= 'That''s tutorialspoint.com!';
BEGIN
dbms_output.put_line(message);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
That's tutorialspoint.com!
PL/SQL procedure successfully completed.
Trong chương này, chúng ta sẽ thảo luận về các toán tử trong PL / SQL. Một toán tử là một ký hiệu yêu cầu trình biên dịch thực hiện các thao tác toán học hoặc logic cụ thể. Ngôn ngữ PL / SQL có nhiều toán tử cài sẵn và cung cấp các loại toán tử sau:
- Toán tử số học
- Toán tử quan hệ
- Toán tử so sánh
- Toán tử logic
- Toán tử chuỗi
Ở đây, chúng ta sẽ hiểu từng toán tử số học, quan hệ, so sánh và logic. Các toán tử chuỗi sẽ được thảo luận trong chương sau -PL/SQL - Strings.
Toán tử số học
Bảng sau hiển thị tất cả các toán tử số học được PL / SQL hỗ trợ. Hãy để chúng tôi giả sửvariable A giữ 10 và variable B giữ 5, sau đó -
Hiển thị các ví dụ
Nhà điều hành | Sự miêu tả | Thí dụ |
---|---|---|
+ | Thêm hai toán hạng | A + B sẽ cho 15 |
- | Trừ toán hạng thứ hai với toán hạng đầu tiên | A - B sẽ cho 5 |
* | Nhân cả hai toán hạng | A * B sẽ cho 50 |
/ | Chia tử số cho tử số | A / B sẽ cho 2 |
** | Toán tử lũy thừa, nâng một toán hạng lên lũy thừa của toán hạng khác | A ** B sẽ cho 100000 |
Toán tử quan hệ
Toán tử quan hệ so sánh hai biểu thức hoặc giá trị và trả về kết quả Boolean. Bảng sau đây cho thấy tất cả các toán tử quan hệ được PL / SQL hỗ trợ. Hãy để chúng tôi giả sửvariable A giữ 10 và variable B giữ 20, sau đó -
Hiển thị các ví dụ
Nhà điều hành | Sự miêu tả | Thí dụ |
---|---|---|
= | Kiểm tra xem giá trị của hai toán hạng có bằng nhau hay không, nếu có thì điều kiện trở thành true. | (A = B) không đúng. |
! = <> ~ = |
Kiểm tra xem giá trị của hai toán hạng có bằng nhau hay không, nếu các giá trị không bằng nhau thì điều kiện trở thành true. | (A! = B) là đúng. |
> | Kiểm tra xem giá trị của toán hạng bên trái có lớn hơn giá trị của toán hạng bên phải hay không, nếu có thì điều kiện trở thành true. | (A> B) là không đúng. |
< | Kiểm tra xem giá trị của toán hạng bên trái có nhỏ hơn giá trị của toán hạng bên phải hay không, nếu có thì điều kiện trở thành true. | (A <B) là đúng. |
> = | Kiểm tra xem giá trị của toán hạng bên trái có lớn hơn hoặc bằng giá trị của toán hạng bên phải hay không, nếu có thì điều kiện trở thành true. | (A> = B) là không đúng. |
<= | Kiểm tra xem giá trị của toán hạng bên trái có nhỏ hơn hoặc bằng giá trị của toán hạng bên phải hay không, nếu có thì điều kiện trở thành true. | (A <= B) là đúng |
Toán tử so sánh
Toán tử so sánh được sử dụng để so sánh một biểu thức này với một biểu thức khác. Kết quả luôn làTRUE, FALSE hoặc là NULL.
Hiển thị các ví dụ
Nhà điều hành | Sự miêu tả | Thí dụ |
---|---|---|
GIỐNG | Toán tử LIKE so sánh một ký tự, chuỗi hoặc giá trị CLOB với một mẫu và trả về TRUE nếu giá trị khớp với mẫu và FALSE nếu không. | Nếu 'Zara Ali' như 'Z% A_i' trả về giá trị Boolean đúng, trong khi 'Nuha Ali' như 'Z% A_i' trả về giá trị Boolean false. |
GIỮA | Toán tử BETWEEN kiểm tra xem một giá trị có nằm trong một phạm vi xác định hay không. x GIỮA a VÀ b nghĩa là x> = a và x <= b. | Nếu x = 10 thì x từ 5 đến 20 trả về true, x từ 5 đến 10 trả về true, nhưng x từ 11 đến 20 trả về false. |
TRONG | Kiểm tra toán tử IN thiết lập tư cách thành viên. x IN (tập hợp) có nghĩa là x bằng với bất kỳ thành viên nào của tập hợp. | Nếu x = 'm' thì x in ('a', 'b', 'c') trả về Boolean false nhưng x in ('m', 'n', 'o') trả về Boolean true. |
LÀ KHÔNG | Toán tử IS NULL trả về giá trị BOOLEAN TRUE nếu toán hạng của nó là NULL hoặc FALSE nếu không phải là NULL. Các phép so sánh liên quan đến giá trị NULL luôn mang lại NULL. | Nếu x = 'm', thì 'x là null' trả về Boolean false. |
Toán tử logic
Bảng sau đây cho thấy các toán tử logic được hỗ trợ bởi PL / SQL. Tất cả các toán tử này hoạt động trên toán hạng Boolean và tạo ra kết quả Boolean. Hãy để chúng tôi giả sửvariable A đúng và variable B giữ sai, sau đó -
Hiển thị các ví dụ
Nhà điều hành | Sự miêu tả | Ví dụ |
---|---|---|
và | Được gọi là toán tử logic AND. Nếu cả hai toán hạng đều đúng thì điều kiện trở thành đúng. | (A và B) là sai. |
hoặc là | Được gọi là Toán tử HOẶC logic. Nếu bất kỳ toán hạng nào trong hai toán hạng là true thì điều kiện trở thành true. | (A hoặc B) là đúng. |
không phải | Được gọi là Toán tử NOT logic. Được sử dụng để đảo ngược trạng thái logic của toán hạng của nó. Nếu một điều kiện là đúng thì toán tử logic NOT sẽ làm cho nó sai. | không (A và B) là đúng. |
Ưu tiên toán tử PL / SQL
Mức độ ưu tiên của toán tử xác định nhóm các từ trong một biểu thức. Điều này ảnh hưởng đến cách một biểu thức được đánh giá. Các toán tử nhất định có quyền ưu tiên cao hơn những toán tử khác; ví dụ, toán tử nhân có quyền ưu tiên cao hơn toán tử cộng.
Ví dụ, x = 7 + 3 * 2; đây,x được gán 13, không phải 20 vì toán tử * có mức độ ưu tiên cao hơn +, do đó, nó đầu tiên được nhân với 3*2 và sau đó thêm vào 7.
Ở đây, các toán tử có mức độ ưu tiên cao nhất xuất hiện ở đầu bảng, những toán tử có mức độ ưu tiên thấp nhất xuất hiện ở cuối bảng. Trong một biểu thức, các toán tử có mức độ ưu tiên cao hơn sẽ được đánh giá đầu tiên.
Thứ tự ưu tiên của các toán tử như sau: =, <,>, <=,> =, <>,! =, ~ =, ^ =, IS NULL, LIKE, BETWEEN, IN.
Hiển thị các ví dụ
Nhà điều hành | Hoạt động |
---|---|
** | lũy thừa |
+, - | nhận dạng, phủ định |
*, / | nhân, chia |
+, -, || | cộng, trừ, nối |
sự so sánh | |
KHÔNG PHẢI | phủ định logic |
VÀ | kết hợp |
HOẶC LÀ | bao gồm |
Trong chương này, chúng ta sẽ thảo luận về các điều kiện trong PL / SQL. Cấu trúc ra quyết định yêu cầu người lập trình chỉ định một hoặc nhiều điều kiện để được đánh giá hoặc kiểm tra bởi chương trình, cùng với một câu lệnh hoặc các câu lệnh sẽ được thực thi nếu điều kiện được xác định là đúng, và tùy chọn, các câu lệnh khác sẽ được thực thi nếu điều kiện được xác định là sai.
Sau đây là dạng chung của cấu trúc có điều kiện (tức là ra quyết định) điển hình được tìm thấy trong hầu hết các ngôn ngữ lập trình:
Ngôn ngữ lập trình PL / SQL cung cấp các loại câu lệnh ra quyết định sau. Nhấp vào các liên kết sau để kiểm tra chi tiết của chúng.
S.Không | Tuyên bố & Mô tả |
---|---|
1 | Câu lệnh IF - THEN Các IF statement liên kết một điều kiện với một chuỗi các câu lệnh kèm theo các từ khóa THEN và END IF. Nếu điều kiện là đúng, các câu lệnh sẽ được thực thi và nếu điều kiện là sai hoặc NULL thì câu lệnh IF không làm gì cả. |
2 | Câu lệnh IF-THEN-ELSE IF statement thêm từ khóa ELSEtheo sau là một chuỗi câu lệnh thay thế. Nếu điều kiện là false hoặc NULL, thì chỉ chuỗi các câu lệnh thay thế được thực thi. Nó đảm bảo rằng một trong hai chuỗi câu lệnh được thực thi. |
3 | Câu lệnh IF-THEN-ELSIF Nó cho phép bạn chọn giữa một số lựa chọn thay thế. |
4 | Tuyên bố tình huống Giống như câu lệnh IF, CASE statement chọn một chuỗi các câu lệnh để thực thi. Tuy nhiên, để chọn chuỗi, câu lệnh CASE sử dụng một bộ chọn thay vì nhiều biểu thức Boolean. Bộ chọn là một biểu thức có giá trị được sử dụng để chọn một trong một số lựa chọn thay thế. |
5 | Câu lệnh CASE đã tìm kiếm Câu lệnh CASE được tìm kiếm has no selectorvà đó là mệnh đề WHEN chứa các điều kiện tìm kiếm mang lại giá trị Boolean. |
6 | IF-THEN-ELSE lồng nhau Bạn có thể sử dụng một IF-THEN hoặc là IF-THEN-ELSIF tuyên bố bên trong khác IF-THEN hoặc là IF-THEN-ELSIF các câu lệnh). |
Trong chương này, chúng ta sẽ thảo luận về Vòng lặp trong PL / SQL. Có thể có một tình huống khi bạn cần thực thi một khối mã nhiều lần. Nói chung, các câu lệnh được thực hiện tuần tự: Câu lệnh đầu tiên trong một hàm được thực hiện đầu tiên, tiếp theo là câu lệnh thứ hai, v.v.
Các ngôn ngữ lập trình cung cấp các cấu trúc điều khiển khác nhau cho phép các đường dẫn thực thi phức tạp hơn.
Câu lệnh lặp cho phép chúng ta thực hiện một câu lệnh hoặc một nhóm câu lệnh nhiều lần và sau đây là dạng chung của câu lệnh lặp trong hầu hết các ngôn ngữ lập trình:
PL / SQL cung cấp các loại vòng lặp sau để xử lý các yêu cầu lặp. Nhấp vào các liên kết sau để kiểm tra chi tiết của chúng.
S.Không | Loại vòng lặp & Mô tả |
---|---|
1 | PL / SQL cơ bản LOOP Trong cấu trúc vòng lặp này, chuỗi các câu lệnh được đặt giữa câu lệnh LOOP và câu lệnh END LOOP. Tại mỗi lần lặp, chuỗi các câu lệnh được thực thi và sau đó điều khiển tiếp tục ở đầu vòng lặp. |
2 | PL / SQL KHI LOOP Lặp lại một câu lệnh hoặc một nhóm câu lệnh trong khi một điều kiện đã cho là đúng. Nó kiểm tra điều kiện trước khi thực thi phần thân của vòng lặp. |
3 | PL / SQL ĐỂ LOOP Thực thi một chuỗi các câu lệnh nhiều lần và viết tắt mã quản lý biến vòng lặp. |
4 | Các vòng lặp lồng nhau trong PL / SQL Bạn có thể sử dụng một hoặc nhiều vòng lặp bên trong bất kỳ vòng lặp cơ bản nào khác, vòng lặp while hoặc for. |
Gắn nhãn vòng lặp PL / SQL
Các vòng lặp PL / SQL có thể được gắn nhãn. Nhãn phải được bao bởi dấu ngoặc kép (<< và >>) và xuất hiện ở đầu câu lệnh LOOP. Tên nhãn cũng có thể xuất hiện ở cuối câu lệnh LOOP. Bạn có thể sử dụng nhãn trong câu lệnh EXIT để thoát khỏi vòng lặp.
Chương trình sau đây minh họa khái niệm -
DECLARE
i number(1);
j number(1);
BEGIN
<< outer_loop >>
FOR i IN 1..3 LOOP
<< inner_loop >>
FOR j IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
END loop inner_loop;
END loop outer_loop;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
i is: 1 and j is: 1
i is: 1 and j is: 2
i is: 1 and j is: 3
i is: 2 and j is: 1
i is: 2 and j is: 2
i is: 2 and j is: 3
i is: 3 and j is: 1
i is: 3 and j is: 2
i is: 3 and j is: 3
PL/SQL procedure successfully completed.
Các tuyên bố kiểm soát vòng lặp
Các câu lệnh điều khiển vòng lặp thay đổi việc thực thi từ trình tự bình thường của nó. Khi việc thực thi rời khỏi một phạm vi, tất cả các đối tượng tự động được tạo trong phạm vi đó sẽ bị phá hủy.
PL / SQL hỗ trợ các câu lệnh điều khiển sau. Việc gắn nhãn các vòng lặp cũng giúp thực hiện việc kiểm soát bên ngoài một vòng lặp. Nhấp vào các liên kết sau để kiểm tra chi tiết của chúng.
S.Không | Tuyên bố & Mô tả Kiểm soát |
---|---|
1 | Tuyên bố EXIT Câu lệnh Exit hoàn thành vòng lặp và điều khiển được chuyển tới câu lệnh ngay sau khi KẾT THÚC LOOP. |
2 | Tuyên bố CONTINUE Làm cho vòng lặp bỏ qua phần còn lại của phần thân và ngay lập tức kiểm tra lại tình trạng của nó trước khi nhắc lại. |
3 | Tuyên bố GOTO Chuyển quyền điều khiển sang câu lệnh được gắn nhãn. Mặc dù không nên sử dụng câu lệnh GOTO trong chương trình của bạn. |
Chuỗi trong PL / SQL thực sự là một chuỗi ký tự với đặc điểm kỹ thuật kích thước tùy chọn. Các ký tự có thể là số, chữ cái, trống, ký tự đặc biệt hoặc kết hợp của tất cả. PL / SQL cung cấp ba loại chuỗi:
Fixed-length strings- Trong các chuỗi như vậy, người lập trình chỉ định độ dài trong khi khai báo chuỗi. Chuỗi được đệm bên phải với khoảng trắng có độ dài được chỉ định.
Variable-length strings - Trong các chuỗi như vậy, độ dài tối đa lên đến 32,767, cho chuỗi được chỉ định và không có phần đệm diễn ra.
Character large objects (CLOBs) - Đây là những chuỗi có độ dài thay đổi có thể lên đến 128 terabyte.
Chuỗi PL / SQL có thể là biến hoặc ký tự. Một chuỗi ký tự được đặt trong dấu ngoặc kép. Ví dụ,
'This is a string literal.' Or 'hello world'
Để bao gồm một dấu ngoặc kép bên trong một chuỗi ký tự, bạn cần nhập hai dấu ngoặc kép bên cạnh nhau. Ví dụ,
'this isn''t what it looks like'
Khai báo các biến chuỗi
Cơ sở dữ liệu Oracle cung cấp nhiều kiểu dữ liệu chuỗi, chẳng hạn như CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB và NCLOB. Các kiểu dữ liệu có tiền tố là'N' Chúng tôi 'national character set' kiểu dữ liệu, lưu trữ dữ liệu ký tự Unicode.
Nếu bạn cần khai báo một chuỗi có độ dài thay đổi, bạn phải cung cấp độ dài tối đa của chuỗi đó. Ví dụ: kiểu dữ liệu VARCHAR2. Ví dụ sau minh họa việc khai báo và sử dụng một số biến chuỗi:
DECLARE
name varchar2(20);
company varchar2(30);
introduction clob;
choice char(1);
BEGIN
name := 'John Smith';
company := 'Infotech';
introduction := ' Hello! I''m John Smith from Infotech.';
choice := 'y';
IF choice = 'y' THEN
dbms_output.put_line(name);
dbms_output.put_line(company);
dbms_output.put_line(introduction);
END IF;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
John Smith
Infotech
Hello! I'm John Smith from Infotech.
PL/SQL procedure successfully completed
Để khai báo một chuỗi có độ dài cố định, hãy sử dụng kiểu dữ liệu CHAR. Ở đây bạn không phải chỉ định độ dài tối đa cho một biến độ dài cố định. Nếu bạn không giới hạn độ dài, Cơ sở dữ liệu Oracle sẽ tự động sử dụng độ dài tối đa được yêu cầu. Hai khai báo sau đây giống hệt nhau:
red_flag CHAR(1) := 'Y';
red_flag CHAR := 'Y';
Các hàm và toán tử chuỗi PL / SQL
PL / SQL cung cấp toán tử nối (||)để nối hai chuỗi. Bảng sau cung cấp các hàm chuỗi được cung cấp bởi PL / SQL:
S.Không | Chức năng & Mục đích |
---|---|
1 | ASCII(x); Trả về giá trị ASCII của ký tự x. |
2 | CHR(x); Trả về ký tự có giá trị ASCII là x. |
3 | CONCAT(x, y); Nối các chuỗi x và y và trả về chuỗi được nối thêm. |
4 | INITCAP(x); Chuyển chữ cái đầu tiên của mỗi từ trong x thành chữ hoa và trả về chuỗi đó. |
5 | INSTR(x, find_string [, start] [, occurrence]); Tìm kiếm find_string trong x và trả về vị trí mà nó xảy ra. |
6 | INSTRB(x); Trả về vị trí của một chuỗi trong một chuỗi khác, nhưng trả về giá trị theo byte. |
7 | LENGTH(x); Trả về số ký tự trong x. |
số 8 | LENGTHB(x); Trả về độ dài của chuỗi ký tự tính bằng byte cho bộ ký tự byte đơn. |
9 | LOWER(x); Chuyển đổi các ký tự trong x thành chữ thường và trả về chuỗi đó. |
10 | LPAD(x, width [, pad_string]) ; Miếng đệm x với khoảng trắng ở bên trái, để nâng tổng chiều dài của chuỗi lên đến ký tự chiều rộng. |
11 | LTRIM(x [, trim_string]); Cắt các ký tự từ bên trái của x. |
12 | NANVL(x, value); Trả về giá trị nếu x khớp với giá trị đặc biệt NaN (không phải số), ngược lại x Được trả lại. |
13 | NLS_INITCAP(x); Giống như hàm INITCAP ngoại trừ việc nó có thể sử dụng một phương pháp sắp xếp khác như được chỉ định bởi NLSSORT. |
14 | NLS_LOWER(x) ; Giống như hàm LOWER ngoại trừ nó có thể sử dụng một phương pháp sắp xếp khác như được chỉ định bởi NLSSORT. |
15 | NLS_UPPER(x); Giống như hàm UPPER ngoại trừ nó có thể sử dụng một phương pháp sắp xếp khác như được chỉ định bởi NLSSORT. |
16 | NLSSORT(x); Thay đổi phương pháp sắp xếp các ký tự. Phải được chỉ định trước bất kỳ chức năng NLS nào; nếu không, sắp xếp mặc định sẽ được sử dụng. |
17 | NVL(x, value); Trả về giá trị nếu xlà null; ngược lại, x được trả về. |
18 | NVL2(x, value1, value2); Trả về giá trị1 nếu x không rỗng; nếu x là null, giá trị2 được trả về. |
19 | REPLACE(x, search_string, replace_string); Tìm kiếm x cho search_string và thay thế nó bằng Replace_string. |
20 | RPAD(x, width [, pad_string]); Miếng đệm x rẽ phải. |
21 | RTRIM(x [, trim_string]); Cắt tỉa x từ bên phải. |
22 | SOUNDEX(x) ; Trả về một chuỗi có chứa biểu diễn ngữ âm của x. |
23 | SUBSTR(x, start [, length]); Trả về một chuỗi con của xbắt đầu tại vị trí được chỉ định bởi bắt đầu. Có thể cung cấp độ dài tùy chọn cho chuỗi con. |
24 | SUBSTRB(x); Tương tự như SUBSTR ngoại trừ các tham số được thể hiện bằng byte thay vì ký tự cho hệ thống ký tự byte đơn. |
25 | TRIM([trim_char FROM) x); Cắt các ký tự từ bên trái và bên phải của x. |
26 | UPPER(x); Chuyển các chữ cái trong x thành chữ hoa và trả về chuỗi đó. |
Bây giờ chúng ta hãy làm một vài ví dụ để hiểu khái niệm -
ví dụ 1
DECLARE
greetings varchar2(11) := 'hello world';
BEGIN
dbms_output.put_line(UPPER(greetings));
dbms_output.put_line(LOWER(greetings));
dbms_output.put_line(INITCAP(greetings));
/* retrieve the first character in the string */
dbms_output.put_line ( SUBSTR (greetings, 1, 1));
/* retrieve the last character in the string */
dbms_output.put_line ( SUBSTR (greetings, -1, 1));
/* retrieve five characters,
starting from the seventh position. */
dbms_output.put_line ( SUBSTR (greetings, 7, 5));
/* retrieve the remainder of the string,
starting from the second position. */
dbms_output.put_line ( SUBSTR (greetings, 2));
/* find the location of the first "e" */
dbms_output.put_line ( INSTR (greetings, 'e'));
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
HELLO WORLD
hello world
Hello World
h
d
World
ello World
2
PL/SQL procedure successfully completed.
Ví dụ 2
DECLARE
greetings varchar2(30) := '......Hello World.....';
BEGIN
dbms_output.put_line(RTRIM(greetings,'.'));
dbms_output.put_line(LTRIM(greetings, '.'));
dbms_output.put_line(TRIM( '.' from greetings));
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
......Hello World
Hello World.....
Hello World
PL/SQL procedure successfully completed.
Trong chương này, chúng ta sẽ thảo luận về mảng trong PL / SQL. Ngôn ngữ lập trình PL / SQL cung cấp một cấu trúc dữ liệu được gọi làVARRAY, có thể lưu trữ tập hợp tuần tự có kích thước cố định của các phần tử cùng loại. Một varray được sử dụng để lưu trữ một tập hợp dữ liệu có thứ tự, tuy nhiên, tốt hơn là bạn nên coi một mảng là một tập hợp các biến cùng kiểu.
Tất cả các varrays bao gồm các vị trí bộ nhớ liền nhau. Địa chỉ thấp nhất tương ứng với phần tử đầu tiên và địa chỉ cao nhất cho phần tử cuối cùng.
Mảng là một phần của dữ liệu kiểu tập hợp và nó là viết tắt của mảng có kích thước thay đổi. Chúng ta sẽ nghiên cứu các kiểu tập hợp khác trong chương sau'PL/SQL Collections'.
Mỗi phần tử trong một varraycó một chỉ mục được liên kết với nó. Nó cũng có kích thước tối đa có thể được thay đổi động.
Tạo kiểu Varray
Một kiểu varray được tạo với CREATE TYPEtuyên bố. Bạn phải chỉ định kích thước tối đa và loại phần tử được lưu trữ trong varray.
Cú pháp cơ bản để tạo kiểu VARRAY ở cấp lược đồ là:
CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>
Ở đâu,
- varray_type_name là tên thuộc tính hợp lệ,
- n là số phần tử (tối đa) trong varray,
- element_type là kiểu dữ liệu của các phần tử của mảng.
Kích thước tối đa của một varray có thể được thay đổi bằng cách sử dụng ALTER TYPE tuyên bố.
Ví dụ,
CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);
/
Type created.
Cú pháp cơ bản để tạo kiểu VARRAY trong khối PL / SQL là:
TYPE varray_type_name IS VARRAY(n) of <element_type>
Ví dụ -
TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;
Bây giờ chúng ta hãy làm một vài ví dụ để hiểu khái niệm -
ví dụ 1
Chương trình sau minh họa việc sử dụng varrays:
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || '
Marks: ' || marks(i));
END LOOP;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Total 5 Students
Student: Kavita Marks: 98
Student: Pritam Marks: 97
Student: Ayan Marks: 78
Student: Rishav Marks: 87
Student: Aziz Marks: 92
PL/SQL procedure successfully completed.
Please note -
Trong môi trường Oracle, chỉ số bắt đầu cho các varrays luôn là 1.
Bạn có thể khởi tạo các phần tử varray bằng phương thức khởi tạo của kiểu varray, có cùng tên với varray.
Varrays là mảng một chiều.
Một varray tự động là NULL khi nó được khai báo và phải được khởi tạo trước khi các phần tử của nó có thể được tham chiếu.
Ví dụ 2
Các phần tử của một varray cũng có thể là% ROWTYPE của bất kỳ bảng cơ sở dữ liệu nào hoặc% TYPE của bất kỳ trường bảng cơ sở dữ liệu nào. Ví dụ sau minh họa khái niệm này.
Chúng tôi sẽ sử dụng bảng CUSTOMERS được lưu trữ trong cơ sở dữ liệu của chúng tôi dưới dạng:
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
Ví dụ sau làm cho việc sử dụng cursor, mà bạn sẽ nghiên cứu chi tiết trong một chương riêng biệt.
DECLARE
CURSOR c_customers is
SELECT name FROM customers;
type c_list is varray (6) of customers.name%type;
name_list c_list := c_list();
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter + 1;
name_list.extend;
name_list(counter) := n.name;
dbms_output.put_line('Customer('||counter ||'):'||name_list(counter));
END LOOP;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completed.
Trong chương này, chúng ta sẽ thảo luận về các Thủ tục trong PL / SQL. Asubprogramlà một đơn vị / mô-đun chương trình thực hiện một nhiệm vụ cụ thể. Các chương trình con này được kết hợp với nhau để tạo thành các chương trình lớn hơn. Về cơ bản đây được gọi là 'Thiết kế mô-đun'. Một chương trình con có thể được gọi bởi một chương trình con hoặc chương trình khác được gọi làcalling program.
Một chương trình con có thể được tạo -
- Ở cấp giản đồ
- Bên trong một gói
- Bên trong một khối PL / SQL
Ở cấp độ lược đồ, chương trình con là standalone subprogram. Nó được tạo bằng câu lệnh CREATE PROCEDURE hoặc câu lệnh CREATE FUNCTION. Nó được lưu trữ trong cơ sở dữ liệu và có thể bị xóa bằng câu lệnh DROP PROCEDURE hoặc DROP FUNCTION.
Một chương trình con được tạo bên trong một gói là packaged subprogram. Nó được lưu trữ trong cơ sở dữ liệu và chỉ có thể bị xóa khi gói bị xóa bằng câu lệnh DROP PACKAGE. Chúng ta sẽ thảo luận về các gói trong chương'PL/SQL - Packages'.
Chương trình con PL / SQL được đặt tên là các khối PL / SQL có thể được gọi với một tập các tham số. PL / SQL cung cấp hai loại chương trình con:
Functions- Các chương trình con này trả về một giá trị duy nhất; chủ yếu được sử dụng để tính toán và trả về một giá trị.
Procedures- Các chương trình con này không trực tiếp trả về giá trị; chủ yếu được sử dụng để thực hiện một hành động.
Chương này sẽ trình bày các khía cạnh quan trọng của một PL/SQL procedure. Chúng ta sẽ thảo luậnPL/SQL function trong chương tiếp theo.
Các phần của chương trình con PL / SQL
Mỗi chương trình con PL / SQL có một tên và cũng có thể có một danh sách tham số. Giống như các khối PL / SQL ẩn danh, các khối được đặt tên cũng sẽ có ba phần sau:
S.Không | Bộ phận & Mô tả |
---|---|
1 | Declarative Part Nó là một phần không bắt buộc. Tuy nhiên, phần khai báo cho chương trình con không bắt đầu bằng từ khóa DECLARE. Nó chứa các khai báo kiểu, con trỏ, hằng, biến, ngoại lệ và chương trình con lồng nhau. Các mục này là cục bộ của chương trình con và không còn tồn tại khi chương trình con hoàn thành việc thực thi. |
2 | Executable Part Đây là phần bắt buộc và chứa các câu lệnh thực hiện hành động được chỉ định. |
3 | Exception-handling Đây lại là một phần không bắt buộc. Nó chứa mã xử lý lỗi thời gian chạy. |
Tạo một thủ tục
Một thủ tục được tạo với CREATE OR REPLACE PROCEDUREtuyên bố. Cú pháp đơn giản cho câu lệnh CREATE OR REPLACE PROCEDURE như sau:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
Ở đâu,
procedure-name chỉ định tên của thủ tục.
Tùy chọn [HOẶC THAY THẾ] cho phép sửa đổi quy trình hiện có.
Danh sách tham số tùy chọn chứa tên, chế độ và các loại tham số. IN đại diện cho giá trị sẽ được truyền từ bên ngoài và OUT đại diện cho tham số sẽ được sử dụng để trả về một giá trị bên ngoài thủ tục.
thân thủ tục chứa phần thực thi.
Từ khóa AS được sử dụng thay cho từ khóa IS để tạo một quy trình độc lập.
Thí dụ
Ví dụ sau tạo một thủ tục đơn giản hiển thị chuỗi 'Hello World!' trên màn hình khi thực thi.
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
Khi đoạn mã trên được thực thi bằng lời nhắc SQL, nó sẽ tạo ra kết quả sau:
Procedure created.
Thực hiện một quy trình độc lập
Một thủ tục độc lập có thể được gọi theo hai cách:
Sử dụng EXECUTE từ khóa
Gọi tên của thủ tục từ một khối PL / SQL
Thủ tục trên có tên 'greetings' có thể được gọi với từ khóa EXECUTE là -
EXECUTE greetings;
Cuộc gọi trên sẽ hiển thị -
Hello World
PL/SQL procedure successfully completed.
Thủ tục cũng có thể được gọi từ một khối PL / SQL khác -
BEGIN
greetings;
END;
/
Cuộc gọi trên sẽ hiển thị -
Hello World
PL/SQL procedure successfully completed.
Xóa một thủ tục độc lập
Một thủ tục độc lập bị xóa với DROP PROCEDUREtuyên bố. Cú pháp để xóa một thủ tục là:
DROP PROCEDURE procedure-name;
Bạn có thể bỏ quy trình chào hỏi bằng cách sử dụng câu lệnh sau:
DROP PROCEDURE greetings;
Chế độ tham số trong chương trình con PL / SQL
Bảng sau liệt kê các chế độ tham số trong chương trình con PL / SQL:
S.Không | Chế độ & Mô tả Thông số |
---|---|
1 | IN Tham số IN cho phép bạn truyền một giá trị vào chương trình con. It is a read-only parameter. Bên trong chương trình con, một tham số IN hoạt động giống như một hằng số. Nó không thể được chỉ định một giá trị. Bạn có thể chuyển một hằng số, nghĩa đen, biến khởi tạo hoặc biểu thức dưới dạng tham số IN. Bạn cũng có thể khởi tạo nó thành một giá trị mặc định; tuy nhiên, trong trường hợp đó, nó bị bỏ qua khỏi cuộc gọi chương trình con.It is the default mode of parameter passing. Parameters are passed by reference. |
2 | OUT Tham số OUT trả về một giá trị cho chương trình đang gọi. Bên trong chương trình con, một tham số OUT hoạt động giống như một biến. Bạn có thể thay đổi giá trị của nó và tham chiếu giá trị sau khi gán nó.The actual parameter must be variable and it is passed by value. |
3 | IN OUT An IN OUTtham số truyền một giá trị ban đầu cho một chương trình con và trả về một giá trị cập nhật cho trình gọi. Nó có thể được gán một giá trị và giá trị đó có thể được đọc. Tham số thực tế tương ứng với tham số chính thức IN OUT phải là một biến, không phải là một hằng số hoặc một biểu thức. Tham số chính thức phải được gán một giá trị.Actual parameter is passed by value. |
Chế độ IN & OUT Ví dụ 1
Chương trình này tìm giá trị nhỏ nhất của hai giá trị. Ở đây, thủ tục nhận hai số bằng chế độ IN và trả về giá trị tối thiểu của chúng bằng cách sử dụng các tham số OUT.
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Minimum of (23, 45) : 23
PL/SQL procedure successfully completed.
Chế độ IN & OUT Ví dụ 2
Thủ tục này tính bình phương giá trị của một giá trị được truyền vào. Ví dụ này cho thấy cách chúng ta có thể sử dụng cùng một tham số để chấp nhận một giá trị và sau đó trả về một kết quả khác.
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Square of (23): 529
PL/SQL procedure successfully completed.
Phương pháp truyền tham số
Các tham số thực tế có thể được chuyển theo ba cách:
- Ký hiệu vị trí
- Ký hiệu được đặt tên
- Ký hiệu hỗn hợp
Ký hiệu vị trí
Trong ký hiệu vị trí, bạn có thể gọi thủ tục là -
findMin(a, b, c, d);
Trong ký hiệu vị trí, tham số thực tế đầu tiên được thay thế cho tham số hình thức đầu tiên; tham số thực tế thứ hai được thay thế cho tham số chính thức thứ hai, v.v. Vì thế,a được thay thế cho x, b được thay thế cho y, c được thay thế cho z và d được thay thế cho m.
Ký hiệu được đặt tên
Trong ký hiệu được đặt tên, tham số thực được liên kết với tham số chính thức bằng cách sử dụng arrow symbol ( => ). Lệnh gọi thủ tục sẽ giống như sau:
findMin(x => a, y => b, z => c, m => d);
Ký hiệu hỗn hợp
Trong ký hiệu hỗn hợp, bạn có thể kết hợp cả hai ký hiệu trong cuộc gọi thủ tục; tuy nhiên, ký hiệu vị trí phải đứng trước ký hiệu được đặt tên.
Cuộc gọi sau đây là hợp pháp -
findMin(a, b, c, m => d);
Tuy nhiên, điều này không hợp pháp:
findMin(x => a, b, c, d);
Trong chương này, chúng ta sẽ thảo luận về các hàm trong PL / SQL. Một hàm giống như một thủ tục ngoại trừ nó trả về một giá trị. Do đó, tất cả các thảo luận của chương trước cũng đúng cho các hàm.
Tạo một chức năng
Một chức năng độc lập được tạo bằng cách sử dụng CREATE FUNCTIONtuyên bố. Cú pháp đơn giản choCREATE OR REPLACE PROCEDURE tuyên bố như sau:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Ở đâu,
function-name chỉ định tên của hàm.
Tùy chọn [HOẶC THAY THẾ] cho phép sửa đổi một chức năng hiện có.
Danh sách tham số tùy chọn chứa tên, chế độ và các loại tham số. IN đại diện cho giá trị sẽ được truyền từ bên ngoài và OUT đại diện cho tham số sẽ được sử dụng để trả về một giá trị bên ngoài thủ tục.
Hàm phải chứa một return tuyên bố.
Các RETURN khoản quy định cụ thể gõ dữ liệu bạn đang đi để trở về từ hàm.
function-body chứa phần thực thi.
Từ khóa AS được sử dụng thay cho từ khóa IS để tạo một hàm độc lập.
Thí dụ
Ví dụ sau minh họa cách tạo và gọi một hàm độc lập. Hàm này trả về tổng số KHÁCH HÀNG trong bảng khách hàng.
Chúng ta sẽ sử dụng bảng CUSTOMERS, mà chúng ta đã tạo trong chương Biến PL / SQL -
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
/
Khi đoạn mã trên được thực thi bằng lời nhắc SQL, nó sẽ tạo ra kết quả sau:
Function created.
Gọi một hàm
Trong khi tạo một hàm, bạn đưa ra định nghĩa về những gì hàm phải làm. Để sử dụng một hàm, bạn sẽ phải gọi hàm đó để thực hiện tác vụ đã xác định. Khi một chương trình gọi một chức năng, điều khiển chương trình được chuyển đến chức năng được gọi.
Một hàm được gọi thực hiện tác vụ đã xác định và khi câu lệnh trả về của nó được thực thi hoặc khi last end statement đạt được, nó trả lại điều khiển chương trình trở lại chương trình chính.
Để gọi một hàm, bạn chỉ cần chuyển các tham số cần thiết cùng với tên hàm và nếu hàm trả về một giá trị, thì bạn có thể lưu trữ giá trị trả về. Chương trình sau gọi hàmtotalCustomers từ một khối ẩn danh -
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Total no. of Customers: 6
PL/SQL procedure successfully completed.
Thí dụ
Ví dụ sau minh họa Khai báo, Định nghĩa và Gọi một hàm PL / SQL đơn giản để tính toán và trả về tối đa hai giá trị.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Maximum of (23,45): 45
PL/SQL procedure successfully completed.
Các hàm đệ quy PL / SQL
Chúng ta đã thấy rằng một chương trình hoặc chương trình con có thể gọi một chương trình con khác. Khi một chương trình con gọi chính nó, nó được gọi là một cuộc gọi đệ quy và quá trình này được gọi làrecursion.
Để minh họa khái niệm, chúng ta hãy tính giai thừa của một số. Giai thừa của một số n được định nghĩa là -
n! = n*(n-1)!
= n*(n-1)*(n-2)!
...
= n*(n-1)*(n-2)*(n-3)... 1
Chương trình sau đây tính giai thừa của một số nhất định bằng cách gọi chính nó một cách đệ quy:
DECLARE
num number;
factorial number;
FUNCTION fact(x number)
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;
BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Factorial 6 is 720
PL/SQL procedure successfully completed.
Trong chương này, chúng ta sẽ thảo luận về các con trỏ trong PL / SQL. Oracle tạo một vùng bộ nhớ, được gọi là vùng ngữ cảnh, để xử lý một câu lệnh SQL, vùng này chứa tất cả thông tin cần thiết để xử lý câu lệnh; ví dụ: số lượng hàng được xử lý, v.v.
A cursorlà một con trỏ đến khu vực ngữ cảnh này. PL / SQL điều khiển vùng ngữ cảnh thông qua con trỏ. Một con trỏ giữ các hàng (một hoặc nhiều) được trả về bởi một câu lệnh SQL. Tập hợp các hàng mà con trỏ giữ được gọi làactive set.
Bạn có thể đặt tên cho một con trỏ để nó có thể được tham chiếu đến trong một chương trình để tìm nạp và xử lý các hàng được trả về bởi câu lệnh SQL, từng hàng một. Có hai loại con trỏ -
- Con trỏ ngầm
- Con trỏ rõ ràng
Con trỏ ngầm
Các con trỏ ngầm được Oracle tạo tự động bất cứ khi nào một câu lệnh SQL được thực thi, khi không có con trỏ rõ ràng cho câu lệnh. Người lập trình không thể kiểm soát các con trỏ ngầm và thông tin trong đó.
Bất cứ khi nào một câu lệnh DML (INSERT, UPDATE và DELETE) được đưa ra, một con trỏ ngầm được liên kết với câu lệnh này. Đối với các thao tác INSERT, con trỏ giữ dữ liệu cần được chèn. Đối với các hoạt động CẬP NHẬT và XÓA, con trỏ xác định các hàng sẽ bị ảnh hưởng.
Trong PL / SQL, bạn có thể tham khảo con trỏ ngầm gần đây nhất là SQL cursor, luôn có các thuộc tính như %FOUND, %ISOPEN, %NOTFOUNDvà %ROWCOUNT. Con trỏ SQL có các thuộc tính bổ sung,%BULK_ROWCOUNT và %BULK_EXCEPTIONS, được thiết kế để sử dụng với FORALLtuyên bố. Bảng sau cung cấp mô tả về các thuộc tính được sử dụng nhiều nhất:
S.Không | Thuộc tính & Mô tả |
---|---|
1 | %FOUND Trả về TRUE nếu câu lệnh INSERT, UPDATE hoặc DELETE ảnh hưởng đến một hoặc nhiều hàng hoặc câu lệnh SELECT INTO trả về một hoặc nhiều hàng. Nếu không, nó trả về FALSE. |
2 | %NOTFOUND Đối lập logic với% FOUND. Nó trả về TRUE nếu câu lệnh INSERT, UPDATE hoặc DELETE không ảnh hưởng đến không có hàng hoặc câu lệnh SELECT INTO không trả về hàng nào. Nếu không, nó trả về FALSE. |
3 | %ISOPEN Luôn trả về FALSE cho các con trỏ không tường minh, vì Oracle tự động đóng con trỏ SQL sau khi thực hiện câu lệnh SQL liên quan của nó. |
4 | %ROWCOUNT Trả về số hàng bị ảnh hưởng bởi câu lệnh INSERT, UPDATE hoặc DELETE hoặc được trả về bởi câu lệnh SELECT INTO. |
Mọi thuộc tính con trỏ SQL sẽ được truy cập dưới dạng sql%attribute_name như thể hiện bên dưới trong ví dụ.
Thí dụ
Chúng ta sẽ sử dụng bảng CUSTOMERS mà chúng ta đã tạo và sử dụng trong các chương trước.
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
Chương trình sau sẽ cập nhật bảng và tăng lương của mỗi khách hàng thêm 500 và sử dụng SQL%ROWCOUNT để xác định số lượng hàng bị ảnh hưởng -
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
6 customers selected
PL/SQL procedure successfully completed.
Nếu bạn kiểm tra các bản ghi trong bảng khách hàng, bạn sẽ thấy rằng các hàng đã được cập nhật -
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+
Con trỏ rõ ràng
Con trỏ rõ ràng là con trỏ do lập trình viên xác định để giành được nhiều quyền kiểm soát hơn đối với context area. Một con trỏ rõ ràng nên được định nghĩa trong phần khai báo của Khối PL / SQL. Nó được tạo trên Câu lệnh SELECT trả về nhiều hơn một hàng.
Cú pháp để tạo một con trỏ rõ ràng là:
CURSOR cursor_name IS select_statement;
Làm việc với một con trỏ rõ ràng bao gồm các bước sau:
- Khai báo con trỏ để khởi tạo bộ nhớ
- Mở con trỏ để cấp phát bộ nhớ
- Tìm nạp con trỏ để truy xuất dữ liệu
- Đóng con trỏ để giải phóng bộ nhớ được cấp phát
Khai báo con trỏ
Khai báo con trỏ xác định con trỏ với tên và câu lệnh SELECT liên quan. Ví dụ -
CURSOR c_customers IS
SELECT id, name, address FROM customers;
Mở con trỏ
Việc mở con trỏ sẽ phân bổ bộ nhớ cho con trỏ và làm cho nó sẵn sàng để tìm nạp các hàng được câu lệnh SQL trả về vào đó. Ví dụ, chúng ta sẽ mở con trỏ được xác định ở trên như sau:
OPEN c_customers;
Tìm nạp con trỏ
Tìm nạp con trỏ liên quan đến việc truy cập từng hàng một. Ví dụ, chúng tôi sẽ tìm nạp các hàng từ con trỏ đã mở ở trên như sau:
FETCH c_customers INTO c_id, c_name, c_addr;
Đóng con trỏ
Đóng con trỏ có nghĩa là giải phóng bộ nhớ được cấp phát. Ví dụ, chúng ta sẽ đóng con trỏ đã mở ở trên như sau:
CLOSE c_customers;
Thí dụ
Sau đây là một ví dụ đầy đủ để minh họa các khái niệm về con trỏ rõ ràng & minua;
DECLARE
c_id customers.id%type;
c_name customer.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
PL/SQL procedure successfully completed.
Trong chương này, chúng ta sẽ thảo luận về Bản ghi trong PL / SQL. Arecordlà một cấu trúc dữ liệu có thể chứa các mục dữ liệu khác nhau. Bản ghi bao gồm các trường khác nhau, tương tự như một hàng của bảng cơ sở dữ liệu.
Ví dụ: bạn muốn theo dõi sách của mình trong thư viện. Bạn có thể muốn theo dõi các thuộc tính sau của từng cuốn sách, chẳng hạn như Tên sách, Tác giả, Chủ đề, ID sách. Bản ghi có chứa một trường cho mỗi mục này cho phép coi SÁCH như một đơn vị logic và cho phép bạn tổ chức và trình bày thông tin của nó theo cách tốt hơn.
PL / SQL có thể xử lý các loại bản ghi sau:
- Table-based
- Bản ghi dựa trên con trỏ
- Bản ghi do người dùng xác định
Bản ghi dựa trên bảng
Thuộc tính% ROWTYPE cho phép lập trình viên tạo table-based và cursorbased Hồ sơ.
Ví dụ sau minh họa khái niệm table-basedHồ sơ. Chúng tôi sẽ sử dụng bảng CUSTOMERS mà chúng tôi đã tạo và sử dụng trong các chương trước -
DECLARE
customer_rec customers%rowtype;
BEGIN
SELECT * into customer_rec
FROM customers
WHERE id = 5;
dbms_output.put_line('Customer ID: ' || customer_rec.id);
dbms_output.put_line('Customer Name: ' || customer_rec.name);
dbms_output.put_line('Customer Address: ' || customer_rec.address);
dbms_output.put_line('Customer Salary: ' || customer_rec.salary);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Customer ID: 5
Customer Name: Hardik
Customer Address: Bhopal
Customer Salary: 9000
PL/SQL procedure successfully completed.
Bản ghi dựa trên con trỏ
Ví dụ sau minh họa khái niệm cursor-basedHồ sơ. Chúng tôi sẽ sử dụng bảng CUSTOMERS mà chúng tôi đã tạo và sử dụng trong các chương trước -
DECLARE
CURSOR customer_cur is
SELECT id, name, address
FROM customers;
customer_rec customer_cur%rowtype;
BEGIN
OPEN customer_cur;
LOOP
FETCH customer_cur into customer_rec;
EXIT WHEN customer_cur%notfound;
DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);
END LOOP;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
1 Ramesh
2 Khilan
3 kaushik
4 Chaitali
5 Hardik
6 Komal
PL/SQL procedure successfully completed.
Bản ghi do người dùng xác định
PL / SQL cung cấp kiểu bản ghi do người dùng xác định cho phép bạn xác định các cấu trúc bản ghi khác nhau. Các bản ghi này bao gồm các trường khác nhau. Giả sử bạn muốn theo dõi sách của mình trong thư viện. Bạn có thể muốn theo dõi các thuộc tính sau về mỗi cuốn sách -
- Title
- Author
- Subject
- Mã sách
Xác định bản ghi
Loại bản ghi được định nghĩa là -
TYPE
type_name IS RECORD
( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION],
field_name2 datatype2 [NOT NULL] [:= DEFAULT EXPRESSION],
...
field_nameN datatypeN [NOT NULL] [:= DEFAULT EXPRESSION);
record-name type_name;
Bản ghi Sách được khai báo theo cách sau:
DECLARE
TYPE books IS RECORD
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;
Truy cập các trường
Để truy cập bất kỳ trường nào của bản ghi, chúng tôi sử dụng dấu chấm (.)nhà điều hành. Toán tử truy cập thành viên được mã hóa là khoảng thời gian giữa tên biến bản ghi và trường mà chúng ta muốn truy cập. Sau đây là một ví dụ để giải thích việc sử dụng bản ghi:
DECLARE
type books is record
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;
BEGIN
-- Book 1 specification
book1.title := 'C Programming';
book1.author := 'Nuha Ali ';
book1.subject := 'C Programming Tutorial';
book1.book_id := 6495407;
-- Book 2 specification
book2.title := 'Telecom Billing';
book2.author := 'Zara Ali';
book2.subject := 'Telecom Billing Tutorial';
book2.book_id := 6495700;
-- Print book 1 record
dbms_output.put_line('Book 1 title : '|| book1.title);
dbms_output.put_line('Book 1 author : '|| book1.author);
dbms_output.put_line('Book 1 subject : '|| book1.subject);
dbms_output.put_line('Book 1 book_id : ' || book1.book_id);
-- Print book 2 record
dbms_output.put_line('Book 2 title : '|| book2.title);
dbms_output.put_line('Book 2 author : '|| book2.author);
dbms_output.put_line('Book 2 subject : '|| book2.subject);
dbms_output.put_line('Book 2 book_id : '|| book2.book_id);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Book 1 title : C Programming
Book 1 author : Nuha Ali
Book 1 subject : C Programming Tutorial
Book 1 book_id : 6495407
Book 2 title : Telecom Billing
Book 2 author : Zara Ali
Book 2 subject : Telecom Billing Tutorial
Book 2 book_id : 6495700
PL/SQL procedure successfully completed.
Ghi lại dưới dạng tham số chương trình con
Bạn có thể truyền một bản ghi dưới dạng tham số chương trình con giống như bạn truyền bất kỳ biến nào khác. Bạn cũng có thể truy cập các trường bản ghi theo cách giống như cách bạn truy cập trong ví dụ trên -
DECLARE
type books is record
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;
PROCEDURE printbook (book books) IS
BEGIN
dbms_output.put_line ('Book title : ' || book.title);
dbms_output.put_line('Book author : ' || book.author);
dbms_output.put_line( 'Book subject : ' || book.subject);
dbms_output.put_line( 'Book book_id : ' || book.book_id);
END;
BEGIN
-- Book 1 specification
book1.title := 'C Programming';
book1.author := 'Nuha Ali ';
book1.subject := 'C Programming Tutorial';
book1.book_id := 6495407;
-- Book 2 specification
book2.title := 'Telecom Billing';
book2.author := 'Zara Ali';
book2.subject := 'Telecom Billing Tutorial';
book2.book_id := 6495700;
-- Use procedure to print book info
printbook(book1);
printbook(book2);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Book title : C Programming
Book author : Nuha Ali
Book subject : C Programming Tutorial
Book book_id : 6495407
Book title : Telecom Billing
Book author : Zara Ali
Book subject : Telecom Billing Tutorial
Book book_id : 6495700
PL/SQL procedure successfully completed.
Trong chương này, chúng ta sẽ thảo luận về các Ngoại lệ trong PL / SQL. Ngoại lệ là một điều kiện lỗi trong quá trình thực thi chương trình. PL / SQL hỗ trợ các lập trình viên nắm bắt các điều kiện như vậy bằng cách sử dụngEXCEPTIONtrong chương trình và một hành động thích hợp được thực hiện đối với tình trạng lỗi. Có hai loại ngoại lệ -
- Các ngoại lệ do hệ thống xác định
- Các ngoại lệ do người dùng xác định
Cú pháp xử lý ngoại lệ
Cú pháp chung để xử lý ngoại lệ như sau. Tại đây, bạn có thể liệt kê nhiều trường hợp ngoại lệ nhất có thể. Ngoại lệ mặc định sẽ được xử lý bằng cách sử dụngWHEN others THEN -
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
Thí dụ
Hãy để chúng tôi viết một đoạn mã để minh họa khái niệm. Chúng ta sẽ sử dụng bảng CUSTOMERS mà chúng ta đã tạo và sử dụng trong các chương trước -
DECLARE
c_id customers.id%type := 8;
c_name customerS.Name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
No such customer!
PL/SQL procedure successfully completed.
Chương trình trên hiển thị tên và địa chỉ của khách hàng có ID được cung cấp. Vì không có khách hàng nào có giá trị ID 8 trong cơ sở dữ liệu của chúng tôi, chương trình sẽ tăng ngoại lệ thời gian chạyNO_DATA_FOUND, được chụp trong EXCEPTION block.
Nâng cao ngoại lệ
Các ngoại lệ được máy chủ cơ sở dữ liệu tự động nâng lên bất cứ khi nào có bất kỳ lỗi cơ sở dữ liệu nội bộ nào, nhưng lập trình viên có thể nêu ra các ngoại lệ một cách rõ ràng bằng cách sử dụng lệnh RAISE. Sau đây là cú pháp đơn giản để tăng một ngoại lệ:
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
Bạn có thể sử dụng cú pháp trên để nâng cao ngoại lệ tiêu chuẩn Oracle hoặc bất kỳ ngoại lệ nào do người dùng xác định. Trong phần tiếp theo, chúng tôi sẽ cung cấp cho bạn một ví dụ về việc nâng cao một ngoại lệ do người dùng xác định. Bạn có thể nâng cao các ngoại lệ tiêu chuẩn Oracle theo cách tương tự.
Ngoại lệ do người dùng xác định
PL / SQL cho phép bạn xác định các ngoại lệ của riêng bạn theo nhu cầu của chương trình của bạn. Một ngoại lệ do người dùng xác định phải được khai báo và sau đó được nêu ra một cách rõ ràng, sử dụng câu lệnh RAISE hoặc thủ tụcDBMS_STANDARD.RAISE_APPLICATION_ERROR.
Cú pháp để khai báo một ngoại lệ là:
DECLARE
my-exception EXCEPTION;
Thí dụ
Ví dụ sau minh họa khái niệm này. Chương trình này yêu cầu ID khách hàng, khi người dùng nhập ID không hợp lệ, ngoại lệinvalid_id được nuôi dưỡng.
DECLARE
c_id customers.id%type := &cc_id;
c_name customerS.Name%type;
c_addr customers.address%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Enter value for cc_id: -6 (let's enter a value -6)
old 2: c_id customers.id%type := &cc_id;
new 2: c_id customers.id%type := -6;
ID must be greater than zero!
PL/SQL procedure successfully completed.
Các ngoại lệ được xác định trước
PL / SQL cung cấp nhiều ngoại lệ được xác định trước, được thực thi khi bất kỳ quy tắc cơ sở dữ liệu nào bị chương trình vi phạm. Ví dụ: ngoại lệ xác định trước NO_DATA_FOUND được đưa ra khi câu lệnh SELECT INTO trả về không có hàng nào. Bảng sau liệt kê một số trường hợp ngoại lệ quan trọng được xác định trước:
ngoại lệ | Lỗi Oracle | SQLCODE | Sự miêu tả |
---|---|---|---|
ACCESS_INTO_NULL | 06530 | -6530 | Nó được nâng lên khi một đối tượng null được tự động gán một giá trị. |
CASE_NOT_FOUND | 06592 | -6592 | Nó được đưa ra khi không có lựa chọn nào trong mệnh đề WHEN của câu lệnh CASE được chọn và không có mệnh đề ELSE. |
COLLECTION_IS_NULL | 06531 | -6531 | Nó được nâng lên khi một chương trình cố gắng áp dụng các phương thức thu thập khác với EXISTS cho một bảng hoặc varray lồng nhau chưa được khởi tạo hoặc chương trình cố gắng gán giá trị cho các phần tử của một bảng hoặc varray lồng nhau chưa được khởi tạo. |
DUP_VAL_ON_INDEX | 00001 | -1 | Nó được nâng lên khi các giá trị trùng lặp được cố gắng lưu trữ trong một cột có chỉ mục duy nhất. |
INVALID_CURSOR | 01001 | -1001 | Nó được nâng lên khi cố gắng thực hiện một thao tác con trỏ không được phép, chẳng hạn như đóng một con trỏ chưa mở. |
INVALID_NUMBER | 01722 | -1722 | Nó được nâng lên khi chuyển đổi một chuỗi ký tự thành một số không thành công vì chuỗi không đại diện cho một số hợp lệ. |
ĐĂNG NHẬP BỊ TỪ CHỐI | 01017 | -1017 | Nó được nâng lên khi một chương trình cố gắng đăng nhập vào cơ sở dữ liệu bằng tên người dùng hoặc mật khẩu không hợp lệ. |
KHÔNG TÌM THẤY DỮ LIỆU NÀO | 01403 | +100 | Nó được nâng lên khi câu lệnh SELECT INTO trả về không có hàng nào. |
NOT_LOGGED_ON | 01012 | -1012 | Nó được nâng lên khi một lệnh gọi cơ sở dữ liệu được đưa ra mà không được kết nối với cơ sở dữ liệu. |
PROGRAM_ERROR | 06501 | -6501 | Nó được nêu ra khi PL / SQL có vấn đề nội bộ. |
ROWTYPE_MISMATCH | 06504 | -6504 | Nó được nâng lên khi một con trỏ tìm nạp giá trị trong một biến có kiểu dữ liệu không tương thích. |
SELF_IS_NULL | 30625 | -30625 | Nó được đưa ra khi một phương thức thành viên được gọi, nhưng thể hiện của kiểu đối tượng không được khởi tạo. |
STORAGE_ERROR | 06500 | -6500 | Nó được nâng lên khi PL / SQL hết bộ nhớ hoặc bộ nhớ bị hỏng. |
TOO_MANY_ROWS | 01422 | -1422 | Nó được nâng lên khi một câu lệnh SELECT INTO trả về nhiều hơn một hàng. |
VALUE_ERROR | 06502 | -6502 | Nó được nâng lên khi xảy ra lỗi số học, chuyển đổi, cắt bớt hoặc sizeconstraint. |
ZERO_DIVIDE | 01476 | 1476 | Nó được nâng lên khi cố gắng chia một số cho không. |
Trong chương này, chúng ta sẽ thảo luận về các Trigger trong PL / SQL. Trình kích hoạt là các chương trình được lưu trữ, được tự động thực thi hoặc kích hoạt khi một số sự kiện xảy ra. Trên thực tế, các trình kích hoạt được viết ra để thực thi phản ứng với bất kỳ sự kiện nào sau đây:
A database manipulation (DML) câu lệnh (DELETE, INSERT hoặc UPDATE)
A database definition (DDL) câu lệnh (CREATE, ALTER hoặc DROP).
A database operation (MÁY CHỦ, LOGON, LOGOFF, STARTUP hoặc SHUTDOWN).
Trình kích hoạt có thể được xác định trên bảng, dạng xem, lược đồ hoặc cơ sở dữ liệu mà sự kiện được liên kết với.
Lợi ích của Trigger
Kích hoạt có thể được viết cho các mục đích sau:
- Tự động tạo một số giá trị cột dẫn xuất
- Thực thi tính toàn vẹn tham chiếu
- Ghi nhật ký sự kiện và lưu trữ thông tin về quyền truy cập bảng
- Auditing
- Sao chép đồng bộ các bảng
- Áp đặt ủy quyền bảo mật
- Ngăn chặn các giao dịch không hợp lệ
Tạo trình kích hoạt
Cú pháp để tạo trình kích hoạt là:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Ở đâu,
TẠO [HOẶC THAY THẾ] TRIGGER trigger_name - Tạo hoặc thay thế một trình kích hoạt hiện có bằng trigger_name .
{TRƯỚC | SAU | INSTEAD OF} - Điều này chỉ định thời điểm trình kích hoạt sẽ được thực thi. Mệnh đề INSTEAD OF được sử dụng để tạo trình kích hoạt trên một khung nhìn.
{CHÈN [HOẶC] | CẬP NHẬT [HOẶC] | DELETE} - Điều này chỉ định hoạt động DML.
[OF col_name] - Điều này chỉ định tên cột sẽ được cập nhật.
[ON table_name] - Tên này chỉ định tên của bảng được liên kết với trình kích hoạt.
[THAM KHẢO CŨ NHƯ o MỚI NHƯ n] - Điều này cho phép bạn tham khảo các giá trị mới và cũ cho các câu lệnh DML khác nhau, chẳng hạn như INSERT, UPDATE và DELETE.
[FOR EACH ROW] - Điều này chỉ định trình kích hoạt cấp hàng, tức là trình kích hoạt sẽ được thực thi cho mỗi hàng bị ảnh hưởng. Nếu không, trình kích hoạt sẽ thực thi chỉ một lần khi câu lệnh SQL được thực thi, được gọi là trình kích hoạt cấp bảng.
WHEN (điều kiện) - Điều này cung cấp điều kiện cho các hàng mà trình kích hoạt sẽ kích hoạt. Mệnh đề này chỉ hợp lệ đối với trình kích hoạt cấp hàng.
Thí dụ
Để bắt đầu, chúng ta sẽ sử dụng bảng CUSTOMERS mà chúng ta đã tạo và sử dụng trong các chương trước -
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
Chương trình sau tạo ra một row-levelkích hoạt cho bảng khách hàng sẽ kích hoạt các hoạt động CHÈN hoặc CẬP NHẬT hoặc XÓA được thực hiện trên bảng KHÁCH HÀNG. Trình kích hoạt này sẽ hiển thị chênh lệch lương giữa giá trị cũ và giá trị mới -
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Trigger created.
Những điểm sau đây cần được xem xét ở đây:
Tham chiếu CŨ và MỚI không có sẵn cho trình kích hoạt cấp bảng, thay vào đó bạn có thể sử dụng chúng cho trình kích hoạt cấp bản ghi.
Nếu bạn muốn truy vấn bảng trong cùng một trình kích hoạt, thì bạn nên sử dụng từ khóa SAU vì trình kích hoạt có thể truy vấn bảng hoặc thay đổi lại bảng chỉ sau khi áp dụng các thay đổi ban đầu và bảng trở lại trạng thái nhất quán.
Trình kích hoạt ở trên đã được viết theo cách mà nó sẽ kích hoạt trước bất kỳ thao tác XÓA hoặc CHÈN hoặc CẬP NHẬT nào trên bảng, nhưng bạn có thể viết trình kích hoạt của mình trên một hoặc nhiều thao tác, ví dụ TRƯỚC KHI XÓA, thao tác này sẽ kích hoạt bất cứ khi nào một bản ghi sẽ bị xóa bằng thao tác DELETE trên bảng.
Kích hoạt một Trigger
Hãy để chúng tôi thực hiện một số thao tác DML trên bảng CUSTOMERS. Đây là một câu lệnh INSERT, sẽ tạo một bản ghi mới trong bảng -
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );
Khi một bản ghi được tạo trong bảng CUSTOMERS, trình kích hoạt tạo ở trên, display_salary_changes sẽ được kích hoạt và nó sẽ hiển thị kết quả sau:
Old salary:
New salary: 7500
Salary difference:
Bởi vì đây là một kỷ lục mới, lương cũ không có sẵn và kết quả trên là vô hiệu. Bây giờ chúng ta hãy thực hiện một thao tác DML nữa trên bảng CUSTOMERS. Câu lệnh UPDATE sẽ cập nhật một bản ghi hiện có trong bảng -
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
Khi bản ghi được cập nhật trong bảng CUSTOMERS, trình kích hoạt tạo ở trên, display_salary_changes sẽ được kích hoạt và nó sẽ hiển thị kết quả sau:
Old salary: 1500
New salary: 2000
Salary difference: 500
Trong chương này, chúng ta sẽ thảo luận về các Gói trong PL / SQL. Gói là các đối tượng lược đồ nhóm các kiểu, biến và chương trình con PL / SQL có liên quan một cách logic.
Một gói sẽ có hai phần bắt buộc -
- Đặc điểm kỹ thuật gói
- Nội dung hoặc định nghĩa gói
Đặc điểm kỹ thuật gói
Đặc điểm kỹ thuật là giao diện của gói. Chỉ làDECLAREScác kiểu, biến, hằng số, ngoại lệ, con trỏ và chương trình con có thể được tham chiếu từ bên ngoài gói. Nói cách khác, nó chứa tất cả thông tin về nội dung của gói, nhưng không bao gồm mã cho các chương trình con.
Tất cả các đối tượng được đặt trong đặc tả được gọi publiccác đối tượng. Bất kỳ chương trình con nào không có trong đặc tả gói nhưng được mã hóa trong thân gói được gọi làprivate vật.
Đoạn mã sau cho thấy một đặc tả gói có một thủ tục duy nhất. Bạn có thể có nhiều biến toàn cục được xác định và nhiều thủ tục hoặc hàm bên trong một gói.
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Package created.
Gói cơ thể
Phần thân gói có mã cho các phương thức khác nhau được khai báo trong đặc tả gói và các khai báo riêng tư khác, được ẩn khỏi mã bên ngoài gói.
Các CREATE PACKAGE BODYCâu lệnh được sử dụng để tạo phần thân gói. Đoạn mã sau hiển thị khai báo nội dung gói chocust_salgói đã tạo ở trên. Tôi giả định rằng chúng tôi đã tạo bảng CUSTOMERS trong cơ sở dữ liệu của chúng tôi như đã đề cập trong chương PL / SQL - Biến .
CREATE OR REPLACE PACKAGE BODY cust_sal AS
PROCEDURE find_sal(c_id customers.id%TYPE) IS
c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END cust_sal;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Package body created.
Sử dụng các phần tử gói
Các phần tử gói (biến, thủ tục hoặc hàm) được truy cập bằng cú pháp sau:
package_name.element_name;
Hãy xem xét, chúng tôi đã tạo gói ở trên trong lược đồ cơ sở dữ liệu của chúng tôi, chương trình sau sử dụng find_sal phương pháp của cust_sal gói -
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó sẽ nhắc nhập ID khách hàng và khi bạn nhập ID, nó sẽ hiển thị mức lương tương ứng như sau:
Enter value for cc_id: 1
Salary: 3000
PL/SQL procedure successfully completed.
Thí dụ
Chương trình sau cung cấp một gói hoàn chỉnh hơn. Chúng tôi sẽ sử dụng bảng CUSTOMERS được lưu trữ trong cơ sở dữ liệu của chúng tôi với các bản ghi sau:
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 3000.00 |
| 2 | Khilan | 25 | Delhi | 3000.00 |
| 3 | kaushik | 23 | Kota | 3000.00 |
| 4 | Chaitali | 25 | Mumbai | 7500.00 |
| 5 | Hardik | 27 | Bhopal | 9500.00 |
| 6 | Komal | 22 | MP | 5500.00 |
+----+----------+-----+-----------+----------+
Đặc điểm kỹ thuật gói
CREATE OR REPLACE PACKAGE c_package AS
-- Adds a customer
PROCEDURE addCustomer(c_id customers.id%type,
c_name customerS.No.ame%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type);
-- Removes a customer
PROCEDURE delCustomer(c_id customers.id%TYPE);
--Lists all customers
PROCEDURE listCustomer;
END c_package;
/
Khi đoạn mã trên được thực thi tại dấu nhắc SQL, nó sẽ tạo ra gói trên và hiển thị kết quả sau:
Package created.
Tạo phần thân gói
CREATE OR REPLACE PACKAGE BODY c_package AS
PROCEDURE addCustomer(c_id customers.id%type,
c_name customerS.No.ame%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type)
IS
BEGIN
INSERT INTO customers (id,name,age,address,salary)
VALUES(c_id, c_name, c_age, c_addr, c_sal);
END addCustomer;
PROCEDURE delCustomer(c_id customers.id%type) IS
BEGIN
DELETE FROM customers
WHERE id = c_id;
END delCustomer;
PROCEDURE listCustomer IS
CURSOR c_customers is
SELECT name FROM customers;
TYPE c_list is TABLE OF customers.Name%type;
name_list c_list := c_list();
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list.extend;
name_list(counter) := n.name;
dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
END LOOP;
END listCustomer;
END c_package;
/
Ví dụ trên sử dụng nested table. Chúng ta sẽ thảo luận về khái niệm bảng lồng nhau trong chương tiếp theo.
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Package body created.
Sử dụng gói
Chương trình sau sử dụng các phương thức được khai báo và định nghĩa trong gói c_package .
DECLARE
code customers.id%type:= 8;
BEGIN
c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
c_package.listcustomer;
c_package.delcustomer(code);
c_package.listcustomer;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
PL/SQL procedure successfully completed
Trong chương này, chúng ta sẽ thảo luận về các Tập hợp trong PL / SQL. Tập hợp là một nhóm có thứ tự các phần tử có cùng kiểu dữ liệu. Mỗi phần tử được xác định bởi một chỉ số con duy nhất thể hiện vị trí của nó trong tập hợp.
PL / SQL cung cấp ba kiểu tập hợp:
- Lập chỉ mục theo bảng hoặc mảng liên kết
- Bảng lồng nhau
- Mảng kích thước thay đổi hoặc Varray
Tài liệu Oracle cung cấp các đặc điểm sau cho từng loại tập hợp:
Loại bộ sưu tập | Số phần tử | Loại chỉ số | Mật độ hoặc thưa thớt | Nơi tạo ra | Có thể là thuộc tính loại đối tượng |
---|---|---|---|---|---|
Mảng liên kết (hoặc bảng chỉ mục) | Không giới hạn | Chuỗi hoặc số nguyên | Hoặc | Chỉ trong khối PL / SQL | Không |
Bảng lồng nhau | Không giới hạn | Số nguyên | Bắt đầu dày đặc, có thể trở nên thưa thớt | Trong khối PL / SQL hoặc ở cấp lược đồ | Đúng |
Kích thước mảng biến đổi (Varray) | Bị ràng buộc | Số nguyên | Luôn dày đặc | Trong khối PL / SQL hoặc ở cấp lược đồ | Đúng |
Chúng ta đã thảo luận về varray trong chương 'PL/SQL arrays'. Trong chương này, chúng ta sẽ thảo luận về các bảng PL / SQL.
Cả hai loại bảng PL / SQL, tức là bảng theo chỉ mục và bảng lồng nhau có cấu trúc giống nhau và các hàng của chúng được truy cập bằng cách sử dụng ký hiệu chỉ số con. Tuy nhiên, hai loại bảng này khác nhau ở một khía cạnh; các bảng lồng nhau có thể được lưu trữ trong một cột cơ sở dữ liệu và các bảng theo chỉ mục thì không.
Chỉ mục theo bảng
An index-by bảng (còn được gọi là associative array) là một tập hợp của key-valuecặp. Mỗi khóa là duy nhất và được sử dụng để định vị giá trị tương ứng. Khóa có thể là một số nguyên hoặc một chuỗi.
Một bảng chỉ mục được tạo bằng cú pháp sau. Ở đây, chúng tôi đang tạoindex-by bảng tên table_name, các khóa trong đó sẽ thuộc loại chỉ số con và các giá trị được liên kết sẽ thuộc loại_phần tử
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
Thí dụ
Ví dụ sau cho thấy cách tạo một bảng để lưu trữ các giá trị số nguyên cùng với tên và sau đó nó sẽ in ra cùng một danh sách các tên.
DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish') := 62000;
salary_list('Minakshi') := 75000;
salary_list('Martin') := 100000;
salary_list('James') := 78000;
-- printing the table
name := salary_list.FIRST;
WHILE name IS NOT null LOOP
dbms_output.put_line
('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Salary of James is 78000
Salary of Martin is 100000
Salary of Minakshi is 75000
Salary of Rajnish is 62000
PL/SQL procedure successfully completed.
Thí dụ
Các phần tử của một bảng chỉ mục cũng có thể là %ROWTYPE của bất kỳ bảng cơ sở dữ liệu nào hoặc %TYPEcủa bất kỳ trường bảng cơ sở dữ liệu nào. Ví dụ sau minh họa khái niệm này. Chúng tôi sẽ sử dụngCUSTOMERS bảng được lưu trữ trong cơ sở dữ liệu của chúng tôi dưới dạng -
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
DECLARE
CURSOR c_customers is
select name from customers;
TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer;
name_list c_list;
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list(counter) := n.name;
dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter));
END LOOP;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completed
Bảng lồng nhau
A nested tablegiống như mảng một chiều với số phần tử tùy ý. Tuy nhiên, bảng lồng nhau khác với một mảng ở các khía cạnh sau:
Một mảng có một số phần tử được khai báo, nhưng một bảng lồng nhau thì không. Kích thước của một bảng lồng nhau có thể tăng động.
Một mảng luôn dày đặc, tức là nó luôn có các chỉ số con liên tiếp. Một mảng lồng nhau ban đầu dày đặc, nhưng nó có thể trở nên thưa thớt khi các phần tử bị xóa khỏi nó.
Một bảng lồng nhau được tạo bằng cú pháp sau:
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;
Khai báo này tương tự như khai báo một index-by bảng, nhưng không có INDEX BY mệnh đề.
Một bảng lồng nhau có thể được lưu trữ trong một cột cơ sở dữ liệu. Nó còn có thể được sử dụng để đơn giản hóa các hoạt động SQL trong đó bạn nối bảng một cột với một bảng lớn hơn. Một mảng kết hợp không thể được lưu trữ trong cơ sở dữ liệu.
Thí dụ
Các ví dụ sau minh họa việc sử dụng bảng lồng nhau:
DECLARE
TYPE names_table IS TABLE OF VARCHAR2(10);
TYPE grades IS TABLE OF INTEGER;
names names_table;
marks grades;
total integer;
BEGIN
names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i IN 1 .. total LOOP
dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
end loop;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92
PL/SQL procedure successfully completed.
Thí dụ
Các yếu tố của một nested table cũng có thể là một %ROWTYPEcủa bất kỳ bảng cơ sở dữ liệu nào hoặc% TYPE của bất kỳ trường bảng cơ sở dữ liệu nào. Ví dụ sau minh họa khái niệm này. Chúng tôi sẽ sử dụng bảng CUSTOMERS được lưu trữ trong cơ sở dữ liệu của chúng tôi dưới dạng:
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
DECLARE
CURSOR c_customers is
SELECT name FROM customers;
TYPE c_list IS TABLE of customerS.No.ame%type;
name_list c_list := c_list();
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list.extend;
name_list(counter) := n.name;
dbms_output.put_line('Customer('||counter||'):'||name_list(counter));
END LOOP;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completed.
Phương thức thu thập
PL / SQL cung cấp các phương thức thu thập được tích hợp sẵn giúp sử dụng tập hợp dễ dàng hơn. Bảng sau liệt kê các phương pháp và mục đích của chúng:
S.Không | Tên phương pháp & Mục đích |
---|---|
1 | EXISTS(n) Trả về TRUE nếu phần tử thứ n trong tập hợp tồn tại; nếu không trả về FALSE. |
2 | COUNT Trả về số phần tử mà một tập hợp hiện đang chứa. |
3 | LIMIT Kiểm tra kích thước tối đa của một bộ sưu tập. |
4 | FIRST Trả về các số chỉ mục đầu tiên (nhỏ nhất) trong một tập hợp sử dụng các chỉ số số nguyên. |
5 | LAST Trả về các số chỉ mục cuối cùng (lớn nhất) trong một tập hợp sử dụng các chỉ số số nguyên. |
6 | PRIOR(n) Trả về số chỉ mục đứng trước chỉ mục n trong một tập hợp. |
7 | NEXT(n) Trả về số chỉ mục kế tiếp chỉ mục n. |
số 8 | EXTEND Thêm một phần tử rỗng vào một tập hợp. |
9 | EXTEND(n) Thêm n phần tử rỗng vào một tập hợp. |
10 | EXTEND(n,i) Xuất hiện ncác bản sao của phần tử thứ i vào một tập hợp. |
11 | TRIM Xóa một phần tử khỏi phần cuối của bộ sưu tập. |
12 | TRIM(n) Loại bỏ n các phần tử từ cuối tập hợp. |
13 | DELETE Xóa tất cả các phần tử khỏi bộ sưu tập, đặt COUNT thành 0. |
14 | DELETE(n) Loại bỏ nthphần tử từ một mảng kết hợp với một khóa số hoặc một bảng lồng nhau. Nếu mảng kết hợp có khóa chuỗi, phần tử tương ứng với giá trị khóa sẽ bị xóa. Nếun là null, DELETE(n) Không lam gi cả. |
15 | DELETE(m,n) Loại bỏ tất cả các phần tử trong phạm vi m..ntừ một mảng kết hợp hoặc bảng lồng nhau. Nếum lớn hơn n hoặc nếu m hoặc là n là null, DELETE(m,n) Không lam gi cả. |
Ngoại lệ Bộ sưu tập
Bảng sau cung cấp các ngoại lệ của bộ sưu tập và khi nào chúng được nâng lên:
Ngoại lệ Bộ sưu tập | Lớn lên trong các tình huống |
---|---|
COLLECTION_IS_NULL | Bạn cố gắng vận hành trên một tập hợp rỗng nguyên tử. |
KHÔNG TÌM THẤY DỮ LIỆU NÀO | Một chỉ số phụ chỉ định một phần tử đã bị xóa hoặc một phần tử không tồn tại của một mảng kết hợp. |
SUBSCRIPT_BEYOND_COUNT | Chỉ số con vượt quá số phần tử trong một tập hợp. |
SUBSCRIPT_OUTSIDE_LIMIT | Chỉ số con nằm ngoài phạm vi cho phép. |
VALUE_ERROR | Chỉ số con là null hoặc không thể chuyển đổi thành loại khóa. Ngoại lệ này có thể xảy ra nếu khóa được xác định làPLS_INTEGER và chỉ số con nằm ngoài phạm vi này. |
Trong chương này, chúng ta sẽ thảo luận về các giao dịch trong PL / SQL. Một cơ sở dữ liệutransactionlà một đơn vị công việc nguyên tử có thể bao gồm một hoặc nhiều câu lệnh SQL liên quan. Nó được gọi là nguyên tử vì các sửa đổi cơ sở dữ liệu do các câu lệnh SQL tạo thành một giao dịch mang lại có thể được cam kết chung, tức là được thực hiện vĩnh viễn với cơ sở dữ liệu hoặc được khôi phục (hoàn tác) từ cơ sở dữ liệu.
Một câu lệnh SQL được thực thi thành công và một giao dịch đã cam kết không giống nhau. Ngay cả khi một câu lệnh SQL được thực thi thành công, trừ khi giao dịch chứa câu lệnh được cam kết, nó có thể được khôi phục và tất cả các thay đổi được thực hiện bởi (các) câu lệnh có thể được hoàn tác.
Bắt đầu và Kết thúc Giao dịch
Một giao dịch có một beginning và một end. Giao dịch bắt đầu khi một trong các sự kiện sau diễn ra:
Câu lệnh SQL đầu tiên được thực hiện sau khi kết nối với cơ sở dữ liệu.
Tại mỗi câu lệnh SQL mới được phát hành sau khi hoàn thành một giao dịch.
Giao dịch kết thúc khi một trong các sự kiện sau diễn ra:
A COMMIT hoặc một ROLLBACK tuyên bố được phát hành.
A DDL tuyên bố, chẳng hạn như CREATE TABLEtuyên bố, được phát hành; bởi vì trong trường hợp đó, một COMMIT được tự động thực hiện.
A DCL tuyên bố, chẳng hạn như một GRANTtuyên bố, được phát hành; bởi vì trong trường hợp đó, một COMMIT được tự động thực hiện.
Người dùng ngắt kết nối với cơ sở dữ liệu.
Người dùng thoát khỏi SQL*PLUS bằng cách phát hành EXIT lệnh COMMIT được thực hiện tự động.
SQL * Plus kết thúc bất thường, ROLLBACK được thực hiện tự động.
A DMLtuyên bố không thành công; trong trường hợp đó, một ROLLBACK được tự động thực hiện để hoàn tác câu lệnh DML đó.
Thực hiện một giao dịch
Một giao dịch được thực hiện vĩnh viễn bằng cách đưa ra lệnh SQL COMMIT. Cú pháp chung cho lệnh COMMIT là:
COMMIT;
Ví dụ,
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
COMMIT;
Giao dịch quay vòng
Các thay đổi được thực hiện đối với cơ sở dữ liệu mà không có COMMIT có thể được hoàn tác bằng lệnh ROLLBACK.
Cú pháp chung cho lệnh ROLLBACK là:
ROLLBACK [TO SAVEPOINT < savepoint_name>];
Khi một giao dịch bị hủy bỏ do một số tình huống chưa từng có, chẳng hạn như lỗi hệ thống, toàn bộ giao dịch kể từ khi cam kết sẽ tự động được khôi phục. Nếu bạn không sử dụngsavepoint, sau đó chỉ cần sử dụng câu lệnh sau để khôi phục tất cả các thay đổi:
ROLLBACK;
Điểm lưu
Điểm lưu là loại điểm đánh dấu giúp chia một giao dịch dài thành các đơn vị nhỏ hơn bằng cách thiết lập một số điểm kiểm tra. Bằng cách thiết lập các điểm lưu trong một giao dịch dài, bạn có thể quay trở lại điểm kiểm tra nếu cần. Điều này được thực hiện bằng cách phát hànhSAVEPOINT chỉ huy.
Cú pháp chung cho lệnh SAVEPOINT là:
SAVEPOINT < savepoint_name >;
Ví dụ
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 );
SAVEPOINT sav1;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000;
ROLLBACK TO sav1;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 7;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 8;
COMMIT;
ROLLBACK TO sav1 - Câu lệnh này khôi phục tất cả các thay đổi cho đến thời điểm mà bạn đã đánh dấu sav1 savepoint.
Sau đó, các thay đổi mới mà bạn thực hiện sẽ bắt đầu.
Kiểm soát giao dịch tự động
Để thực hiện một COMMIT tự động bất cứ khi nào INSERT, UPDATE hoặc là DELETE lệnh được thực thi, bạn có thể đặt AUTOCOMMIT biến môi trường là -
SET AUTOCOMMIT ON;
Bạn có thể tắt chế độ cam kết tự động bằng lệnh sau:
SET AUTOCOMMIT OFF;
Trong chương này, chúng ta sẽ thảo luận về Ngày và Giờ trong PL / SQL. Có hai lớp kiểu dữ liệu liên quan đến ngày và giờ trong PL / SQL -
- Các kiểu dữ liệu ngày giờ
- Các kiểu dữ liệu khoảng thời gian
Các kiểu dữ liệu Ngày giờ là -
- DATE
- TIMESTAMP
- TIMESTAMP VỚI Múi giờ
- TIMESTAMP VỚI VÙNG GIỜ ĐỊA PHƯƠNG
Các kiểu dữ liệu Khoảng thời gian là -
- INTERVAL NĂM ĐẾN THÁNG
- INTERVAL NGÀY ĐẾN THỨ HAI
Giá trị trường cho kiểu dữ liệu ngày giờ và khoảng thời gian
Cả hai datetime và interval kiểu dữ liệu bao gồm fields. Giá trị của các trường này xác định giá trị của kiểu dữ liệu. Bảng sau liệt kê các trường và các giá trị có thể có của chúng cho ngày giờ và khoảng thời gian.
Tên trường | Giá trị ngày giờ hợp lệ | Giá trị khoảng thời gian hợp lệ |
---|---|---|
NĂM | -4712 đến 9999 (không bao gồm năm 0) | Mọi số nguyên khác không |
THÁNG | 01 đến 12 | 0 đến 11 |
NGÀY | 01 đến 31 (giới hạn bởi các giá trị của THÁNG và NĂM, theo quy tắc của lịch cho ngôn ngữ) | Mọi số nguyên khác không |
GIỜ | 00 đến 23 | 0 đến 23 |
PHÚT | 00 đến 59 | 0 đến 59 |
THỨ HAI | 00 đến 59,9 (n), trong đó 9 (n) là độ chính xác của giây phân số thời gian Phần 9 (n) không áp dụng cho DATE. |
0 đến 59,9 (n), trong đó 9 (n) là độ chính xác của khoảng phân số giây |
TIMEZONE_HOUR | -12 đến 14 (phạm vi có thể thay đổi thời gian tiết kiệm ánh sáng ban ngày) Không áp dụng cho DATE hoặc TIMESTAMP. |
Không áp dụng |
TIMEZONE_MINUTE | 00 đến 59 Không áp dụng cho DATE hoặc TIMESTAMP. |
Không áp dụng |
TIMEZONE_REGION | Không áp dụng cho DATE hoặc TIMESTAMP. | Không áp dụng |
TIMEZONE_ABBR | Không áp dụng cho DATE hoặc TIMESTAMP. | Không áp dụng |
Các loại dữ liệu ngày giờ và chức năng
Sau đây là các kiểu dữ liệu Ngày giờ:
NGÀY
Nó lưu trữ thông tin ngày và giờ ở cả kiểu dữ liệu ký tự và số. Nó được làm bằng thông tin về thế kỷ, năm, tháng, ngày, giờ, phút và giây. Nó được chỉ định là -
TIMESTAMP
Nó là phần mở rộng của kiểu dữ liệu DATE. Nó lưu trữ năm, tháng và ngày của loại dữ liệu DATE, cùng với các giá trị giờ, phút và giây. Nó rất hữu ích để lưu trữ các giá trị thời gian chính xác.
TIMESTAMP VỚI Múi giờ
Đây là một biến thể của TIMESTAMP bao gồm tên khu vực múi giờ hoặc độ lệch múi giờ trong giá trị của nó. Chênh lệch múi giờ là chênh lệch (tính theo giờ và phút) giữa giờ địa phương và UTC. Loại dữ liệu này rất hữu ích để thu thập và đánh giá thông tin ngày tháng trên các vùng địa lý.
TIMESTAMP VỚI VÙNG GIỜ ĐỊA PHƯƠNG
Đây là một biến thể khác của TIMESTAMP bao gồm độ lệch múi giờ trong giá trị của nó.
Bảng sau cung cấp các hàm Datetime (trong đó, x có giá trị datetime):
S.Không | Tên & Mô tả chức năng |
---|---|
1 | ADD_MONTHS(x, y); Thêm y tháng tới x. |
2 | LAST_DAY(x); Trả về ngày cuối cùng của tháng. |
3 | MONTHS_BETWEEN(x, y); Trả về số tháng giữa x và y. |
4 | NEXT_DAY(x, day); Trả về ngày giờ của ngày hôm sau saux. |
5 | NEW_TIME; Trả về giá trị thời gian / ngày từ múi giờ do người dùng chỉ định. |
6 | ROUND(x [, unit]); Vòng tròn x. |
7 | SYSDATE(); Trả về ngày giờ hiện tại. |
số 8 | TRUNC(x [, unit]); Cắt ngắn x. |
Các hàm dấu thời gian (trong đó, x có giá trị dấu thời gian) -
S.Không | Tên & Mô tả chức năng |
---|---|
1 | CURRENT_TIMESTAMP(); Trả về TIMESTAMP WITH TIME ZONE chứa thời gian phiên hiện tại cùng với múi giờ của phiên. |
2 | EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) Trích xuất và trả về một năm, tháng, ngày, giờ, phút, giây hoặc múi giờ từ x. |
3 | FROM_TZ(x, time_zone); Chuyển đổi TIMESTAMP x và múi giờ được chỉ định bởi time_zone thành TIMESTAMP VỚI TIMEZONE. |
4 | LOCALTIMESTAMP(); Trả về TIMESTAMP chứa giờ địa phương trong múi giờ phiên. |
5 | SYSTIMESTAMP(); Trả về TIMESTAMP WITH TIME ZONE chứa thời gian cơ sở dữ liệu hiện tại cùng với múi giờ cơ sở dữ liệu. |
6 | SYS_EXTRACT_UTC(x); Chuyển đổi TIMESTAMP VỚI TIMEZONE x thành TIMESTAMP có chứa ngày và giờ theo UTC. |
7 | TO_TIMESTAMP(x, [format]); Chuyển đổi chuỗi x thành TIMESTAMP. |
số 8 | TO_TIMESTAMP_TZ(x, [format]); Chuyển đổi chuỗi x thành TIMESTAMP VỚI TIMEZONE. |
Ví dụ
Các đoạn mã sau minh họa việc sử dụng các chức năng trên:
Example 1
SELECT SYSDATE FROM DUAL;
Output -
08/31/2012 5:25:34 PM
Example 2
SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;
Output -
31-08-2012 05:26:14
Example 3
SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;
Output -
01/31/2013 5:26:31 PM
Example 4
SELECT LOCALTIMESTAMP FROM DUAL;
Output -
8/31/2012 5:26:55.347000 PM
Các loại dữ liệu khoảng thời gian và chức năng
Sau đây là các kiểu dữ liệu Khoảng thời gian:
IINTERVAL YEAR TO MONTH - Nó lưu trữ một khoảng thời gian bằng cách sử dụng các trường datetime YEAR và MONTH.
INTERVAL DAY TO SEND - Nó lưu trữ một khoảng thời gian theo ngày, giờ, phút và giây.
Hàm khoảng thời gian
S.Không | Tên & Mô tả chức năng |
---|---|
1 | NUMTODSINTERVAL(x, interval_unit); Chuyển đổi số x thành NGÀY LIÊN TỤC ĐẾN GIÂY. |
2 | NUMTOYMINTERVAL(x, interval_unit); Chuyển đổi số x thành INTERVAL NĂM SANG THÁNG. |
3 | TO_DSINTERVAL(x); Chuyển đổi chuỗi x thành INTERVAL DAY TO SEND. |
4 | TO_YMINTERVAL(x); Chuyển đổi chuỗi x thành INTERVAL NĂM SANG THÁNG. |
Trong chương này, chúng ta sẽ thảo luận về Đầu ra DBMS trong PL / SQL. CácDBMS_OUTPUTlà một gói tích hợp cho phép bạn hiển thị thông tin đầu ra, gỡ lỗi và gửi thông báo từ các khối PL / SQL, chương trình con, gói và trình kích hoạt. Chúng tôi đã sử dụng gói này trong suốt hướng dẫn của chúng tôi.
Chúng ta hãy xem một đoạn mã nhỏ sẽ hiển thị tất cả các bảng người dùng trong cơ sở dữ liệu. Hãy thử nó trong cơ sở dữ liệu của bạn để liệt kê tất cả các tên bảng -
BEGIN
dbms_output.put_line (user || ' Tables in the database:');
FOR t IN (SELECT table_name FROM user_tables)
LOOP
dbms_output.put_line(t.table_name);
END LOOP;
END;
/
Chương trình con DBMS_OUTPUT
Gói DBMS_OUTPUT có các chương trình con sau:
S.Không | Chương trình con & Mục đích | |
---|---|---|
1 | DBMS_OUTPUT.DISABLE; Tắt đầu ra tin nhắn. |
|
2 | DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); Cho phép xuất tin nhắn. Giá trị NULL củabuffer_size đại diện cho kích thước bộ đệm không giới hạn. |
|
3 | DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER); Truy xuất một dòng thông tin được lưu vào bộ đệm. |
|
4 | DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER); Lấy một mảng dòng từ bộ đệm. |
|
5 | DBMS_OUTPUT.NEW_LINE; Đặt một điểm đánh dấu cuối dòng. |
|
6 | DBMS_OUTPUT.PUT(item IN VARCHAR2); Đặt một phần dòng trong bộ đệm. |
|
7 | DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); Đặt một dòng trong bộ đệm. |
Thí dụ
DECLARE
lines dbms_output.chararr;
num_lines number;
BEGIN
-- enable the buffer with default size 20000
dbms_output.enable;
dbms_output.put_line('Hello Reader!');
dbms_output.put_line('Hope you have enjoyed the tutorials!');
dbms_output.put_line('Have a great time exploring pl/sql!');
num_lines := 3;
dbms_output.get_lines(lines, num_lines);
FOR i IN 1..num_lines LOOP
dbms_output.put_line(lines(i));
END LOOP;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Hello Reader!
Hope you have enjoyed the tutorials!
Have a great time exploring pl/sql!
PL/SQL procedure successfully completed.
Trong chương này, chúng ta sẽ thảo luận về PL / SQL hướng đối tượng. PL / SQL cho phép xác định một kiểu đối tượng, giúp thiết kế cơ sở dữ liệu hướng đối tượng trong Oracle. Một kiểu đối tượng cho phép bạn tạo các kiểu kết hợp. Sử dụng các đối tượng cho phép bạn triển khai các đối tượng trong thế giới thực với cấu trúc dữ liệu và phương pháp cụ thể để vận hành nó. Các đối tượng có các thuộc tính và phương thức. Thuộc tính là thuộc tính của một đối tượng và được sử dụng để lưu trữ trạng thái của đối tượng; và các phương pháp được sử dụng để mô hình hóa hành vi của nó.
Các đối tượng được tạo bằng cách sử dụng câu lệnh CREATE [OR REPLACE] TYPE. Sau đây là một ví dụ để tạo mộtaddress đối tượng bao gồm một số thuộc tính -
CREATE OR REPLACE TYPE address AS OBJECT
(house_no varchar2(10),
street varchar2(30),
city varchar2(20),
state varchar2(10),
pincode varchar2(10)
);
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type created.
Hãy tạo thêm một đối tượng nữa customer nơi chúng tôi sẽ quấn attributes và methods cùng nhau để có cảm giác hướng đối tượng -
CREATE OR REPLACE TYPE customer AS OBJECT
(code number(5),
name varchar2(30),
contact_no varchar2(12),
addr address,
member procedure display
);
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type created.
Khởi tạo một đối tượng
Việc xác định kiểu đối tượng cung cấp bản thiết kế cho đối tượng. Để sử dụng đối tượng này, bạn cần tạo các thể hiện của đối tượng này. Bạn có thể truy cập các thuộc tính và phương thức của đối tượng bằng cách sử dụng tên phiên bản vàthe access operator (.) như sau -
DECLARE
residence address;
BEGIN
residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');
dbms_output.put_line('House No: '|| residence.house_no);
dbms_output.put_line('Street: '|| residence.street);
dbms_output.put_line('City: '|| residence.city);
dbms_output.put_line('State: '|| residence.state);
dbms_output.put_line('Pincode: '|| residence.pincode);
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
House No: 103A
Street: M.G.Road
City: Jaipur
State: Rajasthan
Pincode: 201301
PL/SQL procedure successfully completed.
Phương thức thành viên
Member methods được sử dụng để điều khiển attributescủa đối tượng. Bạn cung cấp khai báo của một phương thức thành viên trong khi khai báo kiểu đối tượng. Phần thân đối tượng xác định mã cho các phương thức thành viên. Phần thân đối tượng được tạo bằng câu lệnh CREATE TYPE BODY.
Constructorslà các hàm trả về một đối tượng mới làm giá trị của nó. Mọi đối tượng đều có một phương thức khởi tạo do hệ thống xác định. Tên của hàm tạo giống với kiểu đối tượng. Ví dụ -
residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');
Các comparison methodsđược sử dụng để so sánh các đối tượng. Có hai cách để so sánh các đối tượng -
Phương pháp bản đồ
Các Map methodlà một hàm được triển khai theo cách mà giá trị của nó phụ thuộc vào giá trị của các thuộc tính. Ví dụ, đối với một đối tượng khách hàng, nếu mã khách hàng giống nhau cho hai khách hàng thì cả hai khách hàng có thể giống nhau. Vì vậy, mối quan hệ giữa hai đối tượng này sẽ phụ thuộc vào giá trị của mã.
Phương thức đặt hàng
Các Order methodthực hiện một số logic nội bộ để so sánh hai đối tượng. Ví dụ, đối với một đối tượng hình chữ nhật, một hình chữ nhật lớn hơn một hình chữ nhật khác nếu cả hai cạnh của nó đều lớn hơn.
Sử dụng phương pháp Bản đồ
Chúng ta hãy thử hiểu các khái niệm trên bằng cách sử dụng đối tượng hình chữ nhật sau:
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member function enlarge( inc number) return rectangle,
member procedure display,
map member function measure return number
);
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type created.
Tạo phần thân kiểu -
CREATE OR REPLACE TYPE BODY rectangle AS
MEMBER FUNCTION enlarge(inc number) return rectangle IS
BEGIN
return rectangle(self.length + inc, self.width + inc);
END enlarge;
MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length);
dbms_output.put_line('Width: '|| width);
END display;
MAP MEMBER FUNCTION measure return number IS
BEGIN
return (sqrt(length*length + width*width));
END measure;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type body created.
Bây giờ sử dụng đối tượng hình chữ nhật và các hàm thành viên của nó -
DECLARE
r1 rectangle;
r2 rectangle;
r3 rectangle;
inc_factor number := 5;
BEGIN
r1 := rectangle(3, 4);
r2 := rectangle(5, 7);
r3 := r1.enlarge(inc_factor);
r3.display;
IF (r1 > r2) THEN -- calling measure function
r1.display;
ELSE
r2.display;
END IF;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Length: 8
Width: 9
Length: 5
Width: 7
PL/SQL procedure successfully completed.
Sử dụng phương thức đặt hàng
Bây giờ, same effect could be achieved using an order method. Hãy để chúng tôi tạo lại đối tượng hình chữ nhật bằng phương thức đặt hàng -
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member procedure display,
order member function measure(r rectangle) return number
);
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type created.
Tạo phần thân kiểu -
CREATE OR REPLACE TYPE BODY rectangle AS
MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length);
dbms_output.put_line('Width: '|| width);
END display;
ORDER MEMBER FUNCTION measure(r rectangle) return number IS
BEGIN
IF(sqrt(self.length*self.length + self.width*self.width)>
sqrt(r.length*r.length + r.width*r.width)) then
return(1);
ELSE
return(-1);
END IF;
END measure;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type body created.
Sử dụng đối tượng hình chữ nhật và các hàm thành viên của nó -
DECLARE
r1 rectangle;
r2 rectangle;
BEGIN
r1 := rectangle(23, 44);
r2 := rectangle(15, 17);
r1.display;
r2.display;
IF (r1 > r2) THEN -- calling measure function
r1.display;
ELSE
r2.display;
END IF;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Length: 23
Width: 44
Length: 15
Width: 17
Length: 23
Width: 44
PL/SQL procedure successfully completed.
Kế thừa cho các đối tượng PL / SQL
PL / SQL cho phép tạo đối tượng từ các đối tượng cơ sở hiện có. Để thực hiện kế thừa, các đối tượng cơ sở phải được khai báo làNOT FINAL. Mặc định làFINAL.
Các chương trình sau đây minh họa sự kế thừa trong PL / SQL Objects. Hãy để chúng tôi tạo một đối tượng khác có tênTableTop, điều này được kế thừa từ đối tượng Rectangle. Đối với điều này, chúng ta cần tạo đối tượng hình chữ nhật cơ sở -
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member function enlarge( inc number) return rectangle,
NOT FINAL member procedure display) NOT FINAL
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type created.
Tạo phần thân kiểu cơ sở -
CREATE OR REPLACE TYPE BODY rectangle AS
MEMBER FUNCTION enlarge(inc number) return rectangle IS
BEGIN
return rectangle(self.length + inc, self.width + inc);
END enlarge;
MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length);
dbms_output.put_line('Width: '|| width);
END display;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type body created.
Tạo mặt bàn đối tượng con -
CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
material varchar2(20),
OVERRIDING member procedure display
)
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type created.
Tạo phần thân kiểu cho mặt bàn đối tượng con
CREATE OR REPLACE TYPE BODY tabletop AS
OVERRIDING MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length);
dbms_output.put_line('Width: '|| width);
dbms_output.put_line('Material: '|| material);
END display;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type body created.
Sử dụng đối tượng tabletop và các hàm thành viên của nó -
DECLARE
t1 tabletop;
t2 tabletop;
BEGIN
t1:= tabletop(20, 10, 'Wood');
t2 := tabletop(50, 30, 'Steel');
t1.display;
t2.display;
END;
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Length: 20
Width: 10
Material: Wood
Length: 50
Width: 30
Material: Steel
PL/SQL procedure successfully completed.
Các đối tượng trừu tượng trong PL / SQL
Các NOT INSTANTIABLEmệnh đề cho phép bạn khai báo một đối tượng trừu tượng. Bạn không thể sử dụng một đối tượng trừu tượng như nó vốn có; bạn sẽ phải tạo một kiểu con hoặc kiểu con của các đối tượng như vậy để sử dụng các chức năng của nó.
Ví dụ,
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
NOT INSTANTIABLE NOT FINAL
/
Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
Type created.