Stored procedure আর user-defined function

১ · ডেটাবেসের ভেতরে রাখা কোড — procedure আর function কী

এতদিন আপনি SQL লিখেছেন একটি একটি স্টেটমেন্ট করে: একটি SELECT, একটি UPDATE. কিন্তু বাস্তবের একটি কাজ প্রায়ই কয়েকটি স্টেটমেন্টের দল। দোকানে একটি অর্ডার নেওয়া মানে: স্টক আছে কি না দেখা, orders-এ একটি সারি, order_items-এ একটি লাইন, products-এ স্টক কমানো — আর এর যেকোনো ধাপ ব্যর্থ হলে সবকিছু বাতিল। এই পুরো দলটিকে একটি নাম দিয়ে ডেটাবেসের ভেতরেই রেখে দেওয়া যায়: place_order(customer, product, qty). এটিই stored procedure — ডেটাবেসে সংরক্ষিত, নাম ধরে ডাকা যায় এমন কোড।

দুই রকম জিনিস আছে, আর পার্থক্যটি পরীক্ষায় প্রায়ই আসে:

এই কোড লেখা হয় DBMS-এর নিজস্ব procedural ভাষায় — যেখানে SQL-এর সাথে আছে ভ্যারিয়েবল, IF, লুপ আর ত্রুটি তোলার ব্যবস্থা। PostgreSQL-এ PL/pgSQL, Oracle-এ PL/SQL, SQL Server-এ T-SQL, আর MySQL-এ SQL standard-এর SQL/PSM ঘেঁষা একটি ভাষা। নাম আর খুঁটিনাটি আলাদা, ধারণা একই।

লাইভ SQL চলে দোকানের ডেটাবেসে (db-shop): customers (customer_id, name, city, phone, joined_on — ৮ জন), categories (৪টি শ্রেণি), products (product_id, name, category_id, price, stock — ১০টি পণ্য; stock-এ CHECK (stock >= 0)), orders (order_id, customer_id, order_date, status — ১০টি অর্ডার) আর order_items (order_id, product_id, quantity, unit_price — ১৬টি লাইন)। বিশেষ করে মনে রাখুন: Smart Watch (110)-এর দাম 3500, স্টক মাত্র 5. দুটি এডিটর নিজের ছোট wallet টেবিল বানিয়ে নেয়।

২ · কেন ডেটাবেসে কোড রাখবেন — round trip, নিয়ম আর অধিকার

প্রথম কারণ গতি, আরও নির্দিষ্টভাবে round trip. অ্যাপ সার্ভার আর ডেটাবেস সার্ভার সাধারণত আলাদা যন্ত্র; প্রতিটি স্টেটমেন্ট পাঠানো আর উত্তর ফেরত পাওয়া মানে নেটওয়ার্কে একবার যাওয়া-আসা। একই ডেটা সেন্টারে এটি সাধারণত এক মিলিসেকেন্ডের কম থেকে কয়েক মিলিসেকেন্ড; ভিন্ন শহরের সার্ভারে আরও বেশি। আর যতক্ষণ অ্যাপ নেটওয়ার্কে অপেক্ষা করছে, ততক্ষণ transaction খোলা, lock ধরা। নিচের ডায়াগ্রামে একটি অর্ডার দুইভাবে দেখুন:

দ্বিতীয় কারণ: নিয়ম এক জায়গায়। একটি দোকানের ডেটাবেসে লেখে ওয়েবসাইট, মোবাইল অ্যাপ, অ্যাডমিন প্যানেল আর রাতের আমদানি-স্ক্রিপ্ট — হয়তো চারটি ভিন্ন ভাষায়। “স্টক কমাও, শূন্যের নিচে নয়, অর্ডারের দাম products থেকে নাও” — এই ব্যবসার নিয়ম চার জায়গায় চারবার লিখলে কোনো একদিন একটিতে ভুল থাকবেই। procedure-এ লিখলে সবাই একই কোড ডাকে।

তৃতীয় কারণ: নিরাপত্তা। ডেটাবেসে অধিকার দেওয়া যায় শুধু procedure চালানোর — EXECUTE privilege — টেবিলে সরাসরি UPDATE-এর অধিকার না দিয়ে। তখন অ্যাপের অ্যাকাউন্ট চুরি হলেও আক্রমণকারী UPDATE products SET price = 1 চালাতে পারে না; সে শুধু place_order ডাকতে পারে, যা নিজের নিয়ম মানে। এটি least privilege নীতির প্রয়োগ (পর্ব ৫১)।

procedure-এর ভেতরে যে কোয়েরি থাকে, সেটি সাধারণ SQL-ই। যেমন একটি function customer_tier(id) গ্রাহককে gold/silver/regular শ্রেণি দেবে তাঁর মোট কেনাকাটা দেখে। তার ভেতরের হিসাবটি এখানেই চালিয়ে দেখুন — বাতিল অর্ডার বাদ দিয়ে প্রত্যেক গ্রাহকের মোট:

-- customer_tier() function ভেতরে ঠিক এই হিসাবটি করত
SELECT c.customer_id, c.name,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

৩ · কেন অনেক দল এড়িয়ে চলে — ভারসাম্যপূর্ণ হিসাব

