Stored procedure - prva pomoc

[ U prolazu @ 13.02.2007. 20:32 ] @

Da li je moguce napraviti stored proceduru koja vraca neke kolone? Ili ona moze samo da puni neku tabelu a ne i da je kreira?
Ako moze neko kratak kurs o stored procedurama i sta znace pojedini redovi u sledecoj (napisao sam ono sta znam ili pretpostavljam pa me ispravite ako gresim):

set ANSI_NULLS ON /ne znam/
set QUOTED_IDENTIFIER ON /ne znam/
go

-- =============================================
-- Author: Pero Perovic /ko je autor/
-- Create date: 19 February 2007 /datum kreiranja
-- Description: Insert record in table PROBA /opis/
-- =============================================
ALTER PROCEDURE [dbo].[Ubacuje_u_Proba]
/kljucne reci kojim se kreira sp sa svojim imenom/
@Prvi int, @Drugi int, @Treci tinyint, @Cetvrti tinyint, @Stanje char(1)
/parametri procedure/
AS
BEGIN /pocetak procedure/
SET NOCOUNT ON; /ne znam/
IF
(NOT EXISTS
(
SELECT *
FROM PROBA
WHERE (Prvi_ID=@Drugi) AND (Drugi_ID=@Drugi)
/ako ne postoji slog koji ima ovaj par promenljivih (primarni kljuc)/
)
)
BEGIN
INSERT INTO PROBA(Prvi_ID, Drugi_ID, Treci,
Cetvrti , Stanje)
VALUES (@Prvi, @Drugi, @Treci, @Cetvrti, @Stanje)
END /ubacuje slog sa ovim vrednostima/
ELSE
EXEC NemamPojma @Prvi, @Drugi, @Treci, @Cetvrtii, 'A'
END /kraj procedure/

Unapred hvala svima koji mi pruze bilo kakvu informaciju.

[ roberto555 @ 13.02.2007. 20:48 ] @

Code:

--ovo su mislim neke odredbe za sql ser.man.studio., nisam siguran

set ANSI_NULLS ON /ne znam/
set QUOTED_IDENTIFIER ON /ne znam/
go

-- =============================================
-- Author: Pero Perovic /ko je autor/
-- Create date: 19 February 2007 /datum kreiranja
-- Description: Insert record in table PROBA /opis/
-- =============================================

-- Create je za kreiranje a Alter je za promjenu (znači da ta SP več postoji) inače treba Create ako ne postoji

ALTER PROCEDURE [dbo].[Ubacuje_u_Proba]
/kljucne reci kojim se kreira sp sa svojim imenom/
@Prvi int, @Drugi int, @Treci tinyint, @Cetvrti tinyint, @Stanje char(1)
/parametri procedure/
AS
BEGIN /pocetak procedure/

--kada je ON, eliminira se poruka xx row(s) affested u prozoru SQL Ser.man.stud., i eliminira se poruka
-- koja se prolsjeduje klijentskoj aplikaciji mislim done_in_proc, da bi saznao broj redova ako je ovo na on korsti se
-- sistemska var. @@RowCount

SET NOCOUNT ON; /ne znam/
IF
(NOT EXISTS
(
SELECT *
FROM PROBA
WHERE (Prvi_ID=@Drugi) AND (Drugi_ID=@Drugi)
/ako ne postoji slog koji ima ovaj par promenljivih (primarni kljuc)/
)
)
BEGIN
INSERT INTO PROBA(Prvi_ID, Drugi_ID, Treci,
Cetvrti , Stanje)
VALUES (@Prvi, @Drugi, @Treci, @Cetvrti, @Stanje)
END /ubacuje slog sa ovim vrednostima/
ELSE

--exec služi za izvršavanje recimo druge SP kod ugnježdivanja, recimo exec druga_sp 'parametar', ili ako u varijabli
--imaš neki sql upit
--declare upit nvarchar(100)
--set upit="select * from tablica"
--exec upit

EXEC NemamPojma @Prvi, @Drugi, @Treci, @Cetvrtii, 'A'
END /kraj procedure/

naravno da može vračati vrijednosti sp, možeš kako koristiš između ostalog koristiti i izlazne parametre, deklarira se isto kao i ovaj al mu dodš output!!

[ CallMeSaMaster @ 13.02.2007. 22:00 ] @

Koliko ja znam Sp moze da radi sve kao klasicni upit- ona i nije nista drugo.
Naravno da mozes da kreiras sta hoces i da vracas pojedine kolone.Sve je isto kao i inace.

[ Fedya @ 14.02.2007. 08:06 ] @

Gore su ti (skoro) sve rekli, samo jos malo da dopunim ono sto nije receno

Citat:

U prolazu: Da li je moguce napraviti stored proceduru koja vraca neke kolone? Ili ona moze samo da puni neku tabelu a ne i da je kreira?

Mozes da vracas kolone (izvrsis select naredbu), ona takodje moze da puni i tabelu i da je kreira.

I ono sto je roberto propustio da napise je:
set ANSI_NULLS ON
ANSI_NULLS se odnosi na nacin kako sprocedure tretira NULL vrednosti u WHERE odredbi. Kada je postavljeno na ON
where nesto = null ili where nesto <> null nikada nece vratiti rezultat (moras koristiti IS NULL i IS NOT NULL).

set QUOTED_IDENTIFIER ON
ti omogcujuje da promenljive stavljas pod znakove navodnika. Sa OFF se ova mogucnost iskljucuje

[ Zidar @ 14.02.2007. 14:16 ] @

Malo teorestkog nklapanja, cisto radi prcisznosti:

Citat:

Koliko ja znam Sp moze da radi sve kao klasicni upit- ona i nije nista drugo.

Ovo je samo delimicno tacno. SP naravno moze moze da radi sve sto i klasican upit. Ali moze i mnogo vise - da prima parametre, da poziva druge procedure, da kreira tabele, da manipulise objekte, da izvrsi nekoliko upita i da ih kombinuje. Ispravnije je reci mozda "SP moze da radi sve sto mozes da uradis u query analyzery" sto je isto sto i "SP je sacuvana SKRIPTA" sto je opstije nego "SP je sacuvani upit". Sacuvani upit je VIEW.

[ U prolazu @ 15.02.2007. 12:17 ] @

Hvala svima na odgovorima.

Moze li neko da napise primer kako stored procedura kreira neku tabelu?

[ roberto555 @ 15.02.2007. 12:45 ] @

Code:

CREATE Table ime_tablice (kolona1 int not null primary key, kolona2 nvarchar(30))

/*dođe ovo:
ime_tablice--

-kolona1-------kolona2-----*/

kad u sql server management studiu napraviš tablicu u 'dizajneru', desni klik na nju -> script as -> create table ->[tako nekako piše] i tu stavi u međumemoriju(imaš i datoteku,ili new query), te onda negdje napravi 'Paste' i dobit češ upit koji da si napisao bi kreirao istu takvu kao što si tu u dizajneru!!!

-ako ispred 'ime_tablice' stavis znak #, kreiraš privremenu tabelu,koju može koristiti samo onaj tko ju je kreirao, ona se kreira u systemskoj bazi tempDB!

[ Zidar @ 15.02.2007. 14:10 ] @

