PL/SQL 編程

標籤：else   data   編號   nbsp   tno   sql結構   oop   const   分類   

1.什麼是PL/SQL

　　PL/SQL是結合了Oracle過程語言和結構化查詢語言 (SQL)的一種擴充語言。是一種塊結構的語言。

2.PL/SQL的組成？

• 聲明部分
• 執行部分
• 異常處理部分

3.PL/SQL的優點

• 支援事務控制和SQL資料操作命令
• 支援SQL的所有資料類型，並且在此基礎上擴充了新的資料類型，也支援SQL的函數和運算子
• PL/SQL可以儲存在Oracle伺服器中，提高程式的運行效能
• 伺服器上的PL/SQL程式可以使用許可權進行控制
• 良好的可移植性

 

/*1.PL/SQL結構示範*/

[DECLARE]
--聲明部分，在此聲明PL/SQL用到的變數、類型及遊標，以及局部的預存程序和函數
BEGIN
--執行部分：過程及SQL語句，即程式的主要部分
[EXCEPTION]
--異常處理部分：錯誤處理
END;
--執行部分不可省略

 

/*2.PL/SQL變數與常量文法示範*/

變數名 資料類型[（範圍大小）][:= 值]; --變數聲明賦值文法

常量名 CONSTANT 資料類型 := 值; --常量聲明賦值文法

--例：
DECLARE
v_ename VARCHAR2(20);
v_rate NUMBER(7,2)；
c_rate_incr CONTRANT NUMBER(7,2) := 1.10;
BEGIN
--賦值方式一，通過SELECT INFO
SELECT ename,sal* c_rate_incr INTO v_ename, v_rate
FROM employee
WHERE empno = ‘7788‘;

--賦值方式二,通過賦值操作符“：=”
v_ename := ‘SCOTT‘;

END;

 

/*3.PL/SQL控制語句示範*/
p77-p79:控制文法

--3.1條件控制
--3.1.1
BEGIN
IF false THEN
DBMS_OUTPUT.PUT_LINE(‘t142‘);
ELSE
DBMS_OUTPUT.PUT_LINE(‘AAAAAA‘);
END IF;
END;

--3.1.2
DECLARE
v_num NUMBER(2,0) := 10;

BEGIN
IF v_num>10 THEN
DBMS_OUTPUT.PUT_LINE(‘100‘);
ELSIF v_num=10 THEN
DBMS_OUTPUT.PUT_LINE(‘10‘);
ELSE
DBMS_OUTPUT.PUT_LINE(‘0‘);
END IF;

END;

--3.2.1迴圈控制-LOOP
DECLARE
v_num NUMBER(2,0) := 1;

BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(v_num);
v_num+:=1;
EXIT WHEN v_num=11;
END LOOP;
END;

--3.2.2迴圈控制-FOR
DECLARE
v_num NUMBER(2,0) := 1;
v_num2 NUMBER(2,0);
BEGIN
FOR v_num2 IN REVERSE 1 .. 5 LOOP
DBMS_OUTPUT.PUT_LINE(v_num2);
END LOOP;
END;

4.異常處理

　　異常：在運行程式時出現的錯誤叫做異常

/*
===========================================================
| 預定義異常
============================================================
*/
create table employee as select * from scott.emp;

select * from employee;

--未進行異常處理
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
dbms_output.put_line(‘僱員名:‘||v_ename);
END;

--進行異常處理
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
dbms_output.put_line(‘僱員名:‘||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘僱員號不正確‘);
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line(‘查詢只能返回單行‘);
WHEN OTHERS THEN
dbms_output.put_line(‘錯誤號碼：‘||SQLCODE||‘錯誤描述：‘||SQLERRM);
END;

/*
===========================================================
| 查詢編號為7788的僱員的福利補助（comm列）。
============================================================
*/
DECLARE
v_comm employee.comm%TYPE;
e_comm_is_null EXCEPTION; --定義異常類型變數
BEGIN
SELECT comm INTO v_comm FROM employee WHERE empno=7788;
IF v_comm IS NULL THEN
RAISE e_comm_is_null;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘僱員不存在！錯誤為:‘||SQLCODE||SQLERRM);
WHEN e_comm_is_null THEN
dbms_output.put_line(‘該僱員無補助‘);
WHEN others THEN
dbms_output.put_line(‘出現其他異常‘);
END;

5.遊標：

　　用來處理使用select語句從資料庫中檢索到的多行記錄的工具

　　分類：

• • 顯示遊標：返回多條記錄時，使用顯示遊標逐行讀取

  • 隱式遊標：PL/SQL自動為DML語句建立隱式遊標，包含一條返回記錄

　　屬性：

• • %found：用於檢驗遊標是否成功，通常在FETCH語句前使用，當遊標按照條件查詢出一條記錄時，返回true
  • %isopen：判斷遊標是否處於開啟狀態，視圖開啟一個已經開啟或者已經關閉的遊標，將會出現錯誤
  • %notfound ：與%found的作用相反，當按照條件無法查詢到記錄時，返回true 
  • %rowcount ：迴圈執行遊標讀取資料時，返回檢索出的記錄資料的行數

--顯示遊標使用步驟

declare
v_ename employee.ename%type;
v_sal employee.sal%type;
--聲明遊標
cursor cursor_emp is
select ename,sal from employee
for update of sal;
begin
--開啟遊標
open cursor_emp;
--提取遊標
/*loop
fetch cursor_emp into v_ename,v_sal;
exit when cursor_emp%notfound;
dbms_output.put_line(‘僱員姓名：‘||v_ename||‘ 僱員薪水：‘||v_sal);
end loop;*/

loop
fetch cursor_emp into v_ename,v_sal;
exit when cursor_emp%notfound;
update employee set sal = sal+200
where current of cursor_emp;
end loop;

close cursor_emp;

end;

6.預存程序

　　範例程式碼:

CREATE OR REPLACE PROCEDURE add_employee(

eno employee.empno%type, --輸入參數,僱員編號
name employee.ename%type, --輸入參數,僱員名稱
salary employee.sal%type, --輸入參數,僱員薪水
job employee.job%type DEFAULT ‘CLERK‘, --輸入參數,僱員工種預設‘CLERK‘
dno employee.deptno%type, --輸入參數,僱員部門編號
on_Flag OUT number, --執行狀態
os_Msg OUT VARCHAR2 --提示資訊
)
IS
BEGIN
INSERT INTO employee (empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);
on_Flag:=1;
os_Msg:=‘添加成功‘;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
on_Flag:=-1;
os_Msg:=‘該僱員已存在。‘;
WHEN OTHERS THEN
on_Flag:=SQLCODE;
os_Msg:=SQLERRM;
END;

DECLARE
on_Flag NUMBER;
os_Msg VARCHAR2(100);
BEGIN
--按位置傳遞參數
add_employee(2111,‘MARY‘,2000,‘MANAGER‘,10,on_Flag,os_Msg);
dbms_output.put_line(on_Flag||os_Msg);
END;

 

PL/SQL 編程