SQL-Interview-Questions2

2021年11月 上海美维电子有限公司 SQL面试题

环境

1、 数据库环境为 Oralce

2、 测试用表及数据环境如下

--分析结果
CREATE TABLE MTG_CHEMLAB_MAINTAIN_NEW(
   TDATE DATE , --取样日期
    PROCESS VARCHAR2(30), --工序
    BZYL  NUMBER(18, 4), --标液量
    CALCULATION_COEFF NUMBER(18,4), --计算系数
    ALAYSIS_RESULT NUMBER(18,4),--分析结果
    FORMULA VARCHAR2(100) --分析结果公式
);
INSERT INTO  MTG_CHEMLAB_MAINTAIN_NEW
select  to_date('2021-11-09','yyyy-mm-dd') TDATE ,'内层IF' PROCESS,0.1089 BZYL,0.366 CALCULATION_COEFF ,null ALAYSIS_RESULT ,'BZYL*CALCULATION_COEFF*AD_VALUE*2.55' FORMULA from dual
union all
select  to_date('2021-11-10','yyyy-mm-dd') TDATE ,'内层IF' PROCESS,0.1064 BZYL,0.275 CALCULATION_COEFF ,null ALAYSIS_RESULT ,'BZYL*CALCULATION_COEFF*AD_VALUE' FORMULA from dual
union all
select  to_date('2021-11-08','yyyy-mm-dd') TDATE ,'SM' PROCESS,0.1064 BZYL,0.275 CALCULATION_COEFF ,null ALAYSIS_RESULT ,'BZYL*CALCULATION_COEFF*AD_VALUE-100' FORMULA from dual;

--每天的调整系数
CREATE TABLE MTG_CHEMLAB_SHIFT_VALUE_NEW(
    TDATE DATE , --日期
    AD_VALUE NUMBER(18,4) --调整系数
  );
INSERT INTO  MTG_CHEMLAB_SHIFT_VALUE_NEW
select  to_date('2021-11-08','yyyy-mm-dd') TDATE,1.0377  AD_VALUE from dual
union all
select  to_date('2021-11-09','yyyy-mm-dd') TDATE,1.0018  AD_VALUE from dual;

题目

1.写一个存储，要求：

输入参数：1).取样日期，2).工序；
输出参数：1).分析结果，2).提示消息。

答案

CREATE OR REPLACE
PROCEDURE learn_1 ( 
	IN_TDATE IN VARCHAR2,
	IN_PROCESS IN  VARCHAR2,
	OUT_ALARYSIS_RESULT OUT NUMBER,
	OUT_TITLE OUT VARCHAR2)
IS
	V_RESULT_COUNT NUMBER;
BEGIN
	SELECT COUNT(ALAYSIS_RESULT) INTO V_RESULT_COUNT  FROM MTG_CHEMLAB_MAINTAIN_NEW 
	WHERE TO_CHAR(TDATE,'yyyy-mm-dd')= IN_TDATE AND  PROCESS = IN_PROCESS;
	IF (V_RESULT_COUNT > 1) THEN
	  OUT_TITLE := '获取数据失败--错误代码****';
	ELSE
		SELECT ALAYSIS_RESULT INTO OUT_ALARYSIS_RESULT FROM MTG_CHEMLAB_MAINTAIN_NEW 
		WHERE TO_CHAR(TDATE,'yyyy-mm-dd')= IN_TDATE AND  PROCESS = IN_PROCESS;
		OUT_TITLE := '获取数据成功';
		DBMS_OUTPUT.put_line('分析结果：' || OUT_ALARYSIS_RESULT);
	END IF;
	DBMS_OUTPUT.put_line(OUT_TITLE);
END;

2.写一个存储，要求：

1).分析结果
计算分析结果的值是MTG_CHEMLAB_MAINTAIN_NEW表中分析结果公式计算出的，
公式表达式里
1).BZYL：MTG_CHEMLAB_MAINTAIN_NEW.BZYL；
2).CALCULATION_COEFF： MTG_CHEMLAB_MAINTAIN_NEW.CALCULATION_COEFF；
3).AD_VALUE： MTG_CHEMLAB_SHIFT_VALUE_NEW.AD_VALUE.
2).提示消息:

