1樓：東坡**站
一般分為十種情況，每種語法各不相同：
1、 建立語法
create proc | procedure pro_name
[ [=預設值] [output],
[=預設值] [output],
....]as
sql_statements
2、 建立不帶引數儲存過程
--建立儲存過程
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
gocreate proc proc_get_student
asselect * from student;
--呼叫、執行儲存過程
exec proc_get_student;
3、 修改儲存過程
--修改儲存過程
alter proc proc_get_student
asselect * from student;
4、 帶參儲存過程
--帶參儲存過程
if (object_id('proc_find_stu', 'p') is not null)
drop proc proc_find_stu
gocreate proc proc_find_stu(@startid int, @endid int)
asselect * from student where id between @startid and @endid
goexec proc_find_stu 2, 4;
5、 帶萬用字元引數儲存過程
--帶萬用字元引數儲存過程
if (object_id('proc_findstudentbyname', 'p') is not null)
drop proc proc_findstudentbyname
gocreate proc proc_findstudentbyname(@name varchar(20) = '%j%', @nextname varchar(20) = '%')
asselect * from student where name like @name and name like @nextname;
goexec proc_findstudentbyname;
exec proc_findstudentbyname '%o%', 't%';
6、 帶輸出引數儲存過程
if (object_id('proc_getstudentrecord', 'p') is not null)
drop proc proc_getstudentrecord
gocreate 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 *** = @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;
7、 不快取儲存過程
--with recompile 不快取
if (object_id('proc_temp', 'p') is not null)
drop proc proc_temp
gocreate proc proc_temp
with recompile
asselect * from student;
goexec proc_temp;
8、 加密儲存過程
--加密with encryption
if (object_id('proc_temp_encryption', 'p') is not null)
drop proc proc_temp_encryption
gocreate proc proc_temp_encryption
with encryption
asselect * from student;
goexec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
9、 帶遊標引數儲存過程
if (object_id('proc_cursor', 'p') is not null)
drop proc proc_cursor
gocreate proc proc_cursor
@cur cursor varying output
asset @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);
endclose @exec_cur;
deallocate @exec_cur;--刪除遊標
10、 分頁儲存過程
---儲存過程、row_number完成分頁
if (object_id('pro_page', 'p') is not null)
drop proc proc_cursor
gocreate proc pro_page
@startindex int,
@endindex int
asselect 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
gocreate 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
) twhere t.number between @startrow and @endrow;
exec pro_stu 2, 2;
希望採納！！
2樓：匿名使用者
看著語法沒有問題。建議看一下 p_course1表和course表字定義的是否一樣的。
如果直接這樣插入。要求倆表的表結構必須是一致的
sql 儲存過程變數，SQL 儲存過程變數
整個語句塊建立了乙個儲存過程，其中的查詢使用了儲存過程的引數。你可以通過直接執行儲存過程，將值傳給傳出過程的引數，來實現執行查詢。例如 execute dbo p sele sp a 1 aaa bbb 如果希望直接執行查詢語句，則需要用declare關鍵字事先宣告變數。如下 宣告變數 declar...
關於儲存過程in的問題
if exists select from sysobjects where xtype p and name proc update messagetouser drop procedure proc update messagetouser gocreate procedure proc upd...
寫SQL語句或者儲存過程，使用SQL語句建立儲存過程
試下，我也不知道行不行 oracle10g以上版本提供wmsys.wm concat select wmsys.wm concat id aa,wmsys.wm concat mawb bb from tablename where rownum 10 select ltrim max sys co...