I have an update SQL such like ‘UPDATE T SET d=d*2’, then query the updated value such like ‘SELECT d FROM T’. Is it possible to use one SQL call to implement this in JDBC? Thanks!
Add a comment
|
2 Answers
No, mixing DML with SELECT queries is already not possible in plain SQL, so JDBC can't do any much for you. You need to fire at least two queries, if necessary in a single transaction. An alternative is a stored procedure which you can then exeucte by a single CallableStatement, but that's overcomplicated for this particular simple purpose.
Comments
You can use Oracle's RETURNING INTO Clause to get the result into a variable in pl/sql. Something like this inside pl/sql, but achieving the same result using simple SQL might not be possible.
By the way, what database are you using?
SQL> declare
2 l_empno number := 7369;
3 l_sal_initial number;
4 l_sal_updated number;
5 begin
6 select sal
7 into l_sal_initial
8 from emp
9 where empno= l_empno;
10 dbms_output.put_line('initial sal is.. ' || l_sal_initial);
11
12 update emp
13 set sal = sal*2
14 where empno = l_empno
15 returning sal into l_sal_updated;
16
17 dbms_output.put_line('final sal is ...' || l_sal_updated);
18
19 rollback;
20 end;
21 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> /
initial sal is.. 800
final sal is ...1600
1 Comment
Vineet Reynolds
All that is fine, except for the query pertaining to Java and JDBC and possibly for a non-Oracle database.