Oracle DML语言学习

orcale中将查询结果当作表来用的查询叫做内联视图。标量子查询的结果无法再分（一行一列一个值）。标量子查询可以写在select，from，where或者values后。 case case 表达式 when 值1 zhen 返回值1 when 值2 zhen 返回值2 when 值3 zhen 返回值3 ...... else 返回值x end case when 条件1 zhen 返回值1 when 条件2 zhen 返回值2 when 条件3 zhen 返回值3 ...... else 返回值x end

相关子查询 GET--->EXECUTE--->USE 👆 👇 --------------------------- 性能差例子： select * from employees e where salary > (select avg(salary) from employees e where department_id = e.department_id)

改进：

    select *
    from employees e, (select department_id, avg(salary) saay from employees group by department_id) b
    where e.department_id = b.department_id
    and e.salary > b.saay    

优化2：
    select employee_id, last_name, job_id, department_id
    from employees outer
    where exists(select 'X'
                from employees
                where manager_id = outer.employee_id)

with子句 with 名1 as (查询语句) //获得一张名为【名1】的临时表

∪，∩，差集条件：列数相同，列的类型相融

∪：
    union/union all
    ∪去重+排序，全并，不去重不排序
∩：
    intersect
差集：
    minus

SQL: QL 查询语言 DML 数据操作语言 DDL 数据定义语言 DCL 数据控制语言 TC 事务控制

事务：一系列DML 一条DDL 一条DCL

查询数据字典： select table_name from user_tables; select distinct object_type from user_objects; select * from user_catalog; 三大类： user开头可以看自己拥有的//普通用户 all开头的可以看自己拥有的和有权限查看的//普通用户 dba开头的//dba用户

数据库类型： VARCHAR2(size) 变长字符 CHAR(size) 定长字符 NUMBER(p,s) 数字类型 DATE 日期类型 --------------------------------------------分割线-------------------------------------- LONG 长字符型 CLOB 字符文件 RAW and LONG RAW 从其他数据库导入时，类型不匹配，用此类型存储 BLOB 二进制文件（二进制//音乐//视频） BFILE 二进制文件类型 ROWID 行id --------------------------------------------分割线-------------------------------------- TIMESTAMP(n) WITH TIME ZONE 时间戳 INRERVAL YEAR TO MONTH 时间差（精确到月） //时间差（精确到天）

DDL 数据定义语言创建表 CREATE TABLE 表名 AS 查询; CREATE TABLE 表名 (列名类型 [约束]，列名类型);

修改表结构 ALTER TABLE 表名 ADD(列名类型); MODIFY(列名类型)；//修改类型 DROP(列名);

重命名表： RENAME 表名 TO 新表名;

截断表 TRUNCATE TABLE 表名;

约束//强制性的规则五大约束唯一UNIQUE CONSTRAINT 表名_列名_约束类型 UNIQUE(列名) 主键PRIMARY KEY
列名列类型 CONSTRAINT 表名_列名_约束类型 PRIMARY KEY//列级 CONSTRAINT 表名_列名_约束类型 PRIMARY KEY(列名)//表级外键FOREIGN KEY CONSTRAINT 表名_列名_约束类型 FOREIGN KEY (列名1) REFERENCES 表2(列2) 非空NOT NULL//只有列级列名列类型 not null 检查CHECK CONSTRAINT 表名_列名_约束类型 CHECK(条件)

添加约束ALTER TABLE table ADD CONSTRAINT 约束名 约束(列名)
删除约束ALTER TABLE table DROP CONSTRAINT 约束名
删除主键约束ALTER TABLE table DROP PRIMARY KEY CASCADE;
临时失效ALTER TABLE table DISABLE CONSTRAINT    约束名
生效ALTER TABLE table ENABLE CONSTRAINT 约束名

视图view：简单view：来源：一张表包含函数？N 分组信息？N 增删改？Y 复杂view 来源：一张或多张表包含函数？Y 分组信息？Y 增删改？Not always

创建：
    CREATE VIEW view AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]

同义词： create [public] synonym 同义词 for 模式名.表名(模式名一般和用户名相同)

