معاملات الإجراء: IN و OUT و INOUT

CREATE PROCEDURE customer_stats(  IN  cid    int,  OUT n_orders int,  OUT spent    numeric)LANGUAGE plpgsqlAS $$BEGIN  SELECT count(DISTINCT o.id), coalesce(sum(i.qty * i.unit_price), 0)  INTO n_orders, spent  FROM orders o LEFT JOIN order_items i ON i.order_id = o.id  WHERE o.customer_id = cid;END;$$; CALL customer_stats(1, NULL, NULL);

CREATE PROCEDURE customer_stats(  IN  cid    int,  OUT n_orders int,  OUT spent    numeric)LANGUAGE plpgsqlAS $$BEGIN  SELECT count(DISTINCT o.id), coalesce(sum(i.qty * i.unit_price), 0)  INTO n_orders, spent  FROM orders o LEFT JOIN order_items i ON i.order_id = o.id  WHERE o.customer_id = cid;END;$$; DO $$DECLARE  n int;  s numeric;BEGIN  CALL customer_stats(3, n, s);  RAISE NOTICE 'نورة: % طلبات بمجموع % ريال', n, s;END $$;

CREATE PROCEDURE apply_discount(INOUT amount numeric, IN pct numeric DEFAULT 10)LANGUAGE plpgsqlAS $$BEGIN  amount := round(amount * (1 - pct / 100), 2);END;$$; DO $$DECLARE  bill numeric := 500;BEGIN  CALL apply_discount(bill);              -- pct أخذ القيمة الافتراضية 10  RAISE NOTICE 'بعد الخصم الافتراضي: %', bill;  CALL apply_discount(bill, pct => 20);   -- تسمية المعامل عند الاستدعاء  RAISE NOTICE 'بعد خصم 20%% إضافي: %', bill;END $$;

CREATE FUNCTION price_range(cat int, OUT low numeric, OUT high numeric)LANGUAGE sqlAS $$ SELECT min(price), max(price) FROM products WHERE category_id = cat $$; SELECT c.name, r.low, r.highFROM categories c, price_range(c.id) rORDER BY c.id;