PL/SQL基础与使用

一使用PL/SQL打印hello world

注意：dbms_output.put_line是oracle为我们提供的程序包，oracle中提供了很多程序包，可以自己去查相关资料

可以sqlplus中使用：desc dbms_output查看程序包的结构

二、什么是PL/SQL程序

2.1PL/SQL（procedure Language/SQL）

PL/SQL是Oracle对sql语言的过程话扩展
只在sql命令语言中增加 过程化处理（如分支，循环），使用SQL语言具有过程处理能力
PL/SQL通过将SQL语言的数据操纵能力与过程语言的数据处理能力结合起来使得SQL具有面向过程语言的扩张

2.2不同的数据库的SQL扩展

oracle:PL/SQL
DB2:SQL/PL
SQL Server:Transac-SQL（T-SQL）

三、PL/SQL的程序结构

3.1完整的PL/SQL结构块

注意：下边是PL/SQL块，与存储过程存储函数还是有一定的区别，PL/SQL块，是存储过程和存储函数的基础。

declare
说明部分（变量说明，光标申明，例外说明）
begin
语句序列（DML语句）
exception
例外处理语句
end;

注意：declare 和exception如果没有可以不写是非必需的,但是必需有 begin 和end

3.2说明部分：

3.2.1定义基本变量

类型：char ,varchar2,date,number,boolean,long
举例：
var1 char(15);
married boolean :=ture; --声明的同时初始化 PL/SQL中赋值使用 :=
pasl number(7,2);

案例：

3.2.2两种特殊变量 引用类型变量和记录类型 变量

引用型变量主要是用于记录某一 列的类型，记录型变量相当于记录了一行的变量类型，下边我们看一下两者的使用

引用型变量：
my_name emp.ename%type
表示变量 my_name 的类型为表emp中列ename的类型一样
举例：

declare--定义引用型变量pename emp.ename%type;psal  emp.sal%type;begin--得到员工7839的姓名和薪水select ename,sal into pename,psal from emp where empno=7839;dbms_output.put_line('员工姓名:'||pename);dbms_output.put_line('员工薪水:'||psal);end;

注意：1.PLSQL中赋值有两种方式，第一种为 :=  第二种使用into 关键字注意，into后边的变量
需要与前边的查询结果顺序一致
      2.select ename,sal into pename,psal from emp where empno=7839; 后边的赋值不用:= 与变量的赋值有区别

记录型变量：
emp_rec emp%rowtype
emp_rec表示记录了表emp一行的类型，可以认为是一个数组记录了一行中的每一列
举例：

declare--定义记录型变量emp_rec emp%rowtype;begin--得到员工7839的姓名和薪水select * into emp_rec from emp where empno=7839;dbms_output.put_line('员工姓名:'||emp_rec.ename);dbms_output.put_line('员工薪水:'||emp_rec.sal);end;

3.3程序中的语句体

PL/SQL和其他的过程语言一样，具有常见的循环分支结构,下边我们将介绍这些常见的循环分支结构在PL/SQL中的使用

3.3.1if语句

语法：
1.

if 条件 then 语句1;end if;

2. 

if 条件 then语句1;else 语句2;end if;

3.下边这种形式主要需要注意elsif的写法，不是elseif

if 条件 then语句1;elsif 条件 then语句2;...elsif 语句..;end if;

案例：接受一个用户从键盘输入的数字，判断数字

/*判断用户从键盘输入的数字1.接受键盘输入2.如何使用if语句*/set serveroutput on;--接受一个键盘输入--num是一个地址只，在该地址中保存输入的值accept num prompt '请输入一个数字'；declare--定义个变量保存键盘输入的数字pnum number := #begin--执行条件判断语句if pnum = 0 thendbms_output.put_line('你输入的数字是0');elsif pnum = 1 thendbms_output.put_line('你输入的数字是1');elsedbms_output.put_line('其他数字');end if;end;

3.3.2 循环语句

1.while循环

