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

Popular posts from this blog

Gem::LoadError (Specified 'mysql2' for database adapter, but the gem is not loaded. Add `gem 'mysql2'` to your Gemfile (and ensure its version is at the minimum required by ActiveRecord).?

Gem::LoadError (Specified 'mysql2' for database adapter, but the gem is not loaded. Add `gem 'mysql2'` to your Gemfile (and ensure its version is at the minimum required by ActiveRecord).) In rails?


1)change the gemfile in mysql version
 gem 'mysql2'

2)Change to mysql2 version
 gem 'mysql2','0.3.20'

Active Scaffold : Add radio buttons to your form ruby on rails ?

Active Scaffold : Add radio buttons to your form ruby on rails ?

The way to add radio buttons to your active scaffold create/update from is as shown below

#Controller

class UsersController < ApplicationController
active_scaffold :user do |config|
config.label = “Users”
config.columns = [:name, :gender]
config.columns[:gender].form_ui = :radio
config.columns[:gender].options[:options] = [['Male', '1'], ['Female','2']]
end
end

Get user accessing ip address in rails?

Get user accessing ip address rails?

request.ip ->returns the ip, whether is is a local proxy ip (localhost address) or not.
*request.remote_ip -> is smarter and gets the ip address of the client outside of local proxies.
3)If you are using apache in front of a mongrel, then remote_ip will return the source address of the request, which in this case will be local host because the Apache web server is making the request, so instead put this in your controller:

@remote_ip = request.env["HTTP_X_FORWARDED_FOR"]