উপরের যুক্তিগুলো শক্ত, তবু আজ অনেক সফল দল ইচ্ছে করেই ভারী logic ডেটাবেসে রাখে না। তাদের কারণগুলোও সমান বাস্তব:

বাস্তবে বেশিরভাগ দল মাঝামাঝি পথ নেয়: ডেটার নিজের সত্যতা — NOT NULL, CHECK, foreign key, UNIQUE, আর “স্টক শূন্যের নিচে নয়” — থাকে ডেটাবেসে, কারণ কোনো অ্যাপ এগুলো এড়াতে পারে না। কাজের প্রবাহ — SMS পাঠানো, পেমেন্ট গেটওয়ে ডাকা, ছাড় হিসাবের জটিল নিয়ম — থাকে অ্যাপে। আর procedure ব্যবহার হয় বেছে বেছে: যেখানে অনেক সারি নিয়ে ভারী কাজ (মাসিক সুদ, পুরোনো ডেটা সরানো), যেখানে কঠোর অধিকার-নিয়ন্ত্রণ দরকার, বা যেখানে round trip সত্যিই সমস্যা। ব্যাংক আর সরকারি বড় সিস্টেমে, যেখানে Oracle বা SQL Server বহু বছর ধরে চলছে, procedure-এর ব্যবহার এখনো ব্যাপক; নতুন ওয়েব কোম্পানিগুলোতে কম।

৪ · PL/pgSQL-এর গঠন — CREATE FUNCTION থেকে RETURN QUERY

PostgreSQL-এ একটি function-এর কাঠামো প্রায় সবসময় একই। নিচের customer_tier অংশ ২-এর হিসাবটি নিয়ে গ্রাহকের শ্রেণি ফেরত দেয়। প্রতিটি অংশ চিনে নিন:

CREATE OR REPLACE FUNCTION customer_tier(p_customer int)
RETURNS text                      -- কী ফেরত দেবে
LANGUAGE plpgsql
STABLE                            -- টেবিল পড়ে, বদলায় না (অংশ ৫)
AS $$
DECLARE
  v_total numeric := 0;           -- ভ্যারিয়েবল, প্রাথমিক মানসহ
BEGIN
  SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
  INTO v_total                    -- ফল ভ্যারিয়েবলে
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.customer_id = p_customer AND o.status <> 'cancelled';

  IF v_total >= 20000 THEN
    RETURN 'gold';
  ELSIF v_total >= 5000 THEN
    RETURN 'silver';
  ELSE
    RETURN 'regular';
  END IF;
END;
$$;

SELECT name, customer_tier(customer_id) AS tier FROM customers;

• CREATE OR REPLACE — না থাকলে বানানো, থাকলে বদলানো। তবে প্যারামিটারের টাইপ বদলালে PostgreSQL এটিকে আলাদা function ধরে (একই নামে একাধিক function থাকতে পারে — overloading), আর ফেরত-টাইপ বদলাতে আগে DROP লাগে।
• $$ … $$ — dollar quoting: পুরো শরীরটি একটি লেখা (string) হিসেবে PostgreSQL-এ যায়। '…' ব্যবহার করলে ভেতরের প্রতিটি 'gold'-কে ''gold'' লিখতে হতো।
• DECLARE — ভ্যারিয়েবল ঘোষণা; মান না দিলে শুরুতে NULL. প্যারামিটারের নামে p_, ভ্যারিয়েবলে v_ — এই রীতি কলামের নামের সাথে সংঘর্ষ ঠেকায়।
• BEGIN … END — শরীর। সাবধান: এটি transaction-এর BEGIN নয়, শুধু একটি block. function চলে ডাকার স্টেটমেন্টের transaction-এর ভেতরেই।
• SELECT … INTO — কোয়েরির প্রথম সারি ভ্যারিয়েবলে। কোনো সারি না এলে ভ্যারিয়েবল NULL হয় আর FOUND হয় false — অংশ ৭-এ এটিই কাজে লাগবে।
• IF / ELSIF / ELSE — খেয়াল করুন বানান: ELSIF (MySQL-এ ELSEIF).

db-shop-এর ডেটায় এই কোয়েরির ফল হতো: Rahim gold (37180), Nusrat আর Karima silver (5050 আর 5000 — ঠিক 5000-ও silver, কারণ শর্ত >=), বাকি সবাই regular — Sabbir আর Tanvir-ও, কারণ COALESCE তাঁদের 0 দিয়েছে।

লুপ, ত্রুটি আর বার্তা

PL/pgSQL-এ লুপ তিন রকম: LOOP … EXIT WHEN … END LOOP, WHILE, আর সবচেয়ে বেশি ব্যবহৃত FOR r IN SELECT … LOOP — যা কোয়েরির প্রতিটি সারি একে একে দেয়। RAISE NOTICE শুধু একটি বার্তা দেখায়; RAISE EXCEPTION ত্রুটি তোলে, function থামায়, আর ডাকার স্টেটমেন্টের সব বদল বাতিল করে। % চিহ্নের জায়গায় পরের মানগুলো বসে। নিচে একটি নামহীন block (DO), যা কোনো function না বানিয়েই PL/pgSQL চালায়:

DO $$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT product_id, name, stock FROM products WHERE stock < 10 LOOP
    RAISE NOTICE 'restock % (%): only % left', r.product_id, r.name, r.stock;
  END LOOP;

  IF NOT EXISTS (SELECT 1 FROM categories WHERE name = 'Books') THEN
    RAISE EXCEPTION 'category Books is missing'
      USING ERRCODE = 'P0002', HINT = 'run the seed migration first';
  END IF;
