第七章：%type&标签&if&while&loop



- -plsql编程基础
- -变量的声明和使用
- - 1、任何变量在使用前必须现在 declare部分声明，然后才能使用；
- - 2、如果不给变量明确地赋值（初始化），那么变量自动
- -得到 null值，不管变量是什么类型
- - 3、声明变量时必须指定数据类型，这样plsql才知道
- -该变量占据多少存储空间以及可以参与什么运算。

- -以下代码，会在屏幕上输出字面量 'born on':
declare
  v_name varchar2( 20);
  v_dob date;
  v_us_citizen boolean;
begin
  dbms_output.put_line(v_name|| ' born on '||v_dob);
end;

- -当 v _name和v_dob变量值为 null时，
- -希望分别输出’ No Name‘和 '01-Jan-1999' ，可以怎么做 ？
declare
  v_name varchar2( 20);
  v_dob date;
  v_us_citizen boolean;
begin
  dbms_output.put_line(nvl(v_name, 'No Name')||
     ' born on '||nvl(v_dob,to_date( '1999-01-01', 'YYYY-MM-DD')));
end;

- -给变量赋值有 2个要点
- - 1、数据类型要一致
- - 2、数据大小要匹配


- -使用% type属性指定变量的数据类型。提倡尽可能使用
- -语法 ：变量名  表名.列名% type;
- -含义：指定变量的数据类型和某个表的某个列的类型一模一样。

- -什么时候用？
- -如果你什么变量想用来保存某些列的值（ select   into）
- -或者想用来给某些列赋值（ insert或者 update），就可以使用

declare
  v_name student.first_name% type;
  v_grade grade.numeric_grade% type;
begin
  dbms_output.put_line(v_name|| ' 有成绩 '||v_grade);
end;


- -标签
- -语法： < <标签名 > >
- -标签可以添加到语句块，以改进代码的可读性。这是他的
- -第一个作用。
- -标签必须在可执行代码第 1行(或者 BEGIN或者 DECLARE)的前面
< <find_stu_num > >
begin
  dbms_output.put_line( '过程find_stu_num已经执行');
end  find_stu_num;

- -注意：第一行是标签的plsql代码不能在命令窗口执行，
- -可以在sqlplus中或者测试窗口中执行


- -嵌套语句块
- -嵌套语句块是完全位于其它语句块中的语句块，例如：
BEGIN        - - outer block 
       BEGIN       - - inner block 
              ……
       END;         - - end of inner block
  END;        - - end of outer block 

- -嵌套语句块会对变量的作用域带来影响

- -变量的作用域：
- -变量的作用域是可以访问变量或者变量可见的程序块范围。

- -通常，变量的作用域就是声明该变量的当前语句块。

- -在外部块中声明的变量其作用域就是整个外
- -部块（从 BEGIN开始一直到 END），在嵌套块中声明的变量
- -其作用域就是整个嵌套块（从 BEGIN开始一直到 END）。

- -嵌套块可以直接访问外部块中声明的变量。但是当在
- -嵌套块中声明了一个和外部块中同名的变量时，嵌套块
- -就不能直接访问外部块中的同名变量了，因为这时它
- -看不见外部块中的同名变量。

- -只能通过标签名来引用外部同名变量。这是标签的第二个作用。

< <outer_block > >
declare
  v_test binary_integer : = 123;
begin
  dbms_output.put_line( '在外部块中，v_test='||v_test);
   < <inner_block > >
   declare
    v_test binary_integer : = 456;
   begin
    dbms_output.put_line( '在内部块中，v_test='||v_test); 
    dbms_output.put_line( '在内部块中，外部块v_test='||
      outer_block.v_test);
   end inner_block;     
end outer_block;


- -在plsql中使用 sql
- -主要观察 sql语句的一些变化。

- - 1、 select语句的变化：带 into子句，用来给变量赋值。
- -值来自于表的某些列。另外一种变量赋值的方式是使用: =操作符

- -示例 1：将COURSE表中所有课程的平均费用赋给一个变量
declare
  avg_cost varchar2( 12);
begin
   - -查询平均费用，保存到avg_cost中
   select to_char( avg(cost), '$99,999.99')
     into avg_cost
     from course;

  dbms_output.put_line( '课程的平均费用是：'||avg_cost);  
end;


- - 2、dml语句的变化：语句中带有变量
- -示例 2：使用变量值更新ZIPCODE邮编表中已有的行
declare
  v_city zipcode.city% type;
begin
   - -变量初始化：注意如何使用 select   into的语法将字面量
   - -付给一个变量
   select   'COLUMBUS'
     into v_city
     from dual;

   - -更新邮编 43224  
   update zipcode
     set city = v_city
     where zip = 43224;    

  dbms_output.put_line( '更新成功');
