1. Performance (maybe not as much on insert, but updates an queries definitely faster). When running queries in Oracle, a plan is made on how to efficiently retrieve the data. The PL/SQL is precompiled and an action plan is made to optimize performance.
2. Transaction and Locking. A procedure is an implied transaction block that the _database_ manages. If your database supports the ability for transaction and locking, CF should really never be used. If you have multiple SQL statements to execute, when in a procedure, it one fails then the actions are not commited to the database. 3. Security. Security. Security. This is by far the biggest and most important aspect of procedures in my opinion. I can't sleep easy at night knowing an application's SQL statements (a roadmap diagraming your database) is stored in open text files on your web server (which is the server most commonly comprimised). Additionaly, with procedures and packages you can start to leverage the role based permission of your database. For instance, the only actions a user is allowed to perform in the database is to execute a certain set of methods. When the SQL is stored in CF, then the user must have permission to select, insert, update & delete on every table in your database. (Unless you want to get into the enormous task of specifying each table/privelage seperately) 4. Inline SQL functions. The ability to call custom functions within a query. These are the first 4 that benefits that come to mind, but there are surely more. PL/SQL is a very very very simple language. You can read Oracle Press PL/SQL book in a weekend and become an expert. -Adam On 6/6/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: > What type of advantages does an SP in oracle(or whatever) have over queries > for basic DB inserts? > > On 6/6/05, James Holmes <[EMAIL PROTECTED]> wrote: > > > > I agree here too - I've been writing stored procs (and other PL/SQL) for > > a few things and I'm eventually going to convert everything I have to > > them (portability is not an issue for us). A lot of things in PL/SQL are > > similar to CF, in fact - a PL/SQL table works in a similar way to a CF > > query for example. > > > > -----Original Message----- > > From: RADEMAKERS Tanguy [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, 7 June 2005 1:08 > > To: CF-Talk > > Subject: RE: Get New Record ID in ORACLE > > > > You don't need to use triggers at all if you use stored procedures: > > > > ------------------------------setup.sql------------------------------- > > CREATE TABLE t > > ( > > ID INTEGER NOT NULL, > > NAME VARCHAR2(50) > > ); > > > > CREATE SEQUENCE s; > > > > CREATE OR REPLACE PROCEDURE t_insert (newname IN VARCHAR2, newid OUT > > INT) > > AS > > BEGIN > > INSERT INTO t > > (ID, NAME) > > VALUES (s.NEXTVAL, newname) > > RETURNING ID > > INTO newid; > > END; > > > > / > > > > ------------------------------scribble.cfm---------------------------- > > <cfloop collection="#cgi#" item="header"> > > > > <cfstoredproc procedure="t_insert" datasource="trademak"> > > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" > > dbvarname="newname" value="#header#"> > > <cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" dbvarname="newid" > > variable="myNewId"> > > </cfstoredproc> > > > > <cfoutput>#myNewId#: #header#<br></cfoutput> > > > > </cfloop> > > > > > > plus, you can use packages to organize your procedures - either one per > > table with all CRUD code (many tools can auto generate this for you if > > you wish) or whatever (i'm a fuseboxer, so i create an [APPCODE]_QUERIES > > package for selects and an [APPCODE]_ACTIONS package for inserts, > > deletes, and updates). > > > > Many people shy away from stored procs because they think they are > > complicated, but they aren't and they have many benefits over plain > > cfquery (IMNERHO). > > > > /t > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208800 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