END;
$$;

SQL-language function আর table-valued function

সব function-এ PL/pgSQL লাগে না। শুধু একটি হিসাব হলে LANGUAGE sql যথেষ্ট — সহজ, আর PostgreSQL প্রায়ই এমন function-কে কোয়েরির ভেতরে সরাসরি বসিয়ে (inline) দেয়, তাই ডাকার আলাদা খরচ থাকে না। আর function যদি একটি সংখ্যার বদলে পুরো টেবিল ফেরত দেয় — table-valued function — তাকে FROM-এ টেবিলের মতো ব্যবহার করা যায়:

-- SQL-language function (PostgreSQL 14+ এর SQL-standard শরীর)
CREATE OR REPLACE FUNCTION line_total(qty int, price numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE
RETURN qty * price;            -- পুরোনো রূপ: AS $$ SELECT qty * price $$

-- table-valued function: একজন গ্রাহকের অর্ডারগুলো
-- (ধরে নিচ্ছি orders.status-এর টাইপ text)
CREATE OR REPLACE FUNCTION customer_orders(p_customer int)
RETURNS TABLE (order_id int, status text, total numeric)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
  RETURN QUERY
  SELECT o.order_id, o.status, SUM(oi.quantity * oi.unit_price)
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.customer_id = p_customer
  GROUP BY o.order_id, o.status;
END;
$$;

SELECT * FROM customer_orders(1) ORDER BY order_id;

একটি সূক্ষ্ম ফাঁদ: RETURNS TABLE-এর কলামগুলো (order_id, status, total) PL/pgSQL-এর ভেতরে ভ্যারিয়েবলও। তাই কোয়েরিতে o.order_id না লিখে শুধু order_id লিখলে PostgreSQL বলে column reference "order_id" is ambiguous. সবসময় table alias দিয়ে কলাম লিখুন। আর RETURN QUERY-এর কলামের টাইপ হুবহু মিলতে হয় — status varchar হলে o.status::text লিখতে হতো।

SQLite-এ table-valued function লেখা যায় না, কিন্তু সবচেয়ে কাছের বিকল্প আছে: একটি view (পর্ব ২৪), আর ব্যবহারের সময় WHERE দিয়ে “প্যারামিটার”। ফলাফল একই, শুধু প্যারামিটারটি বাইরে থেকে আসে:

CREATE VIEW order_lines AS
SELECT o.order_id, o.customer_id, o.status, p.name AS product,
       oi.quantity, oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id;

-- PostgreSQL-এ হতো: SELECT * FROM customer_orders(1);
SELECT order_id, status, product, quantity, line_total
FROM order_lines WHERE customer_id = 1
ORDER BY order_id, product;

৫ · Volatility — IMMUTABLE, STABLE, VOLATILE আর host-ভাষার function

PostgreSQL-এ প্রতিটি function-এর একটি volatility লেবেল থাকে — এটি planner-কে দেওয়া একটি প্রতিশ্রুতি, function-টি কতটা “স্থির”:

লেবেলটি শুধু কাগুজে নয় — planner এটি দেখে সিদ্ধান্ত নেয়, কোয়েরি কত দ্রুত চলবে, এমনকি ফল সঠিক হবে কি না। ধরুন orders-এ এক লাখ সারি, order_date-এ index আছে, আর একটি function cutoff_date() ফেরত দেয় আজ থেকে ৩০ দিন আগের তারিখ:

Host-ভাষা থেকে function — SQLite-এর পথ

SQLite-এ SQL দিয়ে function লেখা না গেলেও অ্যাপের ভাষায় লেখা function SQLite-এ বসানো যায় — C-তে sqlite3_create_function(), Python-এ create_function(). তারপর সেটি SQL-এর ভেতরে অন্য যেকোনো function-এর মতো চলে। নিচের function ফোন নম্বর এক রূপে আনে (+880 1711-000001 → 01711000001). deterministic=True হলো PostgreSQL-এর IMMUTABLE-এর SQLite-রূপ:

import sqlite3

def norm_phone(p):                     # '+880 1711-000001' -> '01711000001'
    if p is None:                      # SQL NULL arrives as None
        return None
    digits = "".join(ch for ch in p if ch.isdigit())
    return digits[2:] if digits.startswith("880") else digits

con = sqlite3.connect(":memory:")
con.create_function("norm_phone", 1, norm_phone, deterministic=True)
con.execute("CREATE TABLE customers (name TEXT, phone TEXT)")
con.executemany("INSERT INTO customers VALUES (?, ?)", [
    ("Rahim", "+880 1711-000001"), ("Karima", "01811000002"), ("Tanvir", None)])
for row in con.execute("SELECT name, norm_phone(phone) FROM customers"):
    print(row)
con.execute("CREATE INDEX customers_phone_idx ON customers (norm_phone(phone))")
print("index on norm_phone(phone): ok")
con.create_function("lucky", 0, lambda: 7)        # not marked deterministic
try:
    con.execute("CREATE INDEX bad_idx ON customers (lucky())")
except sqlite3.OperationalError as e:
    print("error:", e)

৬ · PROCEDURE আর CALL — PostgreSQL আর MySQL

PostgreSQL 11-এর আগে সবকিছুই function ছিল — ডেটা বদলানোর কাজও SELECT f() দিয়ে ডাকা হতো। ১১ থেকে এসেছে আসল CREATE PROCEDURE, যা ডাকা হয় CALL দিয়ে। মূল পার্থক্যগুলো:

procedure-এর ভেতরে COMMIT কেন দরকার? একটি বড় কাজ টুকরো টুকরো করতে। ধরুন audit_log টেবিল থেকে দুই বছরের পুরোনো কয়েক কোটি সারি মুছতে হবে। একটি DELETE-এ করলে বিশাল একটি transaction — দীর্ঘ lock, বিশাল log, আর মাঝপথে ব্যর্থ হলে সব ফেরত। তার বদলে প্রতি ব্যাচ শেষে COMMIT:

CREATE OR REPLACE PROCEDURE purge_audit_log(p_before date, p_batch int DEFAULT 10000)
LANGUAGE plpgsql
AS $$
DECLARE
  v_deleted int;
BEGIN
  LOOP
    DELETE FROM audit_log
    WHERE log_id IN (SELECT log_id FROM audit_log
                     WHERE logged_at < p_before
                     LIMIT p_batch);
    GET DIAGNOSTICS v_deleted = ROW_COUNT;   -- এই DELETE কতটি সারি মুছল
    COMMIT;                                  -- প্রতিটি ব্যাচ আলাদা transaction
    EXIT WHEN v_deleted = 0;
  END LOOP;
END;
$$;

CALL purge_audit_log('2024-01-01');      -- autocommit-এ: চলবে
-- BEGIN; CALL purge_audit_log(...);     -- ত্রুটি: invalid transaction termination

MySQL-এর procedure — DELIMITER, IN/OUT/INOUT আর HANDLER

MySQL-এ প্রথম যে জিনিসটি অদ্ভুত লাগে, তা হলো DELIMITER. mysql কমান্ড-লাইন client প্রতিটি ; দেখে ভাবে স্টেটমেন্ট শেষ, আর সার্ভারে পাঠিয়ে দেয় — অথচ procedure-এর শরীরের ভেতরেই অনেক ;. তাই কিছুক্ষণের জন্য শেষ-চিহ্ন বদলে // করা হয়। এটি client-এর কৌশল, SQL-এর অংশ নয়; অ্যাপের driver দিয়ে CREATE PROCEDURE পাঠালে DELIMITER লাগে না।

প্যারামিটারের তিন mode: IN (ডিফল্ট — ভেতরে আসে), OUT (বাইরে যায়, শুরুতে NULL), INOUT (দুই দিকেই)। ত্রুটি সামলাতে DECLARE … HANDLER: EXIT HANDLER ত্রুটি হলে নির্দিষ্ট কাজ করে block থেকে বেরিয়ে যায়, CONTINUE HANDLER কাজ করে পরের লাইন থেকে চালিয়ে যায়। নিজে ত্রুটি তুলতে SIGNAL SQLSTATE '45000' — 45000 হলো “ব্যবহারকারীর তৈরি ত্রুটি”-র প্রচলিত SQLSTATE.

DELIMITER //
CREATE PROCEDURE customer_stats(IN p_customer INT,
                                OUT p_orders INT, OUT p_spent DECIMAL(12,2))
BEGIN
  SELECT COUNT(DISTINCT o.order_id), COALESCE(SUM(oi.quantity * oi.unit_price), 0)
  INTO p_orders, p_spent
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.customer_id = p_customer AND o.status <> 'cancelled';
END //
DELIMITER ;

CALL customer_stats(1, @n, @total);   -- OUT মান যায় session variable-এ
SELECT @n, @total;                    -- 3, 37180.00

৭ · পূর্ণ উদাহরণ — place_order: স্টক যাচাই থেকে atomic বদল

এবার এই পর্বের মূল উদাহরণ। নিয়মগুলো: (১) পরিমাণ ধনাত্মক; (২) পণ্যটি থাকতে হবে; (৩) স্টক যথেষ্ট না হলে ত্রুটি, কিছুই বদলাবে না; (৪) একটি নতুন অর্ডার, একটি অর্ডার-লাইন (দাম products থেকে), আর স্টক কমানো — সব একসাথে, নয়তো একটিও না; (৫) একসাথে দুজন ক্রেতা শেষ ঘড়িটি কিনতে এলে একজনই পাবেন। দুটি DBMS-এ একই কাজ (ধরে নিচ্ছি orders.order_id স্বয়ংক্রিয় — PostgreSQL-এ IDENTITY, MySQL-এ AUTO_INCREMENT):

CREATE OR REPLACE FUNCTION place_order(p_customer int, p_product int, p_qty int)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
  v_stock    int;
  v_price    numeric(10,2);
  v_order_id int;
BEGIN
  IF p_qty IS NULL OR p_qty <= 0 THEN
    RAISE EXCEPTION 'quantity must be positive, got %', p_qty;
  END IF;

  -- সারিটি lock করে পড়া: একই পণ্যের অন্য অর্ডার এখানে অপেক্ষা করবে
  SELECT stock, price INTO v_stock, v_price
  FROM products WHERE product_id = p_product
  FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'product % not found', p_product;
  ELSIF v_stock < p_qty THEN
    RAISE EXCEPTION 'insufficient stock for product %: have %, need %',
                    p_product, v_stock, p_qty;
  END IF;

  INSERT INTO orders (customer_id, order_date, status)
  VALUES (p_customer, current_date, 'pending')
  RETURNING order_id INTO v_order_id;     -- গ্রাহক না থাকলে FK নিজেই ত্রুটি দেবে

  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (v_order_id, p_product, p_qty, v_price);

  UPDATE products SET stock = stock - p_qty WHERE product_id = p_product;

  RETURN v_order_id;
END;
$$;

SELECT place_order(1, 110, 2);    -- 1011

DELIMITER //
CREATE PROCEDURE place_order(IN p_customer INT, IN p_product INT,
                             IN p_qty INT, OUT p_order_id INT)
BEGIN
  DECLARE v_stock INT;
  DECLARE v_price DECIMAL(10,2);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;        -- যা হয়েছে সব ফেরাও
    RESIGNAL;        -- মূল ত্রুটিটি ডাকার জনের কাছে পাঠাও
  END;

  IF p_qty IS NULL OR p_qty <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'quantity must be positive';
  END IF;

  START TRANSACTION;
  SELECT stock, price INTO v_stock, v_price
  FROM products WHERE product_id = p_product
  FOR UPDATE;                    -- সারি না পেলে v_stock থাকে NULL

  IF v_stock IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'product not found';
  ELSEIF v_stock < p_qty THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'insufficient stock';
  END IF;

  INSERT INTO orders (customer_id, order_date, status)
  VALUES (p_customer, CURDATE(), 'pending');
  SET p_order_id = LAST_INSERT_ID();

  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (p_order_id, p_product, p_qty, v_price);

  UPDATE products SET stock = stock - p_qty WHERE product_id = p_product;
  COMMIT;
END //
DELIMITER ;

CALL place_order(1, 110, 2, @new_id);
SELECT @new_id;                  -- 1011

দুটির পার্থক্যগুলো শিক্ষণীয়। PostgreSQL-এর function নিজে transaction শুরু করে না — ডাকার স্টেটমেন্টের transaction-এ চলে, আর RAISE EXCEPTION হলে পুরো স্টেটমেন্ট (সাথে function-এর সব বদল) বাতিল হয়। MySQL-এর procedure নিজেই START TRANSACTION আর COMMIT করছে, আর ত্রুটি হলে HANDLER ROLLBACK করে। এর একটি পার্শ্বপ্রতিক্রিয়া আছে: MySQL-এ START TRANSACTION ডাকার জনের আগে থেকে খোলা transaction-কে নীরবে commit করে দেয় (implicit commit). তাই কোন স্তর transaction-এর মালিক — অ্যাপ না procedure — দলে আগে থেকে ঠিক করে নিতে হয়। নিচের ডায়াগ্রামে PostgreSQL সংস্করণটি দুবার চলছে: প্রথমে সফল, তারপর ব্যর্থ:

1FUNCTION place_order(p_customer, p_product, p_qty)2BEGIN3  SELECT stock, price INTO v_stock, v_price4    FROM products WHERE ... FOR UPDATE;5  IF NOT FOUND THEN RAISE EXCEPTION ...;6  ELSIF v_stock < p_qty THEN RAISE EXCEPTION ...;7  END IF;8  INSERT INTO orders ... RETURNING order_id INTO v_order_id;9  INSERT INTO order_items VALUES (v_order_id, ...);10  UPDATE products SET stock = stock - p_qty ...;11  RETURN v_order_id;12END;

SQLite-এ একই কাজ — BEGIN…COMMIT-এর ভেতরে

SQLite-এ procedure নেই, তাই এই স্টেটমেন্টগুলো অ্যাপ পাঠায়, একটি transaction-এ। প্রথমে সফল পথ — Rahim-এর ২টি Smart Watch:

BEGIN;
SELECT stock, price FROM products WHERE product_id = 110;  -- অ্যাপ যাচাই করে
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2025-10-20', 'pending');
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT last_insert_rowid(), 110, 2, price FROM products WHERE product_id = 110;
UPDATE products SET stock = stock - 2 WHERE product_id = 110;
COMMIT;
SELECT o.order_id, o.status, oi.quantity, oi.unit_price, p.stock
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE o.order_id = (SELECT MAX(order_id) FROM orders);

এবার ব্যর্থ পথ। procedure যে কাজটি IF … RAISE দিয়ে করে, অ্যাপ-পক্ষে তার একটি নিরাপদ রূপ হলো শর্তসহ UPDATE: WHERE stock >= 10 — যাচাই আর বদল একই স্টেটমেন্টে, তাই মাঝখানে কেউ ঢুকতে পারে না। কতটি সারি বদলাল, দেখে অ্যাপ সিদ্ধান্ত নেয়:

BEGIN;
INSERT INTO orders (customer_id, order_date, status)
VALUES (8, '2025-10-20', 'pending');
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT last_insert_rowid(), 110, 10, price FROM products WHERE product_id = 110;
UPDATE products SET stock = stock - 10
WHERE product_id = 110 AND stock >= 10;       -- স্টক থাকলেই কমাও
-- "০টি সারি বদলেছে" দেখে অ্যাপ সব ফেরায়:
ROLLBACK;
SELECT (SELECT COUNT(*) FROM orders)      AS orders,
       (SELECT COUNT(*) FROM order_items) AS lines,
       (SELECT stock FROM products WHERE product_id = 110) AS watch_stock;

শেষ প্রতিরক্ষা থাকে ডেটাবেসের নিজের নিয়মে। db-shop-এর products-এ আছে CHECK (stock >= 0); শর্ত ছাড়া UPDATE products SET stock = stock - 10 WHERE product_id = 110 চালালে পান CHECK constraint failed: stock >= 0. আর procedure-এর মতো নিজের ত্রুটি-বার্তা চাইলে SQLite-এ আছে trigger (পর্ব ২৯-এ বিস্তারিত) — RAISE(ABORT, '…') দিয়ে:

CREATE TRIGGER order_items_stock_guard
BEFORE INSERT ON order_items
WHEN (SELECT stock FROM products
      WHERE product_id = NEW.product_id) < NEW.quantity
BEGIN
  SELECT RAISE(ABORT, 'insufficient stock');
END;

INSERT INTO orders VALUES (1011, 8, '2025-10-20', 'pending');
INSERT INTO order_items VALUES (1011, 110, 10, 3500);

৮ · transfer_money, cursor আর ত্রুটি সামলানো

দ্বিতীয় ক্লাসিক উদাহরণ: একটি wallet থেকে আরেকটিতে টাকা পাঠানো, bKash-এর “Send Money”-র মতো। নিয়ম: প্রেরকের ব্যালান্স যথেষ্ট হতে হবে, প্রেরক আর প্রাপক আলাদা, অঙ্ক ধনাত্মক, আর ডেবিট-ক্রেডিট দুটিই হবে, নয়তো একটিও না। এখানে একটি নতুন বিপদ আছে — deadlock: A→B আর B→A দুটি লেনদেন একসাথে চললে প্রথমটি A-র সারি lock করে B-র জন্য অপেক্ষা করে, দ্বিতীয়টি B lock করে A-র জন্য। প্রতিকার: দুটি সারি সবসময় একই ক্রমে lock করা (ছোট id আগে)।

CREATE OR REPLACE PROCEDURE transfer_money(p_from int, p_to int, p_amount numeric)
LANGUAGE plpgsql
AS $$
BEGIN
  IF p_amount IS NULL OR p_amount <= 0 OR p_from = p_to THEN
    RAISE EXCEPTION 'invalid transfer';
  END IF;

  -- দুটি সারি একই ক্রমে lock: deadlock এড়াতে
  PERFORM 1 FROM wallets WHERE wallet_id IN (p_from, p_to)
  ORDER BY wallet_id FOR UPDATE;

  UPDATE wallets SET balance = balance - p_amount
  WHERE wallet_id = p_from AND balance >= p_amount;
  IF NOT FOUND THEN                        -- শেষ UPDATE কোনো সারি বদলায়নি
    RAISE EXCEPTION 'insufficient balance or no wallet %', p_from;
  END IF;

  UPDATE wallets SET balance = balance + p_amount WHERE wallet_id = p_to;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'wallet % not found', p_to;   -- ডেবিটও ফিরে যাবে
  END IF;

  INSERT INTO transfers (from_id, to_id, amount) VALUES (p_from, p_to, p_amount);
END;
$$;

CALL transfer_money(1, 2, 500);

এখানে procedure-এ COMMIT নেই, ইচ্ছে করেই: CALL-এর transaction-এর ভেতরেই সব চলে, আর যেকোনো RAISE পুরো CALL ফেরায় — দ্বিতীয় UPDATE ব্যর্থ হলে প্রথমটিও। এবার SQLite-এ। এডিটরের ছোট schema: wallets (wallet_id, owner, balance — CHECK (balance >= 0); Rahim 2000, Karima 300, Sabbir 0) আর transfers (transfer_id, from_id, to_id, amount). প্রথমে Rahim থেকে Karima-কে ৫০০ টাকা:

-- transfer_money(1, 2, 500)
BEGIN;
UPDATE wallets SET balance = balance - 500 WHERE wallet_id = 1;
UPDATE wallets SET balance = balance + 500 WHERE wallet_id = 2;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 500);
COMMIT;
SELECT * FROM wallets;
SELECT * FROM transfers;
SELECT SUM(balance) AS total_money FROM wallets;

