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

一、PL/SQL概述
1.PL/SQL(Procedural Language/SQL，过程语言/SQL)
  它是结合Oracle过程语言和结构化查询语言的一种扩展语言
  PL/SQL支持多种数据类型，可以使用条件语句和循环语句等控制结构
  PL/SQL可用于创建存储过程、触发器和程序包，也可以用来处理业务规则、  数据库事件或给SQL命令的执行添加程序逻辑
2.优点
  支持SQL
  支持面向对象编程(OOP)
  更好的性能
  可移植性
  与SQL集成
  安全性
二、PL/SQL基本结构
1.PL/SQL语言是程序化程序设计语言。
  块(Block)是PL/SQL程序中最基本的结构，所有PL/SQL程序都是由块组成。
  块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）
2.块的分类
 匿名块
    匿名块是出现在应用程序中的没有名字且不存储到数据库中的块
    匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能    被其他程序调用
命名块
    命名块是一种带有标签的匿名块，标签为块指定了一个名称
子程序
    子程序是存储在数据库中的过程（procedure）、函数（function），生      成之后可以被多次执行
3.PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成：
  DECLARE
  标记声明部分
  变量的声明，必须要在begin前面
  声明一些变量、常量、用户定义的数据类型及游标
  name varchar(30); --声明时不设置值
  name varchar(30):=‘Jack’;--声明带有默认值
  name preson.name%type; --直接引用一个表的数据类型
  BEGIN
  标记主程序体部分开始
  主程序体，在这里可以加入各种合法语句
  EXCEPTION 
  标记异常处理部分开始
  异常处理程序，当程序中出现错误时执行这一部分
  END 
  标记主程序体结束部分
  基本语法：
  declare
      说明部分    （变量说明，例外说明 〕
  begin
      语句序列   （DML语句〕… 
  exception
      例外处理语句   
  End;
三、PL/SQL字符集
1.PL/SQL语言有效字符包括以下三类
  所有大写和小写英文字母
  0~9的阿拉伯数字
  操作符，包括(、)、+、-、*、/、<、>、!、=、@、%等
2.PL/SQL标识符
  标识符的最大长度为30个字符，不区分大小写，但建议在标识符中适当使用  大小写，以增加程序的可读性。

四、PL/SQL变量和常量
1.在PL/SQL程序运行时，需要定义一些变量来存放一些数据。
   常量和变量在使用前必须声明，可以使用DECLARE对变量进行声明，语法如   下：

        DECLARE
	    <变量名> <变量类型>:=默认值;
		….
   在DECLARE块中可以同时声明多个常量和变量。声明普通常量或变量是需要   说明以下信息：
     常量或变量的名称
     常量或变量的数据类型
2.说明变量  （char, varchar2, date, number, boolean, long)
    varl             char(15);   说明变量名、数据类型和长度后用分号结束说明语
    married      boolean :=true; 
    psal            number(7,2);
    my_name   emp.ename%type; 引用型变量，即my_name的类型与emp表中ename列的类型一样
    emp_rec     emp%rowtype;   记录变量集
    记录变量分量的引用：
       emp_rec.ename:='ADAMS';
3.声明常量
  声明常量的基本格式如下：
    <常量名> constant <数据类型> := <值>;
        := 为赋值语句
        关键字constant表示声明的是常量。常量一旦定义，在以后的使用中其值不再改变。
        一些固定的大小为了防止有人改变，最好定义成常量。
  例如  Pass_Score constant INTEGER := 60 ;
4.声明变量
  声明变量的基本格式如下：
    <变量名> <数据类型>[(宽度) := <初始值>];
        变量声明是没有关键字，但要指定数据类型，宽度和初始值可以定义也可以不定义。
    例如
      Address VARCHAR2(30) := ‘地址未知’;
   PL/SQL对一个未初始化的变量，将被默认赋值为NULL
    例如
      Address VARCHAR2(30);
5.PLSQL案例

 PLSQL案例----1
   --打开输出
  et severoutput on
    --声明一个变量并输出
  declare
   	name varchar(10):=‘HelloWorld';
  begin
     dbms_output.put_line(name);
  end;

 PLSQL案例----2
  SET SERVEROUTPUT ON;
  DECLARE
  Pass_Score constant INTEGER:=60;
  Address VARCHAR2(30):='北京海淀区';
  BEGIN
	DBMS_OUTPUT.PUT_LINE(Pass_Score);
	DBMS_OUTPUT.PUT_LINE(Address);
  END;

  使用SET SERVEROUTPUT ON命令设置环境变量SERVEROUTPUT为打开状态，从而使PL/SQL程序能    够在SQL*Plus中输出结果
  使用函数DBMS_OUTPUT.PUT_LINE()可以输出参数的值
  LSQL案例----3
    匿名块示例：
      创建一个匿名程序块，该程序块用于接收用户输入的员工编号，查询并输出该员工的姓         名，还  用于处理用户输入的员工编号不存在的异常 

     set serveroutput on   ---set serveroutput on设置输出、显示环境变量

     set verify off        ---set verify off :  设置是否进行数据校验。


     DECLARE                                ---定义块变量
        v_ename varchar2(50);   
     BEGIN                                  ---执行业务逻辑
	select ename into v_ename from emp where empno=&eno;	 
	dbms_output.put_line('您要查找的姓名是：'|| v_ename);       
     EXCEPTION                              ---异常处理部分
	WHEN NO_DATA_FOUND THEN
	dbms_output.put_line('输入的员工编号不存在！');
     END;

