| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Updating a table from another - how to avoid a correlated subquery
A copy of this was sent to bgwillia_at_vcu.edu (Boyce G. Williams, Jr.)
(if that email address didn't require changing)
On Thu, 25 Mar 1999 14:54:23 GMT, you wrote:
>
>remco_at_rd1936.quicknet.nl (Remco Blaakmeer) wrote:
>
>>Try it in PL/SQL, like this (this is _untested_ code):
>>
>>DECLARE
>> CURSOR cur_phone IS
>> SELECT n.posted_date
>> , n.amount
>> FROM phonecalls p
>> , newdata n
>> WHERE n.duration_seconds = p.duration_seconds
>> , n.duration_minutes = p.duration_minutes
>> , n.destination_number = p.destination_number
>> , n.origin_number = p.origin_number
>> , n.call_date = p.call_date
>> , n.persion_id = p.persion_id
>> FOR UPDATE OF phonecalls.posted_date, phonecalls.amount;
>>BEGIN
>> FOR row_phone IN cur_phone LOOP
>> UPDATE phonecalls
>> SET posted_date = row_phone.posted_date
>> , amount = row_phone.posted_date
>> WHERE CURRENT OF cur_phone;
>> END LOOP;
>>END;
>>/
>>
>>The "UPDATE ... WHERE CURRENT OF" is very fast, since Oracle already
>>knows the rowid of the row it has to update.
>>
>>Remco
>
>I know I'm going off on a tangent on this, but I've never written a
>PL/SQL program and always assume one has to write it in C or COBOL
>(which I've done in DB2). I have written SQL scripts and executed
>them as "@someprogram" from the "SQL>" prompt. Can the same be done
>with PL/SQL without using compiled languages?
>
Yes & No. PLSQL is a compiled language but its compiled into the database. PLSQL has many features of 3gls (records, arrays of sorts, formal paramters to procedures and function, packages for encapsulation, session persistent variables (like globals), etc etc etc...
In fact, in Oracle7.x all of replication was written using plsql. Over time pieces of it have moved to C but the entire functionality was programable in plsql.
Oracle Applications are written almost entirely in plsql.
>Thanks in advance,
>
>
>Boyce G. Williams, Jr.
>
> .--------------------------------------------------------------------.
> | "People should have two virtues: purpose- the courage to envisage |
> | and pursue valued goals uninhibited by the defeat of infantile |
> | fantasies, by guilt and the failing fear punishment; and wisdom- a|
> | detached concern with life itself, in the face of death itself." |
> | Norman F. Dixon|
> '--------------------------------------------------------------------'
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |