学习笔记day66-----oracle-qlsql中对oracle数据库的操作的模块化

PLSQL
1、数据库访问相关的技术
    1)  plsql       procedural  过程化sql
    2)  proc/c++    在c/c++语言中嵌入sql语句实现对oracle数据库访问的技术
    3)  odbc/ado    vc中访问数据库的技术
    4)  oci         oracle底层提供的连接接口
    5)  jbdc        java访问数据库的技术
2、PLSQL
    2.1、概念
        plsql(procedural language/sql) 是在标准sql的基础上增加了过程化的处理的语言。
        Oracle客户端工具访问oracle服务器的操作语句
        Oracle对SQL的扩充。
    2.2、特点
        结构化模块化编程
        良好的可移植性，良好的可维护性
        提升系统性能

        不便于向异构数据库移植
3、SQL语言的特点
    机器语言    汇编    高级语言    结构化语言

    只管做什么，不管怎么做
    没有过程和控制语句
    没有算法描述能力
4、QL/SQL扩充了
    1)  增加了变量和数据类型
    2)  流程控制语句
    3)  过程和函数
    4)  对象类型和方法

5、plsql程序的结构
    declare
        /*声明区 声明变量、定义类型等*/
        /*没有声明定义的内容，声明区可以省略*/
        --  单行注释
    begin
        /*执行区 执行sql或plsql语句*/
    exception
        /*异常处理区 程序出错后进行处理的区域*/
        /*可以省略*/
    end;
6、plsql的开发环境
    sqlplus         命令行下的工具
    plsql developer 可视化的工具

    begin
        dbms_output.put_line('Hello World!');
    end;
    /   --必须独立占一行

    set serveroutput on //打开sqlplus的输出
7、标识符
    7.1、作用
        给变量、数据类型、游标、异常、过程、函数、包、触发器等命名。
    7.2、使用变量
        declare
            变量名 类型;
            变量名 类型:=值; --赋值 ':='
        begin
            变量名:=值;

        declare
            var_id number;
            var_name varchar(20);
        begin
            var_id:=1;
            var_name:='test';
            dbms_output.put_line(var_id||','||var_name);
        end;
        /
