plsql cursor via straight sql

Im looking at the following two ways of retrieving a value and storing it later via an insert statement. i.e. via Pl/SQL cursors or via direct SQL. Is there any advantage to either approach? Or is there a more efficient approach?

Approach 1

Cursor system_date
Is 
 select sysdate from dual;
system_date_rec system_date%type;

Open s开发者_Python百科ystem_Date;
Fetch system_date into system_date_rec;

Insert into table(dateValue)
values(system_date_rec.date);

Approach 2

dateString  varchar(20);
Select sysdate into dateString from dual;
Insert into table(dateValue)
values(dateString);

---

How about approach 3:

Insert into table(dateValue)
values(sysdate);

or assuming you did actually need to do a select to get the data:

Insert into table(dateValue)
select dateValue from other_table where ...;

Regarding whether an explicit cursor or a SELECT INTO is preferable when one or the other is needed, I would go for the SELECT INTO because it is neater and safer if you expect the query to return exactly one row:

select some_value
into l_var
from other_table
where ...;

if l_var = 'A' then
  do_something;
end if;

Now you will get an exception (NO_DATA_FOUND or TOO_MANY_ROWS) if the number of rows returned is not as expected. With the cursor you will just end up with l_var unchanged, or set to the value from the first matching row - which probably means yu've got a bug but don't know it.

---

Each approach has it's merit but if it's one and only one value you are getting then I'd go with select ... into ... as this is much simpler and will check that you have one and only one value.

Although Tony's approach is possibly preferable to both in the right circumstances.

If you also want to get the value back there is always the RETURNING clause of the insert statement.

my_date_value date;
...
INSERT into table(datevalue)
values (sysdate)
returning sysdate into my_date_value;

---

I'd agree with @Tony and @MikeyByCrikey that select ... into is generally preferable, not least - in my personal, subjective opinion - because it keeps the select and into together instead of having the select out of sight up in the declare section. Not really an issue if it's simple but you've suggested you're doing several big queries and manipulations, which implies a longish procedure.

Slightly off-topic, but if all the manipulations are to gather data for a single insert at the end, then rather than having lots of separate variables I'd consider declaring a single variable as a row type and updating the columns as appropriate:

declare
    l_row my_table%ROWTYPE;
begin
    select ... into l_row.column1;
    select ... into l_row.column2;
    if l_row.column2 = 'A' then
        /* do something */
    end if;
    l_row.column3 := 'somevalue';
    fetch ... into l_row.column4;
    /* etc */
    insert into my_table values l_row;
end;