Budi oprezan sa kreiranjem tabela pomocu stored procedura. Ako je to temp tabela (#TableName) OK. Ali ako tvoje procedure treba da kreiraju permanentne tabele u run time, ti verovatno ima sproblem sa dizajnom baze. SQL Serevr je relaciona baza podataka, uglavnom. To znaci da ne bi trebalo kreirati tabele 'on the fly'. Neznanjem ili nepaznjom mozes lako SQL bazu da pretvoris u najobicniji file sistem, onako kako se to radilo u Dbase i Clipper sistemima. Ono sto je u SQL tabela, tamo se zvalo 'file' ili 'datoteka' ili cak 'baza'. Aplikacija je morala da sve datoteke (baze :)) poveze u neki logican sistem i da vodi racuna o integritetu podataka. U SQL sistemima, baza vodi racuna o tome. Kad dizajniras bazu, aplikacija te uopste ne interesuje.

Bolje je da se zabavis dizajnom baze nego programiranjem stored procedura. Previse programiranja u SQL sistemim cesto vodi u propast a najcesce je potreba za mnogo programiranja znak da nesto u dizajnu nije u redu, pogotovo ako stored procedure kreiraju objekte baze (permanentne tabele). I kreiranje temp tabela u proceduri najcesce je znak da u dizajnu nie sve kako valja, ali se tolerise zbog brzine izvrsavanja (teorije je teorija a praksa je praksa)

[ branimir.ts @ 01.03.2007. 13:18 ] @

Citat:

Bolje je da se zabavis dizajnom baze nego programiranjem stored procedura. Previse programiranja u SQL sistemim cesto vodi u propast a najcesce je potreba za mnogo programiranja znak da nesto u dizajnu nije u redu.

Ovo apsolutno nije tacno. Sto vise programiras ( u ovom slucaju MSSQL stored proc), bolju kontrolu ces imati nad celim SQL sistemom, a mislim da je suvisno cak i da pominjem koliko mozes dobiti na performansama.

Citat:

I kreiranje temp tabela u proceduri najcesce je znak da u dizajnu nie sve kako valja, ali se tolerise zbog brzine izvrsavanja (teorije je teorija a praksa je praksa)

Imao sam skoro u praksi jedan zanimljiv zadatak. Naime, trebalo je napraviti u MSSQL2000 nesto poput pivot tabele -sto se u MS Accessu !! trivijalno resava i nazalost nije bilo drugog nacina osim da sednem i napisem stored proc sa dve temp tabele- jednostavno nije bilo drugog nacina-bar za MSSQL2000.

Mislim da je bolje da sto vise izbegavas "poglede", trigere i sve ostale stvari o kojima SQL servis " sam vodi racuna " - sve su to u sustini bespotrebne stvari koje samo mogu usporiti SQL sistem ( pa i ceo server ) i da, sto je moguce vise, paznje posvetis izradi i implemetaciji stored procedura.

Pozdrav

[ Zidar @ 01.03.2007. 14:01 ] @

Citat:

Pivot tabele se i u SQL 2000 sasvim lepo rade SELECT upitom, samo treba umeti. Svo vreme pokusavam da kazem da se cesto nedostatk SQL znanja nadoknadjuje sa mnogo programiranja. Programeri naravno uzivaju u programiranju i nije im tesko to da rade i zaista ih je tesko ubediti da tako ne treba. Zato ne vredi raspravljati, svako ce ostati na svome ionako.

I ja sam bivsi programer, a sad imam gomilu progarmera koji rade sta im kazem. U pocetku smo imali problem da odvojimo funkcije front enda od funkcija baze. Kad smo sve podesili kako treba, odjedamput je nestala potreba da programeri bilo sta rade na samoj bazi. Onda su iznenada programeri prestali da razmisljau o tome kako na front endu odrzati integritet podataka i slicne stvari. Sada se bave korisnijim stvarima. Mogu da se fokusiraju na user interface, reporte, i sta vec drugo treba. A DBA ne provodi po ceo dan pisuci nove i nove stored procedure, kako kome sta zatreba. Za tim jednostavno nema potrebe, kad je baza lepo dizajnirana. Pravila poslovanja se ponekad promene, nema veze, dobar dizajn baze to trpi bez problema.

Poenta je da svi odjednom imaju vise vremena nego ranije i mozemo da krenemo i na projekte koje nismo mogli da radimo ranije jer smo bili previse zauzeti programiranjem. Dobar dizajn baze smanjuje potrebu za programiranjem jer baza preuzme mnogo poslova na sebe. Ukupna produktivnost je dakle porasla, a to je krajnji cilj.

:-)

[ negyxo @ 01.03.2007. 14:30 ] @

Zidar ne bi se slozio sa tobom u jednoj stvari a to je

Citat:

Svo vreme pokusavam da kazem da se cesto nedostatk SQL znanja nadoknadjuje sa mnogo programiranja. Programeri naravno uzivaju u programiranju i nije im tesko to da rade i zaista ih je tesko ubediti da tako ne treba. Zato ne vredi raspravljati, svako ce ostati na svome ionako.

