【初識Oracle】⑤資料庫視圖以及序列

---序列

create sequence SEQ_EMPLOYEE
minvalue 1
maxvalue 999999999
start with 2
increment by 2
cache 20;

SELECT SEQ_EMPLOYEE.Nextval FROM dual;
--下一個字段
SELECT SEQ_EMPLOYEE.CURRVAL FROM dual;
--目前字段

INSERT INTO employee VALUES(SEQ_EMPLOYEE.Nextval,'張三',25,2300,'',SYSDATE,10);
COMMIT;
SELECT * FROM employee;

DROP SEQUENCE SEQ_EMPLOYEE;

--視圖
--簡單視圖
CREATE VIEW vw_emp  AS
       SELECT empno,ename,hiredate FROM emp;

CREATE VIEW vw_emp1 AS
       SELECT ename,hiredate,sal,comm FROM emp;

SELECT * FROM vw_emp;
SELECT * FROM vw_emp1;

--複雜視圖
CREATE VIEW vm_emp3(deptno,ecount,avgsal) AS
       SELECT deptno,COUNT(1),round(AVG(sal),2) FROM emp GROUP BY deptno;
       
RENAME vm_emp3 TO vw_emp3;
SELECT * FROM vw_emp3;

--連接配接視圖
CREATE VIEW vw_emp4 AS
       SELECT e.empno,e.ename,d.dname,d.loc
       FROM emp e,dept d 
       WHERE e.deptno=d.deptno;

SELECT ename,dname FROM vw_emp4;

--隻讀視圖
CREATE VIEW vw_emp5 AS
       SELECT * FROM emp WITH READ ONLY;

SELECT * FROM vw_emp5;
CREATE VIEW vw_emp6 AS
       SELECT * FROM emp ;

SELECT * FROM vw_emp6;
UPDATE vw_emp6 SET COMM=20 WHERE ename='SMITH';
INSERT INTO vw_emp6(EMPNO,ENAME,JOB,MGR,hiredate,sal,comm,DEPTNO)
       VALUES(2530,'DA','DA',5632,SYSDATE,5000,1000,10)