DCL:数据控制语言创建用户： CREATE USER user IDENTIFIED BY passowrd; eg: CREATE USER scott IDENTIFIED BY tiger;

权限：
    给予权限：GRANT 权限 TO user
    权限包括：
        create session
        create table
        create sequence
        create view
        create procedure
    
    角色（权限的集合）：
        创建角色：create role 角色名
        给予角色权限：grant 权限 to 角色
        给予用户角色：grant 角色 to 权限

    对象权限：
        GRANT 权限
        ON 对象
        TO 用户, [角色] [WITH GRANT OPTION(可以给别人权限)];

orcale独有 PL/SQL语言：程序块[ DECLARE - Optional （可选的）//声明 BEGIN - Mandatory（必须的） //执行 EXCEPTION - Optional //异常处理 END; - Mandatory //结束 ] eg: DECLARE v_variable VARCHAR2(2); BEGIN select colmun_name INTO v_variable FORM table_name; EXCEPTION WHEN exception_name THEN... END;

    存储过程：
        PROCEDURE name
        IS
        BEGIN
        END;

    自定义函数：
        FUNCTION name
        RETURN datatype
        IS
        ......
        RETURN value
    
        END;
    
    变量类型：
        VARCHAR2
        NUMBER
        DATA
        CHAR
        LONG
        LONG RAW
    --------------------------------------------------------
        BOOLEAN
        BINARY_INTEGER
        PLS_INTEGER
        PL/SQL
            -Scalar标量变量
            -Composite复合变量
            -Reference引用变量
            -LOB（large objects）大对象类型
        Non-PL/SQL variables
            -Bind and host variables绑定变量和主机变量
        
    变量声明语法：
        identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULE expr];
        不能与所使用表所包含的列名相同
        eg:
            Declare
                v_hiredate    DATE;
                v_deptno    NUMBER(2) NOT NULL := 10;
                v_location    VARCHAR2(13) := 'Atlanta';
                c_comm        CONSTANT NUMBER := 1400;
    
    %TYPE
        v_ename        emp.ename%TYPE;
        v_banlance    NUMBER(7, 2);
        v_min_banlance    v_banlance%TYPE := 10;
    
    复合类型：
        TABLES
        RECORDS
    LOB
        CLOB(text)
        BLOB(photo)
        BFILE(movie)
        NCLOB
    Non-PL/SQL
        var 变量名
        引用时变量名前加冒号
    
    PL/SQL Function
        CHR()//将ASCII码转化为字符
        LOWER()小写
        TO_CHAR
        TO_DATE
        TO_NUMBER
    IF语句
        IF THEN END IF
        IF THEN ELSE END IF
        IF THEN ELSIF END IF
    
    循环：
        Basic Loop:
            LOOP
                .....
                EXIT [WHEN ...];
            END LOOP;
        
        FOR counter in [REVERSE] lower_bound..upper_bound LOOP
            xxx;
        END LOOP;
        eg:
            declare
                v_sum binary_integer:=0;
            begin
                for i in 1..100 loop
                    v_sum := v_sum + i;
                end loop;
                dbms_output.put_line(v_sum);
            end;
        

        WHILE 条件 LOOP
        xxxx;
        END LOOP;
        eg:
            declare
                v_sum binary_integer:=0;
                v_i binary_integer:=1;
            begin
                while v_i <= 100 loop
                    v_sum := v_sum + v_i;
                    v_i := v_i + 1;
                end loop;
                dbms_output.put_line(v_sum);
            end;
            /

        嵌套循环与标签
        BEGIN
            <<Outer_loop>>
            LOOP
                v_count := v_counter + 1;
                EXIT WHEN v_counter > 10;
                <<Inner_loop>>
                LOOP
                    ...
                    EXIT Outer_loop WHEN total_done = 'YES';
                    --跳出外层循环
                    EXIT WHEN inner_done = 'YES';
                    --跳出内层循环
                    ...
                END LOOP Inner_loop;
                ...
            END LOOP Outer_loop;
        END;

    SQL%ROWCOUNT//上次执行的SQL语句影响到多少行
    SQL%FOUND//上次执行的SQL语句是否影响到行
    SQL%NOTFOUND
    SQL%ISOPEN
    
    显示数据：
        dbms_output.put_line()

    复合类型：类似于结构体record_type
        TYPE type_name IS RECORD
            (分量名 分量类型,
            ......
            );
        identidier type_name;
        
        %ROWTYPE
            dept_record dept%ROWTYPE;

    table类型
        TYPE xx IS TABLE OF 普通标量
            INDEX BY BINARY_INTEGER

        方法：
            exists 是否存在
            count 计数
            first and last 第一个和最后一个
            prior 上一个
            next 下一个
            extend 扩展
            trim 删除前导和结尾的空值
            delete 删除
        
    显示游标
        1.declare
        2.open
        3.fetch（提取）
        4.empty？close：3

    syntax：
        CURSOR cursor_name IS
            select_statement;9

        OPEN cursor_name;

        FETCH cursor_name INTO [type|record_type_name]

        CLOSE cursor_name;

    cursor_name%ISOPEN
    cursor_name%NOTFOUND
    cursor_name%FOUND//是否剩余行
    cursor_name%ROWCOUNT//已经提取出多少行

    用for循环处理游标
        FOR record_name IN cursor_name LOOP
            statement1;
            statement2;
            ...
        END LOOP;

    带参数的游标
        CURSOR 游标名 (p_deptno NUMBER, p_job VARCHAR2) IS
            SELECT empno, ename
            FROM emp
            WHERE deptno = p_deptno
            AND job = p_job;
        BEGIN
            OPEN emp_cursor(10, 'CLERK');

    FOR UPDATE//人为加锁
        SELECT ...
        FORM ...
        FOR UPDATE [OF column_reference][NOWAIT];
        eg:
            DECLARE
                CURSOR emp_cursor IS
                    SELECT empno,ename,sal
                    FROM emp
                    WHERE deptno = 30
                    FOR UPDATE OF sal NOWAIT(报错);

    更新表中数据->cursor
    WHERE CURRENT OF cursor;


