A stored procedure and shell to operate MySQL

1. Stored Procedure name. SQL

Use test; drop procedure if exists deleteconfigifdelcamera; delimiter // create procedure deleteconfigifdelcamera (in cameraid integer) Begin declare camera_id integer; select ID into camera_id from camera where id = cameraid; if (camera_id> 0) Then delete vehicleinfo, vehicletype, vehiclelicense, bodycolor from vehicleinfo left join vehicletype on vehicletype. id = vehicleinfo. vehicletype_id join vehiclelicense on vehiclelicense. id = vehicleinfo. vehiclelicense_id join bodycolor on bodycolor. id = vehicleinfo. bodycolor_id where vehicleinfo. ID <record_id; delete from baseconfig where baseconfig. cameraid = camera_id; delete from brightcontrolconfig where brightcontrolconfig. cameraid = camera_id; delete from laneinfo where laneinfo. cameraid = camera_id; delete from lightandlanerelation where lightandlanerelation. laneid in (select Min (laneid) from laneinfo where laneinfo. cameraid = camera_id); Delete from osdconfig where osdconfig. cameraid = cameraid; delete from siglightconfig where siglightconfig. camearid = camera_id; delete from vpalgorithmconfig where vpalgorithmconfig = camera_id; delete from serialconfig where; end if; end ;//

Delimiter;

 

2. shell script name. Sh

#! /Bin/bashmysql -- user = root <SQL _tables.sqlmysql -- user = root <insertbodycolor. sqlmysql -- user = root <insertvehiclelicense. sqlmysql -- user = root <insertvehicletype. sqlmysql -- user = root <deletehistoryrecord. sqlmysql -- user = root <insertuploadqueue. sqlmysql -- user = root <inserthistoryrecord. sqlmysql -- user = root <inserttrafficdata. SQL

 

The beginning of SQL _table. SQL is

Drop database if exists test; Create Database test default Character Set utf8; Connect test;

Drop table if exists vehicletype;

 

 

How to test the stored procedure:

Name of the call Stored Procedure (parameter );