开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