while total <= 2500 loop
...
total :=total +10;
end loop;

案例：while打印1。。10

declare--定义循环变量pnum number := 1;beginwhile pnum <=10 loop--执行循环体dbms_output.put_line(pnum);pnum := pnum +1; end loop;end;

注意：pl/sql中不能使用++、 +=、 --等运算

2.loop循环

loop 
exit [when 条件]；
....
end loop;

案例：loop打印1。。10

declare--定义循环变量pnum number := 1;beginloop--退出条件exit when pnum > 10;--没有达到退出条件打印dbms_output.put_line(pnum);pnum := pnum +1;end loop; end;

3.for循环
for i in 1..10 loop
语句序列
end loop;
案例：使用for循环打印1。。10

declare--定义循环变量pnum number := 1;begin-- 1..10表示每次取出一个给pnum变量for pnum in 1..10 loop  dbms_output.put_line(pnum);end loop; end;

3.2.3 CASE语句

case语句应用《精通Oracle10g SQL和PL/SQL》 原文

3.4光标的引入

光标就是一个结果集合Result Set
语法：

CURSOR 光标名称[(参数名 数据类型[参数名 数据类型]...)]
is select 语句；

3.4.1光标的属性

%found光标可以取到数据 

%notfound 光标不能取到数据
%isopen 判断光标是否打开
%rowcount 影响行数，比如说我们光标中总共有100条记录，我取走10条记录，那么%rowcount应该是10而不是100
光标的限制 

默认情况下 oracle数据库只允许在同一个回话中打开300个光标
这些信息我们可以在数据库的管理员用户中查询到

通过管理员用户可以执行如下命令

show parameter abcd  --语句相当于做了模糊查询show parameter cursor --查询光标的相关参数cursor_sharing                       stringEXACTcursor_space_for_time                booleanFALSEopen_cursors                         integer300session_cached_cursors               integer20

修改默认可以打开的光标数

通过管理员用户修改默认光标的打开数量：
alter system set open_cursors=4000 scope = both;
scope的取值：both，memory(表示只更改当前实例，不更改参数文件),spfile(只更改参数文件，不更改当前实例，需要重新启动数据生效)

3.4.2不带参数的光标

案例：查询并打印员工的薪水

/*1.光标的属性 4个 都是 %开头%found-取到记录  %notfound-没有取到记录%isopen 判断光标是否打开%rowcount 影响行数，比如说我们光标中总共有100条记录，我取走10条记录，那么%rowcount应该是10而不是100*/declare--定义一个光标,可以不带参数cursor cemp is select ename,sal from emp;--定义变量pename emp.ename%type;psal emp.sal%type;begin--打开光标 --关闭使用之前需要打开，使用完了需要关闭open cemp;--判断光标是否打开if cemp%isopen then dbms_output.put_line('光标打开了');--循环从光标结果集合中取出数据loop--取一条记录,使用fetch从光标中取出一条记录，之后光标会向后移动fetch cemp into peanme,psal;--循环退出没有取到记录的时候exit when cemp%notfound;--否则打印记录dbms_output.put_line(pename||'薪水是'||psal);--打印影响行数dbms_output.put_line(cemp%rowcount);end loop;elsedbms_output.put_line('光标没有打开');--关闭光标close cemp;end;

3.4.3带参数的光标

定义的时候
与不带参数的光标的区别就是定义的时候，可以设定形参和实参
打开的时候
带参数的光标打开的时候需要传递实参

declare--定义带参数的光标  括号中dno作为形参                :=dno 作为实参cursor cemp(dno number) is select ename from emp where deptno:=dno;--定义变量pename emp.ename%type;begin--打开光标的时候需要传递一个实参  比如说需要查询10号部门的员工姓名open cemp(10)；loop--取出每个员工的姓名fetch cemp into pename;--没有取到记录退出循环exit where cemp%notfoun;--否则打印员工姓名dbms_output.put_line(pename);end loop;--关闭光标close cemp;end;

3.5例外，异常处理

