Linux, FreeBSD, Juniper, Cisco / Network security articles and troubleshooting guides

FAQ
It is currently Wed May 24, 2017 1:11 pm


Mysql Application & Query Optimization, Questions, Indexes, Bugs.

Author Message
debuser
Post  Post subject: Mysql> stored procedure for inserting in multiple tables \w common key  |  Posted: Tue Nov 22, 2011 5:15 pm

Joined: Thu Aug 06, 2009 2:48 am
Posts: 105

Offline
 

Mysql> stored procedure for inserting in multiple tables \w common key

Below is an example of a mysql stored procedure that:
1) drops the procedure if it already exists
2) creates procedure and defines the fields that it accepts
3) declares an internal variable
4) inserts a row in multiple tables having a common key all tables using last_insert_id() from the first table as their first column's value
5) sets the internal variable to the last_insert_id()
6) selects the internal variable, returning it to the calling handler

Code:
DELIMITER //
DROP PROCEDURE IF EXISTS insert_multiple_tables
//
CREATE PROCEDURE insert_multiple_tables(product_hash char(32), user_id smallint, product_ip char(10), product_dir ENUM('p1','p2','p3','p4'), product_name varchar(100), product_type varchar(30), product_ext varchar(10), product_size bigint)
BEGIN
   DECLARE RETURN_VAL INT UNSIGNED DEFAULT 0;
START TRANSACTION;

   INSERT INTO f (product_id, product_hash, user_id)
     VALUES('', product_hash, user_id);

   INSERT INTO product_info (product_id, product_upload_date, product_ip )
     VALUES(LAST_INSERT_ID(), NOW(), product_ip);

   INSERT INTO product_location (product_id, product_dir, product_node)
     VALUES(LAST_INSERT_ID(), product_dir, '0');

   INSERT INTO product_properties(product_id, product_name, product_type, product_ext, product_size)
     VALUES (LAST_INSERT_ID(), product_name, product_type, product_ext, product_size);

   INSERT INTO product_status(product_id, product_private)
     VALUES (LAST_INSERT_ID(), '0');

   INSERT INTO product_download(product_id, product_d_date, product_d_ip)
     VALUES (LAST_INSERT_ID(), CURDATE(), '0');

   SET RETURN_VAL = LAST_INSERT_ID();

   SELECT RETURN_VAL;

COMMIT;
END//
DELIMITER ;





Top
debuser
Post  Post subject: Re: Mysql> stored procedure > inserting in multiple files \w commond key  |  Posted: Tue Nov 22, 2011 5:17 pm

Joined: Thu Aug 06, 2009 2:48 am
Posts: 105

Offline
To load the above stored procedure in your mysql database, save it to a file, i.e.: insert_multiple_tables.sql and load it via input redirection:
Code:
# mysql -u user -ppassword db_name <insert_multiple_tables.sql


Top
Display posts from previous:  Sort by  
E-mail friendPrint view

Topics related to - "Mysql> stored procedure for inserting in multiple tables \w common key"
 Topics   Author   Replies   Views   Last post 
There are no new unread posts for this topic. How to use a mysql stored procedure

debuser

1

457

Tue Nov 22, 2011 1:23 am

Darwin View the latest post

There are no new unread posts for this topic. Mysql command to show stored procedures

debuser

0

436

Mon Nov 21, 2011 3:57 pm

debuser View the latest post

There are no new unread posts for this topic. Mysql show stored procedures and functions

mandrei99

0

820

Mon Nov 18, 2013 9:06 am

mandrei99 View the latest post

There are no new unread posts for this topic. MySql doesn't start: libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)

debuser

0

1020

Mon Feb 22, 2010 1:43 pm

debuser View the latest post

There are no new unread posts for this topic. Mysql: add column to table

debuser

1

1578

Fri Oct 08, 2010 1:38 am

sonee4 View the latest post

There are no new unread posts for this topic. Rename MySql table

debuser

1

481

Fri Oct 08, 2010 1:36 am

sonee4 View the latest post

There are no new unread posts for this topic. MySql rename table

admin

2

773

Mon Nov 28, 2011 3:39 am

Darwin View the latest post

There are no new unread posts for this topic. Mysql: Cleaning binary logs

debuser

1

805

Tue May 18, 2010 9:45 am

designeru View the latest post

There are no new unread posts for this topic. Mysql change column's default value

debuser

0

995

Thu Aug 12, 2010 3:43 am

debuser View the latest post

There are no new unread posts for this topic. Mysql drop (remove) unique key

mandrei99

0

818

Wed Jan 14, 2015 7:53 pm

mandrei99 View the latest post

 

Who is online
Users browsing this forum: No registered users and 1 guest
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum
Jump to:  
News News Site map Site map SitemapIndex SitemapIndex RSS Feed RSS Feed Channel list Channel list


Delete all board cookies | The team | All times are UTC - 5 hours [ DST ]



phpBB SEO