开发者

How do I check whether the table has a record with a primary key values?

I have a exception to prevent inserting same 开发者_C百科records on my oracle.

If a record having spcific Key value already exists, record insertion interface has to be enabled.

I've heard that COUNT(*) has lots of cost which I don't want to use.

Is there any alternative way to make it at least cost?


There are two choices

BEGIN
  INSERT INTO table (pk_col, col_a, col_b) VALUES (:pk, :col_a, :col_b);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE table SET col_a = :col_a, col_b = :col_b WHERE pk_col = :pk;
END;

or

BEGIN
  UPDATE table SET col_a = :col_a, col_b = :col_b WHERE pk_col = :pk;
  IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO table (pk_col, col_a, col_b) VALUES (:pk, :col_a, :col_b);
  END IF;
END;

I've shown a PL/SQL code snippet since you don't specify a client language (eg PHP, Python...). You may handle exceptions and/or checking SQL%ROWCOUNT differently.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