ITPub博客

首页 > Linux操作系统 > Linux操作系统 > The Power of EXECUTE IMMEDIATE and CASE WHEN[akadia]

The Power of EXECUTE IMMEDIATE and CASE WHEN[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-07-19 11:54:02 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60601/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    268
  • 访问量
    172259