end;

- -示例 3：使用变量值向ZIPCODE邮编表中插入行
declare
  v_zip zipcode.zip% type;
  v_user zipcode.created_by% type;
  v_date zipcode.created_date% type;
begin
   - -变量初始化
   select '43438', user,sysdate
     into v_zip,v_user,v_date
     from dual;

   - -插入新行
   insert into zipcode(zip,created_by,
    created_date,modified_by,modified_date)  
     values(v_zip,v_user,v_date,v_user,v_date);

    dbms_output.put_line( '插入成功');  
end;


- -plsql块和事务的关系
- - 1、一个事务中可以包含多个plsql块
- - 2、一个plsql块中可以包含多个事务（例如在循环中提交）

- -提交控制之if语句
- -使用条件控制，可以基于某种条件来控制程序的执行流
- -PL / SQL有 3种类型的条件控制: IF、ELSIF和 CASE语句

- -IF语句有两种形式:
- - IF - THEN（一个分支）和IF - THEN - ELSE（两个分支）

- -示例 1：两个数字值存储在变量v_num1和v_num2中，
- -如果v_num1大于v_num2，则交换两个数字并把它们在屏幕
- -上打印出来
declare
  v_num1 binary_integer : = 5;
  v_num2 binary_integer : = 3;
  v_temp binary_integer;
begin
   - -判断大小  
  if v_num1 > v_num2 then
     - -交换
    v_temp : = v_num1;
    v_num1 : = v_num2;
    v_num2 : = v_temp;  
   end if;

  dbms_output.put_line( 'v_num1='||v_num1);
  dbms_output.put_line( 'v_num2='||v_num2);
end;

- -语法如下: 
IF condition THEN 
      STATEMENT 1; 
ELSE 
       STATEMENT 2; 
END IF; 
STATEMENT 3;


- -示例 2：判断用户提供的数字是否为偶数并打印结果
declare
  v_num number : = &sv_num;
begin
   - -判断除 2余数是否为 0
  if mod(v_num, 2) = 0 then
    dbms_output.put_line( '偶数');
   else
    dbms_output.put_line( '奇数');    
   end if;

  dbms_output.put_line( 'Done...');
end;

- -示例 3：IF条件的计算结果是 NULL
- -如果if带有 else子句，则执行 else的部分，否则程序
- -直接转到 end if之后执行

declare
  v_num1 number : = 0;
  v_num2 number;   - - null
begin
  if v_num1 =v_num2 then
     dbms_output.put_line( 'v_num1=v_num2');  
   else
     dbms_output.put_line( 'v_num1!=v_num2'); 
   end if;    
end;


- -示例 4：使用IF - THEN语句来测试用户所提供的日期
- -是否是星期六或者星期天
declare
  v_date date : = to_date( '&sv_date', 'yyyy-mm-dd');  
  v_day varchar2( 10);
begin
   - -得到输入的日期所代表的那一天的名字
  v_day : = rtrim(to_char(v_date, 'DAY'));  
   - -测试变量的值
   - -dbms_output.put_line(v_day);

   - -判断
  if v_day in ( 'SATURDAY', 'SUNDAY') then
    dbms_output.put_line( '周末');
   end if;

  dbms_output.put_line( 'Done');
end;

- -如果条件判断需要产生 3个以上的分支，那么就使用elsif语句

- -ELSIF语句的结构如下所示:
IF  condition 1   THEN 
STATEMENT 1; 
ELSIF condition 2   THEN     - -注意ELSIF中只有 1个E
STATEMENT 2; 
ELSIF  condition 3   THEN 
STATEMENT 3; 
……
[ ELSE ] 
[ STATEMENT N ];
END IF;


- -示例 6：判断用户输入的数字是 0、正数还是负数
declare
  v_num number : = &sv_num;
begin
  if v_num < 0 then
    dbms_output.put_line( '负的');
  elsif v_num = 0 then
    dbms_output.put_line( '0'); 
   else
    dbms_output.put_line( '正的');  
   end if;       
end;

- -嵌套的if语句：
- -各种if的写法，相互之间可以任意嵌套。
- -即，在一个条件分支中，可以再开一个条件判断
- -假设变量v _num1和v_num2的值分别是 - 4和 3
declare
  v_num1 number : = &sv_num1;
  v_num2 number : = &sv_num2;
  v_total number;
