PL/SQL 编程语言
目标：
1.理解PL/SQL组成及体系结构
2.了解变量及其用法
3.使用控制语句进行编程
4.掌握游标的基本原理，理解游标的工作过程和分类
5.会创建动态sql语句
6.能够使用异常处理问题
1.PL/SQL
PL（过程语言）SQL（结构化查询语言）结合而成的编程语言，下面需要讲：变量和类型、控制语句、子程序和函数、对象类型和方法。
原理：用户发送PL/SQL块发给Oracle服务器，由服务器中的PL/SQL引擎进行判断，是过程语句就由过程语句执行器来执行，SQL语句就由SQL语句执行器来执行。
组成：分为三部分（声明部分、可执行部分、异常处理部分）
语法：
[declare -- Local variables here i integer;] begin
[Exception
hanlers
]
end;
2.声明部分：
这里能使用的数据类型包括：
标量类型：数字、字符、布尔、日期时间
LOB类型：BFILE、BLOB、CLOB、NCLOB
属性类型：%TYPE、%ROWTYPE
v_name varchar2(20):='张三'; v_sal number default 1000; c_rate constant number(4,3):=0.037; v_income number(10,3);
v_name teacher.tname%type;
注：常量为constant
对变量进行赋值：
begin select tname,tcal into v_name,v_sal from teacher where no=1004; dbms_output.put_line(v_name);//输出语句 end;
3.控制语句结构
条件控制：if、case
循环控制：loop、while、for
顺序控制：null、goto
//1.if语句 if <布尔表达式> then elsif <布尔表达式> then else end if; //2.case语句 case 条件表达式 when 结果1 then 语句段1 when 结果2 then 语句段2 else 语句段 end case;
//3.loop循环语句 loop 要执行的语句 exit when <条件语句> end loop; //4.while语句 while <布尔表达式> loop 要执行的语句; end loop; //5.for 循环 for 循环计数器 in [reverse] 下限..上限 loop 要执行语句; end loop;
4.游标的使用
什么是游标：游标是系统为用户开设的一个数据缓冲区，存放sql语句的执行结果，每个游标区都有一个名字。用户，可以通过游标逐一获取记录。
游标类型：静态游标（编译是知道其select语句的游标）、动态游标(运行时动态决定执行何种查询)
声明游标：cursor cursor_name is query_sql;
打开游标: open cursor_name;
提取游标: fetch cursor_name into variable_list;
(使用fetch语句实现对游标内容的读取，variable_list必须从游标提取的结果集类型相同)
关闭游标：close cursor_name;
游标的属性:
%fount：用于检验游标是否成功，一般在fetch语句前使用，当游标按照条件查询出一条记录时，返回true.
%isopen:用于判断游标是否处于打开状态，尝试打开一个已经打开或者已经关闭的游标，会报错
%notfound:当按照条件无法查询到记录时，会返回ture.
%rowcount:循环执行游标读取数据时，返回检索出的记录数据行数
游标实例代码：
-- Created on 2017/8/31 by WENLI declare -- Local variables here cursor cursor_teacher is select sal,tname from teacher; v_teacher_item cursor_teacher%rowtype; v_sal teacher.sal%type; v_name teacher.tname%type; c_tax1 constant number:=0.05; c_tax2 constant number:=0.1; c_tax3 constant number:=0.3; v_sal_tax number:=0; v_total_tax number:=0; begin -- Test statements here open cursor_teacher; loop fetch cursor_teacher into v_teacher_item; exit when cursor_teacher%notfound; v_sal := v_teacher_item.sal; v_name:=v_teacher_item.tname; v_sal_tax:=0; if v_sal>=5000 and v_sal<10000 then v_sal_tax:=(v_sal - 5000)*c_tax1; elsif v_sal>=10000 and v_sal<40000 then v_sal_tax:=(v_sal - 10000)*c_tax2+5000*c_tax1; elsif v_sal>=40000 then v_sal_tax:=(v_sal - 40000)*c_tax3+30000*c_tax1+5000*c_tax1; end if; v_total_tax:=v_total_tax+ v_sal_tax; dbms_output.put_line(v_name||':'||v_sal_tax); end loop; dbms_output.put_line('个税和：'||v_total_tax); close cursor_teacher; v_total_tax:=0; v_sal_tax:=0; for v_teacher_item2 in cursor_teacher loop v_sal := v_teacher_item2.sal; v_name:=v_teacher_item2.tname; v_sal_tax:=0; if v_sal>=5000 and v_sal<10000 then v_sal_tax:=(v_sal - 5000)*c_tax1; elsif v_sal>=10000 and v_sal<40000 then v_sal_tax:=(v_sal - 10000)*c_tax2+5000*c_tax1; elsif v_sal>=40000 then v_sal_tax:=(v_sal - 40000)*c_tax3+30000*c_tax1+5000*c_tax1; end if; v_total_tax:=v_total_tax+ v_sal_tax; dbms_output.put_line(v_name||':'||v_sal_tax); end loop; dbms_output.put_line('个税和：'||v_total_tax); end;
5.动态SQL
动态sql是说在PL/SQL程序执行时生成的sql语句，DDL语句命令和会话控制语句不能在PL/SQL例直接使用，但可以通过动态SQL执行
编译程序对动态sql不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行。
两类实现方式：
（1）本地动态sql ：execute immediate语句执行、通过游标实现
（2）DBMS_SQL程序包
a.execute immediate:
-- Created on 2017/8/31 by WENLI declare -- Local variables here v_sql varchar2(300); v_tname teacher.tname%type; v_gender teacher.gender%type; v_deptno teacher.deptno%type; begin -- Test statements here v_sql:='update teacher set sal=sal+100 where 1=1'; if v_tname is not null then v_sql := v_sql||' and tname='''||v_tname||''''; end if; if v_gender is not null then v_sql := v_sql||' and gender='''||v_gender||''''; end if; if v_deptno is not null then v_sql := v_sql||' and deptno='''||v_deptno||''''; end if; execute immediate v_sql; end;
动态sql的增加和Java后台拼接sql是一个道理。
传递参数的写法：
-- Created on 2017/8/31 by WENLI declare -- Local variables here v_sql varchar2(300); v_deptno teacher.deptno%type:=10; begin -- Test statements here v_sql:='update teacher set sal=sal+100 where 1=1 and deptno=:1'; execute immediate v_sql using v_deptno; end;
b.通过游标实现
-- Created on 2017/8/31 by WENLI declare -- Local variables here type type_cursor_teacher is ref cursor; --动态游标ref cursor cursor_teacher type_cursor_teacher; v_gender teacher.gender%type:='女'; v_tname teacher.tname%type; v_sal teacher.sal%type; begin -- Test statements here open cursor_teacher for 'select tname,sal from teacher where gender=:1 ' using v_gender; loop fetch cursor_teacher into v_tname,v_sal; exit when cursor_teacher%notfound; dbms_output.put_line(v_tname||v_sal); end loop; close cursor_teacher; end;
注：动态游标无法使用for循环进行操作，因为for循环会自动打开；而动态游标在使用是会使用open语句来打开。动态游标一般用在查询上。
c.DBMS_SQL程序包
（1）将要执行sql语句或一个语句块放到一个字符串变量中
（2）打开光标（游标）
（3）使用DBMS_SQL包的parse过程来分析该字符串
（4）使用DBMS_SQL包的bind_variable过程来绑定变量
（5）使用DBMS_SQL包的execute函数来执行语句
（6）关闭光标（游标）
使用案例：
-- Created on 2017/8/31 by WENLI declare v_gender teacher.gender%type:='女'; v_cursor number;--光标 v_sql varchar2(300); v_rows number; begin v_sql:='update teacher set sal=sal+100 where gender=:p_gender'; --打开光标 v_cursor:=dbms_sql.open_cursor; --解释语句 dbms_sql.parse(v_cursor,v_sql,dbms_sql.native); --绑定参数 dbms_sql.bind_variable(v_cursor,':p_gender',v_cursor); --执行语句 v_rows:=dbms_sql.execute(v_cursor); --关闭光标 dbms_sql.close_cursor(v_cursor); end;
6.异常处理
异常：预定义异常、用户定义异常
案例：
-- Created on 2017/8/31 by WENLI declare v_gender teacher.gender%type:='女'; v_cursor number;--光标 v_sql varchar2(300); v_rows number; e_outof_money exception;--定义异常 begin v_sql:='update teacher set sal=sal+100 where gender=:p_gender'; --打开光标 v_cursor:=dbms_sql.open_cursor; --解释语句 dbms_sql.parse(v_cursor,v_sql,dbms_sql.native); --绑定参数 dbms_sql.bind_variable(v_cursor,':p_gender',v_cursor); --执行语句 v_rows:=dbms_sql.execute(v_cursor); --关闭光标 dbms_sql.close_cursor(v_cursor); if v_rows>20 then raise e_outof_money;--抛出异常 end if; --异常处理 exception when e_outof_money then dbms_output.put_line('超出预算'); rollback; when others then null; end;
