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
Database Articles
Page 162 of 546
How can we see the source code of a particular MySQL stored procedure?
With the help of SHOW CREATE PROCEDURE statement, we can see the source code of a stored procedure. To make it understand we are using the stored procedure named allrecords() in the query as follows −mysql> Show Create Procedure allrecords\G *************************** 1. row *************************** Procedure: allrecords 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 DEFINERb=`root`@`localhost` PROCEDURE `allrecords`() BEGIN Select * from Student_info; END character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
Read MoreHow MySQL evaluates when I use a conditional expression within SUM() function?
As we know that, by using a conditional expression within SUM() function we can get the number of rows that meet the condition. So, in this case, MySQL evaluates to 1 each time the condition is true and 0 each time it is false.To understand it, consider the following example of table ‘employee’, having the following details −mysql> Select * from Employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | Aarav | 65000 | ...
Read MoreWhat are the different modes of parameters used by MySQL stored procedure?
Parameters make the stored procedure more useful and flexible. In MySQL, we have the following three kinds of modes −IN modeIt is the default mode. When we define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure. The value of an IN parameter is protected which means that even the value of the IN parameter is changed inside the stored procedure; its original value is retained after the stored procedure ends.OUT modeThe value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling ...
Read MoreHow can we find the most recent and most oldest date from a table with the help of MySQL MAX() and MIN() functions?
For getting the most recent date from a table, we need to provide the name of the column, having a date as value, as the argument of MAX() function. Similarly, forgetting the oldest date from a table, we need to provide the name of a column, having a date as value, as the argument of MIN() function. To understand it, consider the following example of table ‘Collegedetail’, having the following details −mysql> Select * from collegedetail; +------+---------+------------+ | ID | Country | estb | +------+---------+------------+ | 111 | INDIA | 2010-05-01 | | 130 | ...
Read MoreWhat is the method with the help of which the hexadecimal value can be treated as a number?
For treating the hexadecimal value as a number, we can use the CAST(… AS UNSIGNED) function. Following example will demonstrate it −mysql> Select 0x54, CAST(0x54 AS UNSIGNED); +------+------------------------+ | 0x54 | CAST(0x54 AS UNSIGNED) | +------+------------------------+ | T | 84 | +------+------------------------+ 1 row in set (0.01 sec)
Read MoreHow can I create MySQL stored procedure with IN parameter?
To make it understand we are using the table named ‘student_info’ which have the following values −mysql> Select * from student_info; +-----+---------+------------+------------+ | id | Name | Address | Subject | +-----+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 110 | Rahul | Chandigarh | History | | 125 | Raman | Shimla | Computers | +------+--------+------------+------------+ 4 rows in set (0.00 sec)Now, with the help of the following query, we will create a stored procedure ...
Read MoreHow can I create MySQL stored procedure with INOUT parameter?
Following example will demonstrate MySQL stored procedure with INOUT parameter −mysql> DELIMITER // ; mysql> Create PROCEDURE counter(INOUT count INT, IN increment INT) -> BEGIN -> SET count = count + increment; -> END // Query OK, 0 rows affected (0.03 sec)Here, ‘count’ is the INOUT parameter, which can store and return values and ‘increment’ is the IN parameter, which accepts the values from user.mysql> DELIMITER ; mysql> SET @counter = 0; Query OK, 0 rows affected (0.00 sec) mysql> CALL counter(@Counter, 1); Query OK, 0 rows affected (0.00 sec) mysql> Select @Counter; ...
Read MoreHow can we see the list of stored procedures and stored functions in a particular MySQL database?
We can see the list of the stored procedure and stored functions in a particular database by using the following query on INFORMATION_SCHEMA.ROUTINES as follows −mysql> SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'query'; +--------------+--------------+ | ROUTINE_TYPE | ROUTINE_NAME | +--------------+--------------+ | PROCEDURE | allrecords | | FUNCTION | Hello | +--------------+--------------+ 2 rows in set (0.04 sec)The above query returns the procedure named ‘allrecords’, and function named ‘Hello’ which are stored in the database named ‘query’.
Read MoreHow can we get some starting number of characters from the data stored in a MySQL table’s column?
To get some starting number of characters from the data stored in the MySQL table’s column, we can use MySQL LEFT() function. It will return the number of characters specified as its argument. We need to provide the name of the column, having the particular record from which we want to get starting characters, as its first argument. To demonstrate it we are taking the example of a table named ‘examination_btech’ having the following examination details of students −mysql> Select * from examination_btech; +-----------+----------+--------+ | RollNo | Name | Course | +-----------+----------+--------+ | 201712001 | Rahul ...
Read MoreHow can I customize the output of MySQL SUM() function to 0 instead of NULL when there are no matching rows?
As we know that the SUM() function returns NULL if there is no matching row but sometimes we want it to return zero instead of NULL. For this purpose, we can use the MySQL COALESCE() function which accepts two arguments and returns the second argument if the first argument is NULL, otherwise, it returns the first argument. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | ...
Read More