一 存储过程的优点:
1 SQL存储过程执行起来比SQL命令文本快得多。
当一个SQL语句包含在存储过程中时,服务器不必每次执行它时都要分析和编译它。
2 调用存储过程,可以认为是一个三层结构。
程序易于维护。如果程序需要做某些改动,你只要改动存储过程即可
3 你可以在存储过程中利用Transact-SQL的强大功能。
一个SQL存储过程可以包含多个SQL语句。可以使用变量和条件。
这意味着你可以用存储过程建立非常复杂的查询,以非常复杂的方式更新数据库
4 安全,不会有SQL语句注入问题。
------------------------------
二 创建存储过程
1 语句格式:
create or replace procedure 过程名(var_name_1 in type,var_name_2 out type) as
变量1 数据类型 --声明变量(变量名 变量类型)
begin
--存储过程的执行体
end 过程名;
eg:
create or replace procedure test(workDate in Date) is
begin
--打印输出信息
dbms_output.put_line('The input date is :' || to_date(workDate,'yyyy-MM-dd') );
end test;
2 变量赋值
变量名 := 值;
eg:
create or replace procedure test(workDate in Date) as
x number(4,2);
begin
x := 1;
end test;
3 判断语句
if 比较式 then begin 执行语句 end;end if;
eg:
create or replace procedure test(x in number) as
begin
if x > 0 then
begin
x := x-1;
end;
end if;
end test;
eg:
create or replace procedure test(num in number) as
begin
if mod(num,400)=0 then
leap:=true;
elsif mod(num,100)=0 then
leap:=false;
elsif mod(num,4)=0 then
leap:=true;
else
leap:=false;
end if;
end test;
4 case语句
create or replace procedure test(num in integer) as
begin
case num
when 1 then
dbms_output.put_line('小于100');
when 2 then
dbms_output.put_line('大于100小于200');
else
dbms_output.put_line('大于200');
end case;
end test;
运行:
set serveroutput on 这一句需要在SQLPLUS 下或者 PL/SQL DEV的command窗口下运行
eg:
set serveroutput on;
begin
test(2);
end;
5 for 循环
格式:
for ... in ... loop
--执行语句
end loop;
eg:循环遍历游标:
create or replace procedure test as
Cursor cursor is select name from tbl_student;
name varchar(20);
begin
for name in cursor loop
begin
dbms_output.put_line(name);
end;
end Loop;
end test;
eg:循环遍历数组
create or replace procedure test(varArray in myPackage.TestArray) as
i number;
begin
--注意:oracle 数组遍历是从1开始,因为他是表,只能从第一个数据开始
i :=1;
for i in 1..varArray.count loop
dbms_output.put_line(The No.||i||record in varArray is:||varArray(i));
end loop;
end;
end test;
6 while 循环
格式:
while 条件语句 loop
begin
--语句
end;
end loop;
eg:
create or replace procedure test(i in number) as
begin
while i<10 loop
begin
--执行语句
i:=i+1;
end;
end loop;
end test;
--还有一种直接循环
create or replace procedure test as
num integet;
begin
num :=10;
loop
dbms_output.put_line(num);
num := num+1;
exit when num > 100;
end loop;
end test;
7 数组
Oracle 没有数组的概念,数组其实是一张表(Table),每个元素就是表中的记录
使用数组时,可以使用Oracle已经定义好的数组,或根据需要自定义数组。
--使用Oracle自带的数组
x array; --使用时需要进行初始化
eg;
create or replace procedure test(y out array) as
x array;
begin
x:=new array();
y:=x;
end test;
--自定义数组类型
eg:
使用数组:
create or replace procedure test(varArray in myPackage.TestArray) as
...
自定义的数组
Public type declarations type info is record(name varchar(20),y number);
type TestArray is table of info index of binary_integer;
--此处声明了一个TestArray类型数组,其实他是一张存储info数据的一张表,表中有
两个字段:
name,y
index of binary_integer --编制该table的索引,可以不写。上面呈现这样:
type TestArray is table of info;--那么需要初始化:
varArray myPackage.TestArray;
varArray := new myPackage.TestArray();
end TestArray;
eg:
create or replace package myPackage is
begin
type TestArray is table of info index of binary_integer;
end myPackage;
8 游标的使用
一般用于遍历临时表的查询结果
(1)Cursor 型游标(不能用于参数传递)
使用场景:DML 和单行查询
create or replace procedure test is
cursor_1 Cursor is select stu_name from tbl_student where ....;--Cursor使用方式一
cursor_2 Cursor;
begin
select class_name into cursor_2 from tbl_class where ....;--Cursor使用方式二
--遍历游标
for name in cursor_1 loop
begin
dbms_output.put_line(name);
end;
end Loop;
end test;
(2)SYS_REFCURSOR型游标(Oracle 预先定义的游标,可以传递参数)
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR;name varchar(20);
begin
open cursor for select name from tbl_student where ...;--SYS_REFCURSOR只能通过open方法打开和赋值
loop
fetch cursor into name --SYS_REFCURSOR只能通过fetch into 来打开和遍历
exit when cursor %NOTFOUND;
--SYS_REFCURSOR中三种状态属性:%NOTFOUND 未找到记录 %FOUND 找到记录 %ROWCOUNT 当前游标指向的行位置
dbms_output.put_line(name);
end loop;
rsCursor := cursor;
end test;
游标属性:
%ROWCOUNT :返回到目前为止,已经从游标中取出的记录数量,也就是DML语句成功执行的数据行数;
%FOUND :如果成功取到数据返回true,否则返回false;
%NOTFOUND :未找到记录;
%ISOPEN :如果游标打开返回true,否则返回false;
%BULK_ROWCOUNT :返回forall语句修改的记录数量;
%BULK_EXCEPTIONS :返回forall语句修改记录时的异常信息.
下面是一个完整的显式游标操作实例:
一个这样的游标可以被多次open进行使用,显式游标是静态游标,它的作用域是全局的,
但也必须明白,静态游标也只有pl/sql代码才可以使用她。
eg;
解析:
%type与%rowtype区别:
%TYPE:属性的一种方式是在变量声明中使用。%TYPE是用于取得表或游标中的字段类型,便于维护。
DEALARE
V_first_name cur_chief.first_name%TYPE;
V_last_name cur_chief.last_name%TYPE;
%ROWTYPE
使用%ROWTYPE属性可以声明一个基于表或游标的行对象,也称为记录对象。
r_chief.department_name 就是去行记录对象中department_name字段下的记录。
----
9 存储过程异常
eg;
create or replace procedure test is
begin
select name,age,gender from tbl_student where ....;
exception
when NO_DATA_FOUND then
dbms_output.put_line('未找到记录');
when TO_MANY_ROWS then
dbms_output.put_line('返回值多于1行');
when others then
--异常处理
end test;
10 存储过程的若干问题
(1) 表名不能用as
(2) select 某一字段,后面必须加into
(3) 在使用select into 必须确保有记录,否则抛NO_DATA_FOUND异常
可以用select count(*) from 表名,查看记录是否存在
(4) 定义的参数名或变量名不能与字段名相同,否则会编译通过,但运行会报错
(5) 空值null处理
eg:
create or replace procedure test is
fcount number(8):=0;
begin
select sum(vount) into fcount from tbl_A where bid='xxxx';
--假如无记录,那么fcount 值为null,要加一个处理null语句
if fount is null then
fount := 0;
end if;
end test;
--
11 oracle中的布尔型
create table tbool (bool char check (bool in ('N','Y'));
insert into tbool values ('N');
insert into tbool values ('Y');
1 SQL存储过程执行起来比SQL命令文本快得多。
当一个SQL语句包含在存储过程中时,服务器不必每次执行它时都要分析和编译它。
2 调用存储过程,可以认为是一个三层结构。
程序易于维护。如果程序需要做某些改动,你只要改动存储过程即可
3 你可以在存储过程中利用Transact-SQL的强大功能。
一个SQL存储过程可以包含多个SQL语句。可以使用变量和条件。
这意味着你可以用存储过程建立非常复杂的查询,以非常复杂的方式更新数据库
4 安全,不会有SQL语句注入问题。
------------------------------
二 创建存储过程
1 语句格式:
create or replace procedure 过程名(var_name_1 in type,var_name_2 out type) as
变量1 数据类型 --声明变量(变量名 变量类型)
begin
--存储过程的执行体
end 过程名;
eg:
create or replace procedure test(workDate in Date) is
begin
--打印输出信息
dbms_output.put_line('The input date is :' || to_date(workDate,'yyyy-MM-dd') );
end test;
2 变量赋值
变量名 := 值;
eg:
create or replace procedure test(workDate in Date) as
x number(4,2);
begin
x := 1;
end test;
3 判断语句
if 比较式 then begin 执行语句 end;end if;
eg:
create or replace procedure test(x in number) as
begin
if x > 0 then
begin
x := x-1;
end;
end if;
end test;
eg:
create or replace procedure test(num in number) as
begin
if mod(num,400)=0 then
leap:=true;
elsif mod(num,100)=0 then
leap:=false;
elsif mod(num,4)=0 then
leap:=true;
else
leap:=false;
end if;
end test;
4 case语句
create or replace procedure test(num in integer) as
begin
case num
when 1 then
dbms_output.put_line('小于100');
when 2 then
dbms_output.put_line('大于100小于200');
else
dbms_output.put_line('大于200');
end case;
end test;
运行:
set serveroutput on 这一句需要在SQLPLUS 下或者 PL/SQL DEV的command窗口下运行
eg:
set serveroutput on;
begin
test(2);
end;
5 for 循环
格式:
for ... in ... loop
--执行语句
end loop;
eg:循环遍历游标:
create or replace procedure test as
Cursor cursor is select name from tbl_student;
name varchar(20);
begin
for name in cursor loop
begin
dbms_output.put_line(name);
end;
end Loop;
end test;
eg:循环遍历数组
create or replace procedure test(varArray in myPackage.TestArray) as
i number;
begin
--注意:oracle 数组遍历是从1开始,因为他是表,只能从第一个数据开始
i :=1;
for i in 1..varArray.count loop
dbms_output.put_line(The No.||i||record in varArray is:||varArray(i));
end loop;
end;
end test;
6 while 循环
格式:
while 条件语句 loop
begin
--语句
end;
end loop;
eg:
create or replace procedure test(i in number) as
begin
while i<10 loop
begin
--执行语句
i:=i+1;
end;
end loop;
end test;
--还有一种直接循环
create or replace procedure test as
num integet;
begin
num :=10;
loop
dbms_output.put_line(num);
num := num+1;
exit when num > 100;
end loop;
end test;
7 数组
Oracle 没有数组的概念,数组其实是一张表(Table),每个元素就是表中的记录
使用数组时,可以使用Oracle已经定义好的数组,或根据需要自定义数组。
--使用Oracle自带的数组
x array; --使用时需要进行初始化
eg;
create or replace procedure test(y out array) as
x array;
begin
x:=new array();
y:=x;
end test;
--自定义数组类型
eg:
使用数组:
create or replace procedure test(varArray in myPackage.TestArray) as
...
自定义的数组
Public type declarations type info is record(name varchar(20),y number);
type TestArray is table of info index of binary_integer;
--此处声明了一个TestArray类型数组,其实他是一张存储info数据的一张表,表中有
两个字段:
name,y
index of binary_integer --编制该table的索引,可以不写。上面呈现这样:
type TestArray is table of info;--那么需要初始化:
varArray myPackage.TestArray;
varArray := new myPackage.TestArray();
end TestArray;
eg:
create or replace package myPackage is
begin
type TestArray is table of info index of binary_integer;
end myPackage;
8 游标的使用
一般用于遍历临时表的查询结果
(1)Cursor 型游标(不能用于参数传递)
使用场景:DML 和单行查询
create or replace procedure test is
cursor_1 Cursor is select stu_name from tbl_student where ....;--Cursor使用方式一
cursor_2 Cursor;
begin
select class_name into cursor_2 from tbl_class where ....;--Cursor使用方式二
--遍历游标
for name in cursor_1 loop
begin
dbms_output.put_line(name);
end;
end Loop;
end test;
(2)SYS_REFCURSOR型游标(Oracle 预先定义的游标,可以传递参数)
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR;name varchar(20);
begin
open cursor for select name from tbl_student where ...;--SYS_REFCURSOR只能通过open方法打开和赋值
loop
fetch cursor into name --SYS_REFCURSOR只能通过fetch into 来打开和遍历
exit when cursor %NOTFOUND;
--SYS_REFCURSOR中三种状态属性:%NOTFOUND 未找到记录 %FOUND 找到记录 %ROWCOUNT 当前游标指向的行位置
dbms_output.put_line(name);
end loop;
rsCursor := cursor;
end test;
游标属性:
%ROWCOUNT :返回到目前为止,已经从游标中取出的记录数量,也就是DML语句成功执行的数据行数;
%FOUND :如果成功取到数据返回true,否则返回false;
%NOTFOUND :未找到记录;
%ISOPEN :如果游标打开返回true,否则返回false;
%BULK_ROWCOUNT :返回forall语句修改的记录数量;
%BULK_EXCEPTIONS :返回forall语句修改记录时的异常信息.
下面是一个完整的显式游标操作实例:
一个这样的游标可以被多次open进行使用,显式游标是静态游标,它的作用域是全局的,
但也必须明白,静态游标也只有pl/sql代码才可以使用她。
eg;
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
-- declare a cursor
CURSOR cur_chief IS
SELECT first_name,
last_name,
department_name
FROM employees e
INNER JOIN departments d ON d.manager_id = e.employee_id;
r_chief cur_chief%ROWTYPE;
BEGIN
OPEN cur_chief;
LOOP
-- fetch information from cursor into record
FETCH cur_chief INTO r_chief;
EXIT WHEN cur_chief%NOTFOUND;
-- print department - chief
DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
r_chief.first_name || ',' ||
r_chief.last_name);
END LOOP;
-- close cursor cur_chief
CLOSE cur_chief;
END;
解析:
%type与%rowtype区别:
%TYPE:属性的一种方式是在变量声明中使用。%TYPE是用于取得表或游标中的字段类型,便于维护。
DEALARE
V_first_name cur_chief.first_name%TYPE;
V_last_name cur_chief.last_name%TYPE;
%ROWTYPE
使用%ROWTYPE属性可以声明一个基于表或游标的行对象,也称为记录对象。
r_chief.department_name 就是去行记录对象中department_name字段下的记录。
----
9 存储过程异常
eg;
create or replace procedure test is
begin
select name,age,gender from tbl_student where ....;
exception
when NO_DATA_FOUND then
dbms_output.put_line('未找到记录');
when TO_MANY_ROWS then
dbms_output.put_line('返回值多于1行');
when others then
--异常处理
end test;
10 存储过程的若干问题
(1) 表名不能用as
(2) select 某一字段,后面必须加into
(3) 在使用select into 必须确保有记录,否则抛NO_DATA_FOUND异常
可以用select count(*) from 表名,查看记录是否存在
(4) 定义的参数名或变量名不能与字段名相同,否则会编译通过,但运行会报错
(5) 空值null处理
eg:
create or replace procedure test is
fcount number(8):=0;
begin
select sum(vount) into fcount from tbl_A where bid='xxxx';
--假如无记录,那么fcount 值为null,要加一个处理null语句
if fount is null then
fount := 0;
end if;
end test;
--
11 oracle中的布尔型
create table tbool (bool char check (bool in ('N','Y'));
insert into tbool values ('N');
insert into tbool values ('Y');