Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 65 Next 30 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   | int(11)     | NO   | PRI | NULL    | auto_increment |
| account_name | varchar(50) | NO   |







  [Read more...]
Use of loops in MySQL stored procedures
+1 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.

Simple counter from 1 to 10:

Store below stored procedure in a file named my_loop.sql

DELIMITER $$


















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

BEGIN














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

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE















  [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 redmine_nsp.projects p ON e.project_id = p.id
	WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07'
 GROUP BY
  [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 to produce reports based on hierarchical data. And thus is an alternative to Oracle’s CONNECT BY. MySQL does not natively support WITH RECURSIVE, but it is easy to emulate it with a generic, reusable stored

  [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 ...
MONTH INT, # January, February, ...
SALES INT # how much we sold on that month of that year
);
Now I want to know the sales trend (increase/decrease), year after year:

SELECT D1.YEAR, (CASE WHEN












  [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  Warnings: 0
 
mysql> SELECT *
  [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 these procedures in a database called tools, but you can still choose different location.

So when call the procedure will have

  [Read more...]
OpenCode: MySQL procedures + python + shell code repositories now public
+1 Vote Up -0Vote Down

I write a fair number of scripts on this site and have posted a lot of code over the years. Generally if I am not pasting the code to be viewed on the webpage then I link to a file that a user can download; which leads to a lot of mish-mash code that doesn’t have a home. I’ve always kept the code files in a private SVN repo over the years but have recently moved them all to BitBucket Git repositories. So here they are: lots of code samples and useful bits of programming to save time.

Generic Shell Scripts: https://bitbucket.org/themattreid/generic-bash-scripts/src
Generic Python Scripts: https://bitbucket.org/themattreid/generic-python-scripts/src
Generic MySQL Stored Procs:

  [Read more...]
MySQL: a convenient stored procedure for memory usage reporting
+1 Vote Up -0Vote Down

If you’ve ever been troubleshooting on the MySQL command line and needed to quickly see how much memory is being used then you’ve probably noticed that there are no built in commands to give you this data. Unlike other enterprise databases MySQL doesn’t have a very robust management system built in to help make the DBA’s life easier. It doesn’t come with built in Stored Procedures to report on usage statistics or generate handy reports; so we have to code them and import them to MySQL — no relying on Oracle to help us out here.

So, here’s a stored procedure that can be imported to MySQL and run whenever you need to see the memory usage statistics. Installation and usage info is built into the SP below. The SP can also be downloaded from the repo:

  [Read more...]
Using a mysql cursor in a stored procedure
+0 Vote Up -0Vote Down

Example using a mysql cursor in a stored procedure. Mysql Cursor example DELIMITER $$   DROP PROCEDURE IF EXISTS mysql_cursor_example $$ CREATE PROCEDURE mysql_cursor_example ( IN in_name VARCHAR(255) ) BEGIN -- First we declare all the variables we will need DECLARE l_name VARCHAR(255); -- flag which will be set to true, when cursor reaches end [...]
Manage hierarchical data with MySQL stored procedures
+1 Vote Up -0Vote Down
Below you will find all code to create the table and the stored procedures to manage hierarchical trees in MySQL. All code is documented and can be downloaded in a zip file.
Use the strict sql mode when compiling a MySQL stored procedure to avoid unexpected errors.
+0 Vote Up -0Vote Down
Setting the mode to STRICT_ALL_TABLES when you compile your stored procedure or function can prevent a lot of subtle bugs in your MySQL application. Consider this example: DELIMITER // CREATE FUNCTION test(p_first TINYINT, p_second TINYINT) RETURNS TINYINT BEGIN     DECLARE v_result TINYINT;     SET v_result := p_first + p_second;     RETURN v_result; END// DELIMITER ; compile it and then test the funcion: mysql> [...]
Avoid locks when storing counters in MySQL
+0 Vote Up -0Vote Down
A common problem with storing counters in a table is that every time your application update your counter, a row lock needs to be set on the row the table. If your application has a need for storing counters you can use this package which contains the scripts for a table and some stored procedures to handle managing the counters.
It's alive!
+3 Vote Up -0Vote Down
LinkedIn has what they call "inDays" where employees may so something interesting which may not be directly related to their day job. I spent my inDay by porting my old WL820 project (External Language Stored Procedures) to MariaDB 5.3. The code, as usual, is available on LaunchPad ... To get the branch, simply do: bzr branch lp:~atcurtis/maria/5.3-wl820 The test cases pass... I haven't tested
Custom auto increment values
+0 Vote Up -0Vote Down

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

  • Create the table to store the current sequence values:
    CREATE TABLE _sequence
    (
        seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
        seq_group VARCHAR(10) NOT NULL,
        seq_val INT UNSIGNED NOT NULL
    );
  • Create a function to get and increment the current value:
    delimiter //
    DROP FUNCTION IF EXISTS getNextCustomSeq//
    CREATE FUNCTION getNextCustomSeq
    (
        sSeqName
  •   [Read more...]
    A better SHOW TABLE STATUS
    +4 Vote Up -0Vote Down

    From command line we have the entire MySQL server on hands (if we have privileges too of course) but we don’t have a overall overview, at this point the show table status command is every useful, or not?.

    This is what we get when run show table status in a standard 80×25 terminal screen:

    We can maximize the terminal window and decrease font size, but not all the time we need that lots of info. Some time ago I develop a stored procedure to get a global overview including functions and stored procedures. The result is pretty comprehensible:

      [Read more...]
    Using LOAD DATA INFILE with Stored Procedure Workaround-MySQL
    +2 Vote Up -0Vote Down
    Okay! So here we will use Load Data syntax to load file into MySQL Server in a Stored procedure. Yep! It’s a workaround. Download MySQL UDF: [root@localhost kedar]# wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz [refer: http://www.mysqludf.org/] Extract and Install: [root@localhost kedar]# tar -xzvf lib_mysqludf_sys_0.0.3.tar.gz install.sh lib_mysqludf_sys.c lib_mysqludf_sys.html lib_mysqludf_sys.so lib_mysqludf_sys.sql Makefile [root@localhost kedar]# sh install.sh Compiling the MySQL UDF gcc […]
    Getting a return code from a stored procedure
    +1 Vote Up -0Vote Down

    Sometimes we have some special need for a stored procedure to call another to do something. That is fine, but what if the second stored proc failed for some reason? Maybe you want to halt the first stored procedure (the caller) and not proceed with the work until the problem is verified and resolved. So How do you do it?

    Simple, get a return code from the called procedure to determine if it worked or not!

    Here’s a sample piece of code to explain better:


    DROP PROCEDURE IF EXISTS `test`.`testing123`;
    DELIMITER $$

    CREATE
    PROCEDURE `test`.`testing123`(OUT a INT)
    BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SET a=2;
    END;
    SET a=0;

    # toggle the below as comment or not to see the call at the bottom working
    #










      [Read more...]
    MySQL stored procedure debugging, can I sue for going insane?
    +5 Vote Up -0Vote Down

    Lets paint the picture:

    Scenario part 1 : Migrating a couple thousand stored procedures from database technology X to mysql
    Scenario part 2 : Legacy system where the people who wrote it left a long time ago
    Scenario part 3 : Developers sure can get real creative and invent all kinds of ways to get data (eg: having a stored proc which formulates a big query using concat after going through a bunch of conditions (fair enough), BUT the parts making up the different queries are stored in a table on a database rather than within the stored proc itself) … talk about KIS – Keep it simple!!
    Scenario part 4 : This stored proc references 18 tables, 4 views, and another two stored procedures on 5 databases

    Now close your eyes and try to imagine that for a few seconds, nah kidding don’t want you to hurt




      [Read more...]
    Using MySQL Partitioning Instead of MERGE Tables
    +8 Vote Up -0Vote Down

    One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.

    First, a simple table, not partitioned (yet):

    use test;
    DROP TABLE IF EXISTS my_part;
    CREATE TABLE IF NOT EXISTS my_part (
      id int NOT NULL,
      creationDate datetime NOT NULL,
      PRIMARY KEY (id,creationDate)
    ) ENGINE=InnoDB;
    

    In real, life there is more to the table than just id and creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.

    Now, add the partition definition. This can be done in the


      [Read more...]
    MySQL Stored procedure to Generate-Extract Insert Statement
    +1 Vote Up -0Vote Down
    A lot of places I saw people asking for ways to generate Insert statements. We do have GUI Tools which can extract insert statements for us readily. of the time I choose the MySQLDump way to generate insert statements. mysqldump -uroot -ppassword –complete-insert –no-create-info DATABASE TABLENAME > TABLENAME.sql But mind is very unstable and hungry, […]
    One last bit of evil….
    +2 Vote Up -0Vote Down
    You can store things for later! drizzle> select libtcc("#include <string.h>\n#include <stdlib.h>\nint foo(char* s) { char *a= malloc(1000); return snprintf(s, 100, \"%p\", a); }") as RESULT;
    +-----------+
    | RESULT    |
    +-----------+
    | 0x199c610 |
    +-----------+
    1 row in set (0 sec)
    drizzle> select libtcc("#include <string.h>\n#include <stdlib.h>\nint foo(char* s) { char *a= 0x199c610; strcpy(a, \"Hello World!\"); strcpy(s,\"done\"); return strlen(s); }") as result;
    +--------+
    | result |
    +--------+
    | done   |
    +--------+
    1 row in set (0.01 sec)
    drizzle> select libtcc("#include <string.h>\n#include <stdlib.h>\nint foo(char* s) { char *a= 0x199c610; strcpy(s, a); return strlen(s); }") as result;
    +--------------+
    | result       |
      [Read more...]
    Stored Procedures/Functions for Drizzle
    +1 Vote Up -0Vote Down

    Previously, in “Thoughts on Thoughts on Drizzle” I theorized that one of the major reasons why we did not see lots of people jumping at stored procedures in MySQL (http://www.mysql.com) was that it wasn’t in their native language (for lack of a better term). We’ve seen External Language Stored Procedures for MySQL that let you write stored procedures in some other languages…. but I felt something was missing.

    Firstly, I wanted a language I was really familiar with and comfortable writing complex things in.

    Secondly, it should be compiled so that it runs as fast as possible.

    Thirdly, it shouldn’t just be linking to a pre-compiled

      [Read more...]
    Ideas for select all columns but one mysql stored procedure
    +0 Vote Up -0Vote Down
    Assume we’ve a table with 100 rows and we need to select all columns but one. The problem is headache of actually typing out all 99 required columns!! Solutions / Ideas to above problem are: Ignorance is bliss. Select all(*) and ignore the column. Manually type column names or manage it with copy paste! Create […]
    Stored procedure to add-remove prefix by rename table mysql
    +1 Vote Up -0Vote Down
    Here is one more procedure – (this time) for mass renaming of table. Adding and Removing table-name prefixes A friend of mine was renaming 100+ tables by using replace methods in notepad. I showed em a bit better editor: Editplus and then I thought of rescue rest of those who are still interested in some […]
    Stored Procedure For Finding Columns In MySQL
    +4 Vote Up -0Vote Down

    Looking for instances particular column in a large schema can be a pain. Fortunately the information schema makes this pretty easy, if your columns have a consistent naming convention.

    SELECT table_schema, table_name, column_name
    FROM information_schema.columns
    WHERE column_name LIKE '%some_name%';
    

    Now, if we want to wrap this up into an easy to use stored procedure, we can do something like this:

    drop procedure find_column;
    delimiter //
    CREATE PROCEDURE find_column(c varchar(255))
    begin
    	SET @a = CONCAT("%", c, "%");
    	SELECT table_schema, table_name, column_name, column_type
    		FROM information_schema.columns
    		WHERE column_name LIKE @a;
    end
    //
    delimiter ;
    

    We need to use the concat statement in order to

      [Read more...]
    MySQL Stored procedure – Split Delimited string into Rows
    +0 Vote Up -0Vote Down
    This procedure will split  a “;” separated column in to new fields preserving ids. This is very specific problem, lets check it with example. Consider a sample table test: And we want output as follows: So again I wrote a procedure. Procedure will read a data from “tmp” table and will split data by my_delimiter […]
    Showing entries 1 to 30 of 65 Next 30 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.