Chương 3. PL/SQL

08/02/2012 1 Chương 3. PL/SQL 1 Nội dung  Sử dụng PL/SQL  Các khái niện về PL/SQL  Các quy tắc cơ bản lập trình PL/SQL  Sequence của Oracle  Điều khiển có điều kiện  Điều khiển lặp  Các ngoại lệ  Cursor 2 08/02/2012 2 Các khái niện về PL/SQL  Kiến trúc client/server.  Cấu trúc khối PL/SQL 3 Kiến trúc PL/SQL trong client/server Ứng dụng sử dụng SQL Ứng dụng sử dụng PL/SQL Bộ xử lý phát triển SQL Bộ máy PL/SQL Client Server 4 08/02/2012 3 Sử dụng PL/SQL  Cách 1: trực tiếp gõ vào SQLPlus begin dbms_output.put_line('Hello World!); end; / 5 Sử dụng PL/SQL  Cách 2: dùng notepad tạo e:\hello.sql begin dbms_output.put_line('Hello World!); end; / SQL>@:\hello.sql 6 08/02/2012 4 Sử dụng PL/SQL  Cách 3: dùng tool SQLDeveloper 7 Các khái niện về PL/SQL 8  Cấu trúc khối PL/SQL  Thay đổi giá trị trong biến 08/02/2012 5 Cấu trúc khối PL/SQL DECLARE Các phát biểu khai báo BEGIN Các phát biểu thực thi EXCEPTION Các phát biểu xử lý ngoại lệ END; Phần khai báo Phần thực thi Phần ngoại lệ 9 Ví dụ declare v_first_name varchar2(35); v_last_name varchar2(35); begin select first_name,last_name into v_first_name,v_last_name from student where student_id=123; dbms_output.put_line('Student name: '||v_first_name||' ‘ ||v_last_name); exception when no_data_found then dbms_output.put_line('there is no student with student 123'); end; 10 08/02/2012 6 Thay đổi giá trị trong biến declare v_student_id NUMBER:=&sv_student_id v_first_name varchar2(35); v_last_name varchar2(35); begin select first_name,last_name into v_first_name,v_last_name from student where student_id= v_student_id; dbms_output.put_line('Student name: '||v_first_name||' ‘ ||v_last_name); exception when no_data_found then dbms_output.put_line('there is no student such student’); end; 11 Thay đổi giá trị trong biến (tt) begin dbms_output.put_line('today is '||'&sv_day'); dbms_output.put_line('tomorrow will is '||'&sv_day'); end; 12 08/02/2012 7 Thay đổi giá trị trong biến (tt)  Thiết lập giá trị SET VERIFY OFF SET VERIFY OFF begin dbms_output.put_line('today is '||'&sv_day'); dbms_output.put_line('tomorrow will is '||'&sv_day'); end; 13 Các quy tắc cơ bản lập trình PL/SQL  Định danh cho biến  Kiểu dữ liệu đối tượng  Khởi tạo giá trị ban đầu 14 08/02/2012 8 Định danh cho biến DECLARE v_var1 varchar2(20); v_var2 number(5,3); BEGIN v_var1:='hello'; v_var2:=12.5; dbms_output.put_line('v_var1:'||v_var1); dbms_output.put_line('v_var2:'||v_var2); END; 15 Kiểu dữ liệu đối tượng  Kiểu dữ liệu tham chiếu đến trực tiếp một cột trong CSDL  Cú pháp: .%TYPE  Ví dụ: DECLARE v_name student.first_name%type; vgrade grade.numeric_grade%type; BEGIN dbms_output.put_line(NVL(v_name, „No Name‟) || „has grade of ‟|| NVL(v_grade,0)); END; 16 08/02/2012 9 Khởi tạo giá trị ban đầu Sử dụng “:=” DECLARE v_cookies_amt Number:=2; v_cookies_per_cookie CONSTANT Number:=300; BEGIN dbms_output.put_line(v_cookies_amt* v_cookies_per_cookie); END; 17 Khởi tạo giá trị ban đầu (tt) Ví dụ: Declare V_average_cost Varchar2(10); Begin Select To_char(AVG(cost),'$9,999.99') Into v_average_cost From course; DBMS_output.put_line('The average cost is '||v_average_cost); End; Select item_name Into variable_name From table_name 18 08/02/2012 10 Sequence của Oracle  Sequence: dùng để tạo tự động các giá trị trong thuộc tính của dữ liệu 19 Sequence của Oracle (tt)  Trong dó:  Sequence: tên sequence  Increment by n: sô bước nhảy  Start with n: số bắt đầu  Maxvalue n | Nomaxvalue: giá trị lớn nhất  Minvalue n | Nominvalue: giá trị nhỏ nhấtt  Cycle| Nocycle: khi đạt đến max se quay lui hoặc tiêp tục tăng. Mặc định là Nocycle  Cache| Nocache: phát sinh vùng nhớ mặc định cache có 20 giá trị 20 08/02/2012 11 Sequence của Oracle (tt) Ví dụ CREATE SEQUENCE test_seq increment by 1 start with 51 maxvalue 9999 nocache nocycle; 21 Sequence của Oracle (tt) Ví dụ tạo bảng sau: Create table test(col1 number); Lấy các số từ sequence Insert into test values(test_seq.Nextval); - Nextval: trả về giá trị sequence kế tiếp - Currval: trả về giá trị hiện hành của sequence 22 08/02/2012 12 Sequence của Oracle (tt)  Cú pháp thay đổi các giá trị trong Sequence  Cú pháp xóa Sequence DROP SEQUENCE sequence_name 23 Điều khiển có điều kiện  Phát biểu IF-THEN  Phát biểu IF-THEN-ELSE  Phát biểu ELSEIF  Phát biểu CASE  Phát biểu CASE tìm kiếm 24 08/02/2012 13 Phát biểu IF-THEN Declare v_num1 NUMBER:=5; v_num2 NUMBER:=2; v_temp NUMBER; Begin IF v_num1>v_num2 THEN v_temp=v_num1; v_num1=v_num2; v_num2=temp; END IF; dbms_output.put_line(„v_num1=‟||v_num1); dbms_output.put_line(„v_num2=‟||v_num2); end IF THEN khối lệnh; END IF; 25 Phát biểu IF-THEN-ELSE DECLARE v_num Number:=&sv_user_num; BEGIN IF MOD(v_num,2)=0 then dbms_output.put_line(v_num|| „la so chan‟); ELSE dbms_output.put_line(v_num|| „la so lẻ‟); END IF; END; IF THEN phát biểu 1; ELSE phát biểu 2; END IF; phát biểu 3; 26 08/02/2012 14 Phát biểu ELSIF IF THEN Phát biểu 1; ELSIF THEN Phát biểu 2; ELSIF THEN Phát biểu 3; ELSE Phát biểu n; END IF; 27 Phát biểu ELSIF  Ví dụ: DECLARE v_num NUMBER:=&sv_num; BEGIN IF v_num<0 THEN dbms_output.put_line(v_num||' la so am'); ELSIF v_num=0 THEN dbms_output.put_line(v_num||' la so khong'); ELSE dbms_output.put_line(v_num||' la so duong'); END IF; END; 28 08/02/2012 15 Phát biểu CASE Phát biểu CASE có 2 dạng:  CASE  CASE tìm kiếm 29 CASE CASE SELECTOR WHEN Expression1 then Statement 1; WHEN Expression2 then Statement 2; WHEN Expression2 then Statement n; ELSE Statement n+1; END CASE 30 08/02/2012 16 Ví dụ Declare v_num NUMBER:=&sv_user_num; v_num_flag NUMBER; BEGIN v_num_flag:=MOD(v_num,2); case v_num_flag when 0 then dbms_output.put_line(v_num||„la so chan‟); else when 0 then dbms_output.put_line(v_num||„la so le‟); end case; END 31 CASE TÌM KIẾM CASE WHEN THEN phát biểu 1; WHEN THEN phát biểu 2; WHEN THEN phát biểu n; ELSE phát biểu n+1; END CASE 32 08/02/2012 17 Ví dụ Declare v_num NUMBER:=&sv_user_num; BEGIN case when MOD(v_num,2)=0 then dbms_output.put_line(v_num||„la so chan‟); else dbms_output.put_line(v_num||„la so le‟); end case; END 33 Điều khiển lặp  LOOP  WHILE  FOR 34 08/02/2012 18 LOOP  Cú pháp EXIT LOOP phát biểu 1; phát biểu 2; IF THEN EXIT; END IF; END LOOP; • Cú pháp EXIT WHEN LOOP phát biểu 1; phát biểu 2; EXIT WHEN <điều kiện> END LOOP; 35 Ví dụ set serveroutput on declare v_counter binary_integer:=0; begin LOOP v_counter:=v_counter+1; dbms_output.put_line('v_couter='||v_counter); if v_counter=5 then exit; end if; END LOOP ; dbms_output.put_line('Done'); end; 36 08/02/2012 19 WHILE  Cú pháp WHILE LOOP phát biểu 1; phát biểu 2; phát biểu n; END LOOP; 37 Ví dụ set serveroutput on declare v_counter binary_integer:=1; begin while v_counter<5 LOOP dbms_output.put_line('v_couter='||v_counter); v_counter:=v_counter+1; END LOOP ; end; 38 08/02/2012 20 FOR  Cú pháp FOR loop_counter IN [REVERSE] lower_limit .. upper_limit LOOP phát biểu 1; phát biểu 2; phát biểu n; END LOOP; 39 Ví dụ set serveroutput on declare v_counter binary_integer; begin for v_counter in 1..5 loop dbms_output.put_line('v_counter= '||v_counter); end loop; end; 40 08/02/2012 21 Những ngoại lệ  Danh sách các lỗi ngoại lệ  Ngoại lệ do người dùng định nghĩa  RAISE_APPLICATION_ERROR 41 Danh sách các lỗi ngoại lệ Lỗi Diễn giải NO_DATA_FOUND Không trả về dòng nào TOW_MANY_ROW Trả về nhiều dòng ZERO_DIVIDE Chia cho zero LOGIN_DENIED Kết nối vào Oracle với tên và mật khẩu không hợp lệ PROGRAM_ERROR Chương trình PL/SQL có lỗi bên trong INVALID_NUMBER Kiểu dữ liệu về số không hợp lệ VALUE_ERROR Kích thước kiểu dữ liệu không đủ 42 08/02/2012 22 Ngoại lệ do người dùng định nghĩa  Ngoại lệ do người dùng định nghĩa phải được kích hoạt từng minh  Khai báo ngoại lệ Declare Tên ngoại lệException;  Ví dụ: Declare e_invalid Exception; 43 Ngoại lệ do người dùng định nghĩa Declare exception_name EXCEPTION Begin if then RAISE exception_name; else end if; EXCEPTION when exception_name then Error-processing statement; End; 44 08/02/2012 23 Ví dụ Declare v_student_id STUDENT.STUDENT_ID%TYPE:=&sv_student_id; v_total_course NUMBER; e_invalid_id EXCEPTION; BEGIN if v_student_id<0 then raise e_invalid_id; else select count(*) into v_total_course from enrollment where student_id=v_student_id; dbms_output.put_line('The student is registered for '||v_total_course||' courses'); end if; dbms_output.put_line('No exception has been raised'); exception when e_invalid_id then dbms_output.put_line('An id cannot be negation'); END; 45 RAISE_APPLICATION_ERROR  RAISE_APPLICATION_ERROR là một thủ tục dựng sẵn đặc biệt do Oracle cung cấp.  Cú pháp: RAISE_APPLICATION_ERROR (error_number, error_message) - error_number: số hiệu lỗi giá trị từ -20,999 đến - 20,000 - error_message: nội dung lỗi 46 08/02/2012 24 Ví dụ Declare v_student_id STUDENT.STUDENT_ID%TYPE:=&sv_student_id; v_total_course NUMBER; BEGIN if v_student_id<0 then raise_application_error(-20000, ‘An id cannot be negative’); else select count(*) into v_total_course from enrollment where student_id=v_student_id; dbms_output.put_line('The student is registered for '||v_total_course||' courses'); end if; END; 47 Ví dụ Cho biết có bao nhiêu sinh viên đăng ký trong mỗi môn học. Hiển thị mã số môn học cho số lượng sinh viên đăng ký cho môn đó Declare cursor course_cur is select course_no, section_id from section order by course_no, section_id; v_cur_course section.course_no%TYPE:=0; v_student number(3):=0; v_total number(3):=0; begin for course_rec IN course_cur loop if v_cur_course=0 then v_cur_course:=course_rec.course_no; end if; 48 08/02/2012 25 select count(*) into v_student from enrollment where section_id=course_rec.section_id; if v_cur_course=course_rec.course_no then v_total:=v_total+v_student; if v_total>20 then raise_application_error(-20002,'Course '||v_cur_course||' has too many student' ); end if; else dbms_output.put_line('Course '||v_cur_course||' has'||v_total||' students'); v_cur_course:=course_rec.course_no; v_total:=0; end if; end loop; dbms_output.put_line('Done...'); end; 49 CURSOR  Cursor là một vùng nhớ cho phép bạn cấp phát bộ nhớ và truy cập thông tin được trích từ một phát biểu SQL.  Cursor là một con trỏ trỏ đến vùng nhớ.  Có 2 loại cursor:  Cursor ngầm định  Cursor tường minh 50 08/02/2012 26 CURSOR(tt)  Cursor ngầm định: được tự động khai báo bở Oracle. Người dùng không nhận thấy điều khiển xảy ra và cũng không thể điều khiển hoặc xử lý thông tin trong cursor ngầm định.  Cursor ngầm định xử lý các phát biểu INSERT, UPDATE, DELETE và phát biểu SELECt INTO. Trong quá trình xử lý cursor ngầm định Oracle tự động thực hiện các thao tác OPEN, FETCH, CLOSE 51 Ví dụ set serveroutput on begin update student set first_name='B' where first_name like 'B%'; dbms_output.put_line(SQL%ROWCOUNT); end; Cursor ngầm định không cho biết có biêu nhiêu dòng bị ảnh hưởng bởi thao tác cập nhật. SQL%ROWCOUNT: trả về số dòng được cập nhật 52 08/02/2012 27 CURSOR(tt)  Cursor tường minh: cung cấp nhiều khả năng điều khiển và dễ dàng trong việc bẫy lỗi dữ liệu.  Quy trình làm việc của cursor tường minh  Khai báo cursor  Mở cursor  Lấy dữ liệu về từ cursor.  Đóng cursor 53 CURSOR(tt)  Khai báo: Cursor IS phát biểu Select  Ví dụ: DECLARE Cursor C_MyCursor IS SELECT * FROM ZIPCODE WHERE state =„NY‟ 54 08/02/2012 28 CURSOR(tt)  Mở một cursor OPEN cursor_name  Ví dụ: Open C_MyCursor 55 CURSOR(tt) • Lấy dữ liệu về từ cursor. FETCH cursor_name INTO ; hoặc FETCH cursor_name INTO record; 56 08/02/2012 29 CURSOR(tt) DECLARE Cursor C_MyCursor IS SELECT * FROM ZIPCODE WHERE state =„NY‟ vr_zip C_MyCursor%ROWTYPE Begin open c_zip; loop FETCH c_zip INTO vr_zip; EXIT WHEN C_MyCursor%NOTFOUND; dbms_output.put_line(vr_zip.zip||‟ „||vr_zip.city||‟ „|| vr_zip.state); end loop; End; 57 CURSOR(tt)  Đóng cursor CLOSE Ví dụ: CLOSE C_MyCursor 58 08/02/2012 30 Ví dụ DECLARE CURSOR c_student_name IS SELECT first_name,last_name FROM student WHERE rownum<=5; vr_student_name c_student_name%ROWTYPE; BEGIN OPEN c_student_name; LOOP FETCH c_student_name INTO vr_student_name; EXIT WHEN c_student_name%NOTFOUND; dbms_output.put_line('Student name: '||vr_student_name.first_name||' '||vr_student_name.last_name); END LOOP; CLOSE c_student_name; END; Student name: George Kocka Student name: Janet Jung Student name: Kathleen Mulroy Student name: Joel Brendler Student name: Michael Carcia 59 Các thuộc tính cursor Thuộc tính Cursor Cú pháp Diễn giải %NOTFOUND Cursor_name%NOTFOUND Trả về TRUE nếu không có dòng nào %FOUND Cursor_name%FOUND Trả về TRUE nếu có 1 dòng %ROWCOUNT Cursor_name%ROWCOUNT Trả về số lượng record %ISOPEN Cursor_name%ISOPEN Trả về TRUE nếu cursor đang mở 60 08/02/2012 31 Ví dụ DECLARE v_sid student.student_id%TYPE; CURSOR c_student IS SELECT student_id FROM student WHERE student_id<110; BEGIN OPEN c_student; LOOP FETCH c_student INTO v_sid; dbms_output.put_line('student id: '||v_sid); EXIT WHEN c_student%NOTFOUND; END LOOP; CLOSE c_student; EXCEPTION when others then if c_student%isopen then close c_student; end if; END; 61