函数与存储过程

CREATE OR REPLACE FUNCTION core.discount_price(orig numeric, rate numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
  IF rate < 0 OR rate > 1 THEN
    RAISE EXCEPTION '折扣率必须在 0~1 之间';
  END IF;
  RETURN round(orig * (1 - rate), 2);
END;
$$;
 
SELECT core.discount_price(7999, 0.1);   -- 7199.10

CREATE OR REPLACE FUNCTION core.products_by_category(cat text)
RETURNS TABLE (id bigint, name text, price numeric)
LANGUAGE sql
AS $$
  SELECT id, name, price FROM core.products WHERE category = cat;
$$;
 
SELECT * FROM core.products_by_category('phone');

CREATE OR REPLACE FUNCTION core.order_stats(IN uid bigint,
                                            OUT cnt int, OUT total numeric)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT COUNT(*), COALESCE(SUM(total_amount),0)
  INTO cnt, total
  FROM core.orders
  WHERE user_id = uid AND status = 'paid';
END;
$$;
 
SELECT * FROM core.order_stats(1);

CREATE OR REPLACE PROCEDURE core.batch_cancel_old_orders()
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE core.orders SET status = 'cancelled'
  WHERE status = 'pending' AND created_at < now() - interval '7 days';
  COMMIT;   -- 过程内可提交
END;
$$;
 
CALL core.batch_cancel_old_orders();

DO $$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT id FROM core.orders WHERE status='pending' LOOP
    -- 一些临时逻辑……
    NULL;
  END LOOP;
END;
$$;