oracle中有两种例外
1.internally defined (系统定义好的例外)
比如：  

No_data_found 没有找到数据
Too_many_rows （select..into 语句匹配多个行）
Zero_Divide(被除零)
Value_error(算术或转换错误)
Timeout_on_resource(在等待资源时发生超时)
比如说在分布式数据库中  一个数据库在北京    一个数据库在上海  北京的数据库想访问上海的数据库网络断了，很久都没有等到上海的数据
给我返回结果。就会发生这种情况
....
2.user defined（自定义例外）

3.5.1系统例外

--系统例外 没有返现数据 no_data_found

declarepname emp.ename%type;begin--查询员工工号是1234的员工的姓名(假如不存在该员工就会找不到数据)select ename into pename from emp where empno =1234;exceptionwhen no_data_found thendbms_output.put_line('没有找到该员工');when others then --除了上边这个数据库中情况，都由这里捕获，避免将例外抛给数据库dbms_output.put_line('其他例外');end;

结果：没有找到该员工

--系统例外 返回多个结果 Too_many_rows

declarepname emp.ename%type;begin--查询所有10号部分的员工的姓名select ename into pename from emp where deptno =10;exceptionwhen too_many_rows thendbms_output.put_line('匹配了多个行');when others then --除了上边这个数据库中情况，都由这里捕获，避免将例外抛给数据库dbms_output.put_line('其他例外');end

结果：匹配了多个行


--系统例外 被零除 Zero_Divide

declare--定义一个基本变量pnum number;beginpnum :=1/0;exceptionwhen zero_divide thendbms_output.put_line('0不能做除数');when others then --除了上边这个数据库中情况，都由这里捕获，避免将例外抛给数据库dbms_output.put_line('其他例外');end;

结果：0不能做除数

--系统例外 算术或转换错误 value_error

declare--定义一个基本变量pnum number;beginpnum :='abc';exceptionwhen value_error thendbms_output.put_line('算术或转换错误');when others then --除了上边这个数据库中情况，都由这里捕获，避免将例外抛给数据库dbms_output.put_line('其他例外');end;

结果：算术或转换错误

3.5.2自定义例外

除了使用系统定义好例外之外，我们还可以使用自定义例外

定义例外变量，类型是exception 
使用raise抛出自定义例外

案例：

declare my_job char(20);v_sal emp.sal%type;--自定义异常no_data exception;cursor c1 sis elect distinct job from emp order by job;beginopen c1;fetch c1 into v_job;if c1%notfound then --抛出自定义异常raise no_data;end if;exceptionwhen no_data then  --捕获自定义异常dbms_output.put_line('没有发现数据');when others then --除了上边这个数据库中情况，都由这里捕获，避免将例外抛给数据库dbms_output.put_line('其他例外');end;

4.0一个比较复杂的PL/SQL块

实现continue

1.说明
1.下边的for emm in (select * from newqdgl.agent_point_not_user_sql) loop .. end loop; 中for循环将查询结果
集合保存到emm临时变量中，这很像光标cursor的功能

2.oracle的循环语句在较低的版本中有break表示结束整个循环，不过没有continue跳过本次循环的功能，我们可以通过
goto语句间接的实现continue功能 如下定义命名块<<endlabel>> ，使用goto endlabel;就可以跳转到有名块

3.有名块和匿名块
前边我们用的都是匿名块，定义有名块语法 <<名字>>

