Oracle使用pl/sql

---

一、PL/SQL概述

• PL/SQL是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言(过程化SQL语言)；
• PL/SQL是Oracle对SQL语言的扩展，扩展部分内容：变量、常量、存储过程、程序包、游标、函数、触发器、条件控制、循环控制等；
• PL/SQL与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性；
• PL/SQL语法特点：PL/SQL程序都是以块（block）为基本单位，整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以 begin开头，以end结尾）和异常处理部分（以Exception开头）。其中执行部分是必须的，其他两个部分可选。无论PL/SQL程序段的代码量有多大，其基本结构就是由这三部分组成。

二、变量和常量

2.1变量

declare
变量名 数据类型；
begin
执行代码块内容；
end；

2.2赋值方式

方式一：“:=”
方式二：“select into 语句”

2.3常量

decare
变量名1 constant 数据类型 := 值；
begin
执行代码块内容；
end；

2.4常量和变量的区别

1. 常量声明的时候要在数据类型前面加 constant 关键字，表示常量的意思；
   常量必须初始值，且值不能被修改；

三、%type和%rowtype

3.1 %type

• 作用：用来引用数据表里面某个字段的数据类型；
  通俗解释：当我们想要声明一个变量来保存表内某个字段值但是又不知道该变量用什么数据类型的时候，我们就可以引用表内该字段的数据类型来作为变量的数据类型。

3.2 %rowtype

• 作用：可以声明一个基于表的行对象，也称为记录对象。

四、控制结构

4.1 if语句

语法结构：
if(条件1) then
条件1成立执行代码块
elsif(条件2) then
条件2成立执行代码块
…
else
以上条件都不成立执行代码块
end if;

4.2 case语句

语法结构：
begin
case ‘&变量’
when ‘值1’ then 执行代码块1；
when ‘值2’ then 执行代码块2；
when ‘值3’ then 执行代码块3；
when ‘值4’ then 执行代码块4；
…
else 当以上值都不满足的时候执行代码块
end case;
end;
“&”：作用相等于Java里面的Scanner；

4.3循环控制语句

loop循环.
案例1：输出1-100之间所有数字；
declare
i number:=0;
begin
<> --给loop循环取别名：aloop
loop
dbms_output.put_line(i); --输出每一个值
i:=i+1; --i的值在叠加
exit aloop when i=101; --当i等于100的时候aloop循环退出
end loop;
end;

4.4 while循环

while循环.
案例：求1-100之间的和。
declare
i number(8):=0;
mysum number(8):=0;
begin
while(i<=100) loop
mysum:=mysum+i;
i:=i+1;
end loop;
dbms_output.put_line(mysum); --循环完成后输出最终求的和
end;

4.5 for循环

案例：求1-100之间的和。
declare
mysum number(7):=0;
begin
for i in 1…100 loop
mysum:=mysum+i;
end loop;
dbms_output.put_line(mysum);
end;

五、异常处理

5.1预定义异常/系统自带异常.
当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发。
5.1.1 too_many_rows.
too_many_rows：行太多。意为：实际返回的行数超出请求的行数。
异常演示：
declare
sid number:=7499;
mysum emp%rowtype;
begin
select * into mysum from emp;
dbms_output.put_line(‘名字是：’||mysum.ename||‘，岗位是：’||mysum.job);
end;
处理异常：
declare
sid number:=7499;
mysum emp%rowtype;
begin
select * into mysum from emp;
dbms_output.put_line(‘名字是：’||mysum.ename||‘，岗位是：’||mysum.job);
exception when too_many_rows then
dbms_output.put_line(‘太多行’);
end;

5.1.2 no_data_found.
no_data_found：行太少。意为：未查询到数据。
异常演示：
declare
sid number:=0000;
mysal emp.sal%type;
begin
select sal into mysal from emp where empno=sid;
if(mysal>3500) then
dbms_output.put_line(‘要纳税’);
else
dbms_output.put_line(‘要努力’);
end if;
end;
处理异常：
declare
sid number:=0;
mysum emp%rowtype;
begin
select * into mysum from emp where empno=sid;
dbms_output.put_line(‘名字是：’||mysum.ename||‘，岗位是：’||mysum.job);
exception when no_data_found then
dbms_output.put_line(‘行太少’);
end;

5.2自定义异常.
语法：
declare
异常变量 exception;
begin
if(触发异常条件) then
raise 异常变量;
else
未触发异常执行的代码块
end if;
exception when 异常变量 then
触发异常执行的代码块
end;
案例演示：–根据工号查询工资，如果工资低于3500则抛出异常：‘你的工资太低了’。否则就输出：‘你的工资可以哟’。
declare
sid number:=7369;
mye exception;
mysal emp.sal%type;
begin
select sal into mysal from emp where empno=sid;
if(mysal<3500)then
raise mye;
else
dbms_output.put_line(‘你的工资可以哟’);
end if;
exception when mye then
dbms_output.put_line(‘你的工资太低了’);
end;

---

六、自定义函数

语法结构
create [or replace] function 函数名 (参数1,参数2)
return 返回值类型 is|as
定义返回变量(要取长度)；
begin
函数要执行的sql语句
return 变量名;
end;
案例演示：
定义函数：通过学号返回学生的名字。
create or replace function
fun_name(sid number) --函数名，带参数
return varchar2 is --返回值的类型，注意这里不能指定数据类型的长度
sname varchar2(10); --定义返回的变量(要取长度)
begin
select ename into sname from emp where empno=sid;
return sname; --返回定义的变量sname
end;
调用函数：select fun_name(7369) from dual;