在Oracle中纠正它

本文介绍了在Oracle中纠正它的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我是oracle的新手，所以我无法在Oracle中编写此查询，它显示了一些编译时错误

主要问题是：

1）不能使用@@ identity选项在sql中并返回最后插入的id（作为其自动插入值）

I am new in oracle so I am not able to write this query in Oracle its showing me some compile time error
main issues are:
1) Not able to use @@identity option which is in sql and return last inserted id (as its auto insert value)

ALTER PROCEDURE [dbo].[Insert_Update_Students](
@SelectedCourse varchar(4000) = NULL
,@StudentID int = NULL
 output
,@UserID int = NULL
,@YogaBefore bit = NULL
,@YourComment varchar(250) = NULL
)
AS  BEGIN
SET NOCOUNT ON;
IF(@StudentID IS NULL OR @StudentID <= 0)
BEGIN
INSERT INTO Students( 
[SelectedCourse]
,[UserID]
,[YogaBefore]
,[YourComment]
)
VALUES(
@SelectedCourse
,@UserID
,@YogaBefore
,@YourComment
)
set @StudentID=@@identity;
END
ELSE
BEGIN
UPDATE Students SET[SelectedCourse]=@SelectedCourse
,[UserID]=@UserID
,[YogaBefore]=@YogaBefore
,[YourComment]=@YourComment
 where
 [StudentID]=@StudentID

END
END

推荐答案

CREATE SEQUENCE student_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

然后你可以按照以下方式轻松使用序列

and then you can easily use sequence in following way

INSERT INTO Students( StudentId,
,SelectedCourse
,UserID
,YogaBefore
,YourComment
)
VALUES(
student_seq.NEXTVAL,
,@SelectedCourse
,@UserID
,@YogaBefore
,@YourComment

UPDATE Students SET[SelectedCourse]=@SelectedCourse
,[UserID]=@UserID
,[YogaBefore]=@YogaBefore
,[YourComment]=@YourComment
 where
 [StudentID]=student_seq.CURRVAL
)

这篇关于在Oracle中纠正它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！