目录
一、PL/SQL概念：
PL/SQL是过程语言PL与结构化查询语言SQL结合而成的编程语言。
PL/SQL是针对Oracle数据库的；
它是过程语言 + 结构化查询语言的结合；
过程语言PL：如变量声明，流程的控制，循环等；
查询语言SQL：SQL语言，如增、删、改、查等；
PL/SQL是SQL的扩展版，SQL能做的，PL/SQL绝大多数都能做。
PL/SQL的优点：
1.支持SQL：数据操纵命令，事务控制命令，游标控制，SQL函数和SQL运算符；
2.支持面向对象编程；
3.可移植性，可运行在任何操作系统上；
4.经过编译执行，性能佳；
5.与SQL紧密集成，简化数据处理，支持SQL数据类型，支持NULL值，支持%type和%rowtype属性类型（oracle中最有意思的）；
6.安全性
PL/SQL分成三个部分：
1. 声明部分
2. 可执行部分
3. 异常片理部分
语法结构：
[declare declaration] --声明部分
begin
executable statements --可执行部分
[exception handlers] --异常区
end;输出：
select 'abc' from dual;
dbms_output.put_line('abc');
--打印输出(必带begin)
begin
dbms_output.put_line('abc');
end赋值：( := )
--变量声明赋值，并打印
declare i number(6);
begin
i:=77; //:=赋值，select...into也是赋值
dbms_output.put_line(i);
end;--在emp表中将工号为7369的姓名输出
declare sid number;
sname varchar2(22);
begin
sid:=7369;
select ename into sname from emp where empno=sid; --select...into赋值方式
dbms_output.put_line(sname);
end;提示下：在begin里面用select语句，必定要用select…into。
===========================================
①数据类型：
1.标量类型
2.LOB类型
3.属性类型：
%type：提供某个变量或数据库表列的数据类型
%rowtype：提供表中一行的记录类型(非常有特色)
3.1 %type
--求7369的入职日期(在不知道该列是什么类型的情况下)
--申请一个与“入职日期”一样的
declare sid number;
shiredate emp.hiredate%type; --声明个变量，它的类型与表中某个列的类型一样
begin
sid:=7369;
select hiredate into shiredate from emp where empno=sid;
dbms_output.put_line(shiredate);
end;--也可以sb shiredate%type;
--求某某的所有信息
declare sid number;
sname emp.ename%type;
sjob emp.job%type;
begin
sid:=7369;
select ename,job into sname,sjob from emp where empno=sid;
dbms_output.put_line(sname||' '||sjob);
end;当然，要是表中有很多的列，还像以上这么写吗？
NO，使用行类型：%rowtype;
--查询某某的所有信息
declare sid number;
er emp%rowtype;
begin
sid:=7369;
select * into er from emp where empno=sid;
dbms_output.put_line(er.ename||' '||er.job);
end;
============================================
输入：& （一般做测试用，其它情况不怎么用）
declare sid number;
er emp%rowtype;
begin
sid:=&请输入; --&类似scanner
select * into er from emp where empno=sid;
dbms_output.put_line(er.ename||' '||er.job);
end; 注意：sid:=&请输入; --代表录入的是整型
sid:='&请输入'; --代表录入的是varchar2类型。
②逻辑比较：
<> , !=
③控制语句：
if 条件 then
……
else或者elsif…then
……
end if
例：
--工资大于3500交税，=3500刚好，<3500努力
if语句：
begin
if sal>3500 then
dbms_output.put_line('交税');
elsif sal=3500 then
dbms_output.put_line('刚好');
else
dbms_output.put_line('努力');
end if;
end;
case语句：
case
when then ;
when then ;
else
end case;
④循环语句：有三种：
1.loop 无条件循环
2.while
3.for
案例：
--打印1~100
declare i number;
begin
i:=1;
loop
dbms_output.put_line(i);
i:=i+1;
exit when i=100; --退出及退出条件
end loop;
end;
declare i number;
begin
i:=1;
<<b_loop>> --loop循环的名字
loop
dbms_output.put_line(i);
i:=i+1;
exit b_loop when i=100; --退出及退出条件
end loop;
end;
=
--求1~100的和
declare i number;
mysum number;
begin
i:=0;
mysum:=0;
while i<=100 loop
mysum:=mysum+i;
i:=i+1;
end loop;
dbms_output.put_line('总和：'||mysum);
end;
--循环里都会使用到loop
--for循环的写法：
declare
mysum number;
begin
mysum:=0;
for i in 1..100 loop
mysum:=mysum+i;
end loop;
dbms_output.put_line(mysum);
end;
--求奇数之和
declare
mysum number;
begin
mysum:=0;
for i in 1..100 loop
if mod(i,2)=1 then
mysum:=mysum+i;
end if;
end loop;
dbms_output.put_line(mysum);
end; 顺序语句：goto 标名……<<标名>>
⑤异常：
1.系统自带的异常---预定义异常
2.我们写代码报的异常---自定义异常
1.预定义异常
too_many_rows : 行太多
no_data_found : 数据未找到
例示：预定义异常：
declare sid number;
shiredate emp.hiredate%type;
sb shiredate%type;
begin
sid:=7369;
select hiredate into shiredate from emp;
dbms_output.put_line(shiredate);
--异常
exception --发生异常就会报错
when too_many_rows then
dbms_output.put_line('行太多');
end;
例示：自定义异常
三步：声明，判断，捕捉
iee exception;
raise iee;
exception when iee then
dbms_output.put_line('错误信息');
when too_many_rows then
dbms_output.put_line('错误信息');
……可以带多个异常体
⑥函数：
create or replace function f_name [(参数1,参数2..)]
return 类型
is/as
[local declarations] --函数体里面的变量声明全放该位置
begin
--执行体
return
--异常
end;
--案例：给编号，返回工资'交税'还是'刚好'，还是'努力'
create or replace function f_n126(sid number)
return varchar2
is
ssal number(8,2);
str varchar2(22); --注意，该处不用declare定义
begin
select sal into ssal from emp where empno=sid;
if ssal>3500 then
str:='交税';
elsif ssal=3500 then
str:='刚好';
else
str:='努力';
end if;
return str;
end; --如何调用函数？
--oracle调用方式：
select f_n126(7369) from dual; --pl/sql调用方式：
declare str varchar2(22);
begin
str:=f_n126(7369);
dbms_output.put_line(str);
end;自定义异常：
declare n_s number(5);
e_my exception;
pragma exception_init(e_my,-20001);
begin
select count(stuname) into n_s from stuinfo where stuname like '赵%';
if n_s=0 then
raise e_my;
end if;
dbms_output.put_line('数量是'||n_s);
exception
when e_my then
dbms_output.put_line('人员为空');
end;函数：
create or replace function my_temp(n_s in varchar2, n_max in out number)
return number is
n_tavg number(5, 2);
begin
select avg(stuage),max(stuage) into n_tavg,n_max from stuinfo where stuname like n_s||'%';
return n_tavg;
end;
create or replace function my_sum(n_a in number)
return number
is
n_sum number(5):=0;
begin
for int_s in 1..n_a loop
n_sum:=n_sum+int_s;
end loop;
return n_sum;
end;二、PL/SQL本章综合代码
--Java打印输出
System.out.println("123");
--oracle打印输出
--注意事项：PL/SQL编程中的语句必须放入begin...end语句块中。
begin
dbms_output.put_line('我是Oracle打印输出的结果');
end;
--2.PL/SQL变量的使用
--需求：定义一个变量保存一个姓名，然后打印输出。
declare
myname varchar2(100);
begin
--赋值 :=
myname:='林凡';
dbms_output.put_line('myname = '||myname);
end;
--3.SQL语言能实现的操作PL/SQL也能实现
--查询7369的薪资 通过SQL语言实现
select sal from emp where empno = 7369;
--通过PL/SQL编程查找7369的薪资 控制台打印输出
--语法：select ....into.....
--从某张表中查询到指定的结果然后into（赋值）给指定的变量名
declare
--声明一个变量保存查找的薪资
mysal number;
begin
select sal into mysal from emp where empno = 7369;
--打印输出
dbms_output.put_line('薪水为: '||mysal);
end;
--4.在使用变量的同时可以合二为一（int a = 10）
declare
myempno number:=7369;
mysal number;
begin
select sal into mysal from emp where empno = myempno;
--打印输出
dbms_output.put_line('薪水为: '||mysal);
end;
--5.oracle中的输入器 &
--需求：查找指定编号的薪资
declare
myempno number(10):='&输入员工编号';--编号
mysal number(20);
begin
select sal into mysal from emp where empno = myempno;
dbms_output.put_line(mysal);
end;
====================================================================
--PL/SQL编程中的数据类型
--1.标量类型：number,varchar2,date,boolean
--2.大数据类型:LOB 存储二进制文件（图片，视频等等）
--3. 属性类型： %type %rowtype
select * from emp;
--%type 引用指定表中的指定字段的类型
--%rowtype 引用指定表中的指定行的类型（类似java的对象）
--需求：查找7369的薪水，薪水类型不给定，怎么解决？ %type
declare
myempno emp.empno%type:=7369;--假设empno是number类型 emp.empno%type = number
mysal emp.sal%type;
begin
select sal into mysal from emp where empno = myempno;
dbms_output.put_line(mysal);
end;
--需求：查找7499的所有信息
declare
myempno emp.empno%type:=7499;
--根据行类型来定义一行的记录对象
myemp emp%rowtype;
begin
select * into myemp from emp where empno = myempno;
dbms_output.put_line(myemp.empno||' '||myemp.ename||' '||myemp.sal);
end;
===================================================================
--oracle中的选择结构：if结构 case结构
--需求：定义一个变量保存一个数字，判断这个数字是否为偶数
declare
mynum number(10):='&请输入一个数字';
begin
if (mod(mynum,2) = 0) then
dbms_output.put_line('偶数');
else
dbms_output.put_line('奇数');
end if;--结束if语句
end;
--大于3000 交税 等于3000继续努力 小于3000 回家挖田
select ename,decode(sign(sal - 3000),'1','交税','-1','回家挖田','0','继续努力')
from emp;
--通过IF实现 判断7369处于哪个级别
declare
myempno emp.empno%type:=7369;
mysal emp.sal%type;
begin
select sal into mysal from emp where empno = myempno;
--判断
if (mysal > 3000) then
dbms_output.put_line('交税');
elsif (mysal = 3000) then
dbms_output.put_line('继续努力');
elsif (mysal < 3000) then
dbms_output.put_line('回家挖田');
end if;
end;
--case的使用
begin
case '&请输入'
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('low');
else
dbms_output.put_line('鬼');
end case;
end;
=====================================================================
PL/SQL循环结构 loop while for
--loop循环 （涉及到嵌套需要命名 结束循环）
-- loop....end loop; while(true){}
--打印输出1-100
declare
mynum number(20):=1;
begin
loop
dbms_output.put_line(mynum);
--递增+1
mynum:=mynum+1;
--满足条件就结束
exit when mynum = 101;
end loop;
end;
------
declare
mynum number(20):=0;
begin
loop
dbms_output.put_line(mynum);
--满足条件就结束
exit when mynum = 100;
--递增+1
mynum:=mynum+2;
end loop;
end;
--1-100的和
declare
mynum number(10):=1;
mysum number(10):=0;
begin
<<myloop>>
loop
mysum:=mysum+mynum;
mynum:=mynum+1;
exit myloop when mynum = 101;
end loop;
dbms_output.put_line(mysum);
end;
--1-100的偶数和
declare
mynum number(10):=1;
mysum number(10):=0;
begin
<<myloop>>
loop
if (mod(mynum,2) = 0) then
mysum:=mysum+mynum;
end if;
mynum:=mynum+1;
exit myloop when mynum = 101;
end loop;
dbms_output.put_line(mysum);
end;
======
--while循环
--1-100
declare
mynum number(10):=1;
begin
while (mynum <= 100) loop
dbms_output.put_line(mynum);
mynum:=mynum + 1;
end loop;
end;
--for循环 for...in
--1-100的和
begin
for i in 1..100 loop
dbms_output.put_line(i);
end loop;
end;
--1-100的和
declare
mysum number(10):=0;
begin
for i in 1..100 loop
if(mod(i,2)=0) then
mysum:=mysum+i;
end if;
end loop;
dbms_output.put_line(mysum);
end;
======================================================================
异常（Exception）处理：当给定的语句执行后，如果找不到数据或者结果以预期的结果不匹配（行数）
plsql中提供了2中预设义异常：数据未找到异常，行太多异常
数据未找到异常(no_data_found)：提供的字段在数据表中查找不到
行太多异常(too_many_rows):匹配的结果过多。
try...catch(){}
自定义异常
--打印输出250的薪水
declare
myempno emp.empno%type:=250;
mysal emp.sal%type;
begin
select sal into mysal from emp where empno = myempno;
dbms_output.put_line(mysal);
--异常处理部分
exception
when no_data_found then
dbms_output.put_line('提供的编号不存在');
end;
=================================================
declare
mysal emp.sal%type;
begin
select sal into mysal from emp;
dbms_output.put_line(mysal);
--异常处理部分
exception
when too_many_rows then
dbms_output.put_line('行太多异常');
end;
--同时处理多个异常
declare
mysal emp.sal%type;
begin
select sal into mysal from emp;
dbms_output.put_line(mysal);
--异常处理部分
exception
when too_many_rows then
dbms_output.put_line('行太多异常');
when no_data_found then
dbms_output.put_line('提供的编号不存在');
end;
======================================================================
自定义异常 exception 定义异常 raise 抛出异常
/*
步骤
1.声明 异常名称 exception;
2.抛出 raise 异常名称;
3.捕捉 excetpion when 异常名称 then 输出异常结果提示;
*/
--判断一个年龄是否在指定的区间（18-36）
declare
myAgeException exception; --自己定义异常
myAge number(20):=45;
begin
if (myAge < 20 or myAge > 36) then
raise myAgeException;
else
dbms_output.put_line('年龄为： '||myAge);
end if;
--捕捉异常
exception
when myAgeException then
dbms_output.put_line('年龄不在指定的范围内');
end;
--函数 函数就是方法 方法就是函数
--方法的核心：封装代码，方便调用
/*
语法：
create [or replace] function 函数名称[(参数1,参数2,....)]
return 结果类型 is|as
声明部分不需要使用declare去定义
begin
语句块;
end;
*/
--封装求和的方法 1-10 1-100 1-1000
create or replace function returnSum(mynum number)
return number is
mysum number:=0;--保存和
begin
for i in 1..mynum loop
mysum:=mysum+i;
end loop;
return mysum;
end;
--调用
select returnSum(100) from dual;
--封装一个函数 根据指定的编号查找对应的工资
create or replace function getSalByEmpno(myempno emp.empno%type)
return emp.sal%type is
mysal emp.sal%type;
begin
select sal into mysal from emp where empno = myempno;
return mysal;
end;
--调用
select getSalByEmpno(7369) from dual;
------------------------------
declare
属性名称 varchar2(100):=xxx
属性名称 varchar2(100):='&'
属性名称 emp.xxx%type;
属性名称 emp%rowtype;
名称 exception
begin
select into
raise 异常名称
if then else end if
if then elsif then else end if
exception to_many_rows no_data_found
when....then
输出
end;
case when...then end case三、游标
1.什么是游标？
游标（cursor）是数据库系统在内存中开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字，用户可以用SQL语句逐一从游标中获取记录，并赋给变量做进一步处理。
--它是什么时候产生？ 当执行DML SQL语句时；
--它用来存放什么？ 结果集；
--它有名字吗？ 有，SQL或用户给它取名；
--它如何操作？ 用Fetch语句逐一从游标中获取记录，并赋给变量进一步处理；
--可以同时开几个？ 可同时开多个，具体数量由数据库初始化参数OPEN_CURSORS定义。
1.1 作用
用于定位结果集的行。
用来遍历结果集。
1.2 特点
一次提取一行给变量用于进一步处理。
通过循环，将结果集都遍历完。
2.游标的种类
游标分为静态游标（隐式和显式）和REF游标（+游标变量）。
2.1 游标的状态
%found -- sql语句影响了一行或多行时为true；
%notfound -- sql语句没有影响任何行时为true(常用，没找到为T，就退出)
%rowcount -- sql语句影响的行数；
%isopen -- 游标是否打开，始终为false。
2.2 静态游标：结果集已经确实（静态定义）的游标。
(1) 隐式游标
在PL/SQL中执行DML SQL(Insert/Delete/Update/Select)语句时自动创建；
自动声明、打开和关闭，其名SQL（注：所有的隐式游标名都叫“SQL”）；
例示：使用游标的属性
Begin
Update emp Set sal=5000 Where empno=7369;
If SQL%found Then
dbms_output.put_line('表已更新');
End if;
End;提示：在Java中，如对表进行了增/删/改操作，结果会返回个int n，
我们通过 n>0 或 n=0 来判断SQL代码是否执行成功。
这个n 即SQL%RowCount属性。
(2) 显示游标
用于处理Select时返回多行的查询；
增/删/改时不会用显示游标；
需要手动的去做声明、打开、提取、关闭操作。
例示：显示游标操作
declare
--①声明游标：划分存储区域，注意此时并没有执行Select语句。
cursor c_name is
select sal from emp where empno=7369;
my_sal emp.sal%type;
begin
--②打开游标：执行select语句，获得结果集存到游标中，此时游标指向结果集头，而不是第一条记录。
open c_name;
--③获取记录：移动游标取一条记录
fetch c_name into my_sal;
dbms_output.put_line(my_sal);
--④关闭游标：将游标放入缓冲池中，没有完全释放资源，可重新打开。
close c_name;
end;案例：
--带参数的显示游标
declare
cursor c_name(dno number) is
select * from emp where deptno=dno;
my_a emp%rowtype;
begin
open c_name(10); --打开游标，并传值
loop
fetch c_name into my_a;
exit loop c_name%notfound;
dbms_output.put_line('名字：'||my_a.ename);
end loop;
close c_name;
end;
--For循环游标
作用：简化游标处理代码（简化打开、提取、关闭）。
语法：
for r_index in cursor_name loop
……
end loop;
例示：For循环游标操作
declare
cursor c_name is
select * from emp;
begin
for i in c_name loop
dbms_output.put_line(i.ename||' '||i.job);
end loop;
end;
注释：如同foreach语句一样，i默认为行类型(%rowtype)，自动打开、提取、关闭。
--带参数的For循环游标
declare
cursor c_name(dno number) is
select * from emp where deptno=dno;
begin
for i in c_name(30) loop
dbms_output.put_line(i.ename||' '||i.job);
end loop;
end; 以上几种显示游标，For循环游标最为有效和简单；
但在游标的使用方法上，推荐第一种，希望大家把游标的步骤理解清楚。
--游标的嵌套问题
固名思义，就是在游标中嵌套一个游标。
例示：列出所有部门的人员信息。
结果如下：
部门号：10 部门名称：****
编号：** 姓名：**
编号：** 姓名：**
……
部门号：20 部门名称：****
编号：** 姓名：**
编号：** 姓名：**
……
分析：1.创建部门游标，先将部门信息列出来；
2.在循环取部门信息时，再创建打开员工表游标，进行读取。 代码：
declare
cursor mydc is select * from dept;
mydr dept%rowtype;
cursor myec(dno number) is select * from emp where empno=dno;
myer emp%rowtype;
begin
open mydc;
loop
fetch mydc into mydr;
exit loop mydc%notfound;
dbms_output.put_line('部门号：'||mydr.deptno||' 部门名称：'||mydr.dname);
--员工信息
open myec(mydr.deptno); --打开带参的游标
loop
fetch myec into myer;
exit loop myec%notfound;
dbms_output.put_line(' 编号：'||myer.empno||' 姓名：'||myer.ename);
end loop;
close myec;
end loop;
close mydc;
end;
--用For循环游标简化
declare
cursor mydc is select * from dept;
cursor myec(dno number) is select * from emp where empno=dno;
begin
for i in mydc loop
dbms_ouput.put_line('部门号'||i.deptno||' 部门名称：'||i.dname);
for j in myec(i.detpno) loop
dbms_output.put_line(' 编号'||j.empno||' 姓名：'||j.ename);
end loop;
end loop;
end; 3 REF游标 + 游标变量
REF游标也叫动态游标，动态SQL执行时产生；
REF游标更应该被称之为游标类型，而游标变量则为该类型的游标
(1) 创建REF游标+游标变量语法：
type REF_CURSOR_NAME is REF cursor
[return 返回类型];
游标变量 REF_CURSOR_NAME;
例示：创建个REF游标类型
type ref_name is REF cursor; --定义弱游标类型，名字叫ref_name
c_name ref_name; --设定游标变量c_name为ref_name类型
(2) REF游标分类：
强类型----有return
弱类型----无return
相对来说，弱类型更为灵活。
例示：声明强类型REF游标+游标变量
declare
type c_type is ref cursor
return emp%rowtype;
c_name c_type;(3) 打开游标变量语法：
open CURSOR_NAME for 查询结果集;
--用REF游标+游标变量显示数据
declare
type my_t is ref cursor
return emp%rowtype; --声明一个游标类型
myc my_t; --定义一个REF类型的游标变量
myr emp%rowtype;
begin
open myc for select * from emp;
loop
fetch myc into myr;
exit when myc%notfound;
dbms_output.put_line(myr.ename);
end loop;
close myc;
end;游标变量的优点和限制：
1.游标变量功能强大，可以简化数据处理；
2.游标变量优点：
a.可从不同的select语句中提取结果集；
b.可以作为过程的参数进行传递；
c.可以引用游标的所有属性；
d.可以进行赋值运算；
3.游标变量的限制：
a.不能在程序包中声明游标变量；
b.for update子句不能与游标变量一起使用；
c.不能使用比较运算符。
4.动态语句拼接 :n
设定一个游标变量来执行动态的sql语句。
数据库管理员、设计师常用的知识点。
语法：
open 游标名 for 'select … :1 …';
using 变量名;
--例示：
open c_name for 'select * from emp where sal>:1 order by sal desc'
using p_sal; --sal>:1动态拼接（相当于JDBC中的?占位符）
--将p_sal这个变量代入select中“:1”位置处
--如有多个动态拼接处，请设置:2，:3，……，同时using 后面变量用逗号分隔。
--例示：
DECLARE
r_emp emp%ROWTYPE;
TYPE c_type IS REF CURSOR;
cur c_type;
p_salary NUMBER;
BEGIN
p_salary:=2500;
OPEN cur FOR 'select * from emp where sal>:1 order by sal desc'
USING p_salary;
DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有：');
LOOP
FETCH cur INTO r_emp;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('编号：'|| r_emp.empno||'姓名：'||r_emp.ename||'薪水：'||r_emp.sal);
END LOOP;
CLOSE cur;
END;
439
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
