存储过程样例

#-------in参数：条件---------------------
drop procedure if exists selectByID;
CREATE PROCEDURE `selectByID`(in _id int)
begin
select * from zt_bug where id=_id;
end;


call selectByID(10083);
#---------------------------------------




#------in out-----------------------
drop procedure if exists selectByID;
CREATE PROCEDURE `selectByID`(in _id int,out _put varchar(30))
begin
select title  into _put from zt_bug where id=_id;
end;


call selectByID(10083,@value);


select @value as '返回值';
#---------------------------------------




#-------in参数：列名 + 条件---------------------
CREATE PROCEDURE `testabc`(in _id int, in _col varchar(20))
begin
declare strSQL varchar(200);
declare sqlRun varchar(200);


set @strSQL = concat( 'select  ',_col, '  from zt_bug where id=',_id);
prepare sqlRun from @strSQL;
execute sqlRun;
end;


call testabc(10082,'title' )
#---------------------------------------




#-------in参数：表名 + 列名 + 条件---------------------
CREATE PROCEDURE `testabc`(in _tableName varchar(100), in _colName varchar(20),in _id int)
begin
declare strSQL varchar(200);
declare sqlRun varchar(200);
set @strSQL = concat( 'select  ',_colName , '  from ', _tableName,' where id=',_id);
prepare sqlRun from @strSQL;
execute sqlRun;
end;


call testabc('zt_bug','id',10082 )
#---------------------------------------




#-------out参数：sql---------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `testabc2`(out _value varchar(200))
begin
declare strSQL varchar(200);
declare sqlRun varchar(200);


select count(*) into _value from zt_bug;
end;


call testabc2(@name);
select @name;
#---------------------------------------




#-------out参数：concat ---------------------------
drop procedure if exists testOut;
CREATE PROCEDURE `testOut`(in _in varchar(20),out `_value` varchar(200))
begin
# set @stmt = concat('select  count(*)  into  @_total from information_schema.columns  where table_name=\'zt_bug\'');
set @stmt = concat('select  count(*)  into  @_total from information_schema.columns  where table_name="zt_bug"');
prepare sqlRun from @stmt;
execute sqlRun;
set _value =@_total;
DEALLOCATE PREPARE sqlRun;
end;


call testOut('tmp',@myvalue);
select @myvalue;
#---------------------------------------




#-----查询返回值------------------------
drop procedure if exists addBugCol;
create procedure addBugCol( 
in _tableName varchar(50),
in _columnName varchar(50)
)
begin
declare strSQL varchar(200);
declare sqlRun varchar(500);


select count(*)  into @_put  from INFORMATION_SCHEMA.COLUMNS where table_name =_tableName and column_name= _columnName ;
  select @_put;
  
end;


call addBugCol('zt_bugcus','id' );
#---------------------------------------




#-------查询构造的SQL---------------------------
drop procedure if exists testSQLSyntax;
create procedure testSQLSyntax( 
in _tableName varchar(50),
in _columnName varchar(50)
)
begin
declare strSQL varchar(200);
declare sqlRun varchar(500);
set @strSQL = "";
set @strSQL = concat('alter table  ', _tableName ,'  add column ', _columnName , ' varchar(50)' );
select @strSQL;
end;


call testSQLSyntax('zt_bugcus' ,'myCol' );
#---------------------------------------




#-------在表中不存在某列则增加---------------------------
drop procedure if exists colIsExist;
create procedure colIsExist( 
in _tableName varchar(50),
in _columnName varchar(50)
)
begin
declare strSQL varchar(200);
declare sqlRun varchar(500);


select count(*)  into @colName from INFORMATION_SCHEMA.COLUMNS where table_name =_tableName and column_name= _columnName ;


if (0 = @colName) then
set @strSQL = "";
set @strSQL = concat('alter table  ', _tableName ,'  add column ', _columnName , ' varchar(50)' );
prepare sqlRun from @strSQL;
execute sqlRun;
end if;
end;


call colIsExist('zt_bugcus','myNewCol' );


desc zt_bugcus
#---------------------------------------




#---传递关键字作为参数-----------
drop procedure if exists addBugCol;
create procedure addBugCol( 
in _tableName varchar(50),
in _columnName varchar(50)
)
begin
declare strSQL varchar(200);
declare sqlRun varchar(500);


select count(*)  into @_rowCount  from INFORMATION_SCHEMA.COLUMNS where table_name =_tableName and column_name= _columnName ;




if (0 = @_rowCount )then
set @strSQL = "";
set @strSQL = concat('alter table  ', _tableName ,'  add column ', _columnName , '  varchar(50)' );
prepare sqlRun from @strSQL;
execute sqlRun;
end if;
end;


#因为analyze为关键字，无法传递，需要如下操作
call addBugCol('zt_bugcus','`analyze`' );