oracle约束、视图、索引、序列、PL/SQL、存储过程

1、约束
主键约束
外键约束
非空约束
唯一约束
检查约束

ALTER TABLE TBL表名 
    ADD CONSTRAINT 约束的标识名 具体的约束;

ALTER TABLE S
    ADD CONSTRAINT CHK_S_001 
    CHECK ( SSEX IN ('男','女') );

INSERT INTO S VALUES( 1008 , '任盈盈' , '女' );

SC(SID) ---> S(SID)
外键：不能引用不存在的实体（主键）
ALTER TABLE SC 
    ADD CONSTRAINT FK_SC_001 
    FOREIGN KEY ( SID ) 
    REFERENCES S(SID);

ALTER TABLE S 
ADD CONSTRAINT PK_S1_001 
PRIMARY KEY ( SID );

ALTER TABLE S 
ADD CONSTRAINT UNQ_S_001
UNIQUE(SNAME);

ALTER TABLE S DROP CONSTRAINT UNQ_S_001

2、视图
为了提供开发效率，减少重复劳动，将复杂的SQL语句
封装起来。
视图本身不存储数据，他的数据都来自于查询语句对应的
基表，所以，我们可以把视图看成是虚表

CREATE OR REPLACE VIEW 视图名(别名)
AS
    复杂的SQL语句
    
例子：    
CREATE OR REPLACE VIEW V_EMP(员工编号,员工姓名,工种)
AS
     SELECT EMPNO , ENAME ,JOB FROM EMP 
     
使用视图就和使用表一样
SELECT * FROM V_EMP;

UPDATE V_EMP SET 工种 = '秘书' 
WHERE 员工编号 = 1001;

SELECT * FROM EMP;

当你的基表的表结构发生变化的时候，需要重新
运行或者编译视图
ALTER VIEW 视图名 COMPILE

3、索引
索引就好像一本书的目录，主要是为了提高
查询效率，快速定位数据，减少磁盘访问次数。

语法
CREATE INDEX S_ENAME_INDEX 
ON EMP(ENAME);

DROP INDEX S_ENAME_INDEX 

4、什么时候用索引（非常重要）

字段取值分布范围很广

字段中包含大量空值

字段经常出现在where子句或连接条件中

表经常被访问、数据量很大，且通常每次访问的数据量小于记录总量的2%~4%

5、不适合用索引的情况

表很小

字段不经常出现在where子句中

每次访问的数据量大于记录总数的2%~4%

表经常更新

被索引的字段作为表达式的一部分被引用而不是被直接引用（使用频繁的话可考虑创建函数索引）

6、
SELECT * FROM EMP 
WHERE lower(ENAME) = 'smith'

CREATE INDEX S_ENAME_INDEX 
ON EMP(lower(ENAME));

SELECT * FROM EMP 
WHERE lower(ENAME) = 'smith'

SELECT * FROM S

CREATE UNIQUE INDEX S_UINQ_INDEX
    ON S(SNAME);

  7、序列 有两个属性（NEXTVAL , CURRVAL）
CREATE sequence seq_S ;
SELECT seq_S.NEXTVAL FROM DUAL; --获取下一个序列值
SELECT seq_S.CURRVAL FROM DUAL; --获取当前序列值

序列语法
CREATE sequence 序列名
MAXVALUE 999999 CACHE 20;

一般是在新增的时候使用
INSERT INTO S VALUES( seq_S.NEXTVAL ,     '令狐冲' , '男'
)

SELECT * FROM S;

8、范式：至少要符合前三范式
第一范式：属性不可分割
第二范式：不存在局部依赖
第三范式：不存在传递依赖

9、PL/SQL

基本结构
[DECLARE]
    --声明变量
BEGIN     
END;

PL/SQL专属数据类型
        %TYPE（字段，属性类型）   %ROWTYPE（行类型）

声明一个变量SAL， 赋值800 , 并打印出来                                           赋值方式       : = 
DECLARE
    SAL NUMBER(8,2);
BEGIN
    SAL := 800;  

    DBMS_OUTPUT.PUT_LINE(SAL);
END;

开启服务端输出命令
SET SERVEROUTPUT ON;

DECLARE
    D_SAL NUMBER(8,2);
BEGIN
    --第两种赋值方式 INTO
    SELECT SAL INTO D_SAL FROM EMP WHERE EMPNO = 7369;
    DBMS_OUTPUT.PUT_LINE(D_SAL);
END;

DECLARE
    D_SAL NUMBER(8,2);
    D_JOB VARCHAR(30);
BEGIN
    --第两种赋值方式 INTO
    SELECT SAL,JOB INTO D_SAL ,D_JOB 
        FROM EMP WHERE EMPNO = 7369;
    DBMS_OUTPUT.PUT_LINE(D_SAL || D_JOB);
END;

10、

%TYPE（字段，属性类型）
DECLARE
    D_SAL EMP.SAL%TYPE;
    D_JOB EMP.JOB%TYPE;
