Downloaded 74 times
![Declare Handler • The DECLARE ... HANDLER statement specifies a handler that deals with one or more conditions. If one of these conditions occurs, the specified statement executes. • DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... Statement – handler_action: – CONTINUE | EXIT | UNDO – condition_value: mysql_error_code – SQLWARNING | NOT FOUND | SQLEXCEPTION](https://image.slidesharecdn.com/introductiontomysqlpart3-140329022314-phpapp01/75/Introduction-to-mysql-part-3-22-2048.jpg)
Introduction to mysql part 3
The document provides information about stored procedures in databases: - A stored procedure is a way to encapsulate repetitive tasks like queries into reusable code blocks stored in the database. - Stored procedures offer advantages like precompiled execution for improved performance, reduced network traffic, code reuse, and enhanced security. - The example shows how to create a stored procedure using delimiters to change parsing behavior and pass parameters to a procedure. Cursors allow fetching multiple rows from a result set into variables.
Introduction to mysql part 3
- 1. Recall • When touse group by? • Difference between ‘having’ and ‘where’? • Difference between scalar and aggregate functions? • What are the different types of joins?
- 2. Introduction To DBMSand MySQL Stored Procedures Week 6 – Day1
- 3. Stored Procedures • Astored procedure is a method to encapsulate repetitive tasks. • A stored Procedure is actually stored in database data dictionary
- 4. Without Stored Procedure Employee.jsp ...................... Select* from tbl_employee ................ Report.php .......................... Select * from tbl_employee ................. ..................... Select * from tbl_employee viewDetails.php .......................... ......................... Select * from tbl_employee ............... Database
- 5. Stored Procedure Employee.php .......................... .......................... Call getName(); ................ Report.php .......................... ......................... .......................... CallgetName(); viewDetails.php .......................... ......................... Call getName(); ............... getName() Begin Select * from tbl_employee End Database
- 6. Advantages • Precompiled Execution •Mysql compiles each stored procedure once and then reutilizes the execution plan. This result in tremendous performance boosts when stored procedures are called repeatedly • Reduced client server traffic • If network traffic is concerned you will be happy to learn that stored procedures can reduce long sql queries to a single line that is transmitted between application program and database server.
- 7. Advantages • Efficient reuseof code and programming abstraction • Stored procedures can be used by multiple programs and users. If you utilize them in a planned manner, you’ll find the development cycle takes less time. • Enhanced Security Control • You can grant users permission to execute stored procedure independently of underlying table permissions
- 8. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName(IN id int) BEGIN Select * from tbl_user where userid=id; END Delimiter ; Call getName(‘1001’);
- 9. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName(IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); By default the delimiter is “;” . That means if you want the sever to start compiling of your sql query, you must put “;” and then press “enter”. Delimiter // statement changes the default delimiter to “//” so that giving “;” inside store procedure will not push server to start compilation
- 10. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName(IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); Is the keyword to create a new stored procedure
- 11. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName(IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); Is the name of the stored procedure that we are creating
- 12. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName ( IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); Is the parameter list (here intiger type variable called “id”) IN -> passing value into the stored procedure Out -> returning value out of stored procedure INOUT -> Same variable can be used to send in and out of stored procedure
- 13. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName ( IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); Is the keyword to begin the block of codes inside the stored function It’s same as curly brace ‘{‘ we use for functions in C
- 14. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName ( IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); Is the body of the stored procedure. Here we have only a single select query statements. We can also apply logic using the below • DECLARE a INT; : declaring an integer type variable • SET a=20; : Setting value of a to 20 • IF THEN • ELSE IF label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; : For conditions : Loops
- 15. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName ( IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); End // -> the compiler starts compiling after you press enter. This is because “//” is the delimiter
- 16. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName ( IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); Now we are changing the delimiter back to “;”
- 17. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getName ( IN id int) BEGIN Select * from tbl_user where userid=id; END// Delimiter ; Call getName(‘1001’); Calling the stored procedure we’ve just created and passing the value 1001 as its argument
- 18. How to createStored Procedure ? example Delimiter // CREATE PROCEDURE getSale ( IN id int,OUT sale int,OUT aver int) BEGIN Select sum(price) into sale,avg(price) into aver from tbl_purchase where deptid=id; END// Delimiter ; Call getSale(1001,@sale,@aver); Select @sale; Select @aver;
- 19. • Create thebelow table • Create a stored procedure called – csp_getSalary(1000) : should return the salary of employee with id as passed in the argument – Csp_getSalaryAtPlace(‘calicut’,@total) : should return the total salary of employees from a perticular place Live Task Tbl_employee Emp_id Emp_name Emp_age Emp_email int_salary vchr_place 1000 Deepak 24 dk@gmail.com 10000 Calicut 1001 Aneesh 23 an@gmail.com 20000 Cochin 1002 Naveen 25 nn@gmail.com 10000 Calicut 1003 Jacob 25 jb@gmail.com 30000 Cochin
- 20. Cursors • SELECT INTOis fine for single-row queries, but many applications require the querying of multiple rows of data. You will use a cursor in MySQL to accomplish this. • A cursor lets you fetch one or more rows from a SQL result set into stored program variables, usually with the intention of performing some row-by-row processing on the result set.
- 21. Cursors - example Createprocedure changePlace() Begin DECLARE empId INT; DECLARE placeId VARCHAR(50); DECLARE done INT DEFAULT 0; DECLARE myCur CURSOR FOR SELECT emp_id,vchr_place from tbl_employees; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN myCur; Emp_Loop: LOOP FETCH myCur INTO empId, placeId; If done=1 THEN LEAVE Emp_loop Else iterate emp_loop ENDIF END LOOP emp_loop CLOSE myCur ; END//
- 22. Declare Handler • TheDECLARE ... HANDLER statement specifies a handler that deals with one or more conditions. If one of these conditions occurs, the specified statement executes. • DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... Statement – handler_action: – CONTINUE | EXIT | UNDO – condition_value: mysql_error_code – SQLWARNING | NOT FOUND | SQLEXCEPTION
- 23. Questions? “A good questiondeserve a good grade…”
- 24.
- 25. • Why shouldsomeone use stored procedure? –To avoid data redundancy –To reduce network traffic between application server and database server –To ensure data integrity Self Check !!
- 26. • Why shouldsomeone use stored procedure? –To avoid data redundancy –To reduce network traffic between application server and database server –To ensure data integrity Self Check !!
- 27. Self Check !! •Why should someone change the delimiter before creating a stored procedure? – To use ; as seperation between multiple statements in a stored procedure body – To push the server to compile the whole body of stored procedure all together – Both of above – None of above
- 28. Self Check !! •Why should someone change the delimiter before creating a stored procedure? – To use ; as seperation between multiple statements in a stored procedure body – To push the server to compile the whole body of stored procedure all together – Both of above – None of above
- 29. Self Check !! CREATEPROCEDURE simpleProc ( OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t ; END CALL simpleProc(@a); Select @a ;
- 30. Self Check !! CREATEPROCEDURE simpleProc ( OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t ; END CALL simpleProc(@a); Select @a ;
- 31. Self Check !! •What are the uses of cursors? • For extracting multiple rows from a table • For extracting multiple rows into variables from a table • For setting handlers
- 32. Self Check !! •What are the uses of cursors? • For extracting multiple rows from a table • For extracting multiple rows into variables from a table • For setting handlers
- 33.