------------------------2013-5-14------------------------
oracle數據類型
Scalar:
? char(n),nchar(n)
? varchar2(n)
? nvarchar2(n)
? date
? timestamp
? raw
? blob,clob
? nclob,bfile
? long,long raw
? rowid,urowid
Collection:
? varray
? table
Relationship
? ref

PL/SQL 代表Procedural Language/SQL（Procedural Language:過程語言）
它是對SQL的擴展
PL/SQL中存在過程構造和數據庫訪問

優點：調用外部函數和過程。
支持sql,oop
更高生產率，性能，可移植性。
嚴密的安全性

PL/SQL引擎
過程語句執行器（過程語句）和SQL語句執行器（SQL語句）。

PL/SQL塊的各個組成部分
? 聲明部分
? 可執行部分
? 異常處理部分
PL/SQL塊的結構
? declare
??? declarations? --聲明部分
? begin
??? executable statements? --可執行部分
? exception
??? handlers? --異常處理部分
? end;

變量
臨時存儲，賦予新值。
可重用性，易于維護。

內置數據類型
標量，容納單個值（number,character,date,boolean）
?number類型：binary_integer,number,pls_integer
?number子類型：dec,decimal,double precision,float,integer,int,numeric,real,smallint
?character類型：char,varchar2,raw,long和long raw,rowid和urowid
?區域字符類型：nchar,nvarchar2
?datetime和interval類型：date,timestamp,
?timestamp with time zone,timestamp with local time zone,
?interval year to month,interval day to second
?boolean類型：true,false,null,只允許對boolean變量執行邏輯操作。
復合，record,varray,nested table
引用，ref cursor,ref操作符
lob，blob,clob,nclob,bfile

屬性的類型：
%type 引用數據庫列，變量的類型和數據表中的字段的數據類型一致。
%rowtype 代表表中的行
記錄類型變量，引用記錄型變量的方法是"記錄變量名.基本類型變量名"

布爾表達式類型：
數值型，比較數值
字符型，比較用引號括起來的序列或單個字符
日期型，比較兩個日期

控制結構
條件控制，if then,if then else,if then elsif和case
迭代控制，while,for
順序控制，goto,null

錯誤處理
預定義，違反oracle規則時，將隱式引發它。
用戶定義，由raise語句顯示引發，只能在pl/sql塊的聲明部分中聲明

pl/sql支持所有sql數據類型和ansi標準類型。

------------------------2013-5-15------------------------

1.pl/sql程序的結構
2.基本語法要素，包括常量，基本數據類型，復合數據類型，表達式和函數。
3.條件控制和循環控制的流程控制結構
4.事務處理的commit,rollback和savepoint等3個命令
5.定義，打開和提取游標的數據
6.無參數過程和帶參數過程的創建，查詢，修改和使用方法
7.序列的創建和使用方法
8.定義異常
9.綜合實例

pl/sql以解釋型方式執行，不能編譯成可執行文件，脫離支撐環境執行。

每次只能執行一條語句，語句以英文的分號;為結束標識。
oracle不像vb,vc程序設計語言，側重后臺數據庫的管理，提供編程能力較弱。

grant "RESOURCE" to "TEMPUSER";
alter user "TEMPUSER" default role all;

--創建表
create table "TESTTABLE"(
?"RECORDNUMBER" NUMBER(4) NOT NULL,
?"CURRENTDATE" date not null
);

select * from testtable;

--pl/sql添加數據
set serveroutput on?? --允許服務器輸出
declare
? maxrecords constant int:=100;
? i int:=1;
begin
? for i in 1..maxrecords loop
??? insert into TESTTABLE(recordnumber,currentdate)values(i,sysdate);
? end loop;
? dbms_output.put_line('成功錄入數據!');
? commit;
end;

成功錄入數據!

PL/SQL 過程已成功完成。

sysdate為系統時間函數
dbms_output為系統默認程序包，put_line為包中定義的方法，輸出信息。
對數據庫數據的更改并沒有直接操作數據庫，放在工作區的內存里，直到commit語句執行后才發生永久更改。

