Home » SQL & PL/SQL » SQL & PL/SQL » use UPDATE in function
use UPDATE in function [message #36858] Fri, 28 December 2001 03:20 Go to next message
orafan
Messages: 8
Registered: December 2001
Junior Member
Could anyone tell me why can not use UPDATE statement in a PL/SQL function? Thanks!

----------------------------------------------------------------------
Re: use UPDATE in function [message #36860 is a reply to message #36858] Fri, 28 December 2001 03:49 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
You can. Here is an example...

08:43:47 ==> create or replace function sf_update_sal (perc in number) return number
08:44:23 2 is
08:44:23 3 begin
08:44:23 4 update emp set sal = sal * (1 + perc/100);
08:44:23 5 return perc;
08:44:23 6 end;
08:44:25 7 /

Function created.

08:44:26 ==> create or replace procedure sp_test_update is
08:44:40 2 my_perc number;
08:44:55 3 begin
08:44:55 4 my_perc := sf_update_sal(10);
08:44:55 5 end;
08:44:56 6 /

Procedure created.

08:45:00 ==> select sal from emp order by 1 desc;

SAL
---------
5500
3300
3300
3272.5
3135
2695
1760
1650
1430
1375
1375
1210
1045
880

14 rows selected.

08:45:14 ==> exec sp_test_update;

PL/SQL procedure successfully completed.

08:45:37 ==> select sal from emp order by 1 desc;

SAL
---------
6050
3630
3630
3599.75
3448.5
2964.5
1936
1815
1573
1512.5
1512.5
1331
1149.5
968

14 rows selected.

08:45:42 ==>

----------------------------------------------------------------------
Previous Topic: Does Oracle 8.0.0.5 support the OBJECT feature ?
Next Topic: Where can I find Sql*Loader to install
Goto Forum:
  


Current Time: Thu Mar 28 08:43:08 CDT 2024