[es] - Koriscenje funkcije/procedure umesto INSERT/UPDATE-a

Dejan Topalovic
Dejan Topalović
Senior Oracle DBA & Senior PL/SQL
Developer, Erste Sparinvest (Erste
Bank), Vienna, Austria
Vienna

Član broj: 635
Poruke: 1374
*.1.14.vie.surfer.at

Sajt: www.baze-podataka.net

+2 Profil

Re: Koriscenje funkcije/procedure umesto INSERT/UPDATE-a

^{16.07.2004. u 18:32 - pre 240 meseci}

Procedure i funkcije se koriste za malo slozenije programe, dok INSERT/UPDATE/DELETE mozes direktno koristiti kad radis stvari "onako na brzaka za jednokratnu upotrebu maltene".
Procedure i funkcije se kompajliraju jednom samo, dok ti pojedinacnu naredbu INSERT/DELETE/UPDATE moras u tom SQL upitu svaki put parsovati pri izvrsenju, osim ako se ne nalazi u cache-u, sto je malo vjerovatno, jer se ne radi o SELECT-u.

Procedure i funkcije omogucavaju modularnost, prosirivost, mogu se koristiti na vise mjesta i u drugim programima, a lako se odrzavaju.

Inace, ako ti zatreba trenutna vrijednost neke sekvence, ne moras koristiti posebnu funkciju da ti vraca sequence ID, nego mozes koristiti built-in opciju sequence_name.CURRVAL . Naravno, to koristi samo u posebnim slucajevima, jer ako neki drugi korisnik pokrene neki program, koji takodje koristi vrijednosti te sekvence, onda ti CURRVAL nece dati zeljenu vrijednost, odnosno zadnju unesenu u tvom programu.

Dovoljno?

Blog - baze podataka
---------------------
Oracle OCP DBA (9i & 10g)
Oracle Database: SQL Certified Expert
Oracle OCP Developer
Certified MySQL DBA

Odgovor na temu

Jim Jackson
Veljko Sarac
Beograd

Član broj: 4456
Poruke: 97
*.nat-pool.bgd.sbb.co.yu

Profil

Re: Koriscenje funkcije/procedure umesto INSERT/UPDATE-a

^{21.07.2004. u 11:45 - pre 240 meseci}

Praksa koriscenja PL/SQL procedura na taj nacin postoji. Sve zavisi od toga kako koncipiras aplikaciju i gde drzis logiku aplikacije. Ako logiku drizis u pl/sql-u onda je opravdano koristiti procedure i za insert/update, tj. u takvim procedurama onda vrsis i proveru parametara i obradu gresaka. Takodje preko procedura se mogu lepo kontrolistai prava pristupa.

Dobro nije bas sve tako jednostavno ... Na taj nacin (drzanjem business logic-a ) na DB-serveru mozes preopteretiti isti (ali to je vec filozofska rasprava) ...

http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx

Citat:

There are several reasons for using stored procedures to access data, below I’ve attempted to capture the 3 that I always reference:

1. Ad-hoc SQL Script is brittle

You would never put business logic code in your presentation tier, right? Why put ad-hoc SQL in your business logic layer? Embedded SQL script is very brittle; small changes to the database can have severe impacts on the application. Whereas stored procedures afford abstraction between the data and the business logic layer. The data model can be dramatically changed, but the stored procedures can still return identical data.

2. Security

What permissions are required for working with your data? If embedded SQL is used it likely means that the application can execute any INSERT, UPDATE, DELETE, SELECT script it desires. You wouldn’t - hopefully you don’t - run as the administrator (sa) account on your server, so why allow full access to the database itself?

Stored procedures allow for better data protection by controlling how the data is accessed. By granting a database login EXECUTE permissions on stored procedures you can specify limited actions to the application. Additionally, stored procedures are a counter-measure to dangerous SQL Script injection attacks, a susceptibility that applications using embedded SQL are more vulnerable to.

3. Performance

A counter argument for performing data manipulation in a stored procedure, vs. simply retrieving the desired records in a SELECT statement, is it slows SQL Server down. That’s not exactly correct. In many cases you can get better performance by looping and filtering data in SQL Server than you could performing the same loops and filters in the Data Access Layer – databases are intrinsically designed to do this, while you and I have to write our own code (which do you think is going to be faster?). It is, however, important to understand how SQL Server uses indexes and clustered indexes. I’d recommend SQL Server 2000 Performance and Optimization Tuning to learn more about this and other SQL Server performance guidelines

Odgovor na temu