Oracle PL／SQL语言基础_控制语句

PL/SQL(Procedural Language/SQL，过程语言/SQL):
    1.它是结合Oracle过程语言和结构化查询语言的一种扩展语言 
    2.PL/SQL支持多种数据类型，可以使用条件语句和循环语句等控制结构
    3.PL/SQL可用于创建存储过程、触发器和程序包，也可以用来处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑

PL/SQL的优点:
           1.支持SQL
           2.支持面向对象编程(OOP)
           3.更好的性能
           4.可移植性
           5.与SQL集成
           6.安全性

块的分类
          匿名块:
                  1.匿名块是出现在应用程序中的没有名字且不存储到数据库中的块
                  2.匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用
          命名块: 
                  1.命名块是一种带有标签的匿名块，标签为块指定了一个名称
          子程序:
                  1.子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以被多次执行

PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成：
        (1)DECLARE
               1.标记声明部分
               2.变量的声明，必须要在begin前面
               3.声明一些变量、常量、用户定义的数据类型及游标
                     name varchar(30); --声明时不设置值
                     name varchar(30):=‘Jack’;--声明带有默认值
                     name preson.name%type; --直接引用一个表的数据类型
         (2)BEGIN
               1.标记主程序体部分开始
               2.主程序体，在这里可以加入各种合法语句
         (3)EXCEPTION 
               1.标记异常处理部分开始
               2.异常处理程序，当程序中出现错误时执行这一部分
         (4)END 
               1.标记主程序体结束部分

声明常量的基本格式如下：
           <常量名> constant <数据类型> := <值>;
               例如:
                   Pass_Score constant INTEGER := 60 ;

*************************************************************
数据类型：
        （1）%TYPE数据类型
             1.当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能会在运行过程中出现PL/SQL运行错误 
             2.为了避免这种不必要的错误，可以使用%TYPE属性来定义变量 
             3.当使用%TYPE属性定义变量时，Oracle会自动地按照数据库表列或其他变量来确定新变量的类型和长度
        （2）%ROWTYPE数据类型
             1.如果一张表中包含较多的列，则可以使用%ROWTYPE来定义一个表示表中一行记录的变量 
例子：
declare
    id student.stu_id%type;
    name student.stu_name%type;
    v_class_id class.class_id%type;
    sex student.sex%type;
    emall student.email%type;
    address student.address%type;
    c_class_row class%rowtype;
begin
    select stu.stu_id,stu.stu_name,stu.class_id,stu.sex,stu.email,stu.address 
    into id,name, v_class_id,sex,emall,address
    from student stu  where stu.stu_id='&id';
    select * into c_class_row from class where class_id=v_class_id;
    dbms_output.put_line('查询学生id:'||id||'  姓名:'||name||'  性别:'||sex||'  邮箱:'||emall||'  地址:'||address);
    dbms_output.put_line('班级:'||v_class_id||'  班级编号:'||c_class_row .class_name||'  班主任:'||c_class_row .class_teacher);
    end;

        （3）%record数据类型
             1.类似高级语言中的结构
             2.首先需要定义记录类型和记录变量
             3.当引用记录成员时，必须将记录变量作为前缀
