DCA认证练习内容记录

1.插入记录

INSERT INTO U_DMSALM.DEPT(DEPTNO, DNAME, LOCATION) VALUES
('101', '总经理办','北三环西路'),
('102', '行政部','北三环西路'),
('103', '开发部','北三环西路'),
('104', '市场部','关山一路')，
('105', '技术支持部','东湖开发区')，
('201', '总经理办','关山一路')，
('202', '行政部','关山一路')，
('304', '技术支持部','体育东路');
COMMIT;

INSERT INTO U_DMSALM.EMP(EMPNO, ENAME, HIREDATE, COMM, DEPTNO) VALUES
(1001, '马学铭','2008-05-30',NULL,101),
(1002, '程擎武','2012-03-27',NULL,101),
(1003, '郑吉群','2010-12-27',NULL,101),
(2001, '李慧军','2010-05-15',NULL,201),
(2002, '常鹏程','2011-08-06',NULL,201);
Commit;

INSERT INTO U_DMSALM.SALARY(EMPNO, BASICAL, BONUS, COMM) VALUES
('1001',18000,12000,null),
('1002',8000,10000,null),
('1003',10000,5000,null),
('2001',9000,1100,'迟到2次'),
('2002',5000,0,null);
Commit;

2.表连接

SELECT D.DNAME,E.ENAME,S.BASICAL + S.BONUS S_SALARY
FROM U_DMSALM.SALARY S JOIN U_DMSALM.EMP E ON S.EMPNO = E.EMPNO
JOIN U_DMSALM.DEPT D ON E.DEPTNO = D.DEPTNO
ORDER BY S_SALARY DESC;

3.子查询+聚合函数

SELECT MAX(S_SALARY),MIN(S_SALARY),
AVG(S_SALARY),SUM(S_SALARY),COUNT(1)
FROM (
    SELECT D.DNAME,E.ENAME,S.BASICAL + S.BONUS S_SALARY,D.DEPTNO DEPTNO
    FROM U_DMSALM.SALARY S JOIN U_DMSALM.EMP E ON S.EMPNO = E.EMPNO
    JOIN U_DMSALM.DEPT D ON E.DEPTNO = D.DEPTNO
    ORDER BY S_SALARY DESC
)
GROUP BY DEPTNO
HAVING AVG(S_SALARY) > 10000
ORDER BY AVG(S_SALARY);

4.物化视图+手动刷新

create materialized view U_DMSALM.V_TJ
/*视图的列名, 格式为(<列名>{, <列名>}),此处为可选*/
(MAX_SALARY,MIN_SALARY,AVG_SALARY,SUM_SALARY,EMPLOYEE_COUNT)
storage(initial 1, next 1, minextents 1, fillfactor 0)
as
/*<查询说明>*/
SELECT MAX(S_SALARY),MIN(S_SALARY),
AVG(S_SALARY),SUM(S_SALARY),COUNT(1)
FROM (
    SELECT D.DNAME,E.ENAME,S.BASICAL + S.BONUS S_SALARY,D.DEPTNO DEPTNO
    FROM U_DMSALM.SALARY S JOIN U_DMSALM.EMP E ON S.EMPNO = E.EMPNO
    JOIN U_DMSALM.DEPT D ON E.DEPTNO = D.DEPTNO
    ORDER BY S_SALARY DESC
)
GROUP BY DEPTNO
HAVING AVG(S_SALARY) > 10000
ORDER BY AVG(S_SALARY);

SELECT * FROM U_DMSALM.V_TJ;

CALL SYS.DBMS_MVIEW.REFRESH('U_DMSALM.V_TJ','C');

5.函数

CREATE OR REPLACE FUNCTION U_DMSALM.F_GETGZ 
(EMPNO_IN IN INT,DEPTNO_IN IN INT,COMM_OUT OUT VARCHAR)
RETURN NUMBER
AS
    /*变量说明部分*/
    SALARY NUMBER;
BEGIN
    /*执行体*/
    SELECT S.BASICAL + BONUS, COMM
    INTO SALARY, COMM_OUT
    FROM U_DMSALM.SALARY S
    WHERE S.EMPNO=EMPNO_IN;
    RETURN SALARY;
EXCEPTION
    WHEN OTHERS THEN
    COMM_OUT:=NULL;
    RETURN -1000;
END;

DECLARE
    RES NUMBER;
    COMM VARCHAR;
BEGIN
    RES:=U_DMSALM.F_GETGZ(201,1,COMM);
    DBMS_OUTPUT.PUT_LINE(RES);
    DBMS_OUTPUT.PUT_LINE(COMM);
END;