常量語法格式
常量名 constant 類型標識符 [not null]:=值
括號內的not null為可選參數，若選用，表明該常（變）量不能為空值。

--定義常量
declare
? pi constant number(9) :=3.1415926;
begin
? dbms_output.put_line(pi);
? commit;
end;

3

PL/SQL 過程已成功完成。

關于 varchar2 的最大長度
varchar2有兩個最大長度：一個是在字段類型4000；一個是在PL/SQL中變量類型32767。

boolean 布爾型（true,false,null三者取一）

變量語法格式
變量名 類型標識符 [not null]:=值

--定義變量
declare
? age number(3,1) :=26.1;????? --number(3) :=26.1; 輸出26
begin
? dbms_output.put_line(age);
? commit;
end;
--輸出26.1

?

--select * from testtable;

--定義變量，引用列字段類型。
declare
? mydate testtable.currentdate%type:='15-6月 -13';
begin
? dbms_output.put_line(mydate);
? commit;
end;

--記錄類型
set serveroutput on;
declare
? type myrecord is record(
??? myrecordnumber int,
??? mycurrentdate date
? );
? srecord myrecord;
begin
? select * into srecord from testtable where recordnumber = 68;
? dbms_output.put_line(srecord.myrecordnumber);
? dbms_output.put_line(srecord.mycurrentdate);
end;
--在pl/sql程序中，select語句總是和into配合使用，into子句后面就是要被賦值的變量。

--記錄類型
set serveroutput on;
declare
? myrecord testtable%rowtype;
begin
? select * into myrecord from testtable where recordnumber = 88;
? dbms_output.put_line(myrecord.recordnumber);
? dbms_output.put_line(myrecord.currentdate);
end;

一維表類型變量，相當于一維數組。
type 表類型 is table of 類型 index by binary_integer;
表變量名 表類型

-- 一維表類型
declare
? type tabletype1 is table of varchar2(4) index by binary_integer;
? type tabletype2 is table of testtable.recordnumber%type index by binary_integer;
? table1 tabletype1;
? table2 tabletype2;
? begin
??? table1(1) := '大學';
??? table1(2) := '大專';
??? table2(1) := 88;
??? table2(2) := 55;
? dbms_output.put_line(table1(1) || table2(1));
? dbms_output.put_line(table1(2) || table2(2));
? dbms_output.put_line('總記錄數：' || to_char(table1.count));
? dbms_output.put_line('第一條記錄' || table1.first);
? dbms_output.put_line('最后條記錄' || table1.last);
? dbms_output.put_line('第二條的前一條記錄' || table1.prior(2));
? dbms_output.put_line('第一條的后一條記錄' || table1.next(1));
? end;

count,delete,first,last,next,exists和prior屬性進行操作，使用方法為"表變量名.屬性"，返回的是數字。

||是連接字符串的運算符。

多維表類型變量，相當于多維數組。

declare
? type tabletype1 is table of testtable%rowtype index by binary_integer;
? table1 tabletype1;
? begin
? select * into table1(1) from testtable where recordnumber=60;
? dbms_output.put_line(table1(1).recordnumber || table1(1).currentdate);
? end;
--table1(1)如果返回多個記錄，也不可以，table1(60)不是代表60個記錄。

數值表達式：** 乘方
declare
? result integer;
? begin
??? result := 10+3*4-20+5**2;
??? dbms_output.put_line('返回結果是：' || to_char(result));
? end;

字符表達式：就是連接運算符 ||
關系表達式：= 等于（不是賦值運算符:=，like類似于，in在...之中，between在...之間）
關系型表達式運算符兩邊的表達式的數據類型必須一致。

邏輯表達式：not邏輯非,or邏輯或,and邏輯與
運算的優先次序：not,and和or

函數：to_char（字符型）,to_date（日期型）,to_number（數值型）

#條件控制#
if..then..end if條件控制

declare
? number1 integer := 90;
? number2 integer := 60;
begin
? if number1 >= number2 then
??? dbms_output.put_line('number1大于等于number2');
? end if;
end;

if..then..else..end if條件控制

