Oracle的PL/SQL块

声明：本文章中所有采用的表均是Oracle范例用户Scott/tiger的表Emp,dept等。
一，PL/SQL块：

概念：PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言。

分类：  a，匿名：一次性使用，不可复用。
b，命名：包括（存储过程，触发器，函数等），存储在数据库内部，可重复使用。  

优点：  a，支持 SQL，在 PL/SQL 中可以使用：

1，数据操纵命令
2，事务控制命令
3，游标控制
4，SQL 函数和 SQL 运算符
5，支持面向对象编程 (OOP)
6，可移植性，可运行在任何操作系统和平台上的Oralce 数据库
7，更佳的性能，PL/SQL 经过编译执行

b，与 SQL 紧密集成，简化数据处理。

1，支持所有 SQL 数据类型
2，支持 NULL 值
3，支持 %TYPE 和 %ROWTYPE 属性类型（%type能自动获得表中某列的类型，%rowtype            能自动获得一整行数据的类型，相当于一个实体的类型）

c，安全性，可以通过存储过程限制用户对数据的访问

体系结构: 
    用户编写好pl/sql语句，发送给Oracle服务器，然后oracle服务器上的pl/sql引擎对其编译 执行，其中属于sql的语句，将其转发送给sql引擎处理，pl/sql的由pl/sql引擎自身处理，最后 把结果整合好返回给用户。

匿名pl/sql块：

a，语法结构：

declare
    声明变量/游标/异常
begin
    可执行的sql或pl/sql语句
exception
    异常处理语句
end

b,具体示例：

		declare
  		  v_empno emp.empno%type;
  		  v_empname emp.ename%type;
  		begin
     		  v_empno:=7788;
     		  select ename into v_empname from emp where empno=v_empno;
     		  dbms_output.put_line(v_empno||':'||v_empname);
  		exception
     		  when too_many_rows then
     		  dbms_output.put_line('many rows exception!');
     		  when others then
     		  dbms_output.put_line('other excetption!');
		end;

              其中：要在command window中设置set serverout on 才能顺利的把             dbms_output.put_line()中的内容打印出来；在异常处理的时候，可以先制定确切的异       常类型too_many_rows或其他类型的异常，此异常拦截不住的话，由others来最后处理。

变量：

a，PL/SQL 块中可以使用变量和常量
1，在声明部分声明，使用前必须先声明
2，声明时必须指定数据类型，每行声明一个标识符
3，在可执行部分的 SQL 语句和过程语句中使用
b，声明变量和常量的语法：
identifier [CONSTANT] datatype [NOT NULL]   [:= | DEFAULT expr];
示例：  c_rate CONSTANT NUMBER := 0.10;
c，给变量赋值有两种方法：
1，使用赋值语句 :=
2，使用 SELECT INTO 语句，
示例：

select ename into v_empname from emp where empno=v_empno;

异常：
概念：可以控制，通过捕获处理，可以是程序继续执行其他程序块。程序不死亡。
分类：
    1，预定义异常
    2，自定义异常：业务相关的异常（不符合业务规则的处理手段）
自定义异常的步骤：
    1，声明异常：declare v_myexception exception;
    2, 根据条件跑出异常：raise v_myexception ;
    3, 处理异常：EXCEPTION
  WHEN invalidCATEGORY THEN
    DBMS_OUTPUT.PUT_LINE('无法识别该类别');
        示例：

	    DECLARE 
  		invalidCATEGORY EXCEPTION;
  		category VARCHAR2(10);
	    BEGIN
  		category := '&Category';
  	    		IF category NOT IN ('附件','顶盖','备件') THEN
    			RAISE invalidCATEGORY;
  	    	ELSE
    			DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);
  		END IF;
	    EXCEPTION
 			WHEN invalidCATEGORY THEN
    			DBMS_OUTPUT.PUT_LINE('无法识别该类别');
	    END;

错误：
概念：系统爆发的严重错误，或抛出错误，系统程序死亡，崩溃。
抛出方法：RAISE_APPLICATION_ERROR(-20001, '未指定项费率');
注意，我们定义错误号码时要大于20000，而且抛出错误要放在处理异常中，             因为错误被系统认为异常，会被一场处理拦截。
   什么时候用：在出现业务上无法继续进行的情况，或程序中无法继续进行的情况，我们手动抛出                    错误来终止程序。
