Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 65 10 Older Entries

Displaying posts with tag: stored procedure (reset)

How to use value from row derived column from a table in another query?
+1 Vote Up -1Vote Down

SELECT...INTO can be used to store the single row column result value from a query to be used in another query depending on that value.

"accounts" table

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| account_id   | …






  [Read more...]
Use of loops in MySQL stored procedures
+2 Vote Up -1Vote Down

Loops -> Repetitive tasks

3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT  and UNTIL clauses that runs until a condition is true



Termination of loop -> LEAVE statement.

I will demonstrate the use of loops the simple loop way.














  [Read more...]
Using conditional expressions inside MySQL Stored Procedure
+1 Vote Up -1Vote Down

Sometimes there is need to use conditional expression inside stored procedure to control the flow of execution.
We can use IF or CASE statements for this.


Below is a stored procedure to check the performance of a student based on its score.
Store the below stored procedure in a file called get_performance.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS get_performance$$

CREATE PROCEDURE …










  [Read more...]
How to use and get output arguments in stored procedure using OUT parameter mode?
+1 Vote Up -0Vote Down

It is sometimes required to get the result of a stored procedure in a variable which can be used later or to output the result.

To do so, we can use the "OUT" parameter mode while defining stored procedures.

In the below section, we will be writing a stored procedure to get the square root of a number returned in an output variable provided by us.



Stored Procedure Definition:

Store the below stored procedure in a file named …









  [Read more...]
An approach to MySQL dynamic cross-reference query
+0 Vote Up -0Vote Down

I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:

1. Write the main query with rows: project identifier, task subject and total hours in a date range.

SELECT p.identifier, 
        e.issue_id, i.subject, round(SUM(e.hours),2) AS total_hours
        FROM redmine_nsp.time_entries e
        INNER JOIN redmine_nsp.issues i ON e.issue_id = i.id
        INNER JOIN …
  [Read more...]
WITH RECURSIVE and MySQL
Employee_Team +1 Vote Up -0Vote Down

If you have been using certain DBMSs, or reading recent versions of the SQL standard, you are probably aware of the so-called “WITH clause” of SQL.
Some call it Subquery Factoring. Others call it Common Table Expression. A form of the WITH CLAUSE, WITH RECURSIVE”, allows to design a recursive query: a query which repeats itself again and again, each time using the results of the previous iteration. This can be quite useful …

  [Read more...]
WITH RECURSIVE and MySQL
Employee +2 Vote Up -0Vote Down

If you have been using certain DBMSs, or reading recent versions of the SQL standard, you are probably aware of the so-called "WITH clause" of SQL.
Some call it Subquery Factoring. Others call it Common Table Expression.
In its simplest form, this feature is a kind of "boosted derived table".

Assume that a table T1 has three columns:


CREATE TABLE T1(
YEAR INT, # 2000, 2001, 2002 ... …






  [Read more...]
Using Perl Stored Procedures for MariaDB, slides uploaded.
+1 Vote Up -0Vote Down

Just recently did the presentation and Q&A for the Using Perl Stored Procedures presentation at Percona Live 2013.

The presentation has been uploaded:

Using Perl Stored Procedures for MariaDB from Antony T Curtis

(repeat posting because planet.mysql.com didn't notice it the first time around)

MySQL, the strange case of a timestamp field
+0 Vote Up -5Vote Down

I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:

When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.

There is test-case script:

-- CREATING TABLE AND INSERT SOME DUMMY DATA
mysql> CREATE TABLE t(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> val VARCHAR(50)
    -> );
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> INSERT INTO t (val) VALUES ("foo") ,("var");
Query OK, 2 ROWS affected (0.08 sec)
Records: 2  Duplicates: 0 …
  [Read more...]
MySQL get disk usage of all databases
+0 Vote Up -0Vote Down

I was tired to get manually disk space used for all MySQL databases, I just created a stored procedure to get an overview of the database sizes in our MySQL server.

MySQL don’t have a command that allows us an overall summary of the databases, something like SHOW TABLE STATUS for databases. The “SHOW DATABASES” command lists only the current databases without any other information like how many space are using or how many tables there are, etc.

The procedure that I wrote this based on INFORMATION_SCHEMA database, which contains rich information of all existing databases.

I suggest, in a previous post, place all …

  [Read more...]
Showing entries 1 to 10 of 65 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.