Oracle ขั้นตอนและฟังก์ชันที่จัดเก็บ PL/SQL พร้อมตัวอย่าง

PL/SQL Subprograms คืออะไร?

ในบทช่วยสอนนี้ คุณจะได้เรียนรู้คำอธิบายโดยละเอียดเกี่ยวกับวิธีการสร้างและเรียกใช้บล็อก โปรซีเดอร์ และฟังก์ชันต่างๆ ที่ระบุไว้

ขั้นตอนและฟังก์ชันเป็นโปรแกรมย่อยที่สามารถสร้างและบันทึกไว้ในฐานข้อมูลในรูปแบบของอ็อบเจ็กต์ฐานข้อมูลได้ นอกจากนี้ยังสามารถเรียกใช้หรืออ้างอิงถึงภายในบล็อกอื่นๆ ได้อีกด้วย

นอกจากนี้เรายังจะกล่าวถึงความแตกต่างที่สำคัญระหว่างสองโปรแกรมย่อยนี้และอภิปรายกันด้วย Oracle ฟังก์ชั่นในตัว

คำศัพท์เฉพาะในโปรแกรมย่อย PL/SQL

ก่อนที่จะเรียนรู้เกี่ยวกับซับโปรแกรม PL/SQL เราจะมาพูดคุยเกี่ยวกับคำศัพท์ต่างๆ ที่เกี่ยวข้องกับซับโปรแกรมเหล่านี้กันก่อน

พารามิเตอร์

พารามิเตอร์คือตัวแปรหรือตัวแทนของค่าใดๆ ที่ถูกต้อง ประเภทข้อมูล PL/SQL ซึ่งเป็นช่องทางที่โปรแกรมย่อย PL/SQL แลกเปลี่ยนค่ากับโค้ดหลัก พารามิเตอร์นี้อนุญาตให้ป้อนข้อมูลเข้าสู่โปรแกรมย่อยและส่งออกข้อมูลได้tracคุณค่าที่ได้รับจากพวกเขา

• ควรกำหนดพารามิเตอร์เหล่านี้พร้อมกับโปรแกรมย่อยในขณะที่สร้าง
• ส่วนประกอบเหล่านี้ถูกรวมอยู่ในคำสั่งเรียกใช้เพื่อโต้ตอบกับโปรแกรมย่อย
• ชนิดข้อมูลของพารามิเตอร์ในโปรแกรมย่อยและคำสั่งเรียกใช้จะต้องเหมือนกัน
• ไม่ควรระบุขนาดของชนิดข้อมูลในขณะที่ประกาศพารามิเตอร์ เนื่องจากขนาดนั้นเปลี่ยนแปลงได้

สามารถจำแนกพารามิเตอร์ตามวัตถุประสงค์ได้ดังนี้:

1. ในพารามิเตอร์
2. ออกพารามิเตอร์
3. เข้าออกพารามิเตอร์

ในพารามิเตอร์

• ใช้สำหรับป้อนข้อมูลเข้าสู่โปรแกรมย่อย
• ตัวแปรนี้เป็นตัวแปรแบบอ่านอย่างเดียวภายในโปรแกรมย่อย ไม่สามารถเปลี่ยนแปลงค่าของตัวแปรนี้ได้ภายในโปรแกรมย่อย
• ในคำสั่งเรียกใช้ อาจเป็นตัวแปร ค่าคงที่ หรือนิพจน์ เช่น '5*8' หรือ 'a/b'
• โดยค่าเริ่มต้น พารามิเตอร์จะเป็นประเภท IN

ออกพารามิเตอร์

• ใช้สำหรับรับผลลัพธ์จากโปรแกรมย่อย
• เป็นตัวแปรแบบอ่าน-เขียนภายในโปรแกรมย่อย สามารถเปลี่ยนแปลงค่าของตัวแปรนี้ได้ภายในโปรแกรมย่อยเหล่านั้น
• ในคำสั่งเรียกใช้ ควรใช้ตัวแปรเสมอเพื่อเก็บค่าจากโปรแกรมย่อย

