Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQLi Articles
Page 70 of 341
How can we access tables through MySQL stored procedures?
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 MoreHow can we perform COMMIT transactions inside MySQL stored procedure?
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 MoreHow can REPLACE() be used with UPDATE clause to make permanent changes to a table?
As we know that REPLACE () function is used to replace the occurrences of a substring with another substring within a string. We can also use the REPLACE function with the UPDATE statement to update the table. Following example will demonstrate it −Examplemysql> Update Student set Father_Name = REPLACE(Father_Name, 'Mr.', 'Shri '); Query OK, 5 rows affected (0.06 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> Select Name, Father_Name from Student; +---------+-----------------+ | Name | Father_Name | +---------+-----------------+ | Gaurav | Shri Ramesh | | Aarav | Shri Sanjay | ...
Read MoreWhat would be the default return type of MySQL IFNULL() control flow operator?
Actually, the default return type of IFNULL(expression1, expression2) is more general of the two expressions, in the order STRING, REAL or INTEGER. It can be understood from the following example −Examplemysql> Create table testing Select IFNULL(100, 'testing123'); Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Select * from testing568; +-----------------------+ | IFNULL(100, 'testing') | +-----------------------+ | 100 | +-----------------------+ 1 row in set (0.00 sec) mysql> Describe testing568; +-----------------------+------------+------+-----+---------+-------+ | Field | Type ...
Read MoreWhat is the use of MySQL IFNULL() control flow function?
MySQL IFNULL() control flow function will return the first argument if it is not NULL otherwise it returns the second argument.SyntaxIFNULL(expression1, expression2)Here if expression1 is not NULL then IFNULL() will return expression1 otherwise expression2. It will return NULL if both of the arguments are NULL. Following example will exhibit this −mysql> Select IFNULL(NULL, 'Ram'); +--------------------+ | IFNULL(NULL, 'Ram') | +--------------------+ | Ram | +--------------------+ 1 row in set (0.00 sec) mysql> Select IFNULL('Shyam', 'Ram'); +-----------------------+ | IFNULL('Shyam', 'Ram') | +-----------------------+ | Shyam | +-----------------------+ ...
Read MoreWhat is the difference between = and: = assignment operators?
Actually, they both are assignment operator and used to assign values but the significant difference between them is as follows −= operator assigns a value either as a part of the SET statement or as a part of the SET clause in an UPDATE statement, in any other case = operator is interpreted as a comparison operator. On the other hand, := operator assigns a value and it is never interpreted as a comparison operator.mysql> Update estimated_cost1 SET Tender_value = '8570.000' where id = 2; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 ...
Read MoreWhat is the use of MySQL NULLIF() control flow function?
MySQL NULLIF() control flow function will return NULL if both the arguments are the same, otherwise, it returns the first argument.SyntaxNULLIF(expression1, expression2)Here if expression1 = expression2, NULL will be returned by NULLIF() otherwise expression1 will be returned. Following example will exhibit this −mysql> Select NULLIF('Ram','Ram'); +---------------------+ | NULLIF('Ram','Ram') | +---------------------+ | NULL | +---------------------+ 1 row in set (0.00 sec) mysql> Select NULLIF('Ram','Shyam'); +-----------------------+ | NULLIF('Ram','Shyam') | +-----------------------+ | Ram | +-----------------------+ 1 row in set (0.00 sec)
Read MoreWhat happens when we use COMMIT in MySQL stored procedure and one of the transaction, under START transaction, fails?
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 MoreHow can we drop a MySQL stored procedure?
If we have ALTER ROUTINE privileges for the procedure then with the help of DROP PROCEDURE statement we can drop a MySQL stored procedure. To demonstrate it, we are dropping a stored procedure named ‘coursedetails’ as follows −mysql> DROP PROCEDURE coursedetails; Query OK, 0 rows affected (0.68 sec)The above query will drop the procedure named ‘coursedetails’. It can be confirmed by running the statement SHOW CREATE PROCEDURE which will return an error because the procedure does not exist.
Read MoreHow can we alter a MySQL stored procedure?
If we have ALTER ROUTINE privileges for the procedure then with the help of ALTER PROCEDURE statement we can alter a MySQL stored procedure. To demonstrate it we are taking an example of a stored procedure named ‘delete_studentinfo’ which have the following create a statement −mysql> SHOW CREATE PROCEDURE Delete_studentinfo\G *************************** 1. row *************************** Procedure: Delete_studentinfo sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_studentinfo`( IN p_id INT) BEGIN DELETE FROM student_info WHERE ID=p_id; END character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: ...
Read More