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 get_performance
    (score NUMERIC(8, 2),
    OUT result VARCHAR(11))

BEGIN
    IF (score >= 90) THEN
        SET result = 'OUTSTANDING';
    ELSEIF (score >= 70 …

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 my_sqrt.sql and save it.

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE my_sqrt(inp_number INT, OUT op_number FLOAT)
BEGIN
    SET op_number=SQRT(inp_number);
…

August 15, 2014 By Severalnines

We're looking for an energetic and talented web developer to join our small but agile web team. This position is full-time and pay is negotiable. The hours are flexible and work can be done remotely.

Severalnines is a self-funded startup with a dozen employees; headquartered in Stockholm, Sweden and with a globally distributed, home-office based team. We provide automation and management software for database clusters. Our ClusterControl product is the leading management application for database clusters and is used by thousands of companies.

We were founded in 2010 and launched our product that same year, so you’ll be …

This week we are talking about size, which is a subject that should matter to any system administrator in charge of the backup system of any project, and in particular database backups.

I sometimes get questions about what should be the best compression tool to apply during a particular backup system: gzip? bzip2? any other?

The testing environment

In order to test several formats and tools, I created a .csv file (comma-separated values) that was 3,700,635,579 bytes in size by transforming a recent dump of all the OpenStreetMap nodes of the European portion of Spain. It had a total of 46,741,126 rows and looked like this:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250 …

To ensure that your MySQL Database works as efficiently as possible, it is important to know how to handle error messages and warnings.

Error messages have three components:

• A MySQL-specific error code, such as 1146
• A SQLSTATE error code. These codes are defined by standard SQL and the ODBC standard.
• A text message that describes the problem

MySQL Server generates a warning when it is not fully able to comply with a request or when an action has possibly unintended side effects. You can display these warnings with the SHOW WARNINGS statement.

To learn about handling error messages and warnings along with other developer topics, consider taking the MySQL for Developers training course.

Infrequent SQL developers often get confused about when to put parentheses and/or aliases on derived tables. There has been this recent Reddit discussion about the subject, where user Elmhurstlol was wondering why they needed to provide an alias to the derived table (the subselect with the UNION) in the following query: SELECT AVG(price) AS AVG_PRICE … Continue reading Should I Put That Table Alias or Not? →

“There are four things that motivate open source development teams:
1. The challenge/puzzle of programming, 2. Need for the software, 3. Personal advancement, 4. Belief in open source” — Bruce Momjian.

On PostgreSQL and the challenges of motivating and managing open source teams, I have interviewed Bruce Momjian, Senior Database Architect at EnterpriseDB, and Co-founder of the PostgreSQL Global Development Group and Core Contributor.

RVZ

Q1. How did you manage to transform PostgreSQL from an abandoned academic project into a commercially viable, now enterprise relational database?

Bruce Momjian: Ever since I was a developer of database …

While working with an error that my students surfaced in the Oracle Database 12c, I blogged about the limit of using a subquery in an Oracle INSERT statement, and I discovered something when retesting it in MySQL. It was a different limitation. I was also surprised when I didn’t find any mention of it through a Google search, but then I may just not have the right keywords.

The original test case uses a subquery to create a multiple row result set for an INSERT statement. Unfortunately, I discovered that you can’t always embed a UNION or UNION ALL inside a subquery. At least, you can’t when you call the subquery inside an INSERT statement. For example, let’s create a DESTINATION table and then we’ll try to insert records with a query that …

Being a terminal fan myself, I usually find myself running queries in the mysql client instead of a UI interface as it is much faster. You get to see the results instantaneously.

One thing which is pretty tedious is editing a big query again after once running it as the whole multi-line formatted query now appears on a single line, thus reducing its readability.

But no problems, you can edit your query from a file and run the file from your mysql client terminal as many times as you want with as many edits.

To do so, follow the below steps:

1. Open your terminal and cd into the folder you want to store our sample mysql file. Then save your query in a sample file called my_query.sql

$ cd /path/to/folder
$ vim my_query.sql

Save a sample query like:

SELECT * FROM employees
WHERE type LIKE …

I’ve previously covered MySQL 5.6 on POWER (with patch), MySQL 5.6 Performance on POWER8 (spoiler: new performance record) and MySQL 5.7 on POWER.

Of course, The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions. Also, these numbers should be considered preliminary, but trust me – I did get them and it’s not April 1st.

From my last post, you saw that with my preliminary patch for MySQL 5.7 to work on POWER, we could easily match the previous record for sysbench point select …