এবার একটি বিপজ্জনক ভুল, যা AI-এর লেখা কোডেও প্রায়ই দেখা যায়। ডেবিটে শর্ত আছে (balance >= 5000), কিন্তু কেউ দেখছে না সেটি আসলে কোনো সারি বদলাল কি না — PL/pgSQL-এর IF NOT FOUND-এর কাজটি বাদ পড়েছে:

-- ভুল: ডেবিট শর্তসহ, কিন্তু ফল যাচাই ছাড়াই ক্রেডিট
BEGIN;
UPDATE wallets SET balance = balance - 5000
WHERE wallet_id = 1 AND balance >= 5000;
UPDATE wallets SET balance = balance + 5000 WHERE wallet_id = 3;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 3, 5000);
COMMIT;
SELECT * FROM wallets;
SELECT SUM(balance) AS total_money FROM wallets;

বিকল্প নিরাপদ পথ: ডেবিটে কোনো শর্তই না দিয়ে CHECK-কে পাহারাদার বানানো। ব্যালান্স ঋণাত্মক হওয়ার মুহূর্তে ডেটাবেস নিজেই স্টেটমেন্টটি থামায়:

BEGIN;
UPDATE wallets SET balance = balance - 5000 WHERE wallet_id = 1;
UPDATE wallets SET balance = balance + 5000 WHERE wallet_id = 3;
COMMIT;