异常处理
    WHEN exception1 [OR exception2] THEN...
    [WHEN exception2 [OR exception3] THEN...]
    [WHEN ORTHERS THEN...]

    NO_DATA_FOUND
    TOO_MANY_ROWS
    INVALLD_CURCOR//无效游标异常
    ZERO_DIVIDE//除数为零
    DUP_VAL_ON_INDEX//出现重复值

    DECLARE
        e_emps_ramaining EXCEPTION;
        PRAGMA EXCEPTION_INIT(
            e_emps_ramaining, -2292);
        v_dept dept.deptno%TYPE := &p_deptno;
    BEGIN
        DELETE FROM dept
        WHERE deptno = v_deptno;
        COMMIT;
    EXCEPTION
        WHEN e_emps_remaining THEN
        DBMS_OUTPUT.PUT_LINE(' Cannot delete dept ' || TO_CHAR(v_deptno) || '.  Employees exist. ');
    END;

    SQLCODE
    SQLERRM//error message

    WHEN OTHERS THEN
        ROLLBACK;
        v_error_code := SQLCODE;
        v_error_errm := SQLERRM;
        INSERT INTO errors
        VALUES(v_error_code, v_error_errm);
    
    WHEN SQL%ROWCOUNT = 0 THEN
    RAISE e

块存储过程子程序（带名字的块） DECARE - >

IS | AS

    IN //向存储过程内传参
    OUT //存储过程向外传参
    IN OUT //

    创建存储过程
        CREATE OR REPLACE PROCEDURE raise_salary
            (p_id IN employees.employee_id%TYPE)
        IS
        BEGIN
            UPDATE employees
            SET salary = salary*1.10
            WHERE employee_id = p_id;
        END raise_salary;
        /

函数 create or replace function name (参数) return 类型

package CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS xxx xxx END; package_name;

CREATE OR REPLACE PACKAGE BODY comm_package
IS
    FUNCTION validate_comm (p_comm IN NUMBER)
RETURN BOOLEAN
IS
    v_max_comm NUMBER;
BEGIN
    SELECT MAX(commission_pct)
    INTO v_max_comm
    FROM employees;
    IF p_comm > v_max_comm THEN RETURN(FALSE);
    ELSE RETURN(TRUE);
    END IF;
END validate_comm;