sql insert之后如何返回主键值？在asp.net程序里面的sql语句怎么写？

[strife013]

你好，是sql语句，不是存储过程，Insert into T_company_Apply (applyId, phase, ApplyPath, CurRole, ApplyGameAccount, Status,
records, Title, ApplyName, ApplyText, CreateDate, ApproveDate, AMOUNT,
CreateUserId, UpdateUserId, UpdateDate, CreateUserAccount ,ApplyNameEn)
VALUES (t_company_apply_seq.nextval,:Phase, :ApplyPath, :CurRole, :ApplyGameAccount, :Status,
:records, :Title, :ApplyName, :ApplyText, :CreateDate,
:ApproveDate, :AMOUNT ,:CreateUserId,:UpdateUserId,:UpdateDate,:CreateUserAccount,:ApplyNameEn);


:returnvalue = t_company_apply_seq.currval
这样写不行，调用的时候我想用一个output参数直接得到returnvalue的值，不用另外写存储过程了，这个怎么做啊？

[crazylaa]

给段java的给你参考下，相信asp.net应该也有相应的接口。

package test.oracle;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import test.oracle.conn.OConnection;

public class TestGetSeqInsert {

	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = OConnection.getConn();
			DatabaseMetaData dmd = con.getMetaData();

			System.out.println("database product name: "
					+ dmd.getDatabaseProductName());
			System.out.println("database product version: "
					+ dmd.getDatabaseProductVersion());
			System.out.println("supports generate keys? "
					+ dmd.supportsGetGeneratedKeys());

			String sql = "INSERT INTO test11 " + "VALUES (t_seq.nextval, ?)";

			ps = con.prepareStatement(sql, new String[] { "ID" }); // 后面一个参数表示需要返回的列名

			ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			ps.executeUpdate();

			if (dmd.supportsGetGeneratedKeys()) {
				rs = ps.getGeneratedKeys();
				while (rs.next()) {
					// 如果使用 rs.getInt("ID") 会报错，奇怪的 Oracle JDBC 驱动！
					System.out.println("ID: " + rs.getInt(1));
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

[todayandtomorrow]

SQL> select * from test_2;

        ID NAME                 DISTR
---------- -------------------- -----
         1 xiaoming             S
         2 xiaohua              S
         3 xiaosheng            P

SQL> set serveroutput on
SQL> 
SQL> declare
  2  id number;
  3  begin
  4  insert into test_2 values(4,'xiaohuang','S')
  5  returning id into id;
  6  commit;
  7  dbms_output.put_line(id);
  8  end;
  9  /

4

PL/SQL procedure successfully completed

我只能从数据库角度告诉你怎么返回一个插入的值，.net代码只能等待高人

[strife013]

....谁帮忙看下？

[shiyiwan]

还在公司答题啦？
[Quote=引用 2 楼 todayandtomorrow 的回复:]
SQL codeSQL>select*from test_2;

ID NAME DISTR---------- -------------------- -----1 xiaoming S2 xiaohua S3 xiaosheng P

SQL>set serveroutputon
SQL>
SQL>declare2 idnumber;3begin4insertinto test_2values(4,'xiaohuang','S')5 returning idinto id;6commit;7 dbms_output.put_line(id);8end;9/4

PL/SQLprocedure successfully completed

我只能从数据库角度告诉你怎么返回一个插入的值，.net代码只能等待高人
[/Quote]

[strife013]

顶

[sxq129601]

PL/SQL 里返回值好像没啥意思 。直接DBMS不就行了

[itweichao]

sql="insert into [table](列1，列2) values ()";
//关键是后面直接加入
sql+=" SELECT @@IDENTITY as returnID";
sqlcommand cmd=new sqlcommand(sql,connection);
//这就是返回主键值
int returnID=Convert.ToInt32(cmd.ExecuteScalar()) ;

[墨水鱼]

a3，要是Java里我知道，要是DB2我也知道怎么返回。
Java里用hibernate或其他框架，插入一个对象后，对象里的主键自动就获得了。
要是db2里，就用这样的SQL语句去插数据：SELECT ID, NAME FROM FINAL TABLE (INSERT INTO CKM_DOWNLOADEDLIST (NAME)VALUES (?));