Mark Pilgrim, the owner and author of the famous Dive Into series of sites has returned to the online world, after his extended absense since October 2004. Hopefully, we will now see a nice steady stream of content.
Since you’ve all been introduced to Princess, I thought it time to post a few more pictures we’ve managed to snap of her over the last fortnight. No surprise, Princess loves to play – often at inopportune times. One of her favourties toys has been a little blue mouse made of rope, she can really dig her claws into it!
This evening whilst researching on the internet, Princess jumped onto the table like normal. This time, it was different; this time she saw the mouse cursor on the screen and thought it was the best thing since sliced bread. As I moved the mouse, she followed it intently over the screens. When I started to wiggle it a little in front of her, she started trying to touch and bite the screen – so cute!
As it turns out, our Princess isn’t very lady like – she flops and sprawls over everything and anything, makes for great little cuddles though. She is also an intrepid explorer, if there is anything in the house she can climb under, over, through or into – she had already been there.
RETURNING clause was implemented as part of the Oracle 10g release and is used to return information about the effected rows after issuing Data Manipulation Language (DML) statements. Prior to Oracle 10g, you would have needed to work around not having this feature, most likely by issuing additional statements to the database.
RETURNING clause has a few restrictions:
- it is only available for use on tables, materialised views, normal views based on a single table or an expression based on the previous three items
- it is only valid on a single-set aggregate. A single set aggregate is DML which only effects a single row or using an aggregate function in the
RETURNINGstatement (such as
The general syntax for the
RETURNING clause is:
INSERT INTO <table> (c1, c2, .., cn) VALUES (v1, v2, .., vn) RETURNING <expression> INTO <variables>
UPDATE <table> SET (c1) = (v1), (c2) = (v2), (cn) = (vn) WHERE <condition> RETURNING <expression> INTO <variables>
DELETE FROM <table> WHERE <condition> RETURNING <expression> INTO <variables>
This feature is particularly useful when
INSERTing into a table, where the Primary Key is sourced from a sequence and fetched via a
TRIGGER. In the below example, the ID of the newly inserted row is assigned to
pContactID using the
RETURNING clause. This is an elegant solution as it means you don’t have to
NEXTVAL from the sequence and assign that value to the
INSERT simply so you can return the new primary key value.
PROCEDURE Ins (pContactID OUT Contacts.ContactID%TYPE, pFirstname IN Contacts.Firstname%TYPE, pSurname IN Contacts.Surname%TYPE) IS BEGIN INSERT INTO Contacts (fname, sname) VALUES (pFirstname, pSurname) RETURNING ContactID INTO pContactID; END;
You could just as easily use it to return the information about a row deleted, such as:
PROCEDURE Del (pContactID IN Contacts.ContactID%TYPE,, pFirstname OUT Contacts.Firstname%TYPE, pSurname OUT Contacts.Surname%TYPE) IS BEGIN DELETE FROM Contacts WHERE ContactID = pContactID RETURNING fname, sname INTO pFirstname, pSurname; END;
RETURNING clause is for use with aggregates, an example illustrating its use is in order. The below example modifies
pContactID salary by
pPercentageChange and subsequently returns the updated total company salary expenditure.
PROCEDURE UpdateSalary (pContactID IN Contacts.ContactID%TYPE, pPercentageChange IN NUMBER, pGrossSalary OUT NUMBER) IS BEGIN UPDATE Contacts SET salary = salary * pPercentageChange WHERE ContactID = pContactID RETURNING SUM(salary) INTO pGrossSalary; END;
RETURNING clause provides the PL/SQL developer with a lot of flexibility. The real benefits however, come from the simplified PL/SQL and clarity gained in the code. If you’ve got a lot of application code or PL/SQL which isn’t utilising the power available to you – it might be time to undertake a clean up in your project.