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:208759 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
