PL/SQL笔记1
来源：互联网 发布：linux svn 删除库 编辑：程序博客网 时间：2024/05/29 00:31
1、 NVL(v_counter,0)
2、
set serveroutput on
declare
v_descript varchar2(35);
v_number_test number(8,2);
v_location constant varchar2(4) := '603D';
v_boolean_test boolean;
v_start_date date := trunc(sysdate) + 7;
begin
if v_descript = 'Introduction to underwater'
then
dbms_output.put_line('This course is ' || v_descript || ',');
elsif v_location = '603D'
then
if v_descript is not null
then
dbms_output.put_line('The course is '|| v_descript || '.' || ' The
location is ' || v_location || '.');
end if;
else
dbms_output.put_line('The course and location ' || 'could not be determined');
end if;
exception
when others then
dbms_output.put_line('An error occurred.');
end;
3、
set serveroutput on
declare
v_student_id number := 102;
v_section_id number := 89;
v_letter_grade char(1);
begin
select case
when final_grade >= 90 then 'A'
when final_grade >= 80 then 'B'
when final_grade >= 70 then 'C'
when final_grade >= 60 then 'D'
else 'F'
end
into v_letter_grade from enrollment
where student_id = v_student_id and section_id = v_section_id;
dbms_output.put_line(v_letter_grade);
end;
4、 本地动态SQL
OPEN-FOR,FETCH和CLOSE语句被用来处理多行查询或者游标
实例一：
declare
--弱游标类型
type student_cur_type is ref cursor;
--游标变量
student_cur student_cur_type;
v_zip varchar2(5) := '&sv_zip';
v_first_name varchar2(25);
v_last_name varchar2(25);
sql_stmt varchar2(500);
begin
--select查询语句也可以放到动态SQL变量里面
/* sql_stmt := 'select first_name,last_name' ||
' from student ' ||
'where zip = :1';
open student_cur for sql_stmt using v_zip; */
open student_cur for
'select first_name,last_name
from student ' ||
'where zip = :1'
using v_zip;
loop
fetch student_cur into v_first_name,v_last_name;
exit when student_cur%NOTFOUND;
dbms_output.put_line('First Name: ' || v_first_name);
dbms_output.put_line('Last Name: ' || v_last_name);
end loop;
close student_cur;
exception
when others then
if student_cur%ISOPEN
then
close student_cur;
end if;
dbms_output.put_line('ERROR: ' || substr(SQLERRM,1,200));
end;
实例二：可以将select返回的结果放入到record记录中
set serveroutput on
declare
type zip_cur_type is ref cursor;
zip_cur zip_cur_type;
type zip_rec_type is record
(
zip varchar2(5),
total number
);
zip_rec zip_rec_type;
v_table_name varchar2(20) := '&sv_table_name';
sql_stmt varchar2(500);
v_count number;
begin
dbms_output.put_line('Totals from ' || v_table_name || ' table');
sql_stmt := 'select zip, count(*) total ' ||
' from ' || v_table_name || ' ' ||
' group by zip';
v_count := 0;
open zip_cur for sql_stmt;
loop
fetch zip_cur into zip_rec;
exit when zip_cur%NOTFOUND;
v_count := v_count + 1;
if v_count <= 10
then
dbms_output.put_line('zip: ' || zip_rec.zip);
dbms_output.put_line('total: ' || zip_rec.total);
end if;
end loop;
close zip_cur;
exception
when others then
if zip_cur%ISOPEN
then
close zip_cur;
end if;
end;
5、 批量SQL
PL/SQL和SQL引擎之间的通信称为上下文切换
FORALL语法
forall loop_counter in bounds_clause
SQL_STATEMENT[SAVE EXCEPTIONS];
bounds_clause有三种形式：
lower_limit..upper_limit
indices of collection_name between lower_limit..upper_limit
values of collection_name
当使用values of选项时，注意以下的限制：
如果values of子句中所使用的集合是联合数组,它必须使用PLS_INTEGER和BINAY_INTEGER进行索引
values of子句中所使用集合的元素必须是PLS_INTEGER或者BINARY_INTEGER
当values of子句所引用的集合是空的, ForALL子句会导致异常
实例：
create table test (row_num number, row_text varchar2(10));
declare
type row_num_type is table of number index by pls_integer;
type row_text_type is table of varchar2(10) index by pls_integer;
row_num_tab row_num_type;
row_text_tab row_text_type;
v_total number;
begin
for i in 1..10 loop
row_num_tab(i) := i;
row_text_tab(i) := 'row ' || i;
end loop;
forall i in 1..10
insert into test(row_num,row_text)
values (row_num_tab(i), row_text_tab(i));
commit;
select count(*) into v_total from test;
dbms_output.put_line(v_total);
end;
--create table test (row_num number, row_text varchar2(10));
declare
type row_num_type is table of number index by pls_integer;
type row_text_type is table of varchar2(10) index by pls_integer;
row_num_tab row_num_type;
row_text_tab row_text_type;
v_total number;
v_start_time integer;
v_end_time integer;
begin
for i in 1..10000 loop
row_num_tab(i) := i;
row_text_tab(i) := 'row ' || i;
end loop;
v_start_time := dbms_utility.get_time;
for i in 1..10000
loop
insert into test(row_num,row_text)
values (row_num_tab(i), row_text_tab(i));
end loop;
v_end_time := dbms_utility.get_time;
dbms_output.put_line('Duration of the for for loop: ' || (v_end_time-v_start_time));
v_start_time := dbms_utility.get_time;
forall i in 1..10000
insert into test(row_num,row_text)
values (row_num_tab(i), row_text_tab(i));
v_end_time := dbms_utility.get_time;
dbms_output.put_line('Duration of the for for loop: ' || (v_end_time-v_start_time));
commit;
end;
/
输出结果如下：
Duration of the for for loop: 32
Duration of the for for loop: 0
PL/SQL procedure successfully completed
注释：使用dbms_utility包的get_time函数，Oracle用户sys拥有这个包。
get_time函数会返回当前时间，精度为百分之一秒
indices of选项
可以循环处理稀疏的集合
--看下面的例子，如果直接使用forall会报错
declare
type row_num_type is table of number index by pls_integer;
type row_text_type is table of varchar2(10) index by pls_integer;
row_num_tab row_num_type;
row_text_tab row_text_type;
v_total number;
begin
for i in 1..10 loop
row_num_tab(i) := i;
row_text_tab(i) := 'row ' || i;
end loop;
row_num_tab.delete(1);row_text_tab.delete(1);
row_num_tab.delete(5);row_text_tab.delete(5);
row_num_tab.delete(7);row_text_tab.delete(7);
forall i in 1..10
insert into test(row_num,row_text)
values (row_num_tab(i), row_text_tab(i));
commit;
end;
/
ORA-22160: 下标 [1] 中的元素不存在
ORA-06512: 在 line 20
使用indices of就不会有问题：
--create table test (row_num number, row_text varchar2(10));
declare
type row_num_type is table of number index by pls_integer;
type row_text_type is table of varchar2(10) index by pls_integer;
row_num_tab row_num_type;
row_text_tab row_text_type;
v_total number;
begin
for i in 1..10 loop
row_num_tab(i) := i;
row_text_tab(i) := 'row ' || i;
end loop;
row_num_tab.delete(1);row_text_tab.delete(1);
row_num_tab.delete(5);row_text_tab.delete(5);
row_num_tab.delete(7);row_text_tab.delete(7);
forall i in indices of row_num_tab
insert into test(row_num,row_text)
values (row_num_tab(i), row_text_tab(i));
commit;
end;
/
PL/SQL procedure successfully completed
SQL> select * from test;
ROW_NUM ROW_TEXT
---------- ----------
2 row 2
3 row 3
4 row 4
6 row 6
8 row 8
9 row 9
10 row 10
7 rows selected
values of选项
实例：
drop table test_exc;
create table test_exc (row_num number,row_text varchar2(50));
truncate table test;
declare
type row_num_type is table of number index by binary_integer;
type row_text_type is table of varchar2(11) index by binary_integer;
type exc_ind_type is table of pls_integer index by binary_integer; --可以在values of中引用这个集合
row_num_tab row_num_type;
row_text_tab row_text_type;
exc_ind_tab exc_ind_type;
errors exception;
pragma exception_init(errors, -24381);
begin
for i in 1..10
loop
row_num_tab(i) := i;
row_text_tab(i) := 'row ' || i;
end loop;
row_text_tab(1) := rpad(row_text_tab(1), 11, ' ');
row_text_tab(5) := rpad(row_text_tab(5), 11, ' ');
row_text_tab(7) := rpad(row_text_tab(7), 11, ' ');
forall i in 1..10 save exceptions
insert into test (row_num,row_text)
values (row_num_tab(i),row_text_tab(i));
commit;
exception
when errors then
--统计插入表test中异常数据的行数
for i in 1.. SQL%BULK_EXCEPTIONS.COUNT
loop
--存储异常的索引值
exc_ind_tab(i) := SQL%BULK_EXCEPTIONS(i).error_index;
end loop;
forall i in values of exc_ind_tab
insert into test_exc (row_num,row_text)
values (row_num_tab(i), row_text_tab(i));
commit;
end;
/
输出结果为：
SQL> select * from test;--可以正常插入
ROW_NUM ROW_TEXT
---------- ----------
2 row 2
3 row 3
4 row 4
6 row 6
8 row 8
9 row 9
10 row 10
7 rows selected
SQL> select * from test_exc; --插入表test失败后，插入到test_exc表里面
ROW_NUM ROW_TEXT
---------- --------------------------------------------------
1 row 1
5 row 5
7 row 7
注释：
save exceptions选项：即使当对应的SQL语句导致异常，forall语句仍旧能够继续执行。这些异常
被存储在名为SQL%EXCEPTIONS的游标属性中，SQL%EXCEPTIONS游标是个集合属性，其中每个记录包含两个字段ERROR_INDEX和ERROR_CODE
使用bulk collect子句
批量检索数据
create table my_instructor as select * from instructor;
declare
type instructor_id_type is table of my_instructor.instructor_id%TYPE;
type first_name_type is table of my_instructor.first_name%TYPE;
type last_name_type is table of my_instructor.last_name%TYPE;
instructor_id_tab instructor_id_type;
first_name_tab first_name_type;
last_name_tab last_name_type;
begin
select instructor_id,first_name,last_name
bulk collect into instructor_id_tab,first_name_tab,last_name_tab
from my_instructor;
for i in instructor_id_tab.first..instructor_id_tab.last
loop
dbms_output.put_line('instructor_id: ' || instructor_id_tab(i));
dbms_output.put_line('first_name: ' || first_name_tab(i));
dbms_output.put_line('last_name: ' || last_name_tab(i));
end loop;
end;
/
输出：
instructor_id: 101
first_name: Fernand
last_name: Hanks
instructor_id: 102
first_name: Tom
last_name: Wojick
instructor_id: 103
first_name: Nina
last_name: Schorin
instructor_id: 104
first_name: Gary
last_name: Pertez
instructor_id: 105
first_name: Anita
last_name: Morris
instructor_id: 106
first_name: Todd
last_name: Smythe
bulk collect 子句类似于游标循环，因为当select语句不返回任何数据行时，它不会抛出no_data_found异常
如果将my_instructor里面的数据清空时，再执行就会报错
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在 line 15
修改上面的SQL代码：
create table my_instructor as select * from instructor;
declare
type instructor_id_type is table of my_instructor.instructor_id%TYPE;
type first_name_type is table of my_instructor.first_name%TYPE;
type last_name_type is table of my_instructor.last_name%TYPE;
instructor_id_tab instructor_id_type;
first_name_tab first_name_type;
last_name_tab last_name_type;
begin
select instructor_id,first_name,last_name
bulk collect into instructor_id_tab,first_name_tab,last_name_tab
from my_instructor;
if instructor_id_tab.count >0
then
for i in instructor_id_tab.first..instructor_id_tab.last
loop
dbms_output.put_line('instructor_id: ' || instructor_id_tab(i));
dbms_output.put_line('first_name: ' || first_name_tab(i));
dbms_output.put_line('last_name: ' || last_name_tab(i));
end loop;
end if;
end;
过程
1. 创建过程
create or replace procedure Discount
as
cursor c_group_discount
is
select distinct s.course_no, c.description
from section s, enrollment e, course c
where s.section_id = e.section_id
and c.course_no = s.course_no
group by s.course_no, c.description,
e.section_id, s.section_id
having count(*) >= 8; --course_no记录至少为8条
begin
for r_group_discount in c_group_discount
loop
update course
set cost = cost * .95
where course_no = r_group_discount.course_no;
dbms_output.put_line('A 5% discount has been given to ' ||
r_group_discount.course_no || ' ' ||
r_group_discount.description);
end loop;
end;
/
2.执行过程
execute Discount
SQL> execute Discount
A 5% discount has been given to 120 Intro to Java Programming
A 5% discount has been given to 140 Systems Analysis
A 5% discount has been given to 230 Intro to the Internet
A 5% discount has been given to 25 Intro to Programming
A 5% discount has been given to 122 Intermediate Java Programming
A 5% discount has been given to 240 Intro to the BASIC Language
查看错误
show error
比如将上面的course表写错
Warning: Procedure created with compilation errors
SQL> show error;
Errors for PROCEDURE JIANG.DISCOUNT:
LINE/COL ERROR
-------- ---------------------------------
16/16 PL/SQL: ORA-00942: 表或视图不存在
16/9 PL/SQL: SQL Statement ignored
3.从数据字典查询有关过程的信息
在数据字典中，两个重要的视图用于提供有关存储代码的信息
USER_OBJECTS会显示有关对象的信息
USER_SOURCE会显示源代码文本
数据字典也有这些视图的ALL_和DBA_版本
查询上面创建的discount存储过程信息
select object_name, object_type, status
from user_objects
where object_name = 'DISCOUNT';
列status表示存储编译的是否成功
使用USER_SOURCE视图查询DISCOUNT的源代码
SQL> desc user_source;
Name Type Nullable Default Comments
---- -------------- -------- ------- ------------------------------------------------------------------------------------------------------------------------------------
NAME VARCHAR2(30) Y Name of the object
TYPE VARCHAR2(12) Y Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY", "LIBRARY", "ASSEMBLY" or "JAVA SOURCE"
LINE NUMBER Y Line number of this line of source
TEXT VARCHAR2(4000) Y Source text
SQL> column text format a70 --设置text列的每行显示长度
SQL> select to_char(line,99) || '>', text
2 from user_source where name = 'DISCOUNT';
TO_CHAR(LINE,99)||'>' TEXT
--------------------- --------------------------------------------------------------------------------
1> procedure Discount
2> as
3> cursor c_group_discount
4> is
5> select distinct s.course_no, c.description
6> from section s, enrollment e, course c
7> where s.section_id = e.section_id
8> and c.course_no = s.course_no
9> group by s.course_no, c.description,
10> e.section_id, s.section_id
11> having count(*) >= 8; --course_no记录至少为8条
12>
13> begin
14> for r_group_discount in c_group_discount
15> loop
16> update coursse
17> set cost = cost * .95
18> where course_no = r_group_discount.course_no;
19> dbms_output.put_line('A 5% discount has been given to ' ||
20> r_group_discount.course_no || ' ' ||
TO_CHAR(LINE,99)||'>' TEXT
--------------------- --------------------------------------------------------------------------------
21> r_group_discount.description);
22> end loop;
23> end;
24>
注:如果某过程所基于的表被删除或者修改，则该过程就会变为无效。重新编译如下：
alter procedure procedure_name compile;
过程传入和传出参数
使用IN和OUT过程参数
create or replace procedure find_name
(i_student_id in number,
o_first_name out varchar2,
o_last_name out varchar2
)
as
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when others
then
dbms_output.put_line('Error in finding student_id: ' ||
i_student_id);
end find_name;
declare
v_local_first_name student.first_name%TYPE;
v_local_last_name student.last_name%TYPE;
begin
find_name(145, v_local_first_name, v_local_last_name);
dbms_output.put_line('Student 145 is: ' ||
v_local_first_name || ' ' ||
v_local_last_name || '.');
end;
/
输出：
Student 145 is: Paul Lefkowitz.
函数
创建和使用函数
函数和过程区别：
函数式能够返回单个值的PL/SQL语句块,函数可以接收一个或者多个参数，也可以不接收参数，但是函数的可执行部分
必须有一个返回子句。必须在函数的头部声明返回值的数据类型。
实例1：
create or replace function show_description
(i_course_no course.course_no%TYPE)
return varchar2
as
v_description varchar2(50);
begin
select description
into v_description
from course
where course_no = i_course_no;
return v_description;
exception
when no_data_found then
return('The Course is not in the database');
when others
then
return('Error in returning show_description');
end;
/
使用：
declare
v_description varchar2(50);
begin
v_description := show_description(&sv_number);
dbms_output.put_line(v_description);
end;
实例2：
create or replace function id_is_good
(i_student_id in number)
return boolean
as
v_id_cnt number;
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return 1 = v_id_cnt;
exception
when others
then
return false;
end id_is_good;
/
使用：
declare
v_id number;
begin
v_id := &id;
if id_is_good(v_id)
then
dbms_output.put_line('Student ID: ' || v_id || ' is a valid.');
else
dbms_output.put_line('Student ID: ' || v_id || ' is not valid.');
end if;
end;
在SQL语句中调用函数
对于在SQL表达式中使用用户定义的函数，它必须是ROW函数，而不是group函数，并且数据类型必须是SQL数据类型。
数据类型不是PL/SQL数据类型。初次之外，该函数不能使用任何的DML(insert,update,delete)
SQL> select course_no , show_description(course_no) from course;
COURSE_NO SHOW_DESCRIPTION(COURSE_NO)
--------- --------------------------------------------------------------------------------
10 Technology Concepts
20 Intro to Information Systems
25 Intro to Programming
80 Programming Techniques
100 Hands-On Windows
120 Intro to Java Programming
122 Intermediate Java Programming
124 Advanced Java Programming
125 Java Developer I
实例3：
SQL> create or replace function new_instructor_id
2 return instructor.instructor_id%TYPE
3 as
4 v_new_instid instructor.instructor_id%TYPE;
5 begin
6 select instructor_id_seq.nextval
7 into v_new_instid
8 from dual;
9 return v_new_instid;
10 exception
11 when others
12 then
13 declare
14 v_sqlerrm varchar2(250) := substr(sqlerrm,1,250);
15 begin
16 raise_application_error(-20003,'Error in instructor_id: ' || v_sqlerrm);
17 end;
18 end new_instructor_id;
19 /
Function created
SQL> declare
2 v_new_instid instructor.instructor_id%TYPE;
3 begin
4 v_new_instid := new_instructor_id();
5 dbms_output.put_line(v_new_instid);
6 end;
7 /
114
包
(1) 创建包规范
--调用此代码时，会编译但不会执行
create or replace package manage_students
as
procedure find_name
(i_student_id in student.student_id%TYPE,
o_first_name out student.first_name%TYPE,
o_last_name out student.last_name%TYPE
);
function id_is_good
(i_student_id in student.student_id%TYPE)
return boolean;
end manage_students;
/
创建名为school_api的包规范
create or replace package school_api
as
procedure discount;
function new_instructor_id
return instructor.instructor_id%TYPE;
end school_api;
/
(2) 创建包体
create or replace package body manage_students
as
procedure find_name
(i_student_id in student.student_id%TYPE,
o_first_name out student.first_name%TYPE,
o_last_name out student.last_name%TYPE
)
is
v_student_id student.student_id%TYPE;
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when others
then
dbms_output.put_line('Error in finding student_id: ' ||
i_student_id);
end find_name;
function id_is_good
(i_student_id in student.student_id%TYPE)
return boolean
as
v_id_cnt number;
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return 1 = v_id_cnt;
exception
when others
then
return false;
end id_is_good;
end manage_students;
/
(3) 调用存储包
set serveroutput on
declare
v_first_name student.first_name%TYPE;
v_last_name student.last_name%TYPE;
begin
if manage_students.id_is_good(&&v_id)
then
manage_students.find_name(&&v_id, v_first_name, v_last_name);
dbms_output.put_line('Student No. ' ||
&&v_id ||' is ' ||
v_last_name || ' ' ||
v_first_name);
else
dbms_output.put_line('Student ID: ' ||
&&v_id ||' is not in the database');
end if;
end;
/
输入145，结果为：
Student No. 145 is Lefkowitz Paul
(4) 创建私有对象
首先创建包规范
create or replace package manage_students
as
procedure find_name
(i_student_id in student.student_id%TYPE,
o_first_name out student.first_name%TYPE,
o_last_name out student.last_name%TYPE
);
function id_is_good
(i_student_id in student.student_id%TYPE)
return boolean;
procedure display_student_count;
end manage_students;
/
再创建包体
create or replace package body manage_students
as
procedure find_name
(i_student_id in student.student_id%TYPE,
o_first_name out student.first_name%TYPE,
o_last_name out student.last_name%TYPE
)
is
v_student_id student.student_id%TYPE;
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when others
then
dbms_output.put_line('Error in finding student_id: ' ||
i_student_id);
end find_name;
function id_is_good
(i_student_id in student.student_id%TYPE)
return boolean
as
v_id_cnt number;
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return 1 = v_id_cnt;
exception
when others
then
return false;
end id_is_good;
function student_count_priv
return number
is
v_count number;
begin
select count(*)
into v_count from student;
return v_count;
exception
when others then
return(0);
end student_count_priv;
procedure display_student_count
is
v_count number;
begin
v_count := student_count_priv;
dbms_output.put_line('There are ' || v_count || ' students.');
end display_student_count;
end manage_students;
注释：添加一个私有函数student_count_priv和一个调用私有过程的公开过程display_student_count.
student_count_priv没有在包规范中定义，为私有。
declare
v_count number;
begin
v_count := manage_students.student_count_priv;
dbms_output.put_line(v_count);
end;
ORA-06550: 第 5 行, 第 32 列:
PLS-00302: 必须声明 'STUDENT_COUNT_PRIV' 组件
ORA-06550: 第 5 行, 第 5 列:
PL/SQL: Statement ignored
执行如下代码：
SQL> execute manage_students.display_student_count;
There are 268 students
注意：
如果在包规范中包含某过程或者函数，它就变成私有的。在另一方面，如果在包规范中声明了一个过程
或者函数，然后当创建包体时不定义它，就会报错
- pl/sql 笔记1
- PL/SQL笔记1
- PL/SQL 学习笔记1
- PL/SQL学习笔记（1）
- Oracle PL/SQL 学习笔记（1）
- PL/SQL学习笔记[1]-基础
- 第1 章 PL/SQL简介 笔记
- PL/SQL学习笔记[1]-基础
- PL/SQL 学习笔记（1）
- PL/SQL学习笔记
- PL/SQL学习笔记
- PL/SQL学习笔记
- PL/SQL学习笔记
- PL/SQL学习笔记
- PL/SQL学习笔记
- 笔记081120----PL/SQL
- PL/SQL学习笔记
- PL/SQL学习笔记
- linux解压命令
- acm-C++经典程序训练---斐波那契（Fibonacci）数列
- python_修改配置文件参数
- cmd下sqlplus命令登录失败
- java版的压缩
- PL/SQL笔记1
- 如何写一个sevlet
- Spring声明式事务配置管理方法
- PyQT中ui界面转换python脚本pyuic4的参数小结
- Linux环境下安装压缩版MYSQL5.5
- ios中绘制渐变色
- android主流UI布局
- equals()与hashCode()方法协作约定
- 指针高级