declare
? number1 integer := 80;
? number2 integer := 90;
begin
? if number1 >= number2 then
??? dbms_output.put_line('number1大于等于number2');
? else
??? dbms_output.put_line('number1小于number2');
? end if;
end;

if嵌套條件控制
declare
? number1 integer := 80;
? number2 integer := 90;
begin
? if number1 <= number2 then
??? if number1 = number2 then
????? dbms_output.put_line('number1等于number2');
??? else
????? dbms_output.put_line('number1小于number2');
??? end if;
? else
??? dbms_output.put_line('number1大于number2');
? end if;
end;

#循環控制#
loop .. exit .. end loop循環控制
declare
? number1 integer := 80;
? number2 integer := 90;
? i integer := 0;
begin
? loop
??? number1 := number1 + 1;
??? if number1 = number2 then
????? exit;
??? else
????? i := i+1;
??? end if;
? end loop;
? dbms_output.put_line('共循環次數：' || to_char(i));
end;

loop .. exit .. when .. end loop循環控制
exit when實際上就相當于 if 條件 then
exit
end if;

declare
? number1 integer := 80;
? number2 integer := 90;
? i integer := 0;
begin
? loop
??? number1 := number1 + 1;
??? i := i+1;
??? exit when number1 = number2;
? end loop;
? dbms_output.put_line('共循環次數：' || to_char(i));
end;

while .. loop .. end loop循環控制
declare
? number1 integer := 80;
? number2 integer := 90;
? i integer := 0;
begin
? while number1 < number2 loop
??? number1 := number1 + 1;
??? i := i+1;
? end loop;
? dbms_output.put_line('共循環次數：' || to_char(i));
end;

for .. in .. loop .. end loop循環控制
declare
? number1 integer := 80;
? number2 integer := 90;
? i integer := 0;
begin
? for i in 1..10 loop
??? number1 := number1 + 1;
? end loop;
? dbms_output.put_line('number1的值：' || to_char(number1));
end;

事務處理
commit命令，工作區內的修改內容才寫入到數據庫上，稱為物理寫入，這樣可以保證在任意的客戶機沒有物理提交修改以前，別的客戶機讀取的后臺數據庫中的數據是完整的，一致的。

打開自動提交事務：set auto on;
取消自動提交事務：set auto off;

rollback命令
--查詢--
已選擇100行。
--刪除--
已刪除100行。
--查詢--
未選定行
--rollback--
回退已完成。
--查詢--
已選擇100行。

savepoint命令
創建保存點：savepoint 保存點名；
回滾保存點：rollback to 保存點名；

insert into testtable values('101','16-5月 -13');

savepoint ins1;

--執行刪除操作--
select * from testtable;

rollback to ins1;

--查詢，回滾到刪除之前的狀態。--

##游標##
游標是從數據表中提取出來的數據，以臨時表的形式存放在內存中，在游標中有一個數據指針，在初始狀態下指向的是首記錄，利用fetch語句可以移動該指針，從而對游標中的數據進行各種操作，然后將操作結果寫回數據表中。

cursor 游標名 is select 語句;

declare
? rn testtable.recordnumber%type;
? cursor mycursor is select * from testtable where recordnumber > rn;? --定義游標
? rs mycursor%rowtype;
begin
? rn := 80;
? open mycursor;? --打開游標 1.將符合條件的記錄送入內存，2.將指針指向第一條記錄。
? if mycursor%isopen then
??? fetch mycursor into rs;? --提取游標
??? if mycursor%found then
????? dbms_output.put_line(to_char(rs.recordnumber) || rs.currentdate);
??? else
????? dbms_output.put_line('沒有數據!');
??? end if;
? else
??? dbms_output.put_line('游標沒有打開!');
? end if;
? close mycursor;? --關閉游標
end;

--提取游標
fetch 游標名 into 變量名1，變量名2...;
或
fetch 游標名 into 記錄型變量名;

游標的屬性
1. %isopen 測試游標是否打開。
2. %found 測試前一個fetch語句是否有值，有值將返回true，否則為false。
3. %notfound 是%found屬性的反邏輯，常被用于退出循環。
4. %rowcount 返回游標的數據行數，若返回值為0，表明游標已經打開，但沒有提取出數據。