五、数据类型
  1.%TYPE数据类型
    当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能会在运行    过程中出现PL/SQL运行错误 
    为了避免这种不必要的错误，可以使用%TYPE属性来定义变量 
    当使用%TYPE属性定义变量时，Oracle会自动地按照数据库表列或其他变量来确定新变量的    类型和长度
  2.%ROWTYPE数据类型
   如果一张表中包含较多的列，则可以使用%ROWTYPE来定义一个表示表中一行记录的变量 
   示例：
     根据输入的员工编号查找该员工信息，及其所在部门的信息。
	declare
            v_ename emp.ename%type;     --定义和dept相同的行类型
            v_deptno dept.deptno%type;  --定义和dept相同的行类型
            v_dept_row dept%rowtype;
        begin
            select ename ,deptno into v_ename,v_deptno from emp where empno=&eno;
            select * into v_dept_row from dept where deptno=v_deptno;
            dbms_output.put_line('您要查找的员工是：'||v_ename||'，所在部门信为：');
            dbms_output.put_line('部门编号：'||v_deptno||'  部门名称：'||v_dept_row.dname||'   所在地：'||v_dept_row.loc);
        end; 
 3.%record数据类型
   类似高级语言中的结构
   首先需要定义记录类型和记录变量
   当引用记录成员时，必须将记录变量作为前缀
  示例：
   根据输入的员工编号输出该员工的姓名、基本工资、奖金及实发工资。
   Type  record_name is record
    (
      V1  data_type1  [not null] [:=default_value],
      V2  data_type2  [not null] [:=default_value],
      Vn  data_typen  [not null] [:=default_value]
    );
   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数据类型
  TABLE(索引表)相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串
  示例：
  将部门编号是10和20的部门信息存储到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||'   所在地：'||    v_dept_table(0).loc);
    dbms_output.PUT_LINE('编号：'||v_dept_table(1).deptno||'   名称：'||v_dept_table(1).dname||'   所在地：'||    v_dept_table(1).loc);
    end;

六、PL/SQL程序的执行部分
  1.PL/SQL程序的执行部分包括
     赋值语句
     流程控制语句
     SQL语句
     游标语句
  2.接收用户的输入赋值
     在declare之前，可以通过
        accept someVar prompt ‘提示信息’;要求用户输入
        在后面的代码中，可以通过地址引用&someVal接收这值
    --接收用户的输入
         set serveroutput on
    --要求用户输入一个数字,如果不输入后面也可以直接引用，只是一个提示而已
	accept age prompt 'plz enter a number:';
	declare
		nn number;
	begin 
		nn := &age;
                dbms_output.put_line('你输入的信息是'||nn);
	end;
   3.从查询中结果中赋值
       用into关键字可以将查询结果的值，设置给变量：
       set serveroutput on
       declare 
         id number(4);
         nm varchar(30);
       begin
         select  empno,ename into id,nm from emp where empno=7369;
         dbms_output.put_line('编号'||id||'名称'||nm);
       --以下是异常处理 -–可选
       exception
         when NO_DATA_FOUND then
          dbms_output.put_line('没有你要查询的数据');
         when others then
          dbms_output.put_line('其他错误');
       end;
