MERGE编译错误:
create or replace procedure PR_UpSert_T_UserInfo (
v_UserName T_UserInfo.UserName%TYPE,
v_Password T_UserInfo.Password%TYPE,
v_RoleID T_UserInfo.RoleID%TYPE,
v_Sex T_UserInfo.Sex%TYPE,
v_IDCard T_UserInfo.IDCard%TYPE,
v_Unit T_UserInfo.Unit%TYPE,
v_Department T_UserInfo.Department%TYPE,
v_Memo T_UserInfo.Memo%TYPE
) AS
begin
merge into T_UserInfo
using T_UserInfo
on (T_UserInfo.UserName=v_UserName)
when matched then
update set
T_UserInfo.Password=v_Password,
T_UserInfo.RoleID=v_RoleID
when not matched then
insert values(
v_UserName,
Password,
RoleID) ;
end PR_UpSert_T_UserInfo;
/
------解决方法--------------------------------------------------------
create or replace procedure PR_UpSert_T_UserInfo (
v_UserName T_UserInfo.UserName%TYPE,
v_Password T_UserInfo.Password%TYPE,
v_RoleID T_UserInfo.RoleID%TYPE
) AS
begin
merge into T_UserInfo
using ( select v_userName userName,v_Password Password,v_Roleid Roleid from dual) t
on (T_UserInfo.UserName=t.username)
when matched then
update set
T_UserInfo.Password=t.Password,
T_UserInfo.RoleID=t.RoleID
when not matched then
insert (T_UserInfo.UserName,T_UserInfo.Password,T_UserInfo.RoleID)
values(t.UserName,t.Password,t.RoleID) ;
end PR_UpSert_T_UserInfo;
------解决方法--------------------------------------------------------
http://www.vipcn.com/InfoView/Article_104338.html
MERGE INTO [your table-name] [rename your table here]
USING
(
[write your query here]
)[rename your query-sql and using just like a table]
ON
([conditional eXPression here] AND [...]...)