Database Tutorials PL/pgSQL দিয়ে Stored Procedures তৈরি গাইড ও নোট

PL/pgSQL (Procedural Language/PostgreSQL SQL) হল PostgreSQL ডেটাবেসে প্রোগ্রামিং ভাষা, যা SQL স্টেটমেন্টগুলির সাথে procedural লজিক একত্রিত করে। এটি PostgreSQL তে Stored Procedures এবং Functions তৈরি করার জন্য ব্যবহৃত হয়। Stored Procedures ডেটাবেসে কিছু নির্দিষ্ট কাজ বা লজিক সম্পাদন করতে ব্যবহৃত হয়, যা একবার তৈরি করার পর একাধিকবার চালানো যেতে পারে।

PL/pgSQL ব্যবহার করে Stored Procedures তৈরি করতে CREATE FUNCTION বা CREATE PROCEDURE কমান্ড ব্যবহার করা হয়। এখানে আমরা Stored Procedure তৈরি করার প্রক্রিয়া বিস্তারিতভাবে আলোচনা করব।

1. Stored Procedure এবং Function এর মধ্যে পার্থক্য

• Function: একটি function নির্দিষ্ট একটি কাজ সম্পাদন করে এবং একটি মান ফেরত দেয় (Return value)।
• Procedure: একটি procedure কোনো মান ফেরত দেয় না, তবে এটি একটি বা একাধিক কাজ সম্পাদন করতে ব্যবহৃত হয়।

2. Stored Procedure তৈরি করা

PostgreSQL 11 এ CREATE PROCEDURE কমান্ডে stored procedure তৈরি করা সম্ভব হয়েছে। এর মাধ্যমে আপনি একটি sequence বা process সম্পাদন করতে পারেন এবং এটি ডেটাবেসের মধ্যে একাধিক কাজ বা কার্যকলাপ সম্পাদন করতে সাহায্য করে। একটি Stored Procedure সাধারণত কিছু নির্দিষ্ট কাজ (যেমন ইনসার্ট, আপডেট, ডিলিট, কুয়েরি) করার জন্য ব্যবহৃত হয়।

Stored Procedure তৈরি করার সাধারণ সিনট্যাক্স:

CREATE PROCEDURE procedure_name (parameter1 type, parameter2 type)
LANGUAGE plpgsql
AS $BEGIN
    -- Procedure logic here
    -- Example: INSERT, UPDATE, SELECT, DELETE, etc.
END;$;

3. Stored Procedure এর উদাহরণ

ধরা যাক, আমাদের একটি টেবিল রয়েছে যার নাম employees, এবং আমরা একটি Stored Procedure তৈরি করতে চাই যা টেবিলের নতুন রেকর্ড যোগ করবে।

উদাহরণ ১: নতুন employee যোগ করার জন্য Stored Procedure

CREATE PROCEDURE add_employee(emp_name VARCHAR, emp_age INT)
LANGUAGE plpgsql
AS $BEGIN
    INSERT INTO employees (name, age)
    VALUES (emp_name, emp_age);
END;$;

এটি add_employee নামের একটি Stored Procedure তৈরি করবে, যা দুইটি প্যারামিটার নিবে (emp_name এবং emp_age) এবং সেগুলি employees টেবিলে ইনসার্ট করবে।

Stored Procedure কল করার পদ্ধতি:

CALL add_employee('John Doe', 30);

এটি employees টেবিলে John Doe নামক একজন নতুন employee এবং তার বয়স ৩০ ইনসার্ট করবে।

4. Stored Procedure তে নিয়ন্ত্রণ কাঠামো ব্যবহার (Control Structures)

PL/pgSQL তে কিছু সাধারণ নিয়ন্ত্রণ কাঠামো যেমন IF, LOOP, FOR, WHILE, EXIT ইত্যাদি ব্যবহার করা যায়। এগুলি stored procedure বা function এর মধ্যে লজিক্যাল সিদ্ধান্ত নিতে সাহায্য করে।

উদাহরণ ২: IF/ELSE শর্ত ব্যবহার

ধরা যাক, একটি Stored Procedure তৈরি করতে চাই যা বয়সের ভিত্তিতে কর্মচারীকে eligible বা not eligible হিসাবে চিহ্নিত করবে।