begin
   - -外部if
  if v_num1 > v_num2 then
    dbms_output.put_line( '现在在外部if的if部分');
    v_total : = v_num1 - v_num2;
   else
    dbms_output.put_line( '现在在外部if的else部分');  
    v_total : = v_num1 + v_num2;

     - -内部if
    if v_total < 0 then
      dbms_output.put_line( '现在在内部if的if部分');
       - -取绝对值
      v_total : = v_total *( - 1);
     end if;

   end if;

  dbms_output.put_line( 'v_total:='||v_total);
end;



- -条件控制之 case语句
- - CASE语句存在两种形式: simple CASE和搜索式 CASE 。 

- - Simple CASE语句具有如下结构: 
CASE  selector 
       WHEN expression_1 THEN    statement 1; 
       WHEN expression_2 THEN    statement 2; 
      ……
       WHEN expression_n THEN   statement n; 
       ELSE   statement n + 1; 
END CASE;

- -其中，selector是一个要比较值的变量
- -示例 1：使用 simple   CASE语句判断用户输入的数字
- -是否为偶数
declare
  v_num number : = &sv_num;
  v_num_flag number;
begin
   - -得到除 2余数
  v_num_flag : = mod(v_num, 2);

   - -判断余数是否为 0
   case v_num_flag
     when 0 then 
      dbms_output.put_line( '偶数');
     else
      dbms_output.put_line( '奇数');
   end case;

end;

- -搜索式 CASE语句
- -搜索式 CASE语句有个能够产生布尔
- -值( TRUE、 FALSE或者 NULL)的搜索条件。
- -当特定搜索条件的计算结果为 TRUE时，会执行与该条件相关的语句组合。搜索式 CASE语句的语法如下所示: 
CASE 
      WHEN search condition_1   THEN   statement 1; 
      WHEN search condition_2   THEN   statement 2; 
      WHEN search condition_n   THEN   statement n; 
     [ ELSE statement n + 1]; 
END CASE;


- -示例 2：使用搜索式 case改写上例
declare
  v_num number : = &sv_num;
   - -v_num_flag number;
begin
   - -得到除 2余数
   - -v_num_flag : = mod(v_num, 2);

   - -判断余数是否为 0
   case 
     when mod(v_num, 2) = 0 then 
      dbms_output.put_line( '偶数');
     else
      dbms_output.put_line( '奇数');
   end case;  
end;


- - - -示例 3：使用 simple   CASE表达式判断用户输入的数字
- -是否为偶数
declare
  v_num       number : = &sv_num;
  v_num_flag number;
  v_result   varchar2( 10);
begin
   - -得到除 2余数
  v_num_flag : = mod(v_num, 2);

  v_result : = case v_num_flag
                 when 0 then
                  '偶数'
                 else
                  '奇数'
               end;

  dbms_output.put_line(v_result);
end;


- -循环控制
- -循环是一种编程机制，允许一组指令被反复执行
- -PL / SQL有四种类型的循环：简单循环、WHILE循环、 FOR循环以及游标 FOR循环
- -简单循环具有 2种形式。第 1种是带exit子句的形式: 
LOOP 
statement 1; 
statement 2; 
……
IF condition THEN
     EXIT; 
END IF; 
END LOOP;
statement 3;

- -第 2种是带exit   when子句的形式:
LOOP 
statement 1; 
statement 2; 
……
EXIT   WHEN  condition; 
END LOOP; 
statement 3;


- -写循环有 3个要点：
- - 1、定义一个变量作为循环计数器变量，根据他的值
- -来决定是否结束循环
- - 2、在循环体中，一定要正确改变循环计数器变量的值
- - 3、在循环体中，一定要写上循环退出条件

declare
   - - 1、循环计数器变量
  v_counter binary_integer : = 0;
begin
   - -开始简单循环
  loop
     - - 2、改变计数器变量的值。递增
    v_counter : = v_counter + 1;

    dbms_output.put_line( 'v_counter='||v_counter);
     - - 3、循环退出条件
     - -if v_counter = 5 then
       - -exit;
     - - end if;
    exit when v_counter = 5 ;  
   end loop;

  dbms_output.put_line( 'Done');
end;


- -示例 2：使用EXIT WHEN条件来终止循环。给特定课程添加 4个班级

DECLARE
   v_course        course.course_no% type : = 430;
   v_instructor_id instructor.instructor_id% type : = 102;
    - -班级编号，同时兼做循环计数器变量
   v_sec_num        section.section_no% type : = 0;
begin
  loop
     - -班级号加 1  
    v_sec_num : = v_sec_num + 1;

     - -插入班级
     insert into section(section_id,course_no,section_no,
         instructor_id,created_by,
          created_date,modified_by,modified_date)
        values(section_id_seq.nextval,v_course,v_sec_num,
          v_instructor_id, user,sysdate, user,sysdate);   

   exit when v_sec_num = 4; 
   end loop;

   commit;

  dbms_output.put_line( '插入成功');
