Saklı yordamdan veri döndürme

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiMicrosoft Fabric'de Azure Synapse AnalyticsSQL veritabanı

Bir yordamdan çağıran programa veri döndürmenin üç yolu vardır: sonuç kümeleri, çıkış parametreleri ve dönüş kodları. Bu makalede üç yaklaşım hakkında bilgi verilmektedir.

Bu makaledeki kod örnekleri, AdventureWorks2022 giriş sayfasından indirebileceğiniz AdventureWorksDW2022 veya örnek veritabanını kullanır.

Sonuç kümelerini kullanarak veri döndürme

Saklı yordamın gövdesine bir SELECT deyimi eklerseniz (SELECT ... INTO veya INSERT ... SELECTdeğil), SELECT deyimi tarafından belirtilen satırlar doğrudan istemciye gönderilir. Büyük sonuç kümeleri için, sonuç kümesi istemciye tamamen gönderilene kadar saklı yordam çalışması sonraki ifadeye devam etmez. Küçük sonuç kümeleri için sonuçlar istemciye döndürülmek üzere biriktirilir ve yürütme devam eder. Saklı yordamın yürütülmesi sırasında bu tür birden çok SELECT deyimi çalıştırılırsa, istemciye birden çok sonuç kümesi gönderilir. Bu davranış iç içe Transact-SQL toplu işlemler, iç içe saklı yordamlar ve en üst düzey Transact-SQL toplu işlemler için de geçerlidir.

Sonuç kümesi kullanarak veri döndürme örnekleri

Bu örnekte, LastName görünümünde de görünen tüm SalesYTD satırların SalesPerson ve vEmployee değerlerini döndüren saklı bir yordam gösterilmektedir.

SQL

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO

CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
AS
SET NOCOUNT ON;
SELECT LastName,
       SalesYTD
FROM Sales.SalesPerson AS sp
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO

Çıkış parametresi kullanarak veri döndürme

Yordam tanımında bir parametre için output anahtar sözcüğünü belirtirseniz, yordam çıktığında parametresinin geçerli değerini çağıran programa döndürebilir. parametresinin değerini çağıran programda kullanılabilecek bir değişkene kaydetmek için, çağıran programın yordamı yürütürken output anahtar sözcüğünü kullanması gerekir. Çıkış parametresi olarak hangi veri türlerinin kullanılabileceğini hakkında daha fazla bilgi için bkz. create PROCEDURE.

Çıkış parametreleri örnekleri

Aşağıdaki örnekte giriş ve çıkış parametresi içeren bir yordam gösterilmektedir. @SalesPerson parametresi, çağıran program tarafından belirtilen bir giriş değeri alır. SELECT deyimi, doğru SalesYTD değerini almak için giriş parametresine geçirilen değeri kullanır. SELECT deyimi de değeri @SalesYTD çıkış parametresine atar. Bu parametre, yordamın çıkışında değeri çağıran programa döndürür.

SQL

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO

CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson NVARCHAR (50), @SalesYTD MONEY OUTPUT
AS
SET NOCOUNT ON;
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN;
GO

Aşağıdaki örnek, ilk örnekte oluşturulan yordamı çağırır ve çağrılan yordamdan döndürülen @SalesYTD çıkış parametresini @SalesYTDBySalesPerson değişkenine kaydeder.

Örnek:

• Prosedürün çıkış değerini almak için @SalesYTDBySalesPerson değişkenini bildirir.

• Giriş parametresi için bir aile adı belirten Sales.uspGetEmployeeSalesYTD yordamını yürütür. çıkış değerini @SalesYTDBySalesPersondeğişkenine kaydedin.

• 'ye kaydedilen değeri görüntülemek için @SalesYTDBySalesPerson'i çağırır.

SQL

DECLARE @SalesYTDBySalesPerson AS MONEY;

EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe',
    @SalesYTD = @SalesYTDBySalesPerson OUTPUT;

PRINT 'Year-to-date sales for this employee is '
    + CONVERT (VARCHAR (10), @SalesYTDBySalesPerson);
GO