To da li programeri uzivaju u programiranju spageti koda je veoma dikutabilno. Ja licno ne uzivam. Moje vidjenje je da ti programeri koji mnogo pisu SQL code, recimo radije ce koristiti cursore nego select, je da su isto toliko dobri i u imperativnim jezicim, ovim klasicnim. Drugim recima, onaj koji pise u SQL bespotrebno dugacak i velik spageti code, taj isto to radi i u programskim jezicima, isto pise gomilu koda, ima mngo redudancije, kod je ne citljiv i rezultat svega je isto kao sto si i naveo, spor program, motanje gresaka, tesko za odrzavanje i jos gomila negativnih propratnih efekata.
Problem je u logici svakog pojedincanog programera, neki uvek teze za sto cistijim i kracim kodom/resenjima dok neki potezu tesku artiljeriju, dal' iz ne znanja ili neceg drugog ja to ne znam, ali do sada ja sam stekao takav utisak. Po meni, najbolja resenja kada se sistem usloznjava je kompozitnost programa, modularnost. Sto vise malih gotovih celina koje su kompaktne i spremene za 'plug in' sto na kraju rezultuje jednom cvrstom celinom. Mislim da se ovaj model moze uspesno implementirati na skoro sve oblasti, od dizajna DB-a, do modela aplikacije, do samih modula u aplikaciji.

[ branimir.ts @ 01.03.2007. 14:42 ] @

Citat:

Pivot tabele se i u SQL 2000 sasvim lepo rade SELECT upitom, samo treba umeti.

Citat:

...I ja sam bivsi programer...

Jasno mi je zasto vise nisi (SQL) programer :).

Citat:

baza preuzme mnogo poslova na sebe

.

Salu na stranu, to sto navodis jeste ok kada su tabele reda srednjih velicina ( do 1000 000 slogova). Dizajniras bazu i tabele , postavis kljuceve, definises relacije, indexe constrainte i miran si :).

Citat:

Onda su iznenada programeri prestali da razmisljau o tome kako na front endu odrzati integritet podataka

Sve to lepo radi kada je u poolu 100 ak konekcija....Ali sta ako -teorijski- imas vise hiljada konekcija, kada je isti SQL server vidljiv "spolja" - hoces li tada prepustiti bazi da vodi racuna o integritetu podataka ?

Pozdrav

[ negyxo @ 01.03.2007. 14:55 ] @

Citat:

E sad si mene bacio u razmisljanje. A kako ti to resavas ako nije tajna?

[ branimir.ts @ 01.03.2007. 15:09 ] @

Citat:

negyxo
radije ce koristiti cursore nego select

Sta fali kursorima?
Pa nisi valjda lud da emitujes komandu UPDATE ili ne daj Boze DELETE nad ogromnim tabelama??
Kursori su jako korisni u ovakvim situacijama.

Naravno da je zlatno programersko pravilo - sto manje koda - to je bolje, preglednije i efikasnije. Iskreno, ni ja nisam razumeo nikada one programere koje ti nazivas "teskom artiljerijom" ili kako god.

Citat:

E sad si mene bacio u razmisljanje. A kako ti to resavas ako nije tajna?

Postoji drugo zlatno pravilo u programiranju koje se zove "defanzivno programiranje" - predvidi i handluj situaciju iako se ista mozda nikada nece desiti .

Pozdrav

[ chachka @ 01.03.2007. 16:23 ] @

Citat:

branimir.ts: Pa nisi valjda lud da emitujes komandu UPDATE ili ne daj Boze DELETE nad ogromnim tabelama??
Kursori su jako korisni u ovakvim situacijama.

Kakva je razlika da li UPDATE ili kurzor prolazi kroz milion redova?

[ branimir.ts @ 01.03.2007. 17:17 ] @

Citat:

chachka: Kakva je razlika da li UPDATE ili kurzor prolazi kroz milion redova?

Razlika je u tome sto ce UPDATE formirati pokazivace na 1000 000 redova, dok ce kursor imati pointer samo na tekuci red.

Pozdrav

[ negyxo @ 01.03.2007. 17:21 ] @

Citat:

chachka: Kakva je razlika da li UPDATE ili kurzor prolazi kroz milion redova?

Ovo bi i ja voleo da cujem.

Citat:

branimir.ts:
Postoji drugo zlatno pravilo u programiranju koje se zove "defanzivno programiranje" - predvidi i handluj situaciju iako se ista mozda nikada nece desiti .

Nisi mi odgovorio na pitanje ali nema veze pojasnici kasnije. Sto se tice defanzivnog programiranja, hm... ne znam sta da ti kazem, u redu je do one granice kada ne predstavlja udar na dve vrste performanse:
1.) developersko, inzenjersko/<nazovi kako god hoces> vreme
2.) same performance programa, u ovom slucaju baze

