一使用PL/SQL打印hello world
笔记教程见：https://github.com/caojx-git/learn/blob/master/notes/oracle/PLSQL.sql
注意：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 := &
num;
-
begin
-
--执行条件判断语句
-
if pnum =
0
then
-
dbms_output.put_line(
'你输入的数字是0');
-
elsif pnum = 1 then
-
dbms_output.put_line('你输入的数字是1');
-
else
-
dbms_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;
-
begin
-
while 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;
-
begin
-
loop
-
--退出条件
-
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
string
-
EXACT
-
cursor_space_for_time
boolean
-
FALSE
-
open_cursors
integer
-
300
-
session_cached_cursors
integer
-
20
修改默认可以打开的光标数
通过管理员用户修改默认光标的打开数量：
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从光标中取出一条记录，之后光标会向后移动,取出的值对应光标中select结果顺序值
-
fetch cemp into pename,psal;
-
--循环退出没有取到记录的时候
-
exit when cemp%notfound;
-
--否则打印记录
-
dbms_output.put_line(pename||'薪水是'||psal);
-
--打印影响行数
-
dbms_output.put_line(cemp%rowcount);
-
end
loop;
-
else
-
dbms_output.put_line('光标没有打开');
-
--关闭光标
-
close cemp;
-
end
if;
-
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 when 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
-
declare
-
pname emp.ename%
type;
-
begin
-
--查询员工工号是1234的员工的姓名(假如不存在该员工就会找不到数据)
-
select ename
into pename
from emp
where empno =
1234;
-
-
exception
-
when no_data_found then
-
dbms_output.put_line('没有找到该员工');
-
when others then
--除了上边这个数据库中情况，都由这里捕获，避免将例外抛给数据库
-
dbms_output.put_line('其他例外');
-
end;
结果：没有找到该员工
--系统例外 返回多个结果 Too_many_rows
-
declare
-
pname emp.ename%
type;
-
begin
-
--查询所有10号部分的员工的姓名
-
select ename
into pename
from emp
where deptno =
10;
-
-
exception
-
when too_many_rows then
-
dbms_output.put_line('匹配了多个行');
-
when others then
--除了上边这个数据库中情况，都由这里捕获，避免将例外抛给数据库
-
dbms_output.put_line('其他例外');
-
end
结果：匹配了多个行
--系统例外 被零除 Zero_Divide
-
declare
-
--定义一个基本变量
-
pnum
number;
-
begin
-
pnum :=
1/
0;
-
-
exception
-
when zero_divide then
-
dbms_output.put_line('0不能做除数');
-
when others then
--除了上边这个数据库中情况，都由这里捕获，避免将例外抛给数据库
-
dbms_output.put_line('其他例外');
-
end;
结果：0不能做除数
--系统例外 算术或转换错误 value_error
-
declare
-
--定义一个基本变量
-
pnum
number;
-
begin
-
pnum :=
'abc';
-
-
exception
-
when value_error then
-
dbms_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 is
select
distinct job
from emp
order
by job;
-
begin
-
open c1;
-
fetch c1 into v_job;
-
if c1%notfound then
-
--抛出自定义异常
-
raise no_data;
-
end
if;
-
exception
-
when 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_temp
-
declare
-
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;
结果：
原文：
https://github.com/caojx-git/learn/blob/master/notes/oracle/PLSQL.sql
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