Yordam yürütülürken çıkış parametreleri için giriş değerleri de belirtilebilir. Bu, yordamın çağıran programdan bir değer almasına, değerle işlemleri değiştirmesine veya gerçekleştirmesine ve ardından yeni değeri çağıran programa döndürmesine olanak tanır. Önceki örnekte, program @SalesYTDBySalesPerson yordamı çağırmadan önce Sales.uspGetEmployeeSalesYTD değişkenine bir değer atanabilir. Yürütülen deyim, @SalesYTDBySalesPerson değişken değerini @SalesYTD çıkış parametresine geçirir. Ardından yordam gövdesinde değer, yeni bir değer oluşturan hesaplamalar için kullanılabilir. Yeni değer, çıkış parametresi aracılığıyla yordamdan geri geçirilir ve yordamdan çıkıldığında @SalesYTDBySalesPerson değişkenindeki değer güncelleştiriler. Bu genellikle referans ile geçiş özelliği olarak adlandırılır.

Bir yordamı çağırdığınızda bir parametrenin çıkışını belirtirseniz ve bu parametre yordam tanımında çıkış kullanılarak tanımlanmamışsa bir hata iletisi alırsınız. Ancak, çıkış parametreleri olan bir yordamı yürütebilir ve yordamı yürütürken çıkışı belirtmeyebilirsiniz. Hata döndürülmedi, ancak çıkış değerini çağıran programda kullanamazsınız.

Çıkış parametrelerinde imleç veri türünü kullanma

Transact-SQL yordamları yalnızca çıkış parametreleri için imleç veri türünü kullanabilir. bir parametre için imleç veri türü belirtilirse, yordam tanımında bu parametre için hem değişken hem de çıkış anahtar sözcükleri belirtilmelidir. Parametre yalnızca çıkış olarak belirtilebilir, ancak değişken anahtar sözcük parametre bildiriminde belirtilirse, veri türü imleç olmalı ve çıkış anahtar sözcüğü de belirtilmelidir.

Not

İmleç veri türü OLE DB, ODBC, ADO ve DB Kitaplığı gibi veritabanı API'leri aracılığıyla uygulama değişkenlerine bağlanamaz. Bir uygulamanın bir yordamı yürütebilmesi için çıkış parametrelerinin bağlanması gerektiğinden, imleç çıkış parametrelerine sahip yordamlar veritabanı API'lerinden çağrılamıyor. Bu yordamlar Transact-SQL toplu işlemlerden, yordamlardan veya tetikleyicilerden yalnızca imleç çıkış değişkeni bir Transact-SQL yerel imleç değişkenine atandığında çağrılabilir.

İmleç çıkış parametreleri için kurallar

Yordam yürütülürken imleç çıkış parametreleriyle ilgili aşağıdaki kurallar:

• Yalnızca ileriye doğru imleç için, yordam yürütmesi tamamlandığında imlecin sonuç kümesinde geri döndürülen satırlar, yalnızca imlecin konumundan itibaren ve sonrasındaki satırlardır. Örneğin:

  • Bir prosedürde, 100 satırlık RS adlı sonuç kümesi üzerinde kaydırılamayan bir imleç açılır.

  • İşlem, sonuç kümesi RS'ın ilk beş satırını getirir.

  • Yöntem, onu çağırana döner.

  • Arayana döndürülen sonuç kümesi RS 6 ile 100 arasında RSsatırlarından oluşur ve çağıran içindeki imleç RSilk satırından önce konumlandırılır.

• Yalnızca ileriye doğru imleç için, imleç yordamdan çıkıldığında ilk satırın önüne yerleştirilirse, sonuç kümesinin tamamı çağıran toplu işleme, yordama veya tetikleyiciye döndürülür. döndürülürse, imleç konumu ilk satırdan önce ayarlanır.

• Yalnızca ileriye doğru imleç için, yordamdan çıkıldığında imleç son satırın sonundan öteye konumlandırılırsa, çağıran toplu işleme, yordama veya tetikleyiciye boş bir sonuç kümesi döndürülür.

  Not

  Boş sonuç kümesi null değerle aynı değildir.

• Kaydırılabilir bir imleç için, sonuç kümesindeki tüm satırlar, yordam sonlandığında çağrılan toplu işleme, yordama veya tetikleyiciye iletilir. İade edildiğinde, imleç konumu prosedürde yürütülen son getirme işlemi konumunda bırakılır.