七、流程控制语句
  1.流程控制语句是所有过程性程序语言的关键
   PL/SQL的主要控制语句如下：
   if...then      elsif … then      end if;
   判断if正确则执行then，否则执行else(elsif为嵌套判断)
   注意elsif,里面少一下e.
   Case  var  when … then    when … then   end
   有逻辑的从数值中做出选择
   Loop     exit    end loop
   循环控制，用判断语句执行exit
   Loop       exit when …        end loop
   同上，当when为真时执行exit
   while..loop         end loop
   当while为真时循环
   for...in...loop        end loop
   已知循环次数的循环
   if语句
   条件控制——if
	IF 条件表达式 THEN
           语句段
        END IF;
        ---------------------------
        IF 条件表达式 THEN
	  语句段1
        ELSE
	  语句段2
        END IF;
        ----------------------------
        IF 条件表达式1 THEN
          语句段1
        ELSIF 条件表达式2 THEN
           语句段2
        ELSIF 条件表达式3  THEN
           语句段3
         ......
        ELSIF 条件表达式n
          语句段n
        END IF;
   注意：是elsif 不是elseif，也不是else  if

   if案例
   员工奖金发放：
   输入员工编号，如果该员工
   原来没有奖金，则按照工资的10%发放 
   原来有奖金但不超过1000的，补到1000；
   其余的按照原来奖金基础再加上10%发放；
   declare
	v_emp emp%rowtype;
   begin
  	v_emp.empno:=&no;
  	select * into v_emp from emp where empno=v_emp.empno;
  	dbms_output.PUT_LINE('更新前的奖金'||nvl(v_emp.comm,0));
  	if v_emp.comm is null  then
    		update emp set comm=v_emp.sal*0.1 where empno=v_emp.empno;
  	elsIf v_emp.comm<1000 then
    		update emp set comm=1000 where empno=v_emp.empno;
  	else
    		update emp set comm=comm+comm*0.1 where empno=v_emp.empno;  
  	end if;    
   end;
  2.在查询是使用简单的case
    在查询中使用case语句：
    SQL> select (case id when 2 then '222' else '33' end) from t6;
    SQL> --上面的示例等于
    SQL> select (case when id=2 then '2222' else '333' end) from t6;
    条件控制——case
	CASE
        WHEN 条件表达式1 THEN
           语句段1;
        WHEN 条件表达式2 THEN
           语句段2;
        ......
        ELSE
           语句段n;
        END CASE;
        case应用案例
        case应用案例
    case应用案例
    根据员工编号输出员工工资级别
        sal<2000   			A级工资
        sal>=2000 and sal<3000   	B级工资
        其余   			        C级工资
     declare  	
  	v_sal emp.SAL%type;
     begin
  	select sal into v_sal from emp where empno=&empno;
  	case 
 		when v_sal<2000 then dbms_output.put_line('A级工资');
  		when v_sal>=2000 and v_sal<3000 
				then	dbms_output.put_line('B级工资');
  		else dbms_output.put_line('C级工资');
  	end case;
     exception
  	when no_data_found then
   		dbms_output.put_line('员工编号不存在');
     end;

   3.循环语句LOOP..EXIT..ENDLOOP
   此语句的功能是重复执行循环体中的程序块，直到执行EXIT语句，则退出循环。
     LOOP...EXIT...END语句的语法结构如下
     LOOP
       <程序块 1>
     IF <条件表达式> THEN
       EXIT
     END IF
       <程序块 2>
     END LOOP;

    计算1~4累加
    SET ServerOutPut ON;
    DECLARE
      varNum INTEGER := 1;
      varSum INTEGER := 0;
    BEGIN
    LOOP
      varSum := varSum + varNum;
      dbms_output.put_line(varNum);
    IF varNum = 4 THEN
      EXIT;
    END IF;
      dbms_output.put_line('+');
      varNum := varNum + 1;
    END LOOP;
      dbms_output.put_line('=');
      dbms_output.put_line(varSum);
    END;


    基本循环
     定义一个dept类型的表结构
     手工添加3条数据
     然后用循环将其数据添加到dept表中。


     LOOP
        语句段;
     EXIT [WHEN 条件表达式]
     END LOOP;
    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:='北京';
  	loop
   		 if i>2 then exit; end if;
    		insert into dept values
			( v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
    		i:=i+1;
  	end loop;
     end;
  3.EXIT WHEN
     此循环语句的功能是重复执行循环体中的程序块，直到满足EXIT WHEN后面的判断语句，则退出循环。
      LOOP...EXIT WHEN...END语句的语法结构如下：
      LOOP
        <程序块 1>
      EXIT WHEN <条件表达式>
         <程序块 2>
      END LOOP;
     重新实现1~4累加
         SET ServerOutPut ON;
         DECLARE
            varNum INTEGER := 1;
            varSum INTEGER := 0;
         BEGIN
         LOOP
            varSum := varSum + varNum;
            dbms_output.put_line(varNum);
         EXIT WHEN varNum = 4;
            dbms_output.put_line('+');
            varNum := varNum + 1;
         END LOOP;
            dbms_output.put_line('=');
            dbms_output.put_line(varSum);
         END;
   4.WHILE..LOOP..END LOOP
   此语句的功能是当WHILE后面的语句条件成立时，重复执行循环体中的程序块。
     WHILE...LOOP...END LOOP语句语法结构如下：
     WHILE <条件表达式>  LOOP
       <程序块>
     END LOOP;

    再次实现1~4累加
    SET ServerOutPut ON;
    DECLARE
      varNum INTEGER := 1;
      varSum INTEGER := 0;
    BEGIN
    WHILE varNum <= 4  LOOP
      varSum := varSum + varNum;
       dbms_output.put_line(varNum);
    IF varNum < 4 THEN
      dbms_output.put_line('+');
    END IF;
    varNum := varNum + 1;
    END LOOP;
     dbms_output.put_line('=');
     dbms_output.put_line(varSum);
    END;
    while循环
    定义一个dept类型的表结构
    手工添加3条数据
    然后用循环将其数据添加到dept表中。
    WHILE 条件表达式 LOOP
      语句段;
    END LOOP;
   declare
    type dept_type is table of dept%rowtype
    index by binary_integer;
    i number := 0;
   v_dept_type dept_type;
   begin
    v_dept_type(0).deptno := 50;
    v_dept_type(0).dname := '研发1部';
    v_dept_type(0).loc := '北京';
    v_dept_type(1).deptno := 60;
    v_dept_type(1).dname := '研发2部';
    v_dept_type(1).loc := '上海';
    v_dept_type(2).deptno := 70;
    v_dept_type(2).dname := '研发1部';
    v_dept_type(2).loc := '广州';
   while i <= 2 loop
     insert into dept
       (deptno, dname, loc)
     values
       (v_dept_type(i).deptno, v_dept_type(i).dname, v_dept_type(i).loc);
       i := i + 1;
   end loop;
  end;
    
  此语句定义一个循环变量，并指定循环变量的初始值和终止值。每循环一次循环变量自动加1.
  FOR...IN...LOOP...END LOOP语句的语法如下
  FOR <循环变量> IN <初始值>..<终止值> LOOP
     <程序块>
  END LOOP;
  再次实现1~4累加
    SET ServerOutPut ON;
    DECLARE
      varNum INTEGER := 1;
      varSum INTEGER := 0;
    BEGIN
    FOR varNum IN 1..4  LOOP
      varSum := varSum + varNum;
      dbms_output.put_line(varNum);
    IF varNum < 4 THEN
      dbms_output.put_line('+');
    END IF;
    END LOOP;
      dbms_output.put_line('=');
      dbms_output.put_line(varSum);
    END;
  for循环
    默认情况下每次循环，控制变量会自动增加1；
    如果指定了reverse选项，则每次循环变量自动减少1。
    FOR 循环变量 in [REVERSE] 初值表达式..终值表达式 LOOP
	语句段;
    END LOOP;

  declare
   type dept_type is table of dept%rowtype
   index by binary_integer;
    i number := 0;
   v_dept_type dept_type;
  begin
   v_dept_type(0).deptno := 50;
   v_dept_type(0).dname := '研发1部';
   v_dept_type(0).loc := '北京';
   v_dept_type(1).deptno := 60;
   v_dept_type(1).dname := '研发2部';
   v_dept_type(1).loc := '上海';
   v_dept_type(2).deptno := 70;
   v_dept_type(2).dname := '研发1部';
   v_dept_type(2).loc := '广州';
   for i in 0..v_dept_type.count - 1 loop
     insert into dept
       (deptno, dname, loc)
     values
       (v_dept_type(i).deptno, v_dept_type(i).dname, v_dept_type(i).loc);
   end loop;
  end;
 
八、异常处理
PL/SQL程序在运行过程中，可能会出现错误或异常现象
例如：无法建立到Oracle的连接或用0做除数。好的程序应该对可能发生的异常情况进行处理，异常处理代码在EXCEPTION块中实现
可以使用WHEN语句来定义异常。WHEN语句的使用方法如下：
EXCEPTION
       WHEN <异常情况名> THEN
             <异常处理代码>
      WHEN <异常情况名> THEN
          <异常处理代码>
       ...
      WHEN OTHERS THEN
          <异常处理代码>
预定义异常种类

http://blog.itpub.net/22678696/viewspace-1116535/

例子：

向一个NUMBER类型的变量赋值字符串时，导致异常的发生
SET ServerOutPut ON;
DECLARE
  varNum NUMBER;
BEGIN
  varNum := 'abc';
EXCEPTION
  WHEN VALUE_ERROR THEN
    dbms_output.put_line('VALUE_ERROR');
END;

使用SQLCODE , SQLERRM输出错误信息：
set serveroutput on;
declare 
  j integer:=0;
begin
  j:='Jack';
  --如果出错，直接去异常处执行，以下行不会输出
  dbms_output.put_line('Value is setted');
  exception
    when others then
      --在异常中默认使用sqlerrm输出信息
      dbms_output.put_line(SQLCODE||'Other errors    '||sqlerrm);
end;
预定义异常
  begin
  	insert into dept values(10,test',test');
  exception
  	when dup_val_on_index then
    		dbms_output.put_line('违反唯一约束！');
    	when others then
    		dbms_output.put_line('发生其他错误！');
  end;