BEGIN
    --第两种赋值方式 INTO
    SELECT SAL,JOB INTO D_SAL ,D_JOB 
        FROM EMP WHERE EMPNO = 7369;
    DBMS_OUTPUT.PUT_LINE(D_SAL || D_JOB);
END;

%ROWTYPE（行类型）
DECLARE
    R_EMP EMP%ROWTYPE;
    C_COUNT CONSTANT NUMBER(4) := 100;
BEGIN
    --第两种赋值方式 INTO
    SELECT * INTO R_EMP 
        FROM EMP WHERE EMPNO = 7369;
    DBMS_OUTPUT.PUT_LINE( R_EMP.JOB );
END;

11、

查询员工7369的工资，判断工资等级
1-1000之间  1001-2500 中等  以上是高工资
DECLARE
    D_SAL EMP.SAL%TYPE := 0 ;
BEGIN
    --第两种赋值方式 INTO
    SELECT SAL INTO D_SAL 
        FROM EMP WHERE EMPNO = 7369;
        
    IF D_SAL >= 0 AND D_SAL <= 1000 THEN
        DBMS_OUTPUT.PUT_LINE( '低工资' );
    ELSIF D_SAL > 1000 AND D_SAL <= 2500 THEN
        DBMS_OUTPUT.PUT_LINE( '中等工资' );
    ELSE
        DBMS_OUTPUT.PUT_LINE( '高工资' );
    END IF;
END;

--打印五个Hello！
DECLARE
    COUNTER NUMBER(1) := 0;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE( 'Hello' );
        COUNTER := COUNTER + 1;
        IF COUNTER >= 5 THEN
            EXIT;
        END IF;
    END LOOP;
END;

DECLARE
    COUNTER NUMBER(1) := 0;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE( 'Hello' );
        COUNTER := COUNTER + 1;
        EXIT WHEN COUNTER >= 5;
    END LOOP;
END;

FOR  IN   LOOP
END LOOP

BEGIN
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE( 'Hello' || i );
    END LOOP;
END;

WHILE 循环条件 LOOP

END LOOP

写一个带异常的语句
DECLARE
    D_SAL EMP.SAL%TYPE := 0 ;
BEGIN
    --第两种赋值方式 INTO
    SELECT SAL INTO D_SAL 
        FROM EMP ;
        
    IF D_SAL >= 0 AND D_SAL <= 1000 THEN
        DBMS_OUTPUT.PUT_LINE( '低工资' );
    ELSIF D_SAL > 1000 AND D_SAL <= 2500 THEN
        DBMS_OUTPUT.PUT_LINE( '中等工资' );
    ELSE
        DBMS_OUTPUT.PUT_LINE( '高工资' );
    END IF;
    
    EXCEPTION
        WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE( '请指定员工编号！！！' );
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE( '有异常了！！！' );
END;

12、

存储过程
封装PL/SQL
没有程序级别的返回值

语法
CREATE OR REPLACE PROCEDURE 存储过程名字
( 参数列表...... )
IS
    声明变量
BEGIN
    PL/SQL
END;

--更新EMP （根据员工编号修改工种）
CREATE OR REPLACE PROCEDURE USP_UPDATE_EMP 
( i_job IN VARCHAR2 , i_empno IN NUMBER )
IS
        v_job EMP.JOB%TYPE;
BEGIN
    v_job := i_job;
    
    UPDATE EMP SET JOB = v_job 
    WHERE EMPNO = i_empno;    
    
    commit;
END;

调用存储过程
EXECUTE USP_UPDATE_EMP( '内勤' , 1001 );

BEGIN
   USP_UPDATE_EMP( '保洁' , 1001 );
END;

SELECT * FROM EMP WHERE EMPNO = 1001;

--查询部门为10的员工的平均工资
--存储过程没有提供返回值，所以，只能输出型参数
CREATE OR REPLACE PROCEDURE USP_GETSAL
    ( i_deptno IN NUMBER , o_sal OUT NUMBER )
IS
BEGIN
    SELECT AVG( SAL ) INTO o_sal FROM EMP
    WHERE DEPTNO = i_deptno;
END;

DECLARE
    o_sal NUMBER(8,2) := 0.0;
BEGIN
    USP_GETSAL( 10 ,o_sal   );
    DBMS_OUTPUT.PUT_LINE( o_sal);
END;

CREATE OR REPLACE PROCEDURE USP_GETSAL2
    ( i_deptno IN NUMBER , io_sal IN OUT NUMBER )
IS
    v_sal EMP.SAL%TYPE := 0.0;
BEGIN
    v_sal := io_sal;
    
    DBMS_OUTPUT.PUT_LINE( v_sal);
    
    SELECT AVG( SAL ) INTO io_sal FROM EMP
    WHERE DEPTNO = i_deptno;
END;

DECLARE
    o_sal NUMBER(8,2) := 150;
BEGIN
    USP_GETSAL2( 10 ,o_sal   );
    DBMS_OUTPUT.PUT_LINE( o_sal);
END;

--删除存储过程
DROP PROCEDURE 存储过程名字