SQL å建åå¨è¿ç¨ï¼
ä¸ãåºç¡è¯æ³ï¼
create proc | procedure pro_name
[{@åæ°æ°æ®ç±»å} [=é»è®¤å¼] [output],
{@åæ°æ°æ®ç±»å} [=é»è®¤å¼] [output],
....
]
as
SQL_statements
äºã常è§å建åå¨è¿ç¨å®ä¾
1ãå建ä¸å¸¦åæ°åå¨è¿ç¨
create proc proc_get_student
as
select * from student;
æ§è¡åå¨è¿ç¨ï¼
exec proc_get_student;
2ã 带ååå¨è¿ç¨
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
æ§è¡åå¨è¿ç¨ï¼
exec proc_find_stu 2, 4;
3ã 带éé
符åæ°åå¨è¿ç¨
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
æ§è¡åå¨è¿ç¨ï¼
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
4ã 带è¾åºåæ°åå¨è¿ç¨
create proc proc_getStudentRecord(
@id int, --é»è®¤è¾å
¥åæ°
@name varchar(20) out, --è¾åºåæ°
@age varchar(20) output--è¾å
¥è¾åºåæ°
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
æ§è¡åå¨è¿ç¨ï¼
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;