所以我有一个应用程序,检查SQL SELECT查询对一些定义的答案。
现在它使用PHP中的一些FOR循环来比较两个查询的结果集。它从定义的查询中搜索每一列,并尝试在输入的查询中找到它;
是否有办法在oracle级别检查这个?
示例:假设我们有以下表格
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 13-JUL-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 13-JUL-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
定义的答案:
SELECT ENAME, JOB FROM EMP WHERE DEPTNO=20;
输入答案:
SELECT * FROM EMP WHERE DEPTNO=20;
这两个查询在选择的行方面或多或少是等同的。
我怎样才能验证这个事实呢?
- (这两个查询是等效的-在行的方面)
- (输入查询可能有额外的列)
我试过了:
- 使用UNION/MINUS操作符。(问题是,当查询有不同数量的列减去不工作)。
有什么建议吗?
这在某种程度上取决于您希望应用程序提供哪些信息,以及您希望动态提取哪些信息。这也取决于你打算如何向Oracle提出这个问题。
如果你想构建一个包含引用查询和输入查询并告诉你它们是否对应的查询,如果你愿意依赖于提供显式列列表的应用程序,如果你想检查结果集列标签,如果你不关心列或行顺序,那么你可以这样做:
WITH
expected AS (
-- select all the expected columns, plus a count
SELECT ENAME, JOB, COUNT(*) AS count FROM (
-- the standard query to test against
SELECT ENAME, JOB FROM EMP WHERE DEPTNO=20
) reference
-- group by all the columns of the result
GROUP BY ENAME, JOB
),
observed AS (
-- choose only the expected columns (Oracle will barf if any are not present),
-- plus a count
SELECT ENAME, JOB, COUNT(*) AS count FROM (
-- the query whose results are to be tested
SELECT * FROM EMP WHERE DEPTNO=20
) user_specified
-- group by all the columns of the reference result
GROUP BY ENAME, JOB
)
SELECT COUNT(*) as differences
FROM (
(
SELECT * FROM expected
MINUS
SELECT * FROM observed
)
UNION ALL
(
SELECT * FROM observed
MINUS
SELECT * FROM expected
)
) differing_rows
这基本上是样板文件。在一个地方插入整个引用查询,在另一个地方插入整个输入查询,并在四个不同的地方插入(相同的)预期列列表。如果查询结果与感兴趣的列完全匹配(忽略列和行顺序),则查询结果为单行,单列包含值0,否则查询结果为大于0的值。如果任何一个组件查询返回重复行,则总体结果将测试两者返回的每个重复行的副本数量是否相同。