8、变量和数据类型
    8.1、数据类型
    1)  标量类型
        number  binary_integer
        char    varchar2
        date
        boolean
    2)  复合类型
        record
        table
    3)  参考类型
        ref cursor
    4)  大类型  (一般保存大数据的文件路径)
        BLOB    0~4g
        CLOB    0~4g
        BFILE   
    8.2、变量的修饰符
        变量名  constant 类型
        变量名  数据类型 not null

        declare
            --var_id constant number;constant修饰的变量必须初始化
            var_id constant number:=1;
            var_name varchar(25) not null;
        begin
            --var_id:=1;constant修饰的变量不能被赋值
            varchar:='test';
            dbms_output.put_line(var_id);
        end;
        /

        declare
            --var_id constant number;
            var_id constant number:=1;
            --var_name varchar2(25) not null;
            var_name varchar2(25) not null:='test';
        begin
            --var_id:=1;
            var_name:='test';
            dbms_output.put_line(var_id);
        end;
        /
        任何变量在初始化之前都是null
    8.3、使用binary_integer和boolean声明变量
        boolean  true false null

        declare
            var_id binary_integer:=1;
            var_f boolean;
        begin
            var_f:=true;
            if var_f then
                dbms_output.put_line(var_id);
            end if;
        end;
        /
    8.4、声明两个变量，类型分别和s_emp中id和first_name类型相同，吧id=1的员工的id和first_name保存着这两个变量中，并输出。

        declare
            var_id s_emp.id%type;
            var_name s_emp.first_name%type;
        begin
            select id,first_name into var_id,var_name from s_emp where id=1;
            dbms_output.put_line(var_id||','||var_name);
        end;
        /

        获取表中字段的类型:表名.字段%type
        获取表中字段的值给变量赋值:
            select 字段列表 into 变量列表 from 表名 where 条件;
        要求有且只有一行结果
    8.5、record类型 相当于c语言中的结构体
        8.5.1、定义record类型
            type 类型名 is record(
                        字段1 类型，
                        ...
                        字段2 类型
                    );
        8.5.2、定义record类型，成员分别和s_emp中id,first_name和salary的类型一致，声明该类型变量，接受制定编号的员工的信息并输出。

        declare
            type record_emp is record(id s_emp.id%type,
                    name s_emp.first_name%type,
                    salary s_emp.salary%type);
            var_emp record_emp;
        begin
            select id,first_name,salary into var_emp from s_emp where id=1;
            dbms_output.put_line(var_emp.id||','||var_emp.name||','||var_emp.salary);
        end;
        /
    8.6、使用 表名%rowtype 变量
        相当于将这张表中的所有字段的类型都作为一个特殊类型，且字段类型，顺序和表一致。
        declare
            var_emp s_emp%rowtype;
        begin
            select * into var_emp from s_emp where id=1;
            dbms_output.put_line(var_emp.salary);
        end;
        /
    8.7、table类型 类似于c语言中的数组
        8.7.1、定义table类型的语法
            type 类型名称 is table of 元素的数据类型
            index by binary_integer;
        8.7.2、定义一个table类型，声明变量用来保存多个数字
            declare
                type table_number is table of number
                index by binary_integer;
                vars_num table_number;
            begin
                vars_num(1):=1;
            end;
            /
        8.7.3、下标连续，遍历table类型的变量
            declare
                type table_number is table of number
                index by binary_integer;
                vars_num table_number;
                var_i number;
            begin
                vars_num(1):=100;
                vars_num(7):=200;
                vars_num(3):=300;
                var_i:=1;
            end;
            /
            下标不连续，便利table类型的变量
            迭代器思想
                first():获取第一个元素的键
                last():获取最后一个元素的键
                next(n):获取键为n的元素的下一个元素
            declare
                type table_number is table of number
                index by binary_integer;
                vars_num table_number;
                var_i number;
            begin
                vars_num(1):=100;
                vars_num(7):=200;
                vars_num(3):=300;
                var_i:=vars_num.first;
                var_i:=vars_num.next(var_i);
            end;
        8.7.4、定义一个table类型，声明变量保存s_emp表中id为1,3,7的员工的信息，并输出
            declare
                type table_emp is table of s_emp%rowtype
                index by binary_integer;
                vars_emp table_emp;
                var_i binary_integer;
            begin
                select * into vars_emp(1) from s_emp where id=1;
                select * into vars_emp(3) from s_emp where id=3;
                select * into vars_emp(7) from s_emp where id=7;
                var_i:=vars_emp.first();
                dbms_output.put_line(vars_emp(var_i).first_name||','||vars_emp(var_i).salary);
                var_i:=vars_emp.next(var_i);
                dbms_output.put_line(vars_emp(var_i).first_name||','||vars_emp(var_i).salary);
                var_i:=vars_emp.next(var_i);
                dbms_output.put_line(vars_emp(var_i).first_name||','||vars_emp(var_i).salary);
            end;
            /
    8.8、变量的作用域
        plsql代码块的嵌套

        declare
            var_m number:=100;
        begin
            declare
                var_n number:=10;
            begin
                /*局部是可以访问全局变量的*/
                dbms_output.put_line(var_m);    
                dbms_output.put_line(var_n);    
            end;
            dbms_output.put_line(var_m);    
            /*全局不可以访问局部变量的*/
            dbms_output.put_line(var_n);    
        end;

        --  在局部访问同名的全局变量，可以使用标签
        --  定义标签:   <<标签名>>
        --  使用标签:   标签名.变量名
        <<global>>
        declare
            var_n number:=100;
        begin
            declare
                var_n number:=10;
            begin
                /*局部会隐藏同名全局变量*/
                dbms_output.put_line(var_n);    
                dbms_output.put_line(global.var_n);    
            end;
        end;
            /
