PL/SQL第二课(学习笔记)

时间:2023-02-10 15:33:44
第五章
游标CURSOR
CURSOR 用于提取多行数据集

DECLARE CURSOR

DECLARE
    v_major student.major%TYPE; --定义一个变量
CURSOR c_sutdent IS
--可以在这里为我们的参数进行赋值然后我们就可以查询到数据,然后将数据放置在游标内
--赋值数据v_major
    SELECT first_name,last_name
    FROM students
    WHERE major = v_major;

建立一个游标c_student内容为first_name,last_name,要求为major= v_major的内容
********============****************

Open Cursor
语法:OPEN c_student;

FETCH FROM CURSOR
FETCH 的两种方式
FETCH cursor_name INTO var1,var2    --一个一个参数的赋值
FETCH cursor_name INTO record_var    --一个集合内部全部赋值

CLOSE CURSOR
CLOSE cursor_name
游标使用结束后应当关闭
  FETCH 关闭后的游标是非法的
  关闭一个关闭了的游标是非法的

游标的属性
%FOUND
若前面的FETCH语句返回一行数据,则%FOUND返回TRUE
若游标没有打开就检查%FOUND,则返回ORA-1001
%NOTFOUND
与%FOUND相反
%ISOPEN
检测游标是否打开
%ROWCOUNT
当前游标指针的位移量,到目前为止游标所检索的数据行的个数,若未打开就未引用则返回ORA-1001

游标的属性TEST
假设表
temp_table
只有两行数据
Declare
    --Cursor declaration
    CURSOR c_TempData IS SELECT * FROM temp_table;
    --Record to store the fetched data
    v_TempRecord c_TempData%ROWTYPE;
BEGIN
    --lacation 1
    OPEN c_TempData;
    --location 2
    FETCH c_TempData INTO v_TempRecord.--Fetch first row
    DBMS_OUTPUT.PUT_LINE(v_TempRecord.id||' '||v_TempRecord.name);
    --location 3
    FETCH c_TempData INTO v_TempRecord.--Fetch second row
    DBMS_OUTPUT.PUT_LINE(v_TempRecord.id||' '||v_TempRecord.name);
    --location4
     FETCH c_TempData INTO v_TempRecord;--Fetch third row
    --location 5
    CLOSE c_TempData;
    --location 6
END;

游标的FETCH循环
1、LOOP
    FETCH cursor INTO
    EXIT WHEN cursor%NOTFOUND;
    END LOOP;
2、WHILE cursor%FOUND LOOP
    FETCH cursor INTO
    END LOOP ;
3、FOR var IN cursor LOOP
    FETCH cursor INTO
    END LOOP;

例:
DECLARE
    --Cursor declaration
    CURSOR c_TempDate IS SELECT * FROM temp_table;
    --Record to store the fetched data
    v_TempRecord c_TempData%ROWTYPE;
一、BEGIN
    --location1
    OPEN c_TempDate;
    --location2
    FETCH c_TempDate INTO v_TempRecord;
    --location3
    WHILE c_TempDate%FOUND LOOP
    FETCH c_TempDate INTO v_TempRecord;
    END LOOP;
    --location4
    CLOSE c_TempDate;
   END
二、BEGIN
    --location1
    OPEN c_TempDate;
    --location2
    LOOP
    FETCH c_TempDate INTO v_TempRecord;
    DBMS_OUTPUT.PUT_LINE(v_TempRecord.id||' '||v_TempRecord.name);
    END WHEN c_TempDate%NOTFOUND;
    END LOOP;
    --location3
    CLOSE c_TempDate;
   END
三、BEGIN
    --location1
    不要开启
    --location2
    FOR v_TempRecord IN c_TempDate LOOP
    END LOOP;
    --location3
    不要关闭

带参数的游标
CURSOR 可以带参数
 DECLARE
   CURSOR c_student(p_major student.major%TYPE)
    --IS--在建立游标的同时建立参数括号内部的数据包含,参数名,和参数类型。
    --参数申明的时候类型的约束不可以些(如精度,刻度等) 仅写类型就好。   
    --尽量使用TYPE OR ROWTYPE定义参数的类型
   SELECT *
   FROM students
   WHERE major = p_major;
 BEGIN
   OPEN c_student(101);--打开时候给游标的参数赋值
   .....其他一样。

如果是for语句参数怎么附加呢?
FOR c_student(101) INTO v_emp;

=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+

Handle Exception 异常处理
PL/SQL错误
      1、编译时
    --语法的错误
      2、运行时
        --例:没有数据,等。。。
运行时的出错处理
      EXCEPTION
异常分类:
    1、用户自定义
    2、Oracle预定义
异常处理快
DECLARE
。。。。
BEGIN
。。。
EXCEPTION
    WHEN OTHERS THEN
        handler_error(...);
END;

用户自定义异常
DECLARE
    e_TooManyStudents EXCEPTION;