CREATE PROCEDURE check_eligibility(emp_age INT)
LANGUAGE plpgsql
AS $BEGIN
    IF emp_age >= 18 THEN
        RAISE NOTICE 'Eligible';
    ELSE
        RAISE NOTICE 'Not Eligible';
    END IF;
END;$;

এটি emp_age এর উপর ভিত্তি করে "Eligible" বা "Not Eligible" বার্তা দিবে।

Stored Procedure কল করার পদ্ধতি:

CALL check_eligibility(20);

এটি "Eligible" বার্তা দেখাবে, যেহেতু বয়স ১৮ বা তার বেশি।

5. Error Handling (ব্যতিক্রম হ্যান্ডলিং)

PostgreSQL তে PL/pgSQL এ EXCEPTION ব্লক ব্যবহার করে error handling করা যায়, যা কোন ভুল হলে আপনার প্রোগ্রামটি ঠিকমতো শেষ না হওয়া আটকাবে না।

উদাহরণ ৩: Error Handling সহ Stored Procedure

CREATE PROCEDURE divide_numbers(num1 INT, num2 INT)
LANGUAGE plpgsql
AS $BEGIN
    BEGIN
        -- Try block
        RAISE NOTICE 'Result:     EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'Error: Cannot divide by zero';
    END;
END;$;

এটি দুইটি সংখ্যার ভাগ করবে। যদি দ্বিতীয় সংখ্যাটি শূন্য হয়, তবে এটি একটি ত্রুটি বার্তা দেখাবে: "Error: Cannot divide by zero"।

Stored Procedure কল করার পদ্ধতি:

CALL divide_numbers(10, 0);

এটি "Error: Cannot divide by zero" বার্তা দেখাবে।

6. Stored Procedure তে Transaction Management

PostgreSQL তে আপনি Stored Procedure এর মধ্যে transaction management করতে পারেন, যেমন COMMIT, ROLLBACK।

উদাহরণ ৪: Transaction Management সহ Stored Procedure

CREATE PROCEDURE transfer_money(sender_id INT, receiver_id INT, amount INT)
LANGUAGE plpgsql
AS $BEGIN
    -- Begin Transaction
    BEGIN
        -- Deduct money from sender
        UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;
        
        -- Add money to receiver
        UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;

        -- Commit the transaction
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- Rollback in case of error
            ROLLBACK;
            RAISE NOTICE 'Transaction failed';
    END;
END;$;

এটি transfer_money নামের একটি Stored Procedure তৈরি করবে যা দুটি অ্যাকাউন্টের মধ্যে টাকা স্থানান্তর করবে। যদি কোনো ভুল হয়, তাহলে ROLLBACK করা হবে, অন্যথায় COMMIT হবে।

7. Stored Procedure এর সুবিধা

• Reusability: Stored Procedures একবার তৈরি করা হলে বারবার ব্যবহৃত হতে পারে।
• Performance: Stored Procedure ব্যবহার করার ফলে কমপ্লেক্স কুয়েরি এক্সিকিউট করা দ্রুত হয়, কারণ এটি ডেটাবেস সার্ভারেই সংরক্ষিত থাকে।
• Security: Stored Procedures ব্যবহার করে আপনি ডেটাবেসের কার্যকলাপ সীমিত করতে পারেন এবং ডেটাবেসের এক্সেস কন্ট্রোল করতে পারেন।
• Error Handling: Stored Procedures এর মধ্যে ত্রুটি (Error) হ্যান্ডলিং সহজে করা যায়।

সারাংশ

PL/pgSQL দিয়ে Stored Procedures তৈরি করার মাধ্যমে আপনি ডেটাবেসে কিছু নির্দিষ্ট কাজ সম্পাদন করতে পারেন যা একাধিকবার ব্যবহার করা যায়। এটি কোড পুনঃব্যবহারযোগ্যতা, পারফরম্যান্স, এবং নিরাপত্তা নিশ্চিত করে। Stored Procedures তে নিয়ন্ত্রণ কাঠামো, ত্রুটি হ্যান্ডলিং, এবং ট্রানজেকশন ম্যানেজমেন্ট ব্যবহার করে আরো জটিল লজিক প্রোগ্রাম করা সম্ভব।