Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure in oracle to select and update table data. (Win XP, Oracle 10g)
Stored Procedure in oracle to select and update table data. [message #422277] Tue, 15 September 2009 06:08 Go to next message
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 #422279 is a reply to message #422277] Tue, 15 September 2009 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just do it in a single update.

Regards
Michel
Re: Stored Procedure in oracle to select and update table data. [message #422283 is a reply to message #422279] Tue, 15 September 2009 06:20 Go to previous messageGo to next message
soorajms
Messages: 5
Registered: September 2009
Junior Member

will be good if you can help me out with the query. i want to get the value of the query as the acc number and at the same time the corresponding columns need to get updated.

Thank you in advance
Re: Stored Procedure in oracle to select and update table data. [message #422285 is a reply to message #422283] Tue, 15 September 2009 06:31 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Update table test  set col2=in_col2, col3=in_col3 where  col1=in_col1;

[Updated on: Tue, 15 September 2009 06:32]

Report message to a moderator

Re: Stored Procedure in oracle to select and update table data. [message #422288 is a reply to message #422285] Tue, 15 September 2009 06:42 Go to previous messageGo to next message
soorajms
Messages: 5
Registered: September 2009
Junior Member

I hope you havent understood the scenario correctly.

The table has three fields say col1,col2,col3, only col1 is filled in it. I want to update col2 and col3 of one row and return the value of col1 in that row. I hope this can be done only by using a SP or a function.

[Updated on: Tue, 15 September 2009 06:44]

Report message to a moderator

Re: Stored Procedure in oracle to select and update table data. [message #422291 is a reply to message #422288] Tue, 15 September 2009 06:48 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
return the value of col1 in that row.


What does this means?
What do you want to achieve?

Please explain properly.

regards,
Delna
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 Go to previous messageGo to next message
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.


Re: Stored Procedure in oracle to select and update table data. [message #422295 is a reply to message #422291] Tue, 15 September 2009 06:56 Go to previous messageGo to next message
soorajms
Messages: 5
Registered: September 2009
Junior Member

Delna,

I want to write a procedure. I have a table with three columns(name , id and details).

In that table, one column(id) is populated with around 4 million rows of data and other columns are with null values. Once when an id is used we need to update name and dept details to that id.

I am looking for a query to retrive the id on screen at the same time i want to update the values of the name and details column.


Thanks in advance.

Re: Stored Procedure in oracle to select and update table data. [message #422298 is a reply to message #422295] Tue, 15 September 2009 07:08 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
CREATE OR REPLACE FUNCTION TEST_FUN(P1 INTEGER, P2 VARCHAR2, P3 VARCHAR2)
RETURN INTEGER
AS
    UPDATE <TABLE>
        SET <COL2> = P2,
        <COL3> = P3
        WHERE <COL1> = P1;

    RETURN P1;
END;


Are you looking for this type of function?

regards,
Delna
Re: Stored Procedure in oracle to select and update table data. [message #422318 is a reply to message #422298] Tue, 15 September 2009 08:38 Go to previous messageGo to next message
soorajms
Messages: 5
Registered: September 2009
Junior Member

Hi All,

Thank you for all the responses.

The issue is resolved. I created a SP and a function. I called that SP from the function.

The code is as follows.

Create or replace function abc(var1,var2)
Return Number AS
Begin
declare

num Number;
Begin
select acc_no into num from xyz where name is null and details is null and rownum=1;
updateid(a,b,c);
return num;
end;
end;

create or replace procedure updateid(a in varchar2, b in varchar2, c in number)
AS
Pragma Autonomous_Transactions
Begin
Update xyz set name=a,details=b where id = c;
commit;
end;

now run the query below.

select abc('name1','details1') from dual;

This will return the id in which the name and details are getting updated.


Re: Stored Procedure in oracle to select and update table data. [message #422325 is a reply to message #422318] Tue, 15 September 2009 08:52 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It will be hard to make it worse.
Oh yes, I know, add "exception when others then null;" in your procedure and function.

Regards
Michel
Previous Topic: Problems that we face when migrate from oracle9i to 10g
Next Topic: GROUP BY FUNCTION
Goto Forum:
  


Current Time: Fri Dec 09 02:07:42 CST 2016

Total time taken to generate the page: 0.24764 seconds