Za ono moje pitanje, kada sam ga postavio imao sam u vidu integritet podataka nad PK i FK. To kako god okrenes ne mozes da resis na klijentu (tj. mozes ali po cenu jos gorih performansi). Na ovaj deo sam mislio, mada to i ne treba da odgovoris, ovaj deo je jasno kako se resava. Ali u integritet podataka ne spada samo PK i FK, nego i sama biznis logika, ako bi tako mogao da nazovem, tj. sve vrste constrainta. Sa ovim delom bi se delimicno slozio da moze da se resi na klijentu, ili jos bolje receno, moglo bi na klijentu da se uradi mnogo provere, pre nego sto stignu u bazu, sto na kraju dovodi do boljih performansi. Sve zavisi kakva vrsta provrere je u pitanju. I sada dolazimo do onoga vec opsteg poznatog, u zavisnosti od problema se prave i resenja.

[ negyxo @ 01.03.2007. 17:34 ] @

Citat:

branimir.ts: Razlika je u tome sto ce UPDATE formirati pokazivace na 1000 000 redova, dok ce kursor imati pointer samo na tekuci red.
Pozdrav

Taman sam hteo da ti napisem da nisi u pravu, bar ne za 99% slucajeva ali pre nego sto napisem najbolje ti daj primer. Ovako samo mogu da pogadjam na sta mislis, a nisam vidovit, tako da cu sigurno promasiti.

_{[Ovu poruku je menjao negyxo dana 01.03.2007. u 19:12 GMT+1]}

[ axx420 @ 01.03.2007. 19:29 ] @

Citat:

branimir.ts: Sve to lepo radi kada je u poolu 100 ak konekcija....Ali sta ako -teorijski- imas vise hiljada konekcija, kada je isti SQL server vidljiv "spolja" - hoces li tada prepustiti bazi da vodi racuna o integritetu podataka?

Citat:

negyxo: E sad si mene bacio u razmisljanje. A kako ti to resavas ako nije tajna?

Pa šta razmišljaš negyxo? Stvar je prosta, svaka od 100 ak ili hiljadu konekcija će samostalno brinuti o integritetu podataka

Zašto jednostavno kada može složeno.

[ negyxo @ 01.03.2007. 19:51 ] @

Ajde da si odgovorio neposredno posle pitanja ali ovako kada sam pojasnio sta se na sta odnosi...
Ako imas, hipoteticki, 100 000 konekcija i tabelu od 1 000 000 slogova zar ces ti na svakog klijenta svlaciti milion redova da bi odrzao integritet PK i FK kljuceva (nema smisla da posle PK i FK stavljam rec 'kljuceva' jer on K u PK i FK znaci key, ali ovako lepse zvuci

) Cak ti ni to ne garantuje i da uradis da ce podaci biti u integritetu jer neko moze vec dodati kljuc pre tebe, smisla ima da klijent preuzima na sebe kada imas nesto poput JMBG, minimalne zarade, itd. sto mozes da proveris na klijentu da ne saljes serveru zbog neceg sto se zna i pre slanja, tj. zna se preko BL (biznis logike) ili keshiranih podataka sa servera na klijentu koje suu granicne vrednosti. Stvar je u tome da se neki integritet moze proveriti na klijentu a neki ne moze, iliti moglo bi, ali uz veliki trosak performanci. Ako ovo nije nekom jasno, ja bolje od ovog ne umem da objasnim.

[ axx420 @ 01.03.2007. 20:10 ] @

Izvinjavam se, izgledalo mi je da je ironija očigledna.
"Defanzivno programiranje" je samo pokazalo da se ne može sve predvideti.
Integritet podataka je kao Zakon, ponekad tup ali jedini način da se uvede red.

[ negyxo @ 01.03.2007. 20:27 ] @

Onda primi ti moje izvinjenje, nesto sam malo izbacen iz takata (ne vezano za ovaj forum ovde) pa sam dosta hirovito reagovao. Inace ako budem dobre volje i uhvatio malo vremena napisao bi jednom generalno problem danasnjih baza odnosno njihov rad. Ukratko ono sto meni nedostaje u svetu baza je kompajler, a i taj integritet podataka i razna resenja mi se u opste ne svidjaju, ima mnogo preklapanja ili ispreplitanja raznih slojeva sa svakavim validatorima ali o ovome bi na nekoj drugoj temi.