1. 如果分析结果公式里有每天的调整系数， 表MTG_CHEMLAB_SHIFT_VALUE_NEW中没有此日的调整系数数据，则提示”请维护此取样日期的调整系数！”;
2. 如果没设分析结果公式 ，则提示”此取样日期的取样工序没设公式，请确认！”;
3. 如果能成功计算出分析结果，则提示“OK”.

答案

CREATE OR REPLACE
PROCEDURE learn_2 ( 
	IN_TDATE IN VARCHAR2,
	IN_PROCESS IN  VARCHAR2,
	OUT_ALARYSIS_RESULT OUT NUMBER,
	OUT_TITLE OUT VARCHAR2)
IS
	V_RESULT_COUNT NUMBER;
	V_FORMULA VARCHAR2(50);
	V_AD_VALUE NUMBER(18,4);
	V_SQL VARCHAR2(200);
BEGIN
	-- 获取公式及系数
	SELECT m1.FORMULA,m2.AD_VALUE INTO V_FORMULA, V_AD_VALUE
	FROM  MTG_CHEMLAB_MAINTAIN_NEW m1 LEFT JOIN MTG_CHEMLAB_SHIFT_VALUE_NEW m2 ON m1.TDATE = m2.TDATE 
	WHERE TO_CHAR(m1.TDATE,'yyyy-mm-dd') = IN_TDATE AND  m1.PROCESS = IN_PROCESS;
	
	IF V_AD_VALUE IS NULL OR V_AD_VALUE = '' THEN
		OUT_TITLE := '请维护此取样日期的调整系数！';
		DBMS_OUTPUT.put_line(OUT_TITLE);
		RETURN;
	END IF;
	
	IF V_FORMULA IS NULL OR V_FORMULA = '' THEN
		OUT_TITLE := '此取样日期的取样工序没设公式，请确认！';
		DBMS_OUTPUT.put_line(OUT_TITLE);
		RETURN;
	END IF;
	
	V_FORMULA := REPLACE(V_FORMULA,'AD_VALUE',V_AD_VALUE);
	
	V_SQL := 'UPDATE MTG_CHEMLAB_MAINTAIN_NEW SET ALAYSIS_RESULT = '|| V_FORMULA ||
	' WHERE TO_CHAR(TDATE,''yyyy-mm-dd'') = ' || '''' || IN_TDATE || '''' || ' AND  PROCESS = ' || '''' || IN_PROCESS || '''';
	DBMS_OUTPUT.put_line(V_SQL);
	EXECUTE IMMEDIATE V_SQL;
	
	SELECT ALAYSIS_RESULT INTO OUT_ALARYSIS_RESULT FROM MTG_CHEMLAB_MAINTAIN_NEW 
	WHERE TO_CHAR(TDATE,'yyyy-mm-dd')= IN_TDATE AND  PROCESS = IN_PROCESS;
	IF OUT_ALARYSIS_RESULT IS NOT NULL THEN
		OUT_TITLE := 'OK';
		DBMS_OUTPUT.put_line(OUT_TITLE);
		COMMIT;
	ELSE
		ROLLBACK;
	END IF;
END;

3.对MTG_CHEMLAB_MAINTAIN_NEW表中记录写一个动态日期横向的查询报表，要求格式：

工序     20211108    20211109    20211110    ...   
内层IF                分析结果     分析结果       
SM       分析结果 

答案

CREATE OR REPLACE
PROCEDURE learn_3 
IS
	V_SQL VARCHAR2(2000);
	V_TDATE VARCHAR2(2000);
BEGIN
	SELECT LISTAGG(CONCAT(CONCAT('''',TO_CHAR(TDATE,'yyyy-mm-dd')),''''),',') INTO V_TDATE   FROM MTG_CHEMLAB_MAINTAIN_NEW;
	V_SQL := 'SELECT * FROM (SELECT PROCESS "工序",TO_CHAR(TDATE,''yyyy-mm-dd'') TDATE ,ALAYSIS_RESULT FROM MTG_CHEMLAB_MAINTAIN_NEW) 
	PIVOT(
		SUM(ALAYSIS_RESULT)
		FOR TDATE IN (' || V_TDATE || ')
	)';
	V_SQL := 'CREATE OR REPLACE VIEW RCC_RESULT AS ' || V_SQL;
	EXECUTE IMMEDIATE V_SQL;
END;