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( [AboutUs]
,[SelectedCourse]
,[UserID]
,[YogaBefore]
,[YourComment]
)
VALUES(
,@SelectedCourse
,@UserID
,@YogaBefore
,@YourComment
)
set @StudentID=@@identity;
END
ELSE
BEGIN
UPDATE Students SET [AboutUs]=@AboutUs
,[SelectedCourse]=@SelectedCourse
,[UserID]=@UserID
,[YogaBefore]=@YogaBefore
,[YourComment]=@YourComment
where
[StudentID]=@StudentID
END
END
Loading
andreas domevscekPosted Sep 26, 2014, 6:02 PM
create or replace function Insert_update_students
(p_variable_1 number,
p_variable_2 number)
return number
as
v_n number;
begin
insert into test (column1 ,column2 , column3)
values (column1_seq.nextval, p_variable_1, p_variable_2)
returning column1 into v_n;
return (v_n);
end;
if you want to use update then
update test
set column2 = p_variable_1, column3 = p_variable_2
where column1 = p_column1;
for example
so v_n is the automated sequence number; don't use @@ in oracle! does not work!
When it helped! Vote!