Skip to main content

Mysql Store Procedures and cursors and triggers

Mysql Store Procedures and cursors and triggers



---------------start--------------------
to drop procedure
----------------------------------------


mysql> drop procedure myProc;
    -> $$
Query OK, 0 rows affected (0.02 sec)

---------------end----------------------
---------------start--------------------
cursor sample example1
----------------------------------------

mysql> delimiter $$
mysql> CREATE PROCEDURE myProc() BEGIN
    ->  DECLARE l_last_row INT DEFAULT 0;
    ->  DECLARE l_dept_id  INT;
    ->  DECLARE c_dept CURSOR FOR
    -> select id from employees;
    -> DECLARE continue handler for NOT FOUND SET l_last_row=1;
    -> OPEN c_dept;
    -> dept_cursor: LOOP
    -> FETCH c_dept INTO l_dept_id;
    -> IF (l_last_row=1) THEN
    -> LEAVE dept_cursor;
    -> END IF;
    -> select l_dept_id;
    -> END LOOP dept_cursor;
    -> CLOSE c_dept;
    -> END$$
Query OK, 0 rows affected (0.03 sec)

mysql> call myProc()
    -> $$
+-----------+
| l_dept_id |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

+-----------+
| l_dept_id |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

+-----------+
| l_dept_id |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

+-----------+
| l_dept_id |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

+-----------+
| l_dept_id |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)
=====================================================
                 cursor sample ex2
=====================================================
mysql>  DELIMITER $$
mysql> CREATE PROCEDURE userCursor()
    -> BEGIN
    -> DECLARE flag TINYINT DEFAULT 0;
    -> DECLARE n VARCHAR(200);
    -> DECLARE s VARCHAR(200);
    -> DECLARE testCursor CURSOR
    -> FOR SELECT id,name from course;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
    -> SET flag=0;
    -> end $$
Query OK, 0 rows affected (0.00 sec)



mysql> insert into college values("vec","bellary"),("bec","bellary"),("rvc","bangalore");
    -> $$
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> delimiter ;
mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
+------+-----------+
3 rows in set (0.00 sec)

mysql> create table friend(name varchar(10),address varchar(10));
Query OK, 0 rows affected (0.09 sec)
=======================================================
               cursor sample ex3
=======================================================
mysql> DELIMITER $$
mysql> CREATE PROCEDURE userCursor()
    -> BEGIN
    -> DECLARE flag TINYINT DEFAULT 0;
    -> DECLARE n VARCHAR(200);
    -> DECLARE s VARCHAR(200);
    -> DECLARE testCursor CURSOR
    -> FOR SELECT name,address from friend;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
    -> SET flag=0;
    -> OPEN testCursor;
    -> WHILE (NOT flag) DO
    -> FETCH testCursor INTO n,s;
    -> IF NOT flag THEN
    -> INSERT into college (name,address) VALUES (n,s);
    -> END IF;
    -> END WHILE;
    -> SELECT * FROM friend;
    -> CLOSE testCursor;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> call userCursor;
    -> $$
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into friend values("aaa","abc");
    -> $$
Query OK, 1 row affected (0.03 sec)

mysql> delimiter ;
mysql> call userCursor;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
+------+---------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
+------+-----------+
4 rows in set (0.00 sec)

mysql> insert into friend values("bbb","xyz"),("ccc","efg");
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
+------+-----------+
4 rows in set (0.00 sec)

mysql> select *from friend;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
| bbb  | xyz     |
| ccc  | efg     |
+------+---------+
3 rows in set (0.00 sec)

mysql> call userCursor;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
| bbb  | xyz     |
| ccc  | efg     |
+------+---------+
3 rows in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
+------+-----------+
7 rows in set (0.00 sec)





mysql> delimiter ;
mysql> select *from User;
+------+------+------+
| id   | c_id | name |
+------+------+------+
|    2 |    1 | abc  |
|    3 |    2 | xyz  |
|    4 |    1 | klm  |
|    5 |    3 | efg  |
|   10 |    5 | aaa  |
|   11 |    6 | www  |
+------+------+------+
6 rows in set (0.00 sec)
--------------------------end of cursors---------------------
--------------------------------------------------------------
          start of procedure with in parameter
--------------------------------------------------------------

mysql> delimiter //
mysql> create procedure proc_in(in uname varchar(10)) begin select  *from User where name=uname; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_in("abc")
    -> //
