NGÔN NGỮ THỦ TỤC - PL/SQL
- Lý thuyết: 6 tiết
- Thực hành: 6 tiết
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-1
NGÔN NGỮ THỦ TỤC – PL/SQL
9.1 Giới thiệu PL/SQL
9.2 Con trỏ (Cursor)
9.3 Thủ tục và hàm
9.4 Quản lý đối tượng lớn
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-2
Giới thiệu PL/SQL
- Mở rộng của SQL
- Mã PL/SQL lưu trữ trực tiếp trong csdl
- Giao tiếp với csdl
cho phép tích hợp lệnh SQL và các ngôn ngữ lập trình.
sử dụng cả trong cơ sở dữ liệu và lập trình ứng dụng.
dùng để kết hợp các lệnh xử lý chuyển tác.
hỗ trợ nhiều thủ tục hơn SQL.
đơn vị mã lệnh PL/SQL là dạng khối (Begin… End).
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-3
Giới thiệu PL/SQL
Khai báo biến
Identifier [CONSTANT] datatype [NOT NULL]
[:=| DEFAULT expr]
Ví dụ:
V_Hiredate
DATE;
V_DeptNo
NUMBER(3) NOT NULL := 10;
V_Loc
VARCHAR2(13) := ‘Atlanta’;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-4
Giới thiệu PL/SQL
- Khai báo khối
[DECLARE
Khai báo biến, con trỏ,…]
BEGIN
- Lệnh SQL;
- Lệnh PL/SQL;
[EXCEPTION Xử lý ngoại lệ xảy ra;]
END;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-5
Giới thiệu PL/SQL
Ví dụ:
DECLARE
v VARCHAR2(5);
BEGIN
SELECT Ename
INTO v
FROM Emp;
EXCEPTION
WHEN others THEN
Message(SQLERRM);
END;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-6
Giới thiệu PL/SQL
Ví dụ: Lấy dữ liệu cột DeptId và cột Loc của phòng SALES trong
bảng Dept đưa vào hai biến v_deptno và v_loc. Nếu có lỗi xuất hiện
thì xuất dòng thông báo mã lỗi ra màn hình.
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-7
Giới thiệu PL/SQL
DECLARE
v_deptno
NUMBER(2);
v_loc
VARCHAR2(15);
BEGIN
SELECT DeptId, Loc
INTO v_deptno, v_loc
FROM Dept
WHERE Dname = 'SALES';
EXCEPTION
WHEN others THEN
Message(SQLERRM);
END;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-8
Giới thiệu PL/SQL
Thuộc tính %Type
- %Type sử dụng khi lưu trữ giá trị nhận được từ một bảng
- đặt %Type ngay sau tên bảng và tên cột
identifier Table.column%Type;
Ví dụ:
DECLARE
v_deptno
Dept.DeptNo%Type;
v_loc
VARCHAR2(15);
v_name
Dept.Dname%Type;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9-9
Giới thiệu PL/SQL
- Một số chú ý
PL/SQL không hỗ trợ các kiểu dữ liệu mảng.
Biến phải được khái báo trước khi tham chiếu đến.
Kiểu dữ liệu trong phần khai báo có thể có hoặc không.
Trong một khối lệnh các biến không được trùng tên.
Tên biến không được trùng với tên cột của bảng dữ liệu sử
dụng trong khối.
Khởi tạo biến phải chỉ rõ như NOT NULL, CONSTANT.
Khai báo mỗi biến trên 1 hàng.
Khởi tạo giá trị cho biến bằng cách sử dụng phép gán (:=)
hoặc từ khóa DEFAULT
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 10
Giới thiệu PL/SQL
Các kiểu dữ liệu thông dụng
CHAR [(max_lenght)]
DATE
VARCHAR2 (max_lenght)
TIMESTAMP
LONG
TIMESTAMP WITH TIME
ZONE
LONG RAW
NUMBER[(precision, scale)]
BINARY_INTEGER
TIMESTAMP WITH LOCAL
TIME ZONE
PLS_INTEGER
INTERVAL YEAR TO MONTH
BOOLEAN
INTERVAL DAY TO SECOND
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 11
Giới thiệu PL/SQL
Khối lồng nhau và phạm vi biến
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 12
Giới thiệu PL/SQL
- Các phép toán
Logic
Toán học
Ghép nối (Concatenation)
Số mũ (**)
Dùng dấu ngoặc đơn để điều khiển thứ tự ưu tiên.
Ví dụ: Tăng biến đếm cho vòng lặp.
v_count := v_count + 1;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 13
Giới thiệu PL/SQL
- Câu lệnh SQL trong PL/SQL
Một số điểm cần lưu ý
Cần phần biệt từ khóa END của khối lệnh và END của
câu lệnh điều khiển chuyển tác.
PL/SQL không hỗ trợ trực tiếp các câu lệnh ngôn ngữ
định nghĩa dữ liệu (DDL) như CREATE TABLE, ALTER
TABLE, DROP TABLE.
PL/SQL không hỗ trợ các câu lệnh ngôn ngữ điều khiển
dữ liệu (DCL) như GRANT, REVOKE.
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 14
Giới thiệu PL/SQL
- Câu lệnh SELECT trong PL/SQL
SELECT select_list
INTO
{variable [, variable]…| record_name}
FROM
table
[WHERE condition];
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 15
Giới thiệu PL/SQL
- Câu lệnh SELECT trong PL/SQL
Ví dụ: Lấy dữ liệu cột DeptId và cột Loc của phòng SALES
trong bảng Dept.
DECLARE
v_deptno
v_loc_id
BEGIN
SELECT
INTO
FROMDept
WHERE
…
END;
NUMBER(4);
Dept.loc_id%Type;
Deptid, Locid
v_deptno, v_loc_id
Dname=‘Sales’;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 16
Giới thiệu PL/SQL
- Chèn dữ liệu
Ví dụ: Thêm một nhân viên vào trong cơ sở dữ liệu.
…
BEGIN
INSERT INTO Emp
(Empid, Fname, Email, Hireday, Jobid, Salary)
VALUES (emp_seq.NEXTVAL, ‘Ruth’,
‘
[email protected]’, sysdate, ‘SALES’, 4000);
END;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 17
Giới thiệu PL/SQL
- Cập nhật dữ liệu
Ví dụ: Tăng lương thêm $100 cho những nhân viên có mã
công việc là ST_CLERKS.
DECLARE
v_sal_increase
Emp.Salary%TYPE := 100;
BEGIN
UPDATE
Emp
SET
Salary = Salary + v_sal_increase
WHERE
Jobid = ‘ST_CLERK’;
END;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 18
Giới thiệu PL/SQL
- Xóa dữ liệu
Ví dụ: Xóa những hàng có Job_id là ST_CLERKS.
DECLARE
v_name
Emp.Job_id%TYPE := ‘ST_CLERKS’;
BEGIN
DELETE FROM
Emp
WHERE
Job_id = v_name;
END;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 19
Giới thiệu PL/SQL
- Câu lệnh kết hợp
Ví dụ: Chèn hoặc cập nhật dữ liệu trong bảng copy_emp phù
hợp với bảng emp.
DECLARE
v_empno emp.Emp_id%TYPE:=100;
BEGIN
MERGE INTO copy_emp c
USING Emp e
ON
(c.Emp_id=v_empno)
WHEN MATCHED THEN
UPDATE SET c.Emp_id=e.Emp_id, c.Ename=e.Ename
c.Job_id=e.Job_id, c.salary=e.salary
WHEN NOT MATCHED THEN
INSERT VALUES(e.Empid, e.fname, e.Hireday, e.Jobid, e.salary,
e.manageid);
END;
Chương 9. Ngôn ngữ thủ tục –PL/SQL
9 - 20