存储过程练习

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> insert   into  person  values ( 1 , ' zdw ' , ' zdw ' , ' test1 ' )
insert   into  person  values ( 2 , ' test ' , ' test ' , ' test2 ' )
insert   into  person  values ( 3 , ' admin ' , ' admin ' , ' admin3 ' )
/* 在存储过程中使用子查询 */

create   procedure  person_sub_query
(
  @id   int
)
as
   select   *   from  person  where  id  <  ( select   count ( * )  from  person)
go


execute  person_sub_query  2

/* 在存储过程中修改参数值，使用多个查询语句： */
create   procedure  person_multi_query
(
  @id   int
)
as
   select   @id   =  ( select   count ( * )  from  person  where  id  >   @id )
   select   @id   =   @id   -   1
   select   *   from  person  where  id  =   @id
go

execute  person_multi_query  1

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> /* 创建表 */
create   table  person
(
  id  int   primary   key ,
  username  varchar ( 50 )  not   null ,
  password  varchar ( 20 )  not   null ,
  address  varchar ( 200 )  not   null
)
/* 增加一条记录的存储过程 */
create   procedure  proc_person
(
   @id   int  ,
   @username   varchar ( 50 ),
   @password   varchar ( 20 ),
   @address   varchar ( 200 ) 
)
as
insert   into  person(id,username,password,address)  values ( @id , @username , @password , @address )

go
/* 传值顺序是你声明变量时的顺序 */
exec  proc_person  1 , ' admin ' , ' admin ' , ' bj '

select   *   from  person;

/* 修改数据的存储过程 */
create   procedure  proc_person_update
(
  @id   int ,
  @username   varchar ( 50 ),
  @password   varchar ( 50 )
)
as
   update  person  set  username = @username  , password = @password   where  id = @id
go

execute  proc_person_update  1 , ' test ' , ' test '

select   *   from  person

/* 删除数据的存储过程 */

create   procedure  proc_person_del
(
  @id   int
)
as
   delete   from  person  where  id  =   @id
go

execute  proc_person_del  1

select   *   from  person

々上善若水々 2008-04-10 10:11 发表评论