第一篇：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);
第三篇：Oracle使用工具plsql远程连接问题
使用PL/SQL Developer远程连接Oracle数据库，本地机器上已经安装了Oracle数据库只要
1.配置tnsnames.ora（我的安装在D:oracleproduct10.2.0db_1networkADMIN）
2.登录plsql时填写相应信息
第一步：配置tnsnames.ora
找到tnsnames.ora文件，用记事本方式打开，你可以看到像如下配置信息
ORCL =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)))
上面这段是连接本地（安装好Oracle默认的,你也可以修改数据库别名，将“ORCL”换成其他的）的数据库的要连接远程的Oracle数据库则再增加配置信息如下
自己定义远程数据库名称(别名)=
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = 远程服务器IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 远程服务器上数据库名称)))
注意：别名不能与其他数据库（本地、远程）的别名相同，它可以跟相应远程服务器上的数据库名称不一样，要连接多个远程Oracle数据库，照样子在来几段上述配置信息即可。
第二步：登录plsql
Username:
Password:
Database:要登录的数据库名称(别名)
Connect as:
注意：一定要写对上面面的红色部分（其余的3个字段我就不说了），且跟tnsnames.ora配置文件中的别名相同
知道这两步后，以后不管是远程还是本地上的数据库你想怎么连就怎么连！只是tnsnames.ora文件中多了几段配置信息。
第四篇：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)”
第五篇：解决64位win7下plsql连接64位oracle的问题
解决win7 x64系统下PL/SQL无法连接64位Oracle数据库的方法
#问题描述：确定安装好Oracle 11g 64位客户端、PL/SQL developer（不区分32/64）后，打开PL/SQL，无法选择数据库实例，如下图：
#解决办法：
1.下载oracle官网提供的文件instantclient-basic-win32-11.2.0.1.0； 下载地址：http://pan.baidu.com/s/1c0CMOwS
2.将instantclient-basic-win32-11.2.0.1.0 文件加压至oracle文件目录product下：
3.复制oracle安装文件夹..networkadmin下的文件 listener.ora和tnsnames.ora 到..productinstantclient_11_2的下面
4.进入PL/SQL Developer安装目录，新建start.bat文件，用记事本编辑写入： @echo off
set path=D:appAdministratorproductinstantclient_11_2 set ORACLE_HOME=D:appAdministratorproductinstantclient_11_2 set TNS_ADMIN=D:appAdministratorproductinstantclient_11_2 set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 start plsqldev.exe
##此处的所有文件目录需要修改成自己的目录.5.点击运行start.bat, 正常情况会打开PL/SQL Developer（此时已经可以用来正常登录）
6.第5步正常开打登录窗户后，点击取消或登录进入PL/SQL主界面：
1)点击“Tools--Preferences”后，修改Oracle主目录名以及OCI库目录，目录中都应包含instantclient_11_2目录
此时保存退出后，完成对PL/SQL的修改
修改电脑环境变量，默认为oracle安装目录，需添加/修改为instantclient_11_2目录：
path=D:appAdministratorproductinstantclient_11_2 ORACLE_HOME=D:appAdministratorproductinstantclient_11_2 TNS_ADMIN=D:appAdministratorproductinstantclient_11_2 编辑完成保存后，PL/SQL即可正常使用。
点击咨询 