Cursor — সারি ধরে ধরে, আর কখন দরকার নেই

Cursor একটি কোয়েরির ফলের ওপর আঙুল রেখে একটি একটি সারি পড়ার ব্যবস্থা। PL/pgSQL-এর FOR r IN SELECT … LOOP ভেতরে ভেতরে একটি cursor-ই। MySQL-এ পুরো রূপটি লিখতে হয় — DECLARE, OPEN, FETCH, আর সারি ফুরোলে ধরার জন্য একটি NOT FOUND HANDLER:

DECLARE done INT DEFAULT 0;
DECLARE v_id INT;
DECLARE cur CURSOR FOR SELECT product_id FROM products WHERE stock < 10;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
  FETCH cur INTO v_id;
  IF done THEN LEAVE read_loop; END IF;
  UPDATE products SET stock = stock + 20 WHERE product_id = v_id;
END LOOP;
CLOSE cur;

কিন্তু এই এগারো লাইনের কাজ আসলে একটি স্টেটমেন্টের। SQL set-ভিত্তিক ভাষা — “এই শর্তের সব সারি” একবারে বলাই তার শক্তি:

-- cursor-এর লুপের বদলে: স্টক ১০-এর কম এমন সব পণ্যে ২০টি করে যোগ
UPDATE products SET stock = stock + 20 WHERE stock < 10;

