azsd
asked on
how to use an IF statement in oracle's INSERT syntax like mssql server?
hi,
I am using mssql with oledb,
I have this sql text like:
;========================= ========== ========
IF (select count(*) from table_user where userid=1700))=0
INSERT INTO table_user (userid,username)
VALUES (1700,'azsd')
;========================= ========== ========
now our database moved to oracle 9i,
I use the "if" like this,sure its only occur an oledberror,nothing inserted.
;========================= ========== =========
IF NOT EXISTS(select userid from table_user where userid=1700) THEN
INSERT INTO table_user (userid,username)
VALUES (1700,'azsd')
END IF
;========================= ========== =========
I have another idea plan to use when state ments to insert the exists user into an other temp table but i think my methods were so ugly.
how can I do an check before insert in one query?
thx.
I am using mssql with oledb,
I have this sql text like:
;=========================
IF (select count(*) from table_user where userid=1700))=0
INSERT INTO table_user (userid,username)
VALUES (1700,'azsd')
;=========================
now our database moved to oracle 9i,
I use the "if" like this,sure its only occur an oledberror,nothing inserted.
;=========================
IF NOT EXISTS(select userid from table_user where userid=1700) THEN
INSERT INTO table_user (userid,username)
VALUES (1700,'azsd')
END IF
;=========================
I have another idea plan to use when state ments to insert the exists user into an other temp table but i think my methods were so ugly.
how can I do an check before insert in one query?
thx.
ASKER
thx your reply
I only can using query command,store procedure/function not suitable for my programing library....
it seems kololedb don't have that feather....
seems your solution are only suite in procedure,in sql plus it said:
@User_ID table_user.UserID%Type;
*
Line 1 occured error:
ORA-00900: invalied SQL statements....
I only can using query command,store procedure/function not suitable for my programing library....
it seems kololedb don't have that feather....
seems your solution are only suite in procedure,in sql plus it said:
@User_ID table_user.UserID%Type;
*
Line 1 occured error:
ORA-00900: invalied SQL statements....
The @ symbols is used in MS SQL Server to identify variables.
In Oracle the @ symbol is used as a short hand for the 'start' command which executes script files.
Try the above solution without the @ symbols.
In Oracle the @ symbol is used as a short hand for the 'start' command which executes script files.
Try the above solution without the @ symbols.
Sorry, in your variable declaration, you cannot use the "@" symbol. If you did, try doing it without the "@" symbol.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes,by little modify,these two nice script works!
ASKER
thx all yours,
I am going to modify my codes now.
I am going to modify my codes now.
@User_ID Table_User.UserID%Type;
SELECT NVL(UserID,0)
INTO @User_ID
FROM Table_User
WHERE User_ID = 1700
IF @User_ID = 0
INSERT INTO Table_User (UserID, UserName)
VALUES (1700,'@YourUserName');
END IF;