PL / SQL - คู่มือฉบับย่อ
ภาษาโปรแกรม PL / SQL ได้รับการพัฒนาโดย Oracle Corporation ในช่วงปลายทศวรรษ 1980 เป็นภาษาส่วนขยายขั้นตอนสำหรับ SQL และฐานข้อมูลเชิงสัมพันธ์ของ Oracle ต่อไปนี้เป็นข้อเท็จจริงที่น่าสังเกตบางประการเกี่ยวกับ PL / SQL -
PL / SQL เป็นภาษาประมวลผลธุรกรรมแบบพกพาที่มีประสิทธิภาพสูง
PL / SQL จัดเตรียมสภาพแวดล้อมการเขียนโปรแกรมที่เป็นอิสระในตัวตีความและ OS
นอกจากนี้ยังสามารถเรียกใช้ PL / SQL ได้โดยตรงจากบรรทัดคำสั่ง SQL*Plus interface.
สามารถโทรโดยตรงจากการเรียกภาษาโปรแกรมภายนอกไปยังฐานข้อมูล
ไวยากรณ์ทั่วไปของ PL / SQL ขึ้นอยู่กับภาษาโปรแกรม ADA และ Pascal
นอกเหนือจาก Oracle แล้ว PL / SQL ยังมีให้ใน TimesTen in-memory database และ IBM DB2.
คุณสมบัติของ PL / SQL
PL / SQL มีคุณสมบัติดังต่อไปนี้ -
- PL / SQL ถูกรวมเข้ากับ SQL อย่างแน่นหนา
- มีการตรวจสอบข้อผิดพลาดมากมาย
- มีข้อมูลหลายประเภท
- มีโครงสร้างการเขียนโปรแกรมที่หลากหลาย
- สนับสนุนการเขียนโปรแกรมเชิงโครงสร้างผ่านฟังก์ชันและขั้นตอน
- รองรับการเขียนโปรแกรมเชิงวัตถุ
- สนับสนุนการพัฒนาเว็บแอปพลิเคชันและหน้าเซิร์ฟเวอร์
ข้อดีของ PL / SQL
PL / SQL มีข้อดีดังต่อไปนี้ -
SQL เป็นภาษาฐานข้อมูลมาตรฐานและ PL / SQL ถูกรวมเข้ากับ SQL อย่างมาก PL / SQL รองรับ SQL ทั้งแบบคงที่และแบบไดนามิก Static SQL รองรับการดำเนินการ DML และการควบคุมธุรกรรมจากบล็อก PL / SQL ใน Dynamic SQL SQL อนุญาตให้ฝังคำสั่ง DDL ในบล็อก PL / SQL
PL / SQL ช่วยให้สามารถส่งคำสั่งทั้งบล็อกไปยังฐานข้อมูลได้ในคราวเดียว ซึ่งจะช่วยลดปริมาณการใช้งานเครือข่ายและให้ประสิทธิภาพสูงสำหรับแอปพลิเคชัน
PL / SQL ให้ผลผลิตสูงแก่โปรแกรมเมอร์เนื่องจากสามารถสืบค้นแปลงและอัปเดตข้อมูลในฐานข้อมูลได้
PL / SQL ช่วยประหยัดเวลาในการออกแบบและการดีบักด้วยคุณสมบัติที่แข็งแกร่งเช่นการจัดการข้อยกเว้นการห่อหุ้มการซ่อนข้อมูลและชนิดข้อมูลเชิงวัตถุ
แอปพลิเคชันที่เขียนด้วย PL / SQL สามารถพกพาได้อย่างสมบูรณ์
PL / SQL ให้ระดับความปลอดภัยสูง
PL / SQL ให้การเข้าถึงแพ็คเกจ SQL ที่กำหนดไว้ล่วงหน้า
PL / SQL ให้การสนับสนุนสำหรับ Object-Oriented Programming
PL / SQL ให้การสนับสนุนสำหรับการพัฒนา Web Applications และ Server Pages
ในบทนี้เราจะพูดถึงการตั้งค่าสภาพแวดล้อมของ PL / SQL PL / SQL ไม่ใช่ภาษาโปรแกรมแบบสแตนด์อโลน เป็นเครื่องมือภายในสภาพแวดล้อมการเขียนโปรแกรม OracleSQL* Plusเป็นเครื่องมือโต้ตอบที่ช่วยให้คุณพิมพ์คำสั่ง SQL และ PL / SQL ที่พรอมต์คำสั่ง จากนั้นคำสั่งเหล่านี้จะถูกส่งไปยังฐานข้อมูลเพื่อประมวลผล เมื่อประมวลผลคำสั่งแล้วผลลัพธ์จะถูกส่งกลับและแสดงบนหน้าจอ
ในการรันโปรแกรม PL / SQL คุณควรติดตั้ง Oracle RDBMS Server ในเครื่องของคุณ สิ่งนี้จะดูแลการทำงานของคำสั่ง SQL Oracle RDBMS เวอร์ชันล่าสุดคือ 11g คุณสามารถดาวน์โหลด Oracle 11g รุ่นทดลองได้จากลิงค์ต่อไปนี้ -
ดาวน์โหลด Oracle 11g Express Edition
คุณจะต้องดาวน์โหลดการติดตั้งเวอร์ชัน 32 บิตหรือ 64 บิตตามระบบปฏิบัติการของคุณ โดยปกติจะมีสองไฟล์ เราได้ดาวน์โหลดเวอร์ชัน 64 บิต คุณจะใช้ขั้นตอนที่คล้ายกันในระบบปฏิบัติการของคุณไม่ว่าจะเป็น Linux หรือ Solaris
win64_11gR2_database_1of2.zip
win64_11gR2_database_2of2.zip
หลังจากดาวน์โหลดสองไฟล์ข้างต้นคุณจะต้องแตกไฟล์ในไดเร็กทอรีเดียว database และภายใต้นั้นคุณจะพบไดเรกทอรีย่อยต่อไปนี้ -
ขั้นตอนที่ 1
ตอนนี้ให้เราเปิดตัว Oracle Database Installer โดยใช้ไฟล์ติดตั้ง ต่อไปนี้เป็นหน้าจอแรก คุณสามารถระบุรหัสอีเมลของคุณและทำเครื่องหมายในช่องดังที่แสดงในภาพหน้าจอต่อไปนี้ คลิกNext ปุ่ม.
ขั้นตอนที่ 2
คุณจะถูกนำไปที่หน้าจอต่อไปนี้ ยกเลิกการเลือกช่องทำเครื่องหมายแล้วคลิกContinue เพื่อดำเนินการต่อ
ขั้นตอนที่ 3
เพียงเลือกตัวเลือกแรก Create and Configure Database โดยใช้ปุ่มตัวเลือกและคลิกไฟล์ Next เพื่อดำเนินการต่อ
ขั้นตอนที่ 4
เราถือว่าคุณติดตั้ง Oracle เพื่อจุดประสงค์พื้นฐานในการเรียนรู้และคุณกำลังติดตั้งบนพีซีหรือแล็ปท็อปของคุณ ดังนั้นเลือกไฟล์Desktop Class แล้วคลิกไฟล์ Next เพื่อดำเนินการต่อ
ขั้นตอนที่ 5
ระบุตำแหน่งที่คุณจะติดตั้งเซิร์ฟเวอร์ Oracle เพียงแค่ปรับเปลี่ยนไฟล์Oracle Baseและสถานที่อื่น ๆ จะตั้งค่าโดยอัตโนมัติ คุณจะต้องระบุรหัสผ่านด้วย สิ่งนี้จะถูกใช้โดย DBA ของระบบ เมื่อคุณระบุข้อมูลที่จำเป็นแล้วให้คลิกไฟล์Next เพื่อดำเนินการต่อ
ขั้นตอนที่ 6
อีกครั้งให้คลิกไฟล์ Next เพื่อดำเนินการต่อ
ขั้นตอนที่ 7
คลิก Finishปุ่มเพื่อดำเนินการต่อ สิ่งนี้จะเริ่มการติดตั้งเซิร์ฟเวอร์จริง
ขั้นตอนที่ 8
ขั้นตอนนี้จะใช้เวลาสักครู่จนกว่า Oracle จะเริ่มดำเนินการกำหนดค่าที่จำเป็น
ขั้นตอนที่ 9
ที่นี่การติดตั้ง Oracle จะคัดลอกไฟล์คอนฟิกูเรชันที่ต้องการ ขั้นตอนนี้ควรใช้เวลาสักครู่ -
ขั้นตอนที่ 10
เมื่อคัดลอกไฟล์ฐานข้อมูลแล้วคุณจะมีกล่องโต้ตอบต่อไปนี้ เพียงคลิกที่ไฟล์OK ปุ่มและออกมา
ขั้นตอนที่ 11
เมื่อติดตั้งคุณจะมีหน้าต่างสุดท้ายดังต่อไปนี้
ขั้นตอนสุดท้าย
ได้เวลาตรวจสอบการติดตั้งของคุณแล้ว ที่พรอมต์คำสั่งใช้คำสั่งต่อไปนี้หากคุณใช้ Windows -
sqlplus "/ as sysdba"คุณควรมีพรอมต์ SQL ที่คุณจะเขียนคำสั่งและสคริปต์ PL / SQL ของคุณ -
แก้ไขข้อความ
การเรียกใช้โปรแกรมขนาดใหญ่จากพรอมต์คำสั่งอาจทำให้คุณสูญเสียงานบางส่วนโดยไม่ได้ตั้งใจ ขอแนะนำให้ใช้ไฟล์คำสั่งเสมอ ในการใช้ไฟล์คำสั่ง -
พิมพ์รหัสของคุณในโปรแกรมแก้ไขข้อความเช่น Notepad, Notepad+, หรือ EditPlusฯลฯ
บันทึกไฟล์ด้วยไฟล์ .sql ส่วนขยายในโฮมไดเร็กทอรี
เปิดตัว SQL*Plus command prompt จากไดเร็กทอรีที่คุณสร้างไฟล์ PL / SQL
ประเภท @file_name ที่พรอมต์คำสั่ง SQL * Plus เพื่อรันโปรแกรมของคุณ
หากคุณไม่ได้ใช้ไฟล์เพื่อเรียกใช้สคริปต์ PL / SQL ให้คัดลอกโค้ด PL / SQL ของคุณแล้วคลิกขวาที่หน้าต่างสีดำที่แสดงพรอมต์ SQL ใช้pasteตัวเลือกในการวางรหัสทั้งหมดที่พรอมต์คำสั่ง สุดท้ายเพียงแค่กดEnter เพื่อรันโค้ดหากยังไม่ได้ดำเนินการ
ในบทนี้เราจะพูดถึงไวยากรณ์พื้นฐานของ PL / SQL ซึ่งเป็นไฟล์ block-structuredภาษา; ซึ่งหมายความว่าโปรแกรม PL / SQL ถูกแบ่งและเขียนในบล็อกตรรกะของโค้ด แต่ละบล็อกประกอบด้วยสามส่วนย่อย -
| ส. เลขที่ | ส่วนและคำอธิบาย |
|---|---|
| 1 | Declarations ส่วนนี้เริ่มต้นด้วยคำหลัก DECLARE. เป็นส่วนทางเลือกและกำหนดตัวแปรเคอร์เซอร์โปรแกรมย่อยและองค์ประกอบอื่น ๆ ทั้งหมดที่จะใช้ในโปรแกรม |
| 2 | Executable Commands ส่วนนี้อยู่ระหว่างคีย์เวิร์ด BEGIN และ ENDและเป็นส่วนบังคับ ประกอบด้วยคำสั่ง PL / SQL ที่ปฏิบัติการได้ของโปรแกรม ควรมีโค้ดที่เรียกใช้งานได้อย่างน้อยหนึ่งบรรทัดซึ่งอาจเป็นเพียงไฟล์NULL command เพื่อระบุว่าไม่ควรดำเนินการใด ๆ |
| 3 | Exception Handling ส่วนนี้เริ่มต้นด้วยคำหลัก EXCEPTION. ส่วนที่เป็นทางเลือกนี้ประกอบด้วยexception(s) ที่จัดการข้อผิดพลาดในโปรแกรม |
ทุกคำสั่ง PL / SQL ลงท้ายด้วยอัฒภาค (;) บล็อก PL / SQL สามารถซ้อนกันภายในบล็อก PL / SQL อื่นโดยใช้ไฟล์BEGIN และ END. ต่อไปนี้เป็นโครงสร้างพื้นฐานของบล็อก PL / SQL -
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;ตัวอย่าง 'Hello World'
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/end;บรรทัดส่งสัญญาณการสิ้นสุดของบล็อก PL / SQL ในการรันโค้ดจากบรรทัดคำสั่ง SQL คุณอาจต้องพิมพ์ / ที่จุดเริ่มต้นของบรรทัดว่างบรรทัดแรกหลังบรรทัดสุดท้ายของโค้ด เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Hello World
PL/SQL procedure successfully completed.ตัวระบุ PL / SQL
ตัวระบุ PL / SQL คือค่าคงที่ตัวแปรข้อยกเว้นโพรซีเดอร์เคอร์เซอร์และคำสงวน ตัวระบุประกอบด้วยตัวอักษรตามด้วยตัวอักษรตัวเลขเครื่องหมายดอลลาร์ขีดล่างและเครื่องหมายตัวเลขและไม่ควรเกิน 30 อักขระ
โดยค่าเริ่มต้น, identifiers are not case-sensitive. ดังนั้นคุณสามารถใช้integer หรือ INTEGERเพื่อแทนค่าตัวเลข คุณไม่สามารถใช้คีย์เวิร์ดที่สงวนไว้เป็นตัวระบุได้
ตัวคั่น PL / SQL
ตัวคั่นคือสัญลักษณ์ที่มีความหมายพิเศษ ต่อไปนี้เป็นรายการตัวคั่นใน PL / SQL -
| ตัวคั่น | คำอธิบาย |
|---|---|
| +, -, *, / | การบวกการลบ / การลบการคูณการหาร |
| % | ตัวบ่งชี้คุณสมบัติ |
| ' | ตัวคั่นสตริงอักขระ |
| . | ตัวเลือกส่วนประกอบ |
| (,) | นิพจน์หรือตัวคั่นรายการ |
| : | ตัวบ่งชี้ตัวแปรโฮสต์ |
| , | ตัวคั่นรายการ |
| " | ตัวคั่นตัวระบุที่ยกมา |
| = | ตัวดำเนินการเชิงสัมพันธ์ |
| @ | ตัวบ่งชี้การเข้าถึงระยะไกล |
| ; | ตัวยุติคำชี้แจง |
| := | ตัวดำเนินการมอบหมาย |
| => | ผู้ดำเนินการสมาคม |
| || | ตัวดำเนินการเชื่อมต่อ |
| ** | ตัวดำเนินการยกกำลัง |
| <<, >> | ตัวคั่นฉลาก (เริ่มต้นและสิ้นสุด) |
| /*, */ | ตัวคั่นความคิดเห็นหลายบรรทัด (เริ่มต้นและสิ้นสุด) |
| -- | ตัวบ่งชี้ความคิดเห็นบรรทัดเดียว |
| .. | ตัวดำเนินการช่วง |
| <, >, <=, >= | ตัวดำเนินการเชิงสัมพันธ์ |
| <>, '=, ~=, ^= | เวอร์ชันต่างๆของ NOT EQUAL |
ความคิดเห็น PL / SQL
ความคิดเห็นของโปรแกรมเป็นข้อความอธิบายที่สามารถรวมไว้ในโค้ด PL / SQL ที่คุณเขียนและช่วยให้ทุกคนอ่านซอร์สโค้ดได้ ภาษาโปรแกรมทั้งหมดอนุญาตให้แสดงความคิดเห็นบางรูปแบบ
PL / SQL รองรับการแสดงความคิดเห็นแบบบรรทัดเดียวและแบบหลายบรรทัด อักขระทั้งหมดที่มีอยู่ในข้อคิดเห็นใด ๆ จะถูกละเว้นโดยคอมไพลเลอร์ PL / SQL ความคิดเห็นบรรทัดเดียว PL / SQL เริ่มต้นด้วยตัวคั่น - (ยัติภังค์คู่) และความคิดเห็นแบบหลายบรรทัดล้อมรอบด้วย / * และ * /
DECLARE
-- variable declaration
message varchar2(20):= 'Hello, World!';
BEGIN
/*
* PL/SQL executable statement(s)
*/
dbms_output.put_line(message);
END;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Hello World
PL/SQL procedure successfully completed.หน่วยโปรแกรม PL / SQL
หน่วย PL / SQL คืออย่างใดอย่างหนึ่งต่อไปนี้ -
- บล็อก PL / SQL
- Function
- Package
- ตัวบรรจุภัณฑ์
- Procedure
- Trigger
- Type
- พิมพ์ body
แต่ละหน่วยงานเหล่านี้จะกล่าวถึงในบทต่อไปนี้
ในบทนี้เราจะพูดถึงประเภทข้อมูลใน PL / SQL ตัวแปร PL / SQL ค่าคงที่และพารามิเตอร์ต้องมีชนิดข้อมูลที่ถูกต้องซึ่งระบุรูปแบบการจัดเก็บข้อ จำกัด และช่วงค่าที่ถูกต้อง เราจะมุ่งเน้นไปที่SCALAR และ LOBชนิดข้อมูลในบทนี้ ข้อมูลอีกสองประเภทจะครอบคลุมในบทอื่น ๆ
| ส. เลขที่ | หมวดหมู่และคำอธิบาย |
|---|---|
| 1 | Scalar ค่าเดียวที่ไม่มีส่วนประกอบภายในเช่นก NUMBER, DATE, หรือ BOOLEAN. |
| 2 | Large Object (LOB) ชี้ไปยังวัตถุขนาดใหญ่ที่จัดเก็บแยกจากรายการข้อมูลอื่น ๆ เช่นข้อความรูปภาพกราฟิกคลิปวิดีโอและรูปคลื่นเสียง |
| 3 | Composite รายการข้อมูลที่มีส่วนประกอบภายในที่สามารถเข้าถึงได้ทีละรายการ ตัวอย่างเช่นคอลเลกชันและบันทึก |
| 4 | Reference ชี้ไปยังรายการข้อมูลอื่น ๆ |
PL / SQL Scalar Data types and Subtypes
PL / SQL Scalar Data types and Subtypes อยู่ภายใต้หมวดหมู่ต่อไปนี้ -
| ส. เลขที่ | ประเภทวันที่และคำอธิบาย |
|---|---|
| 1 | Numeric ค่าตัวเลขที่ดำเนินการทางคณิตศาสตร์ |
| 2 | Character ค่าตัวอักษรและตัวเลขที่แสดงถึงอักขระเดี่ยวหรือสตริงของอักขระ |
| 3 | Boolean ค่าตรรกะที่ดำเนินการทางตรรกะ |
| 4 | Datetime วันที่และเวลา |
PL / SQL จัดเตรียมประเภทย่อยของชนิดข้อมูล ตัวอย่างเช่นข้อมูลประเภท NUMBER มีประเภทย่อยที่เรียกว่า INTEGER คุณสามารถใช้ชนิดย่อยในโปรแกรม PL / SQL เพื่อทำให้ชนิดข้อมูลเข้ากันได้กับชนิดข้อมูลในโปรแกรมอื่น ๆ ในขณะที่ฝังโค้ด PL / SQL ในโปรแกรมอื่นเช่นโปรแกรม Java
PL / SQL ชนิดข้อมูลตัวเลขและชนิดย่อย
ตารางต่อไปนี้แสดงประเภทข้อมูลตัวเลขที่กำหนดไว้ล่วงหน้าของ PL / SQL และประเภทย่อย -
| ส. เลขที่ | ประเภทข้อมูลและคำอธิบาย |
|---|---|
| 1 | PLS_INTEGER จำนวนเต็มที่ลงชื่อในช่วง -2,147,483,648 ถึง 2,147,483,647 ซึ่งแสดงเป็น 32 บิต |
| 2 | BINARY_INTEGER จำนวนเต็มที่ลงชื่อในช่วง -2,147,483,648 ถึง 2,147,483,647 ซึ่งแสดงเป็น 32 บิต |
| 3 | BINARY_FLOAT เลขทศนิยมรูปแบบ IEEE 754 ความแม่นยำเดียว |
| 4 | BINARY_DOUBLE เลขทศนิยมรูปแบบ IEEE 754 ที่มีความแม่นยำสองเท่า |
| 5 | NUMBER(prec, scale) ตัวเลขจุดคงที่หรือทศนิยมที่มีค่าสัมบูรณ์ในช่วง 1E-130 ถึง (แต่ไม่รวม) 1.0E126 ตัวแปร NUMBER ยังสามารถแทนค่า 0 |
| 6 | DEC(prec, scale) ประเภทจุดคงที่เฉพาะของ ANSI ที่มีความแม่นยำสูงสุด 38 หลักทศนิยม |
| 7 | DECIMAL(prec, scale) ประเภทจุดคงที่เฉพาะของ IBM ที่มีความแม่นยำสูงสุด 38 หลักทศนิยม |
| 8 | NUMERIC(pre, secale) ประเภทลอยที่มีความแม่นยำสูงสุด 38 หลักทศนิยม |
| 9 | DOUBLE PRECISION ประเภททศนิยมเฉพาะของ ANSI ที่มีความแม่นยำสูงสุด 126 หลักไบนารี (ทศนิยมประมาณ 38 หลัก) |
| 10 | FLOAT ประเภทจุดลอยตัวเฉพาะของ ANSI และ IBM ที่มีความแม่นยำสูงสุด 126 หลักไบนารี (ทศนิยมประมาณ 38 หลัก) |
| 11 | INT ประเภทจำนวนเต็มเฉพาะ ANSI ที่มีความแม่นยำสูงสุด 38 หลักทศนิยม |
| 12 | INTEGER ประเภทจำนวนเต็มเฉพาะ ANSI และ IBM ที่มีความแม่นยำสูงสุด 38 หลักทศนิยม |
| 13 | SMALLINT ประเภทจำนวนเต็มเฉพาะ ANSI และ IBM ที่มีความแม่นยำสูงสุด 38 หลักทศนิยม |
| 14 | REAL ชนิดทศนิยมที่มีความแม่นยำสูงสุด 63 หลัก (ทศนิยมประมาณ 18 หลัก) |
ต่อไปนี้เป็นการประกาศที่ถูกต้อง -
DECLARE
num1 INTEGER;
num2 REAL;
num3 DOUBLE PRECISION;
BEGIN
null;
END;
/เมื่อโค้ดด้านบนถูกคอมไพล์และเรียกใช้งานจะให้ผลลัพธ์ดังนี้ -
PL/SQL procedure successfully completedชนิดข้อมูลอักขระ PL / SQL และชนิดย่อย
ต่อไปนี้เป็นรายละเอียดของประเภทข้อมูลอักขระที่กำหนดไว้ล่วงหน้า PL / SQL และประเภทย่อย -
| ส. เลขที่ | ประเภทข้อมูลและคำอธิบาย |
|---|---|
| 1 | CHAR สตริงอักขระที่มีความยาวคงที่ที่มีขนาดสูงสุด 32,767 ไบต์ |
| 2 | VARCHAR2 สตริงอักขระความยาวตัวแปรที่มีขนาดสูงสุด 32,767 ไบต์ |
| 3 | RAW สตริงไบนารีหรือไบต์ที่มีความยาวตัวแปรที่มีขนาดสูงสุด 32,767 ไบต์ไม่ได้รับการตีความโดย PL / SQL |
| 4 | NCHAR สตริงอักขระประจำชาติที่มีความยาวคงที่ที่มีขนาดสูงสุด 32,767 ไบต์ |
| 5 | NVARCHAR2 สตริงอักขระประจำชาติที่มีความยาวตัวแปรที่มีขนาดสูงสุด 32,767 ไบต์ |
| 6 | LONG สตริงอักขระความยาวตัวแปรที่มีขนาดสูงสุด 32,760 ไบต์ |
| 7 | LONG RAW สตริงไบนารีหรือไบต์ที่มีความยาวตัวแปรที่มีขนาดสูงสุด 32,760 ไบต์ไม่ถูกตีความโดย PL / SQL |
| 8 | ROWID ตัวระบุแถวทางกายภาพที่อยู่ของแถวในตารางธรรมดา |
| 9 | UROWID ตัวระบุแถวสากล (ตัวระบุแถวทางกายภาพตรรกะหรือแปลกปลอม) |
ชนิดข้อมูลบูลีน PL / SQL
BOOLEANชนิดข้อมูลเก็บค่าตรรกะที่ใช้ในการดำเนินการทางตรรกะ ค่าตรรกะคือค่าบูลีนTRUE และ FALSE และมูลค่า NULL.
อย่างไรก็ตาม SQL ไม่มีชนิดข้อมูลที่เทียบเท่ากับ BOOLEAN ดังนั้นจึงไม่สามารถใช้ค่าบูลีนใน -
- คำสั่ง SQL
- ฟังก์ชัน SQL ในตัว (เช่น TO_CHAR)
- ฟังก์ชัน PL / SQL เรียกใช้จากคำสั่ง SQL
PL / SQL Datetime และประเภทช่วงเวลา
DATEประเภทข้อมูลใช้เพื่อจัดเก็บวันที่ที่มีความยาวคงที่ซึ่งรวมเวลาของวันเป็นวินาทีตั้งแต่เที่ยงคืน วันที่ใช้ได้ตั้งแต่ 1 มกราคม 4712 ปีก่อนคริสตกาลถึง 31 ธันวาคม ค.ศ. 9999
รูปแบบวันที่เริ่มต้นถูกกำหนดโดยพารามิเตอร์การเริ่มต้น Oracle NLS_DATE_FORMAT ตัวอย่างเช่นค่าเริ่มต้นอาจเป็น "DD-MON-YY" ซึ่งประกอบด้วยตัวเลขสองหลักสำหรับวันของเดือนตัวย่อของชื่อเดือนและตัวเลขสองหลักสุดท้ายของปี ตัวอย่างเช่น 01-OCT-12
แต่ละ DATE ประกอบด้วยศตวรรษปีเดือนวันชั่วโมงนาทีและวินาที ตารางต่อไปนี้แสดงค่าที่ถูกต้องสำหรับแต่ละฟิลด์ -
| ชื่อฟิลด์ | ค่า Datetime ที่ถูกต้อง | ค่าช่วงเวลาที่ถูกต้อง |
|---|---|---|
| ปี | -4712 ถึง 9999 (ไม่รวมปี 0) | จำนวนเต็มที่ไม่ใช่ศูนย์ |
| เดือน | 01 ถึง 12 | 0 ถึง 11 |
| วัน | 01 ถึง 31 (จำกัด โดยค่าของ MONTH และ YEAR ตามกฎของปฏิทินสำหรับภาษา) | จำนวนเต็มที่ไม่ใช่ศูนย์ |
| ชั่วโมง | 00 ถึง 23 | 0 ถึง 23 |
| นาที | 00 ถึง 59 | 0 ถึง 59 |
| วินาที | 00 ถึง 59.9 (n) โดยที่ 9 (n) คือความแม่นยำของเวลาเศษส่วนของวินาที | 0 ถึง 59.9 (n) โดยที่ 9 (n) คือความแม่นยำของช่วงเศษส่วนของวินาที |
| TIMEZONE_HOUR | -12 ถึง 14 (ช่วงรองรับการเปลี่ยนแปลงเวลาออมแสง) | ไม่สามารถใช้ได้ |
| TIMEZONE_MINUTE | 00 ถึง 59 | ไม่สามารถใช้ได้ |
| TIMEZONE_REGION | พบในมุมมองประสิทธิภาพแบบไดนามิก V $ TIMEZONE_NAMES | ไม่สามารถใช้ได้ |
| TIMEZONE_ABBR | พบในมุมมองประสิทธิภาพแบบไดนามิก V $ TIMEZONE_NAMES | ไม่สามารถใช้ได้ |
ประเภทข้อมูล PL / SQL Large Object (LOB)
ประเภทข้อมูล Large Object (LOB) หมายถึงรายการข้อมูลขนาดใหญ่เช่นข้อความรูปภาพกราฟิกคลิปวิดีโอและรูปคลื่นเสียง ประเภทข้อมูล LOB ช่วยให้สามารถเข้าถึงข้อมูลนี้แบบสุ่มได้อย่างมีประสิทธิภาพ ต่อไปนี้เป็นชนิดข้อมูล PL / SQL LOB ที่กำหนดไว้ล่วงหน้า -
| ประเภทข้อมูล | คำอธิบาย | ขนาด |
|---|---|---|
| BFILE | ใช้เพื่อเก็บวัตถุไบนารีขนาดใหญ่ในไฟล์ระบบปฏิบัติการนอกฐานข้อมูล | ขึ้นอยู่กับระบบ ต้องไม่เกิน 4 กิกะไบต์ (GB) |
| หยด | ใช้เพื่อเก็บวัตถุไบนารีขนาดใหญ่ในฐานข้อมูล | 8 ถึง 128 เทราไบต์ (TB) |
| CLOB | ใช้เพื่อจัดเก็บข้อมูลอักขระจำนวนมากในฐานข้อมูล | 8 ถึง 128 TB |
| NCLOB | ใช้เพื่อจัดเก็บข้อมูล NCHAR จำนวนมากในฐานข้อมูล | 8 ถึง 128 TB |
ชนิดย่อยที่ผู้ใช้กำหนด PL / SQL
ประเภทย่อยคือชุดย่อยของชนิดข้อมูลอื่นซึ่งเรียกว่าประเภทฐาน ประเภทย่อยมีการดำเนินการที่ถูกต้องเช่นเดียวกับประเภทฐาน แต่เป็นเพียงส่วนย่อยของค่าที่ถูกต้องเท่านั้น
PL / SQL กำหนดชนิดย่อยต่างๆไว้ล่วงหน้าในแพ็คเกจ STANDARD. ตัวอย่างเช่น PL / SQL กำหนดชนิดย่อยไว้ล่วงหน้าCHARACTER และ INTEGER ดังต่อไปนี้ -
SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);คุณสามารถกำหนดและใช้ประเภทย่อยของคุณเองได้ โปรแกรมต่อไปนี้แสดงให้เห็นถึงการกำหนดและการใช้ประเภทย่อยที่ผู้ใช้กำหนดเอง -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Hello Reader Welcome to the World of PL/SQL
PL/SQL procedure successfully completed.NULL ใน PL / SQL
ค่า PL / SQL NULL แสดงถึง missing หรือ unknown dataและไม่ใช่จำนวนเต็มอักขระหรือชนิดข้อมูลเฉพาะอื่น ๆ โปรดทราบว่าNULL ไม่เหมือนกับสตริงข้อมูลว่างหรือค่าอักขระ null '\0'. สามารถกำหนดค่าว่างได้ แต่ไม่สามารถเทียบได้กับสิ่งใดรวมทั้งตัวมันเอง
ในบทนี้เราจะพูดถึงตัวแปรใน Pl / SQL ตัวแปรไม่ใช่อะไรนอกจากชื่อที่กำหนดให้กับพื้นที่จัดเก็บที่โปรแกรมของเราสามารถจัดการได้ ตัวแปรแต่ละตัวใน PL / SQL มีชนิดข้อมูลเฉพาะซึ่งกำหนดขนาดและรูปแบบของหน่วยความจำของตัวแปร ช่วงของค่าที่สามารถเก็บไว้ในหน่วยความจำนั้นและชุดของการดำเนินการที่สามารถนำไปใช้กับตัวแปร
ชื่อของตัวแปร PL / SQL ประกอบด้วยตัวอักษรตามด้วยตัวอักษรตัวเลขเครื่องหมายดอลลาร์ขีดล่างและเครื่องหมายตัวเลขและไม่ควรเกิน 30 อักขระ โดยค่าเริ่มต้นชื่อตัวแปรจะไม่คำนึงถึงขนาดตัวพิมพ์ คุณไม่สามารถใช้คีย์เวิร์ด PL / SQL ที่สงวนไว้เป็นชื่อตัวแปรได้
ภาษาโปรแกรม PL / SQL ช่วยให้สามารถกำหนดตัวแปรประเภทต่างๆเช่นชนิดข้อมูลวันเวลาเรกคอร์ดคอลเลกชัน ฯลฯ ซึ่งเราจะกล่าวถึงในบทต่อ ๆ ไป สำหรับบทนี้ให้เราศึกษาประเภทตัวแปรพื้นฐานเท่านั้น
การประกาศตัวแปรใน PL / SQL
ต้องประกาศตัวแปร PL / SQL ในส่วนการประกาศหรือในแพ็คเกจเป็นตัวแปรส่วนกลาง เมื่อคุณประกาศตัวแปร PL / SQL จะจัดสรรหน่วยความจำสำหรับค่าของตัวแปรและตำแหน่งที่จัดเก็บจะถูกระบุโดยชื่อตัวแปร
ไวยากรณ์สำหรับการประกาศตัวแปรคือ -
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]โดยที่variable_nameเป็นตัวระบุที่ถูกต้องใน PL / SQL ประเภทข้อมูลต้องเป็นชนิดข้อมูล PL / SQL ที่ถูกต้องหรือประเภทข้อมูลที่ผู้ใช้กำหนดซึ่งเราได้กล่าวไปแล้วในบทสุดท้าย การประกาศตัวแปรที่ถูกต้องพร้อมกับคำจำกัดความแสดงอยู่ด้านล่าง -
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);เมื่อคุณระบุขนาดมาตราส่วนหรือขีด จำกัด ความแม่นยำด้วยชนิดข้อมูลจะเรียกว่า a constrained declaration. การประกาศที่มีข้อ จำกัด ต้องใช้หน่วยความจำน้อยกว่าการประกาศที่ไม่มีข้อ จำกัด ตัวอย่างเช่น -
sales number(10, 2);
name varchar2(25);
address varchar2(100);การเริ่มต้นตัวแปรใน PL / SQL
เมื่อใดก็ตามที่คุณประกาศตัวแปร PL / SQL จะกำหนดค่าเริ่มต้นเป็น NULL หากคุณต้องการเริ่มต้นตัวแปรด้วยค่าอื่นที่ไม่ใช่ค่า NULL คุณสามารถทำได้ในระหว่างการประกาศโดยใช้อย่างใดอย่างหนึ่งต่อไปนี้ -
DEFAULT คำสำคัญ
assignment ตัวดำเนินการ
ตัวอย่างเช่น -
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';คุณยังสามารถระบุว่าตัวแปรไม่ควรมี NULL ค่าโดยใช้ NOT NULLข้อ จำกัด หากคุณใช้ข้อ จำกัด NOT NULL คุณต้องกำหนดค่าเริ่มต้นให้กับตัวแปรนั้นอย่างชัดเจน
เป็นการฝึกเขียนโปรแกรมที่ดีในการเริ่มต้นตัวแปรอย่างเหมาะสมมิฉะนั้นบางครั้งโปรแกรมอาจให้ผลลัพธ์ที่ไม่คาดคิด ลองใช้ตัวอย่างต่อไปนี้ซึ่งใช้ประโยชน์จากตัวแปรประเภทต่างๆ -
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;
/เมื่อดำเนินการโค้ดด้านบนจะให้ผลลัพธ์ดังนี้ -
Value of c: 30
Value of f: 23.333333333333333333
PL/SQL procedure successfully completed.ขอบเขตตัวแปรใน PL / SQL
PL / SQL อนุญาตให้มีการซ้อนบล็อกกล่าวคือแต่ละบล็อกโปรแกรมอาจมีบล็อกด้านในอื่น หากมีการประกาศตัวแปรภายในบล็อกด้านในจะไม่สามารถเข้าถึงบล็อกด้านนอกได้ อย่างไรก็ตามหากมีการประกาศตัวแปรและเข้าถึงบล็อกด้านนอกได้ก็ยังสามารถเข้าถึงบล็อกด้านในที่ซ้อนกันทั้งหมดได้ ขอบเขตตัวแปรมีสองประเภท -
Local variables - ตัวแปรที่ประกาศในบล็อกด้านในและไม่สามารถเข้าถึงบล็อกด้านนอกได้
Global variables - ตัวแปรที่ประกาศในบล็อกนอกสุดหรือแพ็คเกจ
ตัวอย่างต่อไปนี้แสดงการใช้งาน Local และ Global ตัวแปรในรูปแบบง่ายๆ -
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;
/เมื่อดำเนินการโค้ดด้านบนจะให้ผลลัพธ์ดังนี้ -
Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185
PL/SQL procedure successfully completed.การกำหนดผลลัพธ์การสืบค้น SQL ให้กับตัวแปร PL / SQL
คุณสามารถใช้ไฟล์ SELECT INTOคำสั่งของ SQL เพื่อกำหนดค่าให้กับตัวแปร PL / SQL สำหรับแต่ละรายการในSELECT listต้องมีตัวแปรที่เข้ากันได้กับประเภทที่สอดคล้องกันในไฟล์ INTO list. ตัวอย่างต่อไปนี้แสดงให้เห็นถึงแนวคิด ให้เราสร้างตารางชื่อลูกค้า -
(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ตอนนี้ให้เราแทรกค่าบางอย่างในตาราง -
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 );โปรแกรมต่อไปนี้กำหนดค่าจากตารางด้านบนให้กับตัวแปร PL / SQL โดยใช้ SELECT INTO clause ของ 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;
/เมื่อดำเนินการโค้ดด้านบนจะให้ผลลัพธ์ดังนี้ -
Customer Ramesh from Ahmedabad earns 2000
PL/SQL procedure completed successfullyในบทนี้จะกล่าวถึง constants และ literalsใน PL / SQL ค่าคงที่เก็บค่าที่เมื่อประกาศแล้วจะไม่เปลี่ยนแปลงในโปรแกรม การประกาศค่าคงที่ระบุชื่อชนิดข้อมูลและค่าและจัดสรรหน่วยเก็บข้อมูล การประกาศยังสามารถกำหนดไฟล์NOT NULL constraint.
การประกาศค่าคงที่
ค่าคงที่ถูกประกาศโดยใช้ CONSTANTคำสำคัญ. ต้องใช้ค่าเริ่มต้นและไม่อนุญาตให้เปลี่ยนแปลงค่านั้น ตัวอย่างเช่น -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Radius: 9.5
Diameter: 19
Circumference: 59.69
Area: 283.53
Pl/SQL procedure successfully completed.ตัวอักษร PL / SQL
ลิเทอรัลคือตัวเลขอักขระสตริงหรือค่าบูลีนที่ชัดเจนซึ่งไม่ได้แสดงด้วยตัวระบุ ตัวอย่างเช่น TRUE, 786, NULL, 'tutorialspoint' เป็นลิเทอรัลประเภทบูลีนตัวเลขหรือสตริงทั้งหมด PL / SQL ตัวอักษรเป็นตัวพิมพ์เล็กและใหญ่ PL / SQL รองรับตัวอักษรประเภทต่อไปนี้ -
- ตัวอักษรตัวเลข
- ตัวอักษร
- ตัวอักษรสตริง
- BOOLEAN Literals
- วันที่และเวลาตัวอักษร
ตารางต่อไปนี้แสดงตัวอย่างจากค่าลิเทอรัลประเภทเหล่านี้ทั้งหมด
| ส. เลขที่ | ประเภทตัวอักษรและตัวอย่าง |
|---|---|
| 1 | Numeric Literals 050 78 -14 0 +32767 6.6667 0.0 -12.0 3.14159 +7800.00 6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3 |
| 2 | Character Literals 'ก'% '' 9 '' 'z' '(' |
| 3 | String Literals 'สวัสดีชาวโลก!' 'จุดสอน' '19 -NOV-12 ' |
| 4 | BOOLEAN Literals TRUE, FALSE และ NULL |
| 5 | Date and Time Literals วันที่ '2521-12-25'; เวลา '2012-10-29 12:01:01'; |
ในการฝังเครื่องหมายคำพูดเดี่ยวภายในสตริงลิเทอรัลให้วางเครื่องหมายคำพูดเดี่ยวสองคำติดกันดังที่แสดงในโปรแกรมต่อไปนี้ -
DECLARE
message varchar2(30):= 'That''s tutorialspoint.com!';
BEGIN
dbms_output.put_line(message);
END;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
That's tutorialspoint.com!
PL/SQL procedure successfully completed.ในบทนี้เราจะพูดถึงตัวดำเนินการใน PL / SQL ตัวดำเนินการคือสัญลักษณ์ที่บอกให้คอมไพเลอร์ดำเนินการจัดการทางคณิตศาสตร์หรือตรรกะเฉพาะ ภาษา PL / SQL อุดมไปด้วยตัวดำเนินการในตัวและมีตัวดำเนินการประเภทต่อไปนี้ -
- ตัวดำเนินการเลขคณิต
- ตัวดำเนินการเชิงสัมพันธ์
- ตัวดำเนินการเปรียบเทียบ
- ตัวดำเนินการทางตรรกะ
- ตัวดำเนินการสตริง
ในที่นี้เราจะเข้าใจตัวดำเนินการทางคณิตศาสตร์เชิงสัมพันธ์การเปรียบเทียบและตรรกะทีละตัว ตัวดำเนินการ String จะกล่าวถึงในบทต่อไป -PL/SQL - Strings.
ตัวดำเนินการเลขคณิต
ตารางต่อไปนี้แสดงตัวดำเนินการเลขคณิตทั้งหมดที่ PL / SQL รองรับ ให้เราสมมติvariable A ถือ 10 และ variable B ถือ 5 แล้ว -
แสดงตัวอย่าง
| ตัวดำเนินการ | คำอธิบาย | ตัวอย่าง |
|---|---|---|
| + | เพิ่มสองตัวถูกดำเนินการ | A + B จะให้ 15 |
| - | ลบตัวถูกดำเนินการที่สองจากตัวแรก | A - B จะให้ 5 |
| * | คูณตัวถูกดำเนินการทั้งสอง | A * B จะให้ 50 |
| / | หารเศษด้วยตัวเศษ | A / B จะให้ 2 |
| ** | ตัวดำเนินการยกกำลังยกตัวถูกดำเนินการหนึ่งตัวขึ้นสู่อำนาจของอีกตัว | A ** B จะให้ 100000 |
ตัวดำเนินการเชิงสัมพันธ์
ตัวดำเนินการเชิงสัมพันธ์เปรียบเทียบสองนิพจน์หรือค่าและส่งคืนผลลัพธ์แบบบูลีน ตารางต่อไปนี้แสดงตัวดำเนินการเชิงสัมพันธ์ทั้งหมดที่รองรับโดย PL / SQL ให้เราสมมติvariable A ถือ 10 และ variable B ถือ 20 แล้ว -
แสดงตัวอย่าง
| ตัวดำเนินการ | คำอธิบาย | ตัวอย่าง |
|---|---|---|
| = | ตรวจสอบว่าค่าของตัวถูกดำเนินการสองค่าเท่ากันหรือไม่ถ้าใช่เงื่อนไขจะกลายเป็นจริง | (A = B) ไม่เป็นความจริง |
! = <> ~ = |
ตรวจสอบว่าค่าของตัวถูกดำเนินการสองค่าเท่ากันหรือไม่หากค่าไม่เท่ากันเงื่อนไขจะกลายเป็นจริง | (A! = B) เป็นจริง |
| > | ตรวจสอบว่าค่าของตัวถูกดำเนินการด้านซ้ายมากกว่าค่าของตัวถูกดำเนินการด้านขวาหรือไม่ถ้าใช่เงื่อนไขจะกลายเป็นจริง | (A> B) ไม่เป็นความจริง |
| < | ตรวจสอบว่าค่าของตัวถูกดำเนินการด้านซ้ายน้อยกว่าค่าของตัวถูกดำเนินการด้านขวาหรือไม่ถ้าใช่เงื่อนไขจะกลายเป็นจริง | (A <B) เป็นจริง |
| > = | ตรวจสอบว่าค่าของตัวถูกดำเนินการด้านซ้ายมากกว่าหรือเท่ากับค่าของตัวถูกดำเนินการด้านขวาหรือไม่ถ้าใช่เงื่อนไขจะกลายเป็นจริง | (A> = B) ไม่เป็นความจริง |
| <= | ตรวจสอบว่าค่าของตัวถูกดำเนินการด้านซ้ายน้อยกว่าหรือเท่ากับค่าของตัวถูกดำเนินการด้านขวาหรือไม่ถ้าใช่เงื่อนไขจะกลายเป็นจริง | (A <= B) เป็นจริง |
ตัวดำเนินการเปรียบเทียบ
ตัวดำเนินการเปรียบเทียบใช้เพื่อเปรียบเทียบนิพจน์หนึ่งกับอีกนิพจน์ ผลเสมออย่างใดอย่างหนึ่งTRUE, FALSE หรือ NULL.
แสดงตัวอย่าง
| ตัวดำเนินการ | คำอธิบาย | ตัวอย่าง |
|---|---|---|
| ชอบ | ตัวดำเนินการ LIKE จะเปรียบเทียบอักขระสตริงหรือค่า CLOB กับรูปแบบและส่งกลับค่า TRUE หากค่าตรงกับรูปแบบและ FALSE หากไม่เป็นเช่นนั้น | ถ้า 'Zara Ali' เช่น 'Z% A_i' ส่งกลับ Boolean true ในขณะที่ 'Nuha Ali' เช่น 'Z% A_i' จะส่งกลับ Boolean false |
| ระหว่าง | ตัวดำเนินการ BETWEEN จะทดสอบว่าค่าอยู่ในช่วงที่ระบุหรือไม่ x ระหว่าง a และ b หมายความว่า x> = a และ x <= b | ถ้า x = 10 แล้ว x ระหว่าง 5 ถึง 20 จะคืนค่าเป็นจริง x ระหว่าง 5 ถึง 10 จะคืนค่าจริง แต่ x ระหว่าง 11 ถึง 20 จะส่งกลับค่าเท็จ |
| ใน | ตัวดำเนินการ IN ทดสอบการตั้งค่าความเป็นสมาชิก x IN (set) หมายความว่า x เท่ากับสมาชิกใด ๆ ของเซต | ถ้า x = 'm' แล้ว x in ('a', 'b', 'c') จะส่งกลับค่า Boolean false แต่ x in ('m', 'n', 'o') จะส่งกลับค่าบูลีนจริง |
| เป็นโมฆะ | ตัวดำเนินการ IS NULL ส่งคืนค่า BOOLEAN TRUE ถ้าตัวถูกดำเนินการเป็น NULL หรือ FALSE ถ้าไม่ใช่ NULL การเปรียบเทียบที่เกี่ยวข้องกับค่า NULL จะให้ผลเป็น NULL เสมอ | ถ้า x = 'm' ดังนั้น 'x is null' จะส่งกลับ Boolean false |
ตัวดำเนินการทางตรรกะ
ตารางต่อไปนี้แสดงตัวดำเนินการทางตรรกะที่รองรับโดย PL / SQL ตัวดำเนินการทั้งหมดนี้ทำงานบนตัวถูกดำเนินการบูลีนและสร้างผลลัพธ์แบบบูลีน ให้เราสมมติvariable A ถือเป็นความจริงและ variable B ถือเท็จแล้ว -
แสดงตัวอย่าง
| ตัวดำเนินการ | คำอธิบาย | ตัวอย่าง |
|---|---|---|
| และ | เรียกว่าตัวดำเนินการตรรกะ AND ถ้าตัวถูกดำเนินการทั้งสองเป็นจริงเงื่อนไขจะกลายเป็นจริง | (A และ B) เป็นเท็จ |
| หรือ | เรียกว่าตรรกะหรือตัวดำเนินการ หากสองตัวถูกดำเนินการใด ๆ เป็นจริงเงื่อนไขจะกลายเป็นจริง | (A หรือ B) เป็นจริง |
| ไม่ | เรียกว่าตัวดำเนินการไม่เชิงตรรกะ ใช้เพื่อย้อนกลับสถานะตรรกะของตัวถูกดำเนินการ หากเงื่อนไขเป็นจริงตัวดำเนินการ Logical NOT จะทำให้เป็นเท็จ | ไม่ (A และ B) เป็นจริง |
ลำดับความสำคัญของตัวดำเนินการ PL / SQL
ลำดับความสำคัญของตัวดำเนินการกำหนดการจัดกลุ่มคำศัพท์ในนิพจน์ สิ่งนี้มีผลต่อวิธีการประเมินนิพจน์ ตัวดำเนินการบางอย่างมีลำดับความสำคัญสูงกว่าผู้อื่น ตัวอย่างเช่นตัวดำเนินการคูณมีลำดับความสำคัญสูงกว่าตัวดำเนินการบวก
ตัวอย่างเช่น, x = 7 + 3 * 2; ที่นี่x ได้รับมอบหมาย 13ไม่ใช่ 20 เนื่องจากตัวดำเนินการ * มีลำดับความสำคัญสูงกว่า + ดังนั้นจึงได้รับการคูณด้วย 3*2 แล้วเพิ่มเข้าไป 7.
ที่นี่ตัวดำเนินการที่มีลำดับความสำคัญสูงสุดจะปรากฏที่ด้านบนสุดของตารางตัวดำเนินการที่มีค่าต่ำสุดจะปรากฏที่ด้านล่าง ภายในนิพจน์ตัวดำเนินการที่มีลำดับความสำคัญสูงกว่าจะได้รับการประเมินก่อน
ลำดับความสำคัญของตัวดำเนินการมีดังนี้: =, <,>, <=,> =, <>,! =, ~ =, ^ =, IS NULL, LIKE, BETWEEN, IN
แสดงตัวอย่าง
| ตัวดำเนินการ | การดำเนินการ |
|---|---|
| ** | การยกกำลัง |
| +, - | ตัวตนการปฏิเสธ |
| *, / | การคูณการหาร |
| +, -, || | การบวกการลบการเรียงต่อกัน |
| การเปรียบเทียบ | |
| ไม่ | การปฏิเสธเชิงตรรกะ |
| และ | ร่วม |
| หรือ | การรวม |
ในบทนี้เราจะพูดถึงเงื่อนไขใน PL / SQL โครงสร้างการตัดสินใจกำหนดให้โปรแกรมเมอร์ระบุเงื่อนไขอย่างน้อยหนึ่งเงื่อนไขที่จะประเมินหรือทดสอบโดยโปรแกรมพร้อมกับคำสั่งหรือคำสั่งที่จะดำเนินการหากเงื่อนไขถูกกำหนดให้เป็นจริงและเป็นทางเลือกที่จะดำเนินการคำสั่งอื่น ๆ หาก เงื่อนไขถูกกำหนดให้เป็นเท็จ
ต่อไปนี้เป็นรูปแบบทั่วไปของโครงสร้างเงื่อนไขทั่วไป (เช่นการตัดสินใจ) ที่พบในภาษาโปรแกรมส่วนใหญ่ -
ภาษาโปรแกรม PL / SQL จัดเตรียมประเภทของคำสั่งการตัดสินใจดังต่อไปนี้ คลิกลิงก์ต่อไปนี้เพื่อตรวจสอบรายละเอียด
| ส. เลขที่ | คำชี้แจงและคำอธิบาย |
|---|---|
| 1 | คำสั่ง IF - THEN IF statement เชื่อมโยงเงื่อนไขกับลำดับของคำสั่งที่อยู่ในคีย์เวิร์ด THEN และ END IF. หากเงื่อนไขเป็นจริงคำสั่งจะถูกดำเนินการและหากเงื่อนไขเป็นเท็จหรือเป็นโมฆะคำสั่ง IF จะไม่ทำอะไรเลย |
| 2 | คำสั่ง IF-THEN-ELSE IF statement เพิ่มคำหลัก ELSEตามด้วยลำดับทางเลือกของคำสั่ง หากเงื่อนไขเป็นเท็จหรือเป็นโมฆะลำดับทางเลือกของคำสั่งเท่านั้นที่จะถูกดำเนินการ เพื่อให้แน่ใจว่าลำดับของข้อความสั่งอย่างใดอย่างหนึ่งถูกดำเนินการ |
| 3 | คำสั่ง IF-THEN-ELSIF ช่วยให้คุณสามารถเลือกระหว่างทางเลือกต่างๆ |
| 4 | คำชี้แจงกรณี เช่นเดียวกับคำสั่ง IF ไฟล์ CASE statement เลือกหนึ่งลำดับของคำสั่งที่จะดำเนินการ อย่างไรก็ตามในการเลือกลำดับคำสั่ง CASE จะใช้ตัวเลือกแทนนิพจน์บูลีนหลายตัว ตัวเลือกคือนิพจน์ที่มีการใช้ค่าเพื่อเลือกทางเลือกหนึ่งในหลายทางเลือก |
| 5 | ค้นหาคำสั่ง CASE คำสั่ง CASE ที่ค้นหา has no selectorและเมื่อคำสั่งมีเงื่อนไขการค้นหาที่ให้ค่าบูลีน |
| 6 | IF-THEN-ELSE ที่ซ้อนกัน คุณสามารถใช้ IF-THEN หรือ IF-THEN-ELSIF คำสั่งภายในอื่น IF-THEN หรือ IF-THEN-ELSIF คำสั่ง (s) |
ในบทนี้เราจะพูดถึงลูปใน PL / SQL อาจมีสถานการณ์ที่คุณต้องเรียกใช้บล็อกโค้ดหลาย ๆ ครั้ง โดยทั่วไปคำสั่งจะดำเนินการตามลำดับ: คำสั่งแรกในฟังก์ชันจะถูกเรียกใช้งานก่อนตามด้วยคำสั่งที่สองและอื่น ๆ
ภาษาโปรแกรมจัดเตรียมโครงสร้างการควบคุมต่างๆที่ช่วยให้เส้นทางการดำเนินการซับซ้อนมากขึ้น
คำสั่งวนซ้ำช่วยให้เราดำเนินการคำสั่งหรือกลุ่มของคำสั่งได้หลายครั้งและต่อไปนี้เป็นรูปแบบทั่วไปของคำสั่งลูปในภาษาโปรแกรมส่วนใหญ่ -
PL / SQL จัดเตรียมประเภทของลูปต่อไปนี้เพื่อจัดการกับข้อกำหนดการวนซ้ำ คลิกลิงก์ต่อไปนี้เพื่อตรวจสอบรายละเอียด
| ส. เลขที่ | ประเภทห่วงและคำอธิบาย |
|---|---|
| 1 | PL / SQL Basic LOOP ในโครงสร้างลูปนี้ลำดับของคำสั่งอยู่ระหว่างคำสั่ง LOOP และคำสั่ง END LOOP ในการวนซ้ำแต่ละครั้งลำดับของคำสั่งจะถูกดำเนินการจากนั้นการควบคุมจะดำเนินการต่อที่ด้านบนของลูป |
| 2 | PL / SQL ในขณะที่ลูป ทำซ้ำคำสั่งหรือกลุ่มของคำสั่งในขณะที่เงื่อนไขที่กำหนดเป็นจริง จะทดสอบเงื่อนไขก่อนที่จะดำเนินการร่างกายลูป |
| 3 | PL / SQL สำหรับ LOOP เรียกใช้ลำดับของคำสั่งหลาย ๆ ครั้งและย่อโค้ดที่จัดการตัวแปรลูป |
| 4 | ลูปที่ซ้อนกันใน PL / SQL คุณสามารถใช้ลูปหนึ่งหรือหลายวงในลูปพื้นฐานอื่นในขณะที่หรือสำหรับลูป |
การติดฉลาก PL / SQL Loop
สามารถระบุลูป PL / SQL ได้ ฉลากควรอยู่ในวงเล็บเหลี่ยมสองมุม (<< และ >>) และปรากฏที่จุดเริ่มต้นของคำสั่ง LOOP ชื่อป้ายกำกับยังสามารถปรากฏที่ส่วนท้ายของคำสั่ง LOOP คุณอาจใช้เลเบลในคำสั่ง EXIT เพื่อออกจากลูป
โปรแกรมต่อไปนี้แสดงให้เห็นถึงแนวคิด -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
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.คำสั่งควบคุมลูป
คำสั่งควบคุมแบบวนซ้ำเปลี่ยนการดำเนินการจากลำดับปกติ เมื่อการดำเนินการออกจากขอบเขตอ็อบเจ็กต์อัตโนมัติทั้งหมดที่สร้างขึ้นในขอบเขตนั้นจะถูกทำลาย
PL / SQL สนับสนุนคำสั่งควบคุมต่อไปนี้ ลูปการติดฉลากยังช่วยในการควบคุมนอกลูป คลิกลิงก์ต่อไปนี้เพื่อตรวจสอบรายละเอียด
| ส. เลขที่ | คำชี้แจงและคำอธิบายการควบคุม |
|---|---|
| 1 | คำสั่ง EXIT คำสั่ง Exit เสร็จสิ้นการวนซ้ำและการควบคุมจะส่งผ่านไปยังคำสั่งทันทีหลังจาก END LOOP |
| 2 | คำสั่ง CONTINUE ทำให้ลูปข้ามส่วนที่เหลือของร่างกายและทดสอบสภาพของมันใหม่ทันทีก่อนที่จะย้ำอีกครั้ง |
| 3 | คำสั่ง GOTO การควบคุมการถ่ายโอนไปยังคำสั่งที่มีป้ายกำกับ แม้ว่าจะไม่แนะนำให้ใช้คำสั่ง GOTO ในโปรแกรมของคุณ |
สตริงใน PL / SQL เป็นลำดับของอักขระที่มีข้อกำหนดขนาดที่เป็นทางเลือก อักขระอาจเป็นตัวเลขตัวอักษรว่างอักขระพิเศษหรือรวมกันทั้งหมด PL / SQL มีสตริงสามประเภท -
Fixed-length strings- ในสตริงดังกล่าวโปรแกรมเมอร์จะระบุความยาวขณะประกาศสตริง สตริงถูกบุด้านขวาโดยเว้นวรรคตามความยาวที่ระบุไว้
Variable-length strings - ในสตริงดังกล่าวมีการระบุความยาวสูงสุดถึง 32,767 สำหรับสตริงและไม่มีช่องว่างภายในเกิดขึ้น
Character large objects (CLOBs) - เป็นสตริงที่มีความยาวผันแปรได้สูงสุด 128 เทราไบต์
สตริง PL / SQL อาจเป็นตัวแปรหรือตัวอักษรก็ได้ สตริงลิเทอรัลอยู่ภายในเครื่องหมายคำพูด ตัวอย่างเช่น,
'This is a string literal.' Or 'hello world'หากต้องการใส่เครื่องหมายคำพูดเดี่ยวไว้ในสตริงลิเทอรัลคุณต้องพิมพ์เครื่องหมายคำพูดเดี่ยวสองคำติดกัน ตัวอย่างเช่น,
'this isn''t what it looks like'การประกาศตัวแปรสตริง
ฐานข้อมูล Oracle มีประเภทข้อมูลสตริงมากมายเช่น CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB และ NCLOB ประเภทข้อมูลที่นำหน้าด้วย'N' คือ 'national character set' ประเภทข้อมูลที่เก็บข้อมูลอักขระ Unicode
หากคุณต้องการประกาศสตริงที่มีความยาวผันแปรคุณต้องระบุความยาวสูงสุดของสตริงนั้น ตัวอย่างเช่นชนิดข้อมูล VARCHAR2 ตัวอย่างต่อไปนี้แสดงให้เห็นถึงการประกาศและการใช้ตัวแปรสตริง -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
John Smith
Infotech
Hello! I'm John Smith from Infotech.
PL/SQL procedure successfully completedหากต้องการประกาศสตริงที่มีความยาวคงที่ให้ใช้ประเภทข้อมูล CHAR ที่นี่คุณไม่จำเป็นต้องระบุความยาวสูงสุดสำหรับตัวแปรความยาวคงที่ หากคุณละทิ้งข้อ จำกัด ด้านความยาว Oracle Database จะใช้ความยาวสูงสุดที่ต้องการโดยอัตโนมัติ คำประกาศสองคำต่อไปนี้เหมือนกัน -
red_flag CHAR(1) := 'Y';
red_flag CHAR := 'Y';ฟังก์ชันและตัวดำเนินการสตริง PL / SQL
PL / SQL เสนอตัวดำเนินการเชื่อมต่อ (||)สำหรับการเชื่อมต่อสองสตริง ตารางต่อไปนี้แสดงฟังก์ชันสตริงที่จัดเตรียมโดย PL / SQL -
| ส. เลขที่ | ฟังก์ชั่นและวัตถุประสงค์ |
|---|---|
| 1 | ASCII(x); ส่งคืนค่า ASCII ของอักขระ x |
| 2 | CHR(x); ส่งคืนอักขระที่มีค่า ASCII เป็น x |
| 3 | CONCAT(x, y); เชื่อมสตริง x และ y เข้าด้วยกันและส่งคืนสตริงต่อท้าย |
| 4 | INITCAP(x); แปลงอักษรเริ่มต้นของแต่ละคำใน x เป็นตัวพิมพ์ใหญ่และส่งกลับสตริงนั้น |
| 5 | INSTR(x, find_string [, start] [, occurrence]); ค้นหา find_string ใน x และส่งกลับตำแหน่งที่เกิดขึ้น |
| 6 | INSTRB(x); ส่งคืนตำแหน่งของสตริงภายในสตริงอื่น แต่ส่งกลับค่าเป็นไบต์ |
| 7 | LENGTH(x); ส่งคืนจำนวนอักขระใน x |
| 8 | LENGTHB(x); ส่งกลับความยาวของสตริงอักขระเป็นไบต์สำหรับชุดอักขระไบต์เดี่ยว |
| 9 | LOWER(x); แปลงตัวอักษรใน x เป็นตัวพิมพ์เล็กและส่งคืนสตริงนั้น |
| 10 | LPAD(x, width [, pad_string]) ; แผ่น x ด้วยช่องว่างทางด้านซ้ายเพื่อเพิ่มความยาวทั้งหมดของสตริงให้เท่ากับอักขระ width |
| 11 | LTRIM(x [, trim_string]); จดจ้องอักขระจากด้านซ้ายของ x. |
| 12 | NANVL(x, value); ส่งคืนค่าหาก x ตรงกับค่าพิเศษ NaN (ไม่ใช่ตัวเลข) มิฉะนั้น x จะถูกส่งกลับ |
| 13 | NLS_INITCAP(x); เหมือนกับฟังก์ชัน INITCAP ยกเว้นว่าสามารถใช้วิธีการเรียงลำดับอื่นตามที่ระบุโดย NLSSORT |
| 14 | NLS_LOWER(x) ; เหมือนกับฟังก์ชัน LOWER ยกเว้นว่าสามารถใช้วิธีการเรียงลำดับอื่นตามที่ระบุโดย NLSSORT |
| 15 | NLS_UPPER(x); เหมือนกับฟังก์ชัน UPPER ยกเว้นว่าสามารถใช้วิธีการเรียงลำดับอื่นตามที่ระบุโดย NLSSORT |
| 16 | NLSSORT(x); เปลี่ยนวิธีการเรียงลำดับอักขระ ต้องระบุก่อนฟังก์ชัน NLS ใด ๆ มิฉะนั้นจะใช้การจัดเรียงเริ่มต้น |
| 17 | NVL(x, value); ส่งคืนค่าถ้า xเป็นโมฆะ มิฉะนั้น x จะถูกส่งกลับ |
| 18 | NVL2(x, value1, value2); ส่งคืนค่า 1 ถ้า x ไม่ใช่ค่าว่าง ถ้า x เป็นโมฆะระบบจะส่งคืนค่า 2 |
| 19 | REPLACE(x, search_string, replace_string); การค้นหา x สำหรับ search_string และแทนที่ด้วย replace_string |
| 20 | RPAD(x, width [, pad_string]); แผ่น x ไปทางขวา. |
| 21 | RTRIM(x [, trim_string]); จดจ้อง x จากทางขวา |
| 22 | SOUNDEX(x) ; ส่งคืนสตริงที่มีการแสดงการออกเสียงของ x. |
| 23 | SUBSTR(x, start [, length]); ส่งคืนสตริงย่อยของ xที่เริ่มต้นในตำแหน่งที่ระบุโดยเริ่มต้น อาจมีการระบุความยาวเสริมสำหรับสตริงย่อย |
| 24 | SUBSTRB(x); เหมือนกับ SUBSTR ยกเว้นว่าพารามิเตอร์จะแสดงเป็นไบต์แทนที่จะเป็นอักขระสำหรับระบบอักขระแบบไบต์เดียว |
| 25 | TRIM([trim_char FROM) x); จดจ้องอักขระจากด้านซ้ายและขวาของ x. |
| 26 | UPPER(x); แปลงตัวอักษรใน x เป็นตัวพิมพ์ใหญ่และส่งกลับสตริงนั้น |
ตอนนี้ให้เราหาตัวอย่างเพื่อทำความเข้าใจแนวคิด -
ตัวอย่าง 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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
HELLO WORLD
hello world
Hello World
h
d
World
ello World
2
PL/SQL procedure successfully completed.ตัวอย่าง 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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
......Hello World
Hello World.....
Hello World
PL/SQL procedure successfully completed.ในบทนี้เราจะพูดถึงอาร์เรย์ใน PL / SQL ภาษาโปรแกรม PL / SQL จัดเตรียมโครงสร้างข้อมูลที่เรียกว่าVARRAYซึ่งสามารถจัดเก็บคอลเลกชันตามลำดับขนาดคงที่ขององค์ประกอบประเภทเดียวกัน varray ใช้ในการจัดเก็บการรวบรวมข้อมูลตามลำดับอย่างไรก็ตามมักจะดีกว่าถ้าคิดว่าอาร์เรย์เป็นชุดของตัวแปรประเภทเดียวกัน
ตัวแปรทั้งหมดประกอบด้วยตำแหน่งหน่วยความจำที่อยู่ติดกัน ที่อยู่ต่ำสุดสอดคล้องกับองค์ประกอบแรกและที่อยู่สูงสุดขององค์ประกอบสุดท้าย
อาร์เรย์เป็นส่วนหนึ่งของข้อมูลประเภทการรวบรวมและย่อมาจากอาร์เรย์ขนาดตัวแปร เราจะศึกษาประเภทคอลเลกชันอื่น ๆ ในบทต่อไป'PL/SQL Collections'.
แต่ละองค์ประกอบในไฟล์ varrayมีดัชนีที่เกี่ยวข้อง นอกจากนี้ยังมีขนาดสูงสุดที่สามารถเปลี่ยนแปลงได้แบบไดนามิก
การสร้าง Varray Type
ประเภท varray ถูกสร้างขึ้นด้วยไฟล์ CREATE TYPEคำให้การ. คุณต้องระบุขนาดสูงสุดและประเภทขององค์ประกอบที่จัดเก็บใน varray
ไวยากรณ์พื้นฐานสำหรับการสร้างประเภท VARRAY ที่ระดับสคีมาคือ -
CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>ที่ไหน
- varray_type_nameเป็นชื่อแอตทริบิวต์ที่ถูกต้อง
- nคือจำนวนองค์ประกอบ (สูงสุด) ใน varray
- element_typeคือประเภทข้อมูลขององค์ประกอบของอาร์เรย์
ขนาดสูงสุดของ varray สามารถเปลี่ยนแปลงได้โดยใช้ ALTER TYPE คำให้การ.
ตัวอย่างเช่น,
CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);
/
Type created.ไวยากรณ์พื้นฐานสำหรับการสร้างประเภท VARRAY ภายในบล็อก PL / SQL คือ -
TYPE varray_type_name IS VARRAY(n) of <element_type>ตัวอย่างเช่น -
TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;ตอนนี้ให้เราหาตัวอย่างเพื่อทำความเข้าใจแนวคิด -
ตัวอย่าง 1
โปรแกรมต่อไปนี้แสดงให้เห็นถึงการใช้ตัวแปร -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
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 -
ในสภาพแวดล้อม Oracle ดัชนีเริ่มต้นสำหรับตัวแปรจะเป็น 1 เสมอ
คุณสามารถเริ่มต้นองค์ประกอบ varray โดยใช้วิธีการสร้างของประเภท varray ซึ่งมีชื่อเดียวกับ varray
Varrays คืออาร์เรย์หนึ่งมิติ
varray จะเป็นโมฆะโดยอัตโนมัติเมื่อมีการประกาศและต้องเริ่มต้นก่อนจึงจะสามารถอ้างอิงองค์ประกอบได้
ตัวอย่าง 2
องค์ประกอบของ varray อาจเป็น% ROWTYPE ของตารางฐานข้อมูลหรือ% TYPE ของฟิลด์ตารางฐานข้อมูลใดก็ได้ ตัวอย่างต่อไปนี้แสดงให้เห็นถึงแนวคิด
เราจะใช้ตารางลูกค้าที่เก็บไว้ในฐานข้อมูลของเราเป็น -
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 |
+----+----------+-----+-----------+----------+ตัวอย่างต่อไปนี้ทำให้การใช้ cursorซึ่งคุณจะได้ศึกษารายละเอียดในบทแยกต่างหาก
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completed.ในบทนี้เราจะพูดถึงขั้นตอนใน PL / SQL กsubprogramเป็นหน่วยโปรแกรม / โมดูลที่ทำหน้าที่เฉพาะ โปรแกรมย่อยเหล่านี้จะรวมกันเพื่อสร้างโปรแกรมที่ใหญ่ขึ้น โดยทั่วไปเรียกว่า 'การออกแบบโมดูลาร์' โปรแกรมย่อยสามารถเรียกใช้โดยโปรแกรมย่อยหรือโปรแกรมอื่นซึ่งเรียกว่าcalling program.
สามารถสร้างโปรแกรมย่อยได้ -
- ในระดับสคีมา
- ภายในบรรจุภัณฑ์
- ภายในบล็อก PL / SQL
ในระดับสคีมาโปรแกรมย่อยคือไฟล์ standalone subprogram. สร้างขึ้นด้วยคำสั่ง CREATE PROCEDURE หรือ CREATE FUNCTION มันถูกเก็บไว้ในฐานข้อมูลและสามารถลบได้ด้วยคำสั่ง DROP PROCEDURE หรือ DROP FUNCTION
โปรแกรมย่อยที่สร้างขึ้นภายในแพ็คเกจคือไฟล์ packaged subprogram. จะถูกเก็บไว้ในฐานข้อมูลและสามารถลบได้ก็ต่อเมื่อแพ็คเกจถูกลบด้วยคำสั่ง DROP PACKAGE เราจะพูดถึงแพ็คเกจในบทนี้'PL/SQL - Packages'.
โปรแกรมย่อย PL / SQL มีชื่อว่าบล็อก PL / SQL ที่สามารถเรียกใช้ด้วยชุดพารามิเตอร์ PL / SQL มีโปรแกรมย่อยสองประเภท -
Functions- โปรแกรมย่อยเหล่านี้ส่งคืนค่าเดียว ส่วนใหญ่ใช้ในการคำนวณและส่งคืนค่า
Procedures- โปรแกรมย่อยเหล่านี้ไม่ส่งคืนค่าโดยตรง ส่วนใหญ่ใช้ในการดำเนินการ
บทนี้จะกล่าวถึงประเด็นสำคัญของไฟล์ PL/SQL procedure. เราจะหารือPL/SQL function ในบทถัดไป
ส่วนต่างๆของโปรแกรมย่อย PL / SQL
โปรแกรมย่อย PL / SQL แต่ละโปรแกรมมีชื่อและอาจมีรายการพารามิเตอร์ด้วย เช่นเดียวกับบล็อก PL / SQL ที่ไม่ระบุชื่อบล็อกที่มีชื่อจะมีสามส่วนต่อไปนี้ -
| ส. เลขที่ | อะไหล่และคำอธิบาย |
|---|---|
| 1 | Declarative Part เป็นส่วนเสริม อย่างไรก็ตามส่วนที่ประกาศสำหรับโปรแกรมย่อยไม่ได้ขึ้นต้นด้วยคีย์เวิร์ด DECLARE ประกอบด้วยการประกาศประเภทเคอร์เซอร์ค่าคงที่ตัวแปรข้อยกเว้นและโปรแกรมย่อยที่ซ้อนกัน รายการเหล่านี้อยู่ภายในโปรแกรมย่อยและจะหยุดอยู่เมื่อโปรแกรมย่อยดำเนินการเสร็จสิ้น |
| 2 | Executable Part นี่เป็นส่วนบังคับและประกอบด้วยคำสั่งที่ดำเนินการตามที่กำหนด |
| 3 | Exception-handling นี่เป็นอีกส่วนที่เป็นทางเลือก ประกอบด้วยรหัสที่จัดการข้อผิดพลาดขณะทำงาน |
การสร้างกระบวนงาน
ขั้นตอนถูกสร้างขึ้นด้วยไฟล์ CREATE OR REPLACE PROCEDUREคำให้การ. ไวยากรณ์แบบง่ายสำหรับคำสั่ง CREATE OR REPLACE PROCEDURE มีดังนี้ -
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;ที่ไหน
Procedure-nameระบุชื่อของโพรซีเดอร์
ตัวเลือก [หรือแทนที่] อนุญาตให้แก้ไขขั้นตอนที่มีอยู่
รายการพารามิเตอร์ทางเลือกประกอบด้วยชื่อโหมดและประเภทของพารามิเตอร์ IN แสดงถึงค่าที่จะถูกส่งจากภายนอกและ OUT แสดงถึงพารามิเตอร์ที่จะใช้เพื่อส่งคืนค่านอกโพรซีเดอร์
Procedure-bodyมีส่วนที่เรียกใช้งานได้
คีย์เวิร์ด AS ถูกใช้แทนคีย์เวิร์ด IS สำหรับการสร้างโพรซีเดอร์แบบสแตนด์อโลน
ตัวอย่าง
ตัวอย่างต่อไปนี้สร้างขั้นตอนง่ายๆที่แสดงสตริง 'Hello World!' บนหน้าจอเมื่อดำเนินการ
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/เมื่อโค้ดด้านบนถูกเรียกใช้โดยใช้พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Procedure created.การดำเนินการตามขั้นตอนแบบสแตนด์อโลน
ขั้นตอนแบบสแตนด์อโลนสามารถเรียกได้สองวิธี -
ใช้ EXECUTE คำสำคัญ
เรียกชื่อของโพรซีเดอร์จากบล็อก PL / SQL
ขั้นตอนข้างต้นชื่อ 'greetings' สามารถเรียกด้วยคีย์เวิร์ด EXECUTE เป็น -
EXECUTE greetings;การโทรด้านบนจะแสดง -
Hello World
PL/SQL procedure successfully completed.ขั้นตอนนี้สามารถเรียกได้จากบล็อก PL / SQL อื่น -
BEGIN
greetings;
END;
/การโทรด้านบนจะแสดง -
Hello World
PL/SQL procedure successfully completed.การลบขั้นตอนแบบสแตนด์อโลน
ขั้นตอนแบบสแตนด์อโลนจะถูกลบด้วยไฟล์ DROP PROCEDUREคำให้การ. ไวยากรณ์สำหรับการลบโพรซีเดอร์คือ -
DROP PROCEDURE procedure-name;คุณสามารถทิ้งขั้นตอนการทักทายได้โดยใช้ข้อความต่อไปนี้ -
DROP PROCEDURE greetings;โหมดพารามิเตอร์ในโปรแกรมย่อย PL / SQL
ตารางต่อไปนี้แสดงรายการโหมดพารามิเตอร์ในโปรแกรมย่อย PL / SQL -
| ส. เลขที่ | โหมดพารามิเตอร์และคำอธิบาย |
|---|---|
| 1 | IN พารามิเตอร์ IN ช่วยให้คุณส่งผ่านค่าไปยังโปรแกรมย่อย It is a read-only parameter. ภายในโปรแกรมย่อยพารามิเตอร์ IN จะทำหน้าที่เหมือนค่าคงที่ ไม่สามารถกำหนดค่าได้ คุณสามารถส่งค่าคงที่ตัวอักษรตัวแปรเริ่มต้นหรือนิพจน์เป็นพารามิเตอร์ IN คุณยังสามารถเริ่มต้นให้เป็นค่าเริ่มต้น อย่างไรก็ตามในกรณีนั้นจะถูกละเว้นจากการเรียกโปรแกรมย่อยIt is the default mode of parameter passing. Parameters are passed by reference. |
| 2 | OUT พารามิเตอร์ OUT ส่งคืนค่าไปยังโปรแกรมการโทร ภายในโปรแกรมย่อยพารามิเตอร์ OUT จะทำหน้าที่เหมือนตัวแปร คุณสามารถเปลี่ยนค่าและอ้างอิงค่าได้หลังจากกำหนดแล้วThe actual parameter must be variable and it is passed by value. |
| 3 | IN OUT อัน IN OUTพารามิเตอร์ส่งผ่านค่าเริ่มต้นไปยังโปรแกรมย่อยและส่งคืนค่าที่อัพเดตให้กับผู้เรียกใช้ สามารถกำหนดค่าและอ่านค่าได้ พารามิเตอร์จริงที่สอดคล้องกับพารามิเตอร์ทางการ IN OUT ต้องเป็นตัวแปรไม่ใช่ค่าคงที่หรือนิพจน์ ต้องกำหนดค่าพารามิเตอร์ที่เป็นทางการActual parameter is passed by value. |
โหมด IN & OUT ตัวอย่าง 1
โปรแกรมนี้ค้นหาค่าต่ำสุดของสองค่า ที่นี่ขั้นตอนใช้ตัวเลขสองตัวโดยใช้โหมด IN และส่งกลับค่าต่ำสุดโดยใช้พารามิเตอร์ 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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Minimum of (23, 45) : 23
PL/SQL procedure successfully completed.โหมด IN & OUT ตัวอย่าง 2
โพรซีเดอร์นี้คำนวณกำลังสองของค่าที่ส่งผ่าน ตัวอย่างนี้แสดงให้เห็นว่าเราสามารถใช้พารามิเตอร์เดียวกันเพื่อยอมรับค่าแล้วส่งคืนผลลัพธ์อื่นได้อย่างไร
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Square of (23): 529
PL/SQL procedure successfully completed.วิธีการส่งผ่านพารามิเตอร์
พารามิเตอร์จริงสามารถส่งผ่านได้สามวิธี -
- สัญกรณ์ตำแหน่ง
- สัญกรณ์ชื่อ
- สัญกรณ์ผสม
สัญกรณ์ตำแหน่ง
ในสัญกรณ์ตำแหน่งคุณสามารถเรียกขั้นตอนเป็น -
findMin(a, b, c, d);ในสัญกรณ์ตำแหน่งพารามิเตอร์จริงตัวแรกจะถูกแทนที่สำหรับพารามิเตอร์ที่เป็นทางการตัวแรก พารามิเตอร์จริงตัวที่สองถูกแทนที่ด้วยพารามิเตอร์ที่เป็นทางการตัวที่สองและอื่น ๆ ดังนั้น,a ถูกแทนที่ด้วย x, b ถูกแทนที่ด้วย y, c ถูกแทนที่ด้วย z และ d ถูกแทนที่ด้วย m.
ชื่อสัญกรณ์
ในสัญกรณ์ที่มีชื่อพารามิเตอร์จริงจะเชื่อมโยงกับพารามิเตอร์ที่เป็นทางการโดยใช้ arrow symbol ( => ). ขั้นตอนการเรียกใช้จะเป็นดังต่อไปนี้ -
findMin(x => a, y => b, z => c, m => d);สัญกรณ์ผสม
ในสัญกรณ์ผสมคุณสามารถผสมทั้งสองสัญกรณ์ในการเรียกโพรซีเดอร์ อย่างไรก็ตามสัญกรณ์ตำแหน่งควรนำหน้าสัญกรณ์ที่มีชื่อ
การเรียกต่อไปนี้ถูกกฎหมาย -
findMin(a, b, c, m => d);อย่างไรก็ตามสิ่งนี้ไม่ถูกกฎหมาย:
findMin(x => a, b, c, d);ในบทนี้เราจะพูดถึงฟังก์ชันใน PL / SQL ฟังก์ชันเหมือนกับโพรซีเดอร์ยกเว้นว่าจะส่งคืนค่า ดังนั้นการอภิปรายทั้งหมดในบทก่อนหน้าจึงเป็นจริงสำหรับฟังก์ชันด้วยเช่นกัน
การสร้างฟังก์ชัน
ฟังก์ชันแบบสแตนด์อโลนถูกสร้างขึ้นโดยใช้ไฟล์ CREATE FUNCTIONคำให้การ. ไวยากรณ์ที่เรียบง่ายสำหรับCREATE OR REPLACE PROCEDURE คำสั่งมีดังนี้ -
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];ที่ไหน
function-nameระบุชื่อของฟังก์ชัน
ตัวเลือก [หรือแทนที่] อนุญาตให้แก้ไขฟังก์ชันที่มีอยู่
รายการพารามิเตอร์ทางเลือกประกอบด้วยชื่อโหมดและประเภทของพารามิเตอร์ IN หมายถึงค่าที่จะถูกส่งผ่านจากภายนอกและ OUT แสดงถึงพารามิเตอร์ที่จะใช้เพื่อส่งคืนค่านอกโพรซีเดอร์
ฟังก์ชันต้องมีไฟล์ return คำให้การ.
ส่วนคำสั่งRETURNระบุชนิดข้อมูลที่คุณจะส่งคืนจากฟังก์ชัน
function-bodyมีส่วนที่เรียกใช้งานได้
คีย์เวิร์ด AS ใช้แทนคีย์เวิร์ด IS สำหรับการสร้างฟังก์ชันแบบสแตนด์อโลน
ตัวอย่าง
ตัวอย่างต่อไปนี้แสดงวิธีสร้างและเรียกใช้ฟังก์ชันแบบสแตนด์อโลน ฟังก์ชันนี้จะส่งคืนจำนวน CUSTOMERS ทั้งหมดในตารางลูกค้า
เราจะใช้ตาราง CUSTOMERS ซึ่งเราได้สร้างไว้ในบทPL / SQL Variables -
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;
/เมื่อโค้ดด้านบนถูกเรียกใช้โดยใช้พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Function created.เรียกใช้ฟังก์ชัน
ในขณะที่สร้างฟังก์ชันคุณต้องให้คำจำกัดความว่าฟังก์ชันนั้นมีไว้ทำอะไร ในการใช้ฟังก์ชันคุณจะต้องเรียกใช้ฟังก์ชันนั้นเพื่อทำงานที่กำหนดไว้ เมื่อโปรแกรมเรียกใช้ฟังก์ชันตัวควบคุมโปรแกรมจะถูกโอนไปยังฟังก์ชันที่เรียก
ฟังก์ชันที่เรียกว่าทำงานตามที่กำหนดไว้และเมื่อคำสั่ง return ถูกเรียกใช้งานหรือเมื่อ last end statement เมื่อถึงแล้วจะส่งคืนการควบคุมโปรแกรมกลับไปที่โปรแกรมหลัก
ในการเรียกใช้ฟังก์ชันคุณเพียงแค่ส่งพารามิเตอร์ที่ต้องการพร้อมกับชื่อฟังก์ชันและหากฟังก์ชันส่งคืนค่าคุณก็สามารถจัดเก็บค่าที่ส่งคืนได้ โปรแกรมต่อไปนี้เรียกใช้ฟังก์ชันtotalCustomers จากบล็อกนิรนาม -
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Total no. of Customers: 6
PL/SQL procedure successfully completed.ตัวอย่าง
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงการประกาศกำหนดและเรียกใช้ฟังก์ชัน PL / SQL แบบง่ายที่คำนวณและส่งคืนค่าสูงสุดสองค่า
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Maximum of (23,45): 45
PL/SQL procedure successfully completed.PL / SQL ฟังก์ชันเรียกซ้ำ
เราได้เห็นว่าโปรแกรมหรือโปรแกรมย่อยอาจเรียกใช้โปรแกรมย่อยอื่น เมื่อโปรแกรมย่อยเรียกตัวเองจะเรียกว่าการเรียกซ้ำและกระบวนการนี้เรียกว่าrecursion.
เพื่อแสดงแนวคิดให้เราคำนวณแฟกทอเรียลของตัวเลข แฟกทอเรียลของจำนวน n ถูกกำหนดเป็น -
n! = n*(n-1)!
= n*(n-1)*(n-2)!
...
= n*(n-1)*(n-2)*(n-3)... 1โปรแกรมต่อไปนี้จะคำนวณแฟกทอเรียลของหมายเลขที่กำหนดโดยการเรียกตัวเองแบบวนซ้ำ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Factorial 6 is 720
PL/SQL procedure successfully completed.ในบทนี้เราจะพูดถึงเคอร์เซอร์ใน PL / SQL Oracle สร้างพื้นที่หน่วยความจำหรือที่เรียกว่าบริบทพื้นที่สำหรับประมวลผลคำสั่ง SQL ซึ่งมีข้อมูลทั้งหมดที่จำเป็นสำหรับการประมวลผลคำสั่ง ตัวอย่างเช่นจำนวนแถวที่ประมวลผลเป็นต้น
ก cursorเป็นตัวชี้ไปยังพื้นที่บริบทนี้ PL / SQL ควบคุมพื้นที่บริบทผ่านเคอร์เซอร์ เคอร์เซอร์เก็บแถว (หนึ่งแถวขึ้นไป) ที่ส่งคืนโดยคำสั่ง SQL ชุดของแถวที่เคอร์เซอร์ถือเรียกว่าactive set.
คุณสามารถตั้งชื่อเคอร์เซอร์เพื่อให้สามารถอ้างถึงในโปรแกรมเพื่อดึงข้อมูลและประมวลผลแถวที่ส่งคืนโดยคำสั่ง SQL ทีละแถว เคอร์เซอร์มีสองประเภท -
- เคอร์เซอร์โดยปริยาย
- เคอร์เซอร์ที่ชัดเจน
เคอร์เซอร์โดยปริยาย
เคอร์เซอร์โดยนัยจะถูกสร้างขึ้นโดยอัตโนมัติโดย Oracle เมื่อใดก็ตามที่คำสั่ง SQL ถูกเรียกใช้เมื่อไม่มีเคอร์เซอร์ที่ชัดเจนสำหรับคำสั่ง โปรแกรมเมอร์ไม่สามารถควบคุมเคอร์เซอร์โดยนัยและข้อมูลในนั้นได้
เมื่อใดก็ตามที่มีการออกคำสั่ง DML (INSERT, UPDATE และ DELETE) เคอร์เซอร์โดยปริยายจะเชื่อมโยงกับคำสั่งนี้ สำหรับการดำเนินการ INSERT เคอร์เซอร์จะเก็บข้อมูลที่ต้องการแทรก สำหรับการดำเนินการ UPDATE และ DELETE เคอร์เซอร์จะระบุแถวที่จะได้รับผลกระทบ
ใน PL / SQL คุณสามารถอ้างถึงเคอร์เซอร์โดยนัยล่าสุดเป็นไฟล์ SQL cursorซึ่งมักจะมีแอตทริบิวต์เช่น %FOUND, %ISOPEN, %NOTFOUNDและ %ROWCOUNT. เคอร์เซอร์ SQL มีคุณลักษณะเพิ่มเติม%BULK_ROWCOUNT และ %BULK_EXCEPTIONSออกแบบมาเพื่อใช้กับไฟล์ FORALLคำให้การ. ตารางต่อไปนี้แสดงคำอธิบายของแอตทริบิวต์ที่ใช้บ่อยที่สุด -
| ส. เลขที่ | คุณสมบัติและคำอธิบาย |
|---|---|
| 1 | %FOUND ส่งกลับค่า TRUE หากคำสั่ง INSERT, UPDATE หรือ DELETE ส่งผลต่อแถวอย่างน้อยหนึ่งแถวหรือคำสั่ง SELECT INTO ส่งคืนหนึ่งแถวหรือมากกว่า มิฉะนั้นจะส่งกลับ FALSE |
| 2 | %NOTFOUND ตรงข้ามตรรกะของ% FOUND จะส่งคืนค่า TRUE หากคำสั่ง INSERT, UPDATE หรือ DELETE ส่งผลกระทบต่อไม่มีแถวหรือคำสั่ง SELECT INTO ไม่ส่งคืนแถว มิฉะนั้นจะส่งกลับ FALSE |
| 3 | %ISOPEN ส่งคืน FALSE สำหรับเคอร์เซอร์โดยปริยายเสมอเนื่องจาก Oracle ปิดเคอร์เซอร์ SQL โดยอัตโนมัติหลังจากเรียกใช้คำสั่ง SQL ที่เกี่ยวข้อง |
| 4 | %ROWCOUNT ส่งคืนจำนวนแถวที่ได้รับผลกระทบจากคำสั่ง INSERT, UPDATE หรือ DELETE หรือส่งคืนโดยคำสั่ง SELECT INTO |
แอตทริบิวต์เคอร์เซอร์ SQL ใด ๆ จะถูกเข้าถึงในรูปแบบ sql%attribute_name ดังแสดงด้านล่างในตัวอย่าง
ตัวอย่าง
เราจะใช้ตารางลูกค้าที่เราสร้างและใช้ในบทก่อนหน้านี้
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 |
+----+----------+-----+-----------+----------+โปรแกรมต่อไปนี้จะอัปเดตตารางและเพิ่มเงินเดือนของลูกค้าแต่ละราย 500 และใช้ไฟล์ SQL%ROWCOUNT แอตทริบิวต์เพื่อกำหนดจำนวนแถวที่ได้รับผลกระทบ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
6 customers selected
PL/SQL procedure successfully completed.หากคุณตรวจสอบบันทึกในตารางลูกค้าคุณจะพบว่ามีการอัปเดตแถว -
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 |
+----+----------+-----+-----------+----------+เคอร์เซอร์ที่ชัดเจน
เคอร์เซอร์ที่ชัดเจนเป็นเคอร์เซอร์ที่โปรแกรมเมอร์กำหนดขึ้นเพื่อให้สามารถควบคุมได้มากขึ้นใน context area. ควรกำหนดเคอร์เซอร์ที่ชัดเจนในส่วนการประกาศของ PL / SQL Block มันถูกสร้างขึ้นในคำสั่ง SELECT ซึ่งส่งคืนมากกว่าหนึ่งแถว
ไวยากรณ์สำหรับการสร้างเคอร์เซอร์ที่ชัดเจนคือ -
CURSOR cursor_name IS select_statement;การทำงานกับเคอร์เซอร์ที่ชัดเจนมีขั้นตอนต่อไปนี้ -
- การประกาศเคอร์เซอร์สำหรับการเริ่มต้นหน่วยความจำ
- การเปิดเคอร์เซอร์เพื่อจัดสรรหน่วยความจำ
- ดึงเคอร์เซอร์เพื่อดึงข้อมูล
- การปิดเคอร์เซอร์เพื่อปล่อยหน่วยความจำที่จัดสรร
การประกาศเคอร์เซอร์
การประกาศเคอร์เซอร์เป็นการกำหนดเคอร์เซอร์ด้วยชื่อและคำสั่ง SELECT ที่เกี่ยวข้อง ตัวอย่างเช่น -
CURSOR c_customers IS
SELECT id, name, address FROM customers;การเปิดเคอร์เซอร์
การเปิดเคอร์เซอร์จะเป็นการจัดสรรหน่วยความจำสำหรับเคอร์เซอร์และทำให้พร้อมสำหรับการดึงแถวที่ส่งคืนโดยคำสั่ง SQL เข้ามา ตัวอย่างเช่นเราจะเปิดเคอร์เซอร์ที่กำหนดไว้ข้างต้นดังนี้ -
OPEN c_customers;กำลังดึงเคอร์เซอร์
การดึงเคอร์เซอร์เกี่ยวข้องกับการเข้าถึงทีละแถว ตัวอย่างเช่นเราจะดึงแถวจากเคอร์เซอร์ที่เปิดด้านบนดังนี้ -
FETCH c_customers INTO c_id, c_name, c_addr;การปิดเคอร์เซอร์
การปิดเคอร์เซอร์หมายถึงการปล่อยหน่วยความจำที่จัดสรร ตัวอย่างเช่นเราจะปิดเคอร์เซอร์ที่เปิดด้านบนดังนี้ -
CLOSE c_customers;ตัวอย่าง
ต่อไปนี้เป็นตัวอย่างที่สมบูรณ์เพื่อแสดงแนวคิดของเคอร์เซอร์ที่ชัดเจน & 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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
PL/SQL procedure successfully completed.ในบทนี้เราจะพูดถึงบันทึกใน PL / SQL กrecordเป็นโครงสร้างข้อมูลที่สามารถเก็บรายการข้อมูลประเภทต่างๆ ระเบียนประกอบด้วยเขตข้อมูลที่แตกต่างกันคล้ายกับแถวของตารางฐานข้อมูล
ตัวอย่างเช่นคุณต้องการติดตามหนังสือของคุณในห้องสมุด คุณอาจต้องการติดตามแอตทริบิวต์ต่อไปนี้เกี่ยวกับหนังสือแต่ละเล่มเช่นชื่อผู้แต่งหัวเรื่องรหัสหนังสือ เร็กคอร์ดที่มีฟิลด์สำหรับแต่ละรายการเหล่านี้ช่วยให้ถือว่า BOOK เป็นหน่วยตรรกะและช่วยให้คุณสามารถจัดระเบียบและแสดงข้อมูลได้ดีขึ้น
PL / SQL สามารถจัดการบันทึกประเภทต่อไปนี้ -
- Table-based
- บันทึกที่ใช้เคอร์เซอร์
- ผู้ใช้กำหนดระเบียน
บันทึกตามตาราง
แอตทริบิวต์% ROWTYPE ช่วยให้โปรแกรมเมอร์สามารถสร้างได้ table-based และ cursorbased บันทึก
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงแนวคิดของ table-basedบันทึก เราจะใช้ตารางลูกค้าที่เราสร้างและใช้ในบทก่อนหน้านี้ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Customer ID: 5
Customer Name: Hardik
Customer Address: Bhopal
Customer Salary: 9000
PL/SQL procedure successfully completed.บันทึกตามเคอร์เซอร์
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงแนวคิดของ cursor-basedบันทึก เราจะใช้ตารางลูกค้าที่เราสร้างและใช้ในบทก่อนหน้านี้ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
1 Ramesh
2 Khilan
3 kaushik
4 Chaitali
5 Hardik
6 Komal
PL/SQL procedure successfully completed.บันทึกที่ผู้ใช้กำหนด
PL / SQL จัดเตรียมประเภทเรกคอร์ดที่ผู้ใช้กำหนดเองซึ่งช่วยให้คุณกำหนดโครงสร้างเรกคอร์ดที่แตกต่างกัน ระเบียนเหล่านี้ประกอบด้วยเขตข้อมูลที่แตกต่างกัน สมมติว่าคุณต้องการติดตามหนังสือของคุณในห้องสมุด คุณอาจต้องการติดตามคุณลักษณะต่อไปนี้เกี่ยวกับหนังสือแต่ละเล่ม -
- Title
- Author
- Subject
- รหัสหนังสือ
การกำหนดระเบียน
ประเภทบันทึกถูกกำหนดเป็น -
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;บันทึกหนังสือถูกประกาศด้วยวิธีต่อไปนี้ -
DECLARE
TYPE books IS RECORD
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;การเข้าถึงฟิลด์
ในการเข้าถึงฟิลด์ใด ๆ ของระเบียนเราใช้จุด (.)ตัวดำเนินการ ตัวดำเนินการเข้าถึงสมาชิกถูกเข้ารหัสเป็นช่วงเวลาระหว่างชื่อตัวแปรเร็กคอร์ดและฟิลด์ที่เราต้องการเข้าถึง ต่อไปนี้เป็นตัวอย่างเพื่ออธิบายการใช้งานบันทึก -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
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.บันทึกเป็นพารามิเตอร์ของโปรแกรมย่อย
คุณสามารถส่งบันทึกเป็นพารามิเตอร์โปรแกรมย่อยได้เช่นเดียวกับที่คุณส่งผ่านตัวแปรอื่น ๆ คุณยังสามารถเข้าถึงฟิลด์บันทึกด้วยวิธีเดียวกับที่คุณเข้าถึงในตัวอย่างด้านบน -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
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.ในบทนี้เราจะพูดถึงข้อยกเว้นใน PL / SQL ข้อยกเว้นคือเงื่อนไขข้อผิดพลาดระหว่างการทำงานของโปรแกรม PL / SQL สนับสนุนโปรแกรมเมอร์เพื่อตรวจจับเงื่อนไขดังกล่าวโดยใช้EXCEPTIONบล็อกในโปรแกรมและมีการดำเนินการที่เหมาะสมกับเงื่อนไขข้อผิดพลาด มีข้อยกเว้นสองประเภท -
- ข้อยกเว้นที่ระบบกำหนด
- ข้อยกเว้นที่ผู้ใช้กำหนด
ไวยากรณ์สำหรับการจัดการข้อยกเว้น
ไวยากรณ์ทั่วไปสำหรับการจัดการข้อยกเว้นมีดังนี้ ที่นี่คุณสามารถระบุข้อยกเว้นได้มากเท่าที่คุณสามารถจัดการได้ ข้อยกเว้นเริ่มต้นจะได้รับการจัดการโดยใช้WHEN 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;ตัวอย่าง
ให้เราเขียนโค้ดเพื่อแสดงแนวคิด เราจะใช้ตารางลูกค้าที่เราสร้างและใช้ในบทก่อนหน้านี้ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
No such customer!
PL/SQL procedure successfully completed.โปรแกรมข้างต้นแสดงชื่อและที่อยู่ของลูกค้าที่ได้รับรหัส เนื่องจากไม่มีลูกค้าที่มีค่า ID 8 ในฐานข้อมูลของเราโปรแกรมจึงเพิ่มข้อยกเว้นรันไทม์NO_DATA_FOUNDซึ่งถูกจับในไฟล์ EXCEPTION block.
การเพิ่มข้อยกเว้น
เซิร์ฟเวอร์ฐานข้อมูลจะเพิ่มข้อยกเว้นโดยอัตโนมัติเมื่อใดก็ตามที่มีข้อผิดพลาดฐานข้อมูลภายใน แต่โปรแกรมเมอร์สามารถยกข้อยกเว้นอย่างชัดเจนได้โดยใช้คำสั่ง RAISE. ต่อไปนี้เป็นไวยากรณ์ง่ายๆสำหรับการเพิ่มข้อยกเว้น -
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;คุณสามารถใช้ไวยากรณ์ข้างต้นในการเพิ่มข้อยกเว้นมาตรฐาน Oracle หรือข้อยกเว้นที่ผู้ใช้กำหนด ในส่วนถัดไปเราจะให้ตัวอย่างเกี่ยวกับการเพิ่มข้อยกเว้นที่ผู้ใช้กำหนดเอง คุณสามารถเพิ่มข้อยกเว้นมาตรฐาน Oracle ได้ในลักษณะเดียวกัน
ข้อยกเว้นที่ผู้ใช้กำหนด
PL / SQL ช่วยให้คุณกำหนดข้อยกเว้นของคุณเองตามความต้องการของโปรแกรมของคุณ ต้องมีการประกาศข้อยกเว้นที่ผู้ใช้กำหนดแล้วยกขึ้นอย่างชัดเจนโดยใช้คำสั่ง RAISE หรือโพรซีเดอร์DBMS_STANDARD.RAISE_APPLICATION_ERROR.
ไวยากรณ์สำหรับการประกาศข้อยกเว้นคือ -
DECLARE
my-exception EXCEPTION;ตัวอย่าง
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงแนวคิด โปรแกรมนี้ขอรหัสลูกค้าเมื่อผู้ใช้ป้อน ID ที่ไม่ถูกต้องข้อยกเว้นinvalid_id ถูกยกขึ้น
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
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.ข้อยกเว้นที่กำหนดไว้ล่วงหน้า
PL / SQL มีข้อยกเว้นที่กำหนดไว้ล่วงหน้าจำนวนมากซึ่งจะดำเนินการเมื่อกฎฐานข้อมูลใด ๆ ถูกละเมิดโดยโปรแกรม ตัวอย่างเช่นข้อยกเว้นที่กำหนดไว้ล่วงหน้า NO_DATA_FOUND ถูกยกขึ้นเมื่อคำสั่ง SELECT INTO ไม่ส่งคืนแถว ตารางต่อไปนี้แสดงข้อยกเว้นที่กำหนดไว้ล่วงหน้าที่สำคัญบางส่วน -
| ข้อยกเว้น | ข้อผิดพลาด Oracle | SQLCODE | คำอธิบาย |
|---|---|---|---|
| ACCESS_INTO_NULL | 06530 | -6530 | จะเพิ่มขึ้นเมื่อวัตถุว่างถูกกำหนดค่าโดยอัตโนมัติ |
| CASE_NOT_FOUND | 06592 | -6592 | จะเพิ่มขึ้นเมื่อไม่มีการเลือกตัวเลือกใดใน WHEN clause ของคำสั่ง CASE และไม่มีคำสั่ง ELSE |
| COLLECTION_IS_NULL | 06531 | -6531 | เพิ่มขึ้นเมื่อโปรแกรมพยายามใช้วิธีการรวบรวมอื่นที่ไม่ใช่ EXISTS กับตารางหรือ varray ที่ซ้อนกันโดยไม่ได้เริ่มต้นหรือโปรแกรมพยายามกำหนดค่าให้กับองค์ประกอบของตารางหรือตัวแปรที่ซ้อนกันโดยไม่ได้เริ่มต้น |
| DUP_VAL_ON_INDEX | 00001 | -1 | เพิ่มขึ้นเมื่อพยายามเก็บค่าที่ซ้ำกันในคอลัมน์ที่มีดัชนีเฉพาะ |
| INVALID_CURSOR | 01001 | -1001 | จะเพิ่มขึ้นเมื่อมีความพยายามในการดำเนินการเคอร์เซอร์ที่ไม่ได้รับอนุญาตเช่นการปิดเคอร์เซอร์ที่ยังไม่ได้เปิด |
| INVALID_NUMBER | 01722 | -1722 | เกิดขึ้นเมื่อการแปลงสตริงอักขระเป็นตัวเลขล้มเหลวเนื่องจากสตริงไม่ได้แสดงถึงตัวเลขที่ถูกต้อง |
| LOGIN_DENIED | 01017 | -1017 | เกิดขึ้นเมื่อโปรแกรมพยายามเข้าสู่ระบบฐานข้อมูลด้วยชื่อผู้ใช้หรือรหัสผ่านที่ไม่ถูกต้อง |
| ไม่พบข้อมูล | 01403 | +100 | จะเพิ่มขึ้นเมื่อคำสั่ง SELECT INTO ไม่ส่งกลับแถว |
| NOT_LOGGED_ON | 01012 | -1012 | จะเพิ่มขึ้นเมื่อมีการเรียกฐานข้อมูลโดยไม่ได้เชื่อมต่อกับฐานข้อมูล |
| PROGRAM_ERROR | 06501 | -6501 | เพิ่มขึ้นเมื่อ PL / SQL มีปัญหาภายใน |
| ROWTYPE_MISMATCH | 06504 | -6504 | เพิ่มขึ้นเมื่อเคอร์เซอร์ดึงค่าในตัวแปรที่มีชนิดข้อมูลเข้ากันไม่ได้ |
| SELF_IS_NULL | 30625 | -30625 | จะเพิ่มขึ้นเมื่อมีการเรียกใช้เมธอดสมาชิก แต่อินสแตนซ์ของชนิดอ็อบเจ็กต์ไม่ได้ถูกเตรียมใช้งาน |
| STORAGE_ERROR | 06500 | -6500 | จะเพิ่มขึ้นเมื่อ PL / SQL หน่วยความจำหมดหรือหน่วยความจำเสียหาย |
| TOO_MANY_ROWS | 01422 | -1422 | จะเพิ่มขึ้นเมื่อคำสั่ง SELECT INTO ส่งกลับมากกว่าหนึ่งแถว |
| VALUE_ERROR | 06502 | -6502 | จะเพิ่มขึ้นเมื่อเกิดข้อผิดพลาดทางคณิตศาสตร์การแปลงการตัดทอนหรือ sizeconstraint |
| ZERO_DIVIDE | 01476 | พ.ศ. 1476 | จะเพิ่มขึ้นเมื่อมีความพยายามที่จะหารตัวเลขด้วยศูนย์ |
ในบทนี้เราจะพูดถึง Triggers ใน PL / SQL ทริกเกอร์เป็นโปรแกรมที่จัดเก็บไว้ซึ่งจะดำเนินการหรือเริ่มทำงานโดยอัตโนมัติเมื่อมีเหตุการณ์บางอย่างเกิดขึ้น ในความเป็นจริงทริกเกอร์ถูกเขียนขึ้นเพื่อดำเนินการเพื่อตอบสนองต่อเหตุการณ์ใด ๆ ต่อไปนี้ -
ก database manipulation (DML) คำสั่ง (ลบแทรกหรืออัปเดต)
ก database definition (DDL) คำสั่ง (CREATE, ALTER หรือ DROP)
ก database operation (เซิร์ฟเวอร์, LOGON, LOGOFF, เริ่มต้นหรือปิดเครื่อง)
สามารถกำหนดทริกเกอร์บนตารางมุมมองสคีมาหรือฐานข้อมูลที่เกี่ยวข้องกับเหตุการณ์
ประโยชน์ของทริกเกอร์
ทริกเกอร์สามารถเขียนเพื่อวัตถุประสงค์ดังต่อไปนี้ -
- สร้างค่าคอลัมน์ที่ได้รับโดยอัตโนมัติ
- การบังคับใช้ความสมบูรณ์ของการอ้างอิง
- การบันทึกเหตุการณ์และการจัดเก็บข้อมูลในการเข้าถึงตาราง
- Auditing
- การจำลองตารางแบบซิงโครนัส
- การกำหนดสิทธิ์การรักษาความปลอดภัย
- ป้องกันการทำธุรกรรมที่ไม่ถูกต้อง
การสร้างทริกเกอร์
ไวยากรณ์สำหรับการสร้างทริกเกอร์คือ -
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;ที่ไหน
CREATE [หรือเปลี่ยน] TRIGGER trigger_name - สร้างหรือแทนที่ทริกเกอร์ที่มีอยู่กับtrigger_name
{ก่อน | หลัง | แทน} - ระบุเวลาที่จะเรียกใช้ทริกเกอร์ INSTEAD OF clause ใช้สำหรับสร้างทริกเกอร์บนมุมมอง
{ใส่ [หรือ] | อัปเดต [หรือ] | DELETE} - ระบุการดำเนินการ DML
[OF col_name] - ระบุชื่อคอลัมน์ที่จะอัปเดต
[ON table_name] - ระบุชื่อของตารางที่เกี่ยวข้องกับทริกเกอร์
[อ้างอิงเก่าเป็นใหม่ n] - สิ่งนี้ช่วยให้คุณสามารถอ้างอิงค่าใหม่และเก่าสำหรับคำสั่ง DML ต่างๆเช่น INSERT, UPDATE และ DELETE
[สำหรับแต่ละแถว] - ระบุทริกเกอร์ระดับแถวกล่าวคือทริกเกอร์จะดำเนินการสำหรับแต่ละแถวที่ได้รับผลกระทบ มิฉะนั้นทริกเกอร์จะดำเนินการเพียงครั้งเดียวเมื่อคำสั่ง SQL ถูกเรียกใช้ซึ่งเรียกว่าทริกเกอร์ระดับตาราง
WHEN (เงื่อนไข) - สิ่งนี้ให้เงื่อนไขสำหรับแถวที่ทริกเกอร์จะเริ่มทำงาน ประโยคนี้ใช้ได้กับทริกเกอร์ระดับแถวเท่านั้น
ตัวอย่าง
ในการเริ่มต้นเราจะใช้ตารางลูกค้าที่เราสร้างและใช้ในบทก่อนหน้านี้ -
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 |
+----+----------+-----+-----------+----------+โปรแกรมต่อไปนี้สร้างไฟล์ row-levelทริกเกอร์สำหรับตารางลูกค้าที่จะเริ่มทำงานสำหรับการดำเนินการ INSERT หรือ UPDATE หรือ DELETE ที่ดำเนินการบนตาราง CUSTOMERS ทริกเกอร์นี้จะแสดงความแตกต่างของเงินเดือนระหว่างค่าเก่าและค่าใหม่ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Trigger created.ต้องพิจารณาประเด็นต่อไปนี้ที่นี่ -
การอ้างอิงทั้งแบบเก่าและแบบใหม่ใช้ไม่ได้สำหรับทริกเกอร์ระดับตาราง แต่คุณสามารถใช้สำหรับทริกเกอร์ระดับบันทึกได้
หากคุณต้องการสอบถามตารางในทริกเกอร์เดียวกันคุณควรใช้คีย์เวิร์ด AFTER เนื่องจากทริกเกอร์สามารถสืบค้นตารางหรือเปลี่ยนแปลงอีกครั้งได้หลังจากใช้การเปลี่ยนแปลงครั้งแรกเท่านั้นและตารางกลับมาอยู่ในสถานะที่สอดคล้องกัน
ทริกเกอร์ด้านบนได้รับการเขียนในลักษณะที่จะเริ่มทำงานก่อนการดำเนินการ DELETE หรือ INSERT หรือ UPDATE บนตาราง แต่คุณสามารถเขียนทริกเกอร์ของคุณในการดำเนินการเดียวหรือหลายรายการได้ตัวอย่างเช่นก่อนลบซึ่งจะเริ่มทำงานเมื่อใดก็ตามที่มีการบันทึก จะถูกลบโดยใช้การดำเนินการ DELETE บนตาราง
ทริกเกอร์ทริกเกอร์
ให้เราดำเนินการ DML ในตารางลูกค้า นี่คือคำสั่ง INSERT ซึ่งจะสร้างระเบียนใหม่ในตาราง -
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );เมื่อสร้างระเบียนในตาราง CUSTOMERS ด้านบนจะสร้างทริกเกอร์ display_salary_changes จะถูกยิงและจะแสดงผลลัพธ์ต่อไปนี้ -
Old salary:
New salary: 7500
Salary difference:เนื่องจากเป็นการบันทึกใหม่เงินเดือนเก่าจึงไม่สามารถใช้ได้และผลลัพธ์ข้างต้นจึงเป็นค่าว่าง ตอนนี้ให้เราดำเนินการ DML อีกหนึ่งรายการในตารางลูกค้า คำสั่ง UPDATE จะอัปเดตบันทึกที่มีอยู่ในตาราง -
UPDATE customers
SET salary = salary + 500
WHERE id = 2;เมื่อมีการอัปเดตระเบียนในตาราง CUSTOMERS สร้างทริกเกอร์ด้านบน display_salary_changes จะถูกยิงและจะแสดงผลลัพธ์ต่อไปนี้ -
Old salary: 1500
New salary: 2000
Salary difference: 500ในบทนี้เราจะพูดถึงแพ็คเกจใน PL / SQL แพ็กเกจคือวัตถุสคีมาที่จัดกลุ่มประเภท PL / SQL ตัวแปรและโปรแกรมย่อยที่เกี่ยวข้องกันทางตรรกะ
แพ็คเกจจะมีสองส่วนบังคับ -
- ข้อกำหนดแพ็คเกจ
- เนื้อหาแพคเกจหรือคำจำกัดความ
ข้อกำหนดแพ็คเกจ
ข้อกำหนดคืออินเทอร์เฟซไปยังแพ็คเกจ ก็แค่DECLARESประเภทตัวแปรค่าคงที่ข้อยกเว้นเคอร์เซอร์และโปรแกรมย่อยที่สามารถอ้างอิงได้จากภายนอกแพ็คเกจ กล่าวอีกนัยหนึ่งคือมีข้อมูลทั้งหมดเกี่ยวกับเนื้อหาของแพ็กเกจ แต่ไม่รวมโค้ดสำหรับโปรแกรมย่อย
เรียกวัตถุทั้งหมดที่อยู่ในข้อกำหนด publicวัตถุ โปรแกรมย่อยใด ๆ ที่ไม่อยู่ในข้อกำหนดของแพ็คเกจ แต่มีรหัสอยู่ในเนื้อหาของแพ็คเกจเรียกว่าไฟล์private วัตถุ.
ข้อมูลโค้ดต่อไปนี้แสดงข้อกำหนดของแพ็คเกจที่มีโพรซีเดอร์เดียว คุณสามารถกำหนดตัวแปรส่วนกลางจำนวนมากและมีขั้นตอนหรือฟังก์ชันหลายอย่างภายในแพ็คเกจ
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Package created.ตัวแพ็คเกจ
เนื้อหาแพคเกจมีรหัสสำหรับวิธีการต่างๆที่ประกาศไว้ในข้อกำหนดแพ็คเกจและการประกาศส่วนตัวอื่น ๆ ซึ่งซ่อนจากรหัสภายนอกแพ็คเกจ
CREATE PACKAGE BODYคำสั่งใช้สำหรับการสร้างเนื้อหาแพคเกจ ข้อมูลโค้ดต่อไปนี้แสดงการประกาศเนื้อหาแพคเกจสำหรับไฟล์cust_salแพ็คเกจที่สร้างขึ้นด้านบน ผมคิดว่าเรามีอยู่แล้วลูกค้าสร้างตารางในฐานข้อมูลของเราเป็นที่กล่าวถึงในPL / SQL - ตัวแปรบท
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Package body created.การใช้องค์ประกอบแพ็คเกจ
องค์ประกอบแพ็คเกจ (ตัวแปรโพรซีเดอร์หรือฟังก์ชัน) เข้าถึงได้ด้วยไวยากรณ์ต่อไปนี้ -
package_name.element_name;พิจารณาว่าเราได้สร้างแพ็คเกจข้างต้นในสคีมาฐานข้อมูลของเราแล้วโปรแกรมต่อไปนี้ใช้ไฟล์ find_sal วิธีการของ cust_sal แพ็คเกจ -
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL ระบบจะแจ้งให้ป้อน ID ลูกค้าและเมื่อคุณป้อน ID จะแสดงเงินเดือนที่เกี่ยวข้องดังนี้ -
Enter value for cc_id: 1
Salary: 3000
PL/SQL procedure successfully completed.ตัวอย่าง
โปรแกรมต่อไปนี้มีแพ็คเกจที่สมบูรณ์มากขึ้น เราจะใช้ตารางลูกค้าที่เก็บไว้ในฐานข้อมูลของเราโดยมีบันทึกต่อไปนี้ -
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 |
+----+----------+-----+-----------+----------+ข้อกำหนดแพ็คเกจ
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะสร้างแพ็คเกจด้านบนและแสดงผลลัพธ์ต่อไปนี้ -
Package created.การสร้างเนื้อหาแพ็คเกจ
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;
/ตัวอย่างข้างต้นใช้ประโยชน์จากไฟล์ nested table. เราจะพูดถึงแนวคิดของตารางที่ซ้อนกันในบทถัดไป
เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Package body created.การใช้แพ็คเกจ
โปรแกรมต่อไปนี้ใช้วิธีการประกาศและกำหนดไว้ในแพคเกจ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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
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ในบทนี้เราจะพูดถึง Collections ใน PL / SQL คอลเลกชันคือกลุ่มขององค์ประกอบที่เรียงลำดับซึ่งมีชนิดข้อมูลเดียวกัน แต่ละองค์ประกอบถูกระบุโดยตัวห้อยที่ไม่ซ้ำกันซึ่งแสดงถึงตำแหน่งในคอลเล็กชัน
PL / SQL มีคอลเลกชันสามประเภท -
- ตารางดัชนีหรืออาร์เรย์ Associative
- ตารางที่ซ้อนกัน
- อาร์เรย์ขนาดตัวแปรหรือ Varray
เอกสาร Oracle มีคุณสมบัติดังต่อไปนี้สำหรับคอลเล็กชันแต่ละประเภท -
| ประเภทคอลเลกชัน | จำนวนองค์ประกอบ | ประเภทตัวติดตาม | หนาแน่นหรือเบาบาง | สร้างที่ไหน | สามารถเป็นแอตทริบิวต์ประเภทวัตถุ |
|---|---|---|---|---|---|
| อาร์เรย์เชื่อมโยง (หรือตารางดัชนี) | ไม่มีขอบเขต | สตริงหรือจำนวนเต็ม | ทั้ง | เฉพาะในบล็อก PL / SQL | ไม่ |
| ตารางที่ซ้อนกัน | ไม่มีขอบเขต | จำนวนเต็ม | เริ่มหนาแน่นอาจเบาบางลง | ทั้งในบล็อก PL / SQL หรือที่ระดับสคีมา | ใช่ |
| ตัวแปรขนาดอาร์เรย์ (Varray) | ผูกมัด | จำนวนเต็ม | หนาแน่นเสมอ | ทั้งในบล็อก PL / SQL หรือที่ระดับสคีมา | ใช่ |
เราได้กล่าวถึง varray ในบทนี้แล้ว 'PL/SQL arrays'. ในบทนี้เราจะพูดถึงตาราง PL / SQL
ตาราง PL / SQL ทั้งสองประเภท ได้แก่ ตารางดัชนีโดยและตารางที่ซ้อนกันมีโครงสร้างเหมือนกันและมีการเข้าถึงแถวโดยใช้สัญกรณ์ตัวห้อย อย่างไรก็ตามตารางทั้งสองประเภทนี้แตกต่างกันในแง่มุมหนึ่ง ตารางที่ซ้อนกันสามารถเก็บไว้ในคอลัมน์ฐานข้อมูลและตารางดัชนีโดยไม่สามารถ
ตารางดัชนีโดย
อัน index-by ตาราง (เรียกอีกอย่างว่า associative array) เป็นชุดของ key-valueคู่ แต่ละคีย์ไม่ซ้ำกันและใช้เพื่อค้นหาค่าที่เกี่ยวข้อง คีย์อาจเป็นจำนวนเต็มหรือสตริงก็ได้
ตารางดัชนีโดยสร้างขึ้นโดยใช้ไวยากรณ์ต่อไปนี้ ที่นี่เรากำลังสร้างไฟล์index-by ตารางชื่อ table_nameคีย์ซึ่งจะเป็น subscript_type และค่าที่เกี่ยวข้องจะเป็นของelement_type
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;ตัวอย่าง
ตัวอย่างต่อไปนี้แสดงวิธีการสร้างตารางเพื่อเก็บค่าจำนวนเต็มพร้อมกับชื่อและหลังจากนั้นจะพิมพ์รายชื่อเดียวกัน
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
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.ตัวอย่าง
องค์ประกอบของตารางดัชนีโดยอาจเป็นไฟล์ %ROWTYPE ของตารางฐานข้อมูลหรือ %TYPEของฟิลด์ตารางฐานข้อมูลใด ๆ ตัวอย่างต่อไปนี้แสดงให้เห็นถึงแนวคิด เราจะใช้ไฟล์CUSTOMERS ตารางที่จัดเก็บในฐานข้อมูลของเราเป็น -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completedตารางที่ซ้อนกัน
ก nested tableเปรียบเสมือนอาร์เรย์หนึ่งมิติที่มีองค์ประกอบตามจำนวนที่กำหนด อย่างไรก็ตามตารางที่ซ้อนกันแตกต่างจากอาร์เรย์ในแง่มุมต่อไปนี้ -
อาร์เรย์มีจำนวนองค์ประกอบที่ประกาศไว้ แต่ตารางที่ซ้อนกันไม่มี ขนาดของตารางที่ซ้อนกันสามารถเพิ่มขึ้นแบบไดนามิก
อาร์เรย์มีความหนาแน่นเสมอกล่าวคือมีตัวห้อยที่ต่อเนื่องกันเสมอ อาร์เรย์ที่ซ้อนกันมีความหนาแน่นในตอนแรก แต่อาจกระจัดกระจายเมื่อองค์ประกอบถูกลบออกไป
ตารางที่ซ้อนกันถูกสร้างขึ้นโดยใช้ไวยากรณ์ต่อไปนี้ -
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;คำประกาศนี้คล้ายกับการประกาศ index-by โต๊ะ แต่ไม่มี INDEX BY อนุประโยค
ตารางที่ซ้อนกันสามารถจัดเก็บไว้ในคอลัมน์ฐานข้อมูล นอกจากนี้ยังสามารถใช้เพื่อลดความซับซ้อนของการดำเนินการ SQL โดยที่คุณเข้าร่วมตารางคอลัมน์เดียวกับตารางที่ใหญ่ขึ้น ไม่สามารถจัดเก็บอาร์เรย์ที่เชื่อมโยงในฐานข้อมูลได้
ตัวอย่าง
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงการใช้ตารางที่ซ้อนกัน -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
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.ตัวอย่าง
องค์ประกอบของไฟล์ nested table ยังสามารถเป็นไฟล์ %ROWTYPEของตารางฐานข้อมูลหรือ% TYPE ของเขตข้อมูลตารางฐานข้อมูลใด ๆ ตัวอย่างต่อไปนี้แสดงให้เห็นถึงแนวคิด เราจะใช้ตารางลูกค้าที่เก็บไว้ในฐานข้อมูลของเราเป็น -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completed.วิธีการรวบรวม
PL / SQL มีวิธีการรวบรวมในตัวที่ทำให้คอลเลกชันใช้งานง่ายขึ้น ตารางต่อไปนี้แสดงวิธีการและวัตถุประสงค์ -
| ส. เลขที่ | ชื่อวิธีการและวัตถุประสงค์ |
|---|---|
| 1 | EXISTS(n) ส่งคืนค่า TRUE หากองค์ประกอบที่ n ในคอลเล็กชันมีอยู่ มิฉะนั้นจะคืนค่า FALSE |
| 2 | COUNT ส่งคืนจำนวนองค์ประกอบที่คอลเลกชันมีอยู่ในปัจจุบัน |
| 3 | LIMIT ตรวจสอบขนาดสูงสุดของคอลเลกชัน |
| 4 | FIRST ส่งคืนหมายเลขดัชนีแรก (เล็กที่สุด) ในคอลเล็กชันที่ใช้ตัวห้อยจำนวนเต็ม |
| 5 | LAST ส่งคืนหมายเลขดัชนีสุดท้าย (ใหญ่ที่สุด) ในคอลเล็กชันที่ใช้ตัวห้อยจำนวนเต็ม |
| 6 | PRIOR(n) ส่งคืนหมายเลขดัชนีที่นำหน้าดัชนี n ในคอลเล็กชัน |
| 7 | NEXT(n) ส่งคืนหมายเลขดัชนีที่สำเร็จดัชนี n |
| 8 | EXTEND ผนวกองค์ประกอบ null หนึ่งเข้ากับคอลเลกชัน |
| 9 | EXTEND(n) ผนวกองค์ประกอบ n null เข้ากับคอลเลกชัน |
| 10 | EXTEND(n,i) ต่อท้าย nสำเนาขององค์ประกอบi thไปยังคอลเลกชัน |
| 11 | TRIM ลบองค์ประกอบหนึ่งออกจากส่วนท้ายของคอลเลกชัน |
| 12 | TRIM(n) ลบ n องค์ประกอบจากส่วนท้ายของคอลเลกชัน |
| 13 | DELETE ลบองค์ประกอบทั้งหมดออกจากคอลเลกชันโดยตั้งค่า COUNT เป็น 0 |
| 14 | DELETE(n) ลบไฟล์ nthองค์ประกอบจากอาร์เรย์ที่เชื่อมโยงด้วยคีย์ตัวเลขหรือตารางที่ซ้อนกัน หากอาร์เรย์ที่เชื่อมโยงมีคีย์สตริงองค์ประกอบที่ตรงกับค่าคีย์จะถูกลบ ถ้าn เป็นโมฆะ DELETE(n) ไม่ทำอะไรเลย |
| 15 | DELETE(m,n) ลบองค์ประกอบทั้งหมดในช่วง m..nจากอาร์เรย์ที่เชื่อมโยงหรือตารางที่ซ้อนกัน ถ้าm มีขนาดใหญ่กว่า n หรือถ้า m หรือ n เป็นโมฆะ DELETE(m,n) ไม่ทำอะไรเลย |
ข้อยกเว้นของคอลเลกชัน
ตารางต่อไปนี้ระบุข้อยกเว้นของคอลเลกชันและเมื่อมีการยกขึ้น -
| ข้อยกเว้นการรวบรวม | เพิ่มขึ้นในสถานการณ์ |
|---|---|
| COLLECTION_IS_NULL | คุณพยายามดำเนินการกับคอลเลกชันที่เป็นโมฆะอะตอม |
| ไม่พบข้อมูล | ตัวห้อยกำหนดองค์ประกอบที่ถูกลบหรือองค์ประกอบที่ไม่มีอยู่ของอาร์เรย์ที่เชื่อมโยง |
| SUBSCRIPT_BEYOND_COUNT | ตัวห้อยเกินจำนวนองค์ประกอบในคอลเลกชัน |
| SUBSCRIPT_OUTSIDE_LIMIT | ตัวห้อยอยู่นอกช่วงที่อนุญาต |
| VALUE_ERROR | ตัวห้อยเป็นโมฆะหรือไม่สามารถแปลงเป็นประเภทคีย์ได้ ข้อยกเว้นนี้อาจเกิดขึ้นหากคีย์ถูกกำหนดเป็นไฟล์PLS_INTEGER ช่วงและตัวห้อยอยู่นอกช่วงนี้ |
ในบทนี้เราจะพูดถึงธุรกรรมใน PL / SQL ฐานข้อมูลtransactionเป็นหน่วยอะตอมของงานที่อาจประกอบด้วยคำสั่ง SQL ที่เกี่ยวข้องอย่างน้อยหนึ่งคำสั่ง เรียกว่า atomic เนื่องจากการปรับเปลี่ยนฐานข้อมูลที่เกิดจากคำสั่ง SQL ที่ประกอบเป็นธุรกรรมสามารถรวมกันได้เช่นทำให้ถาวรกับฐานข้อมูลหรือย้อนกลับ (ยกเลิก) จากฐานข้อมูล
คำสั่ง SQL ที่ดำเนินการสำเร็จและธุรกรรมที่ถูกผูกมัดจะไม่เหมือนกัน แม้ว่าคำสั่ง SQL จะดำเนินการได้สำเร็จเว้นแต่ว่าธุรกรรมที่มีคำสั่งจะถูกผูกมัดก็สามารถย้อนกลับได้และการเปลี่ยนแปลงทั้งหมดที่ทำโดยคำสั่งนั้นสามารถยกเลิกได้
การเริ่มต้นและการสิ้นสุดธุรกรรม
ธุรกรรมมี beginning และ end. ธุรกรรมเริ่มต้นเมื่อเกิดเหตุการณ์อย่างใดอย่างหนึ่งต่อไปนี้ -
คำสั่ง SQL แรกดำเนินการหลังจากเชื่อมต่อกับฐานข้อมูล
ในแต่ละคำสั่ง SQL ใหม่ที่ออกหลังจากธุรกรรมเสร็จสมบูรณ์
ธุรกรรมจะสิ้นสุดลงเมื่อเกิดเหตุการณ์อย่างใดอย่างหนึ่งต่อไปนี้ -
ก COMMIT หรือก ROLLBACK ออกคำสั่ง
ก DDL คำสั่งเช่น CREATE TABLEออกคำสั่ง; เนื่องจากในกรณีนี้จะมีการดำเนินการ COMMIT โดยอัตโนมัติ
ก DCL คำสั่งเช่นก GRANTออกคำสั่ง; เนื่องจากในกรณีนี้จะมีการดำเนินการ COMMIT โดยอัตโนมัติ
ผู้ใช้ตัดการเชื่อมต่อจากฐานข้อมูล
ผู้ใช้ออกจาก SQL*PLUS โดยการออก EXIT คำสั่ง COMMIT จะดำเนินการโดยอัตโนมัติ
SQL * Plus สิ้นสุดอย่างผิดปกติ a ROLLBACK จะดำเนินการโดยอัตโนมัติ
ก DMLคำสั่งล้มเหลว ในกรณีนั้นจะมีการดำเนินการ ROLLBACK โดยอัตโนมัติสำหรับการยกเลิกคำสั่ง DML นั้น
การทำธุรกรรม
ธุรกรรมถูกทำให้ถาวรโดยการออกคำสั่ง SQL COMMIT ไวยากรณ์ทั่วไปสำหรับคำสั่ง COMMIT คือ -
COMMIT;ตัวอย่างเช่น,
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;การย้อนกลับธุรกรรม
การเปลี่ยนแปลงที่เกิดขึ้นกับฐานข้อมูลโดยไม่มี COMMIT สามารถยกเลิกได้โดยใช้คำสั่ง ROLLBACK
ไวยากรณ์ทั่วไปสำหรับคำสั่ง ROLLBACK คือ -
ROLLBACK [TO SAVEPOINT < savepoint_name>];เมื่อธุรกรรมถูกยกเลิกเนื่องจากสถานการณ์ที่ไม่เคยเกิดขึ้นมาก่อนเช่นความล้มเหลวของระบบธุรกรรมทั้งหมดเนื่องจากการคอมมิตจะถูกย้อนกลับโดยอัตโนมัติ หากคุณไม่ได้ใช้savepointจากนั้นใช้คำสั่งต่อไปนี้เพื่อย้อนกลับการเปลี่ยนแปลงทั้งหมด -
ROLLBACK;Savepoints
Savepoints คือเครื่องหมายที่ช่วยในการแบ่งธุรกรรมที่ยาวออกเป็นหน่วยย่อย ๆ โดยการตั้งจุดตรวจสอบ คุณสามารถย้อนกลับไปยังจุดตรวจสอบได้หากจำเป็น ทำได้โดยการออกไฟล์SAVEPOINT คำสั่ง
ไวยากรณ์ทั่วไปสำหรับคำสั่ง SAVEPOINT คือ -
SAVEPOINT < savepoint_name >;ตัวอย่างเช่น
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 - คำสั่งนี้ย้อนกลับการเปลี่ยนแปลงทั้งหมดจนถึงจุดที่คุณทำเครื่องหมาย savepoint sav1
หลังจากนั้นการเปลี่ยนแปลงใหม่ที่คุณทำจะเริ่มขึ้น
การควบคุมธุรกรรมอัตโนมัติ
เพื่อเรียกใช้งานไฟล์ COMMIT โดยอัตโนมัติเมื่อใดก็ตามที่ไฟล์ INSERT, UPDATE หรือ DELETE ดำเนินการคำสั่งคุณสามารถตั้งค่าไฟล์ AUTOCOMMIT ตัวแปรสภาพแวดล้อมเป็น -
SET AUTOCOMMIT ON;คุณสามารถปิดโหมดการคอมมิตอัตโนมัติโดยใช้คำสั่งต่อไปนี้ -
SET AUTOCOMMIT OFF;ในบทนี้เราจะพูดถึงวันที่และเวลาใน PL / SQL ประเภทข้อมูลที่เกี่ยวข้องกับวันที่และเวลามีอยู่สองประเภทใน PL / SQL -
- ชนิดข้อมูลวันที่และเวลา
- ประเภทข้อมูลช่วงเวลา
ชนิดข้อมูล Datetime คือ -
- DATE
- TIMESTAMP
- จับเวลาด้วยโซนเวลา
- การจับเวลาด้วยโซนเวลาท้องถิ่น
ประเภทข้อมูลช่วงเวลาคือ -
- ช่วงปีต่อเดือน
- ระหว่างวันถึงวินาที
ค่าฟิลด์สำหรับชนิดข้อมูลวันที่เวลาและช่วงเวลา
ทั้งสอง datetime และ interval ประเภทข้อมูลประกอบด้วย fields. ค่าของฟิลด์เหล่านี้กำหนดมูลค่าของชนิดข้อมูล ตารางต่อไปนี้แสดงรายการเขตข้อมูลและค่าที่เป็นไปได้สำหรับวันที่และช่วงเวลา
| ชื่อฟิลด์ | ค่า Datetime ที่ถูกต้อง | ค่าช่วงเวลาที่ถูกต้อง |
|---|---|---|
| ปี | -4712 ถึง 9999 (ไม่รวมปี 0) | จำนวนเต็มที่ไม่ใช่ศูนย์ |
| เดือน | 01 ถึง 12 | 0 ถึง 11 |
| วัน | 01 ถึง 31 (จำกัด โดยค่าของ MONTH และ YEAR ตามกฎของปฏิทินสำหรับภาษา) | จำนวนเต็มที่ไม่ใช่ศูนย์ |
| ชั่วโมง | 00 ถึง 23 | 0 ถึง 23 |
| นาที | 00 ถึง 59 | 0 ถึง 59 |
| วินาที | 00 ถึง 59.9 (n) โดยที่ 9 (n) คือความแม่นยำของเวลาเศษส่วนของวินาที ส่วน 9 (n) ใช้ไม่ได้สำหรับ DATE |
0 ถึง 59.9 (n) โดยที่ 9 (n) คือความแม่นยำของช่วงเศษส่วนของวินาที |
| TIMEZONE_HOUR | -12 ถึง 14 (ช่วงรองรับการเปลี่ยนแปลงเวลาออมแสง) ไม่สามารถใช้ได้กับ DATE หรือ TIMESTAMP |
ไม่สามารถใช้ได้ |
| TIMEZONE_MINUTE | 00 ถึง 59 ไม่สามารถใช้ได้กับ DATE หรือ TIMESTAMP |
ไม่สามารถใช้ได้ |
| TIMEZONE_REGION | ไม่สามารถใช้ได้กับ DATE หรือ TIMESTAMP | ไม่สามารถใช้ได้ |
| TIMEZONE_ABBR | ไม่สามารถใช้ได้กับ DATE หรือ TIMESTAMP | ไม่สามารถใช้ได้ |
ประเภทข้อมูลและฟังก์ชันวันที่และเวลา
ต่อไปนี้เป็นประเภทข้อมูล Datetime -
วันที่
จัดเก็บข้อมูลวันที่และเวลาทั้งในประเภทข้อมูลตัวอักษรและตัวเลข มันทำจากข้อมูลศตวรรษปีเดือนวันที่ชั่วโมงนาทีและวินาที ระบุเป็น -
TIMESTAMP
เป็นส่วนขยายของประเภทข้อมูล DATE โดยจะจัดเก็บปีเดือนและวันของประเภทข้อมูล DATE พร้อมกับค่าชั่วโมงนาทีและวินาที มีประโยชน์ในการจัดเก็บค่าเวลาที่แม่นยำ
จับเวลาด้วยโซนเวลา
เป็นตัวแปรของ TIMESTAMP ที่มีชื่อเขตเวลาหรือเขตเวลาออฟเซ็ตในค่า การชดเชยโซนเวลาคือความแตกต่าง (เป็นชั่วโมงและนาที) ระหว่างเวลาท้องถิ่นและ UTC ประเภทข้อมูลนี้มีประโยชน์สำหรับการรวบรวมและประเมินข้อมูลวันที่ในพื้นที่ทางภูมิศาสตร์
การจับเวลาด้วยโซนเวลาท้องถิ่น
เป็นอีกตัวแปรหนึ่งของ TIMESTAMP ที่มีการชดเชยเขตเวลาในค่า
ตารางต่อไปนี้จัดเตรียมฟังก์ชัน Datetime (โดยที่ x มีค่าวันที่และเวลา) -
| ส. เลขที่ | ชื่อฟังก์ชันและคำอธิบาย |
|---|---|
| 1 | ADD_MONTHS(x, y); เพิ่ม y เดือนถึง x. |
| 2 | LAST_DAY(x); ส่งคืนวันสุดท้ายของเดือน |
| 3 | MONTHS_BETWEEN(x, y); ส่งกลับจำนวนเดือนระหว่าง x และ y. |
| 4 | NEXT_DAY(x, day); ส่งคืนวันที่และเวลาของวันถัดไปหลังจากนั้นx. |
| 5 | NEW_TIME; ส่งคืนค่าเวลา / วันจากเขตเวลาที่ผู้ใช้ระบุ |
| 6 | ROUND(x [, unit]); รอบ x. |
| 7 | SYSDATE(); ส่งคืนวันที่และเวลาปัจจุบัน |
| 8 | TRUNC(x [, unit]); ตัดทอน x. |
ฟังก์ชันการประทับเวลา (โดยที่ x มีค่าการประทับเวลา) -
| ส. เลขที่ | ชื่อฟังก์ชันและคำอธิบาย |
|---|---|
| 1 | CURRENT_TIMESTAMP(); ส่งคืน TIMESTAMP พร้อม TIME ZONE ที่มีเวลาเซสชันปัจจุบันพร้อมกับโซนเวลาเซสชัน |
| 2 | EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) แยกและส่งคืนปีเดือนวันชั่วโมงนาทีวินาทีหรือเขตเวลาจาก x. |
| 3 | FROM_TZ(x, time_zone); แปลง TIMESTAMP x และเขตเวลาที่ระบุโดย time_zone เป็น TIMESTAMP WITH TIMEZONE |
| 4 | LOCALTIMESTAMP(); ส่งคืน TIMESTAMP ที่มีเวลาท้องถิ่นในโซนเวลาของเซสชัน |
| 5 | SYSTIMESTAMP(); ส่งคืน TIMESTAMP พร้อม TIME ZONE ที่มีเวลาฐานข้อมูลปัจจุบันพร้อมกับโซนเวลาฐานข้อมูล |
| 6 | SYS_EXTRACT_UTC(x); แปลง TIMESTAMP ด้วย TIMEZONE x เป็น TIMESTAMP ที่มีวันที่และเวลาเป็น UTC |
| 7 | TO_TIMESTAMP(x, [format]); แปลงสตริง x เป็น TIMESTAMP |
| 8 | TO_TIMESTAMP_TZ(x, [format]); แปลงสตริง x เป็น TIMESTAMP ด้วย TIMEZONE |
ตัวอย่าง
ข้อมูลโค้ดต่อไปนี้แสดงให้เห็นถึงการใช้ฟังก์ชันข้างต้น -
Example 1
SELECT SYSDATE FROM DUAL;Output -
08/31/2012 5:25:34 PMExample 2
SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;Output -
31-08-2012 05:26:14Example 3
SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;Output -
01/31/2013 5:26:31 PMExample 4
SELECT LOCALTIMESTAMP FROM DUAL;Output -
8/31/2012 5:26:55.347000 PMประเภทและฟังก์ชั่นข้อมูลช่วงเวลา
ต่อไปนี้เป็นประเภทข้อมูลช่วงเวลา -
IINTERVAL YEAR TO MONTH - เก็บช่วงเวลาหนึ่งโดยใช้ฟิลด์ YEAR และ MONTH datetime
ช่วงเวลาระหว่างวันถึงวินาที - จัดเก็บช่วงเวลาในรูปแบบของวันชั่วโมงนาทีและวินาที
ฟังก์ชันช่วงเวลา
| ส. เลขที่ | ชื่อฟังก์ชันและคำอธิบาย |
|---|---|
| 1 | NUMTODSINTERVAL(x, interval_unit); แปลงจำนวน x เป็นช่วงเวลาระหว่างวันเป็นวินาที |
| 2 | NUMTOYMINTERVAL(x, interval_unit); แปลงจำนวน x เป็น INTERVAL YEAR TO MONTH |
| 3 | TO_DSINTERVAL(x); แปลงสตริง x เป็นช่วงเวลาระหว่างวันเป็นวินาที |
| 4 | TO_YMINTERVAL(x); แปลงสตริง x เป็น INTERVAL YEAR TO MONTH |
ในบทนี้เราจะพูดถึง DBMS Output ใน PL / SQL DBMS_OUTPUTเป็นแพ็คเกจในตัวที่ช่วยให้คุณสามารถแสดงเอาต์พุตข้อมูลการดีบักและส่งข้อความจากบล็อก PL / SQL โปรแกรมย่อยแพ็คเกจและทริกเกอร์ เราได้ใช้แพ็คเกจนี้ตลอดการสอนของเราแล้ว
ให้เราดูข้อมูลโค้ดขนาดเล็กที่จะแสดงตารางผู้ใช้ทั้งหมดในฐานข้อมูล ลองใช้ในฐานข้อมูลของคุณเพื่อแสดงรายชื่อตารางทั้งหมด -
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;
/โปรแกรมย่อย DBMS_OUTPUT
แพ็คเกจ DBMS_OUTPUT มีโปรแกรมย่อยดังต่อไปนี้ -
| ส. เลขที่ | โปรแกรมย่อยและวัตถุประสงค์ | |
|---|---|---|
| 1 | DBMS_OUTPUT.DISABLE; ปิดการใช้งานเอาต์พุตข้อความ |
|
| 2 | DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); เปิดใช้งานเอาต์พุตข้อความ ค่า NULL ของbuffer_size แสดงถึงขนาดบัฟเฟอร์ที่ไม่ จำกัด |
|
| 3 | DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER); ดึงข้อมูลบัฟเฟอร์บรรทัดเดียว |
|
| 4 | DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER); ดึงอาร์เรย์ของบรรทัดจากบัฟเฟอร์ |
|
| 5 | DBMS_OUTPUT.NEW_LINE; ใส่เครื่องหมายจุดสิ้นสุดของบรรทัด |
|
| 6 | DBMS_OUTPUT.PUT(item IN VARCHAR2); วางเส้นบางส่วนในบัฟเฟอร์ |
|
| 7 | DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); วางเส้นในบัฟเฟอร์ |
ตัวอย่าง
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Hello Reader!
Hope you have enjoyed the tutorials!
Have a great time exploring pl/sql!
PL/SQL procedure successfully completed.ในบทนี้เราจะพูดถึง Object-Oriented PL / SQL PL / SQL อนุญาตให้กำหนดประเภทวัตถุซึ่งช่วยในการออกแบบฐานข้อมูลเชิงวัตถุใน Oracle ประเภทออบเจ็กต์ช่วยให้คุณสร้างประเภทคอมโพสิต การใช้อ็อบเจกต์ช่วยให้คุณสามารถใช้อ็อบเจ็กต์ในโลกแห่งความเป็นจริงโดยมีโครงสร้างข้อมูลและวิธีการที่เฉพาะเจาะจง ออบเจ็กต์มีคุณลักษณะและวิธีการ แอตทริบิวต์เป็นคุณสมบัติของวัตถุและใช้สำหรับจัดเก็บสถานะของวัตถุ และใช้วิธีการในการสร้างแบบจำลองพฤติกรรม
ออบเจ็กต์ถูกสร้างโดยใช้คำสั่ง CREATE [OR REPLACE] TYPE ต่อไปนี้เป็นตัวอย่างในการสร้างไฟล์address วัตถุที่ประกอบด้วยคุณลักษณะบางอย่าง -
CREATE OR REPLACE TYPE address AS OBJECT
(house_no varchar2(10),
street varchar2(30),
city varchar2(20),
state varchar2(10),
pincode varchar2(10)
);
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type created.มาสร้างอีกหนึ่งวัตถุ customer ที่เราจะห่อ attributes และ methods ร่วมกันมีความรู้สึกเชิงวัตถุ -
CREATE OR REPLACE TYPE customer AS OBJECT
(code number(5),
name varchar2(30),
contact_no varchar2(12),
addr address,
member procedure display
);
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type created.การสร้างอินสแตนซ์วัตถุ
การกำหนดประเภทวัตถุจะจัดเตรียมพิมพ์เขียวสำหรับวัตถุ ในการใช้วัตถุนี้คุณต้องสร้างอินสแตนซ์ของวัตถุนี้ คุณสามารถเข้าถึงแอตทริบิวต์และวิธีการของวัตถุโดยใช้ชื่ออินสแตนซ์และthe access operator (.) ดังต่อไปนี้ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
House No: 103A
Street: M.G.Road
City: Jaipur
State: Rajasthan
Pincode: 201301
PL/SQL procedure successfully completed.วิธีการสมาชิก
Member methods ใช้สำหรับจัดการไฟล์ attributesของวัตถุ คุณจัดเตรียมการประกาศเมธอดสมาชิกในขณะที่ประกาศประเภทอ็อบเจ็กต์ เนื้อวัตถุกำหนดรหัสสำหรับเมธอดสมาชิก เนื้อวัตถุถูกสร้างขึ้นโดยใช้คำสั่ง CREATE TYPE BODY
Constructorsคือฟังก์ชันที่ส่งคืนวัตถุใหม่เป็นค่าของมัน ทุกออบเจ็กต์มีวิธีการสร้างที่กำหนดโดยระบบ ชื่อของตัวสร้างจะเหมือนกับชนิดวัตถุ ตัวอย่างเช่น -
residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');comparison methodsใช้สำหรับการเปรียบเทียบวัตถุ มีสองวิธีในการเปรียบเทียบวัตถุ -
วิธีแผนที่
Map methodเป็นฟังก์ชันที่ใช้ในลักษณะที่ค่าของมันขึ้นอยู่กับค่าของคุณลักษณะ ตัวอย่างเช่นสำหรับวัตถุของลูกค้าหากรหัสลูกค้าเหมือนกันสำหรับลูกค้าสองรายลูกค้าทั้งสองอาจเหมือนกัน ดังนั้นความสัมพันธ์ระหว่างวัตถุทั้งสองนี้จะขึ้นอยู่กับค่าของรหัส
วิธีการสั่งซื้อ
Order methodใช้ตรรกะภายในบางอย่างเพื่อเปรียบเทียบวัตถุสองชิ้น ตัวอย่างเช่นสำหรับวัตถุสี่เหลี่ยมผืนผ้ารูปสี่เหลี่ยมผืนผ้าจะใหญ่กว่าอีกรูปหนึ่งหากทั้งสองด้านใหญ่กว่า
ใช้วิธีแผนที่
ให้เราพยายามทำความเข้าใจแนวคิดข้างต้นโดยใช้วัตถุสี่เหลี่ยมผืนผ้าต่อไปนี้ -
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
);
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type created.การสร้างตัวพิมพ์ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type body created.ตอนนี้ใช้วัตถุสี่เหลี่ยมผืนผ้าและฟังก์ชันสมาชิก -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Length: 8
Width: 9
Length: 5
Width: 7
PL/SQL procedure successfully completed.ใช้วิธีการสั่งซื้อ
ตอนนี้ same effect could be achieved using an order method. ให้เราสร้างวัตถุสี่เหลี่ยมผืนผ้าขึ้นมาใหม่โดยใช้วิธีการสั่งซื้อ -
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member procedure display,
order member function measure(r rectangle) return number
);
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type created.การสร้างตัวพิมพ์ -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type body created.การใช้วัตถุสี่เหลี่ยมผืนผ้าและฟังก์ชันสมาชิก -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Length: 23
Width: 44
Length: 15
Width: 17
Length: 23
Width: 44
PL/SQL procedure successfully completed.การสืบทอดสำหรับ PL / SQL Objects
PL / SQL อนุญาตให้สร้างวัตถุจากวัตถุพื้นฐานที่มีอยู่ ในการใช้การสืบทอดวัตถุพื้นฐานควรได้รับการประกาศเป็นNOT FINAL. ค่าเริ่มต้นคือFINAL.
โปรแกรมต่อไปนี้แสดงให้เห็นถึงการสืบทอดใน PL / SQL Objects ให้เราสร้างวัตถุอื่นชื่อTableTopสิ่งนี้สืบทอดมาจากวัตถุสี่เหลี่ยมผืนผ้า สำหรับสิ่งนี้เราต้องสร้างวัตถุสี่เหลี่ยมผืนผ้าฐาน-
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
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type created.การสร้างตัวแบบฐาน -
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type body created.การสร้างวัตถุเด็กโต๊ะ -
CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
material varchar2(20),
OVERRIDING member procedure display
)
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type created.การสร้างเนื้อความชนิดสำหรับโต๊ะวัตถุลูก
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;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type body created.การใช้วัตถุบนโต๊ะและฟังก์ชันสมาชิก -
DECLARE
t1 tabletop;
t2 tabletop;
BEGIN
t1:= tabletop(20, 10, 'Wood');
t2 := tabletop(50, 30, 'Steel');
t1.display;
t2.display;
END;
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Length: 20
Width: 10
Material: Wood
Length: 50
Width: 30
Material: Steel
PL/SQL procedure successfully completed.วัตถุนามธรรมใน PL / SQL
NOT INSTANTIABLEอนุประโยคอนุญาตให้คุณประกาศวัตถุนามธรรม คุณไม่สามารถใช้วัตถุนามธรรมได้อย่างที่เป็นอยู่ คุณจะต้องสร้างประเภทย่อยหรือประเภทย่อยของวัตถุดังกล่าวเพื่อใช้ฟังก์ชันของมัน
ตัวอย่างเช่น,
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
NOT INSTANTIABLE NOT FINAL
/เมื่อรหัสด้านบนถูกเรียกใช้ที่พรอมต์ SQL จะให้ผลลัพธ์ดังต่อไปนี้ -
Type created.