http___link-university.com_lekcija_Tehnike-za-pisanje-uskladi_C5_A1tenih-procedura_4847_1.xml
1.1 2.1 2.2 3.1 4.1 5.1 6.1 7.1 8.1 8.2 9.1 10.1 10.2 11.1 11.2 11.3 11.4 11.5 12.1 12.2 12.3 12.4 12.5 13.1 13.2 14.1 15.1 16.1 16.2 16.3 16.4 16.5 16.6 16.7 16.8 17.1 18.1 19.1 20.1 21.1 22.1 23.1 24.1 25.1 26.1 27.1 28.1 29.1 30.1 31.1 32.1 33.1 34.1 35.1 36.1 37.1 38.1 39.1 39.2
How to Create a Stored Procedure ? When the CREATE PROCEDURE statement is executed, the syntax of the SQL statements within the procedure is checked. If you have made a coding error the system responds with an appropriate message and the procedure is not created. The Syntax of the CREATE PROCEDURE statement CREATE {PROC|PROCEDURE} Procedure_name [Parameter_declaration] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}] AS sql_statements You can use CREATE PROCEDURE statement to create a stored procedure in the database. The name of the stored procedure can be up to 128 characters and is typically prefixed with the letters sp. If you look at the above options like AS, RECOMPILE, ENCRYPTION these are having some significance meaning to it. The AS clause contains the SQL statements to be executed by the stored procedure. Since a stored procedure must consist of single batch. input parameters and Output Parameters. An input Parameter is passed to the stored procedure from the calling program. An output parameter is returned to the calling program from the stored procedure. You can identify an output parameter with the OUTPUT keyword. If this keyword is omitted the parameter is assumed to be an input parameter. Recompile is used when you want to compile the stored procedure every time when you call. This comes into the picture when one doesn't want to catch the execution plan of stored procedure in the database. Encryption implies that you want to hide this code so that no one can see it. This is very important when you want to distribute the code across the globe or when you want to sell this code to other vendors. But make sure you have original copy it; because once you encrypted it no one can decrypt it. Apart from the stored procedure that store in the database a permanent entity you can create stored procedure as per you session. That means as long the as the session is alive then the stored procedure is available in the memory means in the database. Once the session ends the stored procedure is vanished this actually depends on what type of stored procedure you have chosen to create it. Stored procedure provide for two different types of parameters: You can declare an input parameter so that it requires a value or so that its value is optional. The value of a required parameter must be passed to the stored procedure from the calling program on an error occurs. The value of an optional parameter doesn't need to be passed from the calling program. You identify an optional parameter by assigning a default value to it. Then if a value isn't passed from the calling program, the default value is used. You can also use output parameter as input parameters. That is you can pass a value from the calling program to the stored procedure through an output parameter. However is not advisable to pass parameters to Output parameters. The syntax for declaring the parameters @Parameter_name_1 data_type [= default] [OUTPUT] [, @Parameter_name_2 data_type [= default] [OUTPUT]. Parameter declarations @FirstName varchar(50) -- Input parameter that accepts a string. @LastName varchar(50) -- Output Parameter that returns a string. Create Procedure statement that uses an input and an output parameter. CREATE PROC spGetShashi @FirstName varchar(50), @LastName varchar(50) AS SELECT @LastName= ln_Name FROM SHASHI WHERE fn_name = @FirstName Create procedure statement that uses an optional parameter. CREATE PROC spGetShashi @LastName varchar(50), @FirstName varchar(50) = 'shashi' AS SELECT @LastName= ln_Name FROM SHASHI WHERE fn_name = @FirstName A stored procedure can declare up to 2100 parameters. If you declare two or more parameters, the declarations must be separated by commas.
1.1 1.2 1.3 2.1 3.1 3.2 4.1 5.1 6.1 7.1 7.2 8.1 9.1 10.1 11.1 12.1 13.1 14.1 15.1 15.2 16.1 17.1 18.1 19.1 20.1 21.1 22.1 23.1 24.1 25.1 26.1 27.1 28.1 29.1 30.1 31.1 32.1 33.1 33.2 33.3 33.4 34.1 34.2 34.3 34.4 34.5 35.1 36.1 37.1 38.1 39.1 40.1 40.2 41.1 42.1 43.1 44.1 45.1 46.1 47.1 48.1 49.1 50.1 51.1 52.1 53.1 54.1 55.1 56.1 57.1 58.1 59.1 60.1 61.1 62.1 63.1 64.1 65.1 66.1 67.1 68.1 69.1 70.1 71.1 71.2 71.3 72.1 72.2 72.3 73.1 73.2 74.1 75.1 76.1 77.1 78.1 79.1 80.1 81.1 82.1 83.1 83.2 83.3 83.4 83.5 84.1 85.1 86.1 87.1 88.1 89.1 90.1 91.1 92.1 93.1 94.1 94.2 94.3 95.1 95.2 96.1 97.1 98.1 99.1 100.1 101.1 102.1 103.1 104.1 105.1 106.1 107.1 108.1 109.1 111.1 112.1 113.1 114.1 115.1 116.1 117.1 118.1 119.1 120.1 121.1 122.1 123.1 124.1 125.1 126.1 127.1 128.1 129.1 130.1 131.1 132.1 133.1 134.1 135.1 136.1 137.1 137.2 137.3 137.4 137.5 137.6 138.1 140.1 141.1 142.1 143.1 144.1 146.1 147.1 149.1 150.1 151.1 152.1 153.1 154.1 155.1 156.1 157.1 158.1 159.1 160.1 161.1 161.2 161.3 161.4 161.5 161.6 161.7 161.8 162.1 163.1 164.1 165.1 166.1 167.1 168.1 169.1 170.1 171.1 172.1 172.2 173.1 174.1 175.1 176.1 177.1 178.1 179.1 180.1 180.2 180.3 180.4 180.5 181.1 182.1 183.1 184.1 185.1 186.1 187.1 188.1 189.1 190.1 191.1 192.1 193.1 194.1 195.1 195.2 195.3 196.1 196.2 196.3 196.4 196.5 197.1 198.1 199.1 200.1 201.1 202.1 203.1 204.1 205.1 206.1 207.1 207.2 208.1 208.2 208.3 209.1 210.1 210.2 210.3 210.4 211.1 212.1 213.1 214.1 215.1 216.1 217.1 218.1 219.1 220.1 221.1 222.1 223.1 224.1 225.1 226.1 227.1 228.1 229.1 230.1 231.1 232.1 233.1 233.2 233.3 234.1 235.1 235.2 235.3 235.4 236.1 236.2 237.1 238.1 239.1 240.1 241.1 242.1 243.1 244.1 245.1 246.1 247.1 248.1 249.1 250.1 251.1 252.1 253.1 254.1 255.1 256.1 257.1 258.1 259.1 260.1 261.1 262.1 263.1 263.2 264.1 265.1 266.1 267.1 268.1 269.1 270.1 271.1 272.1 273.1 274.1 275.1 276.1 277.1 278.1 279.1 280.1 281.1 282.1 283.1 283.2 284.1 285.1 286.1 287.1 288.1 289.1 290.1 291.1 292.1 293.1 294.1 295.1 296.1 297.1 298.1 299.1 300.1 301.1 302.1 303.1 304.1 305.1 306.1 307.1 308.1 309.1 310.1 311.1 312.1 313.1 313.2 313.3 313.4 314.1 315.1 316.1 317.1 318.1 319.1 320.1 321.1 322.1 323.1 324.1 325.1 326.1 327.1 328.1 329.1 330.1 330.2 331.1 332.1 332.2 332.3 332.4 332.5 333.1 334.1 334.2 335.1 335.2 335.3 335.4 335.5 336.1 336.2 337.1 338.1 339.1 340.1 341.1 342.1 343.1 344.1 345.1 346.1 347.1 348.1 349.1 350.1 350.2 350.3 351.1 351.2 352.1 353.1 353.2 353.3 354.1 355.1 356.1 357.1 358.1 359.1 360.1 361.1 362.1 362.2 363.1 364.1 365.1 365.2 365.3 366.1 367.1 368.1 369.1 370.1 371.1 372.1 373.1 373.2 373.3 374.1 375.1 376.1 376.2 376.3 377.1 378.1 379.1 380.1 381.1
Uskladištene procedure su osnova svake klijent/server aplikacije. Uskladištene procedure vam omogućavaju da se obrada podataka obavi na serveru. Prednosti uskladištenih procedura su: Deklarisanje i korišćenje promenljivih Promenljive možete da deklarišete u uskladištenim procedurama isto kao što možete da ih deklarišete u podupitima i funkcijama. Promenljiva se definiše rezervisanom rečju DECLARE. DECLARE @VariableName DataType [(length)], @VariableName DataType [(length)] Primer: Declare @FirstName VarChar(35) Promenljivima koje nisu inicijalizovane dodeljuje se vrednost Null. Promenljivoj zadajte vrednost pomoću iskaza SELECT. SELECT @FirstName = ‘Alexis’ Promenljiva se koristi na sledeći način: DECLARE @strCompany varchar (50) SELECT @strCompany = Upper(CompanyName) FROM Customer WHERE CustomerID = ‘ALFKI’ SELECT @strCompany U primeru je deklarisana promenljiva @strCompany. Pomoću programskog koda se upisuje u promenljivu. Kontrolisanje izvršavanja uskladištenih procedura se postiže sa konstruktama: Primer konstrukta IF … THEN: DECLARE @Locale VarChar (20), @Country VarChar(30) SELECT @Country = Country FROM Customers WHERE CustomerID = 'ALFKI' IF @Country = 'USA' BEGIN SELECT @Locale = 'Domestic' END ELSE BEGIN SELECT @Locale = 'Foreign' END SELECT CustomerID as imeid, @locale as Ime FROM Customers WHERE CustomerID = 'ALFKI‘ Na početku se deklarišu dve promenljive: promenljiva @Locale i promenljiva @Country. Sadržaj polja County za mušteriju ALFKI se dodeljuje promenljivoj @Country. Zatim se proverava sadržaj promenljive @Country, pri čemu se u promenljivu @Locale zapisuje odgovarajuća vrednost. Na kraju se prikazuje CustomerID i sadržaj promenljive @locale za mušteriju za koju je CustomerID jednak ALFKI. Konstrukt IF ... ELSE koristite kako biste unutar uskladištene procedure doneli odluku. Odluka se zasniva na parametrima koji se prosleđuju u uskladištenu proceduru. Definicija konstrukta IF ... ELSE glasi: IF(Uslov) BEGIN -- Izvršavanje procedure END Samo se neki iskazi izvršavaju kada je uslov ispunjen. Definicija konstrukta IF ... ELSE kada treba izvršiti iskaze u slučaju da uslov nije ispunjen: IF (Uslov) BEGIN -- Izvršavanje procedure END ELSE BEGIN -- Izvršavanje procedure END Primer: DECLARE @Locale VarChar (20), @Country VarChar(30) SELECT @Country = Country FROM Customers WHERE CustomerID = 'ALFKI' IF @Country = 'USA' BEGIN SELECT @Locale = 'Domestic' PRINT 'Ovo je domestic' PRINT ' ' PRINT 'Hello there' END ELSE BEGIN SELECT @Locale = 'Foreign' PRINT 'This is foreign' PRINT ' ' PRINT 'Hello there' END SELECT CustomerID, @Locale FROM Customers WHERE CustomerID = 'ALFKI' Ako je uslov ispunjen, izvršava se iskaz iza IF, a ako uslov nije ispunjen, izvršavaju se iskazi iza ELSE. Kada posle iskaza IF ne biste upotrebili konstrukt BEGIN ... END, prouzrokovala bi se greška. Omogućava da grupišete niz iskaza. Kada ne bi postojao konstrukt BEGIN ... END, onda bi se izvršio samo prvi iskaz nakon IF ili nakon ELSE. Iskaz GOTO koristite kako biste prešli na labelu unutar uskladištene procedure. Promoću iskaza RETURN bezuslovno se prekida izvršavanje uskladištene procedure. Primer: IF Month(GetDate()) > 6 BEGIN Print 'In IF Statement' GOTO MyLabel END SELECT CustomerID, CompanyName FROM Customers MyLabel: SELECT OrderDate, OrderDate FROM Orders U primeru se proverava da li je mesec tekućeg datuma veći od šest. Ukoliko jeste, prikazuje se rečenica “In IF Statement”, a izvršavanje programskog koda se nastavlja od labele MyLabel. Uskladištena procedura zatim izdvaja podatke iz tabele Orders. Ukoliko je mesec tekućeg datuma manji ili jednak vrednosti šest, uskladištena procedura prvo izdvaja podatke iz tabele Customer. Izvršavanje programskog koda se zatim nastavlja od labele MyLabel i izdvajaju se podaci iz tabele Orders. Bezuslovno prekidanje procedure obezbeđuje iskaz “RETURN” Primer: IF Month(GetDate()) > 6 BEGIN Print 'In IF Statement' GOTO MyLabel END SELECT CustomerID, CompanyName FROM Customers RETURN MyLabel:SELECT OrderDate, OrderDate FROM Orders Ako je u primeru mesec tekućeg datuma veći od šest, onda se prikazuje rečenica “In IF Statement” i procedura izdvaja podatke iz tabele Orders. Ukoliko je mesec tekućeg datuma manji ili jednak vrednosti šest, onda se podaci izdvajaju iz tabele Customers i izlazi se iz procedure. Pošto se koristi iskaz RETURN, procedura ne izdvaja podatke iz tabele Orders. Kada je mesec tekućeg datuma manji ili jednak vrednosti šest, onda se podaci izdvajaju iz tabele Customers i izlazi se iz procedure. Pošto se koristi iskaz RETURN, procedura ne izdvaja podatke iz tabele Orders. Primer: IF Month(GetDate()) < 6 BEGIN Print 'In IF Statement' GOTO MyLabel END SELECT CustomerID, CompanyName FROM Customers RETURN MyLabel: SELECT OrderID,OrderDate, OrderDate FROM Orders Pomoću iskaza CASE vrednost tabele se zamenjuje alternativnom vrednošću. Definicija iskaza CASE: Case InputExpression WhenExpression THEN ResultExpression [ELSE ElseResultExpression ] END Konstrukt Case se može pisati i na sledeći način: CASE WHEN Expression Then TruePart ELSE FalsePart END Primer 1: Select OrderID, ShipVIA,OrderDate, CASE ShipVIA WHEN 1 THEN 'UPS' WHEN 2 THEN 'U:S: Mail' WHEN 3 THEN 'FedEx' END AS Shipper, Freight FROM Orders Pomoću iskaza CASE se proverava sadržaj polja ShipVIA i dobija se odgovarajući string u zavisnosti od vrednosti polja ShipVia. Primer 2: DECLARE @AverageFreight Money SELECT @AverageFreight = AVG (Freight) FROM Orders SELECT OrderID, OrderDate, Freight, CASE WHEN FREIGHT <= @AverageFreight THEN 'Low Freight' ELSE 'High Freight' END AS Shipper, Freight FROM Orders Znači definisali smo promenljivu @AverageFreight Money. Njoj smo dodelili prosečnu količinu iz tabele Orders. CASE proverava da li je količina u tekućem redu manja ili jednaka prosečnoj količini. Ukoliko je manja ili jednaka od prosečne količine, onda se prikazuje „Low Freight“. U suprotnom se prikazuje „Height Freight“. Ta vrednost se kombinuje sa poljima OrderID, OrderDate i Freight koja se izdvajaju iz tabele. Iskaz While koristite kada želite da napravite ciklus, koji se izvršava sve dok je zadati uslov ispunjen. Definicija Konstrukta WHILE je: WHILE BooleanExpression (SQLStatement | SQLBlok) Here’s an example: CREATE TABLE MyTable LoopID INT, LoopText VarChar(25) Primer: Iskaza WHILE DECLARE @LoopValue INT DECLARE @LoopText CHAR (25) SELECT @LoopValue = 1 WHILE (@LoopValue < 100) BEGIN SELECT @LoopText = 'Iteration #' + Convert(VarChar(25), @LoopValue) INSERT INTO MyTable(LoopID, LoopText) VALUES (@LoopValue, @LoopText) SELECT @LoopValue = @LoopValue + 1 END select * from MyTable Prvo se pravi tabela MyTable. U tabeli postoje dva polja: polje tipa int i polje tipa char. Pomoću rutine se potom deklarišu dve promenljive, promenljiva tipa int i promenljiva tipa char. Promenljivoj @LoopValue se dodeljuje vrednost jedan. Programski kod izvršava ciklus od jedan do sto. Prilikom izvršavanja ciklusa, promenljivoj tipa char se dodeljuje tekst ‘Iteration #’ i sadržaj promenljive @LoopValue koji se prethodno pretvara u tip VarChar. Zatim se sadržaj promenljivih @LoopValue i @LoopText upisuje u tabelu. Na kraju se uvećava vrednost promenljive @LoopValue. Iskaz SET NOCOUNT poboljšava performanse uskladištene procedure. Primer: CREATE PROCEDURE [dbo].[procEmployeesgetNoCount1] AS SET NOCOUNT ON SELECT EmployeeID, Title, HireDate FROM dbo.Employees ORDER BY Title, HireDate Prilikom pokretanja procedure nema poruke „xx row(s) affected“ se ne prikazuje. Ponašaju se kao funkcije koje proizvode razne tipove informacija o događajima u SQL Server-u. Sistemska promenljiva @@RowCount Sistemska promenljiva @@RowCount proizvodi broj redova koji se dobija pomoću zadatog iskaza ili broj redova čiji podaci su izmenjeni zadatim iskazom. Sistemska promenljiva @@RowCount ima vrednost nula ukoliko zadati iskaz ne proizvode nijedan red ili ukoliko akcioni upit ne izmeni nijedan red. Primer: SELECT EmployeeID, Title FROM Employees WHERE Title = 'Sales Representative' SELECT @@RowCount as Ukupno U primeru se izdvajaju svi zaposleni radnici koji su prodavci. Sistemska promenljiva @@TranCount Funkciju @@TranCount se upotrebljava kada se koriste transakcije. Pomoću iskaza BEGIN TRAN sistemskoj promenljivoj @@TranCount dodeljujete vrednost jedan. Pomoću iskaza Rollback TRAN vrednost sistemske promenljive @@TranCount umanjujete za jedan. Pomoću iskaza COMMIT TRAN vrednost sistemske promenljive @@TranCount takođe umanjujete za jedan. Kada koristite ugnježdene transakcije, sistemska promenljiva @@TranCount vam pomaže da pratite koliko transakcija čeka na izvršenje. Sistemska promenljiva @@Identity Funkcija @@Identity proizvodi novu vrednost koja je upisana u tabelu u kojoj postoji kolona identiteta. Primer: INSERT INTO Table_Identity (ime, broj1, broj2) VALUES ('IT_Akadem', 120,150) SELECT @@Identity Sistemska promenljiva @@Error Pomoću funkcije @@Error dobija se broj greške koja se dogodila u iskazu koji je prethodio funkciji. Primer: INSERT INTO dbo.CustomerDemographics (CustomerTypeID,CustomerDesc) VALUES ('ITAkade', 'ttt') SELECT @@Error U primeru se upisuje red u tabelu. Ukoliko je upisivanje reda bilo uspešno, funkcija @@Error vraća vrednost nula. U suprotnom funkcija @@Error vraća broj greške koja se dogodila u programskom redu koji se nalazi odmah ispred programskog reda u kojem se poziva funkcija @@Error. Neke uskladištene procedure nemaju interfejs. Takve uskladištene procedure možete da pozivate, ali one ne prihvataju i ne vraćaju vrednosti. Druge procedure samo prihvataju vrednosti. Takve procedure imaju ulazne parametre, ali nemaju izlazne parametre. Treći tip uskladištenih procedura ima ulazne i izlazne parametre. Primer: CREATE PROCEDURE [dbo].[procEmp2] @Title nVarChar(50), @BirthDate DateTime AS SELECT EmployeeID, Title, BirthDate, HireDate FROM Employees WHERE Title = @Title or BIRTHDATE >= @Birthdate ORDER BY BirthDate Uskladištena procedura ima dva ulazna parametra, paramere @Title i @BirthDate. U uskladištenoj proceduri se ti parametri koriste kao promenljive u odredbi WHERE za opis posla i datum rođenja. Opcioni parametar pravite tako što SQL Server-u šaljete unapred definisanu vrednost za parametar. U prethodnom primeru parametar @Title je opcioni parametar, a parametar @BirthDate je obavezan parametar. Ukoliko se odlučite da izostavite parametar @title, uskladištenu proceduru pozivate na sledeći način: procEmployeesGetByTitleAndBirthDateOpt @BirtthDate = ’1/1/1960’ U primeru parametar @Birthdate zadaje kao imenovani parametar. Uskladištena procedura proverava vrednost parametra @Title. Ukoliko parametar @title ima vrednost Null, onda se u odredbi WHERE parametar ne koristi u uslovu. Ukoliko korisnik uskladištene procedure zada parametar @Title, uskladištena procedura ga koristi u uslovu u odredbi WHERE. Primer: CREATE PROCEDURE [dbo].[procEmployeesPR3] @Title nVarChar(50) = NULL, @BirthDate DateTime AS IF @Title IS NULL BEGIN SELECT EmployeeID, Title, BirthDate, HireDate FROM Employees WHERE BirthDate >= @BirthDate ORDER BY BirthDate END ELSE BEGIN SELECT EmployeeID, Title, BirthDate, HireDate FROM Employees WHERE Title = @Title AND BirthDate >= @BirthDate ORDER BY BirthDate END Opcioni parametar pravite tako što SQL Server-u šaljete unapred definisanu vrednost za parametar. U prethodnom primeru parametar @Title je opcioni parametar, a parametar @BirthDate je obavezan parametar. Ukoliko se odlučite da izostavite parametar @title, uskladištenu proceduru pozivate na sledeći način: procEmployeesGetByTitleAndBirthDateOpt @BirtthDate = ’1/1/1960’ U primeru parametar @Birthdate se zadaje kao imenovani parametar. Uskladištena procedura proverava vrednost parametra @Title. Ukoliko parametar @Title ima vrednost Null, onda se u odredbi WHERE parametar ne koristi u uslovu. Ukoliko korisnik uskladištene procedure zada parametar @Title, uskladištena procedura ga koristi u uslovu u odredbi WHERE. Za uskladištenu proceduru možete da deklarišete izlazne parametre. SQL Server izlazne parametre šalje pozivaocu uskladištene procedure. Primer: CREATE PROCEDURE dbo.procEmploeesIZ @TITLE NvARcHAR(50) = null, @Birthdate dateTime, @MyMessage Varchar (50) = NULL OUTPUT AS IF @Title IS NULL BEGIN SELECT EmployeeID, Title, BirthDate, HireDate FROM Employees WHERE BirthDate >= @BirthDate ORDER BY BirthDate SELECT @MyMessage = 'No Title' END ELSE BEGIN SELECT EmployeeID, Title, BirthDate, HireDate FROM Employees WHERE Title = @Title AND BirthDate >= @BirthDate ORDER BY BirthDate SELECT @MyMessage = 'Country Supplied' END SELECT @MyMessage Uskladištena procedura u primeru prihvata dva parametra, ima izlazni parametar @MyMessage. U iskazu IF u uskladištenoj proceduri se parametru @MyMessage dodeljuje odgovarajuća vrednost 'No Title', ukoliko je vrednost opcionog parametra @Title is Null, odnosno 'Country Supplied', ukoliko vrednost opcionog parametra @Title nije Null. Uskladištene procedure se najčešće koriste za menjanje podataka koji se nalaze u bazi podataka. Upisivanje podataka Uskladištene procedure pomoću kojih se zapisuju podaci obično imaju nekoliko ulaznih parametara, po jedan za svako polje tabele u koju se podaci upisuju. Primer: CREATE PROCEDURE [dbo].[procOrderDetailAddpr] @SalesOrderID int, @CarrierTrackingNumber nvarchar (25), @OrderQty smallint, @ProductID int, @SpecialOfferID int, @UnitPrice money, @UnitPriceDiscount money AS INSERT INTO it_Details (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount) VALUES (@SalesOrderID, @CarrierTrackingNumber, @OrderQty, @productID, @SpecialOfferId, @UnitPrice, @UnitPriceDiscount) Procedura prihvata sedam ulaznih parametara, po jedan za svako polje tabele it_Details u koju se podaci upisuju. U iskazu INSERT INTO koriste se ulazni parametri i njihove vrednosti se upisuju u tabelu, izlaznih parametara nema. Primer uskladištene procedure sa izlaznim parametrima: CREATE PROCEDURE procOrderDetailAddOutput @SalesOrderID int, @CarriertrackingNumber nvarchar(25), @OrderQty smallint, @ProductID int, @SpecialOfferID int, @unitPrice money, @unitPriceDiscount money, @SalesOrderdetailID int = 0 OUTPUT AS INSERT INTO it_Details (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount) VALUES (@SalesOrderID, @CarrierTrackingNumber, @OrderQty, @ProductID, @SpecialOfferID, @UnitPrice, @UnitPriceDiscount) SET @SalesOrderDetailID =@@IDENTITY Pomoću programskog koda izlaznom parametru @SalesOrder DetailId se dodeljuje vrednost sistemske funkcije @@Identity. Uskladištene procedure se koriste i za ažuriranje podataka i uklanjanje podataka. Ažuriranje podataka Primer: CREATE PROCEDURE [dbo].[procSalesOrderHeaderUpdate] @RegionID int, @NewFreight nchar AS UPDATE dbo.Region SET RegionDescription = @NewFreight WHERE RegionID = @RegionID Procedura ima dva ulazna parametra. Jedan parametar se koristi za polje RegionID. Drugi parametar se koristi za zadavanje nove vrednosti. Pomoću iskaza vrednost parametara @NewFraight zadajete vrednost kao u redovima u kojima se vrednost polja RegionID poklapa sa vrednošću parametara @ RegionID. Uklanjanje podataka Primer: CREATE PROCEDURE procSalesOrderDetailsDelete @SalesOrderDetailID int AS DELETE FROM Sales.SalesOrderDetails WHERE SalesOrderDetailID = @SalesOrderDetailID Primer: ALTER PROCEDURE [dbo].[IT_delete] @dept_number int AS BEGIN SET NOCOUNT ON; DELETE FROM it_Details WHERE SalesOrderID= @dept_number END Procedura prihvata jedan ulazni parametar: SalesOrderdetailID mušterije koju želite da uklonite iz tabele. Vidite da u iskazu DELETE postoji odredba WHERE u kojoj se koristi ulazni parametar @SalesOrderDetailID. Uskladištene procedure i privremene tabele SQL Server privremene tabele pravi u posebnoj sistemskoj bazi podataka TempDB. SQL Server pravi bazu podataka TempDB, prilikom svakog pokretanja, uklanjanja i kada prekinete njegovo izvršavanje. SQL Server bazu podataka TempDB koristi za zapisivanje mnogih privremenih objekata koje koristi tokom svog izvršavanja. Bazu podataka TempDB možete da koristite za deljenje podataka između uskladištenih procedura ili za obavljanje složenih poslova. Često ćete u uskladištenoj proceduri morati da koristite privremene tabele. Uskladištene procedure i zaštita podataka Uskladištene procedure, kao i pogledi, odličan su način zaštite podataka aplikacije. Možete da dodelite dozvole za korišćenje uskladištene procedure, a da ne morate da dodelite dozvole za tabele koje se u uskladištenoj proceduri koriste. Pretpostavite da ste u Acces-u napravili formular pomoću kog korisnici mogu da odaberu razne mušterije. Zatim, izvršavate uskladištene procedure kako biste upisali, ažurirali i uklonili podatke. U tom slučaju korisnicima možete da dodelite dozvole za korišćenje pogleda na tabelu Customers. Ne morate da im dodeljujete dozvole za upisivanje, ažuriranje ili uklanjanje podataka iz tabele. Umesto toga, možete im dodeliti dozvole za izvršavanje odgovarajućih uskladištenih procedura. Na taj način omogućavate korisnicima da lako naprave upite i izveštaje u kojima se koriste podaci iz tabele Customers (dodeljujete im dozvole za korišćenje pogleda na tabelu). Korisnici mogu samo da upisuju, ažuriraju i uklanjaju podatke pomoću uskladištenih procedura koje pozivaju pomoću programskog koda aplikacije. Pravljenje i korišćenje funkcija koje definišete Pomoću funkcija se dobija snaga i fleksibilnost koja se ranije nije mogla ostvariti pomoću pogleda i uskladištenih procedura. Postoje tri tipa funkcija koje definišete: skalarne funkcije, ugrađene funkcije pomoću kojih se dobija sadržaj tabele i funkcije sa više iskaza pomoću kojih se dobija sadržaj tabele. Primer: CREATE PROCEDURE [dbo].[procCustomersGetByCountryAndTitle] @CountryName Varchar (50), @ContactTitle VarChar (50) AS SELECT CompanyName, ContactName, ContactTitle, City FROM Customers WHERE Country = @CountryName AND ContactTitle = @ContactTitle ORDER BY City, CompanyName Uskladištena procedura CustomersGetByCountryAndTitle ima dva parametra. Ti parametri se koriste u odredbi WHERE za polja Country i ContactTitle. Pomoću uskladištene procedure procCustomersGetTemp pravi se privremena tabela u kojoj se nalazi informacije o mušteriji. U privremenu tabelu se upisuje rezultat izvršavanja uskladištene procedure procCustomersGetByCountryAndTitle, pri čemu joj se prosleđuju vrednosti USA i Owner, a te vrednosti se koriste za državu i opis posla. Najzad, pomoću uskladištene procedure se iz privremene tabele izdvajaju podaci, a rezultat se uređuje po poljima City i CompanyName. Skalarne funkcije Skalarne funkcije (Scalar Function) proizvode vrednost koja je tipa podataka koji je definisan pomoću odredbe RETURNS. Telo funkcije se nalazi unutar bloka BEGIN i END. Skalarne funkcije mogu da vrate vrednost bilo kojeg tipa podatka, osim tipa podataka text, ntext, image i cursor. Primer: CREATE FUNCTION dbo.FullName (@FirstName nVarChar (10), @LastName nVarChar(20)) RETURNS nVarChar(35) BEGIN RETURN (@LastName + ', '+@FirstName) END Prethodna funkcija prihvata dva parametra: @FirstName, @LastName između kojih je izraz i razmak. Funkcija se poziva na sledeći način: SELECT FirstName, LastName, dbo.FullName (FirstName, LastName) FROM Employees Ugrađene funkcije pomoću kojih se dobija sadržaj tabele Ugrađene funkcije pomoću kojih se dobija sadržaj tabele (Inline Table-valued Function) daju sadržaj tabele. Ugrađene funkcije pomoću kojih se dobija sadržaj tabele nemaju telo funkcije. Ove funkcije vraćaju rezultate jednostavnog iskaza SELECT. Primer: CREATE FUNCTION dbo.CustGetByTitle (@Title nVarChar(30)) RETURNS Table AS RETURN SELECT CustomerID, CompanyName, City, Region FROM Customers WHERE ContactTitle = @Title Prethodna funkcija prihvata parametar @Title. Funkcija vraća tabelu koja sadrži odabrana polja tabele Customers tako da vrednost polja ContactTitle odgovara vrednosti parametra @Title. Prethodnu funkciju pozivate na sledeći način: SELECT * FROM dbo.CustGetByTitle ('Owner') Funkcije sa više iskaza pomoću kojih se dobija sadržaj tabele Funkcije sa više iskaza pomoću kojih se dobija sadržaj tabele (Multi-Statement Table-Valued Function) slične su ugrađenim funkcijama pomoću kojih se dobija sadržaj tabele. Osnovna razlika je što ove funkcije nemaju telo, koje je definisano blokom BEGIN … END. Slično ugrađenim funkcijama pomoću kojih se dobija sadržaj tabele, ove funkcije vraćaju sadržaj tabele. - Prikaz šablona za Stored Procedure Pravljenje uskladištenih procedura pomoću Query editora Slika 14.3 - Okvir za obeležavanje tabela Primer - Listanje podataka pomoću uskladištene procedure: Slika 14.4 - Prikaz rezultata uskladištene procedure