每一種數據庫都有這樣的一種語言，PL/SQL 是在Oracle裡面的一種編程語言，在Oracle內部使用的編程語言。我們知道SQL語言是沒有分支和循環的，而PL語言是為了補充SQL語言的，是帶有了分支和循環的語言。
declare
v_name varchar2(20);
v_temp number(1);
v_count binary_integer := 0;
v_sal number(7,2) := 4000.00;
v_date date := sysdate;
v_pi constant number(3,2) := 3.14;
v_valid boolean := false;
v_name varchar2(20) not null := 'myname';
declare開頭聲明變量，v_name表示變量名字，通常以v_xxx這種格式命名變量，varchar2(20)表示變量類型， :=為賦值操作符。
在PL/SQL裡面boolean類型變量在定義的時候一定要給初始值，Oracle 裡面的put_line()不能打印boolean類型的值。
還有一種聲明變量的方法：使用%type屬性。
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
使用emp表中empno字段的類型，這種方式的好處是PL/SQL聲明的變量類型隨著表字段類型的變化而變化。dbms_output.put_line('Test');表示輸出Test。
table類型類似java中的數組類型。
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno；
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
--type開頭
--類型名type_table_emp_empno
--is table of 表示table數據類型，相當於java裡面的數組Array[]
--emp.empno%type表示該類型數據裝的是emp表中empno類型的數據
--index by binary_integer 表示下表
--v_empnos type_table_emp_empno；使用type_table_emp_empno類型聲明變量v_empnos
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
--type type_record_dept is record聲明type_record_dept是record類型。
--該類型中有三個屬性deptno ，dname ，loc ，類型分別為dept表中deptno ，dname ，loc 三個字段的類型。record類型類似java中的類，record類型可以存儲一條記錄。
-- v_temp type_record_dept;使用type_record_dept類型聲明變量v_temp。
另一種聲明record變量的方法：
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
使用%rowtype聲明record類型的變量，v_temp 的屬性和dept表的字段保持一致，這種方式的好處是PL/SQL聲明的變量類型隨著表字段類型的變化而變化。
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then //固定寫法
dbms_output.put_line('error');
end;
exception定義異常處理，緊跟“when others then”為固定寫法。
SQL> declare
2 v_num number := 0;
3 begin
4 v_num := 2/v_num;
5 dbms_output.put_line(v_num);
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
error
PL/SQL 過程已成功完成。
SQL>
其他類型的異常
--返回記錄太多異常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多記錄了');
when others then
dbms_output.put_line('error');
end;
--沒有記錄異常
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('沒數據');
end;
PL/SQL裡面的selec t語句必須和into語句一塊用並且有且只有一條記錄。
--將編號為7369的員工的員工編號和薪水查詢出來並存儲到v_empno，v_sal並輸出
declare
v_empno emp.empno%type;
v_sal emp.sal%type;
begin
select empno,sal into v_empno,v_sal from emp where empno=7369;
dbms_output.put_line(v_empno || '-' || v_sal);
end;
--將編號為7369的員工的記錄查詢出來並存儲到v_emp，並輸出員工編號和薪水
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output.put_line(v_emp.empno || '-' || v_emp.sal);
end;
--向dept表中插入一條數據
declare
v_deptno dept2.deptno%type := 50;
v_dname dept2.dname%type := 'dname';
v_loc dept2.loc%type := ‘ckg’;
begin
insert into dept2 values (v_deptno, v_dname, v_loc);
commit;
end;
和sql語句唯一不同的是采用了pl/sql變量。update與delete語句和sql語句一樣。
sql%rowcount可以統計剛執行的sql語句影響了多少條記錄。
declare
v_deptno dept2.deptno%type := 50;
v_dname dept2.dname%type := 'dname';
v_loc dept2.loc%type := ‘can’;
begin
update dept2 set loc = 'sha' where deptno = 10;
dbms_output.put_line (sql%rowcount || '條記錄被影響');
commit;
end;
--輸出
1條記錄被影響
begin
execute immediate 'create table stu2(id number(10),name varchar2(20) default ''zhangsan'')';
end;
PL/SQL編寫ddl語句和SQL語句不同的是需要加execute immediate ，單引號中的sql語句使用雙單引號指定缺省值，如''zhangsan''。alter語句，drop語句同理。
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if(v_sal < 1200) then
dbms_output.put_line('low');
elsif(v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
注意紅色語法部分即可。
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when ( i>=11);
end loop;
end;
注意紅色語法部分即可。上面的循環相當於java裡的 do-while 循環。
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
注意紅色語法部分即可。上面的循環相當於java裡的while 循環。
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
--逆序
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
注意紅色語法部分即可。上面的循環相當於java裡的增強 for 循環。
我們知道，select語句的結果集是一張表，如果我們想對結果集逐條記錄遍歷該如何實現，就像java中的迭代器一樣？PL/SQL提供了解決遍歷結果集的的功能：游標。游標是指在結果集上的指針，通過游標可以對select語句的結果集逐條記錄遍歷。
declare
cursor c is --聲明游標指向select的結果集
select * from emp;
v_emp c%rowtype;
begin
open c; --打開游標
loop
fetch c into v_emp; --取出游標當前執向的值存入v_emp，每fetch一次，游標指向下一條記錄
exit when (c%notfound); --找不到就退出
dbms_output.put_line(v_emp.ename);
end loop;
close c; --關閉游標
end;
--輸出
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
可見，使用PL/SQL的游標和循環的結合，實現了對select結果集的遍歷。
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
for循環中使用游標，不需要聲明 v_emp變量，for開始的時候自動聲明v_emp；不需要打開關閉游標；不需要每次fetch。因此，通常采用此寫法。
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is
select ename, sal from emp where deptno = v_deptno and job = v_job;
begin
for v_temp in c (30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
注意紅色語法部分即可。從這裡我們也可以看出，實際上，真正fetch的時候，PL/SQL才回去數據庫查詢數據。
declare
cursor c is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
注意紅色語法部分即可。聲明游標是跟新用的，更新或者刪除時指明是當前游標指向的記錄。
declare
cursor c is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal > 2000) then
update emp2 set sal = sal / 2 where current of c;
end if;
end loop;
end;
對於上面這段PL/SQL代碼，如果我們需要經常執行，可以將這段代碼創建成存儲過程，如下：
create or replace procedure p is
cursor c is
select * from emp2 for update ;
begin
for e in c loop
if(e.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif(e.sal > 2000) then
update emp2 set sal = sal / 2 where current of c;
end if;
end loop;
end;
創建存儲過程和普通的PL/SQL代碼不同的是將“declare”改為“create or replace procedure p is ”，其他保持不變。
--命令方式
Procedure created
SQL> exec p;
PL/SQL procedure successfully completed
SQL>
--另一種方式
begin
p;
end;
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + v_a;
end;
--in 叫做傳入參數，調用者負責給v_a賦值
--out 叫做傳出參數，存儲過程是沒有返回值的,它就借助於傳出參數
-- v_b 中間什麼都沒寫，默認是in，是接收參數用的
-- v_temp 既可以接收，又可以傳出
調用過程
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
需要注意的是，執行存儲過程並不會直接顯示錯誤，可以使用show error命令顯示編譯錯誤。
求出每個員工的上級（經理）並輸出
create or replace procedure p_emp(v_empno emp.empno%type,v_level binary_integer) is
cursor c is
select * from emp where mgr = v_empno;
v_str varchar2(256) := '';
begin
for i in 1..v_level loop
v_str := v_str || ' ';
end loop;
for emp in c loop
dbms_output.put_line(v_str || emp.ename);
p_emp(emp.empno,v_level+1);--遞歸調用存儲過程
end loop;
end;
--求出沒有經理的員工
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where mgr is null;
dbms_output.put_line(v_emp.ename);
p_emp(v_emp.empno, 1);
end;
--或者我們已經知道7839是沒有上級的
begin
p_emp(7839, 1);
end;
--輸出
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
當對某一張表進行增刪改查操作的時候，觸發其他操作。
--創建一張記錄操作表
create table emp2_log -- emp2這張表的操作記錄
(
uname varchar2(20), --用戶
action varchar2(10),--操作
atime date --操作時間
);
--創建一個隊emp2表操作的觸發器
create or replace trigger trig
after insert or delete or update on emp2 for each row --表示每更新一條記錄都會生成一條操作記錄
--after可以改為before
begin
if inserting then
insert into emp2_log values(USER, 'insert', sysdate);--USER關鍵字,代表當前用戶是誰
elsif updating then
insert into emp2_log values(USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values(USER, 'delete', sysdate);--記錄到log文件中
end if;
end;
觸發器叫不能直接執行，必須指明在哪張表上面執行哪些操作的時候才能觸發觸發器。現在對emp2表進行插入操作並查看emp2_log表
SQL> insert into emp2(ename,deptno) values('lisi',20);
SQL> select * from emp2_log;
UNAME ACTION ATIME
-------------------- ---------- -----------
SCOTT insert 2016/10/10
SCOTT insert 2016/10/10
SQL>
可見對emp2表的插入操作觸發了日志記錄操作。
drop trigger trig;
基礎篇到此。
