My SQL Stored Procedure and UserDefined Function http

My. SQL Stored Procedure and User-Defined Function http: //www. mysqltutorial. org/ ISYS 475

Stored Procedure • A stored procedure is a program with SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure. • My. SQL supports stored procedure since version 5. 0 to allow My. SQL more flexible and powerful.

Three Ways to Create A Procedure • 1. Save the procedure commands in a text file. • 2. Use the php. My. Admin utility to enter commands – Routine/Add routine • 3. Enter the commands using the My. SQL command prompt.

Example of a command file DELIMITER // CREATE PROCEDURE Hello() LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A procedure' BEGIN SELECT 'Hello World !'; END //

php. My. Admin: Routines/Add routine interface

Optional characteristics • Type: Procedure/Function • Language : the default value is SQL. • Deterministic : If the procedure always returns the same results, given the same input. The default value is NOT DETERMINISTIC. • SQL Security : At call time, check privileges of the user. INVOKER is the user who calls the procedure. DEFINER is the creator of the procedure. The default value is DEFINER. • Comment : For documentation purposes; the default value is ""

Run a procedure • With php. My. Admin: – Routines/select the procedure and click execute • With the command prompt: CALL stored_procedure_name (param 1, param 2, . . );

CREATE PROCEDURE Proc. Name() • Stored procedure names are case insensitive • A procedure may have parameters

Define parameters within a stored procedure • Parameter list is empty – CREATE PROCEDURE proc 1 () : • Define input parameter with key word IN: – CREATE PROCEDURE proc 1 (IN varname DATATYPE) – The word IN is optional because parameters are IN (input) by default. • Define output parameter with OUT: – CREATE PROCEDURE proc 1 (OUT varname DATATYPE) • A procedure may have input and output paramters: – CREATE PROCEDURE proc 1 (INOUT varname DATATYPE)

Executable Section • BEGIN Statements • END

Examples of parameters CREATE PROCEDURE proc_IN (IN var 1 INT) BEGIN SELECT var 1 + 2 AS result; END CREATE PROCEDURE proc_OUT(OUT var 1 VARCHAR(100)) BEGIN SET var 1 = 'This is a test'; END CREATE PROCEDURE proc_INOUT (IN var 1 INT, OUT var 2 INT) BEGIN SET var 2 = var 1 * 2; END

Variable Declaration • DECLARE variable_name datatype(size) DEFAULT default_value; • Variable naming rules: Identifiers can consist of any alphanumeric characters, plus the characters '_' and '$'. Identifiers can start with any character that is legal in an identifier, including a digit. However, an identifier cannot consist entirely of digits. • Data types: A variable can have any My. SQL data types. For example: – Character: CHAR(n), VARCHAR(n) – Number: INT, SMALLINT, DECIMAL(i, j), DOUBLE – Date: DATE, TIME, DATETIME – BOOLEAN • http: //www. mysqltutorial. org/mysql-data-types. aspx

Examples DECLARE x, y INT DEFAULT 0; DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE ename VARCHAR(50); DECLARE no_more_rows BOOLEAN; SET no_more_rows = TRUE;

Assigning variables • Using the SET command: DECLARE total_count INT DEFAULT 0; SET total_count = 10; Using the SELECT INTO command: DECLARE total_products INT DEFAULT 0; SELECT COUNT(*) INTO total_products FROM products;

SELECT … INTO • • • SELECT columns separated by commas INTO variables separated by commas FROM tablename WHERE condition; Ex: – SELECT cid, cname INTO cust. ID, customername – FROM customer – WHERE cid = ‘c 01’;

Arithmetic and string operators • Arithmetic operators: +, -, *, / • Modulo operator: – % or mod • Other math calculations use math functions: – Pow(x, y) • Concatenation uses CONCAT function: – SELECT CONCAT('New ', 'York ', 'City');

My. SQL Comparison Operators • • EQUAL(=) LESS THAN(<) LESS THAN OR EQUAL(<=) GREATER THAN(>) GREATER THAN OR EQUAL(>=) NOT EQUAL(<>, !=)

Logical Operators • Logical AND: – AND, && – Units. In. Stock < Reorder. Level AND Category. ID=1 – Units. In. Stock < Reorder. Level && Category. ID=1 • Negates value: – NOT, ! • Logical OR: – ||, OR – Category. ID=1 OR Category. ID=8 – Category. ID=1 || Category. ID=8