declare
? rn testtable.recordnumber%type;
? cursor mycursor is select * from testtable where recordnumber > rn;
? rs mycursor%rowtype;
begin
? rn := 80;
? open mycursor;
? if mycursor%isopen then
??? fetch mycursor into rs;? --提取游標
??? dbms_output.put_line(to_char(mycursor%rowcount));
??? if mycursor%found then
????? dbms_output.put_line(to_char(rs.recordnumber) || rs.currentdate);
??? else
????? dbms_output.put_line('沒有數據!');
??? end if;
? else
??? dbms_output.put_line('游標沒有打開!');
? end if;
end;

##過程##
共同的特點是沒有名稱，只能存儲為文件，然后通過執行文件的方式執行，因此稱為無名塊。
與此對應的是在pl/sql中也引入了高級程序設計的一些概念，其中最重要的就是過程。

過程就是高級程序設計語言中的模塊的概念，將一些內部聯系的命令組成一個個過程，通過參數在過程之間傳遞數據是模塊化設計思想的重要內容。

create or replace procedure 過程名 as? --as關鍵字代替了無名塊的declare
? 聲明語句段；
begin
? 執行語句段；
exception
? 異常處理語句段
end;

create or replace procedure tempprocedure as
? tempdate testtable.currentdate%type;
begin
? select currentdate into tempdate from testtable where recordnumber = 88;
? dbms_output.put_line(to_char(tempdate));
end;
--創建過程是一個數據定義命令。

調用：
begin
tempprocedure;
end;

#參數類型# 調用參數分割用,逗號分割
in參數
out參數
inout參數

create or replace procedure tempprocedure
(
? trn in testtable.recordnumber%type,
? td out testtable.recordnumber%type,
? tn in out testtable.recordnumber%type??? --類型為number(4) or varchar2(20) 會有警告?? 警告: 創建的過程帶有編譯錯誤。
)
as
? tempdate testtable.currentdate%type;
? tempnumber testtable.recordnumber%type;
begin
? select currentdate into tempdate from testtable where recordnumber = trn;
? dbms_output.put_line(to_char(tempdate));
? select recordnumber into tempnumber from testtable where recordnumber = trn;
? dbms_output.put_line(to_char(tempnumber));
? --td := '日期是：' || to_char(tempdate);
?-- tn := '序號是：' || tempnumber;
?td := 1111;
?tn := 2222;
end;

存儲過程的調用
declare
? trn testtable.recordnumber%type;
? td? testtable.recordnumber%type;
? tn? testtable.recordnumber%type;
begin
? trn := 10;
? td := '';
? tn := '';
? tempprocedure(trn,td,tn);
? dbms_output.put_line(to_char(trn));
? dbms_output.put_line(to_char(td));
? dbms_output.put_line(to_char(tn));
end;

##序列##
CREATE SEQUENCE "NEWLIFEYHJ"."TESTSEQ" INCREMENT BY 1 START WITH
??? 1 MAXVALUE 20 MINVALUE 1 NOCYCLE
??? CACHE 20 NOORDER

--查詢序列值--
select TESTSEQ.nextval from dual;

select TESTSEQ.currval from dual;

insert into testtable values(TESTSEQ.currval,'17-5月 -13');

##異常處理##
系統預定義異常處理

自定義異常處理
1.定義異常
declare
? 異常名 exception;
2.觸發異常
? raise 異常名;
3.處理異常
? Exception
? when 異常名1 then
??? 異常處理語句段1;
? when 異常名2 then
??? 異常處理語句段2;

declare
? tempno integer := 90;
? exc exception;
begin
? tempno :=tempno+1;
? if tempno = 91 then
??? raise exc;
? end if;
? exception
? when exc then
??? dbms_output.put_line('自定義異常處理中……');
end;

-->p45/56? 綜合實例

1.功能設計
某高校開發的研究生招生系統，要求設計pl/sql程序對考生的成績數據進行處理，處理的邏輯是根據每門專業課的最低分數線和總分的最低分數線自動將考生歸類為錄取考生，調劑考生和落選考生。

