一个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 '终端类型';

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;

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

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