第七章：%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，再次运行这个脚本。