第一篇：PLSQL学习总结
PL/SQL集合批量绑定（Bulk binds）减少循环开销PL/SQL引擎会执行过程化语句，但它把SQL语句传送给SQL引擎处理，然后SQL引擎把处理的结果返回给PL/SQL引擎。
PL/SQL和SQL引擎间的频繁切换会大大降低效率。典型的情况就是在一个循环中反复执行SQL语句。
批量绑定如何提高性能？
在SQL语句中为PL/SQL变量赋值称为绑定，PL/SQL绑定操作分为三种：
1.内绑定(in-bind)：用INSERT或UPDATE语句将PL/SQL发量或主变量保存到数据库。
2.外绑定(out-bind)：途过INSERT、UPDATE或DELETE语句的RETURNING子句返回值为PL/SQL变量或主变量赋值。
3.定义(define)：使用SELECT或FETCH语句为PL/SQL变量或主变量赋值.DML语句可以一次性传递集合中所有的元素，这个过程就是批量绑定。如果集合有20个元素，批量绑定的一次操作就相当于执行20次SELECT、INSERT、UPDATE或DELETE语句。绑定技术是靠减少PL/SQL和SQL引擎间的切换次数来提高性能的。
批量绑定（Bulk binds）包括：
(i)Input collections, use the FORALL statement，一般用来改善DML（INSERT、UPDATE和DELETE)操作的性能
(ii)Output collections, use BULK COLLECT clause，一般用来提高查询（SELECT）的性能
例一：对DELETE语句应用批量绑定
下面的DELETE语句只往SQL引擎中发送一次，即使是执行了三次DELETE操作： DECLARE
TYPE numlist IS VARRAY(20)OF NUMBER;
depts numlist := numlist(10, 30, 70);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
END;
当执行DELETE的数据数量非常大时，使用上述批量绑定方法能大大的提高运行效率。注意集合变长数组类型只能通过构造函数初始化，不能像索引表和嵌套表一样通过FOR循环直接赋值，具体可见下例。
例二：对INSERT语句应用批量绑定
CREATE TABLE blktest(num NUMBER(20), name varchar2(50));
DECLARE
type numtab is table of number(20)INDEX by binary_integer;
type nametab is TABLE of VARCHAR2(20)INDEX by binary_integer;
pnums
numtab;
pnames
nametab;
t1
number;
t2
number;
t3
number;
begin
for j in 1..100000?为集合索引表变量循环赋值
loop
pnums(j):= j;
pnames(j):= 'Seq No' || to_char(j);
end loop;
select Dbms_Utility.get_time into t1 from dual;--获得FOR运行前时间
for i in 1..100000?使用普通FOR循环插入10000条数据
loop
insert into blktest values(pnums(i),pnames(i));
end loop;
select dbms_utility.get_time into t2 FROM dual;获得FOR运行后时间和FORALL运行前时间
forall i in 1..100000?使用FORALL循环即集合批量绑定方法插入10000条数据
insert into blktest values(pnums(i),pnames(i));
select dbms_utility.get_time
into t3 from dual;--获得FORALL运行后时间
dbms_output.put_line('Execution Time(secs)');
dbms_output.put_line('------------------------');
dbms_output.put_line('FOR loop: ' || TO_CHAR(t2t2));
END;
运行结果：
Execution Time(secs)
------------------------
FOR loop: 982
FORALL loop: 49
总结：
1.在PL/SQL DEVELOPER的SQL WINDOWS中获得数据库当前时间的方法是：dbms_utility.get_time；如果需要测试某一段PLSQL的性能，则只需在开始和结束的时候分别获取数据库时间并做差即可，如上例中分别获取两种循环的耗时。
2.上例中，我们把10000个编号和名称放到索引表中。所有的表元素都向数据库插入两次：第一次使用FOR循环，然后使用FORALL语句。实际上，FORALL版本的代码执行速度要比FOR语句版本的快得多。
3.上述测试过程也可以通过在PL/SQL DEVELOPER的COMMAND WINDOWS中先编写PROCEDURE再运行的方法实现。在COMMAND WINDOWS中设置显示OUTPUT方法如下：
本文章未结束，请继续查看 “PLSQL学习总结：批量绑定减少循环开销--张凯(2)”
第二篇：PLSQL学习
----最简单的块
set serveroutput on
begin
dbms_output.put_line('Hello,World');
end;
----有定义和执行部分的块
----把用户的编号也显示出来
declare
----定义变量
v_ename varchar2(20);
v_salnumber(7,2);
begin
select ename,salinto v_ename,v_sal from empwhere empno = &xy;----在控制台显示用户名
dbms_output.put_line('用户名' || v_ename || '薪水是 ' ||v_sal);----异常处理
exception
when no_data_found then
dbms_output.put_line('朋友，你输入的编号有问题');
end;
CallableStatement 对象为所有的 DBMS 提供了一种以标准形式调用已储存过程的方法。已储存过程储存在数据库中。对已储存过程的调用是 CallableStatement对象所含的内容。这种调用是用一种换码语法来写的，有两种形式：一种形式带结果参，另一种形式不带结果参数。结果参数是一种输出(OUT)参数，是已储存过程的返回值。两种形式都可带有数量可变的输入（IN 参数）、输出（OUT 参数）或输入和输出（INOUT 参数）的参数。问号将用作参数的占位符。
在 JDBC 中调用已储存过程的语法如下所示。注意，方括号表示其间的内容是可选项；方括号本身并不是语法的组成部份。
{call 过程名[(?, ?,...)]}
返回结果参数的过程的语法为：
{? = call 过程名[(?, ?,...)]}
不带参数的已储存过程的语法类似：
{call 过程名}
通常，创建 CallableStatement 对象的人应当知道所用的 DBMS 是支持已储存过程的，并且知道这些过程都是些什么。然而，如果需要检查，多种DatabaseMetaData 方法都可以提供这样的信息。例如，如果 DBMS 支持已储存过程的调用，则supportsStoredProcedures 方法将返回 true，而getProcedures 方法将返回对已储存过程的描述。CallableStatement 继承 Statement 的方法（它们用于处理一般的 SQL 语句），还继承了 PreparedStatement 的方法（它们用于处理 IN 参）。
CallableStatement 中定义的所有方法都用于处理 OUT 参数或 INOUT 参数的输出部分：注册 OUT 参数的 JDBC 类型（一般 SQL 类型）、从这些参数中检索结果，或者检查所返回的值是否为 JDBC NULL。
1、创建 CallableStatement 对象
CallableStatement 对象是用 Connection 方法 prepareCall 创建的。下例创建 CallableStatement 的实例，其中含有对已储存过程 getTestData 调用。该过程有两个变量，但不含结果参数：
CallableStatement cstmt = con.prepareCall(“{call getTestData(?, ?)}”);
其中?占位符为IN、OUT还是INOUT参数，取决于已储存过程getTestData。
2、IN和OUT参数
将IN参数传给 CallableStatement 对象是通过 setXXX 方法完成的。该方法继承自 PreparedStatement。所传入参数的类型决定了所用的setXXX方法（例如，用 setFloat 来传入 float 值等）。
如果已储存过程返回 OUT 参数，则在执行 CallableStatement 对象以前必须先注册每个 OUT 参数的 JDBC 类型（这是必需的，因为某些 DBMS 要求 JDBC 类型）。注册 JDBC 类型是用 registerOutParameter 方法来完成的。语句执行完后，CallableStatement 的 getXXX 方法将取回参数值。正确的 getXXX 方法是为各参数所注册的 JDBC 类型所对应的 Java 类型。换言之，registerOutParameter 使用的是 JDBC 类型（因此它与数据库返回的 JDBC 类型匹配），而 getXXX 将之转换为 Java 类型。
作为示例，下述代码先注册 OUT 参数，执行由 cstmt 所调用的已储存过程，然后检索在 OUT 参数中返回的值。方法 getByte 从第一个 OUT 参数中取出一个 Java 字节，而 getBigDecimal 从第二个 OUT 参数中取出一个 BigDecimal 对象（小数点后面带三位数）：
CallableStatement cstmt = con.prepareCall(“{call getTestData(?, ?)}”);
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
cstmt.executeQuery();
byte x = cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2, 3);
CallableStatement 与 ResultSet 不同，它不提供用增量方式检索大 OUT 值的特殊机制。
3、INOUT参数
既支持输入又接受输出的参数（INOUT 参数）除了调用 registerOutParameter 方法外，还要求调用适当的 setXXX 方法（该方法是从 PreparedStatement 继承来的）。setXXX 方法将参数值设置为输入参数，而 registerOutParameter 方法将它的 JDBC 类型注册为输出参数。setXXX 方法提供一个 Java 值，而驱动程序先把这个值转换为 JDBC 值，然后将它送到数据库中。这种 IN 值的 JDBC 类型和提供给 registerOutParameter 方法的 JDBC 类型应该相同。然后，要检索输出值，就要用对应的 getXXX 方法。例如，Java 类型为byte 的参数应该使用方法 setByte 来赋输入值。应该给registerOutParameter 提供类型为 TINYINT 的 JDBC 类型，同时应使用 getByte 来检索输出值。
下例假设有一个已储存过程 reviseTotal，其唯一参数是 INOUT 参数。方法setByte 把此参数设为 25，驱动程序将把它作为 JDBC TINYINT 类型送到数据库中。接着，registerOutParameter 将该参数注册为 JDBC TINYINT。执行完该已储存过程后，将返回一个新的 JDBC TINYINT 值。方法 getByte 将把这个新值作为 Java byte 类型检索。
CallableStatement cstmt = con.prepareCall(“{call reviseTotal(?)}”);
cstmt.setByte(1, 25);
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.executeUpdate();
byte x = cstmt.getByte(1);
4、先检索结果，再检索 OUT 参数
由于某些 DBMS 的限制，为了实现最大的可移植性，建议先检索由执行CallableStatement 对象所产生的结果，然后再用 CallableStatement.getXXX 方法来检索 OUT 参数。如果 CallableStatement 对象返回多个 ResultSet 对象（通过调用 execute 方法），在检索 OUT 参数前应先检索所有的结果。这种情况下，为确保对所有的结果都进行了访问，必须对 Statement 方法 getResultSet、getUpdateCount 和getMoreResults 进行调用，直到不再有结果为止。
检索完所有的结果后，就可用 CallableStatement.getXXX 方法来检索 OUT 参数中的值。
5、检索作为OUT参数的NULL值
返回到 OUT 参数中的值可能会是JDBC NULL。当出现这种情形时，将对 JDBC NULL 值进行转换以使 getXXX 方法所返回的值为 null、0 或 false，这取决于getXXX 方法类型。对于 ResultSet 对象，要知道0或false是否源于JDBCNULL的唯一方法，是用方法wasNull进行检测。如果 getXXX 方法读取的最后一个值是 JDBC NULL，则该方法返回 true，否则返回 flases
第三篇：Oracle之PLSQL总结
基本数据类型变量 1.基本数据类型
Number 数字型
Int 整数型
Pls_integer 整数型，产生溢出时出现错误
Binary_integer 整数型，表示带符号的整数
Char 定长字符型，最大255个字符
Varchar2 变长字符型，最大2000个字符
Long 变长字符型，最长2GB
Date 日期型
Boolean 布尔型（TRUE、FALSE、NULL三者取一）
在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型，有的含义是完全一致的，有的是有不同的含义的。
2.基本数据类型变量的定义方法
变量名 类型标识符 [not null]:=值;
declare
age number(3):=26;--长度为3，初始值为26
commit;
begin
end;
其中，定义常量的语法格式：常量名 constant 类型标识符 [not null]:=值;
declare
pi constant number(9):=3.1415926;--为pi的数字型常量，长度为9，初始值为3.1415926
begin
表达式
变量、常量经常需要组成各种表达式来进行运算，下面介绍在PL/SQL中常见表达式的运算规则。
1.数值表达式
PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减法）、*（乘法）、/（除法）和**（乘方）等。
命令窗口中执行下列PL/SQL程序，该程序定义了名为result的整数型变量，计算的是10+3*4-20+5**2的值，理论结果应该是27。
set serveroutput on
Declare
result integer;
result:=10+3*4-20+5**2;
begin
commit;
end;dbms_output.put_line('运算结果是：'||to_char(result));
end;
dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。
2.字符表达式
字符表达式由字符型常数、变量、函数和字符运算符组成，唯一可以使用的字符运算符就是连接运算符“||”。
3.关系表达式
关系表达式由字符表达式或数值表达式与关系运算符组成，可以使用的关系运算符包括以下9种。
4.逻辑表达式
逻辑表达式由逻辑常数、变量、函数和逻辑运算符组成，常见的逻辑运算符包括以下3种。
PLSQL函数
PL/SQL程序中提供了很多函数供扩展功能，除了标准SQL语言的函数可以使用外，最常见的数据类型转换函数有以下3个。
系统输出打印
利用pl/sql在数据库服务器端打印一句话：
set serveroutput on--设置数据库输出，默认为关闭，每次重新打开窗口需要重新设置。
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;To_char：将其他类型数据转换为字符型。
To_date：将其他类型数据转换为日期型。
To_number：将其他类型数据转换为数值型。
继续追加中..NOT：逻辑非
OR：逻辑或
AND：逻辑与
运算的优先次序为NOT、AND和OR。< 小于
> 大于
= 等于（不是赋值运算符:=）
like 类似于
in 在„„之中
<= 小于等于
>= 大于等于
!= 不等于 或<>
between 在„„之间
关系型表达式运算符两边的表达式的数据类型必须一致。
pl/sql程序中对大小写不敏感（打印声明的变量）
set serveroutput on
DECLARE
pl语句块是pl/sql里最小的编程块，其中可以再嵌套 PL/SQL中的变量声明
所有变量必须在declare中声明，程序中不允许声明。没有初始化的变量默认值为null，屏幕上null是看不见的，命名习惯：PL/SQL中变量一般以v_开头（等同于存储过程中as和begin区域的变量定义习惯）。
注意number也能存小数，最长38位，所以以后建议整数都用binary_integer存。
long是字符类型，boolean类型不能打印。
标准变量类型：数字，字符，时间，布尔。
declare
v_number1 number;v_number2 number(3,2);v_number3 binary_integer :=1;v_name varchar2(20):='kettas';v_date date :=sysdate;v_long long :='ni hao';v_b boolean := true;
if(v_number1 is null)then dbms_output.put_line('hello');end if;
dbms_output.put_line(v_number1);dbms_output.put_line(v_number2);dbms_output.put_line(v_number3);dbms_output.put_line(v_name);dbms_output.put_line(v_date);dbms_output.put_line(v_long);
--dbms_output.put_line(v_b);--执行该句ORACLE提示“调用 'PUT_LINE' 时参数v_char varchar2(20):='a';
v_char1 varchar2(20):='b';
DBMS_OUTPUT.PUT_LINE(v_char);
DBMS_OUTPUT.PUT_LINE(v_char1);
BEGIN
END;begin
个数或类型错误”
end;
备注：关于声明number(4,3)中括号中的两个数字的意义，前面的数字叫精度，后面的叫刻度。刻度：当刻度为正数的时候，表示四舍五入到小数点后面的位数,当刻度为负数的时候，表示四舍五入到小数点前面的位数
精度：从数字的最前面不为零开始到刻度精确到的位置
―――――――――――――――――――――――――――――――――――――
v_Number number(4,3):=123.12312
1、按刻度进行四舍五入得到123.123
2、确定刻度精确到的位置123123处，精度为6位（.符号不算）
3、根据精度进行判断6位（>4）精度上限值
--报错不能存储
―――――――――――――――――――――――――――――――――――――
number(3,-3):=44445
1、根据刻度－3进行四舍五入得到44000
2、小数点向前移动3位44.此位置为刻度精确到的位置
3、根据精度进行判断2位（<3）精度上限值
--不报错可存储结果为44000
DECLARE
v_Number number(4,3):=123.12312;--实际精度6位大于上限精度值4位，提示“ORA-06502: PL/SQL: 数字或值错误 : 数值精度太高”
BEGIN
DECLARE
v_Number number(7,3):=4555;--实际精度7位等于上限精度值，可以存储
DBMS_OUTPUT.PUT_LINE(v_Number);
BEGIN
END
;
―――――――――――――――――――――――――――――――――――――
变量赋值方式
oracle中变量赋值方式是值拷贝而非引用
declare
v_number1 number:=100;
v_number2 number;
v_number2:=v_number1;
v_number1:=200;
dbms_output.put_line(v_number1);--200
dbms_output.put_line(v_number2);--100
DBMS_OUTPUT.PUT_LINE(v_Number);
END
;
begin
end;
―――――――――――――――――――――――――――――――――――――
PLSQL复合类型
记录类型record
record类型最常用，声明的时候可以加not null，但必须给初始值，如果record类型一致可以相互赋值，如果类型不同，里面的字段恰好相同，不能互相赋值。
引用记录型变量的方法是“记录变量名.基本类型变量名”。
declare
type t_first is record(id number(3),name varchar2(20));
v_first t_first;
begin
v_first.id:=1;
v_first.name:='cheng';
dbms_output.put_line(v_first.id);
dbms_output.put_line(v_first.name);
end;―――――――――――――――――――――――――――――――――――――
record类型变量间赋值
declare
type t_first is record(id number,name varchar2(20));
v_first t_first;
v_second t_first;
v_first.id:=1;
v_first.name:='susu';
v_second:=v_first;--相互赋值
v_first.id:=2;
v_first.name:='kettas';
dbms_output.put_line(v_first.id);
dbms_output.put_line(v_first.name);
dbms_output.put_line(v_second.id);
dbms_output.put_line(v_second.name);
begin
end;
―――――――――――――――――――――――――――――――――――――
表类型变量table 语法如下：
type 表类型 is table of 类型 index by binary_integer;
表变量名 表类型;
类型可以是前面的类型定义，index by binary_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。
table类型，相当于java中的Map容器，就是一个可变长的数组，key（符号整数索引）必须是整数，可以是负数，value（类型）可以是标量，也可以是record类型。可以不按顺序赋值，但必须先赋值后使用。
――――――――――――――――――――――――――――――――――――― 1.定义一维表类型变量
declare
type t_tb is table of varchar2(20)index by binary_integer;
v_tb t_tb;
v_tb(100):='hello';
v_tb(98):='world';
dbms_output.put_line(v_tb(100));
dbms_output.put_line(v_tb(98));
begin
end;
类型为record的表类型变量
――――――――――――――――――――――――――――――――――――― declare
type t_rd is record(id number,name varchar2(20));
type t_tb is table of t_rd index by binary_integer;
v_tb2 t_tb;
v_tb2(100).id:=1;
v_tb2(100).name:='hello';
--dbms_output.put_line(v_tb2(100).id);
--dbms_output.put_line(v_tb2(100).name);
dbms_output.put_line(v_tb2(100).id||''||v_tb2(100).name);
begin
end;
――――――――――――――――――――――――――――――――――――
2.定义多维表类型变量
该程序定义了名为tabletype1的多维表类型，相当于多维数组，table1是多维表类型变量，将数据表tempuser.testtable中recordnumber为60的记录提取出来存放在table1中并显示。
declare
type tabletype1 is table of testtable%rowtype index by binary_integer;
table1 tabletype1;
select * into table1(60)from tempuser.testtable where recordnumber=60;
dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);
begin
end;
备注：在定义好的表类型变量里，可以使用count、delete、first、last、next、exists和prior等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。
set serveroutput on
declare
type tabletype1 is table of varchar2(9)index by binary_integer;
table1 tabletype1;
table1(1):='成都市';
table1(2):='北京市';
table1(3):='青岛市';
dbms_output.put_line('总记录数：'||to_char(table1.count));
dbms_output.put_line('第一条记录：'||table1.first);
dbms_output.put_line('最后条记录：'||table1.last);
dbms_output.put_line('第二条的前一条记录：'||table1.prior(2));
dbms_output.put_line('第二条的后一条记录：'||table1.next(2));
begin
end;
――――――――――――――――――――――――――――――――――――― %type和%rowtype
使用%type定义变量，为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。
这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。
―――――――――――――――――――――――――――――――――――――
create table student(id number, name varchar2(20),age number(3,0));insert into student(id,name,age)values(1,'susu',23);
查找一个字段的变量 declare
查找多个字段的变量
declare
查找一个类型的变量，推荐用* declare
也可以按字段查找，但是字段顺序必须一样，不推荐这样做
declare
v_student student%rowtype;
select id,name,age into v_student from student where rownum=1;
dbms_output.put_line(v_student.id||''||v_student.name||''||v_student.age);
begin
v_student student%rowtype;
select * into v_student from student where rownum=1;
dbms_output.put_line(v_student.id||''||v_student.name||' '||v_student.age);
begin
v_id student.id%type;
v_name student.name%type;
v_age student.age%type;
select id,name,age into v_id,v_name,v_age from student where rownum=1;
dbms_output.put_line(v_id||' '||v_name||' '||v_age);
v_name varchar2(20);
v_name2 student.name%type;
select name
into
v_name2
from
student
where
rownum=1;
begin
dbms_output.put_line(v_name2);
end;begin
end;end;end;
declare
备注：insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制语句可以在pl/sql里用，但DDL语句不行。
declare
v_name student.name%type:='wang';
insert into student(id,name,age)values(2,v_name,26);
begin
end;
begin
declare
v_name student.name%type:='hexian';
update student set name=v_name where id=1;
begin
end;
begin
update student set name='qinaide' where id=2;
end;
―――――――――――――――――――――――――――――――――――――
PLSQL变量的可见空间
变量的作用域和可见性，变量的作用域为变量申明开始到当前语句块结束。
当外部过程和内嵌过程定义了相同名字的变量的时候，在内嵌过程中如果直接写这个变量名是没有办法访问外部过程的变量的，可以通过给外部过程定义一个名字<
declare
v_i1 binary_inteer:=1;
begin
declare
v_i2 binary_integer:=2;
begin
insert into student(id,name,age)values(5,'hehe',25);
end;v_student student%rowtype;
select id,name,age into v_student.id,v_student.name,v_student.age from--select * into v_student.id,v_student.name,v_student.age from student dbms_output.put_line();
begin
student where id=1;
where id=1;
end;
dbms_output.put_line(v_i1);
dbms_output.put_line(v_i2);
dbms_output.put_line(v_i1);
--dbms_output.put_line(v_i2);解开后执行Oracle会提示“必须说明标识符 'V_I2'”
end;
end;
―――――――――――――――――――――――――――――――――――――
PLSQL流程控制
if判断
declare
if else判断
declare
if elsif else判断
declare
v_name varchar2(20):='cheng';
if v_name='0701' then dbms_output.put_line('0701');
elsif v_name='cheng' then dbms_output.put_line('cheng');
else dbms_output.put_line('false');
end if;begin
v_b boolean:=true;
if v_b then dbms_output.put_line('ok');
else dbms_output.put_line('false');
end if;begin
v_b boolean:=true;
if v_b then dbms_output.put_line('ok');end if;begin end;end;end;――――――――――――――――――――――――――――――――――――― loop循环，注意推出exit是推出循环，而不是推出整个代码块
declare
v_i binary_integer:=0;loop
if v_i>10 then exit;end if;
v_i:=v_i+1;
begin
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');
end;loop简化写法
declare
while循环
declare
for循环，注意不需要声明变量
begin
for v_i in 0..10 loop
dbms_output.put_line('hello'||v_i);
end loop;
dbms_output.put_line('over');v_i binary_integer:=0;while v_i<10 loop
dbms_output.put_line('hello'||v_i);
v_i:=v_i+1;
end loop;
dbms_output.put_line('over');begin
v_i binary_integer :=0;loop
exit when v_i>10;
v_i :=v_i+1;
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');begin
end;end;end;―――――――――――――――――――――――――――――――――――――
PLSQL异常处理
1、声明异常
异常名 EXCEPTION；
2、抛出异常
RAISE 异常名
3、处理异常
抛出异常后的逻辑代码不会被继续执行
异常的定义使用 begin
dbms_output.put_line(1/0);
exception when others then dbms_output.put_line('error');
end;declare
e_myException exception;
dbms_output.put_line('hello');
raise e_myException;
--raise抛出异常,用此关键字，抛出后转到自定义的e_myException，执行其里面的--再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。dbms_output.put_line('world');
dbms_output.put_line(1/0);
exception when e_myException then dbms_output.put_line(sqlcode);dbms_output.put_line(sqlerrm);--当前错误信息
dbms_output.put_line('my error');
when others then dbms_output.put_line('error');
begin
putline函数后，--当前会话执行状态，错误编码
end;
―――――――――――――――――――――――――――――――――――――
PLSQL游标和goto语句
备注：下面提到的游标为静态cursor，包括显示和隐式。
游标，从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，她的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用它。
静态游标变量是在定义时就必须指定SQL语句。
cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。
declare
第二种游标的定义方式，用变量控制结果集的数量。
declare
cursor c_student is select * from book;
open c_student;
close c_student;
begin
end;v_id binary_integer;
cursor c_student is select * from book where id>v_id;
v_id:=10;
begin
第三种游标的定义方式，带参数的游标，用的最多。
declare
cursor c_student(v_id binary_integer)is select * from book where id>v_id;
open c_student(10);
游标的使用，一定别忘了关游标。
declare
如何遍历游标fetch
游标的属性 %found，%notfound,%isopen,%rowcount。
%found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。
%notfound，与%found行为相反。
%isopen，判断游标是否打开。
%rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。
注： no_data_found和%notfound的用法是有区别的，小结如下
1）SELECT...INTO 语句触发 no_data_found；
2）当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound；
3）当UPDATE或DELETE 语句的where 子句未找到时触发 sql%notfound；
4）在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。下面是几个实例：
create table BOOK(ID VARCHAR2(10)not null，BOOKNAME VARCHAR2(10)not null，v_student book%rowtype;
cursor c_student(v_id binary_integer)is select * from book where id>v_id;
open c_student(10);fetch c_student into v_student;
close c_student;
dbms_output.put_line(v_student.name);
close c_student;
end;begin
open c_student;
close c_student;
end;begin
end;
PRICE
VARCHAR2(10)not null，CID
VARCHAR2(10)not null);--insert create or replace procedure say_hello(i_name in varchar2, o_result_msg out varchar2)as
v_price varchar2(100);
e_myException exception;begin
--update or delete create or replace procedure say_hello(i_name in varchar2, o_result_msg out varchar2)as v_price varchar2(100);
e_myException exception;
begin
--select create or replace procedure say_hello(i_name in varchar2, o_result_msg out varchar2)as
v_price varchar2(100);
e_myException exception;begin
--loop方式遍历游标
declare
v_bookname varchar2(100);
cursor c_book(i_id number)is select bookname from book where id = i_id;
select price into v_price from book where bookname = i_name;
o_result_msg := 'success';
exception when no_data_found then rollback;
o_result_msg := 'select into dail';
update book set price = '55' where bookname = i_name;
delete from book where bookname = i_name;
if sql%notfound then raise e_myException;
end if;
/* if sql%rowcount = 0 then--写法2 raise e_myException;end if;*/
o_result_msg := 'success';
exception when e_myException then rollback;
o_result_msg := 'update or delete dail';
insert into book(id,bookname,price)values(1,2,3);
o_result_msg := 'success';
exception when others then rollback;
o_result_msg := substr(sqlerrm, 1, 200);
end;end;end;begin
或
declare
while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环
解决方法：
while nvl(c_student%found,true)loop
declare
for循环遍历，最简单，用的最多，不需要声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)
declare
v_bookname varchar2(100);
cursor c_book(i_id number)is select bookname from book where id = i_id;
Open c_book(i_id);
while nvl(c_book%found,true)
--或这种写法：while c_book%found is null or c_book%found loop Fetch c_book
update book set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
v_bookname varchar2(100);
cursor c_book(i_id number)is select bookname from book where id = i_id;
Open c_book(i_id);
Fetch c_book into v_bookname;
While c_book%Found Loop
update book set price = '33' where bookname = v_bookname;
Fetch c_book into v_bookname;
End Loop;
Close c_book;
Open c_book(i_id);
Loop
Fetch c_book into v_bookname;
exit when c_student%notfound;
update book set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
end;
begin
end;
begin
into v_bookname;
end;
cursor c_book(i_id number)is select bookname from book where id = i_id;
for cur in c_book(i_id)--直接将入参i_id传入cursor即可
loop
update book set price = '53' where bookname = cur.bookname;
end loop;
begin
end;goto例子，一般不推荐使用goto，会使程序结构变乱
declare
Oracle存储过程
在谈存储过程书写中的一些规则时，先看一下执行它的规则，在命令窗口执行存储过程sp_get_product_prompt set serveroutput on
var ret1 varchar2(200);
var ret2 varchar2(200);
exec sp_get_product_prompt(83,:ret1,:ret2);--或execute print ret1;print ret2;或
set serveroutput on
declare
ret1 varchar2(200);
ret2 varchar2(200);
sp_get_product_prompt(83,ret1,ret2);
dbms_output.put_line(ret1);
i number:=0;
if i=0 then
goto hello;
end if;
<
begin
dbms_output.put_line('hello');
goto over;
begin
end;
<
begin
dbms_output.put_line('world');
goto over;
end;
<
dbms_output.put_line('over');
end;begin
dbms_output.put_line(ret2);
end;存储过程入参，不论类型，缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间，同时，存储过程中可以再调用其它的存储过程，如果要保证存储过程之间的事务处理不受影响，可以定义为自治事务。
create or replace procedure say_hello(v_name in varchar2, v_flag number, o_ret out number)as
begin
对于入参为null情况下给予缺省值
create or replace procedure say_hello(i_name in varchar2, i_flag number, o_ret out number)
as
或直接在insert语句中调用nvl函数赋缺省值
insert into phone(..,wname..,)values(..,nvl(v_name,' '),..);----如果将' '写成'',则insert进来的v_name值还是为''等价于null值
带一个参数的存储过程
输入参数in，输入参数不能进行:=赋值，但可以将它赋给as后面定义的变量；
输入参数in，可以作为变量进行条件判断；默认不写就是in； 存储过程没有重载，这个有参的say_hello会替代已经存在的无参say_hello。
create or replace procedure say_hello(v_name in varchar2)
as
begin
--v_name:='a';--存储过程入参v_name不能做为赋值目标
dbms_output.put_line('hello '||v_name);
v_name varchar2(100);
if i_name is null then v_name := '0';
else
v_name := i_name;
end if;
insert into phone(..,wname..,)values(..,v_name,..);
begin
if v_name is null and v_flag is null then--v_name和v_flag都等于null
o_ret := 10;
else
o_ret := 100;
end if;
end;end;end;
存储过程输入参数作为变量进行条件判断
create or replace procedure say_hello(i_opFlag in number)
as
利用存储过程中定义的变量对入参的空值处理：
create or replace procedure say_hello(i_name in varchar2)
as
多个参数的存储过程
create or replace procedure say_hello(v_first_name in varchar2, v_last_name in varchar2)
as
begin
out输出参数，用于利用存储过程给一个或多个变量赋值，类似于返回值
create or replace procedure say_hello(v_name in varchar2，v_content out varchar2)begin
调用：
declare
v_con varchar2(200);
v_in varchar2(20):='wang';
v_content:='hello'||v_name;
end;dbms_output.put_line('hello '||v_first_name||'.'||v_last_name);
end;v_name varchar2(100);
if i_name is null then v_name :='0';
else v_name :=i_name;--将入赋值给定义变量
end if;
dbms_output.put_line('hello '||v_name);
begin
v_name varchar2(100);
if i_opFlag = 1 then
v_name :='0';
else
v_name :='haha';
end if;
dbms_output.put_line('hello '||v_name);
begin
end;end;begin
in out参数，既赋值又取值
create or replace procedure say_hello(v_name in out varchar2)
as
begin
v_name:='hi '||v_name;
end;调用：
declare
对存储过程入参赋缺省值
create or replace procedure say_hello（v_name varchar2 default 'susu', v_content varchar2 default 'hello')
as
begin
调用：(用指明形参名的方式调用更好)
begin
say_hello();
end;
或
begin
say_hello('cheng');
end;
或
begin
PLSQL中的function FUNCTION和PROCEDURE的区别
1、函数有返回值，过程没有
2、函数调用在一个表达式中，过程则是作为pl/sql程序的一个语句
say_hello(v_name=>'cheng');
end;dbms_output.put_line(v_name||' '||v_content);
end;v_inout varchar2(20):='wangsu';
say_hello(v_inout);
dbms_output.put_line(v_inout);
begin
say_hello(v_in,v_con);
dbms_output.put_line(v_con);
end;end;
过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。
过程有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的主要区别不在于返回值，而在于他们的调用方式，过程是作为一个独立执行语句调用的，函数以合法的表达式的方式调用
create or replace function func(v_name in varchar2)return varchar2
is begin
return(v_name||' hello');
end;
调用：
declare
v_name varchar2(20);
begin
v_name:=func('cheng');
dbms_output.put_line(v_name);
end;
带out参数的函数
create or replace function func(v_name in varchar2, v_content out varchar2 return varchar2
is
begin
v_content:=v_name||' hello';
return v_content;
end;
调用：
declare
v_name varchar2(20);
v_name1 varchar2(20);
begin
v_name1:=func('susu',v_name);--返回v_name值
dbms_output.put_line(v_name1);--打印func结果
dbms_output.put_line(v_name);--打印v_name结果
end;带in out 参数的函数
create or replace function func(v_name in out varchar2)return varchar2
is
begin
v_name:=v_name||' hello';
return 'cheng';)
end;调用：
declare
v_inout varchar2(20):='world';
v_ret varchar2(20);
v_ret:=func(v_inout);--返回调用v_inout值(作为出参)
dbms_output.put_line(v_ret);--打印func结果
dbms_output.put_line(v_inout);--返回v_name结果
begin
end;
第四篇：oracle plsql 开窗函数over学习总结
连续求和与求总和的区别 D 为天，S 为销售业绩为每天计算销售总额。
SELECTSUM(s)OVER(ORDERBY d),SUM(s)OVER()
FROM(SELECT'A'“A”,1 D, 20 SFROM DUAL
UNIONALL
SELECT'A'“A”,2 D, 15 SFROM DUAL
UNIONALL
SELECT'A'“A”,3 D, 14 SFROM DUAL
UNIONALL
SELECT'A'“A”,4 D, 18 SFROM DUAL
UNIONALL
SELECT'A'“A”,5 D, 30 SFROM DUAL)；
各种求和举例 CREATE TABLETEST_ZHUXP(DEPTNOVARCHAR2(10), ENAME VARCHAR2(10), SAL VARCHAR2(10));--部门姓名薪水
SELECTtest_zhuxp.*,sum(sal)over(partitionbydeptnoorderbyename)部门连续求和,--各部门的薪水“连续”求和
sum(sal)over(partitionbydeptno)部门总和,--部门统计的总和，同一部门总和不变
100*round(sal/sum(sal)over(partitionbydeptno),4)“部门份额(%)”, sum(sal)over(orderbydeptnoDESC,ename)连续求和,--所有部门的薪水“连续”求和
sum(sal)over()总和--此处sum(sal)over()等同于sum(sal)，所有员工的薪水总和
100*round(sal/sum(sal)over(),4)“总份额(%)”
FROMtest_ZHUXP
注意求和后可以排序不影响结果
SELECT DEPTNO,ENAME,SAL,SUM(SAL)OVER(PARTITIONBY DEPTNO ORDERBY DEPTNO DESC, SAL DESC)部门连续求和,SUM(SAL)OVER(ORDERBY DEPTNO DESC, SAL DESC)公司连续求和
FROM TEST_ZHUXP
排序
1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果
2.rank()和dense_rank()的区别是：
rank()是跳跃排序，有两个第二名时接下来就是第四名dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
SELECTt.*,RANK()OVER(PARTITIONBYCLASSORDERBY S DESC),dense_rank()OVER(PARTITIONBYCLASSORDERBY S DESC),ROW_NUMBER()OVER(PARTITIONBYCLASSORDERBY S DESC)
FROM(SELECT'a' “NAME”,1 “CLASS”,80 “S”FROM DUAL
UNIONALL
SELECT'b' “NAME”,1 “CLASS”,89 “S” FROM DUAL
UNIONALL
SELECT'c' “NAME”,1 “CLASS”,89 “S” FROM DUAL
UNIONALL
SELECT'e' “NAME”,3 “CLASS”,100 “S” FROM DUAL
UNIONALL
SELECT'f' “NAME”,3 “CLASS”,100 “S” FROM DUAL
UNIONALL
SELECT'g' “NAME”,3 “CLASS”,79 “S” FROM DUAL)t
统计
和group by的区别是可以看到每一行数据的所有信息
注意加NAME后的区别
SELECTt.*,SUM(1)OVER(PARTITIONBYCLASSORDERBYCLASS/*NAME*/)
FROM(SELECT'a' “NAME”,1 “CLASS”,80 “S”FROM DUAL
UNIONALL
SELECT'b' “NAME”,1 “CLASS”,89 “S” FROM DUAL
UNIONALL
SELECT'c' “NAME”,1 “CLASS”,89 “S” FROM DUAL
UNION ALL
SELECT'e' “NAME”,1 “CLASS”,100 “S” FROM DUAL
UNION ALL
SELECT'f' “NAME”,3 “CLASS”,100 “S” FROM DUAL
UNION ALL
SELECT'g' “NAME”,3 “CLASS”,79 “S” FROM DUAL)t
开窗函数
开窗函数
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：
over（orderby xxx）按照xxx排序进行累计，order by是个默认的开窗函数
over（partitionbyxxx）按照部门分区
2：
over（orderby salary rangebetween5precedingand5following）
每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
例如:对于以下列
aa
sum(aa)over（orderbyaarangebetween2precedingand2following）
得出的结果是
AASUM
214
214
214
318
418
522
618
722
就是说，对于aa=5的一行，sum为5-1<=aa<=5+2的和
对于aa=2来说，sum=1+2+2+2+3+4=14；
又如对于aa=9，9-1<=aa<=9+2只有9一个数，所以sum=9；
3：其它：
over（orderby salary rowsbetween2precedingand4following）
每行对应的数据窗口是之前2行，之后4行
4：下面三条语句等效：
over（orderby salary rowsbetweenunboundedprecedingandunboundedfollowing）每行对应的数据窗口是从第一行到最后一行，等效：
over（orderby salary
rangebetweenunboundedprecedingandunboundedfollowing）
等效over(partitionbynull)
任意删除重复行
在这个表中如果class与score相同，就考虑这行数据多余，删除多余行，就随便保留一行。
NAMECLASSSCORE
------------------------------
1.ff197
2.gg189
3.ll196
4.jj289
5.oo287
6.ii198
7.kk293
8.uu397
9.rr395
10.ee392
11.yy290
12.mm4100
13.nn498
14.pp198
15.fft197
16.ggt189
17.oot287
18.kkt293
19.ffff197
SQL> delete from c_score t where rowid in(select rowid from(select rowid ,row_number()over(partition by class,score order by class)dup_num from c_score)t where t.dup_num>1);
第五篇：plsql教学（本站推荐）
1、一个简单的程序 set serveroutput on;&符号的作用：提示用户输入 begin
dbms_output.put_line('HelloWorld!');end;/--执行语句
2、一个简单的程序块 declare
v_name varchar2(20);begin
v_name:='i am ynp';
dbms_output.put_line(v_name);end;
3、一个完整的简单程序 declare
v_num number := 0;begin
v_num :=2/v_num;
dbms_output.put_line(v_num);exception
when others then
dbms_output.put_line('error');end;
----变量声明的规则
1.变量名不能够使用保留字,如from、select等 2.第一个字符必须是字母 3.变量名最多包含30个字符 4.不要与数据库的表或者列同名 5.每一行只能声明一个变量
------
4、变量声明尽量以v_ 开头
5、常用变量类型
binary_integer ：整数，主要用来计数而不是用来表示字段类型 number :数字类型
char ：定长字符串类型
v_empnos(-1):=12;
v_empnos(2):=111;
dbms_output.put_line(v_empnos(-1));end;
说明：table变量类型命名规则：type（自定义变量）-table（table类型变量）-emp（表emp）-empno（empno字段）； 下标值可以为负值；
2、Record变量类型(类似于java中的类)declare type type_record_dept is record（deptno dept.deptno%type，dname dept.dname%type，loc dept.loc%type);
v_temp type_record_dept;begin
v_temp.deptno:=50;
v_temp.loc:='bj';
v_temp.dname:='aaa';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);end;
但上述dept表变动时，此程序也得手动做相应改动，可以用下述方法自动改动：
使用%rowtype声明record变量 declare
v_temp dept%rowtype;begin
v_temp.deptno:=50;
v_temp.loc:='bj';
v_temp.dname:='aaa';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);end;
errcode := SQLCODE;--出错代码 errmsg := SQLERRM
--出错信息
-----------------------------pl/sql中的sql语句----------------------
1、select语句
有且只有一条返回值，且必须加into 例子： declare
else
dbms_output.put_line('middle');
end if;--注意有这条语句 end;
5、while语句 declare
k binary_integer:=1;begin
while(k <11)loop
dbms_output.put_line(k);
k:=k+1;
end loop;end;
6、do..while语句 declare
k binary_integer:=1;begin
loop
dbms_output.put_line(k);
k:=k+1;
exit when(k>=11);
end loop;end;
7、for循环 declare
k binary_integer:=1;begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;end;
declare
k binary_integer:=1;begin
for k in reverse 1..10 loop--加上reverse为逆序打印
dbms_output.put_line(k);
end loop;end;
close c;end;
4、for循环（循环时最简单）declare
cursor c is
select * from emp;begin
for v_emp in c loop
--不用定义v_emp，不用打开关闭游标了
dbms_output.put_line(v_emp.ename);
end loop;end;
5、带参数的游标 declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type)is
select * from emp where deptno = v_deptno and job= v_job;begin
for v_emp in c(30,'CLERK')loop
dbms_output.put_line(v_emp.ename);
end loop;end;
6、可更新的游标
游标一般是作为记录集读取数据用的，但有时候用游标修改记录，这就是可更新游标； declare
cursor c is
select * from emp2 for update;begin
for v_emp in c loop
if(v_emp.sal <2000)then
update emp2 set sal =sal+1 where current of c;--修改定位到的当前记录，注意形式
elsif(v_emp.sal>=2000)then
delete from emp2 where current of c;
end if;
end loop;
commit;--提交 end;
-------------------------存储过程--------------------
1、把过程的declare变成 create or Replace produce p is 就行。
end if;
v_d := v_d+1;end;
---> 调试时：
可以在命令窗口调试，出错时 用show errors 显示出错信息； 可以在plDv中调试；
---> 运行时：
可以在命令窗口运行： declare
v_a number:=3;
v_b number:=4;
v_c number;
v_d number:=5;begin
p(v_a,v_b,v_c,v_d);
dbms_output.put_line(v_c);
dbms_output.put_line(v_d);end;可以在plDv中调试；
------------------函数-------------------
1、它有返回值
create or replace function tax_tag(sal number)return number--计算税率 is begin
if(sal > 1000)then
return 0.1;
elsif(sal>=2000)then
return 0.15;
else
return 0.2;
end if;end;
select ename, tax_tag(sal)from emp ；--直接用函数tax_tag
------------------------触发器(trigger)------------------------------1.create table emp2_log（insert into article values(10,'蚂蚁是护士',9,1,3);
----------》存储过程
create or replace procedure p(v_pid article.pid%type,v_level binary_integer)is
cursor c is select * from article where pid = v_pid;
v_perStr varchar2(2000):=' ';begin
for i in 1..v_level loop
v_perStr := v_perStr||'***';
end loop;
for v_article in c loop
dbms_output.put_line(v_perStr||v_article.cont);
if(v_article.isleaf = 0)then
p(v_article.id,v_level + 1);
end if;
end loop;end;
点击咨询 