目录

一、PL/SQL概念：

①数据类型：

②逻辑比较：

③控制语句：

④循环语句：有三种：

顺序语句：goto 标名……<<标名>>⑤异常：

⑥函数：

二、PL/SQL本章综合代码

三、游标

一、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] --声明部分beginexecutable statements --可执行部分[exception handlers] --异常区end;

输出：

select 'abc' from dual;dbms_output.put_line('abc');--打印输出(必带begin)begindbms_output.put_line('abc');end

赋值：( := )

--变量声明赋值，并打印

declare i number(6);begini:=77; //:=赋值，select...into也是赋值dbms_output.put_line(i);end;

--在emp表中将工号为7369的姓名输出

declare sid number;sname varchar2(22);beginsid:=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; --声明个变量，它的类型与表中某个列的类型一样beginsid:=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;beginsid:=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;beginsid:=7369;select * into er from emp where empno=sid;dbms_output.put_line(er.ename||' '||er.job);end;

============================================

输入：& （一般做测试用，其它情况不怎么用）

declare sid number;er emp%rowtype;beginsid:=&请输入; --&类似scannerselect * 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语句：

beginif sal>3500 thendbms_output.put_line('交税');elsif sal=3500 thendbms_output.put_line('刚好');elsedbms_output.put_line('努力');end if;end;

case语句：

case

when then ;

when then ;

else

end case;

④循环语句：有三种：

1.loop 无条件循环

2.while

3.for

案例：

--打印1~100declare i number;begini:=1;loopdbms_output.put_line(i);i:=i+1;exit when i=100; --退出及退出条件end loop;end;declare i number;begini:=1;<<b_loop>> --loop循环的名字loopdbms_output.put_line(i);i:=i+1;exit b_loop when i=100; --退出及退出条件end loop;end;=--求1~100的和declare i number;mysum number;begini:=0;mysum:=0;while i<=100 loopmysum:=mysum+i;i:=i+1;end loop;dbms_output.put_line('总和：'||mysum);end;--循环里都会使用到loop--for循环的写法：declaremysum number;beginmysum:=0;for i in 1..100 loopmysum:=mysum+i;end loop;dbms_output.put_line(mysum);end;--求奇数之和declaremysum number;beginmysum:=0;for i in 1..100 loopif mod(i,2)=1 thenmysum:=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;beginsid:=7369;select hiredate into shiredate from emp;dbms_output.put_line(shiredate);--异常exception --发生异常就会报错when too_many_rows thendbms_output.put_line('行太多');end;例示：自定义异常三步：声明，判断，捕捉iee exception;raise iee;exception when iee thendbms_output.put_line('错误信息');when too_many_rows thendbms_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 varchar2isssal number(8,2);str varchar2(22); --注意，该处不用declare定义beginselect sal into ssal from emp where empno=sid;if ssal>3500 thenstr:='交税';elsif ssal=3500 thenstr:='刚好';elsestr:='努力';end if;return str;end;

--如何调用函数？

--oracle调用方式：

select f_n126(7369) from dual;

--pl/sql调用方式：

declare str varchar2(22);beginstr:=f_n126(7369);dbms_output.put_line(str);end;

自定义异常：

declare n_s number(5);e_my exception;pragma exception_init(e_my,-20001);beginselect count(stuname) into n_s from stuinfo where stuname like '赵%';if n_s=0 thenraise e_my;end if;dbms_output.put_line('数量是'||n_s);exceptionwhen e_my thendbms_output.put_line('人员为空');end;

函数：

create or replace function my_temp(n_s in varchar2, n_max in out number)return number isn_tavg number(5, 2);beginselect 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 numberisn_sum number(5):=0;beginfor int_s in 1..n_a loopn_sum:=n_sum+int_s;end loop;return n_sum;end;

二、PL/SQL本章综合代码

