一个oracle面试题

article/2025/10/3 7:30:54

某天，群里突然冒出一个道友询问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_DATApctfree 10initrans 1maxtrans 255storage(initial 11Mnext 1Mminextents 1maxextents 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_DATApctfree 10initrans 1maxtrans 255storage(initial 11Mnext 1Mminextents 1maxextents 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 = (SELECTCASEWHEN 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_nameFROM SERV S1WHERE S1.PROD_ID = S.PROD_ID AND S1.USER_TYPE = S.USER_TYPE) WHERE EXISTS (SELECT NULL FROM SERV S1WHERE S1.PROD_ID = S.PROD_ID AND S1.USER_TYPE = S.USER_TYPE);COMMIT;
--更新serv表  方法二
UPDATE SERV S --用户资料表
SET S.TERMINAL_NAME=(SELECTCASE 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_NAMEFROM TERMINAL T  --终端类型临时表WHERE  S.SERV_ID=T.SERV_ID)WHERE EXISTS (SELECT 1FROM TERMINAL T1 WHERE  S.SERV_ID=T1.SERV_ID);COMMIT;
--更新terminal表 方法一UPDATE terminal  T SET T.TERMINAL_NAME = (SELECTs1.TERMINAL_NAMEFROM SERV S1INNER JOIN terminal T1ON S1.SERV_ID = t1.SERV_IDWHERE t1.SERV_ID = t.SERV_ID) WHERE EXISTS (SELECTs1.TERMINAL_NAMEFROM SERV S1INNER JOIN terminal T1ON S1.SERV_ID = t1.SERV_IDWHERE t1.SERV_ID = t.SERV_ID);
COMMIT; 
--更新terminal表 方法二
UPDATE terminal  T SET T.TERMINAL_NAME = (SELECTCASEWHEN 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_nameFROM SERV SWHERE t.SERV_ID = s.SERV_ID) WHERE EXISTS (SELECTCASEWHEN 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_nameFROM SERV SWHERE t.SERV_ID = s.SERV_ID);
COMMIT; 
--------------
END sp_terminal;