Oracle ขั้นตอนและฟังก์ชันที่จัดเก็บ PL/SQL พร้อมตัวอย่าง
ขั้นตอนและฟังก์ชันเป็นโปรแกรมย่อยที่สามารถสร้างและบันทึกในฐานข้อมูลเป็นวัตถุฐานข้อมูลได้ พวกเขาสามารถเรียกหรืออ้างอิงภายในบล็อคอื่นได้เช่นกัน
นอกจากนี้ เราจะกล่าวถึงความแตกต่างที่สำคัญระหว่างโปรแกรมย่อยทั้งสองนี้ นอกจากนี้เราจะหารือเกี่ยวกับ Oracle ฟังก์ชั่นในตัว
คำศัพท์เฉพาะในโปรแกรมย่อย PL/SQL
ก่อนที่เราจะเรียนรู้เกี่ยวกับโปรแกรมย่อย PL/SQL เราจะหารือเกี่ยวกับคำศัพท์เฉพาะทางต่างๆ ที่เป็นส่วนหนึ่งของโปรแกรมย่อยเหล่านี้ ด้านล่างนี้เป็นคำศัพท์ที่เราจะพูดถึง
พารามิเตอร์
พารามิเตอร์เป็นตัวแปรหรือตัวยึดตำแหน่งที่ถูกต้อง ประเภทข้อมูล PL/SQL โดยที่โปรแกรมย่อย PL/SQL แลกเปลี่ยนค่ากับโค้ดหลัก พารามิเตอร์นี้อนุญาตให้ป้อนอินพุตให้กับโปรแกรมย่อยและแยกออกจากโปรแกรมย่อยเหล่านี้
- ควรกำหนดพารามิเตอร์เหล่านี้พร้อมกับโปรแกรมย่อยในขณะที่สร้าง
- พารามิเตอร์เหล่านี้จะรวมอยู่ในคำสั่งเรียกของโปรแกรมย่อยเหล่านี้เพื่อโต้ตอบค่ากับโปรแกรมย่อย
- ประเภทข้อมูลของพารามิเตอร์ในโปรแกรมย่อยและคำสั่งการโทรควรเหมือนกัน
- ขนาดของประเภทข้อมูลไม่ควรกล่าวถึงในขณะที่ประกาศพารามิเตอร์ เนื่องจากขนาดจะเป็นแบบไดนามิกสำหรับประเภทนี้
ขึ้นอยู่กับวัตถุประสงค์ พารามิเตอร์ถูกจัดประเภทเป็น
- ในพารามิเตอร์
- ออกพารามิเตอร์
- เข้าออกพารามิเตอร์
ในพารามิเตอร์
- พารามิเตอร์นี้ใช้สำหรับป้อนอินพุตให้กับโปรแกรมย่อย
- เป็นตัวแปรอ่านอย่างเดียวภายในโปรแกรมย่อย ค่าเหล่านี้ไม่สามารถเปลี่ยนแปลงภายในโปรแกรมย่อยได้
- ในคำสั่งเรียก พารามิเตอร์เหล่านี้อาจเป็นตัวแปร หรือค่าตามตัวอักษร หรือนิพจน์ก็ได้ ตัวอย่างเช่น อาจเป็นนิพจน์ทางคณิตศาสตร์ เช่น '5*8' หรือ 'a/b' โดยที่ 'a' และ 'b' เป็นตัวแปร .
- ตามค่าเริ่มต้น พารามิเตอร์จะเป็นประเภท IN
ออกพารามิเตอร์
- พารามิเตอร์นี้ใช้สำหรับรับเอาต์พุตจากโปรแกรมย่อย
- เป็นตัวแปรอ่าน-เขียนภายในโปรแกรมย่อย ค่าสามารถเปลี่ยนแปลงได้ภายในโปรแกรมย่อย
- ในคำสั่งเรียก พารามิเตอร์เหล่านี้ควรเป็นตัวแปรเพื่อเก็บค่าจากโปรแกรมย่อยปัจจุบันเสมอ
เข้าออกพารามิเตอร์
- พารามิเตอร์นี้ใช้สำหรับทั้งการให้อินพุตและการรับเอาต์พุตจากโปรแกรมย่อย
- เป็นตัวแปรอ่าน-เขียนภายในโปรแกรมย่อย ค่าสามารถเปลี่ยนแปลงได้ภายในโปรแกรมย่อย
- ในคำสั่งเรียก พารามิเตอร์เหล่านี้ควรเป็นตัวแปรเพื่อเก็บค่าจากโปรแกรมย่อยเสมอ
ควรกล่าวถึงประเภทพารามิเตอร์เหล่านี้ในขณะที่สร้างโปรแกรมย่อย
กลับ
RETURN คือคีย์เวิร์ดที่สั่งให้คอมไพเลอร์เปลี่ยนการควบคุมจากโปรแกรมย่อยไปเป็นคำสั่งเรียก ในโปรแกรมย่อย RETURN หมายความง่ายๆ ว่าส่วนควบคุมจำเป็นต้องออกจากโปรแกรมย่อย เมื่อคอนโทรลเลอร์พบคีย์เวิร์ด RETURN ในโปรแกรมย่อยแล้ว โค้ดหลังจากนี้จะถูกข้ามไป
โดยปกติ บล็อกหลักหรือบล็อกหลักจะเรียกใช้โปรแกรมย่อย จากนั้นส่วนควบคุมจะเลื่อนจากบล็อกหลักเหล่านั้นไปยังโปรแกรมย่อยที่ถูกเรียกใช้ RETURN ในโปรแกรมย่อยจะส่งส่วนควบคุมกลับไปยังบล็อกหลัก ในกรณีของฟังก์ชัน คำสั่ง RETURN จะส่งคืนค่าด้วย ประเภทข้อมูลของค่านี้จะถูกระบุไว้เสมอเมื่อประกาศฟังก์ชัน ประเภทข้อมูลสามารถเป็นประเภทข้อมูล PL/SQL ที่ถูกต้องได้
ขั้นตอนใน PL/SQL คืออะไร?
A การรักษาอื่นๆ ใน PL/SQL เป็นหน่วยโปรแกรมย่อยที่ประกอบด้วยกลุ่มของคำสั่ง PL/SQL ที่สามารถเรียกตามชื่อได้ แต่ละขั้นตอนใน PL/SQL มีชื่อเฉพาะของตัวเองซึ่งสามารถอ้างอิงและเรียกได้ หน่วยโปรแกรมย่อยนี้อยู่ใน Oracle ฐานข้อมูลจะถูกเก็บเป็นวัตถุฐานข้อมูล
หมายเหตุ โปรแกรมย่อยนั้นเป็นเพียงขั้นตอน และจำเป็นต้องสร้างขึ้นด้วยตนเองตามความต้องการ เมื่อสร้างขึ้นแล้วจะถูกจัดเก็บเป็นวัตถุฐานข้อมูล
ด้านล่างนี้เป็นคุณลักษณะของหน่วยโปรแกรมย่อย Procedure ใน PL/SQL:
- ขั้นตอนคือบล็อกแบบสแตนด์อโลนของโปรแกรมที่สามารถจัดเก็บไว้ใน ฐานข้อมูล.
- การเรียกขั้นตอน PLSQL เหล่านี้สามารถทำได้โดยการอ้างอิงถึงชื่อ เพื่อดำเนินการคำสั่ง PL/SQL
- ส่วนใหญ่จะใช้เพื่อดำเนินการกระบวนการใน PL/SQL
- อาจมีบล็อกที่ซ้อนกัน หรือสามารถกำหนดและซ้อนกันภายในบล็อกหรือแพ็กเกจอื่นๆ ได้
- ประกอบด้วยส่วนการประกาศ (ไม่บังคับ), ส่วนดำเนินการ, ส่วนการจัดการข้อยกเว้น (ไม่บังคับ)
- สามารถส่งผ่านค่าต่างๆ เข้าไปได้ Oracle ขั้นตอนหรือดึงข้อมูลจากขั้นตอนผ่านพารามิเตอร์
- พารามิเตอร์เหล่านี้ควรรวมอยู่ในคำสั่งการโทร
- ขั้นตอนใน SQL สามารถมีคำสั่ง RETURN เพื่อส่งคืนการควบคุมไปยังบล็อกการเรียก แต่ไม่สามารถส่งคืนค่าใด ๆ ผ่านคำสั่ง RETURN ได้
- ไม่สามารถเรียกขั้นตอนโดยตรงจากคำสั่ง SELECT สามารถเรียกได้จากบล็อกอื่นหรือผ่านคีย์เวิร์ด EXEC
วากยสัมพันธ์
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameterl IN/OUT <datatype> .. . ) [ IS | AS ] <declaration_part> BEGIN <execution part> EXCEPTION <exception handling part> END;
- CREATE PROCEDURE สั่งให้คอมไพเลอร์สร้างขั้นตอนใหม่ขึ้นมา Oracle- คำหลัก 'OR REPLACE' สั่งให้คอมไพล์แทนที่ขั้นตอนที่มีอยู่ (ถ้ามี) ด้วยขั้นตอนปัจจุบัน
- ชื่อขั้นตอนไม่ควรซ้ำกัน
- คำหลัก 'IS' จะถูกใช้เมื่อขั้นตอนการจัดเก็บใน Oracle ซ้อนอยู่ในบล็อกอื่นๆ หากขั้นตอนเป็นแบบสแตนด์อโลน ระบบจะใช้ 'AS' นอกเหนือจากมาตรฐานการเข้ารหัสนี้ ทั้งสองมีความหมายเหมือนกัน
ตัวอย่างที่ 1: การสร้างขั้นตอนและเรียกใช้โดยใช้ EXEC
ในตัวอย่างนี้ เราจะสร้างไฟล์ Oracle ขั้นตอนที่ใช้ชื่อเป็นอินพุตและพิมพ์ข้อความต้อนรับเป็นเอาต์พุต เราจะใช้คำสั่ง EXEC เพื่อเรียกขั้นตอน
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) IS BEGIN dbms_output.put_line (‘Welcome '|| p_name); END; / EXEC welcome_msg (‘Guru99’);
คำอธิบายรหัส:
- รหัสบรรทัดที่ 1: การสร้างขั้นตอนด้วยชื่อ 'welcome_msg' และด้วยพารามิเตอร์ 'p_name' ประเภท 'IN' หนึ่งพารามิเตอร์
- รหัสบรรทัดที่ 4: พิมพ์ข้อความต้อนรับโดยเชื่อมชื่ออินพุตเข้าด้วยกัน
- รวบรวมขั้นตอนเรียบร้อยแล้ว
- รหัสบรรทัดที่ 7: การเรียกขั้นตอนโดยใช้คำสั่ง EXEC พร้อมพารามิเตอร์ 'Guru99' ขั้นตอนถูกดำเนินการ และข้อความจะถูกพิมพ์ออกมาเป็น “Welcome Guru99”
ฟังก์ชั่นคืออะไร?
Functions เป็นโปรแกรมย่อย PL/SQL แบบสแตนด์อโลน เช่นเดียวกับโพรซีเดอร์ PL/SQL ฟังก์ชันมีชื่อเฉพาะซึ่งสามารถอ้างอิงได้ สิ่งเหล่านี้ถูกจัดเก็บเป็นวัตถุฐานข้อมูล PL/SQL ด้านล่างนี้คือคุณลักษณะบางประการของฟังก์ชัน
- ฟังก์ชันเป็นบล็อกแบบสแตนด์อโลนที่ใช้เพื่อการคำนวณเป็นหลัก
- ฟังก์ชันใช้คีย์เวิร์ด RETURN เพื่อส่งคืนค่า และประเภทข้อมูลของสิ่งนี้ถูกกำหนดไว้ในขณะที่สร้าง
- ฟังก์ชั่นควรส่งคืนค่าหรือเพิ่มข้อยกเว้น กล่าวคือ return เป็นสิ่งจำเป็นในฟังก์ชัน
- ฟังก์ชันที่ไม่มีคำสั่ง DML สามารถเรียกได้โดยตรงในแบบสอบถาม SELECT ในขณะที่ฟังก์ชันที่มีการดำเนินการ DML จะสามารถเรียกได้จากบล็อก PL/SQL อื่นๆ เท่านั้น
- อาจมีบล็อกที่ซ้อนกัน หรือสามารถกำหนดและซ้อนกันภายในบล็อกหรือแพ็กเกจอื่นๆ ได้
- ประกอบด้วยส่วนการประกาศ (ไม่บังคับ), ส่วนดำเนินการ, ส่วนการจัดการข้อยกเว้น (ไม่บังคับ)
- ค่าสามารถส่งผ่านไปยังฟังก์ชันหรือดึงข้อมูลจากขั้นตอนผ่านพารามิเตอร์ได้
- พารามิเตอร์เหล่านี้ควรรวมอยู่ในคำสั่งการโทร
- ฟังก์ชัน PLSQL ยังสามารถส่งคืนค่าผ่านพารามิเตอร์ OUT นอกเหนือจากการใช้ RETURN
- เนื่องจากจะส่งคืนค่าเสมอ ในการเรียกคำสั่งจึงมักจะมาพร้อมกับตัวดำเนินการกำหนดค่าเพื่อเติมค่าตัวแปร
วากยสัมพันธ์
CREATE OR REPLACE FUNCTION <procedure_name> ( <parameterl IN/OUT <datatype> ) RETURN <datatype> [ IS | AS ] <declaration_part> BEGIN <execution part> EXCEPTION <exception handling part> END;
- CREATE FUNCTION สั่งให้คอมไพเลอร์สร้างฟังก์ชันใหม่ คำหลัก 'OR REPLACE' สั่งให้คอมไพเลอร์แทนที่ฟังก์ชันที่มีอยู่ (ถ้ามี) ด้วยฟังก์ชันปัจจุบัน
- ชื่อฟังก์ชันไม่ควรซ้ำกัน
- ควรกล่าวถึงประเภทข้อมูล RETURN
- คำหลัก 'IS' จะถูกใช้ เมื่อขั้นตอนถูกซ้อนอยู่ในบล็อกอื่นๆ หากขั้นตอนเป็นแบบสแตนด์อโลน ระบบจะใช้ 'AS' นอกเหนือจากมาตรฐานการเข้ารหัสนี้ ทั้งสองมีความหมายเหมือนกัน
ตัวอย่างที่ 1: การสร้างฟังก์ชันและเรียกใช้โดยใช้ Anonymous Block
ในโปรแกรมนี้ เราจะสร้างฟังก์ชันที่ใช้ชื่อเป็นอินพุต และส่งกลับข้อความต้อนรับเป็นเอาต์พุต เราจะใช้บล็อกที่ไม่ระบุชื่อและเลือกคำสั่งเพื่อเรียกใช้ฟังก์ชัน
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2 IS BEGIN RETURN (‘Welcome ‘|| p_name); END; / DECLARE lv_msg VARCHAR2(250); BEGIN lv_msg := welcome_msg_func (‘Guru99’); dbms_output.put_line(lv_msg); END; SELECT welcome_msg_func(‘Guru99:) FROM DUAL;
คำอธิบายรหัส:
- รหัสบรรทัดที่ 1: การสร้าง Oracle ฟังก์ชันที่มีชื่อ 'welcome_msg_func' และมีพารามิเตอร์ 'p_name' ประเภท 'IN' หนึ่งรายการ
- รหัสบรรทัดที่ 2: ประกาศประเภทการส่งคืนเป็น VARCHAR2
- รหัสบรรทัดที่ 5: ส่งคืนค่าที่ต่อกัน 'ยินดีต้อนรับ' และค่าพารามิเตอร์
- รหัสบรรทัดที่ 8: บล็อกที่ไม่ระบุชื่อเพื่อเรียกใช้ฟังก์ชันข้างต้น
- รหัสบรรทัดที่ 9: การประกาศตัวแปรด้วยประเภทข้อมูลเหมือนกับประเภทข้อมูลที่ส่งคืนของฟังก์ชัน
- รหัสบรรทัดที่ 11: การเรียกใช้ฟังก์ชันและเติมค่าที่ส่งคืนให้กับตัวแปร 'lv_msg'
- รหัสบรรทัดที่ 12: การพิมพ์ค่าตัวแปร ผลลัพธ์ที่คุณจะได้คือ “ยินดีต้อนรับ Guru99”
- รหัสบรรทัดที่ 14: การเรียกใช้ฟังก์ชันเดียวกันผ่านคำสั่ง SELECT ค่าที่ส่งคืนจะถูกส่งไปยังเอาต์พุตมาตรฐานโดยตรง
ความคล้ายคลึงกันระหว่างขั้นตอนและฟังก์ชัน
- ทั้งสองสามารถเรียกได้จากบล็อก PL/SQL อื่น
- หากข้อยกเว้นที่เกิดขึ้นในโปรแกรมย่อยไม่ได้รับการจัดการในโปรแกรมย่อย การจัดการข้อยกเว้น จากนั้นจะแพร่กระจายไปยังบล็อกการโทร
- ทั้งสองสามารถมีพารามิเตอร์ได้มากเท่าที่ต้องการ
- ทั้งสองจะถือเป็นวัตถุฐานข้อมูลใน PL/SQL
ขั้นตอนเทียบกับ ฟังก์ชัน: ความแตกต่างที่สำคัญ
การรักษาอื่นๆ | ฟังก์ชัน |
---|---|
ใช้เพื่อดำเนินการกระบวนการบางอย่างเป็นหลัก | ใช้เพื่อการคำนวณเป็นหลัก |
ไม่สามารถโทรในคำสั่ง SELECT | ฟังก์ชั่นที่ไม่มีคำสั่ง DML สามารถเรียกใช้ในคำสั่ง SELECT ได้ |
ใช้พารามิเตอร์ OUT เพื่อส่งกลับค่า | ใช้ RETURN เพื่อส่งกลับค่า |
ไม่จำเป็นต้องส่งคืนค่า | จำเป็นต้องส่งกลับค่า |
RETURN จะออกจากการควบคุมจากโปรแกรมย่อย | RETURN จะออกจากการควบคุมจากโปรแกรมย่อยและส่งกลับค่าด้วย |
ประเภทข้อมูลการส่งคืนจะไม่ถูกระบุในขณะที่สร้าง | ประเภทข้อมูลการส่งคืนมีผลบังคับใช้ในขณะที่สร้าง |
ฟังก์ชันในตัวใน PL/SQL
PL / SQL มีฟังก์ชันในตัวต่างๆ เพื่อทำงานกับสตริงและประเภทข้อมูลวันที่ เราจะมาดูฟังก์ชันที่ใช้กันทั่วไปและการใช้งานกัน
ฟังก์ชันการแปลง
ฟังก์ชันในตัวเหล่านี้ใช้เพื่อแปลงประเภทข้อมูลหนึ่งไปเป็นประเภทข้อมูลอื่น
ชื่อฟังก์ชั่น | การใช้ | ตัวอย่าง |
---|---|---|
TO_CHAR | แปลงประเภทข้อมูลอื่นให้เป็นประเภทข้อมูลอักขระ | TO_CHAR(123); |
TO_DATE ( สตริง รูปแบบ ) | แปลงสตริงที่กำหนดให้เป็นวันที่ สตริงควรตรงกับรูปแบบ |
TO_DATE('2015-ม.ค.-15', 'ปปปป-จันทร์-วว'); เอาท์พุต: 1 / 15 / 2015 |
TO_NUMBER (ข้อความ รูปแบบ) |
แปลงข้อความเป็นประเภทตัวเลขในรูปแบบที่กำหนด ข้อมูล '9' หมายถึงจำนวนหลัก |
เลือก TO_NUMBER('1234′,'9999') จากคู่;
เอาท์พุต: 1234 เลือก TO_NUMBER('1,234.45′,'9,999.99') จากคู่; เอาท์พุต: 1234 |
ฟังก์ชันสตริง
เหล่านี้เป็นฟังก์ชันที่ใช้ในประเภทข้อมูลอักขระ
ชื่อฟังก์ชั่น | การใช้ | ตัวอย่าง |
---|---|---|
INSTR(ข้อความ, สตริง, เริ่มต้น, เหตุการณ์) | ให้ตำแหน่งของข้อความเฉพาะในสตริงที่กำหนด
|
เลือก INSTR('AEROPLane','E',2,1) จากคู่
เอาท์พุต: 2 เลือก INSTR('AEROPLane','E',2,2) จากคู่ เอาท์พุต: 9 (2nd การเกิดขึ้นของ E) |
SUBSTR ( ข้อความ, เริ่มต้น, ความยาว) | ให้ค่าสตริงย่อยของสตริงหลัก
|
เลือก substr('aeroplane',1,7) จาก dual
เอาท์พุต: เครื่องบิน |
บน (ข้อความ) | ส่งกลับตัวพิมพ์ใหญ่ของข้อความที่ให้มา | เลือก upper('guru99') จาก dual;
เอาท์พุต: กูรู99 |
ล่าง ( ข้อความ ) | ส่งกลับตัวพิมพ์เล็กของข้อความที่ให้มา | เลือกด้านล่าง ('AerOpLane') จากคู่;
เอาท์พุต: เครื่องบิน |
INITCAP (ข้อความ) | ส่งกลับข้อความที่กำหนดโดยมีตัวอักษรเริ่มต้นเป็นตัวพิมพ์ใหญ่ | เลือก ('guru99') จากคู่
เอาท์พุต: กูรู99 เลือก ('เรื่องราวของฉัน') จากคู่ เอาท์พุต: เรื่องราวของฉัน |
ความยาว (ข้อความ) | ส่งกลับความยาวของสตริงที่กำหนด | เลือก LENGTH ('guru99') จาก dual;
เอาท์พุต: 6 |
LPAD ( ข้อความ, ความยาว, pad_char) | แพดสตริงทางด้านซ้ายตามความยาวที่กำหนด (สตริงทั้งหมด) ด้วยอักขระที่กำหนด | เลือก LPAD('guru99', 10, '$') จาก dual;
เอาท์พุต: $$$$กูรู99 |
RPAD (ข้อความ, ความยาว, pad_char) | แพดสตริงทางด้านขวาตามความยาวที่กำหนด (สตริงทั้งหมด) ด้วยอักขระที่กำหนด | เลือก RPAD('guru99′,10,'-') จาก dual
เอาท์พุต: guru99—- |
LTRIM ( ข้อความ ) | ตัดช่องว่างสีขาวนำหน้าออกจากข้อความ | เลือก LTRIM(' Guru99') จาก dual;
เอาท์พุต: กูรู99 |
RTRIM ( ข้อความ ) | ตัดช่องว่างสีขาวต่อท้ายออกจากข้อความ | เลือก RTRIM('Guru99 ') จาก dual;
เอาท์พุต- กูรู99 |
ฟังก์ชั่นวันที่
เหล่านี้เป็นฟังก์ชันที่ใช้สำหรับจัดการกับวันที่
ชื่อฟังก์ชั่น | การใช้ | ตัวอย่าง |
---|---|---|
ADD_MONTHS (วันที่ จำนวนเดือน) | เพิ่มเดือนที่กำหนดถึงวันที่ | ADD_MONTH('2015-01-01',5);
เอาท์พุต: 05 / 01 / 2015 |
ซิสเดต | ส่งกลับวันที่และเวลาปัจจุบันของเซิร์ฟเวอร์ | เลือก SYSDATE จากคู่;
เอาท์พุต: 10/4/2015 2:11:43 น |
ทรันซี | การปัดเศษของวันที่แปรผันเป็นค่าที่เป็นไปได้ที่ต่ำกว่า | เลือก sysdate, TRUNC (sysdate) จาก dual;
เอาท์พุต: 10/4/2015 2:12:39 น. 10/4/2015 |
รอบที่ | ปัดเศษวันที่เป็นค่าขีดจำกัดที่ใกล้ที่สุดไม่ว่าจะสูงหรือต่ำ | เลือก sysdate, ROUND(sysdate) จาก dual
เอาท์พุต: 10/4/2015 2:14:34 น. 10/5/2015 |
MONTHS_BETWEEN | ส่งกลับจำนวนเดือนระหว่างวันที่สองวัน | เลือก MONTHS_BETWEEN (sysdate+60, sysdate) จาก dual
เอาท์พุต: 2 |
สรุป
ในบทนี้เราได้เรียนรู้สิ่งต่อไปนี้
- วิธีสร้าง Procedure และวิธีการเรียกต่างๆ
- วิธีสร้าง Function และวิธีการเรียกต่างๆ
- ความเหมือนและความแตกต่างระหว่างขั้นตอนและฟังก์ชัน
- พารามิเตอร์และ RETURN คำศัพท์ทั่วไปในโปรแกรมย่อย PL/SQL
- ฟังก์ชั่นในตัวทั่วไปใน Oracle PL / SQL