- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
110 Views
Actually standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave MySQL server. Even the creation of stored procedures and functions carried out through normal DDL statements on a master MySQL server are replicated to a slave MySQL server. In this way, objects will exist on both the servers.The actions that take place inside the stored procedure and functions are replicated because MySQL records each DDL event that occurs inside stored procedures and functions. After recording the events it is replicated to the slave MySQL server. But the actual calls made ... Read More
2K+ Views
As we know that ROLLBACK will revert any changes made to the database after the transaction has been started. To perform the ROLLBACK in MySQL stored procedure we must have to declare EXIT handler. We can use a handler for either sqlexception or SQL warnings. It can be understood with the help of an example in which stored procedure having ROLLBACK created for the table having the following details −mysql> SHOW CREATE table gg\G *************************** 1. row *************************** Table: gg Create Table: CREATE TABLE `gg` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(30) NOT NULL, PRIMARY KEY ... Read More
492 Views
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
605 Views
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
332 Views
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
275 Views
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
3K+ Views
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
309 Views
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
53 Views
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
64 Views
MySQL REVERSE() function can be used to reverse a string. Following example will demonstrate it −mysql> Select REVERSE('Tutorialspoint'); +---------------------------+ | REVERSE('Tutorialspoint') | +---------------------------+ | tniopslairotuT | +---------------------------+ 1 row in set (0.00 sec) mysql> Select Reverse('10-11-12'); +---------------------+ | Reverse('10-11-12') | +---------------------+ | 21-11-01 | +---------------------+ 1 row in set (0.00 sec)