--Java打印输出System.out.println("123");--oracle打印输出--注意事项：PL/SQL编程中的语句必须放入begin...end语句块中。begindbms_output.put_line('我是Oracle打印输出的结果');end;--2.PL/SQL变量的使用--需求：定义一个变量保存一个姓名，然后打印输出。declaremyname 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;beginselect sal into mysal from emp where empno = 7369;--打印输出dbms_output.put_line('薪水为: '||mysal);end;--4.在使用变量的同时可以合二为一（int a = 10）declaremyempno number:=7369;mysal number;beginselect sal into mysal from emp where empno = myempno;--打印输出dbms_output.put_line('薪水为: '||mysal);end;--5.oracle中的输入器 &--需求：查找指定编号的薪资declaremyempno number(10):='&输入员工编号';--编号mysal number(20);beginselect 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的薪水，薪水类型不给定，怎么解决？ %typedeclaremyempno emp.empno%type:=7369;--假设empno是number类型 emp.empno%type = numbermysal emp.sal%type;beginselect sal into mysal from emp where empno = myempno;dbms_output.put_line(mysal);end;--需求：查找7499的所有信息declaremyempno emp.empno%type:=7499;--根据行类型来定义一行的记录对象myemp emp%rowtype;beginselect * into myemp from emp where empno = myempno;dbms_output.put_line(myemp.empno||' '||myemp.ename||' '||myemp.sal);end;===================================================================--oracle中的选择结构：if结构 case结构--需求：定义一个变量保存一个数字，判断这个数字是否为偶数declaremynum number(10):='&请输入一个数字';beginif (mod(mynum,2) = 0) thendbms_output.put_line('偶数');elsedbms_output.put_line('奇数');end if;--结束if语句end;--大于3000 交税 等于3000继续努力 小于3000 回家挖田select ename,decode(sign(sal - 3000),'1','交税','-1','回家挖田','0','继续努力')from emp;--通过IF实现 判断7369处于哪个级别declaremyempno emp.empno%type:=7369;mysal emp.sal%type;beginselect sal into mysal from emp where empno = myempno;--判断if (mysal > 3000) thendbms_output.put_line('交税');elsif (mysal = 3000) thendbms_output.put_line('继续努力');elsif (mysal < 3000) thendbms_output.put_line('回家挖田');end if;end;--case的使用begincase '&请输入'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');elsedbms_output.put_line('鬼');end case;end;=====================================================================PL/SQL循环结构 loop while for--loop循环 （涉及到嵌套需要命名 结束循环）-- loop....end loop;while(true){}--打印输出1-100declaremynum number(20):=1;beginloopdbms_output.put_line(mynum);--递增+1mynum:=mynum+1;--满足条件就结束exit when mynum = 101;end loop;end;------declaremynum number(20):=0;beginloopdbms_output.put_line(mynum);--满足条件就结束exit when mynum = 100;--递增+1mynum:=mynum+2;end loop;end;--1-100的和declaremynum number(10):=1;mysum number(10):=0;begin<<myloop>>loopmysum:=mysum+mynum;mynum:=mynum+1;exit myloop when mynum = 101;end loop;dbms_output.put_line(mysum);end;--1-100的偶数和declaremynum number(10):=1;mysum number(10):=0;begin<<myloop>>loopif (mod(mynum,2) = 0) thenmysum:=mysum+mynum;end if;mynum:=mynum+1;exit myloop when mynum = 101;end loop;dbms_output.put_line(mysum);end;======--while循环--1-100declaremynum number(10):=1;beginwhile (mynum <= 100) loopdbms_output.put_line(mynum);mynum:=mynum + 1;end loop;end;--for循环 for...in--1-100的和beginfor i in 1..100 loopdbms_output.put_line(i);end loop;end;--1-100的和declaremysum number(10):=0;beginfor i in 1..100 loopif(mod(i,2)=0) thenmysum:=mysum+i;end if;end loop;dbms_output.put_line(mysum);end;======================================================================异常（Exception）处理：当给定的语句执行后，如果找不到数据或者结果以预期的结果不匹配（行数）plsql中提供了2中预设义异常：数据未找到异常，行太多异常数据未找到异常(no_data_found)：提供的字段在数据表中查找不到行太多异常(too_many_rows):匹配的结果过多。try...catch(){}自定义异常--打印输出250的薪水declaremyempno emp.empno%type:=250;mysal emp.sal%type;beginselect sal into mysal from emp where empno = myempno;dbms_output.put_line(mysal);--异常处理部分exceptionwhen no_data_found thendbms_output.put_line('提供的编号不存在');end;=================================================declaremysal emp.sal%type;beginselect sal into mysal from emp;dbms_output.put_line(mysal);--异常处理部分exceptionwhen too_many_rows thendbms_output.put_line('行太多异常');end;--同时处理多个异常declaremysal emp.sal%type;beginselect sal into mysal from emp;dbms_output.put_line(mysal);--异常处理部分exceptionwhen too_many_rows thendbms_output.put_line('行太多异常');when no_data_found thendbms_output.put_line('提供的编号不存在');end;======================================================================自定义异常exception 定义异常 raise 抛出异常/*步骤1.声明异常名称 exception;2.抛出 raise 异常名称;3.捕捉excetpion when 异常名称 then 输出异常结果提示;*/--判断一个年龄是否在指定的区间（18-36）declaremyAgeException exception; --自己定义异常myAge number(20):=45;beginif (myAge < 20 or myAge > 36) thenraise myAgeException;elsedbms_output.put_line('年龄为： '||myAge);end if;--捕捉异常exceptionwhen myAgeException thendbms_output.put_line('年龄不在指定的范围内');end;--函数 函数就是方法 方法就是函数--方法的核心：封装代码，方便调用/*语法：create [or replace] function 函数名称[(参数1,参数2,....)]return 结果类型 is|as声明部分不需要使用declare去定义begin语句块;end;*/--封装求和的方法 1-10 1-100 1-1000create or replace function returnSum(mynum number)return number ismysum number:=0;--保存和beginfor i in 1..mynum loopmysum:=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 ismysal emp.sal%type;beginselect 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;名称 exceptionbeginselect intoraise 异常名称if then else end ifif then elsif then else end ifexception to_many_rows no_data_foundwhen....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”）；