SELECT product_id, name, stock FROM products
WHERE product_id IN (103, 107, 110);

Procedure-এর ভেতরে ত্রুটি ধরা

PL/pgSQL-এ BEGIN … EXCEPTION WHEN … END দিয়ে ত্রুটি ধরে সামলানো যায়। block-টি ভেতরে ভেতরে একটি savepoint: ত্রুটি হলে শুধু সেই block-এর বদলগুলো ফেরে, বাকি transaction চলতে থাকে।

BEGIN
  INSERT INTO customers (name, city, phone, joined_on)
  VALUES (p_name, p_city, p_phone, current_date);
EXCEPTION
  WHEN unique_violation THEN          -- phone UNIQUE: আগেই নিবন্ধিত
    RAISE NOTICE 'phone % already registered', p_phone;
    -- এখানে যা ঠিক মনে হয়: বিদ্যমান গ্রাহক খুঁজে নেওয়া, বা আবার RAISE
END;

দুটি সতর্কতা। প্রথমত, savepoint-এর খরচ আছে; লক্ষ সারির লুপের ভেতরে EXCEPTION block রাখলে কাজ লক্ষণীয়ভাবে ধীর হয়। দ্বিতীয়ত, ত্রুটি গিলে ফেলা (WHEN OTHERS THEN NULL) সবচেয়ে খারাপ অভ্যাস — ব্যর্থতা লুকিয়ে যায়, আর ডেটা নীরবে ভুল হয়। যে ত্রুটি আপনি নির্দিষ্টভাবে সামলাতে জানেন, শুধু সেটিই ধরুন।