เข้าออกพารามิเตอร์

• ใช้สำหรับการป้อนข้อมูลและรับข้อมูลจากโปรแกรมย่อย
• เป็นตัวแปรแบบอ่าน-เขียนภายในโปรแกรมย่อย สามารถเปลี่ยนแปลงค่าของตัวแปรนี้ได้ภายในโปรแกรมย่อยเหล่านั้น
• ในคำสั่งเรียกใช้ ควรใช้ตัวแปรเสมอเพื่อเก็บค่าจากโปรแกรมย่อย

ควรระบุประเภทของพารามิเตอร์ในขณะที่สร้างโปรแกรมย่อย

กลับ

คำสั่ง RETURN เป็นคำสำคัญที่สั่งให้คอมไพเลอร์เปลี่ยนการควบคุมจากโปรแกรมย่อยไปยังคำสั่งที่เรียกใช้ ในโปรแกรมย่อย RETURN หมายความว่าการควบคุมต้องออกจากโปรแกรมย่อยนั้น เมื่อตัวควบคุมพบ RETURN แล้ว โค้ดหลังจากนั้นจะถูกข้ามไป

โดยปกติแล้ว บล็อกหลักหรือบล็อกแม่จะเป็นผู้เรียกโปรแกรมย่อย และการควบคุมจะเปลี่ยนจากบล็อกแม่ไปยังโปรแกรมย่อยที่ถูกเรียก คำสั่ง RETURN ในโปรแกรมย่อยจะส่งการควบคุมกลับไปยังบล็อกแม่ ในกรณีของฟังก์ชัน คำสั่ง RETURN จะส่งค่ากลับมาด้วย ซึ่งชนิดข้อมูลของค่าจะถูกระบุไว้ในขณะที่ประกาศฟังก์ชัน

Procedure ใน PL/SQL คืออะไร?

A การรักษาอื่นๆ ใน PL/SQL นั้น โปรซีเจอร์คือหน่วยย่อยของโปรแกรม ซึ่งประกอบด้วยกลุ่มของคำสั่ง PL/SQL ที่สามารถเรียกใช้ได้โดยใช้ชื่อ แต่ละโปรซีเจอร์จะมีชื่อเฉพาะของตัวเองและถูกจัดเก็บไว้ใน Oracle ฐานข้อมูลในฐานะวัตถุฐานข้อมูล

หมายเหตุ โปรแกรมย่อยก็คือขั้นตอนการทำงานอย่างหนึ่ง ซึ่งต้องสร้างขึ้นด้วยตนเองตามความต้องการ เมื่อสร้างเสร็จแล้วก็จะถูกจัดเก็บเป็นอ็อบเจ็กต์ในฐานข้อมูล

คุณลักษณะของหน่วยย่อยโปรแกรมใน PL/SQL มีดังนี้:

• ขั้นตอนการทำงานเป็นส่วนประกอบอิสระที่สามารถจัดเก็บได้ ฐานข้อมูล.
• สามารถเรียกใช้ไฟล์เหล่านั้นโดยใช้ชื่อไฟล์เพื่อดำเนินการคำสั่ง PL/SQL ได้
• โดยส่วนใหญ่แล้วจะใช้เพื่อดำเนินการตามกระบวนการ
• พวกมันสามารถมีบล็อกซ้อนกัน หรือซ้อนอยู่ภายในบล็อกหรือแพ็กเกจอื่นๆ ได้
• ประกอบด้วยส่วนประกาศ (ไม่บังคับ), ส่วนการดำเนินการ และส่วนการจัดการข้อผิดพลาด (ไม่บังคับ)
• สามารถส่งค่าเข้าหรือดึงค่าออกจากขั้นตอนการทำงานผ่านทางพารามิเตอร์ได้
• พารามิเตอร์เหล่านี้ควรรวมอยู่ในคำสั่งการโทร
• โปรแกรมย่อยสามารถมีคำสั่ง RETURN เพื่อส่งการควบคุมกลับไปยังบล็อกที่เรียกใช้ได้ แต่ไม่สามารถส่งค่าใดๆ กลับมาผ่านคำสั่ง RETURN ได้
• ไม่สามารถเรียกใช้โปรซีเจอร์โดยตรงจากคำสั่ง SELECT ได้ แต่ต้องเรียกใช้จากบล็อกอื่นหรือผ่านคีย์เวิร์ด EXEC

