1 / 50

MySQL Stored Procedure and User-Defined Function mysqltutorial/

MySQL 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.

dacia
Download Presentation

MySQL Stored Procedure and User-Defined Function mysqltutorial/

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MySQL Stored Procedure and User-Defined Functionhttp://www.mysqltutorial.org/ ISYS 475

  2. 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. • MySQL supports stored procedure since version 5.0 to allow MySQL more flexible and powerful.

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

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

  5. phpMyAdmin: Routines/Add routine interface

  6. 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 ""

  7. Run a procedure • With phpMyAdmin: • Routines/select the procedure and click execute • With the command prompt: CALL stored_procedure_name (param1, param2, ....);

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

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

  10. Executable Section • BEGIN Statements • END

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

  12. 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 MySQL 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

  13. 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;

  14. 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;

  15. SELECT … INTO • SELECT columns separated by commas • INTO variables separated by commas • FROM tablename • WHERE condition; • Ex: • SELECT cid, cname INTO custID, customername • FROM customer • WHERE cid = ‘c01’;

  16. 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');

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

  18. Logical Operators • Logical AND: • AND, && • UnitsInStock < ReorderLevel AND CategoryID=1 • UnitsInStock < ReorderLevel && CategoryID=1 • Negates value: • NOT, ! • Logical OR: • ||, OR • CategoryID=1 OR CategoryID=8 • CategoryID=1 || CategoryID=8

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

  20. CASE Statement CREATE PROCEDURE proc_CASE(IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END

  21. WHILE cond DO statement CREATE PROCEDURE proc_WHILE (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable2; END WHILE; END

  22. 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 MySQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace -- Programmer: John Smith

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

  24. Note 1: No need to surround the sidIN with quotation mark: select Salary into empSalary from salesreps where sid = sidIN; • 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 ;

  25. User-Defined Temporary Variables • User variables are written as @var_name. mysql> SET @t1=1, @t2=2, @t3:=4; mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3; +------+------+------+--------------------+ | @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 | +------+------+------+--------------------+ | 1 | 2 | 4 | 7 | +------+------+------+--------------------+

  26. 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('S1',0.1,@tax); Query OK, 1 row affected (0.00 sec) mysql> select @tax; +------+ | @tax | +------+ | 650 | +------+ 1 row in set (0.00 sec)

  27. First, check if the customer exist before adding a new order DELIMITER // CREATE PROCEDURE addOrder(oidIN char(5), cidIN char(5), sidIN char(5), odateIN date) LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A procedure' BEGIN DECLARE cidTemp char(5) default "x"; select cid into cidTemp from customers where cid = cidIN; IF cidTemp=cidIN THEN insert into orders values(oidIN,cidIN,sidIN,odateIN); END IF; END // mysql> call addOrder('O8','C12','S1','2013-06-10'); Query OK, 0 rows affected, 1 warning (0.00 sec) because C12 not exist!

  28. Using Routines/Execute

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

  30. 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 showCustomers()'); 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>"; } ?>

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

  32. Example Using PDO <?php $sid=$_POST["empID"]; $taxRate=$_POST["taxRate"]; $dsn = 'mysql:host=localhost;dbname=salesdb'; $username = 'root'; $password = ''; $db = new PDO($dsn, $username, $password); $db->query("SET @tax=''"); $myquery="call Caltax('" . $sid . "'," . $taxRate . ",@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: $taxRate </P>"; echo "<p>Tax: $row[0] </P>"; } ?>

  33. A few notes • 1. We need to create a MySQL 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, $taxRate,@tax);" ); • 3. We need to create a string for the Call statement. String input must be quoted: • $myquery="call Caltax('" . $sid . "'," . $taxRate . ",@tax)"; • $db->query( $myquery ); • 4. Then runs a Select command to read the output variables: • $rs = $db->query( 'SELECT @tax;');

  34. 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.

  35. 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 ratingChanged AFTER UPDATE ON customers FOR EACH ROW

  36. 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.

  37. Example: Customer Rating Change Log • Table name: CustomerLog • Fields: CID, Cname, OldRating,NewRating

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

  39. Example mysql> update customers set rating='c' where cid='C1'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from customerlog; +-----+-------+-----------+-----------+ | cid | Cname | OldRating | NewRating | +-----+-------+-----------+-----------+ | C1 | MYERS | A | c | +-----+-------+-----------+-----------+ 1 row in set (0.00 sec)

  40. 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 ;

  41. mysql> select * from products; +------+---------------+--------+--------+ | pid | pname | price | onhand | +------+---------------+--------+--------+ | P1 | COMPUTER | 850.00 | 50.0 | | P2 | SVGA MONITOR | 300.00 | 25.0 | | P3 | LASER PRINTER | 530.00 | 10.0 | | P4 | HARD DRIVE | 125.00 | 40.0 | | P5 | SERIAL MOUSE | 25.00 | 75.0 | | P6 | TAPE BACKUP | 225.00 | 15.0 | | P7 | TRACKBALL | 15.00 | 55.0 | +------+---------------+--------+--------+ Example mysql> insert into odetails values('O6','P2',5); Query OK, 1 row affected (0.03 sec) mysql> select * from products; +------+---------------+--------+--------+ | pid | pname | price | onhand | +------+---------------+--------+--------+ | P1 | COMPUTER | 850.00 | 50.0 | | P2 | SVGA MONITOR | 300.00 | 20.0 | | P3 | LASER PRINTER | 530.00 | 10.0 | | P4 | HARD DRIVE | 125.00 | 40.0 | | P5 | SERIAL MOUSE | 25.00 | 75.0 | | P6 | TAPE BACKUP | 225.00 | 15.0 | | P7 | TRACKBALL | 15.00 | 55.0 | +------+---------------+--------+--------+

  42. 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.

  43. Example DELIMITER // DROP FUNCTION IF EXISTS empTax; CREATE FUNCTION empTax(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 //

  44. 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)

  45. 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>"; } ?>

  46. 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.

  47. 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.

  48. 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;

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

  50. 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 continueFlag INTEGER DEFAULT 0; DECLARE useremail varchar(100) DEFAULT ""; DEClARE email_cursor CURSOR FOR SELECT email FROM users; DECLARE CONTINUE HANDLER FOR NOT FOUND SET continueFlag = 1; OPEN email_cursor; WHILE continueFlag = 0 DO FETCH email_cursor INTO useremail; IF continueFlag = 0 THEN SET emaillist = CONCAT(useremail,";",emaillist); END IF; END WHILE; CLOSE email_cursor; END // DELIMITER ;

More Related