Sql server和oracle的多输出参数

最新推荐文章于 2021-04-10 01:36:35 发布

转载最新推荐文章于 2021-04-10 01:36:35 发布 · 613 阅读

文章标签：

#sql server #oracle #output #join #object #less

Sql Server Study 同时被 2 个专栏收录

10 篇文章

订阅专栏

Oracle Study

5 篇文章

订阅专栏

本文介绍了一个SQL存储过程示例，该示例展示了如何通过存储过程来查询特定子类别下的产品，并比较这些产品的价格是否低于设定的最大价格。此外，还介绍了如何使用输出参数来返回查询结果中的最大列表价格及比较价格。

SQL-----------

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
    DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
    , @maxprice money
    , @compareprice money OUTPUT
    , @listprice money OUT
AS
    SELECT p.name AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product p
    JOIN Production.ProductSubcategory s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
-- Populate the output variable @listprice.
SET @listprice = (SELECT MAX(p.ListPrice)
        FROM Production.Product p
        JOIN Production.ProductSubcategory s
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.name LIKE @product AND p.ListPrice < @maxprice);
-- Populate the output variable @compareprice.
SET @compareprice = @maxprice;
GO

DECLARE @compareprice money, @cost money
EXECUTE Production.usp_GetList '%Bikes%', 700,
    @compareprice OUT,
    @cost OUTPUT
IF @cost <= @compareprice
BEGIN
    PRINT 'These products can be purchased for less than
    $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed
    $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'

Oracle-------------------------
v_sqltext:='begin getfeetype(:servicetype,:feetype,:feecode); end;';
execute immediate v_sqltext using v_serviceid,out v_feetype,out v_feecode;