為此設計兩個數據表，graduate數據表存放考生成績，resut數據表存放處理結果，pl/sql程序完成的功能就是將graduate數據表的數據逐行掃描，根據分數線進行判斷，計算出各科總分，在result數據表中將標志字段自動添加上"錄取"或"落選"。

2.數據表設計
--研究生表
create table graduate(
? BH NUMBER(10) NOT NULL,
? XM VARCHAR2(10) NOT NULL,
? LB VARCHAR2(10) NOT NULL,
? YINGYU NUMBER(4,1) NOT NULL,
? ZHENGZHI NUMBER(4,1) NOT NULL,
? ZHUANYE1 NUMBER(4,1) NOT NULL,
? ZHUANYE2 NUMBER(4,1) NOT NULL,
? ZHUANYE3 NUMBER(4,1) NOT NULL
);

--結果表
create table "RESULT"(
? "BH" NUMBER(10) NOT NULL,
? "XM" VARCHAR2(10) NOT NULL,
? "LB" VARCHAR2(10) NOT NULL,
? "YINGYU" NUMBER(4,1) NOT NULL,
? "ZHENGZHI" NUMBER(4,1) NOT NULL,
? "ZHUANYE1" NUMBER(4,1) NOT NULL,
? "ZHUANYE2" NUMBER(4,1) NOT NULL,
? "ZHUANYE3" NUMBER(4,1) NOT NULL,
? "TOTALSCORE" NUMBER(5,1) NOT NULL,
? "FLAG" VARCHAR2(4) NOT NULL
);

3.錄入數據
insert into graduate values(2003080520,'張三豐','碩士',55,56,67,78,89);
insert into graduate values(2003060555,'張翠山','碩士',66,78,78,89,92);
insert into graduate values(2003056066,'張無忌','碩士',76,67,89,90,66);
insert into graduate values(2003010989,'趙敏','碩士',45,59,74,66,56);
insert into graduate values(2003050677,'周芷若','碩士',77,67,72,87,66);
insert into graduate values(2003869401,'小昭','碩士',56,67,56,64,34);
insert into graduate values(2003340987,'阿離','碩士',68,93,64,80,56);
insert into graduate values(2003056709,'宋遠橋','碩士',90,68,81,61,67);
insert into graduate values(2003100894,'殷素素','碩士',69,73,62,70,75);

###
open graduatecursor;
? if graduatecursor%notfound then
??? raise errormessage;
? end if;
? loop
??? fetch graduatecursor into graduaterecord;
??? graduatetotalscore := graduaterecord.yingyu + graduaterecord.zhengzhi + graduaterecord.zhuanye1 + graduaterecord.zhuanye2 + graduaterecord.zhuanye3;
??? if(graduaterecord.yingyu >= tempyingyu and graduaterecord.zhengzhi >= tempzhengzhi and graduaterecord.zhuanye1 >= tempzhuanye1 and graduaterecord.zhuanye2 >= tempzhuanye2 and graduaterecord.zhuanye3 >= tempzhuanye3 and graduatetotalscore >= temptotalscore) then
????? graduateflag := '錄取';
??? else
????? graduateflag := '落選';
??? end if;
??? exit when graduatecursor%notfound;
??? insert into result values(graduaterecord.bh,graduaterecord.xm,graduaterecord.lb,graduaterecord.yingyu,graduaterecord.zhengzhi,graduaterecord.zhuanye1,graduaterecord.zhuanye2,graduaterecord.zhuanye3,graduatetotalscore,graduateflag);
? end loop;
? close graduatecursor;
? commit;
? exception
? when errormessage then
? dbms_output.put_line('無法打開數據表');

create or replace procedure graduateprocess
--(
? --tempzhengzhi in graduate.zhengzhi%type,
? --tempyingyu in graduate.yingyu%type,
? --tempzhuanye1 in graduate.zhuanye1%type,
? --tempzhuanye2 in graduate.zhuanye2%type,
? --tempzhuanye3 in graduate.zhuanye3%type,
? --temptotalscore in result.totalscore%type
--)
as
? --graduaterecord graduate%rowtype;
? --graduatetotalscore result.totalscore%type;
? --graduateflag varchar2(4);
? --cursor gradutecursor is select * from graduate;
? --errormessage exception;
begin
?
end;