9、控制语句
    9.1、分支语句
        9.1.1、简单if
            if 条件 then
                操作;
            end if;
        9.1.2、if...else
            if 条件 then
                操作1
            else
                操作2
            end if;
        9.1.3、多分支if
            if 条件1 then
                操作1
            elsif 条件2 then
                操作2
            ...
            [else 操作n]
            end if;
        9.1.4、定义三个变量，并赋值，输出最大值。
        declare
            num1 number:=1;
            num2 number:=2;
            num3 number:=3;
        begin
            if num1>num2 then
                if num1>num3 then
                    dbms_output.put_line(num1);
                else
                    dbms_output.put_line(num2);
                end if;
            else
                if num2>num3 then
                    dbms_output.put_line(num2);
                else
                    dbms_output.put_line(num3);
                end if;
            end if;
        end;
        /

        9.1.5、NULL值的运算特点
            declare 
                var_a number;
                var_b number;
            begin
                if var_a>var_b then
                    dbms_output.put_line('var_a>var_b');
                elsif var_a<var_b then
                    dbms_output.put_line('var_a<var_b');
                elsif var_a=var_b then
                    dbms_output.put_line('var_a=var_b');
                end if;
            end;
            /
            空值参与的逻辑运算，其结果为假
    9.2、循环语句
        循环变量的初始化 循环条件 循环操作 循环条件的更新
        9.2.1、简单循环
            1)语法
                loop
                    循环操作;
                end loop;
            2) 退出循环的方式
                第一种:
                    if 退出循环的条件 then
                        exit;
                    end if;
                第二种:
                    exit when 退出循环的条件;
            3) 使用简单循环 输出1-10
                declare
                    var_i number:=1;
                begin
                    loop
                        dbms_output.put_line(var_i);
                        if var_i=10 then
                            exit;
                        end if;
                        var_i:=var_i+1;
                    end loop;
                end;
                /
        9.2.2、while循环
            1) 语法
                while 条件 loop
                    循环操作
                end loop;
            2)  输出1-10
                declare
                    var_i number:=1;
                begin
                    while var_i<=10 loop
                        dbms_output.put_line(var_i);
                        var_i:=var_i+1;
                    end loop;
                end;
                /
        9.2.3、for循环
            1)  语法
                for 变量 in 区间 loop
                    循环操作
                end loop;

            2)  输出1-10
                begin
                    for var_i in 1..10 loop
                        dbms_output.put_line(var_i);
                    end loop;
                end;
                /
                a.for循环的循环变量不需要声明和初始化
                b.(var_i)循环变量不允许赋值
            3)  逆序
                begin
                    for var_i in reverse 1..10 loop
                        dbms_output.put_line(var_i);
                    end loop;
                end;
        9.3、goto语句
            9.3.1 语法
                <<标签名>>
                    --必须有语句
                    NULL;--空语句
                goto 标签名;
            9.3.2、使用goto退出多层循环
                begin
                    for i in 1..3 loop
                        for j in 1..5 loop
                            dbms_output.put_line(j);
                            if(j=3) then
                                goto outer;
                            end if;
                        end loop;
                    end loop;
                    <<outer>> 
                        NULL;
                end;
                /
            9.3.3、使用exit退出多层循环
                begin
                    <<outer>>
                    for i in 1..3 loop
                        for j in 1..5 loop
                            dbms_output.put_line(j);
                            if(j=3) then
                                exit outer;
                            end if;
                        end loop;
                    end loop;
                end;
                /
10、PLSQL使用Sql语句
    1)selcet语句(dql)
        配合into使用
            select 字段列表 into 变量列表 from 表名 where 条件;
    2)dml语句(insert update delete)
        tcl语句(commit rollback savepoint)
        可以直接在plsql中使用
    3)ddl语句(create drop alter)
        不能直接在plsql中使用
        需要使用动态sql
11、动态sql
    11.1、概念
        把一条sql语句保存在字符串变量中，执行时把字符串变量的过程sql去执行
    11.2、ddl语句的动态sql
        declare
            sqlstr varchar2(100);
        begin
            sqlstr:='create table testdsql_lx_42(id number)';
            execute immediate (substr(sqlstr,1,length(sqlstr)-1) || ',name varchar2(25))');
        end;
        /
    11.3、dml语句的动态sql
        1)  在plsql中直接使用dml和tcl语句
            begin
                insert into testdsql_lx_42 values(1,'test1');
                commit;
            end;
            /
        2)  常规字符串拼接
            declare
                sqlstr varchar2(100);
            begin
                sqlstr:='insert into testdsql_lx_42 values(2,''test2'')';
                execute immediate sqlstr;
                commit;
            end;
            /
        3)  带变量的字符串的拼接
            declare
                sqlstr varchar2(100);
                var_id number:=3;
                var_name varchar2(25):='test3';
            begin
                sqlstr:='insert into testdsql_lx_42 values(' 
                        || var_id || ',' 
                        || '''' || var_name || ''')';
                execute immediate sqlstr;
            end;
        4)  使用占位符 配合using 实现字符串拼接
            declare
                sqlstr varchar2(100);
                var_id number:=4;
                var_name varchar2(25):='test3';
            begin
                sqlstr:='insert into testdsql_lx_42 values(:b0,:b1)'; 
                execute immediate sqlstr using var_id,var_name;
            end;
-------------------
1、定义table类型，声明变量保存s_dept中编号为31.41.45的部门的信息，并循环输出
2、使用动态sql 删除testdsql_lx_42中指定的id的数据行

declare
    type table_dept is table of s_dept%rowtype
    index by binary_integer;
    vars_dept table_dept;
begin
    select * into vars_dept(1) from s_dept where id=31;
    select * into vars_dept(2) from s_dept where id=14;
    select * into vars_dept(3) from s_dept where id=45;
    for var_i in 1..3 loop
        bdms_output.put_line(vars_dept(var_i));
    end loop;
end;
/            

declare
    id number:=1;
    sqlstr varchar(100);
begin
    sqlstr:='delete table testdsql_lx_42 where id=:d';
    execute immediate sqlstr using id;
end;
/

insert into 表名[(字段列表)] values(值列表);
update 表名 set 字段=新值[,字段=新值,...] [where 子句];
delete [from] 表名 [where 子句];