BEGIN
    ...
    RAISE e_ToolManyStudents;--抛出异常,RAISE e_ToolManyStudents(异常名称);
    ...
EXCEPTION
    WHEN e_TooManyStudents THEN--当异常了则
    ...
END;

预定义的ORACL异常
1、ORA-0001
    DUP_VAL_ION_INDEX
2、ORA-0051
    TIMEOUT_ON_RESOURCE
3、ORA-1001
    INVALID_CURSOR
    ...
4、ORA-6533
    SUBSCRIPT_BEYOND_COUNT

例:
DECLARE
    v_emp s_emp%ROWTYPE;--定义参数

    e_MyException EXCEPTION;--定义异常
BEGIN
    SELECT *
     INTO v_emp
     FROM s_emp
    WHERE id = 100;
    
    IF v_emp.salary<1000 THEN
     RAISE e_MYException;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('v_emp.id = '|| v_emp.id);

    
EXCEPTION  --若没有出现异常,则就不执行EXCEPTION下的异常处理内容。
    WHEN NO_DATA_FOUND THEN--获取oracle系统预定义异常
     DBMS_OUTPUT.PUT_LIEN('v_emp : no data in v_emp !');

    WHEN e_MyException THEN--获取自定义异常
     DBMS_OUTPUT.PUT_LINE('v_emp : salary is too low!');
     UPDATE s_emp SET salary = 1000--更新工资为1000
      WHERE  id = v_emp.id;

    --统一处理异常处理上面处理过的其他异常
    WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('v_emp: all others exceptin !');
    --不可将这个循序为第一位置,必须为最后一位,因为放置的位置与程序的执行流程是有关系的。
END;

处理异常
EXCEPTION
    WHEN exception_name1 THEN
    sequence_of_statements1;
    WHEN exception_name2 THEN
    sequence_of_statements2;
    [WHEN OTHERS THEN    --其他任何异常都执行这段代码
    sequence_of_statements3;]
END;

处理所有的异常
1、异常的传递
2、处理所有其他异常
    EXCEPTION
        WHEN s_ToolManyStudents THEN
        
        WHEN OTHERS THEN
            v_ErrCode :=SQLCODE;
            v_ErrText :=SUBSTR(SQLERRM,1,200);
            INSERT INTO log_file(code,message,info)
            VALUES(v_ErrCode,v_ErrText,'ORACLE Error');
    END;


SUB PROGRAM子程序
1、匿名块
   匿名块不粗那在于数据库中
   每次使用时都会进行编译
   不能在其他块中相互调用
2、带名块
   可存储于数据库中
   可以在任何需要的地方调用
   procedure,function,package,trigger

PROCEDURE存储过程
创建过程
    CREATE [OR REPLACE] PROCEDURE proc_name
    [(arg_name[{IN|OUT|IN OUT}] TYPE.
        ...
       arg_name[{IN|OUT|IN OUT}] TYPE)]
       {IS|AS}
    procedure_body
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―    
CREATE OR REPLACE PROCEDURE pro_hello AS --存储过程的名字与创建    |
                                |
BEGIN                                |
    DBMS_OUTPUT_PUTLINE('hello world');            |
END                                |
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―
上面的存储过程写完毕需要执行创建
若上面的名字为pro_hello.sql则运行
SQL> @pro_hello.sql
那么创建完毕。存储过程已经存放在数据库 里面了,那么我们要执行这个存储过程就可以这么写
SQL> pro_hello
写成块脚本
BEGIN
    --pro_hello无参数,直接执行
    pro_hello;
END

上面的存储过程中没有任何数据的定义,以及变量的使用,那么我们在这里进行数据的定义与数据的其他设置
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―    
CREATE/*创建*/ OR REPLACE/*重新创建*/ PROCEDURE pro_hello        |
    (p_id s_emp.id%TYPE)                    |
 AS                                |
    v_emp s_emp%ROWTYPE;                    |
                                |
BEGIN                                |
    SELECT * INTO v_emp                    |
     FROM s_emp                        |
    WHERE id =p_id;                     |
                                |
    DBMS_OUTPUT_PUTLINE('hello world');            |
END                                |
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―

怎样使用上面的存储过程呢。。。。。。
SQL>pro_hello(1);--此处数字为传入参数
也可以如此写匿名块的脚本
BEGIN
    --pro_hello存储过程的执行
    pro_hellp(1);
END

如此传值,为IN 模式传递值,就是传递一个值给存储过程,没有返回值虽然他返回的内容是显示数据,但是没有返回值这个与OUT模式的相反

OUT模式的传递    相当于程序中有返回值
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―    
CREATE/*创建*/ OR REPLACE/*重新创建*/ PROCEDURE pro_hello        |
    (p_id  IN s_emp.id%TYPE,                |
    p_name OUT s_emp.first_name%TYPE)            |
 AS                                |
    v_emp s_emp%ROWTYPE;                    |
                                |