end;


- -while循环
- -WHILE循环的结构如下所示: 
WHILE  condition  LOOP 
   statement 1; 
   statement 2; 
  ……
   statement n; 
END LOOP; 

- -特点：
- - 1、先判断后执行。如果第一次condition的值就为 false，
- -那么该循环一次也不会执行。
- - 2、condition实际上就是循环的退出条件。循环的每一次
- -迭代，都会检查condition是否为 true。一旦返现为 false，
- -那么循环就自动结束。

- - 3、如果在while循环中使用了exit语句，那么意味着要提前
- -终止循环。不提倡使用。

- -示例 4：使用WHILE循环来计算整数 1到 10的总数
declare
  v_counter binary_integer : = 1; - -计数器变量
  v_sum binary_integer : = 0;
begin
  while v_counter < = 10 loop
     - -累加
    v_sum : = v_sum + v_counter;

     - -改变计数器变量的值
    v_counter : = v_counter + 1;
   end loop;

  dbms_output.put_line( 'v_sum='||v_sum);
end;


- -数值型 FOR循环之所以被称为数值型，原因在于它需要 1个整数作为自己的终止值。其结构如下:
FOR  loop_counter   IN [REVERSE] lower_limit. .upper_limit  LOOP 
   statement 1; 
   statement 2; 
  ……
   statement n; 
END LOOP;

- -特点：
- -这是最常用的、最简单的循环
- - 1、循环计数器变量loop_counter是系统自动定义的，
- -不需要你在 declare部分声明

- - 2、循环计数器变量loop_counter的取值从下限值lower_limit
- -开始取起，每次迭代自动加 1

- - 3、当循环计数器变量loop_counter的取值到达上限值upper_limit
- -后，循环自动结束

- - 4、循环计数器变量loop_counter的值在循环体中是只读的，
- -你不能手工的赋值。它的作用域范围只在循环体中有效

begin
   for v_counter in 1.. 10 loop
    dbms_output.put_line( 'v_counter='||v_counter);
   end loop;
end;


- -PLS - 00363: expression 'V_COUNTER' cannot be used as an assignment target
begin
   for v_counter in 1.. 10 loop
    v_counter : = v_counter + 1;
    dbms_output.put_line( 'v_counter='||v_counter);
   end loop;
end;

- -ORA - 06550: line 7, column 38:
- -PLS - 00201: identifier 'V_COUNTER' must be declared
begin
   for v_counter in 1.. 10 loop    
    dbms_output.put_line( 'v_counter='||v_counter);
   end loop;

  dbms_output.put_line( 'v_counter='||v_counter);
end;

- -在循环中使用REVERSE选项:
- -循环计数器变量的值从上限值取起，每次迭代自动减 1
- -示例 8：
BEGIN
    FOR v_counter   IN REVERSE   1.. 5  LOOP
      DBMS_OUTPUT.PUT_LINE ( 'v_counter = '||v_counter);
    END LOOP;
END;

- - CONTINUE语句
- -它是Oracle 11 g中新引入的PL / SQL特性
- - CONTINUE语句有两种形式: CONTINUE和 CONTINUE   WHEN

- - CONTINUE语句会导致循环终止当前迭代，并且当
- - CONTINUE的条件为 TRUE时，开始执行该循环的下一次迭代。

declare
  v_counter pls_integer : = 0;
begin
  loop
    v_counter : = v_counter + 1;
    dbms_output.put_line( 'before continue,v_counter='||v_counter);  

    if v_counter < 3 then
       continue;
     end if;

    dbms_output.put_line( 'after continue,v_counter='||v_counter);  

    exit when v_counter = 5;  
   end loop;
end;



- -嵌套循环。很常见
- -对于 3种类型的循环：简单循环、WHILE循环以及数
- -值型 FOR循环，任何一种循环都可以嵌套在其它循环中。
- -例如，简单循环可以嵌套在WHILE循环中，或者相反
declare
  v_counter1 number : = 0;
  v_counter2 number;
begin
   - -外循环
  while v_counter1 < 3 loop
    dbms_output.put_line( '外循环中，v_counter1='||v_counter1);

    v_counter2 : = 0;

     - -内循环
    loop
      dbms_output.put_line( '内循环中，v_counter2='||v_counter2);

      v_counter2 : = v_counter2 + 1;

      exit when v_counter2 > = 2;

     end loop;  

     - -外循环计数器加 1
    v_counter1 : = v_counter1 + 1;
   end loop;