+------+------+------+
| id   | c_id | name |
+------+------+------+
|    2 |    1 | abc  |
+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
----------------------end of procedure with in parameter---------
--------------------------------------------------------------
          start of procedure with out parameter
--------------------------------------------------------------
mysql> delimiter //
mysql> create procedure proc_out(in id int(2),out total int(2))
    -> begin
    -> select count(c_id) into total from User where c_id=id;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_out(1,@t)
    -> //
Query OK, 1 row affected (0.02 sec)

mysql> select @t;
    -> //
+------+
| @t   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
----------------------end of procedure with in parameter---------
--------------------------------------------------------------
          start of triggers
--------------------------------------------------------------
mysql> delimiter //
mysql> create trigger user_before_insert before insert on User for each row
    -> begin
    -> insert into college values ("ppp","delhi");
    -> end //
Query OK, 0 rows affected (0.05 sec)

mysql> select *from User;
    -> //
+------+------+------+
| id   | c_id | name |
+------+------+------+
|    2 |    1 | abc  |
|    3 |    2 | xyz  |
|    4 |    1 | klm  |
|    5 |    3 | efg  |
|   10 |    5 | aaa  |
|   11 |    6 | www  |
+------+------+------+
6 rows in set (0.00 sec)

mysql> delimiter ;
mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
+------+-----------+
7 rows in set (0.00 sec)


mysql> insert into User values(12,10,"nnn");
Query OK, 1 row affected (0.04 sec)

mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
| ppp  | delhi     |
+------+-----------+
8 rows in set (0.00 sec)







mysql> delimiter //
mysql> create trigger b_d_u after delete on User for each row
    -> begin
    -> insert into college values("ggg","xyz");
    -> end //
Query OK, 0 rows affected (0.06 sec)

mysql> select *from User;
    -> //
+------+------+------+
| id   | c_id | name |
+------+------+------+
|    2 |    1 | abc  |
|    3 |    2 | xyz  |
|    4 |    1 | klm  |
|    5 |    3 | efg  |
|   10 |    5 | aaa  |
|   11 |    6 | www  |
|   12 |   10 | nnn  |
+------+------+------+
7 rows in set (0.00 sec)

mysql> delimiter ;
mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
| ppp  | delhi     |
+------+-----------+
8 rows in set (0.00 sec)

mysql> delete from User where id =12;
Query OK, 1 row affected (0.03 sec)

mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
| ppp  | delhi     |
| ggg  | xyz       |
+------+-----------+
9 rows in set (0.00 sec)

mysql> delete from User where id =11;
Query OK, 1 row affected (0.03 sec)

mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
| ppp  | delhi     |
| ggg  | xyz       |
| ggg  | xyz       |
+------+-----------+
10 rows in set (0.00 sec)
---------------end of triggers-------------------
mysql> show procedure status;
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| stud | course_out   | PROCEDURE | root@localhost | 2015-04-16 16:55:26 | 2015-04-16 16:55:26 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | course_proc  | PROCEDURE | root@localhost | 2015-04-16 15:50:31 | 2015-04-16 15:50:31 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | cursor_proc  | PROCEDURE | root@localhost | 2015-04-16 18:42:20 | 2015-04-16 18:42:20 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | cursor_proc1 | PROCEDURE | root@localhost | 2015-04-16 19:02:25 | 2015-04-16 19:02:25 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | myProc       | PROCEDURE | root@localhost | 2015-04-17 11:30:09 | 2015-04-17 11:30:09 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | proc_in      | PROCEDURE | root@localhost | 2015-04-17 15:27:38 | 2015-04-17 15:27:38 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | proc_out     | PROCEDURE | root@localhost | 2015-04-17 15:40:56 | 2015-04-17 15:40:56 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | pro_i        | PROCEDURE | root@localhost | 2015-04-17 16:01:10 | 2015-04-17 16:01:10 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | p_i          | PROCEDURE | root@localhost | 2015-04-17 15:58:21 | 2015-04-17 15:58:21 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | set_counter  | PROCEDURE | root@localhost | 2015-04-16 17:20:25 | 2015-04-16 17:20:25 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | set_counter1 | PROCEDURE | root@localhost | 2015-04-16 17:24:25 | 2015-04-16 17:24:25 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | student      | PROCEDURE | root@localhost | 2015-04-16 15:32:34 | 2015-04-16 15:32:34 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| stud | userCursor   | PROCEDURE | root@localhost | 2015-04-17 11:58:45 | 2015-04-17 11:58:45 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
13 rows in set (0.00 sec)