###

4.程序設計
完整存儲過程
/*定義過程參數*/
create or replace procedure graduateprocess
(
? tempzhengzhi in graduate.zhengzhi%type,
? tempyingyu in graduate.yingyu%type,
? tempzhuanye1 in graduate.zhuanye1%type,
? tempzhuanye2 in graduate.zhuanye2%type,
? tempzhuanye3 in graduate.zhuanye3%type,
? temptotalscore in result.totalscore%type
)
as
/*定義graduaterecord為記錄型變量，臨時存放通過游標從graduate數據表中提取的記錄*/
? graduaterecord graduate%rowtype;
/*定義graduatetotalscore為數值型變量，統計總分*/?
? graduatetotalscore result.totalscore%type;
/*定義graduateflag為字符型變量，根據結果放入"落選"或"錄取"，然后寫入數據表result*/
? graduateflag varchar2(4);
/*定義游標graduatecursor，存放的是所有的graduate數據表中的記錄*/?
? cursor graduatecursor is select * from graduate;
/*定義異常處理errormessage*/?
? errormessage exception;
/*開始執行*/?
begin
? /*打開游標*/
? open graduatecursor;
? /*如果游標沒有數據，激活異常處理*/
? if graduatecursor%notfound then
??? raise errormessage;
? end if;
? /*游標有數據，指針指向第一條記錄，每執行fetch命令，就自動下移，循環執行到記錄提取完畢為止*/
? loop
??? fetch graduatecursor into graduaterecord;
??? /*計算總分*/
??? graduatetotalscore := graduaterecord.yingyu + graduaterecord.zhengzhi + graduaterecord.zhuanye1 + graduaterecord.zhuanye2 + graduaterecord.zhuanye3;
??? /*判斷單科和總分是否滿足錄取要求，若滿足，graduateflag變量值為"錄取"，否則為"落選"*/
??? if(graduaterecord.yingyu >= tempyingyu and graduaterecord.zhengzhi >= tempzhengzhi and graduaterecord.zhuanye1 >= tempzhuanye1 and graduaterecord.zhuanye2 >= tempzhuanye2 and graduaterecord.zhuanye3 >= tempzhuanye3 and graduatetotalscore >= temptotalscore) then
????? graduateflag := '錄取';
??? else
????? graduateflag := '落選';
??? end if;
??? /*當游標數據提取完畢后，退出循環*/
??? exit when graduatecursor%notfound;
??? /*向結果數據表result中插入處理后的數據*/
??? insert into result values(graduaterecord.bh,graduaterecord.xm,graduaterecord.lb,graduaterecord.yingyu,graduaterecord.zhengzhi,graduaterecord.zhuanye1,graduaterecord.zhuanye2,graduaterecord.zhuanye3,graduatetotalscore,graduateflag);
? end loop;
? /*關閉游標*/
? close graduatecursor;
? /*提交結果*/
? commit;
? /*異常處理，提示錯誤信息*/
? exception
? when errormessage then
? dbms_output.put_line('無法打開數據表');
? /*程序執行結束*/
end;

5.執行結果，程序調用。
主程序mainprocess
/*定義6個入口變量，分別對應graduate數據表中的專業課和總分分數線*/
declare
? score1 number(4,1);
? score2 number(4,1);
? score3 number(4,1);
? score4 number(4,1);
? score5 number(4,1);
? scoretotal number(5,1);
/*將分數線賦值，在這里修改各值就代表不同的分數線*/
begin
? score1 := 50;
? score2 := 56;
? score3 := 60;
? score4 := 62;
? score5 := 64;
? scoretotal := 325;
/*調用處理過程*/
? graduateprocess(score1,score2,score3,score4,score5,scoretotal);
end;

綜合運用pl/sql的設計要素，就可以設計出很多復雜的處理程序，這也是DBA的一項重要任務。

Oracle Class6. PL/SQL 簡介(數據類型,邏輯比較,控制結構,錯誤處理)