示例：

		DECLARE
  			rate itemfile.itemrate%TYPE;
  			rate_exception EXCEPTION;
		BEGIN
  			SELECT NVL(itemrate,0) INTO rate FROM itemfile 
  			WHERE  itemcode = 'i207';
  			IF rate = 0 THEN
    				RAISE rate_exception;
  			ELSE
   				DBMS_OUTPUT.PUT_LINE('项费率为：' || rate);
  			END IF;
		EXCEPTION
  			WHEN rate_exception THEN
    			RAISE_APPLICATION_ERROR(-20001, '未指定项费率');
		END;

控制结构：
条件控制：
IF 语句(if then end if;  if then elsif then else end if)
示例：

			DECLARE
    				icode VARCHAR2(4);
    				irate NUMBER;
			BEGIN
    				icode := 'i203';
  				SELECT itemrate INTO irate FROM itemfile 
  				WHERE  itemcode = icode;
  				IF irate > 200 THEN
    					UPDATE itemfile SET itemrate = itemrate - 200
    					WHERE itemcode = icode;
  				ELSE
    					UPDATE itemfile SET itemrate = itemrate - 50
    					WHERE itemcode = icode;
  				END IF;
  					DBMS_OUTPUT.PUT_LINE('itemrate='|| irate);
			END;

CASE 语句：
示例：

				BEGIN
    				    CASE ‘&grade’
      					WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’优异’);
      					WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (优秀’);
      					WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’);
      					WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’);
      					WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’较差’);
      					ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’);
    					END CASE;
				END;

循环控制
LOOP 循环：
示例：

			declare
				v_count number:=1;
			begin
			loop
			        v_count:=v_count+1;
				dbms_output.put_line(v_count);
				exit when v_count>10;
			end loop;
			end;

WHILE 循环
示例：
WHILE 循环条件 LOOP
  要循环的代码
END LOOP;

FOR 循环
示例：
FOR 计数器 IN [REVERSE] 起始值..结束值
LOOP
  要循环的代码
END LOOP;

顺序控制
GOTO 语句
                     一般不用，用的话如下例：

               			DECLARE
  					qtyhand itemfile.qty_hand%type;
  					relevel itemfile.re_level%type;
				BEGIN
  					SELECT qty_hand,re_level INTO qtyhand,relevel
  					FROM itemfile WHERE itemcode = 'i201';
  					IF qtyhand < relevel THEN
    						GOTO updation;
  					ELSE
    						GOTO quit;
  					END IF;
  					<<updation>>
  					UPDATE itemfile SET qty_hand = qty_hand + re_level
  					WHERE itemcode = 'i201';
  					<<quit>>
  					NULL;
				END;

NULL 语句：为凑成语法而用，如上例。
动态绑定变量:
SQL语句执行过程：首先，系统将SQL语句进行字符串解析，直到在系统内部组成一条完整的可执                       行的sql执行计划时候，再通过执行该计划返回结果，其中，两条SQL语句的字符                      上有一点点差异，都认为是不同的执行计划，这个时候，还是用绑定变量的机制                       能解决同一SQL多执行计划的问题。
用法和适用场合：

			declare
  				empRec emp%rowtype;
  				sqlStr varchar2(200);
			begin
  				--DDL动态SQL语句--
  				execute immediate 'create table test(id number(4))';
  
  				--带绑定变量的动态SQL语句--
  				sqlStr:='select * from emp where empno=:id';
  				execute immediate sqlStr into empRec using 7934;
  				dbms_output.put_line(empRec.ename);

  				--字符串联接生成的动态SQL语句--
  				sqlStr:='select * from emp'||' '||'where empno=7788';
  				execute immediate sqlStr into empRec;
  				dbms_output.put_line(empRec.ename);
			end;

上机实验：
      a，依据薪水分级：

declare
v_empno_exception exception;
v_empsal emp.sal%type;
v_empno emp.empno%type;
sqlstr varchar2(200);
begin
v_empno:=&e;
sqlstr:='select sal  from emp where empno=:eno';
execute immediate sqlstr into v_empsal using v_empno;
case
when v_empsal<=1000 then dbms_output.put_line('d');
when v_empsal<=2000 then dbms_output.put_line('c');
when v_empsal<=3000 then dbms_output.put_line('b');
else dbms_output.put_line('A');
end case;

exception
  when no_data_found then 
  dbms_output.put_line('此用户不存在！');
end;

      b，编写自定义异常，余额不足问题

declare
v_lessBalance_exception exception;
v_balance number:=1000;
v_cashNumber number;
begin
v_cashNumber:=&cno;
if v_cashNumber>v_balance-5 then
   raise v_lessBalance_exception;
else
   dbms_output.put_line('余额为'||(v_balance-v_cashNumber));
end if;

exception
    when v_lessBalance_exception then 
    dbms_output.put_line('余额不足！');
end;