declare  v_OpId         number(12);  v_Org_Id       number(12);  v_llCountMonth number(12); --表示本月需要积分出账的月份  v_iChangePoint number(12); --表示转出积分  v_pointsSysId  number(12); --积分编号  v_lPointsValue number(12); --转出积分临时变量  v_sql          varchar2(1024);  v_thisyearValuedPoints number(12); --积分子表年度积分  v_CurrPoints           number(12); --积分子表当前积分  v_docode               number(12); --获取业务记录  v_doneCode            number(12);  v_subdtail_CurrPoints number(12); --积分明细当前积分  v_POINTS_INFO_count   number(2);begin  v_OpId                := 999;  v_Org_Id              := 999;  v_llCountMonth        := 201608;  v_iChangePoint        := 0;  v_pointsSysId         := 0;  v_lPointsValue        := 0;  v_subdtail_CurrPoints := 0;  v_POINTS_INFO_count   :=0;  -- 通过下边这种for循环的方式，可以将查询结果集合保存到一个临时集合变量中，可以达到类似于光标cursor的效果  for emm in (select * from newqdgl.agent_point_not_user_sql) loop      dbms_output.put_line('1---' || emm.agent_id || '--' ||emm.curr_point_not_exc);      -- 3.积分变更    --根据代理商编号查询对应积分分表编号        select count(1) into v_POINTS_INFO_count from     newqdgl.POINTS_INFO     WHERE 1 = 1       AND POINTS_ID = emm.agent_id;     if v_POINTS_INFO_count =0 then       goto endlabel;      end if;           SELECT POINTS_SYS_ID      into v_pointsSysId      FROM newqdgl.POINTS_INFO     WHERE 1 = 1       AND POINTS_ID = emm.agent_id;      dbms_output.put_line('2---'||v_pointsSysId);      -- 3.1 计算  不可兑换转出积分  == 可兑换新增积分    if mod(v_llCountMonth, 100) = 1 then      v_iChangePoint := emm.curr_point_not_exc;    else      v_iChangePoint := emm.curr_point_not_exc /                        (14 - mod(v_llCountMonth, 100));    end if;    dbms_output.put_line('3----'||v_pointsSysId||'---'||v_iChangePoint);      --3.2更新AGENT_POINT_NOT_USER 表中的不可兑换积分    UPDATE newqdgl.AGENT_POINT_NOT_USER       set CURR_POINT_NOT_EXC =           (emm.curr_point_not_exc - v_iChangePoint),           REC_STATUS         = 1     WHERE AGENT_ID = emm.agent_id;      --3.3 不考虑赤字积分      v_lPointsValue := v_iChangePoint;      --3.4新增积分addAgentCurrPoint(pointsSysId, agentId, lPointsValue, llCountMonth);      dbms_output.put_line('积分新增开始' || v_pointsSysId || '---' ||emm.agent_id);    --3.4.1查询积分子表中的信息    SELECT CURR_POINTS, THISYEAR_VALUED_POINTS      into v_CurrPoints, v_thisyearValuedPoints      FROM newqdgl.ods_points_subinfo     WHERE 1 = 1       AND POINTS_SYS_ID = v_pointsSysId       AND POINTS_ID = emm.agent_id;      dbms_output.put_line('4----');      -- 2.4.2插入积分子表记录到临时表    insert into ods_points_subinfo_temp      (POINTS_SYS_ID,       POINTS_ID_TYPE,       POINTS_ID,       CURR_POINTS,       THISYEAR_VALUED_POINTS)    values      (v_pointsSysId,       4,       emm.agent_id,       (v_CurrPoints + v_lPointsValue),       (v_thisyearValuedPoints + v_lPointsValue));      dbms_output.put_line('5----');      --3.4.3查询积分明细    SELECT POINTS_SYS_ID SUB_CURR_POINTS      into v_subdtail_CurrPoints      FROM points_info_detail_info     WHERE 1 = 1       AND POINTS_ID = emm.agent_id       AND POINTS_ID_TYPE = 4       AND POINTS_SYS_ID = v_pointsSysId       AND POINTS_SUB_TYPE = 1;      dbms_output.put_line('6----');      --3.4.4插入积分明细到临时表    insert into points_info_detail_info_temp      (POINTS_SYS_ID,       POINTS_ID_TYPE,       POINTS_ID,       POINTS_SUB_TYPE,       SUB_CURR_POINTS)    values      (v_pointsSysId,       4,       emm.agent_id,       1,       (v_subdtail_CurrPoints + v_lPointsValue));      dbms_output.put_line('7----');      --3.4.5 获取业务记录序列    SELECT newqdgl.SEQ_DONE_NEW_CODE.NEXTVAL into v_docode FROM DUAL;      INSERT INTO points_subcount_info_temp      (points_sys_id,       points_id_type,       points_id,       get_sub_points,       points_sub_type,       done_code)    VALUES      (v_pointsSysId, 4, emm.agent_id, v_lPointsValue, 1, v_docode);        dbms_output.put_line('8----' || v_CurrPoints || '--' ||v_thisyearValuedPoints || '--' || v_lPointsValue || '--' ||v_llCountMonth);      insert into ods_points_count_temp      (points_sys_id,       points_id_type,       points_id,       get_points,       points_busi_code,       done_date,       done_code,       bill_month,       op_id,       curr_points,       thisyear_valued_points,       ext4)    values      (v_pointsSysId,       4,       emm.agent_id,       v_lPointsValue,       19,       to_date('20160805', 'yyyymmdd'),       v_docode,       v_llCountMonth,       9,       (v_CurrPoints + v_lPointsValue),       (v_thisyearValuedPoints + v_lPointsValue),       to_char(v_CurrPoints + v_lPointsValue));      dbms_output.put_line('积分新增结束' || v_pointsSysId || '---' ||emm.agent_id);      select newqdgl.SEQ_DONE_CODE.nextval into v_doneCode from dual;    --插入积分记录    insert into newqdgl.Channel_Point_Record_Ext      (Chanenel_Entity_Id,       Operate_Code,       Oper_Value,       Done_Code,       Done_Date,       Org_Id,       Op_Id)    values      (emm.agent_id,       8020311,       0 - v_iChangePoint,       v_doneCode,       to_date('20160805', 'YYYY/MM/DD'), --可以写成具体的出账日期       v_Org_Id,       v_OpId);      dbms_output.put_line('9----');      insert into newqdgl.Channel_Point_Record_Ext      (Chanenel_Entity_Id,       Operate_Code,       Oper_Value,       Done_Code,       Done_Date,       Org_Id,       Op_Id)    values      (emm.agent_id,       8020310,       v_iChangePoint,       v_doneCode,       to_date('20160805', 'YYYY/MM/DD'), ----可以写成具体的出账日期       v_Org_Id,       v_OpId);        dbms_output.put_line('10----');        <<endlabel>> --这里相当于定义了一个有名的的块，endlabel ，使用goto endlabel 可以跳过goto endlabel 到endlabel之间的代码--即跳到for循环的最后，便相当于实现了continue,跳到这里后，不干什么需要些null;    null;  end loop;  --commit;EXCEPTION  WHEN OTHERS THEN    ROLLBACK;    dbms_output.put_line('error');    RETURN;end;

2.动态的拼接出sql

使用 execute immediate v_sql; 可以立即执行拼接后的sql,同时我们也可以使用输出语句将生成后的sql打印出来。

//------------------3.切换用户到"aicbs"  将临时表中的数据插入到各个分表---------------------------- 1. ods_points_count_tempdeclare       v_sql varchar2(1024);begin  for i in 0 .. 9 loop     v_sql:='insert into aicbs.points_count0'||i||'_2016  select * from newqdgl.ods_points_count_temp where mod(points_sys_id,10) = '||i;       execute immediate v_sql;  end loop;  --commit;  EXCEPTION  WHEN OTHERS THEN    ROLLBACK;    dbms_output.put_line('error');    RETURN;end;

2.1如果涉及到日期相关的使用‘’转移成一个‘

create table test(       done_date date);declare         v_sql varchar2(1024);  begin    for i in 0 .. 9 loop       v_sql:='insert into test(done_date) values(to_date(''2016/10/30'',''yyyy/mm/dd''))';         execute immediate v_sql;    end loop;    commit;    EXCEPTION    WHEN OTHERS THEN      ROLLBACK;      dbms_output.put_line('error');      RETURN;  end; 

结果：