Subscribe to mysql

mysql

MySQL 5 Stored Procedure Examples with PHP

Submitted by mikehostetler on Thu, 04/26/2007 - 09:25

I've received a lot of interest from my previous post about MySQL Stored Procedures and PHP 5. So, I decided to set up an example archive of the stored procedures I've written for a Inventory Management project I'm currently working on. I've got 13 examples up so far, with more to come. Check it out.

Inventory Allocation Count Total Served Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 09:06

After an Inventory Allocation has been created, Partners need to be chosen. Thus, we cannot fill in the value of Total Served in the Inventory Allocation table until we've chosen all our partners. The value is calculated and stored because it is used frequently in the distribution algorithm.

<?php
--
-- Inventory Allocation Count Total Served Stored Procedure
--

USE <

code>cics_app</code>;

DROP PROCEDURE IF EXISTS Ia_CountTotalServed;

DELIMITER |
CREATE PROCEDURE Ia_CountTotalServed (IN ia_id INT)
BEGIN
DECLARE total_served INT;

SELECT
        SUM(<code>net_served</code>) INTO total_served
    FROM
      <code>ia_partner</code>
   WHERE
      <code>ia_partner</code>.<code>ia_id</code> = ia_id;

SELECT total_served;
END;
|
DELIMITER ;
?>
Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure

Inventory Allocation Sub-System

Submitted by mikehostetler on Thu, 04/26/2007 - 09:04

The Inventory Allocation functionality in my Inventory Management system is designed to distribute units from inventory to a number of partners. There's way to many business rules associated with this piece to describe here, but I wanted to at least give a high-level picture.

<?php

-- Table structure for table ia

CREATE TABLE ia (
ia_id int(10) unsigned NOT NULL auto_increment,
date date NOT NULL default '0000-00-00',
closed tinyint(1) NOT NULL default '0',
locked tinyint(1) NOT NULL default '0',
total_weight int(10) default '0',
total_served int(10) default '0',
PRIMARY KEY (ia_id),
KEY IDX_ia_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

-- Table structure for table ia_unit

CREATE TABLE ia_unit (
ia_unit_id int(10) unsigned NOT NULL auto_increment,
ia_id int(10) unsigned NOT NULL default '0',
category_id int(10) unsigned NOT NULL default '0',

Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure

Transaction Max Batch Number Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:57

This is somewhat simplistic. The reason I implemented a simple MAX() query is to move towards centralizing all of my custom SQL into stored procedures.

<?php
--
-- Transaction Maximum Batch Number Stored Procedure
--

USE <

Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure

Transaction Unit Balance Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:54

<?php
--
-- Transaction Unit Balance Stored Procedure
--

USE <

code>cics_app</code>;

DROP PROCEDURE IF EXISTS Transaction_UnitBalance;

DELIMITER |
CREATE PROCEDURE Transaction_UnitBalance (IN unit_number INT, IN unit_account_id INT)
BEGIN
   DECLARE unit_balance, unit_credit, unit_debit INT;

   

SELECT
        SUM(t.amount) INTO unit_credit
FROM
      transaction AS t
   WHERE
      t.unit_number = unit_number
      AND
      t.credit_account_id = unit_account_id;

   

SELECT
        SUM(t.amount) INTO unit_debit
  FROM
      transaction AS t
   WHERE
      t.unit_number = unit_number
      AND
      t.debit_account_id = unit_account_id;

SET unit_debit = IFNULL(unit_debit,0);
SET unit_credit = IFNULL(unit_credit,0);

 

SET unit_balance = unit_debit - unit_credit;

 

SELECT unit_balance;
END;
|
DELIMITER ;
?>
Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure

Transaction Open Units Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:53

This function was easy to write after writing the Transaction Open Batches Stored Procedure.

<?php
--
-- Transaction Open Units Stored Procedure
--

USE <

code>cics_app</code>;

DROP PROCEDURE IF EXISTS Transaction_OpenUnits;

DELIMITER |
CREATE PROCEDURE Transaction_OpenUnits (IN unit_account_id INT)
BEGIN
SELECT
        <code>transaction</code>.<code>unit_number</code> AS unit_no,
        <code>category</code>.<code>category_id</code> AS category_id,
       <code>category</code>.<code>calc_name</code> AS category,

      

IFNULL(
            IFNULL((
               SELECT SUM(<code>transaction</code>.<code>amount</code>) FROM <code>transaction</code>
              WHERE <code>unit_number</code> = <code>unit_no</code> AND <code>credit_account_id</code> = unit_account_id
          ),0)
       -
          IFNULL((
               SELECT SUM(<code>transaction</code>.<code>amount</code>) FROM <code>transaction</code>
              WHERE <code>unit_number</code> = <code>unit_no</code> AND <code>debit_account_id</code> = unit_account_id
           ),0)
       ,0) AS unit_balance
    FROM
       <code>transaction</code>

LEFT JOIN <code>category</code> ON <code>transaction</code>.<code>category_id</code> = <code>category</code>.<code>category_id</code>

   

WHERE
     <code>transaction</code>.<code>unit_number</code> > 0
GROUP BY
       <code>transaction</code>.<code>unit_number</code>
    HAVING
     unit_balance > 0;
END
|

DELIMITER ;
?>
Taxonomy upgrade extras: 
mysql
stored procedures
cics
mysqli

Transaction Open Batches Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:51

This was a fun procedure to write. I originally found the IFNULL function for my Account Balance Store Procedure. This query, when directly implemented in PHP was MUCH more complicated because of the possibilities of returning a NULL value. Now, it's all done in one statement.

<?php

-- Transaction Open Batches Stored Procedure

USE cics_app;

DROP PROCEDURE IF EXISTS Transaction_OpenBatches;

DELIMITER |
CREATE PROCEDURE Transaction_OpenBatches (IN batch_account_id INT)
BEGIN
SELECT
transaction.batch_number AS batch_no,
IFNULL(
IFNULL((
SELECT SUM(transaction.amount) FROM transaction
WHERE batch_number = batch_no AND credit_account_id = batch_account_id
),0)
-
IFNULL((
SELECT SUM(transaction.amount) FROM transaction

Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure

Transactioon Max Unit Number Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:48

<?php
--
-- Transaction Maximum Unit Number Stored Procedure
--

USE <

code>cics_app</code>;

Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure

Transaction Count Batch Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:46

<?php
--
-- Transaction Count Batch Number Stored Procedure
--

USE <

code>cics_app</code>;

Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure

Transaction Batch Balance Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:43

This is a procedure that simply gets the balance of a batch. A batch is a business level object that is reflected at this level. This procedure is a direct descendant of the Account Balance Stored Procedure.

<?php

-- Transaction Batch Balance Stored Procedure

USE cics_app;

DROP PROCEDURE IF EXISTS Transaction_BatchBalance;

DELIMITER |
CREATE PROCEDURE Transaction_BatchBalance (IN batch_number INT, IN batch_account_id INT)
BEGIN
DECLARE batch_balance, batch_credit, batch_debit INT;

SELECT 
    SUM(t.amount) INTO batch_credit
FROM 
    transaction AS t
WHERE
    t.batch_number = batch_number
  AND
    t.credit_account_id = batch_account_id;

SELECT 
    SUM(t.amount) INTO batch_debit
FROM 
    transaction AS t
WHERE
    t.batch_number = batch_number
  AND
    t.debit_account_id = batch_account_id;


SET batch_debit = IFNULL(batch_debit,0);
SET batch_credit = IFNULL(batch_credit,0);
Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure

Pages