Chương 4. Index, view, procedure, function và package
Ví dụ dùng con trỏ (tt)
exception when others then
declare v_sqlerrm
varchar2(250):=substr(sqlerrm,1,250);
begin
raise_application_error(-20003,' Error in
instuctor_id'||v_sqlerrm);
end;
end new_instructor_id;
end school_api;
23 trang |
Chia sẻ: vutrong32 | Lượt xem: 1115 | Lượt tải: 0
Bạn đang xem trước 20 trang tài liệu Chương 4. Index, view, procedure, function và package, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
09/02/2012
1
1
Chương 4. INDEX, VIEW, PROCEDURE,
FUNCTION VÀ PACKAGE
NỘI DUNG
2
INDEX
VIEW
PROCEDURE
FUNCTION
PACKAGE
09/02/2012
2
INDEX
3
Index được dùng để truy xuất dữ liệu nhanh hơn
Tập tin chỉ mục gồm:
Tập tin chỉ mục được sắp xếp thứ tự theo khóa tìm
kiếm
pointer search-key
INDEX (tt)
4
Cú pháp tạo Index
CREATE INDEX tên_index ON
QUAN_HE(tên_thuộc_tính)
Ví dụ:
CREATE INDEX STd_idx ON Student(first_name)
Cú pháp xóa Index
DROP INDEX Tên_index
Ví dụ
DROP INDEX STd_idx
09/02/2012
3
INDEX (tt)
5
Tạo Index trong các trường hợp sau:
Trên những dữ liệu lớn và thường xuyên tìm kiếm
Những cột thường được sử dụng trong mệnh đề
where để liên kết cột này với cột khác.
Những cột có miền giá trị lớn và nhiều.
Những cột có giá trị null lớn
Những truy vấn có giá trị trả về >2% số dòng của
bảng
Không nên tạo index trong các trường hợp sau
Các bảng nhỏ
Các cột thường không sử dụng trong mệnh đề
where
Các bảng thường xuyên cập nhật dữ liệu
VIEW
6
View được xem như là bảng ảo. Cho phép người
dùng viết lại các ứng dụng dễ dàng hơn vì cho phép
định nghĩa CSDL theo cách nhìn của người viết và
tăng cường tính độc lập dữ liệu
09/02/2012
4
VIEW (tt)
7
VIEW (tt)
8
Những thuận lợi của view
09/02/2012
5
VIEW (tt)
9
VIEW (tt)
10
View_name: tên view
Subquery: chứa câu lệnh select
With check option: dùng để ngăn cản các thao tác cập nhật
dữ liệu (thêm, sửa) trực tiếp vào view làm ảnh hưởng đến
dữ liệu đối với các view có sử dụng mệnh đề where
With read only: không cho phép các câu lệnh DML thực hiện
trên view
09/02/2012
6
Ví dụ
11
Xem cấu trúc của view
Ví dụ
12
09/02/2012
7
PROCEDURE
13
Việc tạo ra thủ tục ở mức CSDL nhằm làm tăng tốc
độ xử lý của máy tính, tận dụng tối đa những tài
nguyên mà DBMS cung cấp.
Thủ tục có thể tạo ra các xử lý chung và lưu trữ
trong CSDL của ORACLE. Từ đó, các ứng dụng
khác của máy trạm có thể truy xuất đến thủ tục này
dễ dàng và hiệu quả
PROCEDURE (tt)
14
Create Or Replace Procedure tên
[parameter_1, parameter_2, parameter_n]
AS [IS]
[phần khai báo]
Begin
Các câu lệnh PL/SQL
[EXCEPTION
các ngoại lệ]
End [tên];
09/02/2012
8
Ví dụ
15
Cập nhật giảm 5% cho những khóa có học phần được 8
sinh viên đăng ký trở lên.
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
having count(*)>=8;
Ví dụ (tt)
16
begin
for r_group_discount IN c_group_discount
LOOP
update course
set cost=cost*0.95
where course_no=r_group_discount.course_no;
dbms_output.put_line('A 5% discount has given to
'||r_group_discount.course_no||'
'||r_group_discount.description);
end loop;
end;
09/02/2012
9
Ví dụ
17
Gọi thực thi
execute discount
PROCEDURE (tt)
18
Chế độ Diễn giải Cách sử dụng
IN Truyền một giá trị vào
chương trình
- Giá trị chỉ đọc
- Các biểu thức ràng buộc
- Không thể thay đổi trong chương
trình, chế độ mặc định
OUT Truyền một giá trị trả về từ
chương trình
-Giá trị chỉ ghi
- Không thể gán các giá trị mặc định
- Phải là một biến
- Giá trị chỉ được gán khi chương
trình thành công
INOUT Truyền giá trị vào và trả về
giá trị
-Phải là một biến
- Các giá trị sẽ được đọc rồi ghi
09/02/2012
10
PROCEDURE (tt)
19
PROCEDURE CALL
Procedure FIND_NAME(ID IN NUMBER, NAME OUT VARCHAR2)
EXECUTE FIND_NAME(127, NAME)
Ví dụ
20
create or replace procedure Find_sname
(i_student_id 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 no_data_found then
dbms_output.put_line('Error in finding student_id:
'||i_student_id);
end;
09/02/2012
11
Ví dụ
21
declare
v_local_first_name student.first_name%TYPE;
v_local_last_name student.last_name%TYPE;
begin
Find_sname(1, v_local_first_name,v_local_last_name);
dbms_output.put_line('Student 145 is:
'||v_local_first_name||' '||v_local_last_name||'.');
end;
FUNCTION
22
Hàm là loại mã lệnh được truy vấn giống thủ tục. Điểm
khác biệt quan trọng hàm là một khối lệnh trả về chỉ một
giá trị.
Cú pháp:
Create or Replace Function tên_hàm
(danh sách các tham số)
RETURN kiểu dữ liệu
IS
BEGIN
thân của hàm
RETURN (giá trị trả về)
END;
09/02/2012
12
FUNCTION (tt)
23
Ví dụ: Nhập vào mã khóa nếu tìm thấy cho biết
thông tin mô tả của khóa đó ngược lại sẽ thông
báo nếu không tìm thấy
CREATE OR REPLACE FUNCTION show_description
(i_course_no course.course_no%TYPE)
Return varchar2
is
v_description varchar2(50);
begin
select description
into v_description
from course
where course_no=i_course_no;
return v_description;
FUNCTION (tt)
24
exception
when no_data_found then
return('the Course is not in the database');
when others then
return('Error in running show_description');
end;
Gọi hàm
declare des course.description%type;
begin
des:=show_description(17);
dbms_output.put_line(des);
end;
09/02/2012
13
FUNCTION (tt)
25
Ví dụ: nhập vào mã sinh viên cho biết sinh viên này có
không.
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;
FUNCTION (tt)
26
Gọi hàm
declare v_id_is_good boolean;
begin
v_id_is_good:=id_is_good(&sv);
if v_id_is_good=false then
dbms_output.put_line('khong co sinh vien nay');
else
dbms_output.put_line('co sinh vien nay');
end if;
end;
09/02/2012
14
PACKAGE
27
Package là một tập hợp các đối tượng được nhóm lại
với nhau thành một tên. Package bao gồm các thủ tục,
hàm, cursor, các khai báo, các kiểu và biến.
Package được sử dụng trong các ứng dụng mà các thủ
tục và hàm thường được sử dụng lặp đi lặp lại.
Package gồm 2 phần
Phần mô tả
Phần thân
PACKAGE
28
Phần mô tả: chứa thông tin về nội dung của
package nhưng không phải mã lệnh của các thủ
tục hoặc hàm.
Tất cả những đối tượng đặt trong phần mô tả của
package được gọi là các đối tượng dùng chung.
09/02/2012
15
PACKAGE
29
CREATE PACKAGE package_name AS
package_specification
public type and object declaretion
subprogram definition
END [package_name];
Ví dụ
30
create or replace package manager_students
as
procedure find_name
(i_student_id IN student.student_id%type,
o_first_name OUT student.first_name%type,
o_lasr_name OUT student.last_name%type);
function id_s_good
(i_student_id IN student.student_id%type)
return boolean;
end;
09/02/2012
16
PACKAGE
31
Phần thân: chứa mã lệnh có thể thực thi thật sự của các
đối tượng được chỉ định trong phần mô tả của package
Một số quy tắc trong phần thân:
Phải có sự so khớp tuyệt đối giữa cursor, các tiêu đề của
module và những định nghĩa trong phần mô tả.
Không lặp lại phần khai báo trong phần thân đối với các
biến, ngoại lệ hoặc các hằng đã khai báo trong phần mô tả.
Bất kỳ phần nào đã khai báo trong phần mô tả có thể được
tham chiếu trong phần thân
PACKAGE
32
CREATE PACKAGE BODY package_name AS
package_body
private type and object declaretion
subprogram bodies
[BEGIN
initialitation statements;
END];
END [package_name];
09/02/2012
17
Ví dụ
33
create or replace package body manager_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:
'||v_student_id);
end find_name;
.
Ví dụ (tt)
34
.
function id_is_good
(i_student_id IN student.student_id%type)
return boolean
is
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 manager_students;
09/02/2012
18
Ví dụ (tt)
35
Gọi package
declare
v_first_name student.first_name%type;
v_last_name student.last_name%type;
begin
if manager_students.id_is_good(&v_id) then
manager_students.find_name(&v_id,v_first_name,v_last_name);
dbms_output.put_line('Student No: '||&v_id||'
'||v_last_name||','||v_first_name);
else
dbms_output.put_line('Student ID: '||&v_id||' is not in the database.');
end if;
end;
PACKAGE (tt)
36
Các thành phần chung và riêng của package:
Các thành phần chung là các thành phần được định nghĩa
mô tả trong của package
Một đối tượng được định nghĩa trong thân của package, nó
sẽ là đối tượng dùng riêng.
Các thành phần dùng riêng không thể được truy xuất trực
tiếp bằng bất kỳ chương trình nào ở ngoài package.
09/02/2012
19
Ví dụ
37
Thêm vào phần mô tả package với thủ tục:
procedure display_student_count;
create or replace package manager_students
as
procedure find_name
(i_student_id IN student.student_id%type,
o_first_name OUT student.first_name%type,
o_lasr_name OUT student.last_name%type);
function id_s_good
(i_student_id IN student.student_id%type)
return boolean;
procedure display_student_count;
end;
Ví dụ
38
create or replace package body manager_students
as
procedure find_name
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;
09/02/2012
20
Ví dụ
39
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 manager_students;
Ví dụ
40
Gọi thực thi
set serveroutput on
execute manager_students.display_student_count;
Gọi thực thi
declare v_count number;
begin
v_count:=Manager_students.student_count_priv;
dbms_output.put_line('v_count');
end;
Không gọi bên
ngoài package
được
09/02/2012
21
Ví dụ dùng con trỏ
41
create or replace package school_api as
procedure discount;
function new_instructor_id
return instructor.instructor_id%type;
end school_api;
42
create or replace package body school_api as
procedure discount is
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
having count(*)>=8;
Ví dụ dùng con trỏ (tt)
09/02/2012
22
Ví dụ dùng con trỏ (tt)
43
begin
for r_group_discount IN c_group_discount
LOOP
update course
set cost=cost*0.95
where course_no=r_group_discount.course_no;
dbms_output.put_line('A 5% discount has given
to '||r_group_discount.course_no||'
'||r_group_discount.description);
end loop;
end;
Ví dụ dùng con trỏ (tt)
44
function new instructor_id
return instructor.instructor_id%type
is
v_new_instid instructor.instructor_id%type;
begin
select instructor_id_seq.nextval
into v_new_instid
from dual;
return v_new_instid;
09/02/2012
23
Ví dụ dùng con trỏ (tt)
45
exception when others then
declare v_sqlerrm
varchar2(250):=substr(sqlerrm,1,250);
begin
raise_application_error(-20003,' Error in
instuctor_id'||v_sqlerrm);
end;
end new_instructor_id;
end school_api;
Các file đính kèm theo tài liệu này:
- chuong_4_1269.pdf