不固定参数的存储过程实现代码
发布时间:2024-01-22 16:36:39
标签:不固定参数,存储过程
我想此时不妨使用字符串参数来帮助我们解决这种情况,利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子:
假设现在给你一个产品信息列表(显示出各个商品的基本信息),现在我想要根据所选择商品进行统计(任意选择几种),例如统计出价格<10,11-20,21-30,31-40,41-50,50以上的商品个有多少个(姑且认为就统计这些)。此时如果使用存储过程就势必需要传入所选商品的id作为参数,但是id个数是不固定的。此时估计会有人这样写:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 统计商品
-- =============================================
ALTER PROCEDURE StatProductInfo
(
@ids VARCHAR(8000)
)
AS
BEGIN
DECLARE @followingTen INT
DECLARE @elevenToTwenty INT
DECLARE @twentyOneToThirty INT
DECLARE @thirtyOneToFourty INT
DECLARE @fourtyOneToFifty INT
DECLARE @fiftyOrMore INT
SELECT @followingTen=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice<10
SELECT @elevenToTwenty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 11 AND 20
SELECT @twentyOneToThirty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 21 AND 30
SELECT @thirtyOneToFourty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 31 AND 40
SELECT @fourtyOneToFifty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 41 AND 50
SELECT @fiftyOrMore=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice>50
SELECT @followingTen AS '<$10',@elevenToTwenty AS '$11-$20',
@twentyOneToThirty AS '$21-$30',@thirtyOneToFourty AS '$31-$40',
@fourtyOneToFifty AS '$41-$50',@fiftyOrMore AS '>$50'
END
GO
其实如果你测试一下(例如:EXEC dbo . StatProductInfo '3,4,8,10,22' )是有问题的,sql server认为这整个是一个参数,转换时出错。此时我们想一下如果这些字段在一个虚表中就容易操作多了,但是一般虚表是有其他表通过查询得到,现在根本无法查询又哪来的虚表呢?聪明的朋友或许已经想到可以使用"表值函数"。对,答案就是使用"表值函数"。我们知道"表值函数"可以返回一个"Table"类型的变量(相当于一张虚表,存放于内存中),我们首先将字符串分割存放到"表值函数"的一个字段中,然后我们再从"表值函数"中查询就可以了(这个例子也是"表值函数"的一个典型应用)。具体sql如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: cmj
-- Create date: 2010.11.05
-- Description: 返回一个Table,只有一列,每一行的数据就是分割好的字符串
-- =============================================
CREATE FUNCTION GetSplitFieldsByString
(
@toSplitString varchar(1000),
@splitChar varchar(10)
)
RETURNS
@tb TABLE(sp varchar(100))
AS
BEGIN
DECLARE @i INT
SET @toSplitString=RTRIM(LTRIM(@toSplitString))
SET @i=CHARINDEX(@splitChar,@toSplitString)
WHILE @i>0
BEGIN
INSERT @tb VALUES(LEFT(@toSplitString,@i-1))
SET @toSplitString=RIGHT(@toSplitString,LEN(@toSplitString)-@i)
SET @i=CHARINDEX(@splitChar,@toSplitString)
END
IF LEN(@toSplitString)>0
INSERT @tb VALUES(@toSplitString)
RETURN
END
GO
然后我们稍微修改一下存储过程:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 统计商品
-- =============================================
ALTER PROCEDURE StatProductInfo
(
@ids VARCHAR(8000)
)
AS
BEGIN
DECLARE @followingTen INT
DECLARE @elevenToTwenty INT
DECLARE @twentyOneToThirty INT
DECLARE @thirtyOneToFourty INT
DECLARE @fourtyOneToFifty INT
DECLARE @fiftyOrMore INT
SELECT @followingTen=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice<10
SELECT @elevenToTwenty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN 11 AND 20
SELECT @twentyOneToThirty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN 21 AND 30
SELECT @thirtyOneToFourty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN 31 AND 40
SELECT @fourtyOneToFifty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN 41 AND 50
SELECT @fiftyOrMore=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice>50
SELECT @followingTen AS '<$10',@elevenToTwenty AS '$11-$20',@twentyOneToThirty AS '$21-$30',
@thirtyOneToFourty AS '$31-$40',@fourtyOneToFifty AS '$41-$50',@fiftyOrMore AS '>$50'
END
GO
这样通过执行EXEC dbo . StatProductInfo '3,4,8,10,22' 就可以得到想要的结果了:
试试这样会不会快一些
SELECT SUM(CASE WHEN UnitPrice < 10 THEN 1 ELSE 0 END) '<$10',
SUM(CASE WHEN UnitPrice BETWEEN 11 AND 20 THEN 1 ELSE 0 END) '$11-$20',
SUM(CASE WHEN UnitPrice BETWEEN 21 AND 30 THEN 1 ELSE 0 END) '$21-$30',
...
SUM(CASE WHEN UnitPrice > 50 THEN 1 ELSE 0 END) '>$10'
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,','))
0
投稿
猜你喜欢
- 1、从外部文档中粘贴时,如果不想要其格式,只要文字,可以使用“Edit->paste as text”命令,而不要直接Ctrl+V。2
- 道友问我的一个问题,之前确实没遇见过,在此记录一下。问题描述在某网站主页提取url进行迭代,爬虫请求主页时没有问题,返回正常,但是在访问在主
- 比如下面的例子:class Book(object): def __setattr__(self, na
- 列表和元组,都是一个可以放置任何数据类型的有序集合。 列表的特性动态的(mutable):长度大小不固定,可以随意地增加、删减或者改变元素。
- 阅读上一章:Chapter 10 应用CSSChapter 11 打印样式先前在第10章中,讨论了几种为文档应用CSS的方法,这一章是要研究
- 1.socket黏包问题原理黏包:指数据与数据之间没有明确的分界线,导致不能正确的读取数据。应用数据想要发送数据就必须将数据交给操作系统,而
- 在网页设计发展到一定阶段的时候就必然会和其他学科或领域只是产生交汇和共鸣,在阅读《超越CSS:web设计艺术精髓》这本书的时候,发现原来we
- 选择题以下python代码输出什么?a = [2,3,1]sorted(a)print(a)A aB [3, 2, 1]C [2, 3, 1
- 进入主题1.import turtle as timport matht.pensize(3)t.tracer(10)t.hideturtl
- 前言对于很多接触Python的人而言,字符的处理和语言整体的温顺可靠相比显得格外桀骜不驯难以驾驭。文章针对Python 2.7,主要因为3对
- 如下所示:#! usr/bin/python#coding=utf-8 import numpy as npimport matplotli
- 本文实例讲述了Python实现将Excel转换成xml的方法。分享给大家供大家参考,具体如下:最近写了个小工具 用于excel转成xml直接
- 一、正则表达式–元字符re 模块使 Python 语言拥有全部的正则表达式功能1. 数量词# 提取大小写字母混合的单词import rea
- 方法调用行为方法调用比其他类型的查找略为复杂一点。 以下是一些注意事项: 在方法查找过程中,如果某方法抛
- 1.根据id获取元素document.getElementById("id属性的值");返回值是一个元素对象案例:点击按
- 前言今天无意中发现在python中的一个多重赋值的小问题,自己一开始是比较简单化的理解了这个多重赋值操作的概念,所以导致在一道实现斐波那契数
- 在numpy中的ndarry是一个数组,因此index就是位置下标,注意下标是从0开始增加:在插入时使用np.insert(),在末尾添加时
- 微信,一个日活10亿的超级app,不仅在国内社交独领风骚,在国外社交也同样占有一席之地,今天我们要将便是如何用Python来生成一个微信机器
- 在ORACLE中,我们可以通过file_id(file#)与block_id(block#)去定位一个数据库对象(object)。例如,我们
- 使用celery在django项目中实现异步发送短信在项目的目录下创建celery_tasks用于保存celery异步任务。在celery_