当前位置:酷酷问答>百科知识>SQL之PROCEDURE(存储过程)

SQL之PROCEDURE(存储过程)

2024-09-13 07:27:36 编辑:zane 浏览量:519

SQL之PROCEDURE(存储过程)

的有关信息介绍如下:

SQL之PROCEDURE(存储过程)

SQL之PROCEDURE(存储过程)的使用方法

create proc | procedure pro_name

[{@参数数据类型} [=默认值] [output],

{@参数数据类型} [=默认值] [output],

....

]

as

SQL_statements

if (exists (select * from sys.objects where name = 'proc_get_student'))

drop proc proc_get_student

go

create proc proc_get_student

as

select * from student;

--调用、执行存储过程

exec proc_get_student;

alter proc proc_get_student

as

select * from student;

if (object_id('proc_find_stu', 'P') is not null)

drop proc proc_find_stu

go

create proc proc_find_stu(@startId int, @endId int)

as

select * from student where id between @startId and @endId

go

exec proc_find_stu 2, 4;

if (object_id('proc_findStudentByName', 'P') is not null)

drop proc proc_findStudentByName

go

create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')

as

select * from student where name like @name and name like @nextName;

go

exec proc_findStudentByName;

exec proc_findStudentByName '%o%', 't%';

if (object_id('proc_getStudentRecord', 'P') is not null)

drop proc proc_getStudentRecord

go

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;

go

--

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;

if (object_id('proc_temp', 'P') is not null)

drop proc proc_temp

go

create proc proc_temp

with recompile

as

select * from student;

go

exec proc_temp;

if (object_id('proc_temp_encryption', 'P') is not null)

drop proc proc_temp_encryption

go

create proc proc_temp_encryption

with encryption

as

select * from student;

go

exec proc_temp_encryption;

exec sp_helptext 'proc_temp';

exec sp_helptext 'proc_temp_encryption';

if (object_id('proc_cursor', 'P') is not null)

drop proc proc_cursor

go

create proc proc_cursor

@cur cursor varying output

as

set @cur = cursor forward_only static for

select id, name, age from student;

open @cur;

go

--调用

declare @exec_cur cursor;

declare @id int,

@name varchar(20),

@age int;

exec proc_cursor @cur = @exec_cur output;--调用存储过程

fetch next from @exec_cur into @id, @name, @age;

while (@@fetch_status = 0)

begin

fetch next from @exec_cur into @id, @name, @age;

print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);

end

close @exec_cur;

deallocate @exec_cur;--删除游标

if (object_id('pro_page', 'P') is not null)

drop proc proc_cursor

go

create proc pro_page

@startIndex int,

@endIndex int

as

select count(*) from product

;

select * from (

select row_number() over(order by pid) as rowId, * from product

) temp

where temp.rowId between @startIndex and @endIndex

go

--drop proc pro_page

exec pro_page 1, 4

--

--分页存储过程

if (object_id('pro_page', 'P') is not null)

drop proc pro_stu

go

create procedure pro_stu(

@pageIndex int,

@pageSize int

)

as

declare @startRow int, @endRow int

set @startRow = (@pageIndex - 1) * @pageSize +1

set @endRow = @startRow + @pageSize -1

select * from (

select *, row_number() over (order by id asc) as number from student

) t

where t.number between @startRow and @endRow;

exec pro_stu 2, 2;

版权声明:文章由 酷酷问答 整理收集,来源于互联网或者用户投稿,如有侵权,请联系我们,我们会立即处理。如转载请保留本文链接:https://www.kukuwd.com/article/33627.html
热门文章