学习笔记day67-----oracle-存储过程、函数、触发器、包

2、游标 cursor
    2.1、作用
        用来处理多行的查询结果集
    2.2、概念
        游标可以看成是一个位置实体或者位置指针
        游标是映射在结果集中的一行数据上的位置实体。
        有了游标，用户就可以访问结果集中的任何一行数据。
        将游标放到某行数据后就可以操作该行数据，比如提取出数据。
    2.3、游标的使用步骤
        1)  声明游标
            在声明区
            声明游标名和对应的select语句(这个时候select还没有执行)
            语法:
                cursor 游标名 is select 语句;
            游标声明后，可以使用游标名%rowtype声明变量
        执行区
        2)  打开游标
            执行select语句，把结果集存储在游标的工作区，并且指针指向结果集的第一行数据。
            语法:
                open 游标名;
        3)  提取数据
            从游标对应的工作区中，把当前行的数据检索到变量中。
            游标指针自动向下移动一行。
            语法:
                fetch 游标名 into 变量;
        4)  关闭游标
            当提取和处理完结果集中的数据后，应该及时关闭游标，释放其占有的资源，游标对应的工作区变为不可用
            语法:
                close 游标名;
    2.4、使用游标查询s_emp中的数据
        declare
            cursor empcursor is select * from s_emp;
            var_emp empcursor%rowtype;
        begin
            open empcursor;
            fetch empcursor into var_emp;
            dbms_output.put_line(var_emp.id||','||var_emp.first_name);
            close empcursor;
        end;
        /
    2.5、游标的属性
        游标名%属性
        found       在提取数据时，如果提取到了新数据，则返回真，否则返回假
                    如果游标没有打开，产生非法游标异常。
                    如果没有提取，则返回NULL
        notfound    在提取数据时，如果没有提取到新数据，则返回真，否则返回假
                    如果游标没有打开，产生非法游标异常。
                    如果没有提取，则返回NULL
        ifopen      游标是否为打开状态，如果游标打开，则返回真，游标关闭则返回假
                    已经打开的游标不能再次打开(异常，游标已打开)
                    已经关闭的游标不能再次关闭(异常，非法游标)
        rowcount    游标指针的偏移量
                    如果没有打开，则为非法游标
    2.6、使用简单循环
        declare
            cursor empcursor is select * from s_emp;
            var_emp empcursor%rowtype;
        begin
            open empcursor;
            loop
                fetch empcursor into var_emp;
                    exit when empcursor%notfound;
                dbms_output.put_line(var_emp.id||','||var_emp.first_name);
            end loop;
            close empcursor;
        end;
    2.7、使用while循环
        declare
            cursor empcursor is select * from s_emp;
            var_emp empcursor%rowtype;
        begin
            open empcursor;
            fetch empcursor into var_emp;
            while empcursor%found loop
                dbms_output.put_line(var_emp.id||','||var_emp.first_name);
                fetch empcursor into var_emp;
            end loop;
            close empcursor;
        end;
    2.8、使用for循环
        智能循环(自动打开，提取和关闭)
        declare
            cursor empcursor is select * from s_emp;
        begin
            for var_emp in empcursor loop
                dbms_output.put_line(var_emp.id||','||var_emp.first_name);
            end loop;
        end;
    2.9、带参游标
        参数的数据类型不能有长度或精度的修饰。可以使用%type.
        声明游标时:
            cursor 游标名(形参列表) is select 字段列表 from 表名;
                where 条件;
        打开游标时传参:
            open 游标名(实参列表)

        特殊情况，for循环
        declare
            cursor empcursor(var_id number) is select * from s_emp where id>var_id;
        begin
            for var_emp in empcursor(10) loop
                dbms_output.put_line(var_emp.id||','||var_emp.first_name);
            end loop;
        end;
    2.10、参考游标  ref cursor
        游标+动态sql
        2.10.1、使用步骤
            sqlstr:='select * from s_emp';

            1)  定义参考游标类型
                type    参考游标类型名 is ref cursor;
            2)  声明参考游标类型变量(相当于游标使用的声明游标)
                参考游标变量名 参考游标类型名;
            3)  把参考游标变量和动态sql语句结合(相当于游标使用的打开游标)
                open 参考游标变量名 for sqlstr;
            4)  提取数据
            5)  关闭游标
        2.10.2、使用参考游标处理多行结果集
            declare
                sqlstr varchar2(100);
                type emprcursor is ref cursor;
                var_cursor emprcursor;
                var_emp s_emp%rowtype;
            begin
                sqlstr:='select * from s_emp where id>:d0';
                open var_cursor for sqlstr using 10;
                loop
                    fetch var_cursor into var_emp;
                    exit when var_cursor%notfound;
                    dbms_output.put_line(var_emp.id||','||var_emp.first_name);
                end loop;
                close var_cursor;
            end;
            /