วากยสัมพันธ์

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• คำสั่ง CREATE PROCEDURE สั่งให้คอมไพเลอร์สร้างโปรซีเดอร์ใหม่ ส่วนคำหลัก 'OR REPLACE' สั่งให้คอมไพเลอร์แทนที่โปรซีเดอร์ที่มีอยู่แล้ว (ถ้ามี) ด้วยโปรซีเดอร์ใหม่นี้
• ชื่อของขั้นตอนควรไม่ซ้ำกัน
• คำว่า 'IS' ใช้เมื่อ stored procedure ถูกซ้อนอยู่ภายในบล็อกอื่น หาก procedure ทำงานแบบเดี่ยวๆ จะใช้ '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');

Code คำอธิบาย:

• Code สาย 1: สร้างขั้นตอนการทำงานชื่อ 'welcome_msg' และพารามิเตอร์หนึ่งตัวชื่อ 'p_name' ชนิด 'IN'
• Code สาย 4: พิมพ์ข้อความต้อนรับโดยการต่อชื่อที่ป้อนเข้ามา
• ขั้นตอนการดำเนินการเสร็จสมบูรณ์แล้ว
• Code สาย 7: เรียกใช้โปรซีเดอร์โดยใช้ EXEC พร้อมพารามิเตอร์ 'Guru99' ขั้นตอนการทำงานจะดำเนินการและพิมพ์ "ยินดีต้อนรับ" Guru99. "

ฟังก์ชั่นคืออะไร?

ฟังก์ชันคือโปรแกรมย่อย PL/SQL ที่ทำงานได้อย่างอิสระ เช่นเดียวกับโปรซีเจอร์ ฟังก์ชันจะมีชื่อเฉพาะและถูกจัดเก็บเป็นอ็อบเจ็กต์ในฐานข้อมูล PL/SQL ลักษณะเฉพาะของฟังก์ชันมีดังนี้:

• ฟังก์ชันคือบล็อกอิสระที่ใช้สำหรับการคำนวณเป็นหลัก
• ฟังก์ชันใช้คำหลัก RETURN เพื่อส่งค่ากลับ โดยชนิดข้อมูลของค่าจะถูกกำหนดไว้ในขณะที่สร้างฟังก์ชันนั้น
• ฟังก์ชันควรส่งค่ากลับหรือยกเว้นข้อผิดพลาด โดยการส่งค่ากลับเป็นสิ่งจำเป็นในฟังก์ชัน
• ฟังก์ชันที่ไม่มีคำสั่ง DML สามารถเรียกใช้ได้โดยตรงในคำสั่ง SELECT ในขณะที่ฟังก์ชันที่มีคำสั่ง DML สามารถเรียกใช้ได้จากบล็อก PL/SQL อื่นๆ เท่านั้น
• มันสามารถมีบล็อกซ้อนกัน หรือซ้อนอยู่ภายในบล็อกหรือแพ็กเกจอื่นๆ ได้
• ประกอบด้วยส่วนการประกาศ (ไม่บังคับ), ส่วนการดำเนินการ และส่วนการจัดการข้อผิดพลาด (ไม่บังคับ)
• สามารถส่งค่าเข้าหรือดึงค่าออกจากฟังก์ชันได้ผ่านทางพารามิเตอร์
• พารามิเตอร์เหล่านี้ควรรวมอยู่ในคำสั่งการโทร
• นอกจากใช้คำสั่ง RETURN แล้ว ฟังก์ชันยังสามารถส่งค่ากลับผ่านพารามิเตอร์ OUT ได้อีกด้วย
• เนื่องจากฟังก์ชันนี้จะส่งคืนค่าเสมอ ดังนั้นคำสั่งเรียกใช้จึงใช้ตัวดำเนินการกำหนดค่าเพื่อใส่ค่าลงในตัวแปรเสมอ

