【oracle 学习笔记 11】 视图列，check约束，权限角色

SQL> set linesize 120 SQL> set pagesize 120 SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 14 rows selected. SQL> ed Wrote file afiedt.buf 1 CREATE TABLE t( 2 id CHARACTER(4), 3 sex CHARACTER(1) check (sex IN ('M','F')) 4* ) SQL> / CREATE TABLE t( * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> drop table t 2 / Table dropped. SQL> ed Wrote file afiedt.buf 1 CREATE TABLE t( 2 id CHARACTER(4), 3 sex CHARACTER(1) check (sex IN ('M','F')) 4* ) 5 / Table created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO t 3* VALUES('0001','N') SQL> / INSERT * ERROR at line 1: ORA-02290: check constraint (YMC.SYS_C005330) violated SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO t 3* VALUES('0001','M') SQL> / 1 row created. SQL> ed Wrote file afiedt.buf 1 SELECT * 2* FROM dupEmp SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 14 rows selected. SQL> ed Wrote file afiedt.buf 1 SELECT * 2 FROM dupEmp 3* GROUP BY job SQL> / SELECT * * ERROR at line 1: ORA-00979: not a GROUP BY expression SQL> ed Wrote file afiedt.buf 1 SELECT job,COUNT(*) 2 FROM dupEmp 3* GROUP BY job SQL> / JOB COUNT(*) --------- ---------- ANALYST 2 CLERK 4 MANAGER 3 PRESIDENT 1 SALESMAN 4 SQL> ed Wrote file afiedt.buf 1 CREATE VIEW viewJob 2 AS 3 SELECT job,COUNT(*) 4 FROM dupEmp 5* GROUP BY job SQL> / SELECT job,COUNT(*) * ERROR at line 3: ORA-00998: must name this expression with a column alias SQL> ed Wrote file afiedt.buf 1 CREATE VIEW viewJob(job,COUNT) 2 AS 3 SELECT job,COUNT(*) 4 FROM dupEmp 5* GROUP BY job SQL> / View created. SQL> ed Wrote file afiedt.buf 1* SELECT * FROM viewJob SQL> / JOB COUNT --------- ---------- ANALYST 2 CLERK 4 MANAGER 3 PRESIDENT 1 SALESMAN 4 SQL> ed Wrote file afiedt.buf 1* SELECT * FROM dupEmp SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 14 rows selected. SQL> ed Wrote file afiedt.buf 1* SELECT table_name FROM user_tables SQL> / TABLE_NAME ------------------------------ T BIN$G6FBkUHWQV+rp99sbZBBEw==$0 TEMPTABLE EMPEMP TESTCHAR TESTSEQUENCE DUPEMP TESTCHARBYTE TESTCHARCHAR BIN$OlXF71PiTTOrrJGAZcs29Q==$0 TESTPRI TESTINDEX T2 TEST TESTDATE T1 IMPEXT DUPTESTCASE TESTCASE TESTEXT TB 21 rows selected. SQL> conn sys as sysdba Connected. SQL> ed Wrote file afiedt.buf 1 CREATE TABLE ymc.dupDept 2 AS 3* SELECT * FROM scott.dept SQL> / Table created. SQL> conn ymc Connected. SQL> ed Wrote file afiedt.buf 1* SELECT * FROM dupDept SQL> . SQL> / DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> ed Wrote file afiedt.buf 1* SELECT * FROM dupDept SQL> ed Wrote file afiedt.buf 1 CREATE VIEW viewEmpDept 2 AS 3 SELECT * 4 FROM dupEmp, dupDept 5* WHERE dupEmp.deptno = dupDept.deptno SQL> / SELECT * * ERROR at line 3: ORA-00957: duplicate column name SQL> ed Wrote file afiedt.buf 1 SELECT * 2 FROM dupEmp, dupDept 3* WHERE dupEmp.deptno = dupDept.deptno SQL> // EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- LOC ------------- 7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK 14 rows selected. SQL> ed Wrote file afiedt.buf 1 CREATE VIEW viewEmpDept 2 AS 3 SELECT * 4 FROM dupEmp, dupDept 5* WHERE dupEmp.deptno = dupDept.deptno 6 / SELECT * * ERROR at line 3: ORA-00957: duplicate column name SQL> ed Wrote file afiedt.buf 1 CREATE VIEW viewEmpDept(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DEPTNO,DNAME) 2 AS 3 SELECT * 4 FROM dupEmp, dupDept 5* WHERE dupEmp.deptno = dupDept.deptno SQL> / CREATE VIEW viewEmpDept(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DEPTNO,DNAME) * ERROR at line 1: ORA-00957: duplicate column name SQL> ed Wrote file afiedt.buf 1 CREATE VIEW viewEmpDept(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DEPTNO,DNAME,LOC) 2 AS 3 SELECT * 4 FROM dupEmp, dupDept 5* WHERE dupEmp.deptno = dupDept.deptno SQL> / CREATE VIEW viewEmpDept(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DEPTNO,DNAME,LOC) * ERROR at line 1: ORA-00957: duplicate column name SQL> ed Wrote file afiedt.buf 1 CREATE VIEW viewEmpDept(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO1,DEPTNO2,DNAME,LOC) 2 AS 3 SELECT * 4 FROM dupEmp, dupDept 5* WHERE dupEmp.deptno = dupDept.deptno SQL> / View created. SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE VIEW viewEmpDept 2 AS 3 SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DNAME,LOC 4 FROM dupEmp, dupDept 5* WHERE dupEmp.deptno = dupDept.deptno SQL> / SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DNAME,LOC * ERROR at line 3: ORA-00918: column ambiguously defined SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE VIEW viewEmpDept 2 AS 3 SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,dupEmp.DEPTNO,DNAME,LOC 4 FROM dupEmp, dupDept 5* WHERE dupEmp.deptno = dupDept.deptno SQL> / View created. SQL> ed Wrote file afiedt.buf 1* select * from user_role_privs SQL> SQL> / USERNAME GRANTED_ROLE ADM DEF OS_ ------------------------------ ------------------------------ --- --- --- YMC RESOURCE NO YES NO SQL> ed Wrote file afiedt.buf 1 SELECT username,privilege,admin_option 2* FROM user_sys_privs 3 / USERNAME PRIVILEGE ADM ------------------------------ ---------------------------------------- --- YMC CREATE VIEW NO YMC CREATE SESSION NO YMC UNLIMITED TABLESPACE NO SQL> spool off

三种情况下 要指明视图的列名：
1 某个列是聚合函数或表达式

2 多表连接时选出了几个同名的列作为视图的字段

3 人为的取更方便的

user_sys_privs 中不包括该用户角色中的系统权限