3、QLSQL中的异常
    3.1、系统预定义异常
        3.1.1、概念
            Oracle系统为用户提供的，可以在plsql中去使用，用来检查用户代码失败的一般原因。
            定义在Oracle的plsql的核心库，使用时用异常的名字进行标志，对异常进行捕获和处理。
        3.1.2、语法:
            exception
                when 异常名 then 
                    异常处理
        3.1.3、示例:处理一个异常
            declare
                var_id number:=&id;
                var_name varchar2(25);
            begin
                select first_name into var_name from s_emp where id=var_id;
                dbms_output.put_line(var_name);
            exception
                when no_data_found then dbms_output.put_line('no emp');
            end;
            /
        3.1.4、处理其他处理
            declare
                var_id number:=&id;
                var_name varchar2(25);
            begin
                select first_name into var_name from s_emp where id>var_id;
                dbms_output.put_line(var_name);
            exception
                when no_data_found then dbms_output.put_line('no emp');
                when others then
                    dbms_output.put_line('others:'||sqlcode||'#####'||sqlerrm);
            end;
        3.1.5、常用的系统预定义异常
            cursor_already_open 游标已打开
            invalid_cursor      非法游标
            invalid_number      内嵌的sql语句无法将字符串隐式转换成数字
            no_data_found       select...into语句没有返回行
            too_many_rows       select...语句返回超过1行
            dup_val_on_index    唯一索引对应的字段上有重复值
            zero_divide         除数为0

            declare
                var_id number:=&id;
                var_name varchar2(25);
            begin
                select first_name into var_name from s_emp where id>var_id;
                dbms_output.put_line(var_name);
            exception
                when no_data_found then dbms_output.put_line('no emp');
                when too_many_rows then dbms_output.put_line('too many rows');
                when others then
                    dbms_output.put_line('others:'||sqlcode||'#####'||sqlerrm);
            end;
    3.2、自定义异常
        3.2.1、使用步骤
            1)  定义异常
                在声明区
                异常命  exception
            2)  根据条件引发异常
                在执行区
                    if  引发异常的条件 then
                        raise 异常名;
                    end if;
            3)  捕获和处理异常
                在异常处理区
                when 异常名 then 异常处理
        3.2.2、根据id修改员工的信息，如果员工不存在，引发异常
            declare
                var_id number:=&a;
                no_emp exception;
            begin
                update emp_lx_42 set salary=salary+500 where id=var_id;
                if sql%notfound then 
                    raise no_emp;
                end if;
            exception
                when no_emp then dbms_output.put_line('no this emp');
            end;
            /
            隐式游标:
            执行任何一个sql的语句的时候oracle都会自动的创建一个隐式游标。
            这个游标是内存中为该sql语句分配的工作区。
            隐式游标也有属性，并且使用属性时需要提供游标名，oracle给隐式游标命名为sql.
            隐式游标通常用来处理DML(insert update delete)语句
4、存储过程 procedure
    4.1、匿名块和有名块
        匿名块:
            匿名块是不保存在数据库中的
            每次使用时都需要进行编译
            匿名块不能被其他块调用
        有名块:
            可以存储在数据库中
            可以在任何需要的地方调用

            有名块包括: 存储过程    函数        包      触发器
                        procedure   function    package trigger
    4.2、创建存储过程的语法
        create[ or replace] procedure 过程名[(参数列表)] {is|as}
            --临时变量
        begin
        exception
        end;
    4.3、无参的存储过程
        4.3.1、创建:根据编号查名字
            create or replace procedure serch_name_proc_lx_42 is 
                var_i number:=1;
                var_name varchar2(25);
            begin
                select first_name into var_name from s_emp where id=var_i;
                dbms_output.put_line(var_name);
            end;
        show error//列出报错
        4.3.2、调用
            存储过程在plsql中调用方式 是直接调用。
            begin
                serch_name_proc_lx_42;
            end;
    4.4、带参的存储过程
        4.4.1、参数列表的语法
            参数名{[in]|out|in out} 数据类型[{:=|default}值]
            1)  参数的数据类型(不能包含长度或者精度的修饰)
            2)  参数的模式
                in      输入参数        负责传入数据            缺省方式
                out     输出参数        负责传出数据            必须是变量，不需要初始化
                in out  输入输出参数    及负责传入又负责输出    实参是初始化的变量
            3)  参数的默认值
                in模式的参数，才可以有默认值
        4.4.2、创建带参的存储过程，实现根据id查询first_name
            create or replace procedure serch_name_proc_lx_42(var_id number:=1,var_name out varchar2) is
            begin
                select first_name into var_name from s_emp where id=var_id;
            end;
        4.4.3、调用带参数的存储过程
            1)  按照位置赋值
            declare
                var_name varchar2(25);
            begin
                serch_name_proc_lx_42(2,var_name);
                dbms_output.put_line(var_name);
            end;
            2)  按照名字赋值 性参名=>值
            declare
                var_name varchar2(25);
            begin
                serch_name_proc_lx_42(var_name=>var_name);
                dbms_output.put_line(var_name);
            end;

        desc 存储过程名，可以看到存储过程的参数列表
    4.5、创建一个存储过程，输入一个大于1的整数，返回1..n的累加和
        create or replace procedure serch_name_proc_lx_42(var_max number,var_sum out number) is
        begin 
            var_sum:=0;
            for i in 1..var_max loop
                var_sum:=var_sum+i;
            end loop;
        end;
        declare
            var_sum number(7);
        begin
            serch_name_proc_lx_42(10,var_sum);
            dbms_output.put_line(var_sum);
        end;
