The Power of EXECUTE IMMEDIATE and CASE WHEN[akadia]

Never do procedurally what you can do in a single SQL statement. If you have a typical CASE construct you can use EXECUTE IMMEDIATE with a CASE statement.

Suppose you have the following UPDATE:

DECLARE CURSOR empcur IS
SELECT empno, job FROM emp
WHERE (empno IN ('7566','7369','7654'))
AND (job IN ('MANAGER','CLERK','SALESMAN'))
FOR UPDATE;
BEGIN
FOR rec IN empcur LOOP
IF rec.job = 'MANAGER' AND
rec.EMPNO = '7566' THEN
UPDATE emp
SET code = 'D01'
WHERE CURRENT OF empcur;
ELSIF
rec.job = 'CLERK' AND
rec.EMPNO = '7369' THEN
UPDATE emp
SET code = 'D02'
WHERE CURRENT OF empcur;
ELSIF
rec.job = 'SALESMAN' AND
rec.EMPNO = '7654' THEN
UPDATE emp
SET code = 'D03'
WHERE CURRENT OF empcur;
END IF;
END LOOP;
END;
/

Use dynamic SQL on the update since PLSQL doesn't understand CASE until 9i but that's not really relevant. Unlearn "procedural" coding. Try to do it in SQL, resorting to procedural logic only when truly necessary.

DECLARE
BEGIN
EXECUTE IMMEDIATE
'update emp
set code =
case when (job = ''MANAGER'' and empno = 7566) then ''D01''
when (job = ''CLERK'' and empno = 7369) then ''D02''
when (job = ''SALESMAN'' and empno = 7654)then ''D03''
else NULL
end
where empno in (''7566'',''7369'',''7654'')
and job IN (''MANAGER'',''CLERK'',''SALESMAN'')';
END;
/


请使用浏览器的分享功能分享到微信等