Chương 4. Index, view, procedure, function và package

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;