PL/SQL概述
一. 什么是PL/SQL?PL/SQL（ProcedureLanguage/SQL）是 Oracle对 sql 语言的过程化扩展，指在 SQL 下令语言中增长了过程处置处罚语句（如分支、循环等），使 SQL 语言具有过程处置处罚本领。把SQL语言的数据利用本领与过程语言的数据处置处罚本领团结起来，使得 PLSQL 面向过程但比过程语言简单、高效、机动和实用。
根本语法布局:
-- 声明变量
begin
-- 异常处理
end;
案例:
declare
age number default 10; -- 定义一个age变量,默认值为10;
begin
age := 11; -- 将age变量的值变为11;
DBMS_OUTPUT.put_line('年龄:' || age); -- 打印括号里的值
end;
二. 变量
一个变量只不外是在步调中可以利用的存储地域的名称。 PL/SQL中的每个变量都有一个指定的数据范例，它决定了变量内存的巨细和布局.
PL/SQL变量的名称由可选的字母，数字，美元($)符号，下划线和数字符号构成，不能凌驾30个字符。 默认情况下，变量名不区分巨细写。不能将生存的PL/SQL关键字用作变量名称。
根本语法格式:
-- 声明变量
变量名 类型(长度);
-- 变量赋值
变量名 := 变量值;
-- select into 变量赋值
select 列名 into 变量名 from 表名 where 条件;
select列名1,列名2 into 变量1,变量2 from 表名 where 条件 -- 列名要和变量名一一对应
案例:
declare
v_num0 T_ACCOUNT.NUM0%type;-- 定义变量
v_num1 T_ACCOUNT.NUM1%type;
v_usenum T_ACCOUNT.USENUM%type;
v_usernum2 number(10, 2);
v_money number(10, 2);
begin
select USENUM, NUM0, NUM1
INTO v_usenum, -- 对变量进行赋值
v_num0,
v_num1
from T_ACCOUNT
where YEAR = '2012'
and MONTH = '12'
and OWNERUUID = 1;
v_usernum2 := round(v_usenum / 1000, 2);
V_MONEY := ROUND(v_usernum2 * 2.45, 2);
DBMS_OUTPUT.PUT_LINE('单价:' || 2.45 || ',吨数:' || v_usernum2 || ',应付金额' || v_money);
end;
三. 记载型变量
3.1 列变量
记载表中的一列存放在变量中
根本语法格式:
-- 定义列变量
列变量名 表名.列名%type;
案例:
declare
v_num0 T_ACCOUNT.NUM0%type; -- 定义列变量
v_num1 T_ACCOUNT.NUM1%type;
v_usenum T_ACCOUNT.USENUM%type;
v_usernum2 number(10, 2);
v_money number(10, 2);
begin
select USENUM, NUM0, NUM1
INTO v_usenum,
v_num0, -- 将值赋值给列变量
v_num1
from T_ACCOUNT
where YEAR = '2012'
and MONTH = '12'
and OWNERUUID = 1;
v_usernum2 := round(v_usenum / 1000, 2);
V_MONEY := ROUND(v_usernum2 * 2.45, 2);
DBMS_OUTPUT.PUT_LINE('单价:' || 2.45 || ',吨数:' || v_usernum2 || ',应付金额' || v_money);
end;
3.2 行变量
记载表中的一行存放在变量中
根本语法格式:
-- 定义行变量
行变量名 表名%rowtype;
-- 引用行变量
行变量名.列名;
案例:
declare
v_accountT_ACCOUNT%rowtype; -- 定义行变量
v_usernum2 number(10, 2);
v_money number(10, 2);
begin
select *
into v_account -- 赋值给行变量一行的值
from T_ACCOUNT
where YEAR = '2012'
and MONTH = '12'
and OWNERUUID = 1;
v_usernum2 := round(v_account.USENUM / 1000, 2); -- 引用行变量的值
V_MONEY := ROUND(v_usernum2 * 2.45, 2);
DBMS_OUTPUT.PUT_LINE('单价:' || 2.45 || ',吨数:' || v_usernum2 || ',应付金额' || v_money);
end;
四. 非常
非常是指PL/SQL步调在实行时出现的错误,在PL/SQL中的一个告诫或错误的情况都被称为非常。提示：编译错误（重要指语法错误）不包罗在内。
非常处置处罚通常写在实行体的最下面，在全部实行语句之后以exception关键字开始非常处置处罚
在运行步调时出现的错误叫做非常
发生非常后，语句将制止实行，控制权转移到PL/SQL 块的非常处置处罚部分
非常有两种范例：
[*]预界说非常 - 当PL/SQL步调违背Oracle规则或逾越体系限定时隐式引发
[*]用户界说非常 - 用户可以在PL/SQL块的声明部分界说非常，自界说的非常通过RAISE 语句显式引发
4.1 预界说非常
Oracle预界说非常21个
定名的体系非常产生缘故原由ACCESS_INTO_NULL未界说对象CASE_NOT_FOUNDCASE中若未包罗相应的WHEN，而且没有设置ELSE时COLLECTION_IS_NULL聚集元素未初始化CURSER_ALREADY_OPEN游标已经打开DUP_VAL_ON_INDEX唯一索引对应的列上有重复的值INVALID_CURSOR在不合法的游标上举行利用INVALID_NUMBER内嵌的SQL语句不能将字符转换为数字NO_DATA_FOUND利用select into未返回行TOO_MANY_ROWS实行select into时，结果集凌驾一行ZERO_DIVIDE除数为0SUBSCRIPT_BEYOND_COUNT元素下标凌驾嵌套表或VARRAY的最大值SUBSCRIPT_OUTSIDE_LIMIT利用嵌套表或VARRAY时，将下标指定为负数VALUE_ERROR赋值时，变量长度不敷以容纳实际数据LOGIN_DENIEDPL/SQL应用步调毗连到oracle数据库时，提供了不准确的用户名或暗码NOT_LOGGED_ONPL/SQL应用步调在没有毗连oralce数据库的情况下访问数据PROGRAM_ERRORPL/SQL内部题目，大概须要重装数据字典＆pl./SQL体系包ROWTYPE_MISMATCH宿主游标变量与PL/SQL游标变量的返回范例不兼容SELF_IS_NULL利用对象范例时，在null对象上调用对象方法STORAGE_ERROR运行PL/SQL时，超出内存空间SYS_INVALID_ID无效的ROWID字符串TIMEOUT_ON_RESOURCEOracle在等待资源时超时根本语法布局:
exception
when 异常类型 then
异常处理逻辑
案例:
declare
v_accountT_ACCOUNT%rowtype;
v_usernum2 number(10, 2);
v_money number(10, 2);
begin
select *
into v_account
from T_ACCOUNT
where YEAR = '2012'
and MONTH = '12'
and OWNERUUID = 10000;
v_usernum2 := round(v_account.USENUM / 1000, 2);
V_MONEY := ROUND(v_usernum2 * 2.45, 2);
DBMS_OUTPUT.PUT_LINE('单价:' || 2.45 || ',吨数:' || v_usernum2 || ',应付金额' || v_money);
exception
when NO_DATA_FOUND THEN -- 当捕获到NO_DATA_FOUND类型的错误时,打印下方语句
DBMS_OUTPUT.PUT_LINE('未找到任何数据');
end;
4.2 用户自界说非常
根本语法布局:
declare
自定义异常名 EXCEPTION;
PRAGMA EXCEPTION_INIT (自定义异常名,错误码(-01403));
begin
...
exception
when 自定义异常名 THEN
subquery;
end;
案例:
declare
NO_DATA EXCEPTION; -- 定义自定义异常NO_DATA,错误码为-01403
PRAGMA EXCEPTION_INIT (NO_DATA,-01403);
v_accountT_ACCOUNT%rowtype;
v_usernum2 number(10, 2);
v_money number(10, 2);
begin
select *
into v_account
from T_ACCOUNT
where YEAR = '2012'
and MONTH = '12'
and OWNERUUID = 10000;
v_usernum2 := round(v_account.USENUM / 1000, 2);
V_MONEY := ROUND(v_usernum2 * 2.45, 2);
DBMS_OUTPUT.PUT_LINE('单价:' || 2.45 || ',吨数:' || v_usernum2 || ',应付金额' || v_money);
exception
when NO_DATA THEN -- 捕获自定义异常,捕获成功则打印下方语句
DBMS_OUTPUT.PUT_LINE('未找到任何数据');
end;
五.条件判定(if)
5.1 单分支条件判定
根本语法:
if 条件 then
业务逻辑
end if;
案例:
declare
age number default 18;
begin
if age >= 18 then -- 判断条件
DBMS_OUTPUT.put_line('成年'); -- 判断成功则打印语句
end if;
end;
5.2 双分支条件判定
if 条件 then
业务逻辑
else
业务逻辑
end if;
案例:
declare
age number default 16;
begin
if age >= 18 then -- 判断条件
DBMS_OUTPUT.put_line('成年'); -- 如果满足条件则打印
else
DBMS_OUTPUT.put_line('未成年');-- 不满足条件则打印
end if;
end;
5.3 多分支条件判定
if 条件1 then
业务逻辑
elsif 条件2 then
业务逻辑
else
业务逻辑
end if;
案例:
declare
month number default 6;
begin
if month in (1, 2, 3) then -- 判断条件
DBMS_OUTPUT.put_line('第一季度');-- 满足条件1则打印
elsif month in (4, 5, 6) then
DBMS_OUTPUT.put_line('第二季度');-- 满足条件2则打印
elsif month in (7, 8, 9) then
DBMS_OUTPUT.put_line('第三季度');-- 满足条件3则打印
elsif month in (10, 11, 12) then
DBMS_OUTPUT.put_line('第四季度');-- 满足条件4则打印
else
DBMS_OUTPUT.put_line('错误'); -- 以上条件都不满足则打印
end if;
end;
六. 循环
在Oracle数据库中，循环是一种根本的控制布局，用于重复实行一段代码直到满意特定条件。Oracle提供了几种差别范例的循环语句，以顺应差别的编程需求。
6.1 无条件循环loop
Loop循环是Oracle中最根本的循环布局，它会无穷循环直到碰到EXIT语句。在下面的例子中，Loop循环用于实现与Exit When循环类似的功能
根本语法:
loop
--循环语句
end loop;
案例:
declare
i number default 1; -- 定义一个计数器i
begin
loop
DBMS_OUTPUT.put_line('loop循环了:' || i || '次');-- 每循环一次则打印
exit when i >= 10;-- 判断条件 当i大于等于10时循环结束
i := i + 1; -- 每循环一次计数器就加一
end loop;
end;
6.2 条件循环while
While循环在每次迭代开始前查抄条件，假如条件为真，则实行循环体。
根本语法:
while 条件 -- 条件为真则执行循环
loop
...
end loop;
案例:
declare
i number default 1; -- 定义一个计数器
begin
while i <= 10-- 判断条件,条件为真则执行循环
loop
DBMS_OUTPUT.put_line('whileloop循环了:' || i || '次');-- 满足条件则打印
i := i + 1; -- 每循环一次计数器就加一
end loop;
end;
6.3 for循环
For循环是一种预界说迭代次数的循环，它会主动递增计数器并在到达指定次数后制止。
根本语法:
for 变量in 起始值..终止值
loop
...
end loop;
案例:
declare
i number default 1; -- 定义一个计数器
begin
for i in 1..10 -- 如果i迭代的次数满足序列里的值,则执行循环
loop
DBMS_OUTPUT.put_line('for循环了:' || i || '次');-- 满足条件则打印
end loop;
end;
七. 游标
游标是体系为用户开设的一个数据缓冲区,存放 SQL 语句的实行结果。
我们可以把游标明白为 PL/SQL 中的结果集。
这么说实在是不严谨的,准确来说游标（Cursor）是一种用于查询结果集的指针，它答应你逐行处置处罚查询结果。
语法格式:
-- 定义游标
cursor游标名称isSQL语句;
open 游标名称 -- 打开游标
loop
fetch 游标名称into 变量 -- 对游标内的值进行抓取
exit when 游标名称%notfound -- 当游标内的值被抓取完则退出循环
end loop;
close 游标名称 -- 关闭游标
案例:
declare
cursor c_area is select * -- 定义游标,游标里的内容为sql语句的结果集
from T_AREA;
v_area T_AREA%rowtype;
begin
open c_area;-- 打开游标
loop
fetch c_area into v_area; -- 抓取游标里的内容
exit when c_area%notfound; -- 当游标内没有值可以被抓取时,退出循环
DBMS_OUTPUT.put_line('编号为:' || v_area.ID || '地址为:' || v_area.NAME);
end loop;
end;
7.1 游标参数
语法格式:
-- 创建带有参数的游标
cursor 游标名称(参数名参数类型)issubquery;
-- 使用带参数的游标
open 游标名称(value);
案例:
declare
-- 定义游标,将T_AREA表里name列里包含v_name的内容作为游标的结果集
cursor c_area(v_name varchar2) is select *
from T_AREA
where name like '%' || v_name || '%';
v_area T_AREA%rowtype; -- 定义一个行变量
begin
open c_area('城');-- 对v_name进行赋值
loop
fetch c_area into v_area;-- 将游标里的内容赋值给行变量
exit when c_area%notfound;-- 当游标内没有值可以被抓取时,退出循环
DBMS_OUTPUT.put_line('编号为:' || v_area.ID || '地址为:' || v_area.NAME);
-- 打印行变量里的内容
end loop;
end;
7.2 for 循环提取游标值
我们每次提取游标，须要打开游标 关闭游标循环游标提取游标 控制循环的 退出等等，好贫困,可以用 for 循环齐备都变的简单 ，
上例的代码可以改造为下列情势:
declare
-- todo 1 声明带参数游标 cur_pricetable 根据参数值获取指定类型的价格表
cursor 游标名(参数名 参数类型) is
subquery;
begin
-- todo 2 使用 for 循环遍历
for v_pricetable in cur_pricetable(1) loop
...
end loop;
end;
案例:
declare
-- 定义游标c_area和参数type_id,类型为number
cursor c_area(type_id number) is select *
from T_PRICETABLE
where OWNERTYPEID = type_id;
-- T_PRICETABLE表中OWNERTYPEID = type_id的结果集作为游标的内容
begin
for v_row in c_area(1)-- 对游标的参数赋值
loop -- 进行循环
DBMS_OUTPUT.put_line('价格:' || v_row.PRICE || ',吨位:' || v_row.MINNUM || '-' || v_row.MAXNUM);
end loop;
end;
八.总结
8.1 PLSQL简介
PL/SQL编程语言是Oracle公司在1980年代后期开发的，它是SQL和Oracle关系数据库的过程扩展语言。以下是关于PL/SQL的某些值得留意的毕竟-
[*]PL/SQL是一种完全可移植的高性能事件处置处罚语言。
[*]PL/SQL提供了内置的，表明的且与OS无关的编程情况。
[*]也可以从下令行SQL * Plus接口直接调用PL/SQL 。
[*]也可以从外部编程语言调用直接调用数据库。
[*]PL/SQL的通用语法基于ADA和Pascal编程语言。
[*]除了Oracle，PL/SQL还可以在TimesTen内存数据库和IBM DB2中利用。
8.2 PL/SQL的功能
PL/SQL具有以下功能:
[*]PL/SQL与SQL精麋集成。
[*]它提供了广泛的错误查抄。
[*]它提供了许多数据范例。
[*]它提供了多种编程布局。
[*]它通过功能和过程支持布局化编程。
[*]它支持面向对象的编程。
[*]它支持Web应用步调和服务器页面的开发。
8.3 PL/SQL的优点
PL/SQL具有以下优点:
[*]SQL是标准的数据库语言，而PL/SQL与SQL精麋集成在一起。PL/SQL支持静态和动态SQL。静态SQL支持来自PL/SQL块的DML利用和事件控制。在Dynamic SQL中，SQL答应将DDL语句嵌入PL/SQL块中。
[*]PL/SQL答应一次将整个语句块发送到数据库。如允许以镌汰网络流量，并为应用步调提供高性能。
[*]PL/SQL可以查询，转换和更新数据库中的数据，因此可以进步步调员的生产率。
[*]PL/SQL通过强大的功能（比方非常处置处罚，封装，数据隐蔽和面向对象的数据范例）节流了操持和调试时间。
用PL/SQL编写的应用步调是完全可移植的。
[*]PL/SQL提供高安全性级别。
[*]PL/SQL提供对预界说SQL包的访问。
[*]PL/SQL提供了对面向对象编程的支持。
[*]PL/SQL为开发Web应用步调和服务器页面提供支持。
页:
[1]