PL/SQL是oracle的專用語言,它對標準的SQL語言的擴充套件.SQL語句可以巢狀在PL/SQL語言中，並結合處理語句。
PL/SQL程式結構
使用了程式塊的結構組織的程式碼.最簡單的程式塊是一種被稱為”匿名塊”的程式塊,匿名塊是指不會被oracle儲存並且不能夠重用程式塊。
PL/SQL程式通常包括3部分:Declare部分、exception部分、Begin和end部分.
Begin和end是PL/SQL的必須部分,總是begin開始end結束.
Declare部分包含定義變數、常量和遊標等型別的程式碼.
Begin與end之間可以巢狀begin與end及exception.
//開啟伺服器的輸出
SQL> set serveroutput on;
SQL> begin //開始
2 dbms_output.put_line('Hello World');
3 end;
4 /
變數與常量的宣告：
變數名 型別(字元，日期，數字，布林)[:=預設值];
常量名 constant 型別(字元，日期，數字，布林) [:=預設值];
變數與常量的區別：
變數可以在任何部分進行賦值.
而常量在declare中宣告並且賦值.
注意：都是在declare中宣告
正確的操作：
declare
pagesize constant number(2) :=10;
begin
dbms_output.put_line(pagesize);
end;
/
作用域：
是能夠引用變數名稱這樣的識別符號的程式塊。
declare
pagesize number(2):=10; //在整個中都有效
begin
dbms_output.put_line(pagesize);
declare
pagesize number(2):=99; //只是在內容宣告部分到end結束有效
begin
dbms_output.put_line(pagesize);
end;
end;
/
//宣告一個整型的變數
SQL> declare
2 age number(2);
3 begin
4 age:=90;
5 dbms_output.put_line(age);
6 end;
7 /
//宣告後直接賦值
SQL> declare
2 age number(2) :=90;
3 begin
4 dbms_output.put_line(age);
5 end;
6 /
//捕獲異常處理
SQL> declare
2 age number(2);
3 begin
4 age :='qwe'
5 ;
6 exception
7 when others then
8 dbms_output.put_line('賦值失敗');
9 end;
10 /
條件判斷語句
有if與case語句
If語句
If語法
If（expression1）then pl/sql_statement
Else
Pl/sql_statement;
End if;
If（expression1）then pl/sql_statement
Else if（expression2）
Pl/sql_statement;
Else
Pl/sql_statement;
End if;
Case語句
從oracle9i以後引入
Case <selector>
When <expression1> then pl/sql_statement1;
When <expression2> then pl/sql_statement2;
…….
[else pl/sql_statement;]
End;
//案例
SQL> declare score number(2) :=80;
begin
if score>70 then dbms_output.put_line('成績合格');
end if;
end;
/
SQL> declare score number(2) :=80;
begin if score>90 then dbms_output.put_line('成績合格');
else dbms_output.put_line('成績不合格');
end if;
end;
/
成績不合格
SQL> declare score number(2):=8;
begin
case score
when 9 then dbms_output.put_line('成績優秀');
when 8 then dbms_output.put_line('成績亮');
end case;
end;
/
迴圈語句
最基本的迴圈稱為無條件迴圈,如果沒有指定exit語句,迴圈將無條件執行，這種迴圈稱為死迴圈,死迴圈儘量避免。
語法格式如下：
Loop
---statement---
Exit when condition
End loop;
案例：
SQL> declare
i number(2):=1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
exit when i>10;
end loop;
end;
/
While迴圈
語法：
While condition
Loop
Statement;
End loop;
SQL> declare
i number(2):=1;
begin
while i<10
loop dbms_output.put_line(i);
i:=i+1;
end loop;
end;
/
For迴圈
For loop_control_variable in [reverse] lower upper loop
Statement;
End loop;
SQL> begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
/
PL/SQL語句中的SQL語句
declare
countnum number(2); //宣告變數
begin
select count(*) into countnum from emp; //into的作用把從資料中檢測出的資料賦值給哪個變數
dbms_output.put_line(countnum);
end;
/
注意：如果在select中沒有into語句會出現bug
declare
empno number(4);
ename varchar2(10);
job varchar2(9);
begin
select empno,ename,job into empno,ename,job from emp;
dbms_output.put_line(empno||'/'||ename||'/'||job);
end;
/
以上程式碼中會出現bug bug原因是因為定義的變數只可以儲存一個單獨的值,而你返回的是多個值.所以應該為如下：
SQL> declare
2 empno number(4);
3 ename varchar2(10);
4 job varchar2(9);
5 begin
6 select empno,ename,job into empno,ename,job from emp where empno='7369'; //在這加上where條件了
7 dbms_output.put_line(empno||'/'||ename||'/'||job);
8 end;
9 /
7369/SMITH/CLERK
是用%TYPE和%ROWTYPE型別的變數
在定義變數時處理可以使用oracle規定的資料型別外,還可以使用%TYPE和%ROWTYPE來定義變數.
%TYPE型別的變數是專門為儲存在資料列中檢索到的值而建立的.對於使用%TYPE建立的變數，其資料型別由系統根據檢索的資料列的資料型別決定.
%ROWTYPE型別的變數,它可以一次儲存從資料庫檢索的一行資料。
當你不知道表中的資料型別是什麼時候?那你就考慮%TYPE來解決此問題。
SQL> declare
2 empno emp.empno%TYPE; //宣告的時候一定要加上表的中列
3 ename emp.ename%TYPE;
4 job emp.job%TYPE;
5 begin
6 select empno,ename,job into empno,ename,job from emp where empno='7369';
7 dbms_output.put_line(empno||'/'||ename||'/'||job);
8 end;
9 /
7369/SMITH/CLERK
emp.empno%TYPE;解析：首先它到emp表中去查詢empno列 %TYPE返回其資料的資料型別。
%TYPE的好處：
1、 可移植性高(當我們對錶的資料型別發生改變時,使用者不必考慮定義變數型別)
2、 使用者不必檢視資料型別就可以定義變數能夠儲存檢索的資料。
%ROWTYPE
SQL> declare
2 row_data emp%ROWTYPE;
3 begin
4 select * into row_data from emp where empno='7369';
5 dbms_output.put_line(row_data.empno||'/'||row_data.ename||'/'||row_data.job);
6 end;
7 /
注意：如果定義了％rowtype型別的變數,該變數的結構與定義表的結構完全相同,查詢時必須使用*或者列舉全部的欄位資訊。
複合變數
複合變數可以將不同的資料型別的多個值儲存在一個單元中.由於複合資料型別可以有使用者根據需要定義其結構,所以複合資料型別也稱為自定義資料型別。
PL/SQL提供了兩種型別的複合資料型別:
1、 記錄型別
在記錄型別中可以儲存多個標量值,與資料庫中的行相似
2、 記錄表型別
允許使用者在程式程式碼中使用”表”,以便儲存多個行的資料。它只在程式執行期間有效。類似於程式程式碼中集合|陣列。
記錄型別：
必須使用TYPE語句定義記錄型別的語法宣告如下：
TYPE record_name is record(
Field_name data_type[not null， :=default value]
………
)；
與%Rowtype不同之處是
1、%rowtype查詢的是全部資料
2、記錄型別必須使用type語法格式宣告
//案例
SQL> declare
2 type empinfo is record( //宣告一個記錄型別
3 empno number(4),
4 ename varchar2(10),
5 job varchar2(9)
6 );
7
8 emp_data empinfo; //宣告一個記錄型別的變數
9 begin
10 select empno,ename,job into emp_data from emp where empno=7369; //查詢的結果賦值給定義的變數
11 dbms_output.put_line(emp_data.empno||'/'||emp_data.ename||'/'||emp_data.job);
12 end;
13 /
記錄表型別
它可以處理多個記錄或多個行記錄。
1、 為什麼使用記錄表型別呢?
因為我們查詢的資料的往往需要返回多行記錄,所以需要記錄表型別。
2、 定義記錄表型別
TYPE table_name is table of data_type[not null]
Index by binary_integer;//主鍵的索引
declare
type table_emp is table of emp%rowtype //建立一個表 此表的型別與emp表的型別一致
index by binary_integer;
type table_text is table of varchar2(20) //建立一個表 此表具有一個varchar2列的簡單表
index by binary_integer;
empt table_emp; //宣告記錄表型別的變數
tabtext table_text;
begin
案例：
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 begin
6 empt(1).ename:='wangyi';
7 dbms_output.put_line(empt(1).ename);
8 end;
9 /
//返回總記錄
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 begin
6 dbms_output.put_line(empt.count);
7 end;
8 /
0 //沒有記錄
//刪除的操作
表名.Delete(記錄數);
//檢索記錄變數
First：獲取第一個的索引
Next：下一個的索引 但是必須有引數
Last：最後一個的索引
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 i number(2):=1;
6 begin
7 while i<10
8 loop
9 empt(i).ename:='wangyi';
10 i:=i+1;
11 end loop;
12
13 dbms_output.put_line(empt.count);
14
15 empt.delete(2);
16
17 dbms_output.put_line(empt.count);
18
19 dbms_output.put_line(empt.first);
20 dbms_output.put_line(empt.next(2));
21 dbms_output.put_line(empt.last);
22 end;
23 /
9
8
1
3
9
//查詢資料庫的資料賦值給你建立的記錄表型別
分析一：
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 i number(10):=0;
6 begin
7
8 for rec in (select * from emp) loop
9 i:=i+1;
10 empt(i).ename:=rec.ename;
11 end loop;
12
13 dbms_output.put_line(empt.count);
14 end;
15 /
15
具體實現：
//把查詢的的資料全部賦值給記錄表型別
declare
type table_emp is table of emp%rowtype
index by binary_integer;
empt table_emp;
i number(10):=0;
j number(10):=1;
begin
for rec in (select * from emp) loop
i:=i+1;
empt(i).empno:=rec.empno;
empt(i).ename:=rec.ename;
empt(i).job:=rec.job;
empt(i).mgr:=rec.mgr;
empt(i).hiredate:=rec.hiredate;
empt(i).sal:=rec.sal;
empt(i).comm:=rec.comm;
empt(i).deptno:=rec.deptno;
end loop;
while j<=empt.count loop
dbms_output.put_line(empt(j).empno||'/'||empt(j).ename||'/'||empt
(j).job||'/'||empt(j).mgr||'/'||empt(j).hiredate||'/'||empt(j).sal||'/'||empt
(j).comm||'/'||empt(j).deptno);
j:=j+1;
end loop;
end;
/
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 i number(10):=0;
6 j number(10):=1;
7 begin
8
9 for rec in (select * from emp) loop
10 i:=i+1;
11 empt(i).empno:=rec.empno;
12 empt(i).ename:=rec.ename;
13 empt(i).job:=rec.job;
14 empt(i).mgr:=rec.mgr;
15 empt(i).hiredate:=rec.hiredate;
16 empt(i).sal:=rec.sal;
17 empt(i).comm:=rec.comm;
18 empt(i).deptno:=rec.deptno;
19 end loop;
20
21 dbms_output.put_line(empt.count);
22
23 while j<=empt.count loop
24 dbms_output.put_line(empt(j).empno||'/'||empt(j).ename||'/'||empt
25
26 (j).job||'/'||empt(j).mgr||'/'||empt(j).hiredate||'/'||empt(j).sal||'/'||empt
27
28 (j).comm||'/'||empt(j).deptno);
29 j:=j+1;
30 end loop;
31 end;
32 /
以上內容歸redarmy_chen所有,如需轉載請附帶出處,如有疑問請傳送至redarmy_chen@qq.com