create procedure d( )
begin
DECLARE a int;
set a=592;
while a<699
do
insert into t_electronic_identify(id,ba_num,enterprise_name,ba_website,accept_dept,state)values(CONCAT('2c9295423fc1f83f013fc20024eb0001',a),'02801','仲鑫金属制品有限公司',concat('http://www.nffuid.com',a),'011215020000','3');
set a=a+1;
end while;
end
call d();
sqlserver：
create procedure d
as
declare @a int
set @a=5
while @a<15
begin insert into twitter.dbo.twi_article(id,topic_id,title,publish_time,source,author,content,click_count,op_userid,op_time,reply_count) values(@a,'17','bbb','2014-06-03 14:01:09.000','laiyuuan','zuoz','<p>内容</p>','2','19','2014-06-05 14:11:58.300','0');
set @a=@a+1
end
-调用、执行存储过程
exec d;
之前begin的位置放在while前，导致@a值一直还是等于5。。。。
create or replace procedure "adduser"
as
num int;
Begin
num:=1;
while num<10 loop
insert into KHUSER(ID,NAME,ALIAS,PASSWORD,ROLE_ID,ACTIVE,CUSTOMER_ID,USERTYPE,JOBGROUPID)
values(seq_KHUSER.nextval,concat('zdd',CAST(num as varchar(50))),concat('zdd',CAST(num as varchar(50))),'D925C2638454AC42D278CA24F85D4634','1000','1','0','0','5,6,7,8,9,10,11,12,13,14,15,16');
num:=num+1;
end loop;
end;
注意点：1.concat 2 varchar要加字符数，不然提示缺少符号
3有的时候，我们有需要将由不同栏位获得的资料串连在一起。每一种资料库都有提供方法来达到这个目的：
- MySQL: CONCAT()
- Oracle: CONCAT(), ||
- SQL Server: +
存储过程：(sql server 和mysql 不一样)
Create PRocedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
sql_statement 调用执行：exec procedure_name
解释:
output：表示此参数是可传回的
with {recompile|encryption}
recompile:表示每次执行此存储过程时都重新编译一次
encryption:所创建的存储过程的内容会被加密
CREATE PROCEDURE `meizi`(in i integer)
begin
while i <13 do
begin
insert into t_services_help(question,answer,old_num)
VALUES(i,'中文',i);
set i=i+1;
end;
end while;
end;
call meizi(3);
create proc insert_book
@param1 char(10),@param2 char(10),@param3 money,@param4 money output-----有money这种数据类型？
as
insert book(编号，书名，价格)values(@param1,@param2,@param3)
select @param4=sum(价格) from book
go
declare @total_price money
exec insert_book '003','Delphi 控件开发指南',$100,@total_price -----执行语句后面加参数？？
print '总金额为'+convert（varchar，@total_price）
go
my sql:
DECLARE @i int
set @i=3
while@ <13
begin
insert into t_services_help (CAST(@i as varchar),'在常规选项卡中名称左边的复选框来角色的成员因此可以在这里选择多个服务
',@i)
set @i=@i+1
end
DECLARE @i int
set @i=3
while@ <13
begin
insert into t_services_help(question,answer,old_num)VALUES(CAST(@i as varchar),'在常规选项卡中的登录名所属的固定服务器角色是多个固定服务器角色的成',@i)
set @i=@i+1
end
create procedure del3
as
declare @a int
set @a=0
begin
while(@a<83)
begin
delete from
dbo.local_hot_base_info_@a;
set @a=@a+1;
end
end
execute del3(直接拼接表部可以.所以用以下两种方法了)
select distinct 'delete from dbo.local_hot_base_info_' +substring(cast(t.code as char),1,2)+';' from sys_area t
create procedure del5
as
DECLARE @id INT
declare @tablename nvarchar(100)
declare @tablename1 nvarchar(100)
declare @tablename2 nvarchar(100)
set @id=0
while @id<83
begin
set @tablename1='local_hot_info_'+cast(@id as nvarchar(8))
set @tablename2='local_hot_word_'+cast(@id as nvarchar(8))
set @tablename='local_hot_base_info_'+cast(@id as nvarchar(8))
Exec('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tablename+']'') AND type in (N''U'')) BEGIN truncate table
'+@tablename+'END')
Exec('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tablename1+']'') AND type in (N''U'')) BEGIN truncate table
'+@tablename1+'END')
Exec('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tablename2+']'') AND type in (N''U'')) BEGIN truncate table
'+@tablename2+'END')
set @id=@id+1
end
del5
select * from INFORMATION_SCHEMA.TABLES
convert() concat() cast()
