Oracle 10g: Using The Returning Clause With ADOdb
Plymouth State University uses Oracle heavily due to its Student Information System of choice - SungardHE Banner. As such, I play around in Oracle a lot (sometimes a lot more than I'd like) and I occasionally find functionality that seems more cumbersome than it should.
One such item is selecting the last inserted value on an auto-incrementing column.
Historically, when you are inserting into a table with auto incrementing values (via a sequence) you have always been able to grab the last value with a simple SELECT statement (line 22):
SQL:
-
-- setup a table
-
CREATE TABLE bork (id INTEGER NOT NULL PRIMARY KEY, DATA VARCHAR2(10) NOT NULL);
-
-
-- create the sequence
-
CREATE SEQUENCE sq_bork INCREMENT BY 1 START WITH 1;
-
-
-- create a trigger for auto-incrementing the sequence'
-
CREATE OR REPLACE TRIGGER tr_sq_bork
-
BEFORE INSERT
-
ON bork
-
REFERENCING NEW AS NEW
-
FOR EACH ROW
-
BEGIN
-
SELECT sq_bork.NEXTVAL INTO :NEW.id FROM DUAL;
-
END;
-
/
-
-
-- insert a record into the table
-
INSERT INTO bork (name) VALUES ('Matt');
-
-
-- retrieve last inserted id
-
SELECT sq_bork.CURRVAL FROM dual;
As you see there, two statements must be executed to get that new id. The INSERT and the SELECT. Well, as of Oracle 10g you can utilize the RETURNING clause like so:
SQL:
-
INSERT INTO bork (name) VALUES ('Matt') RETURNING id INTO i_id;
That statement inserts a record into "bork" and returns the value of "id" into the "i_id" variable. Pretty sexy and all with one DML statement. Here's what we do at Plymouth to utilize the RETURNING clause with the PHP library ADOdb:
PHP:
-
<?php
-
//do your database object initialization here:
-
//$db = new ADONewConnection...
-
-
$sql = "BEGIN INSERT INTO bork (data) VALUES ('Matt') RETURNING id INTO :i_id; END;";
-
$stmt = $db->PrepareSP($sql);
-
$db->OutParameter($stmt, $inserted_id, 'i_id');
-
$db->Execute($stmt);
-
?>
Yup. 4 lines of PHP but only 1 statement sent to the database! I'd take the extra lines any day over the latency of data retrieval.
Discuss This Article
|
|
One Response to “Oracle 10g: Using The Returning Clause With ADOdb”
-
Adam Backstrom
Posted: Dec 22nd, 2008 at 12:58 pm1
Leave a Reply
Comment Preview:
I did this and it deleted all my Oracle tables. =(