วากยสัมพันธ์

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 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: การสร้างฟังก์ชันและการเรียกใช้โดยใช้บล็อกที่ไม่ระบุชื่อ ในโปรแกรมนี้ เราสร้างฟังก์ชันที่รับชื่อเป็นอินพุตและส่งคืนข้อความต้อนรับ โดยใช้บล็อกที่ไม่ระบุชื่อและคำสั่ง SELECT เพื่อเรียกใช้ฟังก์ชันนั้น

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
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;

Code คำอธิบาย:

• Code สาย 1: สร้างฟังก์ชันชื่อ 'welcome_msg_func' โดยมีพารามิเตอร์ 'p_name' หนึ่งตัว ประเภท 'IN'
• Code สาย 2: กำหนดประเภทค่าส่งคืนเป็น VARCHAR2
• Code สาย 5: ส่งคืนค่าที่ต่อกันระหว่าง 'Welcome' และค่าพารามิเตอร์
• Code สาย 8: บล็อกที่ไม่ระบุชื่อเพื่อเรียกใช้ฟังก์ชันข้างต้น
• Code สาย 9: ประกาศตัวแปรโดยใช้ชนิดข้อมูลเดียวกับชนิดข้อมูลที่ส่งคืนจากฟังก์ชัน
• Code สาย 11: เรียกใช้ฟังก์ชันและนำค่าที่ส่งกลับมาไปเก็บไว้ในตัวแปร 'lv_msg'
• Code สาย 12: แสดงค่าตัวแปร ผลลัพธ์คือ “ยินดีต้อนรับ” Guru99. "
• Code สาย 14: เรียกใช้ฟังก์ชันเดียวกันผ่านคำสั่ง SELECT โดยค่าที่ส่งกลับจะถูกส่งออกไปยังเอาต์พุตมาตรฐาน

ความคล้ายคลึงกันระหว่างขั้นตอนและฟังก์ชัน

• ทั้งสองสามารถเรียกได้จากบล็อก PL/SQL อื่น
• หากข้อผิดพลาดที่เกิดขึ้นในโปรแกรมย่อยไม่ได้รับการจัดการอย่างเหมาะสม การจัดการข้อยกเว้น ส่วนนั้นจะส่งต่อไปยังบล็อกที่เรียกใช้
• ทั้งสองสามารถมีพารามิเตอร์ได้มากเท่าที่ต้องการ
• ทั้งสองจะถือเป็นวัตถุฐานข้อมูลใน PL/SQL

ขั้นตอนการทำงานเทียบกับหน้าที่การใช้งาน: ความแตกต่างที่สำคัญ

ฟังก์ชันในตัวใน PL/SQL

PL / SQL มีฟังก์ชันในตัวหลากหลายสำหรับทำงานกับข้อมูลประเภทสตริงและวันที่ ในที่นี้เราจะมาดูฟังก์ชันที่ใช้กันทั่วไปและวิธีการใช้งาน

ฟังก์ชันการแปลง

ฟังก์ชันในตัวเหล่านี้จะแปลงชนิดข้อมูลหนึ่งไปเป็นอีกชนิดข้อมูลหนึ่ง

ฟังก์ชันสตริง

ฟังก์ชันเหล่านี้ใช้กับชนิดข้อมูลอักขระ

ฟังก์ชั่นวันที่

ฟังก์ชันเหล่านี้ใช้สำหรับจัดการวันที่

คำถามที่พบบ่อย