利用SQL 构造EMP,DEPT表
CodeSELECT ' SELECT '
||'''' || EMPNO || ''' AS EMPNO ,'
||'''' || ENAME || ''' AS ENAME ,'
||'''' || JOB || ''' AS JOB ,'
||'''' || MGR || ''' AS MGR ,'
||'TO_DATE(' ||'''' || TO_CHAR(HIREDATE,'YYYY-MM-DD') || '''' ||' ,''YYYY-MM-DD'') AS HIREDATE ,'
||'''' || SAL || ''' AS SAL ,'
||'''' || COMM || ''' AS COMM ,'
||'''' || DEPTNO || ''' AS DEPTNO '
|| 'FROM DUAL' || ' UNION ' FROM EMP
SELECT * FROM
(
SELECT '7369' AS EMPNO ,'SMITH' AS ENAME ,'CLERK' AS JOB ,'7902' AS MGR ,TO_DATE('1980-12-17' ,'YYYY-MM-DD') AS HIREDATE
,'800' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7499' AS EMPNO ,'ALLEN' AS ENAME ,'SALESMAN' AS JOB ,'7698' AS MGR ,TO_DATE('1981-02-20' ,'YYYY-MM-DD') AS HIREDATE
,'1600' AS SAL ,'300' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7521' AS EMPNO ,'WARD' AS ENAME ,'SALESMAN' AS JOB ,'7698' AS MGR ,TO_DATE('1981-02-22' ,'YYYY-MM-DD') AS HIREDATE
,'1250' AS SAL ,'500' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7566' AS EMPNO ,'JONES' AS ENAME ,'MANAGER' AS JOB ,'7839' AS MGR ,TO_DATE('1981-04-02' ,'YYYY-MM-DD') AS HIREDATE
,'2975' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7654' AS EMPNO ,'MARTIN' AS ENAME ,'SALESMAN' AS JOB ,'7698' AS MGR ,TO_DATE('1981-09-28' ,'YYYY-MM-DD') AS HIREDATE
,'1250' AS SAL ,'1400' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7698' AS EMPNO ,'BLAKE' AS ENAME ,'MANAGER' AS JOB ,'7839' AS MGR ,TO_DATE('1981-05-01' ,'YYYY-MM-DD') AS HIREDATE
,'2850' AS SAL ,'' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7782' AS EMPNO ,'CLARK' AS ENAME ,'MANAGER' AS JOB ,'7839' AS MGR ,TO_DATE('1981-06-09' ,'YYYY-MM-DD') AS HIREDATE
,'2450' AS SAL ,'' AS COMM ,'10' AS DEPTNO FROM DUAL UNION
SELECT '7788' AS EMPNO ,'SCOTT' AS ENAME ,'ANALYST' AS JOB ,'7566' AS MGR ,TO_DATE('1987-04-19' ,'YYYY-MM-DD') AS HIREDATE
,'3000' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7839' AS EMPNO ,'KING' AS ENAME ,'PRESIDENT' AS JOB ,'' AS MGR ,TO_DATE('1981-11-17' ,'YYYY-MM-DD') AS HIREDATE
,'5000' AS SAL ,'' AS COMM ,'10' AS DEPTNO FROM DUAL UNION
SELECT '7844' AS EMPNO ,'TURNER' AS ENAME ,'SALESMAN' AS JOB ,'7698' AS MGR ,TO_DATE('1981-09-08' ,'YYYY-MM-DD') AS HIREDATE
,'1500' AS SAL ,'0' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7876' AS EMPNO ,'ADAMS' AS ENAME ,'CLERK' AS JOB ,'7788' AS MGR ,TO_DATE('1987-05-23' ,'YYYY-MM-DD') AS HIREDATE
,'1100' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7900' AS EMPNO ,'JAMES' AS ENAME ,'CLERK' AS JOB ,'7698' AS MGR ,TO_DATE('1981-12-03' ,'YYYY-MM-DD') AS HIREDATE
,'950' AS SAL ,'' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7902' AS EMPNO ,'FORD' AS ENAME ,'ANALYST' AS JOB ,'7566' AS MGR ,TO_DATE('1981-12-03' ,'YYYY-MM-DD') AS HIREDATE
,'3000' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7934' AS EMPNO ,'MILLER' AS ENAME ,'CLERK' AS JOB ,'7782' AS MGR ,TO_DATE('1982-01-23' ,'YYYY-MM-DD') AS HIREDATE
,'1300' AS SAL ,'' AS COMM ,'10' AS DEPTNO FROM DUAL
) EMP
SELECT ' SELECT '
||'''' || DEPTNO || ''' AS DEPTNO ,'
||'''' || DNAME || ''' AS DNAME ,'
||'''' || LOC || ''' AS LOC '
|| 'FROM DUAL' || ' UNION ' FROM DEPT
SELECT * FROM
(
SELECT '10' AS DEPTNO ,'ACCOUNTING' AS DNAME ,'NEW YORK' AS LOC FROM DUAL UNION
SELECT '20' AS DEPTNO ,'RESEARCH' AS DNAME ,'DALLAS' AS LOC FROM DUAL UNION
SELECT '30' AS DEPTNO ,'SALES' AS DNAME ,'CHICAGO' AS LOC FROM DUAL UNION
SELECT '40' AS DEPTNO ,'OPERATIONS' AS DNAME ,'BOSTON' AS LOC FROM DUAL
)DEPT
---------------------------
SELECT * FROM
(
SELECT '7369' AS EMPNO ,'SMITH' AS ENAME ,'CLERK' AS JOB ,'7902' AS MGR ,TO_DATE('1980-12-17' ,'YYYY-MM-DD') AS HIREDATE
,'800' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7499' AS EMPNO ,'ALLEN' AS ENAME ,'SALESMAN' AS JOB ,'7698' AS MGR ,TO_DATE('1981-02-20' ,'YYYY-MM-DD') AS HIREDATE
,'1600' AS SAL ,'300' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7521' AS EMPNO ,'WARD' AS ENAME ,'SALESMAN' AS JOB ,'7698' AS MGR ,TO_DATE('1981-02-22' ,'YYYY-MM-DD') AS HIREDATE
,'1250' AS SAL ,'500' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7566' AS EMPNO ,'JONES' AS ENAME ,'MANAGER' AS JOB ,'7839' AS MGR ,TO_DATE('1981-04-02' ,'YYYY-MM-DD') AS HIREDATE
,'2975' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7654' AS EMPNO ,'MARTIN' AS ENAME ,'SALESMAN' AS JOB ,'7698' AS MGR ,TO_DATE('1981-09-28' ,'YYYY-MM-DD') AS HIREDATE
,'1250' AS SAL ,'1400' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7698' AS EMPNO ,'BLAKE' AS ENAME ,'MANAGER' AS JOB ,'7839' AS MGR ,TO_DATE('1981-05-01' ,'YYYY-MM-DD') AS HIREDATE
,'2850' AS SAL ,'' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7782' AS EMPNO ,'CLARK' AS ENAME ,'MANAGER' AS JOB ,'7839' AS MGR ,TO_DATE('1981-06-09' ,'YYYY-MM-DD') AS HIREDATE
,'2450' AS SAL ,'' AS COMM ,'10' AS DEPTNO FROM DUAL UNION
SELECT '7788' AS EMPNO ,'SCOTT' AS ENAME ,'ANALYST' AS JOB ,'7566' AS MGR ,TO_DATE('1987-04-19' ,'YYYY-MM-DD') AS HIREDATE
,'3000' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7839' AS EMPNO ,'KING' AS ENAME ,'PRESIDENT' AS JOB ,'' AS MGR ,TO_DATE('1981-11-17' ,'YYYY-MM-DD') AS HIREDATE
,'5000' AS SAL ,'' AS COMM ,'10' AS DEPTNO FROM DUAL UNION
SELECT '7844' AS EMPNO ,'TURNER' AS ENAME ,'SALESMAN' AS JOB ,'7698' AS MGR ,TO_DATE('1981-09-08' ,'YYYY-MM-DD') AS HIREDATE
,'1500' AS SAL ,'0' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7876' AS EMPNO ,'ADAMS' AS ENAME ,'CLERK' AS JOB ,'7788' AS MGR ,TO_DATE('1987-05-23' ,'YYYY-MM-DD') AS HIREDATE
,'1100' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7900' AS EMPNO ,'JAMES' AS ENAME ,'CLERK' AS JOB ,'7698' AS MGR ,TO_DATE('1981-12-03' ,'YYYY-MM-DD') AS HIREDATE
,'950' AS SAL ,'' AS COMM ,'30' AS DEPTNO FROM DUAL UNION
SELECT '7902' AS EMPNO ,'FORD' AS ENAME ,'ANALYST' AS JOB ,'7566' AS MGR ,TO_DATE('1981-12-03' ,'YYYY-MM-DD') AS HIREDATE
,'3000' AS SAL ,'' AS COMM ,'20' AS DEPTNO FROM DUAL UNION
SELECT '7934' AS EMPNO ,'MILLER' AS ENAME ,'CLERK' AS JOB ,'7782' AS MGR ,TO_DATE('1982-01-23' ,'YYYY-MM-DD') AS HIREDATE
,'1300' AS SAL ,'' AS COMM ,'10' AS DEPTNO FROM DUAL
) EMP,
(
SELECT '10' AS DEPTNO ,'ACCOUNTING' AS DNAME ,'NEW YORK' AS LOC FROM DUAL UNION
SELECT '20' AS DEPTNO ,'RESEARCH' AS DNAME ,'DALLAS' AS LOC FROM DUAL UNION
SELECT '30' AS DEPTNO ,'SALES' AS DNAME ,'CHICAGO' AS LOC FROM DUAL UNION
SELECT '40' AS DEPTNO ,'OPERATIONS' AS DNAME ,'BOSTON' AS LOC FROM DUAL
)DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
---------------------------------------
posted on 2009-05-02 18:58 dolphin_bobo 阅读(265) 评论(0) 编辑 收藏 举报
