LE test1
DROP (pk) CASCADE CONSTRAINTS;
ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;
*/
--视图使用WITH CHECK OPTION子句
CREATE OR REPLACE VIEW empvu20
AS
SELECT * FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck --引用约束empvu20_ck
--视图拒绝DML操作
WITH READ ONLY
--排序TOP-N分析：伪列ROWNUM关键字
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;
--序列
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10 --步长
START WITH 120 --开始位
MAXVALUE 9999 --结束位
NOCACHE --
NOCYCLE; --达到结束位不重新开始循环
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
--序列中的伪列,
NEXTVAL
CURRVAL
INSERT INTO departments(department_id,department_name,location_id)
VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);
SELECT dept_deptid_seq.CURRVAL
FROM dual;
--索引
CREATE INDEX emp_last_name_idx
ON employees(last_name);
--同义词
CREATE SYNONYM emp
FOR employees;
CREATE PUBLIC SYNONYM dept
FOR departments
|