PL/SQL入門隨筆

PL/SQL(Procedure Language/SQL)

PLSQL是Oracle對sql語言的過程化擴展：
在SQL命令語言中增加了過程處理語句（如分支、迴圈等），使SQL語言具有過程處理能力。
SQL語言的數據操作能力與過程語言的數據處理能力相結合，使PLSQL面向過程，但比過程語言簡單、高效、靈活、實用。
不同資料庫的SQL擴展
　　Oracle:PL/SQL
　　DB2:SQL/PL
　　SQL Server:Transac-SQL(T-SQL)
===============================================================
1、PL/SQL操作資料庫效率最高
2、繼續學習存儲過程，存儲函數，觸發器
第一個程式
　　--打開輸出開關
　　set serveroutput on
　　declare
　　-- 說明部分（變數，游標或者例外）
　　begin
　　--程式體
　　dbms_output.put_line('hello world');
　　end;
　　/

===========================目錄===============================

1，PL/SQL的程式結構

2，PL/SQL引用型變數和記錄型變數

3，PL/SQL中if語句的使用

4，PL/SQL中迴圈語句的使用

5，PL/SQL中游標

6，PL/SQL例外

7，程式設計方法
================PL/SQL的程式結構===============================
declare
說明部分（變數說明、游標說明、例外說明）
begin
語句序列（DML語句）
exception
例外處理語句
end;
/
定義基本變數
類型：char,varchar2,date,number,boolean,long
舉例：var1 char(15);
married boolean := true;
psal number(7,2);
================PL/SQL引用型變數和記錄型變數====================
emp_rec emp%rowtype emp_rec是emp的行，數組類型，可以獲取EMP的所有欄位，加.欄位即可。（記錄型變數）
pename emp.ename%type 某個欄位的類型，只能獲取表中的一個欄位。（引用型變數）
================PL/SQL中if語句的使用============================
1、if 條件 then 語句1;
語句2;
end if;
2、if 條件 then 語句1;
else 語句2;
end if;
3、if 條件 then 語句1;
elsif 語句 then 語句;
else 語句;
end if;
-- 判斷用戶從鍵盤輸入的數字
set serveroutput on
accept num prompt '請輸入一個數字';