• herhangi bir imleç türü için, imleç kapalıysa, çağıran toplu işleme, yordama veya tetikleyiciye bir null değer geri geçirilir. Bir imleç bir parametreye atandıysa ancak bu imleç hiçbir zaman açılmazsa da bu durum geçerlidir.

  Not

  Kapalı durum yalnızca iade zamanında önemlidir. Örneğin, bir yordamın ortasında bir imleci kapatmak, ardından yordamın ilerleyen bölümlerinde tekrar açmak ve o imlecin sonuç kümesini çağıran toplu işleme, yordama veya tetikleyiciye geri döndürmek geçerlidir.

İmleç çıkış parametreleri örnekleri

Aşağıdaki örnekte, imleç veri türü kullanılarak @CurrencyCursor bir çıkış parametresi belirten bir yordam oluşturulur. Prosedür daha sonra toplu olarak çağrılır.

İlk olarak, bildiren yordamı oluşturun ve ardından Currency tablosunda bir imleç açın.

SQL

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.uspCurrencyCursor', 'P') IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO

CREATE PROCEDURE dbo.uspCurrencyCursor
@CurrencyCursor CURSOR VARYING OUTPUT
AS
SET NOCOUNT ON;
SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC
    FOR SELECT CurrencyCode,
               Name
        FROM Sales.Currency;
OPEN @CurrencyCursor;
GO

Önce, yerel bir imleç değişkeni bildiren bir toplu işlemi yürütün, ardından prosedürü, imleci yerel değişkene atamak için çalıştırın ve son olarak imleçten satırları getirin.

SQL

USE AdventureWorks2022;
GO

DECLARE @MyCursor AS CURSOR;

EXECUTE dbo.uspCurrencyCursor
    @CurrencyCursor = @MyCursor OUTPUT;

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        FETCH NEXT FROM @MyCursor;
    END

CLOSE @MyCursor;

DEALLOCATE @MyCursor;
GO

Dönüş kodu kullanarak veri döndürme

Yordam, bir yordamın yürütme durumunu belirtmek için dönüş kodu olarak adlandırılan bir tamsayı değeri döndürebilir. RETURNkullanarak bir yordamın dönüş kodunu belirtirsiniz. Çıkış parametrelerinde olduğu gibi, çağıran programda dönüş kodu değerini kullanmak için yordam yürütülürken dönüş kodunu bir değişkene kaydetmeniz gerekir. Örneğin, int veri türünün atama değişkeni , yordamından döndürülen kodu depolamak için kullanılır, örneğin:

SQL

DECLARE @result AS INT;
EXECUTE @result = my_proc;
GO

Dönüş kodları, her olası hata durumu için dönüş kodu değerini ayarlamak için yordamlar içindeki akış denetimi bloklarında yaygın olarak kullanılır. deyiminin yürütülmesi sırasında bir hata oluşup oluşmadığını algılamak için Transact-SQL deyiminden sonra @@ERROR işlevini kullanabilirsiniz. TRY / CATCH / THROW hata işlemenin kullanıma sunulmasından önce, Transact-SQL dönüş kodları saklı yordamların başarısını veya başarısızlığını belirlemek için bazen gerekliydi. Saklı yordamlar, başarısızlığı her zaman bir hatayla (gerekirse THROW/RAISERROR ile oluşturulmuş bir hata) belirtmeli ve hatayı göstermek için asla bir dönüş koduna güvenmemelidir. Ayrıca, uygulama verilerini döndürmek için dönüş kodunu kullanmaktan kaçınmanız gerekir.

İade kodları örnekleri

Aşağıdaki örnekte, çeşitli hatalar için özel dönüş kodu değerlerini ayarlayan hata işlemeye sahip usp_GetSalesYTD yordamı gösterilmektedir. Aşağıdaki tabloda, her olası hataya yordam tarafından atanan tamsayı değeri ve her değer için karşılık gelen anlam gösterilmektedir.

Dönüş kodu değeri	Anlam
0	Başarılı yürütme.
1	Gerekli parametre değeri belirtilmemiş.
2	Belirtilen parametre değeri geçerli değil.
3	Satış değerini alırken bir hata oluştu.
4	NULL satış temsilcisi için bulunan satış değeri.

Örnek, Sales.usp_GetSalesYTDadlı bir prosedür oluşturur:

• @SalesPerson parametresini bildirir ve varsayılan değerini NULLolarak ayarlar. Bu parametre, bir satış elemanının aile adını almaya yöneliktir.

