2.2.4. Stored Procedures

Some parts of the business logic will be implemented by means of stored procedures.

Adding a new invoice

The procedure for adding a new invoice is quite simple:

  1. SET TERM ^;
  3. CREATE OR ALTER PROCEDURE SP_ADD_INVOICE (
  4.   INVOICE_ID INTEGER,
  5.   CUSTOMER_ID INTEGER,
  6.   INVOICE_DATE TIMESTAMP = CURRENT_TIMESTAMP)
  7. AS
  8. BEGIN
  9.   INSERT INTO INVOICE (
  10.     INVOICE_ID,
  11.     CUSTOMER_ID,
  12.     INVOICE_DATE,
  13.     TOTAL_SALE,
  14.     PAID
  15.   )
  16.   VALUES (
  17.     :INVOICE_ID,
  18.     :CUSTOMER_ID,
  19.     :INVOICE_DATE,
  20.     0,
  21.     0
  22.   );
  23. END
  24. ^
  26. SET TERM ;^
  28. COMMENT ON PROCEDURE SP_ADD_INVOICE IS
  29. 'Adding Invoice';
  31. COMMENT ON PARAMETER SP_ADD_INVOICE.INVOICE_ID IS
  32. 'Invoice number';
  34. COMMENT ON PARAMETER SP_ADD_INVOICE.CUSTOMER_ID IS
  35. 'Customer Id';
  37. COMMENT ON PARAMETER SP_ADD_INVOICE.INVOICE_DATE IS
  38. 'Date';
  40. GRANT INSERT ON INVOICE TO PROCEDURE SP_ADD_INVOICE;

Editing an invoice

The procedure for editing an invoice is a bit more complicated. We will include a rule to block further editing of an invoice once it is paid. We will create an exception that will be raised if an attempt is made to modify a paid invoice.

  1. CREATE EXCEPTION E_INVOICE_ALREADY_PAYED 'Change is impossible, invoice paid.';
  3. -- The stored procedure for editing an invoice:
  4. SET TERM ^;
  6. CREATE OR ALTER PROCEDURE SP_EDIT_INVOICE (
  7.   INVOICE_ID INTEGER,
  8.   CUSTOMER_ID INTEGER,
  9.   INVOICE_DATE TIMESTAMP)
  10. AS
  11. BEGIN
  12.   IF (EXISTS(SELECT *
  13.              FROM INVOICE
  14.              WHERE INVOICE_ID = :INVOICE_ID
  15.                AND PAID = 1)) THEN
  16.     EXCEPTION E_INVOICE_ALREADY_PAYED;
  18.   UPDATE INVOICE
  19.   SET CUSTOMER_ID = :CUSTOMER_ID,
  20.   INVOICE_DATE = :INVOICE_DATE
  21.   WHERE INVOICE_ID = :INVOICE_ID;
  22. END
  23. ^
  25. SET TERM ;^
  27. COMMENT ON PROCEDURE SP_EDIT_INVOICE IS
  28. 'Editing invoice';
  30. COMMENT ON PARAMETER SP_EDIT_INVOICE.INVOICE_ID IS
  31. 'Invoice number';
  33. COMMENT ON PARAMETER SP_EDIT_INVOICE.CUSTOMER_ID IS
  34. 'Customer Id';
  36. COMMENT ON PARAMETER SP_EDIT_INVOICE.INVOICE_DATE IS
  37. 'Date';
  39. GRANT SELECT,UPDATE ON INVOICE TO PROCEDURE SP_EDIT_INVOICE;

In Firebird 3.0, the USAGE privilege is required for exceptions, so we need to add the following line:

  1. GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_EDIT_INVOICE;

Deleting an invoice

The procedure SP_DELETE_INVOICE procedure checks whether the invoice is paid and raises an exception if it is:

  1. SET TERM ^ ;
  3. CREATE OR ALTER PROCEDURE SP_DELETE_INVOICE (
  4.   INVOICE_ID INTEGER)
  5. AS
  6. BEGIN
  7.   IF (EXISTS(SELECT * FROM INVOICE
  8.              WHERE INVOICE_ID = :INVOICE_ID
  9.                AND PAID = 1)) THEN
  10.     EXCEPTION E_INVOICE_ALREADY_PAYED;
  12.   DELETE FROM INVOICE WHERE INVOICE_ID = :INVOICE_ID;
  13. END
  14. ^
  16. SET TERM ;^
  18. COMMENT ON PROCEDURE SP_DELETE_INVOICE IS
  19. 'Deleting invoices';
  21. GRANT SELECT,DELETE ON INVOICE TO PROCEDURE SP_DELETE_INVOICE;

