一个oracle面试题

某天，群里突然冒出一个道友询问oracle的面试题，特此记录一下

一系列过程：
第一步建表：

DROP TABLE serv;
DROP TABLE terminal; 
CREATE TABLE serv(
       serv_id NUMBER(10),
       prod_id NUMBER(10),
       user_type VARCHAR2(30),
       terminal_name VARCHAR2(20)
)
tablespace ORAC_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 11M
    next 1M
    minextents 1
    maxextents unlimited
  );
COMMENT ON TABLE serv IS '用户资料表';
COMMENT ON COLUMN serv.serv_id IS '用户标识';
COMMENT ON COLUMN serv.prod_id IS '产品标识';
COMMENT ON COLUMN serv.user_type IS '用户类型';
COMMENT ON COLUMN serv.terminal_name IS '终端类型';
CREATE TABLE terminal(
       serv_id NUMBER(10),
       terminal_name VARCHAR2(20) 
)
tablespace ORAC_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 11M
    next 1M
    minextents 1
    maxextents unlimited
  );
COMMENT ON TABLE terminal IS '终端类型临时表';
COMMENT ON COLUMN terminal.serv_id IS '用户标识';
COMMENT ON COLUMN terminal.terminal_name IS '终端类型';

第二步，插入数据
我采用的手工添加，所用的方法如下
oracle 直接更新查询结果

SELECT * FROM serv FOR UPDATE;
SELECT * FROM terminal FOR UPDATE; 

serv 表结果如下：

terminal 表结果如下

第三步，创建存储过程如下：

--创建存储过程
CREATE OR REPLACE PROCEDURE sp_terminal
IS
BEGIN

--更新serv表  方法一
  UPDATE serv s SET s.terminal_name = (
        SELECT
        CASE
          WHEN s.PROD_ID = 1 AND s.USER_TYPE = 'A' THEN '固话'
          WHEN s.PROD_ID = 1 AND s.USER_TYPE = 'B' THEN '小灵通'
          WHEN S.PROD_ID = 2 THEN '宽带'
          WHEN S.USER_TYPE = 'C' THEN 'CDMA'
         ELSE '-1'
           END terminal_name
           FROM SERV S1
           WHERE S1.PROD_ID = S.PROD_ID AND S1.USER_TYPE = S.USER_TYPE
  ) WHERE EXISTS (
    SELECT NULL FROM SERV S1
           WHERE S1.PROD_ID = S.PROD_ID AND S1.USER_TYPE = S.USER_TYPE
  );
  COMMIT;
--更新serv表  方法二
UPDATE SERV S --用户资料表
SET S.TERMINAL_NAME=(SELECT
    CASE WHEN S.PROD_ID = 1 AND  S.USER_TYPE='A' THEN '固话'
         WHEN S.PROD_ID = 1 AND  S.USER_TYPE='B' THEN '小灵通'
         WHEN S.PROD_ID = 2   THEN '宽带'
         WHEN S.USER_TYPE='C' THEN 'CDMA'
          ELSE '-1'
          END  AS TERMINAL_NAME
    FROM TERMINAL T  --终端类型临时表
   WHERE  S.SERV_ID=T.SERV_ID)
   WHERE EXISTS (SELECT 1
   FROM TERMINAL T1 WHERE  S.SERV_ID=T1.SERV_ID);
   COMMIT;
--更新terminal表 方法一
  UPDATE terminal  T SET T.TERMINAL_NAME = (
        SELECT
              s1.TERMINAL_NAME
           FROM SERV S1
           INNER JOIN terminal T1
           ON S1.SERV_ID = t1.SERV_ID
           WHERE t1.SERV_ID = t.SERV_ID
  ) WHERE EXISTS (
          SELECT
              s1.TERMINAL_NAME
           FROM SERV S1
           INNER JOIN terminal T1
           ON S1.SERV_ID = t1.SERV_ID
           WHERE t1.SERV_ID = t.SERV_ID
  );
COMMIT; 
--更新terminal表 方法二
UPDATE terminal  T SET T.TERMINAL_NAME = (
       SELECT
       CASE
          WHEN s.PROD_ID = 1 AND s.USER_TYPE = 'A' THEN '固话'
          WHEN s.PROD_ID = 1 AND s.USER_TYPE = 'B' THEN '小灵通'
          WHEN S.PROD_ID = 2 THEN '宽带'
          WHEN S.USER_TYPE = 'C' THEN 'CDMA'
         ELSE '-1'
           END terminal_name
           FROM SERV S
           WHERE t.SERV_ID = s.SERV_ID
  ) WHERE EXISTS (
     SELECT
       CASE
          WHEN s.PROD_ID = 1 AND s.USER_TYPE = 'A' THEN '固话'
          WHEN s.PROD_ID = 1 AND s.USER_TYPE = 'B' THEN '小灵通'
          WHEN S.PROD_ID = 2 THEN '宽带'
          WHEN S.USER_TYPE = 'C' THEN 'CDMA'
         ELSE '-1'
           END terminal_name
           FROM SERV S
           WHERE t.SERV_ID = s.SERV_ID
  );
COMMIT; 
--------------
END sp_terminal;

后面，其他道友提出此种方法不适合数据量很大的情况，提出了一些方法，也记录在次

处于礼貌这里将聊天内容截图，仅仅展示相关文字

数据量大的话，这种更新就挂了。
buck into for all
数量大 采用 数组 批量更新
弄成分批提交。
引用块内容
bulk collect into for all