2023年6月21日发(作者：)
SQL如何创建存储过程 谢⼤哥USE CMS
----Use 是跳转到哪个数据库，对这个数据库进⾏操作。GO
----GO向 SQL Server 实⽤⼯具发出⼀批 Transact-SQL 语句结束的信号,相当于提交上⾯的SQL语句。----GO是把t-sql语句分批次执⾏。（⼀步成功了才会执⾏下⼀步,即⼀步⼀个GO）/****** Object: StoredProcedure [dbo].[PROC_four_five_hr] Script Date: 07/30/2018 13:44:55 ******/----这⼀段是介绍本存储过程名字和创建时间----/* */ 是注释掉很多⾏内容的符号----object 对象 的意思----storeprocedure 存储过程 的意思----dbo 是每个数据库的默认⽤户，具有所有者权限----dbo后⾯是存储过程的名称----script date 脚本 创建时间SET ANSI_NULLS ONGO----SET QUOTED_IDENTIFIER ONGO----/*⾸先准备数据,测试存储过程use ssqadm;--创建测试books表create table books_test ( book_id int identity(1,1) primary key, book_name varchar(20), book_price float, book_auth varchar(10));--插⼊测试数据insert into books_test (book_name,book_price,book_auth) values ('论语',25.6,'孔⼦'), ('天龙⼋部',25.6,'⾦庸'), ('雪⼭飞狐',32.7,'⾦庸'), ('平凡的世界',35.8,'路遥'), ('史记',54.8,'司马迁'); select * from books_test;*/
--1.创建⽆参存储过程if (exists (select * from s where name = 'getAllBooks')) drop proc getAllBooksgocreate procedure getAllBooksasbeginselect * from books_test;--调⽤,执⾏存储过程exec getAllBooks;endgo--2、修改存储过程alter procedure getallbooksas
select book_name from books_test;--3、删除存储过程drop procedure getallbooks;go--4、修改存储过程的名称sp_rename getallbooks,proc_get_allbooks;goexec proc_get_allbooks;go--5、创建带参数的存储过程use ssqadm goif (exists (select * from s where name = 'searchbooks'))drop proc searchbooks-- exec searchbooks 1;--执⾏存储searchbooks得到如下结果:gocreate procedure searchbooks (@bookid int)--括号⾥⾯是as
begindeclare @book_id int;----定义⼀个标量变量,只是保证存储过程的完整性，在本存储是多此⼀举的。set @book_id = @bookid;select* from books_test where book_id = @book_id;end;go-- exec searchbooks 1;--执⾏存储searchbooks得到如下结果:--6、创建带两个参数的存储过程use ssqadm goif (exists (select * from s where name = 'book_test2'))drop proc book_test2-- exec book_test2 1;--执⾏存储book_test2得到如下结果:gocreate procedure book_test2 (@bookid int,@bookname varchar(20))--括号⾥⾯是as
begindeclare @book_id int;----定义⼀个标量变量,只是保证存储过程的完整性，在本存储是多此⼀举的。declare @book_name varchar(20);set @book_id = @bookid;set @book_name = @bookname;select* from books_test where book_id = @book_id and book_name = @book_name;end;go-- exec book_test2 1,'论语';--执⾏存储book_test2得到如下结果:--7、创建带有返回值的存储过程use ssqadmgoif (exists (select * from s where name = 'book_test3'))drop proc book_test3gocreate procedure book_test3(@bookid int,@out_book_name varchar(20) output)asdeclare @book_id int;set @book_id = @bookid;beginselect @out_book_name = book_name from books_test where book_id= @book_id;endgo-- --执⾏存储book_test2得到如下结果:--执⾏存储过程declare @out_name varchar(20) --声明⼀个变量⽤来接收执⾏存储过程后的返回值exec book_test3 1,@out_name outputselect @out_name as out_book_name;--as是给返回的列值起⼀个名字 附带另⼀位⼤哥的调⽤--根据loginId查询教员信息if OBJECT_ID('SelectTeacherByLoginId') is not nulldrop procedure SelectTeacherByLoginIdgocreate procedure SelectTeacherByLoginId@LoginId varchar(50) --参数asselect TeacherID, TeacherName,Sex,LoginId,LoginPwd,Birthday,UserState,ateIdfrom Teacher t,UserState u where ateId=ateId and LoginId=@LoginIdgo--调⽤exec SelectTeacherByLoginId 't001' Vs中代码调⽤#region 根据LoginID查询教员信息 //根据LoginID查询教员信息 public static Teacher SelectTeacherByLoginId(string loginId) { Teacher tea = null; string sql = "SelectTeacherByLoginId"; SqlDataReader dr = Reader(sql, Procedure //调⽤存储过程 , new SqlParameter[] {new SqlParameter("@LoginId",loginId)} ); if (()) { tea = new Teacher(); rID = 32(dr["TeacherID"]); d = dr["LoginId"] + ""; wd = dr["LoginPwd"] + ""; rName = dr["TeacherName"] + ""; = dr["Sex"] + ""; ate = dr["UserState"] + ""; ateId = 32(dr["UserStateId"]); ay = Time(dr["Birthday"]); } (); on(); return tea; }
#endregion
2023年6月21日发(作者：)
SQL如何创建存储过程 谢⼤哥USE CMS
----Use 是跳转到哪个数据库，对这个数据库进⾏操作。GO
----GO向 SQL Server 实⽤⼯具发出⼀批 Transact-SQL 语句结束的信号,相当于提交上⾯的SQL语句。----GO是把t-sql语句分批次执⾏。（⼀步成功了才会执⾏下⼀步,即⼀步⼀个GO）/****** Object: StoredProcedure [dbo].[PROC_four_five_hr] Script Date: 07/30/2018 13:44:55 ******/----这⼀段是介绍本存储过程名字和创建时间----/* */ 是注释掉很多⾏内容的符号----object 对象 的意思----storeprocedure 存储过程 的意思----dbo 是每个数据库的默认⽤户，具有所有者权限----dbo后⾯是存储过程的名称----script date 脚本 创建时间SET ANSI_NULLS ONGO----SET QUOTED_IDENTIFIER ONGO----/*⾸先准备数据,测试存储过程use ssqadm;--创建测试books表create table books_test ( book_id int identity(1,1) primary key, book_name varchar(20), book_price float, book_auth varchar(10));--插⼊测试数据insert into books_test (book_name,book_price,book_auth) values ('论语',25.6,'孔⼦'), ('天龙⼋部',25.6,'⾦庸'), ('雪⼭飞狐',32.7,'⾦庸'), ('平凡的世界',35.8,'路遥'), ('史记',54.8,'司马迁'); select * from books_test;*/
--1.创建⽆参存储过程if (exists (select * from s where name = 'getAllBooks')) drop proc getAllBooksgocreate procedure getAllBooksasbeginselect * from books_test;--调⽤,执⾏存储过程exec getAllBooks;endgo--2、修改存储过程alter procedure getallbooksas
select book_name from books_test;--3、删除存储过程drop procedure getallbooks;go--4、修改存储过程的名称sp_rename getallbooks,proc_get_allbooks;goexec proc_get_allbooks;go--5、创建带参数的存储过程use ssqadm goif (exists (select * from s where name = 'searchbooks'))drop proc searchbooks-- exec searchbooks 1;--执⾏存储searchbooks得到如下结果:gocreate procedure searchbooks (@bookid int)--括号⾥⾯是as
begindeclare @book_id int;----定义⼀个标量变量,只是保证存储过程的完整性，在本存储是多此⼀举的。set @book_id = @bookid;select* from books_test where book_id = @book_id;end;go-- exec searchbooks 1;--执⾏存储searchbooks得到如下结果:--6、创建带两个参数的存储过程use ssqadm goif (exists (select * from s where name = 'book_test2'))drop proc book_test2-- exec book_test2 1;--执⾏存储book_test2得到如下结果:gocreate procedure book_test2 (@bookid int,@bookname varchar(20))--括号⾥⾯是as
begindeclare @book_id int;----定义⼀个标量变量,只是保证存储过程的完整性，在本存储是多此⼀举的。declare @book_name varchar(20);set @book_id = @bookid;set @book_name = @bookname;select* from books_test where book_id = @book_id and book_name = @book_name;end;go-- exec book_test2 1,'论语';--执⾏存储book_test2得到如下结果:--7、创建带有返回值的存储过程use ssqadmgoif (exists (select * from s where name = 'book_test3'))drop proc book_test3gocreate procedure book_test3(@bookid int,@out_book_name varchar(20) output)asdeclare @book_id int;set @book_id = @bookid;beginselect @out_book_name = book_name from books_test where book_id= @book_id;endgo-- --执⾏存储book_test2得到如下结果:--执⾏存储过程declare @out_name varchar(20) --声明⼀个变量⽤来接收执⾏存储过程后的返回值exec book_test3 1,@out_name outputselect @out_name as out_book_name;--as是给返回的列值起⼀个名字 附带另⼀位⼤哥的调⽤--根据loginId查询教员信息if OBJECT_ID('SelectTeacherByLoginId') is not nulldrop procedure SelectTeacherByLoginIdgocreate procedure SelectTeacherByLoginId@LoginId varchar(50) --参数asselect TeacherID, TeacherName,Sex,LoginId,LoginPwd,Birthday,UserState,ateIdfrom Teacher t,UserState u where ateId=ateId and LoginId=@LoginIdgo--调⽤exec SelectTeacherByLoginId 't001' Vs中代码调⽤#region 根据LoginID查询教员信息 //根据LoginID查询教员信息 public static Teacher SelectTeacherByLoginId(string loginId) { Teacher tea = null; string sql = "SelectTeacherByLoginId"; SqlDataReader dr = Reader(sql, Procedure //调⽤存储过程 , new SqlParameter[] {new SqlParameter("@LoginId",loginId)} ); if (()) { tea = new Teacher(); rID = 32(dr["TeacherID"]); d = dr["LoginId"] + ""; wd = dr["LoginPwd"] + ""; rName = dr["TeacherName"] + ""; = dr["Sex"] + ""; ate = dr["UserState"] + ""; ateId = 32(dr["UserStateId"]); ay = Time(dr["Birthday"]); } (); on(); return tea; }
#endregion
发布评论