In Firebird 3.0, the USAGE privilege is required for exceptions, so we need to add the following line:

  1. GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_DELETE_INVOICE;

Paying an invoice

We will add one more procedure for paying an invoice:

  1. SET TERM ^;
  3. CREATE OR ALTER PROCEDURE SP_PAY_FOR_INVOICE (
  4. INVOICE_ID INTEGER)
  5. AS
  6. BEGIN
  7.   IF (EXISTS(SELECT *
  8.              FROM INVOICE
  9.              WHERE INVOICE_ID = :INVOICE_ID
  10.                AND PAID = 1)) THEN
  11.     EXCEPTION E_INVOICE_ALREADY_PAYED;
  12.   UPDATE INVOICE
  13.   SET PAID = 1
  14.   WHERE INVOICE_ID = :INVOICE_ID;
  15. END
  16. ^
  18. SET TERM ;^
  20. COMMENT ON PROCEDURE SP_PAY_FOR_INVOICE IS
  21. 'Payment of invoices';
  23. COMMENT ON PARAMETER SP_PAY_FOR_INVOICE.INVOICE_ID IS
  24. 'Invoice number';
  26. GRANT SELECT,UPDATE ON INVOICE TO PROCEDURE SP_PAY_FOR_INVOICE;

In Firebird 3.0, the USAGE privilege is required for exceptions, so we need to add the following line:

  1. GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_PAY_FOR_INVOICE;

Invoice Line Items

Procedures for managing invoice items will check whether the invoice is paid and block any attempt to alter the line items of paid invoices. They will also correct the invoice total according to the amount of the product sold and its price.

Adding line items

The procedure for adding a line item to an invoice:

  1. SET TERM ^;
  3. CREATE OR ALTER PROCEDURE SP_ADD_INVOICE_LINE (
  4.   INVOICE_ID INTEGER,
  5.   PRODUCT_ID INTEGER,
  6.   QUANTITY INTEGER)
  7. AS
  8.   DECLARE sale_price D_MONEY;
  9.   DECLARE paid D_BOOLEAN;
  10. BEGIN
  11.   SELECT
  12.     paid
  13.   FROM
  14.     invoice
  15.   WHERE
  16.     invoice_id = :invoice_id
  17.   INTO :paid;
  19.   -- It does not allow you to edit already paid invoice.
  20.   IF (paid = 1) THEN
  21.     EXCEPTION E_INVOICE_ALREADY_PAYED;
  23.   SELECT
  24.     price
  25.   FROM
  26.     product
  27.   WHERE
  28.     product_id = :product_id
  29.   INTO :sale_price;
  31. INSERT INTO invoice_line (
  32.   invoice_line_id,
  33.   invoice_id,
  34.   product_id,
  35.   quantity,
  36.   sale_price)
  37. VALUES (
  38.   NEXT VALUE FOR gen_invoice_line_id,
  39.   :invoice_id,
  40.   :product_id,
  41.   :quantity,
  42.   :sale_price);
  44.   -- Increase the amount of the account.
  45.   UPDATE invoice
  46.   SET total_sale = COALESCE(total_sale, 0) + :sale_price * :quantity
  47.   WHERE invoice_id = :invoice_id;
  49. END
  50. ^
  51. SET TERM ;^
  53. COMMENT ON PROCEDURE SP_ADD_INVOICE_LINE IS
  54. 'Adding line invoices';
  56. COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.INVOICE_ID IS
  57. 'Invoice number';
  59. COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.PRODUCT_ID IS
  60. 'Product Id';
  62. COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.QUANTITY IS
  63. 'Quantity';
  65. GRANT SELECT, UPDATE ON INVOICE TO PROCEDURE SP_ADD_INVOICE_LINE;
  66. GRANT SELECT ON PRODUCT TO PROCEDURE SP_ADD_INVOICE_LINE;
  67. GRANT INSERT ON INVOICE_LINE TO PROCEDURE SP_ADD_INVOICE_LINE;
  68. -- only Firebird 3.0 and above
  69. GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_ADD_INVOICE_LINE;
  70. GRANT USAGE ON SEQUENCE GEN_INVOICE_LINE_ID TO PROCEDURE SP_ADD_INVOICE_LINE;

Editing line items

