OK, evo kako se kroz proceduru u jednom potezu dodaju i temelnica i stavke.
Obicno bih trazio da das skripte za tabele, ali posto se radi o demonstraciji samo, uradicu to sam, a ti vidi kako se cela stvar moze primeniti na tvoj konkretan slucaj.
Code:
IF Object_ID('H_DetaljiTemeljnice') IS NOT NULL DROP TABLE H_DetaljiTemeljnice
;
IF Object_ID('H_Artikli') IS NOT NULL DROP TABLE H_Artikli
;
IF Object_ID('H_Temeljnice') IS NOT NULL DROP TABLE H_Temeljnice
GO
CREATE TABLE H_Temeljnice
(
Vrsta INT NOT NULL,
Broj INT NOT NULL,
Prirodni_Broj AS RIGHT('00' + Cast(Vrsta AS varchar), 2) + '-' + Cast(Broj AS varchar) PERSISTED
-- PERSISTED fizicki cuva izracunatu vrednost u tabeli i ne izracunava je nanovo dok se ne promene paramatri
-- bez PERSISTED, kolona se izracunava nanovo svaki put kad se pozove
-- POsto je prirodni_broj = f(PK), ne ocekuju se ceste promene parametara pa PERSISTED stedi
-- vreme i napor CPU ali trosi prostor na disku, sto je danas jeftino
,
CONSTRAINT PK_H_Temeljnice PRIMARY KEY (Vrsta, Broj)
)
;
GO
CREATE TABLE H_Artikli
(
SifraARtikla varchar(10) NOT NULL,
NazivArtikla varchar(255) NOT NULL,
JedinicaMere varchar(20) NOT NULL,
TrenutnaCena money NOT NULL,
CONSTRAINT PK_H_ARtikli PRIMARY KEY (SifraArtikla)
)
;
GO
CREATE TABLE H_DetaljiTemeljnice
(
Vrsta INT NOT NULL,
Broj INT NOT NULL,
RbSTavke INT NOT NULL,
SifraARtikla varchar(10) NOT NULL,
Kolicina INT NOT NULL,
JedinicaMere varchar(20) NOT NULL,
Cena MONEY NOT NULL,
CONSTRAINT PK_H_StavkeTemeljnice PRIMARY KEY ( Vrsta, Broj, RbSTavke ),
CONSTRAINT FK_STavke FOREIGN KEY (Vrsta, Broj) REFERENCES H_Temeljnice (Vrsta, Broj),
CONSTRAINT FK_Artikli FOREIGN KEY (SifraARtikla) REFERENCES H_Artikli (SifraARtikla)
)
;
GO
-- Nekoliko artikala za primer
INSERT INTO H_ARtikli (SifraArtikla, Nazivartikla, JedinicaMere,TrenutnaCena)
SELECT 'PivoLav','Pivo Lav','6 pack',10.80
UNION
SELECT 'Mleko1pc','Mleko 1%','Kesa 3*1.5 l',4.29
UNION
SELECT 'JabukeC','Jabuke crvene','kg',0.99
UNION
SELECT 'Brasno500','Brasno T500','kg',1.20
;
GO
Nama treba stored procedura koju bismo pozvali ovako:
Code:
EXECUTE usp_h_TemeljnicaINSERT (@Vrsta = 1, @Artikli = 'PivoLav,100;JabukeC,40;Mleko1pc,20')
Dakle, saljemo listu artikala i kolicina kroz string @Artikli. Pretpostavlja se da ce aplikacija da sagradi string i da iz aplikacije mozes da posoves proceduru.
Prvo cemo dati funkciju koja string pretvara u tabelu. Uoci da string @Artikli = 'PivoLav,100;JabukeC,40;Mleko1pc,20' ima delimitere ',' i ';'. Tacka-zarez odvaja paroive (artikl,cena) a zarez odvaja elemente para.
Ima jedan sajt na kome gospodin Erland Sommarskog, SQL Server MVP, diskutuje mnoge vazne probleme, a medju njima i problem pretvaranja stringa u tabelu.
Ovo je sajt i poglavlje o listama:
http://www.sommarskog.se/arrays-in-sql.html
Diskusija je obimna i sveobuhvatna, i predlazem da je procitate svakako. Elem, sa Erlandovog sajta, skinuo sam jednu funkciju i ona izglea ovako:
Code:
IF Object_ID('H_iter_charlist_to_table') IS NOT NULL DROP FUNCTION H_iter_charlist_to_table
GO
CREATE FUNCTION H_iter_charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr)
VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
RETURN
END
GO
Funkcija prihvata dva parametra, @List i @Delimiter. Na primer, lista '1,2,3' treba da se vrati kao tri reda, delimiter je zarez. Proverite kako radi:
Code:
SELECT * FROM dbo.H_iter_charlist_to_table ('1,2,3',',');
Za nas slucaj, mozemo da uradimo ovo:
Code:
SELECT * FROM dbo.H_iter_charlist_to_table ('PivoLav,10;Mleko1pc,20;JabukeC,30',';')
;
-- Rezultat:
listpos str nstr
1 PivoLav,10 PivoLav,10
2 Mleko1pc,20 Mleko1pc,20
3 JabukeC,30 JabukeC,30
Naravno da nama treba nesto malo slozenije, pa mozemo da napisemo:
Code:
SELECT Rb = F.listpos
, Par = F.str
, ARtiklID = LEFT(F.str, Charindex(',', F.Str) - 1)
, Kolicina = RIGHT(F.str, Len(F.str) - ( Charindex(',', F.Str) ))
FROM dbo.H_iter_charlist_to_table ('PivoLav,10;Mleko1pc,20;JabukeC,30', ';') AS F;
-- Rezultat:
Rb Par ARtiklID Kolicina
1 PivoLav,10 PivoLav 10
2 Mleko1pc,20 Mleko1pc 20
3 JabukeC,30 JabukeC 30
Sad treba da nekako ovo sto smo napisali, ubacimo u proceduru koja ce da sev lepo pokupi i upise gde treba. Proceduru cemo napisati postepeno, da se lakse shvati o cemu se radi. Evo kako izgleda procedura koja vraca rezultat koji nam treba. Procedura prbvo izracunava potrebne vrednosti za insert novog reda u tabelu H_Temeljnice, zatim to kombinuje sa poslatom listom i vraca ono sto nam treba da insertujemo uH_DetaljiTemeljnice.
Code:
IF Object_ID('usp_h_TemeljnicaINSERT') IS NOT NULL DROP PROCEDURE usp_h_TemeljnicaINSERT
GO
CREATE PROCEDURE usp_h_TemeljnicaINSERT (@Vrsta INT, @Lista varchar(8000))
AS
DECLARE @TrenutniNajveciBroj int -- trenutno najveci broj za datu vrstu
DECLARE @SledeciBroj int -- broj za jedan veci od trenutno najveceg
SET @TrenutniNajveciBroj = COALESCE ((SELECT Max(Broj)
FROM H_Temeljnice
WHERE vrsta = @vrsta), 0)
;
SET @SledeciBroj = 1 + @TrenutniNajveciBroj
; WITH ListToTable AS
(
SELECT Rb = F.ListPos
, Par = F.str
, ARtiklID = LEFT(F.str, Charindex(',', F.Str) - 1)
, Kolicina = RIGHT(F.str, Len(F.str) - ( Charindex(',', F.Str) ))
FROM dbo.H_iter_charlist_to_table (@Lista, ';') AS F
)
SELECT Vrsta = @Vrsta, Broj = @SledeciBroj,
RbStavke = T.Rb, SifraArtikla = T.ArtiklID
, T.Kolicina, A.JedinicaMere, A.TrenutnaCena
FROM ListToTable AS T
JOIN dbo.H_Artikli AS A ON A.SifraArtikla = T.ArtiklID
RETURN
;
GO
EXECUTE usp_h_TemeljnicaINSERT @Vrsta = 1, @Lista = 'PivoLav,10;Mleko1pc,20;JabukeC,30'
;
Vrsta Broj RbStavke SifraArtikla Kolicina JedinicaMere TrenutnaCena
1 1 1 PivoLav 10 6 pack 10.80
1 1 2 Mleko1pc 20 Kesa 3*1.5 l 4.29
1 1 3 JabukeC 30 kg 0.99
Sada nam treba da procedura koja insertuje podatke:
Code:
-- Dodajemo INSERT INTO u proceduru:
IF Object_ID('usp_h_TemeljnicaINSERT') IS NOT NULL DROP PROCEDURE usp_h_TemeljnicaINSERT
GO
CREATE PROCEDURE usp_h_TemeljnicaINSERT (@Vrsta INT, @Lista varchar(8000))
AS
-- Kontrola ulazne liste, ne zelimo na listi artikle koji ne postoje
IF EXISTS (
SELECT L.ArtiklID
FROM (
SELECT ARtiklID = LEFT(F.str, Charindex(',', F.Str) - 1)
FROM dbo.H_iter_charlist_to_table (@Lista, ';') AS F
) AS L
LEFT JOIN H_Artikli AS A ON A.SifraArtikla = L.ARtiklID
WHERE A.SifraArtikla IS NULL
)
BEGIN
PRINT 'Jedan ili vise artikala na datoj listi ne postoji u tabeli H_Artikli. Procedura stopirana. Transakcija nije ni pocela.'
RETURN
END
-- Izracunajmo sledeci broj temeljnice:
DECLARE @TrenutniNajveciBroj int -- trenutno najveci broj za datu vrstu
DECLARE @SledeciBroj int -- broj za jedan veci od trenutno najveceg
-- COALSECE je za one slucajeve kad jos uvek ne postoji ni jedna temelnica za zadatu vrstu
SET @TrenutniNajveciBroj = COALESCE ((SELECT Max(Broj)
FROM H_Temeljnice
WHERE vrsta = @vrsta), 0)
;
SET @SledeciBroj = 1 + @TrenutniNajveciBroj
;
-- INSERT u tabele
BEGIN TRY -- ovo sluzi da uhvatimo greske
BEGIN TRANSACTION -- treba nam transakcija, jer radimo sa dve tabele
-- Korak 1: insertujemo red u tabelu Temeljnice
INSERT INTO H_Temeljnice (Vrsta, Broj)
SELECT Vrsta = @Vrsta , Broj = @SledeciBroj
;
-- Korak 2: insertujemo redove u tabelu DetaljiTemeljnice
; WITH ListToTable AS
(
SELECT Rb = F.ListPos
, Par = F.str
, ARtiklID = LEFT(F.str, Charindex(',', F.Str) - 1)
, Kolicina = RIGHT(F.str, Len(F.str) - ( Charindex(',', F.Str) ))
FROM dbo.H_iter_charlist_to_table (@Lista, ';') AS F
)
INSERT INTO H_DetaljiTemeljnice
(
Vrsta ,
Broj ,
RbSTavke ,
SifraARtikla ,
Kolicina ,
JedinicaMere ,
Cena
)
SELECT Vrsta = @Vrsta
, Broj = @SledeciBroj
, RbStavke = T.Rb
, SifraArtikla = T.ArtiklID
, T.Kolicina
, A.JedinicaMere
, Cena = A.TrenutnaCena
FROM ListToTable AS T
JOIN dbo.H_Artikli AS A ON A.SifraArtikla = T.ArtiklID
;
-- ako do ove tacke nije bilo greske, mozemo da zavrsiomo posao
COMMIT TRANSACTION
PRINT 'TRansakcija uspeco izvrsena.'
END TRY
BEGIN CATCH
-- Ovde ulazimo ako i samo ako je u BEGIN TRY- END TRY bloku dolso do greske
IF (XACT_State()) = -1 -- otvorena transakcija, COMMIT nije moguc
BEGIN
PRINT 'Doslo je do greske i transakcija je ponistena!'
ROLLBACK TRANSACTION;
END
ELSE IF (XACT_State()) = 1 -- otvorena transakcija, COMMIT moguc
BEGIN
PRINT 'Doslo je do greske, transakcija bi mogla d aprodje, ali je ponistena za svaki slucaj!'
ROLLBACK TRANSACTION; -- ovde je moglo i COMMIT
END
ELSE -- greska pre otvaranja transakcije
BEGIN
PRINT 'Doslo je do greske, transakcija nije ni krenula!'
ROLLBACK TRANSACTION;
END
-- Na kraju pokazemo poruku o gresci!
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
EXIT_HERE:
RETURN
;
GO
Da vidimo kako radi:
Code:
-- Ovo se ocekuje da prodje bez problema:
EXECUTE usp_h_TemeljnicaINSERT @Vrsta = 1, @Lista = 'PivoLav,1;Mleko1pc,2;JabukeC,3'
;
SELECT T.Prirodni_Broj
, D.RbStavke
, D.SifraArtikla
, D.Kolicina
, D.JedinicaMere
, D.Cena
FROM H_temeljnice AS T
JOIN H_DetaljiTemeljnice AS D ON D.Vrsta = T.Vrsta AND D.Broj = T.Broj
ORDER BY T.Vrsta, T.Broj , D.RbStavke
;
Prirodni_Broj RbStavke SifraArtikla Kolicina JedinicaMere Cena
01-1 1 PivoLav 1 6 pack 10.80
01-1 2 Mleko1pc 2 Kesa 3*1.5 l4.29
01-1 3 JabukeC 3 kg 0.99
Ovo ne bi smelo da prodje, ne postoji 'PivoLeopard'.
Code:
-- Ovo ne bi smelo da prodje:
EXECUTE usp_h_TemeljnicaINSERT @Vrsta = 1, @Lista = 'PivoLeopard,111;Mleko1pc,222;JabukeCrveniDelisesKarlovacki,3333'
;
Proceduru mozete nadgraditi, tako da proverava i da lista nije prazna. Ako je lista prazna, ne raditi nista. ne zelimo da imamo temeljnicu bez ijedne stavke. Ako i to dodamo, onda procedura postaje solidan interfejs za tabele Temeljnice i DetaljiTemeljnice.
Pzanja! Procedura radi sta treba da radi, ali ne vraca posebno koristan rezultat, osim poruka. Od aplikacije koja poziva proceduru, zavisi sta procedura treba da vrati. Mozete da vrartite neki integer, koji nesto znaci. A mozete da vratite i SELECT 'sve sto je uneseno'. Aplikacija koja poziva proceduru ce onda nesto uraditi sa vracenim rezultatom, na primer pretvoriti vraceni rezultat, ili jedan deo, u nekakvu poruku za korisnika,
Srecan rad.
:-)