in sql merge syntax how to pass userparameter
i am getting ERROR:Bind Variable "a" is NOT DECLARED
MERGE INTO EMP E1
USING (SELECT E2.EMPNO,E2.SAL FROM EMP2 E2 WHERE E2.EMP开发者_StackOverflow社区NO=:a)
ON
(E1.EMPNO=E2.EMPNO)
WHEN MATCHED THEN
UPDATE SET
E1.SAL=E2.SAL
WHEN NOT MATCHED THEN
INSERT VALUES(E2.EMPNO,E2.ENAME,E2.JOB,E2.MGR,E2.HIREDATE,E2.SAL,E2.COMM,E2.DEPTNO);
If you're in SQL*Plus (as I suspect from the error message given), then do a
variable a number
exec :a := 5
before you try to run the merge
statement.
On the other hand, if you're running the statement in PL/SQL, then there is no need to use the colon, instead, just place the variable name instead:
procedure do_the_merge(a in number) is
begin
merge....
....
where e2.empno = a
....;
end do_the_merge;
精彩评论