5、函数 function
    5.1、函数和存储过程的区别
        1)  关键字不同  存储过程是 procedure 函数是 function
        2)  函数有返回类型和返回值，存储过程没有
        3)  在plsql中调用的时候，过程是直接调用，而函数的调用必须组成表达式
            使用变量接受返回值
            把函数的调用作为其他函数或者存储过程的参数
    5.2、创建函数的语法
        create[ or replace] function 函数名[(参数列表)] return 返回值的数据类型 {is|as}
        --临时变量
        begin
            --必须有return语句
            return 值;
        exception
        end;
    5.3、把上一个练习改写成函数实现
        --创建
        create or replace function serch_name_func_lx_42(var_max number) return number is
            var_sum number:=0;
        begin
            for i in 1..var_max loop
                var_sum:=var_sum+i;
            end loop;
            return var_sum;
        end;
        --调用
        begin
            dbms_output.put_line(serch_name_func_lx_42(10));
        end;
6、包 package
    6.1、概念
        把逻辑上相关的一组变量、数据类型、过程、函数等组织到一起的一种逻辑结构。
    6.2、系统提供的包
        dbms_output     
        dbms_random
        dbms_job
        查看包中的数据:     desc 包名
        使用包中的子程序:   包名.函数   或者    包名.过程
    6.3、自定义包
        1)  定义包的头部    类似于C中的.h文件
            --语法
                create[ or replace] package 包名
                is
                    --过程、函数的声明，类型的定义，变量的声明
                end[ 包名];
        2)  包的实现    类似与C中的.c文件
            create[or replace] package body 包的名字
            is
                --过程、函数的实现
            end[ 包名];

        举例:创建包，包含一个求最大值的函数，一个求最小值的过程
        --创建包的头部
        create or replace package mypackage_lx_42
        is
            procedure getmin(a number,b number);
            function getmax(a number,b number) return number;
        end mypackage_lx_42;
        --创建包的正文
        create or replace package body mypackage_lx_42
        is
            procedure getmin(a number,b number) is
            begin
                if a < b then
                    dbms_output.put_line(a);
                else
                    dbms_output.put_line(b);
                end if;
            end;
            function getmax(a number,b number) return number is
            begin
                if a > b then
                    return a;
                else
                    return b;
                end if;
            end;
        end mypackage_lx_42;
        --测试
        begin
            dbms_output.put_line(mypackage_lx_42.getmax(2,3));
        end;
7、触发器   trigger
    7.1、概念和作用
        触发器可以看做一种特殊的存储过程，定义了一些和数据库相关的事件(insert,update,delete,...)发生时应该执行的功能代码块。
        通常用来管理复杂的完整性约束、监控对表的修改、通知其他程序等。
    7.2、创建触发器的语法
        DML触发器
            create [or replace] trigger 触发器名 {before|after} {insert|update|delete} on 表名 [for each row]
            declare
            begin
            end;
    7.3、语句级触发器
            create or replace trigger emp_update_tri_lx_42 after update on emp_lx_42
            declare
            begin
                dbms_output.put_line('someone update table -> emp_lx_42');
            end;
        语句级触发器和影响的行数无关
    7.4、行级触发器
            create or replace trigger emp_update_tri_lx_42 after update on emp_lx_42 for each row
            declare
            begin
                dbms_output.put_line('someone update table -> emp_lx_42');
                dbms_output.put_line('old salary:'||:old.salary);
                dbms_output.put_line('new salary:'||:new.salary);
            end;

            update emp_lx_42 set salary=salary+100 where id=1;
            update emp_lx_42 set salary=salary+100 where id<1;
            update emp_lx_42 set salary=salary+100 where id>1;

        列标识符：  %rowtype类型
            :new    新值标识符
            :old    原值标识符
    7.5、使用触发器自动为主键字段填充值
        --创建测试表
            create table testtrigger_lx_42(id number primary key,name varchar2(25));
        --创建一个序列
            create sequence testtrigger_id_lx_42;
        --创建触发器
            create or replace trigger testtrigger_insert_lx_42 before insert on testtrigger_lx_42 for each row
            begin
                select testtrigger_id_lx_42.nextval into :new.id from dual;
                dbms_output.put_line('new id:'||:new.id);
            end;
        --测试
            insert into testtrigger_lx_42(name) values('test');