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

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

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

rake db migrate with down, up, redo, rollback options in rails

rake db migrate with down, up, redo, rollback options ? rake db migrate - This can be used to migrate your production/test database using various options like up, down, step, redo, version etc. In this tutorial we will learn how all these options can be used with rake tool to migrate the database. What is rake? rake is basically ruby make. i.e. make tool for ruby It has similar functionality to the make tool that you may have used on unix based systems for comopiling running some kind of script. rake allows you to ruby particular task in the environment that you specify. How to Install rake? You can install rake by installing gem 'rake' as, gem install rake Above command will install the latest version of rake tool avaialable. Various rake db migrate commands Operation     Command     Description General     rake db:migrate     This will migrate your database by running migrations that are not run yet Running specific Migra...
Post a Comment
Read more