৯ · নিরাপত্তা, টেস্ট আর পারফরম্যান্স

Dynamic SQL-এ SQL injection

একটি প্রচলিত ভুল ধারণা: “stored procedure ব্যবহার করলে SQL injection হয় না।” সত্য শুধু এটুকু: procedure-এর প্যারামিটার যখন মান হিসেবে ব্যবহার হয় (WHERE product_id = p_product), তখন তা কখনো SQL কোড হয় না। কিন্তু procedure-এর ভেতরে লেখা জোড়া দিয়ে কোয়েরি বানালে — dynamic SQL — ব্যবহারকারীর লেখা আবার কোড হয়ে যেতে পারে। নিরাপদ পথ দুটি: মান পাঠান USING দিয়ে, আর টেবিল বা কলামের নাম (যা placeholder হতে পারে না) বসান format()-এর %I দিয়ে, যা নামটিকে ঠিকমতো উদ্ধৃত করে:

-- বিপজ্জনক: p_status সরাসরি লেখায় জোড়া হচ্ছে
-- EXECUTE 'SELECT count(*) FROM ' || p_table || ' WHERE status = ''' || p_status || '''';

-- নিরাপদ: নাম %I দিয়ে, মান USING দিয়ে ($1 = প্রথম মান)
EXECUTE format('SELECT count(*) FROM %I WHERE status = $1', p_table)
  INTO v_count
  USING p_status;

-- আরও নিরাপদ: কোন টেবিল চলবে, তার তালিকা আগে থেকে ঠিক করা
IF p_table NOT IN ('orders', 'archived_orders') THEN
  RAISE EXCEPTION 'table not allowed';
END IF;

%L একটি মানকে লেখা হিসেবে উদ্ধৃত করে, তবে মানের জন্য USING ভালো — কোড আর ডেটা পুরোপুরি আলাদা থাকে। MySQL-এ সমতুল্য: PREPARE stmt FROM @sql; EXECUTE stmt USING @status;, আর SQL Server-এ প্যারামিটারসহ sp_executesql. মূল নীতিটি পর্ব ৪৯ আর ৫১-এর মতোই: ব্যবহারকারীর দেওয়া কোনো মান কখনো SQL-এর লেখায় জোড়া দেবেন না।

SECURITY DEFINER — কার অধিকারে চলবে

PostgreSQL-এ function সাধারণত চলে ডাকার জনের অধিকারে (SECURITY INVOKER). SECURITY DEFINER লিখলে চলে function-এর মালিকের অধিকারে — এভাবেই “টেবিলে অধিকার নেই, কিন্তু procedure চালাতে পারে” ব্যবস্থাটি তৈরি হয়। এটি শক্তিশালী, তাই বিপজ্জনকও: function-এর ভেতরের কোনো ফাঁক মানে মালিকের পূর্ণ অধিকার আক্রমণকারীর হাতে। PostgreSQL-এর ডকুমেন্টেশনের পরামর্শগুলো:

CREATE OR REPLACE FUNCTION place_order(p_customer int, p_product int, p_qty int)
RETURNS int
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = shop, pg_temp      -- নিজের schema ঠিক করা, pg_temp শেষে
AS $$ ... $$;

REVOKE EXECUTE ON FUNCTION place_order(int, int, int) FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION place_order(int, int, int) TO web_app;

search_path ঠিক না করলে আক্রমণকারী নিজের schema-য় products নামে একটি টেবিল বা একই নামের একটি operator বানিয়ে function-কে সেটি ব্যবহার করাতে পারে। আর PostgreSQL-এ নতুন function চালানোর অধিকার ডিফল্টভাবে PUBLIC — সবার — থাকে, তাই REVOKE লাইনটি ভুলবেন না।

টেস্ট আর version

procedure-ও কোড, তাই তারও টেস্ট লাগে। PostgreSQL-এর জন্য pgTAP একটি জনপ্রিয় unit-test framework: টেস্টগুলো নিজেরাই SQL, একটি transaction-এ চলে আর শেষে ROLLBACK হয়, তাই ডেটাবেস নোংরা হয় না। SQL Server-এ tSQLt, Oracle-এ utPLSQL একই কাজ করে। একটি ভালো টেস্ট-তালিকায় থাকে সফল পথ, প্রতিটি ব্যর্থ পথ (স্টক কম, পণ্য নেই, পরিমাণ শূন্য), আর অপরিবর্তনীয় নিয়ম (মোট টাকা অপরিবর্তিত)। আর procedure-এর প্রতিটি সংস্করণ রাখুন migration ফাইলে (পর্ব ২৩, ৫০), git-এ — কখনো সরাসরি production সার্ভারে হাতে বদলাবেন না।

পারফরম্যান্স আর plan cache

PL/pgSQL একটি function-এর প্রতিটি SQL স্টেটমেন্টের plan প্রথমবার চলার সময় বানায়, আর সেই সংযোগে (session) জমা রাখে; পরের ডাকগুলোতে আবার parse আর plan করতে হয় না। প্রথম কয়েকবার (বর্তমানে পাঁচবার) প্যারামিটারের আসল মান দেখে আলাদা plan বানানো হয়; তারপর একটি সাধারণ (generic) plan যথেষ্ট ভালো হলে সেটিতে চলে যায় — plan_cache_mode সেটিং দিয়ে এটি নিয়ন্ত্রণ করা যায়। SQL Server-ও procedure-এর plan জমা রাখে, আর MySQL প্রতিটি সংযোগে stored program-এর একটি cache রাখে। লাভ স্পষ্ট, তবে ফাঁদও আছে: প্রথম প্যারামিটারের জন্য ভালো plan পরের অন্য রকম প্যারামিটারের জন্য খারাপ হতে পারে — ভাবনার প্রশ্ন ৫-এ।

১০ · AI-এর যুগে procedure লেখা

“একটি place_order procedure লিখে দাও” — AI কয়েক সেকেন্ডে চল্লিশ লাইন দেয়, আর তা সাধারণত চলেও। কিন্তু procedure-এর ভুলগুলো সফল পথে দেখা যায় না; দেখা যায় ব্যর্থ পথে, একসাথে দুটি অনুরোধ এলে, বা অন্য DBMS-এ। AI-এর সাধারণ ভুলগুলো:

AI-এর লেখা procedure চালানোর আগে — চেকলিস্ট

• আমার DBMS আর সংস্করণে কোডটি তৈরি হয় (একটি test ডেটাবেসে CREATE চালিয়েছি)
• সফল পথ আর প্রতিটি ব্যর্থ পথ নমুনা ডেটায় চালিয়েছি; ব্যর্থতার পরে কোনো অর্ধেক বদল থাকে না
• প্রতিটি যাচাই-তারপর-বদল জোড়ায় lock বা শর্তসহ UPDATE আছে, আর সারি-সংখ্যা দেখা হয়
• আগে-পরে অপরিবর্তনীয় হিসাব মিলিয়েছি (মোট টাকা, মোট স্টক + বিক্রি)
• dynamic SQL-এ কোনো মান লেখায় জোড়া হয়নি; SECURITY DEFINER হলে search_path আর REVOKE আছে
• কোডটি একটি migration ফাইলে, review-সহ, git-এ আছে