BEGIN                                |
    SELECT * INTO v_emp                    |
     FROM s_emp                        |
    WHERE id =p_id;                     |
                                |
    DBMS_OUTPUT_PUTLINE('hello world');            |
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―

DECLARE
    v_name  s_emp.first_name%TYPE;
BEGIN
    --pro_hello out执行
    pro_hello(1,v_name);

    DBMS_OUTPUT.PUT_LINE('hello, ' || v_name);
END;





PROCEDURE的参数模式
IN
    在调用过程的时候,实际参数的值被传递给该过程,在过程内部,形参是只可读的。
OUT
    在调用过程时,任何的实参将被忽略;在过程内部,形象是只可写的。
IN OUT
    是IN与OUT的组合,在调用过程的时候,实参的值可以被传递给该过程;在过程内部,行参也可以被读出也可以被写入,过程结束时,控制会返回给控制环境,面形式参数的内容将赋给调用时的实际参数。


注: 可以使用SHOW ERRORS命令查看错误的信息



文字或常数作为实参
模式为IN OUT 或OUT 参数对应的实际参数必须是变量,不能是常量或表达式。必须要有一个位置可以存储返回的值

对形式参数的约束
1、在调用过程当中,实际参数在将值传递给过程时,也传递了对变量的约束
2、形式参数不能声明约束,但可使用%TYPE来进行约束

指定实参的模式
1、位置标识法
pro_hello(1,v_name);
2、名字标识法
pro_hello(p_name=>v_name,p_id=>1);--可读性强

3、混合
用的第一位必须位置标识法
名字标示法对于参数很多时,可提高程序的可读性

使用缺省参数
1、形参可以指明缺省值
    parm_name[mode]type {:=|DEFAULT} init_value
2、位置标示法时,所有的缺省都放在最后面
    使用名字标示法则无所谓
3、声明时,所有的缺省值,尽量将缺省值放在参数表的末尾

FUNCTION
1、函数在所有的地方都与过程相似
   都有名字
   都有统一的形式,:声明,执行与异常处理
   可以存储在数据库中,也可以声明在无名块的内部
2、差别
   过程调用本身是一个PL/SQL查询
   函数调用是表达式的一部分

函数的声明
CREATE [OR REPLACE] FUNCTION func_name
    [{arg_name[{IN|OUT|IN OUT|]TYPE;
    ...
    (arg_name[{IN|OUT|IN OUT|}]TYPE)]
RETURN TYPE
    {IS|AS}
Func_body

RETURN 语句
1、在函数的主体内部 ,return语句用来将控制通过一个数值返回给调用环境
    RETURN <表达式>;
2、在一个函数主体中,可以使用多个返回语句,只有一个会被执行
3、没有返回语句的函数将是一个错误。

函数样式
1、函数可以他哦难过OUT参数来返回多个数值
2、函数可以接收缺省参数

__________________________________________________
CREATE OR REPLACE FUNCTION fun_add(        |
    p_a NUMBER,                |
    p_b NUMBER) RETURN NUMBER         |
IS                        |
    v_ret number;                |
BEGIN                        |
    v_ret := p_a + p_b;            |
    RETURN v_ret ;                |
END                        |
________________________________________________|_

建立SQL脚本执行
DECLARE
    v_ret number;
BEGIN
    v_ret := fun_add(2,3);
    DBMS_OUTPUT.PUT_LINE(v_ret);
END;

删除过程与函数
DROP PROCEDURE proc_name;
DROP FUNCTION func_name;

子程序的位置
1、Stored subprogram
    通过CREATE OR REPLACE 命令创建
    以编译后的形式存放在数据库中
2、本地子程序
    没有CREATE OR REPLACE 关键字
    子程序的定义放在无名块的声明部分
    子程序被该无名块使用


*******************************************

Package    包
1、包规范
    一个大的声明区
    可以声明变量,声明异常,子程序。。。

    注意:在包规范中,只有声明没有实现

包头的例子1
CREATE OR REPLACE PACKAGE pak_test AS
    PROCEDURE AddStudent(
    p_StuID IN students.id%TYPE,
    p_Dep IN classes.department%TYPE,
    p_Course IN classes.course%TYPE
    );
    PROCEDURE RemoveStudent(
    p_StuID IN students.id%TYPE
    );
    e_StudentNotRegistered EXCEPTION;
    TYPE t_StuIDTable IS TABLE OF students.id%TYPE
    INDEX BY BINNARY_INTEGER;
END pak_name;
2、包主体

包主体的例子1
CREATE OR REPLACE PACKAGE BODY pak_test AS
    PROCEDURE AddStudent(
        p_StuID IN student.id%TYPE,
        p_Dep IN classes.department%TYPE,
        p_Course IN classes.course%TYPE) IS
    BEGIN
        ...
    END AddStudent;
    PROCEDURE RemoveStudent(
        p_StuID IN  students.id%TYPE
        ) IS
    BEGIN
        ...
    END RemoveStudent;
END pak_test;