IF statement: The IF statement can have THEN, ELSE, and ELSEIF clauses, and it is terminated with END IF. IF variable 1 = 0 THEN SELECT variable 1; END IF; IF param 1 = 0 THEN SELECT 'Parameter value = 0'; ELSE SELECT 'Parameter value <> 0'; END IF;

CASE Statement CREATE PROCEDURE proc_CASE(IN param 1 INT) BEGIN DECLARE variable 1 INT; SET variable 1 = param 1 + 1; CASE variable 1 WHEN 0 THEN INSERT INTO table 1 VALUES (param 1); WHEN 1 THEN INSERT INTO table 1 VALUES (variable 1); ELSE INSERT INTO table 1 VALUES (99); END CASE; END

WHILE cond DO statement CREATE PROCEDURE proc_WHILE (IN param 1 INT) BEGIN DECLARE variable 1, variable 2 INT; SET variable 1 = 0; WHILE variable 1 < param 1 DO INSERT INTO table 1 VALUES (param 1); SELECT COUNT(*) INTO variable 2 FROM table 1; SET variable 1 = variable 2; END WHILE; END

Comment Syntax • From a /* sequence to the following */ sequence. • From a “#” character to the end of the line. • From a “-- ” sequence to the end of the line. In My. SQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace -- Programmer: John Smith

A Procedure to compute tax that takes sid. IN and tax. Rate as inputs and return tax. Out as output DELIMITER // CREATE PROCEDURE Caltax(sid. IN char(5), tax. Rate double, out tax. Out double) LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A procedure' BEGIN DECLARE tax DOUBLE; DECLARE emp. Salary DOUBLE; select Salary into emp. Salary from salesreps where sid = sid. IN; set tax. Out=tax. Rate*emp. Salary; END //

• Note 1: No need to surround the sid. IN with quotation mark: select Salary into emp. Salary from salesreps where sid = sid. IN; • Note 2: The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement. It can be restored to “; ” mysql>delimiter ;

User-Defined Temporary Variables • User variables are written as @var_name. mysql> SET @t 1=1, @t 2=2, @t 3: =4; mysql> SELECT @t 1, @t 2, @t 3, @t 4 : = @t 1+@t 2+@t 3; +------+-------------+ | @t 1 | @t 2 | @t 3 | @t 4 : = @t 1+@t 2+@t 3 | +------+-------------+ | 1| 2| 4| 7| +------+-------------+

Example of running the procedure from the command prompt mysql> delimiter ; mysql> set @tax=0; Query OK, 0 rows affected (0. 00 sec) mysql> call caltax('S 1', 0. 1, @tax); Query OK, 1 row affected (0. 00 sec) mysql> select @tax; +------+ | @tax | +------+ | 650 | +------+ 1 row in set (0. 00 sec)

First, check if the customer exist before adding a new order DELIMITER // CREATE PROCEDURE add. Order(oid. IN char(5), cid. IN char(5), sid. IN char(5), odate. IN date) LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A procedure' BEGIN DECLARE cid. Temp char(5) default "x"; select cid into cid. Temp from customers where cid = cid. IN; IF cid. Temp=cid. IN THEN insert into orders values(oid. IN, cid. IN, sid. IN, odate. IN); END IF; END // mysql> call add. Order('O 8', 'C 12', 'S 1', '2013 -06 -10'); Query OK, 0 rows affected, 1 warning (0. 00 sec) because C 12 not exist!

Using Routines/Execute

Example: Procedure show. Customers DELIMITER // DROP PROCEDURE IF EXISTS show. Customers; CREATE PROCEDURE show. Customers () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A procedure' BEGIN Select * from Customers; END // DELIMITER ;

