菜鸟跪求存储过程编程实例
想把一些逻辑运算方法放到存储过程中去运算。
但是完全就对存储过程编程没有概念
比如：
怎么定义变量。
怎么完成变量之间的计算。
计算的运算符是什么？
给我一些教程看一看？
网络上的教程都是很基础的，仅仅是完成一个SELECT。或者返回一个SELECT。
我希望各位高手能够介绍一个包含运算，定义变量，循环的存储过程教程或者案例给我看已下。
[解决办法]
- SQL code
CREATE PROC testasbegindeclare @i smallint,@j smallint,@t smallint,@str varchar(100)set @i=1set @j=1set @str=''while @i<=9 begin while @j<=@i begin set @t=@i*@j set @str=@str+cast(@j as char(2))+'*'+cast(@i as char(2))+'='+cast(@t as char(5)) set @j=@j+1 end print @str set @i=@i+1 set @j=1 set @str='' endendexec test/*1 *1 =1 1 *2 =2 2 *2 =4 1 *3 =3 2 *3 =6 3 *3 =9 1 *4 =4 2 *4 =8 3 *4 =12 4 *4 =16 1 *5 =5 2 *5 =10 3 *5 =15 4 *5 =20 5 *5 =25 1 *6 =6 2 *6 =12 3 *6 =18 4 *6 =24 5 *6 =30 6 *6 =36 1 *7 =7 2 *7 =14 3 *7 =21 4 *7 =28 5 *7 =35 6 *7 =42 7 *7 =49 1 *8 =8 2 *8 =16 3 *8 =24 4 *8 =32 5 *8 =40 6 *8 =48 7 *8 =56 8 *8 =64 1 *9 =9 2 *9 =18 3 *9 =27 4 *9 =36 5 *9 =45 6 *9 =54 7 *9 =63 8 *9 =72 9 *9 =81 */
[解决办法]
- SQL code
SELECT CAST(CASE WHEN 1 <= number THEN RTRIM(number) + ' * 1 = ' + RTRIM(number) ELSE '' END AS varchar(20)), CAST(CASE WHEN 2 <= number THEN RTRIM(number) + ' * 2 = ' + RTRIM(number*2) ELSE '' END AS varchar(20)), CAST(CASE WHEN 3 <= number THEN RTRIM(number) + ' * 3 = ' + RTRIM(number*3) ELSE '' END AS varchar(20)), CAST(CASE WHEN 4 <= number THEN RTRIM(number) + ' * 4 = ' + RTRIM(number*4) ELSE '' END AS varchar(20)), CAST(CASE WHEN 5 <= number THEN RTRIM(number) + ' * 5 = ' + RTRIM(number*5) ELSE '' END AS varchar(20)), CAST(CASE WHEN 6 <= number THEN RTRIM(number) + ' * 6 = ' + RTRIM(number*6) ELSE '' END AS varchar(20)), CAST(CASE WHEN 7 <= number THEN RTRIM(number) + ' * 7 = ' + RTRIM(number*7) ELSE '' END AS varchar(20)), CAST(CASE WHEN 8 <= number THEN RTRIM(number) + ' * 8 = ' + RTRIM(number*8) ELSE '' END AS varchar(20)), CAST(CASE WHEN 9 <= number THEN RTRIM(number) + ' * 9 = ' + RTRIM(number*9) ELSE '' END AS varchar(20))FROM ( SELECT number = 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) AS A/*-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------1 * 1 = 1 2 * 1 = 2 2 * 2 = 4 3 * 1 = 3 3 * 2 = 6 3 * 3 = 9 4 * 1 = 4 4 * 2 = 8 4 * 3 = 12 4 * 4 = 16 5 * 1 = 5 5 * 2 = 10 5 * 3 = 15 5 * 4 = 20 5 * 5 = 25 6 * 1 = 6 6 * 2 = 12 6 * 3 = 18 6 * 4 = 24 6 * 5 = 30 6 * 6 = 36 7 * 1 = 7 7 * 2 = 14 7 * 3 = 21 7 * 4 = 28 7 * 5 = 35 7 * 6 = 42 7 * 7 = 49 8 * 1 = 8 8 * 2 = 16 8 * 3 = 24 8 * 4 = 32 8 * 5 = 40 8 * 6 = 48 8 * 7 = 56 8 * 8 = 64 9 * 1 = 9 9 * 2 = 18 9 * 3 = 27 9 * 4 = 36 9 * 5 = 45 9 * 6 = 54 9 * 7 = 63 9 * 8 = 72 9 * 9 = 81(9 行受影响)*/
[解决办法]
[解决办法]
和一般的编程语言差不多了。因为是数据库，所以处理数据能力很强。
一个参考
- SQL code
--创建存储过程CREATE PROCEDURE [dbo].[sp_JimProc_GetAllChildID]--获得一个部门下所有子部门的ID，无论有多少级@ParentID INT,@ChildIDStr VARCHAR(500) OUTPUTASSET @ChildIDStr=''IF @ParentID IS NULL RETURNDECLARE @OldRecordCnt INTDECLARE @CurRecordCnt INTDECLARE @tmp table ([DepartmentID] INT PRIMARY KEY NOT NULL)INSERT INTO @tmp VALUES(@ParentID)SET @OldRecordCnt=1WHILE 1=1BEGIN INSERT INTO @tmp SELECT a.[DepartmentID] FROM Admin_Department a INNER JOIN @tmp b ON a.[ParentID]=b.[DepartmentID] WHERE a.[DepartmentID] NOT IN (SELECT [DepartmentID] FROM @tmp) SELECT @CurRecordCnt=COUNT(*) FROM @tmp IF @CurRecordCnt=@OldRecordCnt BREAK ELSE SET @OldRecordCnt=@CurRecordCntENDDELETE FROM @tmp WHERE [DepartmentID]=@ParentIDSELECT @ChildIDStr=@ChildIDStr++CAST(DepartmentID AS VARCHAR(4))+',' FROM @tmpIF @ChildIDStr<>'' SET @ChildIDStr=LEFT(@ChildIDStr,LEN(@ChildIDStr)-1)