Stored Procedure in oracle to select and update table data. [message #422277] |
Tue, 15 September 2009 06:08  |
soorajms
Messages: 5 Registered: September 2009
|
Junior Member |

|
|
Hi All,
I am looking for an stored procedure or a function to select a row of data and then to update the column value in that row.
My table structure is as follows.
Table xyz (acc no number, name varchar(50), dept varchar(50))
The table has all acc numbers filled into it, but name and dept fields are empty.
I want to select a row of data using query
select accno from xyz where name is null and dept is null and rownum=1
once this is done, i want to update this row using the name and dept values which are send as parameter to this procedure/function.
I want to call a procedure or a function to do the entire task.
Request your inputs to achieve this.
Regards,
Sooraj.M.S
|
|
|
|
|
|
|
|
Re: Stored Procedure in oracle to select and update table data. [message #422294 is a reply to message #422288] |
Tue, 15 September 2009 06:55   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
you mean something like this?
XE@SQL> create table test as select to_char(rownum,'fm000')
2 x,
3 cast(null as varchar2(10)) y
4 from dual connect by level <= 100
5 /
Table created.
XE@SQL> select * from test;
X Y
---- ----------
001
002
003
004
005
006
007
008
009
010
011
X Y
---- ----------
012
013
014
015
016
017
018
019
020
021
022
X Y
---- ----------
023
024
025
026
027
028
029
030
031
032
033
X Y
---- ----------
034
035
036
037
038
039
040
041
042
043
044
X Y
---- ----------
045
046
047
048
049
050
051
052
053
054
055
X Y
---- ----------
056
057
058
059
060
061
062
063
064
065
066
X Y
---- ----------
067
068
069
070
071
072
073
074
075
076
077
X Y
---- ----------
078
079
080
081
082
083
084
085
086
087
088
X Y
---- ----------
089
090
091
092
093
094
095
096
097
098
099
X Y
---- ----------
100
100 rows selected.
XE@SQL> update test set y='A' where x='100';
1 row updated.
XE@SQL> commit;
Commit complete.
XE@SQL> declare
2 type c_table is table of test.x%type;
3 p_table c_table;
4 begin
5 update test set y=x where y is null returning x bulk collect into p_table;
6 for i in 1..p_table.count
7 loop
8 dbms_output.put_line(p_table(i));
9 end loop;
10 end;
11 /
001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
PL/SQL procedure successfully completed.
|
|
|
|
|
|
|