declare
pnum number:=#
begin
if pnum = 0 then dbms_output.put_line('你輸入的是0');
elsif pnum = 1 then dbms_output.put_line('你輸入的是1');
else dbms_output.put_line('你輸入的是其他數字');
end;
/
================PL/SQL中迴圈語句的使用============================
1.while迴圈：當迴圈條件成立時執行迴圈體，不成立時退出該迴圈
WHILE total <= 25000 LOOP
...
total := total + salary;
END LOOP;
2.loop迴圈：當迴圈條件不成立時執行迴圈體，成立時退出該迴圈
Loop
EXIT [when 條件];
...
End loop;
3.for迴圈：迴圈是連續的區間時
FOR I IN 1..3 LOOP
語句序列;
END LOOP;
================PL/SQL中游標============================
定義：就是一個結果集(result set)
語法：
CURSOR 游標名[(參數名 數據類型[,參數名 數據類型]...)]
IS SELECT 語句;
例如 cursor c1 is select ename from emp;
實用：
1.打開游標：
open c1;打開游標執行查詢
2.關閉游標:
close c1;關閉游標釋放資源
3.取一行游標的值：
fetch c1 into pename;取一行到變數中
fetch的作用：1）把當前指針指向的記錄返回
2）講指針指向下一條記錄
屬性：
%found %notfound
%isopen判斷游標是否打開
%rowcount影響的行數
游標數的限制：預設打開最多300個游標
修改游標數限制 alter system set open_cursors=400 scope=both
scopt屬性：memory（只該當前實例）,spfile（只改參數文件，資料庫需要重啟）,both（兩者都改）
實例：
set serveroutput on
declare
cursor cemp is select ename,salary from emp;
pname emp.ename%type;
psal emp.sal%type;
begin
-- 打開游標
open cemp;
loop
-- 取一條記錄
fetch cemp into pename,psal;
exit when cemp%notfound
dbms_output.put_line(pname||'的薪水是'||psal);
end loop
-- 關閉游標
close cemp
end
/
實例1：給員工漲工資
set serveroutput on
declare
--定義游標給哪些員工漲工資
cursor cemp is select empno,empjob from emp;
penmno emp.empno%type;
pjob emp.empjob%type;
begin
--打開游標
--把之前漲的工資回退回去
rollback;
open cemp;
loop
fetch cemp into penmno,pjob;
exit when cemp%notfound
-- 判斷新員工的職位
if pjob = 'president' then update emp set sal = sal+1000 where empno = pempno;
elsif pjob = 'manager' then update emp set sal=sal +800 where empno = pempno;
else update emp set sal = sal +400 where empno = pempno;
end if;
end loop
close cemp;
commit;
dbms_output.put_line('漲工資完成');
-- 對於oracle，預設的事務隔離級別是read committed
-- 事務的acid：原子性，一致性，隔離性，持久性，
end;
/
eg:
--使用帶參數的游標
declare
--定義一個帶參數的游標
cursor c_emp(dno number) is select ename from emp where deptno=dno;
p_ename emp.ename%type;
begin
--打開游標時需要參入實參
open c_emp(10);
loop
fetch c_emp into p_ename;
exit when c_emp%notfound;
DBMS_OUTPUT.PUT_LINE(p_ename);
end loop;
close c_emp;
end;
/
=================PL/SQL例外============================
例外是程式設計語言提供的一種功能，用來增強程式的健壯性和容錯性
1.No_date_found(沒有找到數據)
2.Too_many_rows (select...into語句匹配多個行)
3.Zero_Divide (被零除)
4.Value_error (算術或轉換錯誤) 如：負數在實數範圍開平方，abc字元串 轉換成 數字123
5.Timeout_on_resource (等待資源時發生超時)比較典型的場景:分散式資料庫
--系統例外：no_data_found
declare
pename emp.ename%type;
begin
select ename into pename from emp where empno=222222;
SYS.DBMS_OUTPUT.PUT_LINE(pename);
exception
when no_data_found then sys.dbms_output.put_line('沒有對應的記錄');
when others then sys.dbms_output.put_line('其它例外');
end;
/
--系統例外：too_many_rows;
declare
pename emp.ename%type;
begin
select ename into pename from emp where deptno=10;
SYS.DBMS_OUTPUT.PUT_LINE(pename);

exception
when no_data_found then sys.dbms_output.put_line('沒有對應的記錄');
when too_many_rows then sys.dbms_output.put_line('無法將多行記錄賦值給一個普通變數');
when others then sys.dbms_output.put_line('其它例外');
end;
/
--系統例外：zero_divide 被0除
declare
pnum number;
begin
pnum:=1/0;

exception
when zero_divide then
sys.dbms_output.put_line('0不能做除數');
sys.dbms_output.put_line('0真的不能做除數');
when others then sys.dbms_output.put_line('其它例外');
end;
/
--系統例外：value_error算術或轉換例外
declare
pnum number;
begin
pnum:='abd';
exception
when value_error then
sys.dbms_output.put_line('算術或者轉換錯誤');
when others then sys.dbms_output.put_line('其它例外');
end;
/
--自定義例外：查詢50號部門的員工姓名
set serveroutput on
declare
--定義游標
cursor cemp is select ename from emp where deptno=50;
pename emp.ename%type;
--自定義例外
no_emp_data exception;
begin
open cemp;
loop
fetch cemp into pename;
if cemp%notfound then raise no_emp_data;-- 拋出例外
exit when cemp%notfound;
end if;
end loop;
close cemp;-- 關閉游標，oracle自動啟動pmon(process monitor)
exception
when no_emp_data then dbms_output.put_line('沒有找到員工');
when others then dbms_output.put_line('其他');
end;
/
=====================程式設計方法============================
需求分析->概要設計->詳細設計->編碼->測試->上線
sql語句
變數：1、初始值是多少2、最終值如何得到