例子：
declare 
type emp_record_type is RECORD((--定义一个记录类型，包含员工信息
  ename emp.ename%type,
  sal emp.sal%type,
  comm emp.comm%type,
  total_sal sal%type);
v_emp_record emp_record_type;--声明记录类型变量
begin
  select ename,sal,nvl(comm,0),sal+nvl(comm,0) into v_emp_record
  from emp where empno=7369;
  dbms_output.put_line('员工姓名：'|| v_emp_record.ename);
  dbms_output.put_line('基本工资：'|| v_emp_record.sal);
  dbms_output.put_line('奖金：'|| v_emp_record.comm);
  dbms_output.put_line('实发工资：'|| v_emp_record.total_sal);
end;

        （4）TABLE数据类型
             1.TABLE(索引表)相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串
                    例子：
declare
  type dept_table_type is table of dept%rowtype index by binary_integer;
  v_dept_table  dept_table_type;
begin
   select * into v_dept_table(0) from dept where                          deptno=10;
   select * into v_dept_table(1) from dept where deptno=20;
   dbms_output.put_line('部门编号为'||v_dept_table(0).deptno||'的部门名称：'||v_dept_table(0).dname);
   dbms_output.put_line('部门编号为'||v_dept_table(1).deptno||'的部门名称：'||v_dept_table(1).dname);
end;
*********************************************************************流程控制语句：
   流程控制语句是所有过程性程序语言的关键
        PL/SQL的主要控制语句如下：
            1. if...then      elsif … then      end if;
                判断if正确则执行then，否则执行else(elsif为嵌套判断)
                注意elsif,里面少一下e.
               例子：
/*   
   输入员工编号，如果该员工原来没有奖金，则按照工资的10%发放 原来有奖金但不超过1000的，补到1000；其余的按照原来奖金基础再加上10%发放；*/
select * from emp
declare
  v_emp emp%rowtype;
begin
  v_emp.empno := '&no';
  select * into v_emp from emp em where em.empno = v_emp.empno;
  dbms_output.put_line('更新前奖金为' || nvl(v_emp.comm, 0));
  if v_emp.comm is null then
    update emp e1
       set e1.comm = v_emp.sal * 0.1
     where e1.empno = v_emp.empno;
  elsif v_emp.comm < 1000 then
    update emp e2 set e2.comm = 1000 where e2.empno = v_emp.empno;
  else
    update emp e3
       set e3.comm = v_emp.comm * 1.1
     where e3.empno = v_emp.empno;
  end if;
end;

            2. Case  var  when … then    when … then   end
                       有逻辑的从数值中做出选择
例子：
/*根据员工编号输出员工工资级别sal<2000 A级工资 ，sal>=2000 and sal<3000 B级工资，其余C级工资*/

declare
  v_emp emp%rowtype;
begin
  v_emp.empno := '&no';
  select * into v_emp from emp e1 where e1.empno = v_emp.empno;
  case
    when v_emp.sal < 2000 then
      dbms_output.put_line('A级工资');
    when v_emp.sal >= 2000 and v_emp.sal < 3000 then
      dbms_output.put_line('B级工资');
    else
      dbms_output.put_line('C级工资');
  end case;
end;

            3. Loop     exit    end loop
                 循环控制，用判断语句执行exit
例子：
        
declare
  v_val integer := 0;
  v_sum integer := 0;
begin
  loop
    v_sum := v_val + v_sum;
    dbms_output.put(v_val);
    if v_val = 4 then
      exit;
    end if;
    dbms_output.put('+');
    v_val := v_val + 1;
  end loop;
  dbms_output.put('=');
  dbms_output.put_line( v_sum);
end;
            4. Loop       exit when …        end loop
                 同上，当when为真时执行exit
例子：
declare
  v_val integer := 0;
  v_sum integer := 0;
begin
  loop
    v_sum := v_val + v_sum;
    dbms_output.put(v_val);
    exit when v_val = 4;
    dbms_output.put('+');
    v_val := v_val + 1;
  end loop;
  dbms_output.put('=');
  dbms_output.put_line(v_sum);
end;
            5. while..loop         end loop
                 当while为真时循环
例子：
declare
  type dept_table_type is table of dept%rowtype index by binary_integer;
  i number(1) := 0;
  v_dept_table dept_table_type;
begin
  v_dept_table(0).deptno := '50';
  v_dept_table(0).dname  := '研发一部';
  v_dept_table(0).loc    := '广州';
  v_dept_table(1).deptno := '60';
  v_dept_table(1).dname  := '研发二部';
  v_dept_table(1).loc    := '深圳';
  v_dept_table(2).deptno := '70';
  v_dept_table(2).dname  := '研发三部';
  v_dept_table(2).loc    := '上海';
  while i<=2 loop
    insert into dept
      (deptno, dname, loc)
    values
      (v_dept_table(i).deptno, v_dept_table(i).dname, v_dept_table     (i).loc);
    i := i + 1;
  end loop;
end;
            6. for...in...loop        end loop
                已知循环次数的循环
例子：
declare
  v_val integer := 0;
  v_sum integer := 0;
begin
  for v_val in 1 .. 4 loop
    v_sum := v_val + v_sum;
    dbms_output.put(v_val);
    if v_val < 4 then
      dbms_output.put('+');
    end if;
  end loop;
  dbms_output.put('=');
  dbms_output.put_line(v_sum);
end;
*******************************************************************
  异常：
1.CURSOR_ALREADY_OPEN：用户试图重新打开已经打开的游标时出现。在重新打开游标前必须先将其关闭
2.INVALID_CURSOR：在执行非法游标运算(如fetch一个尚未打开的游标)时出现
3.NO_DATA_FOUND：在表中不存在请求的行时出现
4.TOO_MANY_ROWS：在执行SELECT INTO语句后返回多行时出现
5.VALUE_ERROR：在产生大小限制错误时出现。如，变量中的列值超出变量的大小
6.ZERO_DIVIDE：以零做除数时出现
例子：
DECLARE
  varNum NUMBER;
BEGIN
  varNum := 'abc';
EXCEPTION
  WHEN VALUE_ERROR THEN
    dbms_output.put_line('VALUE_ERROR');
END;