• @SalesPerson parametresini doğrular.

  • @SalesPerson NULLise, yordam bir ileti yazdırır ve 1dönüş kodunu döndürür.
  • Aksi takdirde, @SalesPerson parametresi NULLdeğilse, yordam HumanResources.vEmployee tablosundaki satır sayısını @SalesPersondeğerine eşit bir aile adıyla denetler. Sayı sıfırsa, yordam 2dönüş kodunu döndürür.

• Belirtilen aile adına sahip satış elemanının yıllık satışlarını sorgular ve @SalesYTD çıkış parametresine atar.

• @@ERRORtest ederek SQL Server hatalarını denetler.

  • @@ERROR sıfıra eşit değilse, yordam 3dönüş kodunu döndürür.
  • @@ERROR sıfıra eşitse, yordam @SalesYTD parametre değerinin NULLolup olmadığını denetler. Yıldan bugüne satış bulunmazsa, prosedür 4 iade kodunu döndürür.
  • Yukarıdaki koşullardan hiçbiri doğru değilse, yordam 0dönüş kodunu döndürür.

• Saklı yordamdaki son deyime ulaşıldığında, giriş değeri belirtilmeksizin saklı yordam özyinelemeli olarak çağrılır.

Örneğin sonunda, giriş parametresi için bir aile adı belirtirken ve çıkış değerini Sales.usp_GetSalesYTDdeğişkenine kaydederken @SalesYTD yordamını yürütmek için kod sağlanır.

SQL

USE AdventureWorks2022;
GO

CREATE PROCEDURE Sales.usp_GetSalesYTD
    @SalesPerson NVARCHAR (50) = NULL,
    @SalesYTD MONEY = NULL OUTPUT
AS
IF @SalesPerson IS NULL
    BEGIN
        PRINT 'ERROR: You must specify a last name for the sales person.';
        RETURN (1);
    END
ELSE
    BEGIN
        IF (SELECT COUNT(*)
            FROM HumanResources.vEmployee
            WHERE LastName = @SalesPerson) = 0
            RETURN (2);
    END
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
IF @@ERROR <> 0
    BEGIN
        RETURN (3);
    END
ELSE
    BEGIN
        IF @SalesYTD IS NULL
            RETURN (4);
        ELSE
            RETURN (0);
    END
EXECUTE Sales.usp_GetSalesYTD;
GO

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS INT;

EXECUTE Sales.usp_GetSalesYTD N'Blythe',
    @SalesYTD = @SalesYTDForSalesPerson OUTPUT;

PRINT N'Year-to-date sales for this employee is '
    + CONVERT (VARCHAR (10), @SalesYTDForSalesPerson);
GO

Aşağıdaki örnek, usp_GetSalesYTD yordamından döndürülen dönüş kodlarını işlemek için bir program oluşturur.

Örnek:

• Prosedürün çıkış değerini ve dönüş kodunu almak için @SalesYTDForSalesPerson ve @ret_code değişkenlerini bildirir.

• Sales.usp_GetSalesYTD yordamını @SalesPerson için belirtilen bir giriş değeriyle yürütür ve çıkış değerini kaydeder ve değişkenlerde kod döndürür.

• 'daki dönüş kodunu denetler ve uygun bir ileti görüntülemek için PRINT çağırır.

SQL

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS INT;

EXECUTE
    @ret_code = Sales.usp_GetSalesYTD N'Blythe',
    @SalesYTD = @SalesYTDForSalesPerson OUTPUT;

IF @ret_code = 0
    BEGIN
        PRINT 'Procedure executed successfully';
        PRINT 'Year-to-date sales for this employee is '
            + CONVERT (VARCHAR (10), @SalesYTDForSalesPerson);
    END
ELSE
    IF @ret_code = 1
        PRINT 'ERROR: You must specify a last name for the sales person.';
    ELSE IF @ret_code = 2
        PRINT 'ERROR: You must enter a valid last name for the sales person.';
    ELSE IF @ret_code = 3
        PRINT 'ERROR: An error occurred getting sales value.';
    ELSE IF @ret_code = 4
        PRINT 'ERROR: No sales recorded for this employee.';
GO

İlgili içerik

• PROCEDURE OLUŞTUR (Transact-SQL)
• PRINT (Transact-SQL)
• SET @local_variable (Transact-SQL)
• RETURN (Transact-SQL)
• @@ERROR (Transact-SQL)