Using PDO Calling a stored procedure with a SQL Select statement: select * from customers <? php $dsn = 'mysql: host=localhost; dbname=salesdb'; $username = 'root'; $password = ''; $db = new PDO($dsn, $username, $password); $customers= $db->query('CALL show. Customers()'); echo "<table border=1><tr>". "<th>CID</th>". "<th>CName</th>". "<th>City</th>". "<th>Rating</th></tr>"; foreach ($customers as $customer){ $cid=$customer["cid"]; //field name is case sensitive $Cname=$customer["cname"]; $City=$customer["city"]; $Rating=$customer["rating"]; echo "<tr><td>$cid</td>". "<td>$Cname</td>". "<td>$City</td>". "<td>$Rating</td></tr>"; } ? >

Calling a procedure with OUT parameter • Must use My. SQL temporary @variable to receive the output value. • Because a stored procedure does not return to PHP anything, it returns the value into the My. SQL variable (@return) (scope is in My. SQL), so you need to query this variable in a separate call.

Example Using PDO <? php $sid=$_POST["emp. ID"]; $tax. Rate=$_POST["tax. Rate"]; $dsn = 'mysql: host=localhost; dbname=salesdb'; $username = 'root'; $password = ''; $db = new PDO($dsn, $username, $password); $db->query("SET @tax=''"); $myquery="call Caltax('". $sid. "', ". $tax. Rate. ", @tax)"; echo $myquery; $db->query( $myquery ); $rs = $db->query( 'SELECT @tax; '); // $row = $rs->fetch(PDO: : FETCH_NUM); foreach ($rs as $row){ echo "<p>SID: $sid </P>"; echo "<p>Rate: $tax. Rate </P>"; echo "<p>Tax: $row[0] </P>"; } ? >

A few notes • 1. We need to create a My. SQL variable using the SET command: $db->query("SET @tax=''"); • 2. Passing PHP variables as inputs will not work. This statement does not work: – $db->query( "call Caltax($sid, $tax. Rate, @tax); " ); • 3. We need to create a string for the Call statement. String input must be quoted: – $myquery="call Caltax('". $sid. "', ". $tax. Rate. ", @tax)"; – $db->query( $myquery ); • 4. Then runs a Select command to read the output variables: – $rs = $db->query( 'SELECT @tax; ');

Triggers • A trigger is a program stored in the database and is called automatically when a triggering event occurs. • It is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update. • A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement.

CREATE TRIGGER Syntax CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body • trigger_time: It can be BEFORE or AFTER • trigger_event: Insert, Delete, Update • Example: CREATE TRIGGER rating. Changed AFTER UPDATE ON customers FOR EACH ROW

OLD and NEW • You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD. col_name refers to a column of an existing row before it is updated or deleted. NEW. col_name refers to the column of a new row to be inserted or an existing row after it is updated.

Example: Customer Rating Change Log • Table name: Customer. Log • Fields: CID, Cname, Old. Rating, New. Rating

Demo : New and : Old delimiter // DROP TRIGGER IF EXISTS rating. Changed; CREATE TRIGGER rating. Changed AFTER UPDATE ON customers FOR EACH ROW BEGIN insert into customerlog values(old. cid, old. cname, old. rating, new. rating); END // delimiter ;

Example mysql> update customers set rating='c' where cid='C 1'; Query OK, 1 row affected (0. 01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from customerlog; +-------+-----------+ | cid | Cname | Old. Rating | New. Rating | +-------+-----------+ | C 1 | MYERS | A |c | +-------+-----------+ 1 row in set (0. 00 sec)

Updating the onhand quantity after a new detail line is added: delimiter // DROP TRIGGER IF EXISTS adddetail; CREATE TRIGGER adddetail AFTER INSERT ON odetails FOR EACH ROW BEGIN DECLARE stocks decimal(5, 1); select onhand into stocks from products where pid=new. pid; update products set onhand=onhand - new. qty where pid=new. pid; END // delimiter ;

Example mysql> select * from products; +-----------+--------+ | pid | pname | price | onhand | +-----------+--------+ | P 1 | COMPUTER | 850. 00 | 50. 0 | | P 2 | SVGA MONITOR | 300. 00 | 25. 0 | | P 3 | LASER PRINTER | 530. 00 | 10. 0 | | P 4 | HARD DRIVE | 125. 00 | 40. 0 | | P 5 | SERIAL MOUSE | 25. 00 | 75. 0 | | P 6 | TAPE BACKUP | 225. 00 | 15. 0 | | P 7 | TRACKBALL | 15. 00 | 55. 0 | +-----------+--------+ mysql> insert into odetails values('O 6', 'P 2', 5); Query OK, 1 row affected (0. 03 sec) mysql> select * from products; +-----------+--------+ | pid | pname | price | onhand | +-----------+--------+ | P 1 | COMPUTER | 850. 00 | 50. 0 | | P 2 | SVGA MONITOR | 300. 00 | 20. 0 | | P 3 | LASER PRINTER | 530. 00 | 10. 0 | | P 4 | HARD DRIVE | 125. 00 | 40. 0 | | P 5 | SERIAL MOUSE | 25. 00 | 75. 0 | | P 6 | TAPE BACKUP | 225. 00 | 15. 0 | | P 7 | TRACKBALL | 15. 00 | 55. 0 | +-----------+--------+

User Defined Functions • Stored functions differ from stored procedures in that stored functions actually return a value. • Stored functions have only input parameters (if any parameters at all), so the IN , OUT , and INOUT keywords aren’t used. • Stored functions have no output parameters; instead, you use a RETURN statement to return a value whose type is determined by the RETURNS type statement, which precedes the body of the function.

Example DELIMITER // DROP FUNCTION IF EXISTS emp. Tax; CREATE FUNCTION emp. Tax(Salary Decimal(10, 2)) RETURNS Decimal(10, 2) BEGIN Declare tax decimal(10, 2); if salary < 3000. 00 then set tax=salary*0. 1; elseif Salary <5000. 00 then set tax=Salary*0. 2; else set tax=Salary*0. 3; end if; return tax; END //

Using the User-defined Function with SQL mysql> delimiter ; mysql> select sname, emptax(Salary) as tax from salesreps; +---------+ | sname | tax | +---------+ | PETER | 1950. 00 | | PAUL | 2160. 00 | | MARY | 2250. 00 | +---------+ 3 rows in set (0. 00 sec)

Example of Using a User-Defined Function <? php $db = new mysqli('localhost', 'root', 'salesdb'); $rs=$db->query( 'select sname, emptax(Salary) as tax from salesreps' ); echo "<table border=1><tr>". "<th>Sname</th>". "<th>Tax</th></tr>"; foreach ($rs as $row){ $sname=$row["sname"]; //field name is case sensitive $tax=$row["tax"]; echo "<tr><td>$sname</td>". "<td>$tax</td></tr>"; } ? >

Cursors • A cursor is a pointer to a set of records returned by a SQL statement. It enables you to take a set of records and deal with it on a row-by-row basis.

Cursor has three important properties • The cursor will not reflect changes in its source tables. • Read Only : Cursors are not updatable. • Not Scrollable : Cursors can be traversed only in one direction, forward, and you can't skip records from fetching.

Defining and Using Cursors • Declare cursor: – DECLARE cursor-name CURSOR FOR SELECT. . . ; • DECLARE CONTINUE HANDLER FOR NOT FOUND: Specify what to do when no more records found – DECLARE b INT; – DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; Open cursor: OPEN cursor-name; Fetch data into variables: FETCH cursor-name INTO variable [, variable]; CLOSE cursor: CLOSE cursor-name;

Cursor Example DELIMITER // DROP Procedure IF EXISTS male. Sum; CREATE Procedure male. Sum(OUT sum. Salary Decimal(10, 2)) BEGIN DECLARE Sal, sum. Sal decimal(10, 2); DECLARE continue. Flag int default 0; DECLARE male. Cursor CURSOR FOR SELECT Salary FROM salesreps where sex='M'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET continue. Flag = 1; OPEN male. Cursor; SET Sal = 0; SET sum. Sal= 0; WHILE continue. Flag = 0 DO FETCH male. Cursor INTO Sal; IF continue. Flag = 0 THEN SET sum. Sal = sum. Sal+Sal; END IF; END WHILE; CLOSE male. Cursor; SET sum. Salary=sum. Sal; END //

A procedure to create email list using cursor DELIMITER // DROP PROCEDURE IF EXISTS emailgroup; CREATE PROCEDURE emailgroup (INOUT emaillist varchar(4000)) LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A procedure' BEGIN DECLARE continue. Flag INTEGER DEFAULT 0; DECLARE useremail varchar(100) DEFAULT ""; DECl. ARE email_cursor CURSOR FOR SELECT email FROM users; DECLARE CONTINUE HANDLER FOR NOT FOUND SET continue. Flag = 1; OPEN email_cursor; WHILE continue. Flag = 0 DO FETCH email_cursor INTO useremail; IF continue. Flag = 0 THEN SET emaillist = CONCAT(useremail, "; ", emaillist); END IF; END WHILE; CLOSE email_cursor; END // DELIMITER ;