end;

- -循环标签
- -在循环嵌套的情况下，可以使用标签来标识循环，
- -以增强代码的可读性。如下所示：
< <label_name > >
FOR  loop_counter   IN  lower_limit..upper_limit  LOOP 
    statement 1; 
    ……
    statement n; 
END LOOP label_name; 


begin
   < <outer_loop > >  
   for i in 1.. 3 loop
    dbms_output.put_line( 'i='||i);
     < <inner_loop > >
     for j in 1.. 2 loop
      dbms_output.put_line( 'j='||j);
     end loop inner_loop;
   end loop outer_loop;
end;


- -异常处理：
- -运行时错误叫异常。

- -PL / SQL有两种类型的异常：内置异常和用户定义异常

- - -为在程序中处理运行时错误，必须添加异常处理程序。
- -异常处理部分的语法结构如下: 
EXCEPTION 
      WHEN   exception_name    THEN 
         error -processing  statements;

- -注意：只能依靠异常的名字来处理异常，不能根据错误编号
- -来处理异常

declare
  v_num1 pls_integer : = &sv_num1;
  v_num2 pls_integer : = &sv_num2;
  v_result number;
begin
  v_result : = v_num1 / v_num2; - -可能出错

  dbms_output.put_line(v_result);
exception
   when zero_divide then
    dbms_output.put_line( '除数不能为0');
end;

- -内置异常  
- -在PL / SQL中，把一些常见的Oracle运行时错误预定义为
- -异常，这叫做内置异常。实际上就是给一些错误预先取了名字。

- -常见的：
- -no_data_found
- -too_many_rows
- -value_error
- - select into语句可能抛出以上 3种异常

- -异常处理的常见写法：
- - 1、如果在一个plsql块中需要对不同的异常做不同的处理，
- -那么就需要在 exception部分写多个不同的 when子句

- -根据用户提供的学生编号，检查学生的注册情况
- -分别使用三个学生ID: 102、 103和 319，来运行这个范例。
declare
  v_student_id student.student_id% type : = &sv_id;
   - -标记变量，值为 NO表示学生未注册，值为YES表示注册
  v_enrolled varchar2( 3) : = 'NO';
begin
   - -检查注册情况
   select 'YES'
     into v_enrolled
     from enrollment
     where student_id = v_student_id;

  dbms_output.put_line( '学生报了一个班');
exception
   when no_data_found then
    dbms_output.put_line( '学生没有注册');    
   when too_many_rows then
    dbms_output.put_line( '学生报了多个班');    
end;


- - 2、可以使用名字为others的异常来捕获所有特定的异常。
- -可以把它看做是所有特定异常的父异常。
- -以教师ID 100执行脚本
declare
  v_instructor_id instructor.instructor_id% type : = &sv_id;

  v_name varchar2( 20) ;
begin
   - -得到老师的姓名
   select first_name|| ' '||last_name
     into v_name
     from instructor
     where instructor_id = v_instructor_id;

  dbms_output.put_line(v_name);
exception
   when others then
    dbms_output.put_line( '有错误');
end;

- -使用others的弊端：
- -你不知道具体的异常到底是什么，因此错误消息
- -只能给的比较含混。

- - 3、可以在一个 when子句中抓多个异常，它的思想是：
- -对不同的异常做统一的处理
DECLARE
   v_exists          NUMBER( 1);
   v_total_students NUMBER( 1);
   v_zip             CHAR( 5): = '&sv_zip';
BEGIN
   - -检查给定的邮编是否合法
   select count( *)
     into v_exists
     from zipcode
     where zip = v_zip;

  if v_exists ! = 0 then - -邮编合法
     - -查找住在此处的学生的数量
     select count( *)
       into v_total_students
       from student
       where zip =v_zip;

    dbms_output.put_line( '学生的数量是：'||v_total_students);
   else - -邮编非法
    dbms_output.put_line( '邮编非法');
   end if;
exception
   when value_error or invalid_number then
    dbms_output.put_line( '有错误'); 
end;    

- -分别使用 07024、 00914和 12345测试

- -往STUDENT表中插入一条新记录，其中邮编的值是 07024
INSERT INTO student (student_id, salutation, first_name, last_name, zip,   registration_date, created_by, created_date, modified_by, 
   modified_date)
VALUES (STUDENT_ID_SEQ.NEXTVAL, 'Mr.', 'John', 'Smith', '07024',    SYSDATE, 'STUDENT', SYSDATE, 'STUDENT', SYSDATE); 
COMMIT;

- -使用相同的值 07024，再次运行这个脚本。