一,PL/SQL数值型数据的选择
PL/SQL的数值型数据类型
BINARY_DOUBLE,BINARY_FLOAT,PLS_INTEGER,BINARY_INTEGER,NUMBER
|
NUMBER
|
BINARY_INTEGER
|
PLS_INTEGER
|
BINARY_FLOAT
|
BINARY_DOUBLE
|
表数据类型
|
是
|
否
|
否
|
是
|
是
|
Pl/sql数据类型
|
是
|
是
|
是
|
是
|
是
|
取值范围
|
|
-231---231
|
-231---231
|
单精度32bit
|
双精度64bit
|
数值类型
|
小数,浮点小鼠
|
整数
|
整数
|
浮点
|
浮点
|
我们在PL/SQL中使用整数时,推荐使用
BINARY_INTEGER
|
PLS_INTEGER
|
这两种数据类型:
declare
wk_cnt数据类型:= 0;
begin
for cnt in 0..100000000 loop
wk_cnt := wk_cnt + 1;
end loop;
end;
/
以上的PL/SQL块中,我们选择BINARY_INTEGER,PLS_INTEGER后循环的执行效率大约会是NUMBER性的3倍(别问我怎么看过程的效率,自己trace去)。个人习惯使用pls/integer
二,变量的申明及NOT NULL限制
这里我们对比以下两个过程
过程一:
declare
value1 numbernot null:= 0;
begin
for cnt in 0 .. 100000000 loop
value1 := 1;
end loop;
end;
/
过程二:
declare
value1 number;
begin
for cnt in 0 .. 100000000 loop
value1 := 1;
end loop;
if value1 is not null then
null;
end if;
end;
/
在过程一中,当我们每次执行循环的时候都会在value1 := 1;时都会对是否null做一次检查。而在过程二中,我们只在循环之后对null检查了一次。
三,NOCOPY
首先我们先解释一下nocopy,
在pl/sql中,参数模式分为三种,in ,out ,inout这三种的区别就不用介绍了
PL/SQL中对out,in out参数使用的?默认形参会复制一份实参的副本,然后在内部传递,修改等,发生异常,不会赋值给实参,控制权交还调用环境,而实参值不变,还是调用前的值。而使用了NOCOPY后,形参将获得一个指向实参的指针,然后在内部传递,赋值都直接修改实参了,此时如果异常发生,控制权交还调用环境,但是实参已经被修改了。无法还原成调用前的值。
对参数IN使用NOCOPY将会产生编译错误,这是因为参数IN总是按引用传递.NOCOPY的主要优点是可以提高程序的效率。当我们传递大型PL/SQL表时,其优越性特别显著.使用NOCOPY的限制在某些情况下,NOCOPY将被编译器忽略,这时的参数仍将按值传递。这时,编译器不会报告编译错误。由于NOCOPY是一个提示项(Hint),编译器可以决定是否执行该项。
大家可以对比以下四个过程,自己体悟下就能理解了。
过程一:
DECLARE
n NUMBER := 10;
PROCEDURE do_something (
n1 IN NUMBER,
n2 IN OUT NUMBER,
n3 IN OUT NOCOPY NUMBER) IS
BEGIN
n2 := 20;
dbms_output.put_line(n); -- prints 10<<1>>
n3 := 30;
dbms_output.put_line(n); -- prints 30 <<2>>
END;
BEGIN
do_something(n, n, n);
dbms_output.put_line(n); -- prints 20 <<3>>
END;
-----当指定nocopy后n3的值没有赋值给n
过程二:
DECLARE
n NUMBER := 10;
PROCEDURE do_something (
n1 IN NUMBER) IS
BEGIN
dbms_output.put_line(n1); -- prints 10
--n1:=20; --illegal assignment.
END;
BEGIN
do_something(n); ---------------- 10
do_something(20); --------------- 20
END;
过程三:
DECLARE
n NUMBER := 10;
PROCEDURE do_something (
n1 OUT nocopy NUMBER) IS
BEGIN
dbms_output.put_line('before assign: ' || n); -- prints none <<1>>
n1:=20;
dbms_output.put_line('before return: ' || n); -- prints 20 <<2>>
END;
BEGIN
do_something(n);
dbms_output.put_line('after return: ' || n); -- prints 20
END;
过程四:
DECLARE
n NUMBER := 10;
PROCEDURE do_something (
n1 OUT NUMBER) IS
BEGIN
dbms_output.put_line('before assign: ' || n1); -- prints none <<1>>
n1:=20;
dbms_output.put_line('before return: ' || n); -- prints 10 <<2>>
dbms_output.put_line('before return: ' || n1); -- prints 20 <<2>>
END;
BEGIN
do_something(n);
dbms_output.put_line('after return: ' || n); -- prints 20
END;
NOCOPY的主要优点是可以提高程序的效率。当我们传递大型PL/SQL表时,其优越性特别显著.使用NOCOPY的限制在某些情况下,NOCOPY将被编译器忽略,这时的参数仍将按值传递。这时,编译器不会报告编译错误。由于NOCOPY是一个提示项(Hint),编译器可以决定是否执行该项。在下列情况下,编译器将忽略NOCOPY项:
1.实参是索引表(index-by table)的成员时。如果该实参是全表,则该限制不起作用。
2.实参被强制指定精度,比例或NOT NULL时。该限制将不适用按最大长度强制的字符串参数。
3.实参和形参都是记录类型,二者是以隐含方式或使用了%ROWTYPE类型声明时,作用在对应字段的强制说明不一致。
4.传递实参需要隐式类型转换时。
5.子程序涉及到远程过程调用(PRC)。远程过程调用就是跨越数据库对远程服务器的过程调用。
(这里除了过程,文字很多都是copy的,整理起来挺麻烦,反正NOCOPY是个很好用的关键字,在参数长度越大的时候,效果越明显。)
四,returning
文字介绍确实很难写,我们直接看下面的程序块。
过程一:
declare
seq number;
begin
insert into table1 values
(seq1.nextval, 'AAA', 'BBB');
select seq1.currval into seq from dual;
dbms_output.put_line
(seq || ‘的值已经取得');
end;
/
过程二:
declare
seq number;
begin
insert into table1 values
(seq1.nextval, 'AAA', 'BBB')
returning col1 into seq;
dbms_output.put_line
(seq || '的值已经取得');
end;
这两个过程的目的都是在执行Insert之后获取当前的seq值,在第一个过程中,我们执行了两次sql,在第二个过程中我们使用了returning只执行了一次sql,代码性能不言而喻吧。
五,execute immediate中的绑定变量
我们看下面两条语句吧
一:
execute immediate'insert into temp values’||emp_no||’,’||emp_name||’,’||emp_sal||’)' ;
二:
execute immediate'insert into temp values (:empno, :ename, :sal)'
using emp_no, emp_name, emp_sal;
首先我们先说说什么是绑定变量,为什么要用绑定变量
绑定变量就是在动态sql的一个占位符,它告诉oracle现在需要生产一个执行计划,随后会为这个占位符提供一个值。
Sql分为动态部分和静态部分。静态部分是不变的,动态部分是不固定的。很明显整个数据库中的对象数量是有限的,而其中的数据是无限的。占位符体态的就是动态部分。动态部分在一般情况下,对执行计划的生成影响是微乎其微的。不同的动态部分产生的执行计划都是相同的。使用绑定变量可以史游标共享,避免多次硬解析。
也许你们已经明白这两条语句的优劣了,在语句二中我们使用了绑定变量。很少有朋友们写代码会注意到这些,在动态语句中并不会默认的将变量转换成绑定变量。在pl/sql中动态语句是不会自动将变量转换成绑定变量的,不知道后来改没改过来。
六,bulk处理
Pl/sql确实很简单,可是要写好却是有些麻烦,就拿bulk处理来说吧,也许很多同行们都不大会注意到这些。
什么是bulk处理,bulk处理就是把大量数据一次性处理。比如:一次性的进行多行的fetch,insert.通过数组等数据类型进行处理,PL/SQL表,嵌套表等。我们先看下面两个过程。
过程一:
declare
type emp_tbl_type is table of
emp%rowtype index by pls_integer;
emp_tbl emp_tbl_type;
cursor emp_cur is select * from emp;
begin
open emp_cur;
loop
fetch emp_cur
into emp_tbl(emp_cur%rowcount);
exit when emp_cur%notfound;
end loop;
close emp_cur;
end;
/
过程二:
declare
type emp_tbl_type is table of
emp%rowtype index by pls_integer;
emp_tbl emp_tbl_type;
cursor emp_cur is select * from emp;
begin
open emp_cur;
fetch emp_curbulk collect
intoemp_tbl;
close emp_cur;
end;
/
这两个过程实现的是同一个功能,想pl/sql表中里fetch数据,大家可以尝试下在大量数据下(10W条吧),第二种写法那是绝对的比第一种牛掰的。
Bulk collect也可以直接用在select into语句中
如下:
declare
type emp_tbl_type is table of
emp%rowtype index by pls_integer;
emp_tbl emp_tbl_type;
begin
select *bulk collect intoemp_tbl
from emp;
end;
/
Returning中也可以用:比如
insert into table1(col1, col2)
select seq1.nextval, col8 from table2
returning col1bulk collect intocol1_tbl;
bulk之forall
pl/sql中,for大家应该都用过了,forall肯定是用的很少了,废话不多说,咱们还是从程序中学习,看下面两段程序
程序一:
declare
type emp_tbl_type is table of
emp.empno%type index by pls_integer;
emp_tbl emp_tbl_type;
begin
select empno bulk collect into emp_tbl
from emp;
for i in emp_tbl.first .. emp_tbl.last
loop
update emp set sal = sal + 100
where empno = emp_tbl(i);
end loop;
end;
/
程序二:
declare
type emp_tbl_type is table of
emp.empno%type index by pls_integer;
emp_tbl emp_tbl_type;
begin
select empno bulk collect into emp_tbl
from emp;
foralli in emp_tbl.first
.. emp_tbl.last
update emp set sal = sal + 100
where empno = emp_tbl(i);
end;
/
看明白没,应该能看明白的,使用forall的效率,应该是第一段的两倍左右。
分享到:
相关推荐
pl/sql编写的院校招生系统(通过包、存储过程、触发器、视图等完成的),可供学习oracle数据库pl/sql编程使用
pl/sql例题代码pl/sql例题代码pl/sql例题代码
1、PL/SQL简介 2、PL/SQL基础 3、记录和表 4、在PL/SQL中使用SQL 5、内置SQL函数 6、游标 7、过程和函数 ...
pl/sql developer11.0下载 pl/sql developer11.0下载 pl/sql developer11.0下载
《Oracle PL/SQL程序设计(第5版)(套装上下册)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何...
PL/SQL Developer是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。如今,有越来越多的商业逻辑和应用逻辑转向了Oracle Server,因此,PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL ...
pl/sql数据库学习笔记,包含一些基础知识,和案例代码
Oracle公司已经将PL/SQL整合到Oracle 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本教程将以循速渐进的方式讲述PL/SQL基础语法,结构和组件、以及如何设计并执行一个PL/SQL程序,...
PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers modern features such as data encapsulation, overloading, collection types, ...
一个对数据库的操作工具PL/SQL,能够对ORACLE\SQL进行很好的帮助操作!
PL/SQL Developer是一个集成开发环境,专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加...
PL/SQL Developer是一个集成开发环境,专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加...
PL/SQL developer 12.07 注册码 可以使用,不错,自己用过了,分享给大家
pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl/sql 下载pl...
最新pl/sql7.0中文手册
PL/SQL 程序设计 本章主要重点: PL/SQL概述 PL/SQL块结构 PL/SQL流程 运算符和表达式 游标 异常处理 数据库存储过程和函数 包 触发器
PL/SQL Developer 8.0.3 1510 含注册机 PL/SQL Developer is an Integrated Development Environment that is specifically targeted at the development of stored program units for Oracle Databases. Over ...
oracle10g pl/sql完备教程,供初学者学习与开发者参考
很多时候你是不是为了32为的plsql的各种复杂配置烦恼,不要紧,现在下载64位的pl/sql,不需要繁琐的配置,让你更轻松
PL/SQL Developer是一种集成的开发环境,专门用于开发、测试、调试和优化Oracle PL/SQL存储程序单元,比如触发器等。PL/SQL Developer功能十分全面,大大缩短了程序员的开发周期。强大的PL/SQL编辑器,完善的Debugger...