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

Create dynamic sitemap on ruby on rails

Sitemaps are an easy way for webmasters to inform search engines about pages on their sites that are available for crawling. In its simplest form, a Sitemap is an XML file that lists URLs for a site along with additional metadata about each URL (when it was last updated, how often it usually changes, and how important it is, relative to other URLs in the site) so that search engines can more intelligently crawl the site. It’s basically a XML file describing all URLs in your page: The following example shows a Sitemap that contains just one URL and uses all optional tags. The optional tags are in italics. <?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">    <url>       <loc>http://www.example.com/</loc>       <lastmod>2005-01-01</lastmod>       <changefreq>monthly</changefreq>     ...
Post a Comment
Read more

Omniauth Linked in Ruby On Rails

def get_linkedin_user_data      omniauth = request.env["omniauth.auth"]      dat=omniauth.extra.raw_info      linked_app_key = "xxxxxxx"      linkedin_secret_key = "yyyyyyy"      client = LinkedIn::Client.new(linked_app_key,linkedin_secret_key)      client.authorize_from_access(omniauth['credentials']['token'],omniauth['credentials']['secret'])      connections=client.connections(:fields => ["id", "first-name", "last-name","picture-url"])      uid=omniauth['uid']      token=omniauth["credentials"]["token"]      secret=omniauth["credentials"]["secret"]   #linked user data     omniauth = request.env["omniauth.auth"]      data             = omniauth.info      user_name...
Post a Comment
Read more

Install Rvm on ubuntu

sudo apt-get install libgdbm-dev libncurses5-dev automake libtool bison libffi-dev curl -L https://get.rvm.io | bash -s stable source ~/.rvm/scripts/rvm rvm install 2.0.0-p645 rvm use 2.0.0-p645 --default ruby -v rvm gemset create rails3.2.8 rvm gemset use rails3.2.8 gem install rails -v 3.2.8
Post a Comment
Read more