[ chachka @ 01.03.2007. 20:56 ] @

Citat:

branimir.ts: Razlika je u tome sto ce UPDATE formirati pokazivace na 1000 000 redova, dok ce kursor imati pointer samo na tekuci red.

Po ovom clanku i kurzor pravi milion pointera.

Priznajem da sam malo koristio MS SQL Server i da ga vise ne koristim, ali znam da se u RDBMS-u kojeg koristim kurzori izvrsavaju cak i stotinjak puta sporije od adekvatnih cistih UPDATE naredbi.

[ negyxo @ 01.03.2007. 21:13 ] @

Ja jos dok nisam ni radio na SQL Serveru cuo sam bio da je svaki FETCH novi SELECT sa dodatnim opterecenjima, plus jos na mikro forumu ili serbiancafe-u ili je to bilo reci ovde na ES-u ne secam se, bilo price da svako u MS ko radi sa kursorima dobija otkaz, bas se pitam da li je to bila istina.

Ja koristim SQL Server svaki dan ali kursore slabo, jedino gde ih koristim je za generisane skriptova, neka vrsta automatizacije posla, prakticnije je preko kursora ali sve to je u samoj izradi baze a ne i koriscenje.

[ dekibre @ 02.03.2007. 02:03 ] @

@branimir.ts
- Koja je konfiguracija servera na kome brišeš ogromnu tabelu pomoću kursora pre svega me interesuje broj procesora i veličina memorije, da li ti je tempdb na posebnom RAID i kako ti je setovan SQL Server što se tiče paralelnog izvršavanja upita?

- Koliko dugo se izvršavalo brisanja ogromne tabele sa DELETE i te iste tabele sa kursorom i koliko ti se povećao log fajl prilikom izvršavanja obe komande i koliko se povećao tempdb prilikom brisanja kursorom?

[ dekibre @ 02.03.2007. 02:06 ] @

[ Zidar @ 02.03.2007. 17:19 ] @

Procitao sam clanak koji je Chachka naveo. Tamo postoji sekcija "Cursor adventages" u koja pocinje sa:

Citat:

Cursors are best used when performing row-by-row operations that can't be accomplished with set-based operations (i.e., when you need to fire a stored procedure once per row in a table).

pa onda malo dalje

Citat:

Quick and dirty
SQL developers are often under the gun to write code fast. Writing a cursor requires less mental effort than writing its set-based equivalent. Unfortunately these shortcuts often remain in production and cause problems further down the line. (Thanks for the above two observations from SQL MVPs Itzik Ben Gan and Erland Sommarskog.)

Cursors are faster than using while loops.

Sekcija "Cursor Advantages" zavrsava se recenicom:

Citat:

With advantages like these, you may wonder what are the disadvantages of using cursors. Please, oh please, oh please keep reading.

Poslusam ih, citam dalje i usekciji 'Disadvantages' nadjem ovo:

Citat:

Cursors are frequently the wrong tool for the wrong task. They're used for quick-and-dirty programming when a developer does not have a good understanding of set operations -- or they're used for the wrong task entirely.

bas sam to hteo da kazem

Uocite u sekciji Advantages kaze se dva puta "Cursors are faster than using while loops". Tu i jeste problem. Dobar dizajn baze nema potrebu za nikakvim 'loops'. Ima situacija u zivotu kada se to ne moze izbeci, cak se ni Identity ne mogu izbeci uvek. To su izuzeci od kojih ne treba praviti pravilo. Znaci, staracka mudrost bi bila: izbegavaj kursore i identity kad god mozes. Sa brojem godina iskustva i starosti, smanjuje se broj situacija kada mislis da se mora koristiti kusrso i identity.

A ja nisam vise SQL programer, jer nikad nisam ni bio SQL programer. SQL nije program environment pa stoga ne postoji tako nesto - SQL programer. Postoje programeri u raznim jezicima, platformama i okruzenjima (web, DOS, Windows, .NET).
A programetre neko mora da kontrolise jer u najboljoj nameri naprave stetu. Losi programeri naprave malu stetu, veliku stetu naprave dobri programeri kad se umesaju u sta im nije posao - 'cursors - they're used for the wrong task entirely'