sql - 如何选择我在 Oracle SQL 中修改的行

Question

我想更新一行，之后我想在 Oracle SQL 中检索整行。我正在使用的表和相应的查询如下所示。我想避免两次查询数据库。因此，我在更新后进行了选择，但我遇到了查询后显示的错误。知道如何解决这个问题吗？

CREATE TABLE mytable (
    sid NUMBER NOT NULL,
    eid VARCHAR(18) NOT NULL,
    first TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created TIMESTAMP,
    status VARCHAR(30),
    tid VARCHAR(18),
    wid VARCHAR(18),
    CONSTRAINT mytable_pk PRIMARY KEY (sid, eid)
    ENABLE
);

BEGIN
UPDATE mytable SET wid=?, status=? WHERE sid=?;
SELECT * FROM mytable WHERE sid=?;
END;

错误：

java.sql.SQLException
ORA-06550: line 3, column 7:
PLS-00428: an INTO clause is expected in this SELECT statement

对应的Java代码段：

PreparedStatement statement = prepareStatement(sql);
int index = 0;
statement.setString(index++, newWid);
statement.setString(index++, newStatus);
statement.setInt(index++, sid);
statement.setInt(index++, sid);
ResultSet results = statement.executeQuery();
if (results.next()) {
    Record rec = new Record();
    fill(rec, results);
}
return record;

标签： sqloracleplsqlsql-updatesql-returning

Answer 1

您可以使用returning into以下子句：

SQL> set serveroutput on;
SQL> DECLARE
  v_sid      mytable.sid%type;
  v_eid      mytable.eid%type;
  v_first    mytable.first%type;
  v_updated  mytable.updated%type;    
  v_created  mytable.created%type;
  v_status   mytable.status%type;
  v_tid      mytable.tid%type;
  v_wid      mytable.wid%type;        
BEGIN
  UPDATE mytable 
     SET wid=v_wid, status=v_status
   WHERE sid = v_sid
   RETURNING sid, eid, first, updated,
             created, status, tid, wid 
        INTO v_sid, v_eid, v_first, v_updated,
             v_created, v_status, v_tid, v_wid;
   dbms_output.put_line(' sid :' ||v_sid);  
   dbms_output.put_line(' eid :' ||v_eid);  
   dbms_output.put_line(' first :' ||v_first);  
   dbms_output.put_line(' updated :' ||v_updated);      
   dbms_output.put_line(' created :' ||v_created);  
   dbms_output.put_line(' status :' ||v_status);  
   dbms_output.put_line(' tid :' ||v_tid);  
   dbms_output.put_line(' wid :' ||v_wid);    
END;