Database Articles - Page 564 of 618

Suppose one of the queries fails or generates errors and another query (s) properly executed the MySQL still commit the changes of the properly executed query(s). It can be understood from the following example in which we are using the table ‘employee.tbl’ having the following data −Examplemysql> Select * from employee.tbl; +----+---------+ | Id | Name    | +----+---------+ | 1  | Mohan   | | 2  | Gaurav  | | 3  | Sohan   | | 4  | Saurabh | | 5  | Yash    | +----+---------+ 5 rows in set (0.00 sec) mysql> Delimiter // mysql> ... Read More

As we know the START transaction will start the transaction and COMMIT is used to made any changes made after starting the transaction. In the following example, we have created a stored procedure with COMMIT along with START transaction which will insert a new record and commit changes in table ‘employee.tbl’ having the following data −mysql> Select * from employee.tbl; +----+---------+ | Id | Name    | +----+---------+ |  1 | Mohan   | |  2 | Gaurav  | |  3 | Rahul   | |  4 | Saurabh | +----+---------+ 4 rows in set (0.00 sec)Examplemysql> Delimiter // mysql> ... Read More

As we know the START transaction will start the transaction and set the auto-commit mode to off. In the following example, we have created a stored procedure with a START transaction which will insert a new record in table employee.tbl having the following data −mysql> Select * from employee.tbl; +----+---------+ | Id | Name    | +----+---------+ | 1  | Mohan   | | 2  | Gaurav  | | 3  | Rahul   | +----+---------+ 3 rows in set (0.00 sec)Examplemysql> Delimiter // mysql> Create Procedure st_transaction()    -> BEGIN    -> START TRANSACTION;    -> INSERT INTO employee.tbl(name) values ... Read More

We can access one or all the tables from the MySQL stored procedure. Following is an example in which we created a stored procedure that will accept the name of the table as a parameter and after invoking it, will produce the result set with all the details from the table.Examplemysql> Delimiter // mysql> Create procedure access(tablename varchar(30))    -> BEGIN    -> SET @X := CONCAT('Select * from', ' ', tablename);    -> Prepare statement from @X;    -> Execute statement;    -> END// Query OK, 0 rows affected (0.16 sec)Now invoke the procedure with the table name, we ... Read More

It is quite possible that a MySQL stored procedure can call another MySQL stored procedure inside it. To demonstrate it, we are taking an example in which a stored procedure will call another stored procedure to find out the last_insert_id.Examplemysql> Create table employee.tbl(Id INT NOT NULL AUTO_INCREMENT, Name Varchar(30) NOT NULL, PRIMARY KEY(id))// Query OK, 0 rows affected (3.87 sec) mysql> Create Procedure insert1()    -> BEGIN insert into employee.tbl(name) values ('Ram');    -> END// Query OK, 0 rows affected (0.10 sec)Now, in the next procedure insert2() we will call the 1st stored procedure i.e. insert1().mysql> Create Procedure insert2() ... Read More

Suppose currently we are using a database named ‘query’ and it is having the following tables in it −mysql> Show tables in query; +-----------------+ | Tables_in_query | +-----------------+ | student_detail  | | student_info    | +-----------------+ 2 rows in set (0.00 sec)Now, following is a stored procedure, which will accept the name of the database as its parameter and give us the list of tables with detailed information −mysql> DELIMITER// mysql> CREATE procedure tb_list(db_name varchar(40))    -> BEGIN    -> SET @z := CONCAT('Select * from information_schema.tables WHERE table_schema = ', '\'', db_name, '\'');    -> Prepare stmt from @z; ... Read More

Suppose currently we are using a database named ‘query’ and it is having the following tables in it −mysql> Show tables in query; +-----------------+ | Tables_in_query | +-----------------+ | student_detail  | | student_info    | +-----------------+ 2 rows in set (0.00 sec)Now, following is a stored procedure, which will give us the list of tables with detailed information −mysql> DELIMITER// mysql> CREATE procedure tablelist()    -> BEGIN    -> Select * from Information_schema.tables WHERE table_schema = 'query';    -> END // Query OK, 0 rows affected (0.06 sec) mysql> DELIMITER; mysql> CALL tablelist()\G *************************** 1. row ***************************   ... Read More

We can use INTERVAL() function with a column of a table by providing the first argument as the name of the column. In this case, al the values in that column would be compared with the values given as the other arguments of INTERVAL() function and on that basis of comparison, the result set is provided. To understand it, the data from the employee table is used which is as follows −mysql> Select* from employee568; +----+--------+--------+ | ID | Name   | Salary | +----+--------+--------+ | 1  | Gaurav | 50000  | | 2  | Rahul  | 20000  | | ... Read More

In this case, MySQL INTERVAL() function returns the index number of the last number in argument list plus 1. In other words, the last index number in the list plus 1 would be returned by this function. Following example will demonstrate it −mysql> Select INTERVAL(50,20,32,38,40); +--------------------------+ | INTERVAL(50,20,32,38,40) | +--------------------------+ | 4                        | +--------------------------+ 1 row in set (0.00 sec)

Actually, INTERVAL() function uses the binary search for searching the bigger number than the number at first argument. So, that is why if we want INTERVAL() function to work efficiently the list of numbers would be in ascending order. Following is a good way to use INTERVAL() function −mysql> Select INTERVAL(50,20,32,38,40,50,55);