The procedure for editing an invoice line item:

  1. SET TERM ^;
  3. CREATE OR ALTER PROCEDURE SP_EDIT_INVOICE_LINE (
  4.   INVOICE_LINE_ID INTEGER,
  5.   QUANTITY INTEGER)
  6. AS
  7.   DECLARE invoice_id INT;
  8.   DECLARE price D_MONEY;
  9.   DECLARE paid D_BOOLEAN;
  10. BEGIN
  11.   SELECT
  12.     product.price,
  13.     invoice.invoice_id,
  14.     invoice.paid
  15.   FROM
  16.     invoice_line
  17.   JOIN invoice ON invoice.invoice_id = invoice_line.invoice_id
  18.   JOIN product ON product.product_id = invoice_line.product_id
  19.   WHERE
  20.     invoice_line.invoice_line_id = :invoice_line_id
  21.   INTO
  22.     :price,
  23.     :invoice_id,
  24.     :paid;
  26.   -- It does not allow you to edit an already paid invoice.
  27.   IF (paid = 1) THEN
  28.     EXCEPTION E_INVOICE_ALREADY_PAYED;
  30.   -- Update price and quantity.
  31.   UPDATE invoice_line
  32.   SET sale_price = :price,
  33.       quantity = :quantity
  34.   WHERE invoice_line_id = :invoice_line_id;
  35.   -- Now update the amount of the account.
  36.   MERGE INTO invoice
  37.   USING (
  38.     SELECT
  39.       invoice_id,
  40.       SUM(sale_price * quantity) AS total_sale
  41.     FROM invoice_line
  42.     WHERE invoice_id = :invoice_id
  43.     GROUP BY invoice_id) L
  44.   ON invoice.invoice_id = L.invoice_id
  45.   WHEN MATCHED THEN
  46.     UPDATE SET total_sale = L.total_sale;
  47. END
  48. ^
  50. SET TERM ;^
  52. COMMENT ON PROCEDURE SP_EDIT_INVOICE_LINE IS
  53. 'Editing invoice line';
  55. COMMENT ON PARAMETER SP_EDIT_INVOICE_LINE.INVOICE_LINE_ID IS
  56. 'Invoice line id';
  58. COMMENT ON PARAMETER SP_EDIT_INVOICE_LINE.QUANTITY IS
  59. 'Quantity';
  61. GRANT SELECT,UPDATE ON INVOICE_LINE TO PROCEDURE SP_EDIT_INVOICE_LINE;
  62. GRANT SELECT,UPDATE ON INVOICE TO PROCEDURE SP_EDIT_INVOICE_LINE;
  63. GRANT SELECT ON PRODUCT TO PROCEDURE SP_EDIT_INVOICE_LINE;
  64. -- only Firebird 3.0 and above
  65. GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_EDIT_INVOICE_LINE;

Deleting line items

The procedure for deleting an invoice line item from an invoice:

  1. SET TERM ^;
  3. CREATE OR ALTER PROCEDURE SP_DELETE_INVOICE_LINE (
  4.   INVOICE_LINE_ID INTEGER)
  5. AS
  6.   DECLARE invoice_id INT;
  7.   DECLARE price D_MONEY;
  8.   DECLARE quantity INT;
  9. BEGIN
  10.   IF (EXISTS(SELECT *
  11.              FROM invoice_line
  12.              JOIN invoice ON invoice.invoice_id = invoice_line.invoice_id
  13.              WHERE invoice.paid = 1
  14.                AND invoice_line.invoice_line_id = :invoice_line_id)) THEN
  15.     EXCEPTION E_INVOICE_ALREADY_PAYED;
  17.   DELETE FROM invoice_line
  18.   WHERE invoice_line.invoice_line_id = :invoice_line_id
  19.   RETURNING invoice_id, quantity, sale_price
  20.   INTO invoice_id, quantity, price;
  22.   -- Reduce the amount of the account.
  23.   UPDATE invoice
  24.   SET total_sale = total_sale - :quantity * :price
  25.   WHERE invoice_id = :invoice_id;
  26. END
  27. ^
  29. SET TERM ;^
  31. COMMENT ON PROCEDURE SP_DELETE_INVOICE_LINE IS
  32. 'Deleting invoice item';
  34. COMMENT ON PARAMETER SP_DELETE_INVOICE_LINE.INVOICE_LINE_ID IS
  35. 'Code invoice item';
  36. Privileges for Procedures
  37. GRANT SELECT,DELETE ON INVOICE_LINE TO PROCEDURE SP_DELETE_INVOICE_LINE;
  38. GRANT SELECT,UPDATE ON INVOICE TO PROCEDURE SP_DELETE_INVOICE_LINE;
  39. -- only Firebird 3.0 and above
  40. GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_DELETE_INVOICE_LINE;