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 databaseIBM 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 BEGINENDvà 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ụngBEGINEND. 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àoSCALARLOBcá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ị BooleanTRUEFALSE 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 conCHARACTERINTEGER 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 LocalGlobal 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ề constantsliteralstrong 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ụ
Đượ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
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 THENEND 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 zd đượ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, %NOTFOUND%ROWCOUNT. Con trỏ SQL có các thuộc tính bổ sung,%BULK_ROWCOUNT%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-basedcursorbased 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 datetimeinterval 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 xy.

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