mysql creating stored procedure

simple

DELIMITER //
CREATE PROCEDURE GetAllProducts()
  BEGIN
  SELECT *  FROM products;
  END //
DELIMITER ;

CALL GetAllProducts();

http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx

adding argument

DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
        IN orderStatus VARCHAR(25),
        OUT total INT)
    BEGIN
        SELECT count(orderNumber)
        INTO total
        FROM orders
        WHERE status = orderStatus;
    END$$
 DELIMITER ;

CALL CountOrderByStatus('in  process',@total);
SELECT @total AS  total_in_process;  

DROP PROCEDURE UpdateSection;

A variable with the ‘@’ at the beginning is session variable. It exists until the session end.

http://www.mysqltutorial.org/stored-procedures-parameters.aspx

 

Calling from php :

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `quadv2_dev`.`testlist`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
    
    SET @Qry ="SELECT *  FROM Content";
    PREPARE st FROM @Qry;
    EXECUTE st;
    DEALLOCATE PREPARE st;

    END$$

DELIMITER ;

 

php code :

 

$mysqli = new mysqli("localhost", "root", "", "quadv2_dev");

if (!$mysqli->multi_query("CALL testlist()")) {
    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        printf("---\n<pre/>");
        print_r($res->fetch_all());
        $res->free();
    } else {
        if ($mysqli->errno) {
            echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;
        }
    }
} while ($mysqli->more_results() && $mysqli->next_result());

 

 

test :

Advertisements

About rahul23134654

Hi, I am Rahul Meha , B.E. in (I.T.)
This entry was posted in Mysql. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s