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
-
Economics & Finance
MySQL Articles
Page 115 of 355
How can we create a MySQL stored function that uses the dynamic data from a table?
MySQL Stored functions can reference tables but they cannot make use of statements that return a result set. Hence we can say that there is no SELECT query that returns result set. But we can have SELECT INTO to get rid of that. For example, we are creating a function ‘Avg_marks’ that uses the dynamic data from table named ‘Student_marks’, having following records, to calculate the average of marks.mysql> Select * from Student_marks; +-------+------+---------+---------+---------+ | Name | Math | English | Science | History | +-------+------+---------+---------+---------+ | Raman | 95 | 89 | 85 | ...
Read MoreHow to write a MySQL stored function that inserts values in a table?
As we know that function is best used when we want to return a result. Hence, when we will create stored functions for manipulating tables like to Insert or Update values then it would be more or less like stored procedures.ExampleIn the following example we are creating a stored function named ‘tbl_insert’ which will insert the values in a table named ‘student_marks’.mysql> Create Function tbl_insert(S_name Varchar(50), M1 INT, M2 INT, M3 INT, M4 INT) -> RETURNS INT -> DETERMINISTIC -> BEGIN -> INSERT INTO student_marks values(S_name, M1, M2, M3, M4); ...
Read MoreHow can we create MySQL stored procedure to calculate the factorial?
mysql> DELIMITER // mysql> CREATE PROCEDURE get_factorial(IN N INT) -> BEGIN -> SET @@GLOBAL.max_sp_recursion_depth = 255; -> SET @@session.max_sp_recursion_depth = 255; -> -> CALL factorial_recursive (N, @factorial); -> -> SELECT @factorial; -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER // mysql> CREATE PROCEDURE factorial_recursive(IN N INT, OUT factorial INT) -> BEGIN -> IF N = 1 THEN -> SET factorial := 1; -> ...
Read MoreHow can we write MySQL handler, in a stored procedure, that throws an error message and continues the execution?
As we know that whenever an exception occurred in MySQL stored procedure, it is very important to handle it by throwing proper error message because if we do not handle the exception, there would be a chance to fail application with that certain exception in a stored procedure. MySQL provides a handler that throws an error message and continues the execution. To demonstrate it, we are using the following example in which we are trying to insert a duplicate value in a Primary key column.Examplemysql> DELIMITER // mysql> Create Procedure Insert_Studentdetails(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20)) -> BEGIN ...
Read MoreHow can we write MySQL stored procedure to select all the data from a table?
To demonstrate it we are creating a procedure named ‘selectdetails()’ which will fetch all the records from table ‘student_detail’.mysql> Delimiter // mysql> Create Procedure selectdetails() -> BEGIN -> Select * from student_detail; -> END// Query OK, 0 rows affected (0.00 sec)Now, after invoking this procedure, we will get all the records from ‘student_detail’ table.mysql> Delimiter ; mysql> CALL selectdetails(); +-----------+-------------+------------+ | Studentid | StudentName | address | +-----------+-------------+------------+ | 100 | Gaurav | Delhi | | 101 | Raman | Shimla | | 103 | Rahul | Jaipur | | 104 | Ram | Chandigarh | | 105 | Mohan | Chandigarh | +-----------+-------------+------------+ 5 rows in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
Read MoreWhat is the default type of a hexadecimal value in MySQL?
As we know that in numeric contexts the hexadecimal values act like integers and in string contexts they act like binary string. It can be understood with the help of the following example,mysql> Select X'5455544F5249414C53504F494E54'; +---------------------------------+ | X'5455544F5249414C53504F494E54' | +---------------------------------+ | TUTORIALSPOINT | +---------------------------------+ 1 row in set (0.07 sec)But, if we are talking about default type of hexadecimal value in MySQL, then it is a string.
Read MoreWhile running MySQL statements in batch mode, how can we print, along with output, which statements are getting executed?
By using –v option in batch mode, the MySQL statements can be printed along with output. For example, after running the same query in batch mode with –v option we will get the statement printed along with output.C:\Program Files\MySQL\bin>mysql -u root -p gaurav < hh.sql -v Enter password: ***** -------------- select * from hh -------------- id 1 2It is showing the statement select * from hh which is written in the file hh.sql.
Read MoreHow can we get the list of MySQL server-side help categories?
We can get the list of MySQL server-side help categories by giving the keyword contents to the help command.mysql> help contents You asked for help about help category: "Contents" For more information, type 'help ', where is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Help Metadata Language Structure Plugins Procedures Storage Engines Table Maintenance Transactions User-Defined Functions Utility
Read MoreHow can we return to windows command shell from MySQL command line tool?
The EXIT or QUIT commands take you returned to windows from MySQL command line tool.mysql> EXITORmysql> QUIT
Read MoreHow can we get an idea about the server performance from the output of MySQL?
After running a query, MySQL returns the number of rows and gives time in the output that shows how much it took for running that query. As for example, if we run the following querymysql> create table e1(id int); Query OK, 0 rows affected (0.23 sec)It is showing the time (0.23 sec).
Read More