mysql> show triggers;
+---------------------+--------+--------+-------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger             | Event  | Table  | Statement                                             | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |
+---------------------+--------+--------+-------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| user_before_insert  | INSERT | User   | begin
insert into college values ("ppp","delhi");
end | BEFORE | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| b_d_u               | DELETE | User   | begin
insert into college values("ggg","xyz");
end    | AFTER  | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| course_after_insert | INSERT | course | begin insert into User values(10,5,"aaa"); end        | AFTER  | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| course_after_update | UPDATE | course | begin insert into User values(11,6,"www"); end        | AFTER  | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------------------+--------+--------+-------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
4 rows in set (0.00 sec)


mysql> select *from friend;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
| bbb  | xyz     |
| ccc  | efg     |
+------+---------+
3 rows in set (0.00 sec)

mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
| ppp  | delhi     |
| ggg  | xyz       |
| ggg  | xyz       |
+------+-----------+
10 rows in set (0.00 sec)

mysql> call userCursor;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
| bbb  | xyz     |
| ccc  | efg     |
+------+---------+
3 rows in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

mysql> select *from college;
+------+-----------+
| name | address   |
+------+-----------+
| vec  | bellary   |
| bec  | bellary   |
| rvc  | bangalore |
| aaa  | abc       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
| ppp  | delhi     |
| ggg  | xyz       |
| ggg  | xyz       |
| aaa  | abc       |
| bbb  | xyz       |
| ccc  | efg       |
+------+-----------+
13 rows in set (0.00 sec)


mysql> delete from college;
Query OK, 13 rows affected (0.07 sec)

mysql> select *from college;
Empty set (0.00 sec)

mysql> call userCursor;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
| bbb  | xyz     |
| ccc  | efg     |
+------+---------+
3 rows in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

mysql> select *from college;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
| bbb  | xyz     |
| ccc  | efg     |
+------+---------+
3 rows in set (0.00 sec)

mysql> delete from college;
Query OK, 3 rows affected (0.03 sec)

mysql> insert into friend values("ppp","qqq");
Query OK, 1 row affected (0.03 sec)

mysql> call userCursor;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
| bbb  | xyz     |
| ccc  | efg     |
| ppp  | qqq     |
+------+---------+
4 rows in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

mysql> select *from college;
+------+---------+
| name | address |
+------+---------+
| aaa  | abc     |
| bbb  | xyz     |
| ccc  | efg     |
| ppp  | qqq     |
+------+---------+
4 rows in set (0.00 sec)

mysql>

Comments

Post a Comment

Popular posts from this blog

Error malloc(): memory corruption nginx with passenger?

Error malloc(): memory corruption nginx with passenger Passenger issue resolving steps :  sudo gem uninstall passenger(uninstall all passenger) sudo gem install passenger sudo passenger-install-nginx-module --auto --auto-download --prefix=/opt/nginx --extra-configure-flags=none Update nginx config file with new passenger version and restart the nginx
1 comment
Read more

Lazy loading in rails – Rails Feature

 Lazy loading in rails – Rails Feature ? Lazy loading in rails is the amazing feature provided with rails. In console you might have tried to examine how lazy loading in rails actually works. In this tutorial, we will learn about this Rails - Lazy loading feature with examples. What exactly is Lazy Loading? As the name suggests the data is loaded in lazy manner (Really!) i.e. Your database is queried only when data from the database is required for some kind of manipulation in code. You will get more of this after you read how-to of lazy loading below. How lazy loading works: Whenever you try to get some data from database, For example, users is the database table that you have. And you are querying database to get users having age less than 20. Then, you will write code like, result = User.where("age < 20") when above statement is executed, your database is not queries yet(because the resultant data is not required yet). When you execute following code, records = resu...
Post a Comment
Read more

Rails Migration Difference between Text and String

Rails Migration Difference between Text and String ? While working with Rails Migration Difference between Text and String is important to be known to every developer. Columns and their data types are finalized while deciding Table structure. This tutorial will help understand difference between String and Text column type and illustrate how to write Rails Migration implementing the same. You might want to read about database.yml files for specifying database configuration for Rails Application. 1. Concepts When String or Text data type is required?     Whenever you require your column to store information which is lengthy in size (Many characters), you need to consider String or Text data type for the column.     Both of them let you store Many(How Many - will see later) characters Difference between String and Text Considering MySQL database Feature     String     Text Length     1 to 255     ...
Post a Comment
Read more