例示：使用游标的属性

BeginUpdate emp Set sal=5000 Where empno=7369;If SQL%found Thendbms_output.put_line('表已更新');End if;End;

提示：在Java中，如对表进行了增/删/改操作，结果会返回个int n，

我们通过 n>0 或 n=0 来判断SQL代码是否执行成功。

这个n 即SQL%RowCount属性。

(2) 显示游标

用于处理Select时返回多行的查询；

增/删/改时不会用显示游标；

需要手动的去做声明、打开、提取、关闭操作。

例示：显示游标操作

declare

--①声明游标：划分存储区域，注意此时并没有执行Select语句。

cursor c_name isselect 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;案例：

--带参数的显示游标declarecursor c_name(dno number) isselect * from emp where deptno=dno;my_a emp%rowtype;beginopen c_name(10); --打开游标，并传值loopfetch 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循环游标操作declarecursor c_name isselect * from emp;beginfor i in c_name loopdbms_output.put_line(i.ename||' '||i.job);end loop;end;注释：如同foreach语句一样，i默认为行类型(%rowtype)，自动打开、提取、关闭。--带参数的For循环游标declarecursor c_name(dno number) isselect * from emp where deptno=dno;beginfor i in c_name(30) loopdbms_output.put_line(i.ename||' '||i.job);end loop;end;

以上几种显示游标，For循环游标最为有效和简单；

但在游标的使用方法上，推荐第一种，希望大家把游标的步骤理解清楚。

--游标的嵌套问题

固名思义，就是在游标中嵌套一个游标。

例示：列出所有部门的人员信息。

结果如下：

部门号：10 部门名称：****

编号：** 姓名：**

编号：** 姓名：**

……

部门号：20 部门名称：****

编号：** 姓名：**

编号：** 姓名：**

……

分析：1.创建部门游标，先将部门信息列出来；

2.在循环取部门信息时，再创建打开员工表游标，进行读取。 代码：

declarecursor mydc is select * from dept;mydr dept%rowtype;cursor myec(dno number) is select * from emp where empno=dno;myer emp%rowtype;beginopen mydc;loopfetch mydc into mydr;exit loop mydc%notfound;dbms_output.put_line('部门号：'||mydr.deptno||' 部门名称：'||mydr.dname);--员工信息open myec(mydr.deptno); --打开带参的游标loopfetch 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循环游标简化declarecursor mydc is select * from dept;cursor myec(dno number) is select * from emp where empno=dno;beginfor i in mydc loopdbms_ouput.put_line('部门号'||i.deptno||' 部门名称：'||i.dname);for j in myec(i.detpno) loopdbms_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游标+游标变量

declaretype c_type is ref cursorreturn emp%rowtype;c_name c_type;

(3) 打开游标变量语法：

open CURSOR_NAME for 查询结果集;

--用REF游标+游标变量显示数据declaretype my_t is ref cursorreturn emp%rowtype; --声明一个游标类型myc my_t; --定义一个REF类型的游标变量myr emp%rowtype;beginopen myc for select * from emp;loopfetch 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 后面变量用逗号分隔。

--例示：DECLAREr_emp emp%ROWTYPE;TYPE c_type IS REF CURSOR;cur c_type;p_salary NUMBER;BEGINp_salary:=2500;OPEN cur FOR 'select * from emp where sal>:1 order by sal desc'USING p_salary;DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有：');LOOPFETCH 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;

如果觉得《Oracle 快速入门 PL/SQL游标》对你有帮助，请点赞、收藏，并留下你的观点哦！