------------------------2013-5-16------------------------
1.sql概念，主要特點，使用sql工具，如何訪問及本書實例兩個數據表
2.單個表查詢
3.多個表查詢
4.復雜的嵌套查詢
5.查詢函數
6.sql錄入數據
7.sql刪除數據
8.sql更新數據

sql已經被ANSI（美國國家標準化組織）確定為數據庫系統的工業標準。

DQL：查詢
DDL：建立，刪除和修改數據對象
DML：完成數據操作的命令，包括查詢。
DCL：控制對數據庫的訪問，服務器的關閉，啟動等。

--\Class1\擴展\Oracle9.0入門_基本SQL.pdf
--\Class6\擴展\Oracle9.0入門_04_SQL.pdf? 相同

set serveroutput on? 打開輸出。

case .. when .. then .. else .. end case條件判斷
declare
lv_Age_num number default 99;
lv_Name_char varchar2(8) := 'AZhu';
lc_Sex_char constant char(8) := 'Male';
begin
? dbms_output.put_line (lv_Age_num);
? dbms_output.put_line ('===========');
? lv_Age_num := 15;
? lv_Name_char := 'ANiu';
? --lc_Sex_char := 'Female';
? case lv_Age_num
? when 12 then
??? dbms_output.put_line (lv_Name_char || '大于10!');
? when 10 then
??? dbms_output.put_line (lv_Name_char || '等于10!');
? else
??? dbms_output.put_line (lv_Name_char || '小于10!');
? end case;
end;

declare
lv_Age_num number default 99;
lv_Name_char varchar2(8) := 'AZhu';
lc_Sex_char constant char(8) := 'Male';
begin
? dbms_output.put_line (lv_Age_num);
? dbms_output.put_line ('===========');
? lv_Age_num := 9;
? lv_Name_char := 'ANiu';
? --lc_Sex_char := 'Female';
? case
? when lv_Age_num > 10 then
??? dbms_output.put_line (lv_Name_char || '大于10!');
? when lv_Age_num = 10 then
??? dbms_output.put_line (lv_Name_char || '等于10!');
? else
??? dbms_output.put_line (lv_Name_char || '小于10!');
? end case;
end;

sql%found判斷，返回影響的行數：sql%rowcount
begin
? update class6 set cname = 'PP' where cid = 3;
? if sql%found then
??? dbms_output.put_line(sql%rowcount);
? else
??? dbms_output.put_line('OhNo!');
? end if;
end;

for循環
for lv_ID_num in 2 .. 4? --退出條件--
? loop

? end loop;

declare
lv_ID_num2 int := 12;
begin
? for lv_ID_num in 8 .. 10?? --for變量,--退出條件--
? loop
??? insert into Class6 values (lv_ID_num2, 'ANiu' || to_char(lv_ID_num2));
??? lv_ID_num2 := lv_ID_num2 + 1;?? --如果寫成lv_ID_num := lv_ID_num + 1; 會報錯。
? end loop;
? commit;
end;

?while lv_ID_num < 8? --退出條件--
? loop?

? end loop;?

loop
??? exit when lv_ID_num = 21;? --退出條件--

end loop;

declare
lv_Title_char varchar2(80);
lv_Price_num number(19,4);
begin
? select? cname,cid
? into lv_Title_char, lv_Price_num??? --可以賦予給多個變量--
? from class6
? where cid='2';??? --'2'可以寫成變量賦值的方式--
? dbms_output.put_line (lv_Title_char);
? dbms_output.put_line (lv_Price_num);
end;

declare
-- 定義記錄(Record)
type type_Title is record??? --創建類型--
(
lv_Title_char varchar2(80),
lv_Price_num number(19,4)
);
lv_title type_Title;
lv_TitleID_char varchar2(80);
begin
? lv_TitleID_char := 3;
? select? cname,cid
? into lv_title??? --賦予給記錄類型變量--
? from class6
? where cid= lv_TitleID_char;
? dbms_output.put_line (lv_title.lv_Title_char);
? dbms_output.put_line (lv_title.lv_Price_num);
end;

###
create type add_type as object(
? street varchar2(10),? --街道名
? city varchar2(10),??? --城市名
? state char(2),??????? --州代碼
? zip?? number????????? --郵編
);
### -->ORACLE埃里森4.txt

創建視圖
create view viewTitles
as
select cid, cname from class6

declare
-- 定義記錄(Record)
lv_title viewTitles%rowtype;??????????? --視圖類型方式--, --表類型: lv_title class6%rowtype;
lv_TitleID_char varchar2(80);
begin
? lv_TitleID_char := 3;
? select cid, cname
? into lv_title??? --賦予給記錄類型變量--
? from class6
? where cid= lv_TitleID_char;
? dbms_output.put_line (lv_title.cid);
? dbms_output.put_line (lv_title.cname);
end;

%type理解,只要是類型都可以引用.
lv_Name_char varchar2(8) := 'AZhu';
lv_Sex_char lv_Name_char%type := 'Male';? --引用變量--

---------------------游標完整例子---------------------
declare
-- 聲明游標(關聯Select語句)
cursor cur_Titles is select cname,cid from class6;
lv_Title_char class6.cname%type;
lv_Price_num class6.cid%type;
begin
? -- 打開游標
? open cur_Titles;
? -- 提取游標
? fetch cur_Titles into lv_Title_char, lv_Price_num;
? loop
??? exit when not cur_Titles%Found;? --退出循環條件
??? dbms_output.put_line ('游標demo========');
??? dbms_output.put_line (lv_Title_char);
??? dbms_output.put_line (lv_Price_num);
??? fetch cur_Titles into lv_Title_char, lv_Price_num;? --循環讀取
? end loop;
? -- 關閉游標
? close cur_Titles;
end;

--游標類型--
declare
-- 聲明游標(關聯Select語句)
cursor cur_Titles is select cname,cid from class6;
lv_title_rec cur_Titles%rowtype;?? ????????? --引用游標類型--
begin
? -- 打開游標
? open cur_Titles;
? -- 提取游標
? fetch cur_Titles into lv_title_rec;
? loop
??? exit when not cur_Titles%Found;? --退出循環條件
??? dbms_output.put_line ('游標demo========');
??? dbms_output.put_line (lv_title_rec.cid);
??? dbms_output.put_line (lv_title_rec.cname);
??? fetch cur_Titles into lv_title_rec;? --循環讀取
? end loop;
? -- 關閉游標
? close cur_Titles;
end;

select? .. for update ..
規則是：FOR UPDATE語句將鎖住查詢結果中的元組，這些元組將不能被其他事務的UPDATE，DELETE和FOR UPDATE操作，直到本事務提交。

在newlifeyhj帳戶下面建立scott.emp表的結構和數據用來測試:
create table class6emp as
select * from scott.emp;

select sal from class6emp where job = 'CLERK';
?????? SAL
----------
?????? 800
????? 1100
?????? 950
????? 1300

##修改,取當前游標的記錄.where current of c##
create or replace procedure proGeMing2
as
cursor c is select empno, job, sal from class6emp for update;
lv_emp_rec c%rowtype;?? --那么這行不要
begin
?? open c;??? --那么這行不要
?? --for lv_emp_rec in c? --另外一種fetch方式--
?? loop
?????????? fetch c into lv_emp_rec;? --那么這行不要
?????????? exit when not c%found;??? --那么這行不要
?????????? case lv_emp_rec.job
?????????? when 'CLERK' then
?????????????????? update class6emp set sal = sal * 2 where current of c;? --c是游標
?????????? when 'SALESMAN' then
?????????????????? update class6emp set sal = sal / 2 where current of c;
?????????? when 'MANAGER' then
?????????????????? update class6emp set sal = 0 where current of c;
?????????? else
?????????????????? update class6emp set sal = 250 where current of c;
?????????? end case;
?? end loop;
?? close c;??????? --那么這行不要
end;

begin
proGeMing2;
end;

select sal from class6emp where job = 'CLERK';
?????? SAL
----------
????? 1600
????? 2200
????? 1900
????? 2600

create or replace procedure proGeMing2
as
cursor c is select empno, job, sal from class6emp for update;
begin
?? for lv_emp_rec in c? --另外一種fetch方式--
?? loop
?????????? case lv_emp_rec.job
?????????? when 'CLERK' then
?????????????????? update class6emp set sal = sal * 2 where current of c;? --c是游標
?????????? when 'SALESMAN' then
?????????????????? update class6emp set sal = sal / 2 where current of c;
?????????? when 'MANAGER' then
?????????????????? update class6emp set sal = 0 where current of c;
?????????? else
?????????????????? update class6emp set sal = 250 where current of c;
?????????? end case;
?? end loop;
end;

select sal from class6emp where job = 'CLERK';
?????? SAL
----------
????? 3200
????? 4400
????? 3800
????? 5200

不使用游標的方式,直接查詢。那么就不可以用where current of c
create or replace procedure proGeMing2
as
begin
?? for lv_emp_rec in (select empno, job, sal from class6emp for update)
?? loop
?????? case lv_emp_rec.job
?????? when 'CLERK' then
?????????????? update class6emp set sal = sal * 2 where empno = lv_emp_rec.empno;
?????? when 'SALESMAN' then
?????????????? update class6emp set sal = sal / 2 where empno = lv_emp_rec.empno;
?????? when 'MANAGER' then
?????????????? update class6emp set sal = 0 where empno = lv_emp_rec.empno;
?????? else
?????????????? update class6emp set sal = 250 where empno = lv_emp_rec.empno;
?????? end case;
?? end loop;
end;

select sal from class6emp where job = 'CLERK';
?????? SAL
----------
????? 6400
????? 8800
????? 7600
???? 10400

##帶參數的游標##? 變量方式
declare
-- 聲明參數游標
cursor cur_Titles(p_t class6.cid%type)
is
select cname,cid from class6 where cid = p_t;
lv_Title_char class6.cname%type;
lv_Price_num class6.cid%type;
begin
? -- 打開游標
? open cur_Titles('2');?? --參數游標--
? loop
??? -- 提取游標
??? fetch cur_Titles into lv_Title_char, lv_Price_num;
??? exit when not cur_Titles%Found;
??? dbms_output.put_line ('========');
??? dbms_output.put_line (lv_Title_char);
??? dbms_output.put_line (lv_Price_num);
? end loop;
? -- 關閉游標
? close cur_Titles;
? -- 打開游標
? open cur_Titles('3');?? --參數游標--
? loop
??? -- 提取游標
??? fetch cur_Titles into lv_Title_char, lv_Price_num;
??? exit when? cur_Titles%notFound;
??? dbms_output.put_line ('========');
??? dbms_output.put_line (lv_Title_char);
??? dbms_output.put_line (lv_Price_num);
? end loop;
? -- 關閉游標
? close cur_Titles;
end;

--記錄方式存儲--
declare
type ttt is record(
? lv_Title_char class6.cname%type,
? lv_Price_num class6.cid%type
);
-- 聲明參數游標
cursor cur_Titles(p_t class6.cid%type)
is
select cname,cid from class6 where cid = p_t;
--lv_Title_char class6.cname%type;
--lv_Price_num class6.cid%type;
objttt ttt;
begin
? -- 打開游標
? open cur_Titles('2');?? --參數游標--
? loop
??? -- 提取游標
??? --fetch cur_Titles into lv_Title_char, lv_Price_num;
??? fetch cur_Titles into objttt;
??? exit when not cur_Titles%Found;
??? dbms_output.put_line ('========');
??? dbms_output.put_line (objttt.lv_Title_char);
??? dbms_output.put_line (objttt.lv_Price_num);
??? --如果游標有多條記錄，那么循環取。
? end loop;
? -- 關閉游標
? close cur_Titles;
end;

?loop
??? -- 提取游標
??? fetch cur_Titles into objttt;

? end loop;
或
?-- 提取游標
?fetch cur_Titles into objttt;
?loop
???
???
??? fetch cur_Titles into objttt;? --需要再次提取游標，便于循環。
? end loop;

##定義引用游標##
declare
type ttt is record(
? lv_Title_char class6.cname%type,
? lv_Price_num class6.cid%type
);
-- 定義引用游標
type type_Titles_cur is ref cursor;???? --這個地方是關鍵。
-- 聲明游標
cur_Titles type_Titles_cur;
objttt ttt;
begin
? -- 打開游標
? open cur_Titles
? for
? select cname, cid from class6;?????? --給游標賦予值。
? loop
??? -- 提取游標
??? fetch cur_Titles into objttt;
??? exit when not cur_Titles%Found;
??? dbms_output.put_line ('========');
??? dbms_output.put_line (objttt.lv_Title_char);
??? dbms_output.put_line (objttt.lv_Price_num);
? end loop;
? -- 關閉游標
? close cur_Titles;
end;

##循環游標##
-- 循環游標
declare
-- 定義記錄(Record)
-- 聲明游標
cursor cur_Titles
is
select cid, cname from class6;
begin
? -- 自動打開游標
? -- 自動提取游標存儲到一個自動定義的記錄變量
? -- 在循環結束部分自動提取下一條記錄
? -- 當讀到游標結尾自動關閉游標
? for lv_Title_type in cur_Titles
? loop
??? dbms_output.put_line ('========');
??? dbms_output.put_line (lv_Title_type.cid);
??? dbms_output.put_line (lv_Title_type.cname);
? end loop;
? -- 關閉游標
end;

##隱式游標##
-- 隱式游標
declare
-- 定義記錄(Record)
-- 自動聲明游標
begin
?-- 自動打開游標
?-- 自動提取游標存儲到一個自動定義的記錄變量
?-- 在循環結束部分自動提取下一條記錄
?-- 當讀到游標結尾自動關閉游標
?for lv_Title_type in (select cid, cname from class6)
?loop
? dbms_output.put_line ('========');
? dbms_output.put_line (lv_Title_type.cid);
? dbms_output.put_line (lv_Title_type.cname);
?end loop;
?-- 關閉游標
end;

PRAGMA EXCEPTION_INIT的用法???? --自動觸發，而不要手動raise 異常名; --
http://blog.csdn.net/wanggangytsoft/article/details/5408692

ora-01843: 這個錯誤代表無效的月份一般在日期轉化的時候會提示。
ORA-00001: 違反唯一約束條件

ORA-06511 CURSOR_ALREADY_OPEN: 程序嘗試打開一個已經打開的游標。一個游標在重新打開之前必須關閉。
ORA-00001 DUP_VAL_ON_INDEX: 唯一索引上有重復值

-- 自定義錯誤消息
-- Raise_Application_error
raise_application_error(-20001, '沒有！');

ORA-01722 invalid number 無效數字
ORA-01476: divisor is equal to zero 這個錯誤是sql語句中存在除數為0的情況

ora-01422：輸出值太多。查詢返回的記錄行大于1。

NO_DATA_FOUND

?

##游標異常##
declare
-- 聲明游標
cursor cur_Titles
is
select * from class6;
begin
? -- 打開游標
? open cur_Titles;
? open cur_Titles;
? -- 關閉游標
? close cur_Titles;
exception
? when CURSOR_ALREADY_OPEN then?????????????????? --重復打開問題-- CURSOR_ALREADY_OPEN
??? dbms_output.put_line('Cursor is Opened!');
??? dbms_output.put_line('Cursor is Opened!');
end;

#存儲過程參數 in 是參數的默認模式。# in, out, in out三種類型。

##創建存儲過程##
create or replace procedure GetTitle
(
p_titleid in class6.cid%type := '2',
p_title out class6.cid%type,
p_price out class6.cname%type
)
as
begin
select cid, cname into p_title, p_price from class6 where cid = p_titleid;
end;

##調用存儲過程##
declare
lv_title_char class6.cid%type;
lv_price_num class6.cname%type;
begin
GetTitle (p_title => lv_title_char, p_price => lv_price_num);??? --參數名稱要和過程定義的相同--，否則參數個數不匹配。
? --GetTitle (4,lv_title_char, lv_price_num);??? --參數接受，個數匹配--這樣也可以的!!! --
dbms_output.put_line(lv_title_char);
dbms_output.put_line(lv_price_num);
end;

?

create or replace procedure GetTitle
(
p_title out class6.cid%type,
p_price out class6.cname%type
)
as
begin
select cid, cname into p_title, p_price from class6 where cid = 2;
end;

declare
lv_title_char class6.cid%type;
lv_price_num class6.cname%type;
begin
GetTitle (lv_title_char, lv_price_num);??? --參數接受，個數匹配--
dbms_output.put_line(lv_title_char);
dbms_output.put_line(lv_price_num);
end;

create or replace procedure GetTitleByTitleID2
(
p_TitleID in class6.cid%type,
p_Title out class6.cid%type,
p_Price out class6.cname%type???? ??? --關鍵點：這個地方沒有逗號。--
)
as
begin
select cid, cname into p_Title, p_Price from class6 where cid = p_TitleID;
end;

?

exec 不要寫，否則會報錯。

set serveroutput on; 打開，否則無法看到輸出信息，pl/sql developer 測試窗口可以運行存儲過程。

set serveroutput on;
declare
lv_Title class6.cid%type;
lv_Price class6.cname%type;
begin
? GetTitleByTitleID2 ( 2, lv_Title,lv_Price);
? dbms_output.put_line('----' || lv_Title);
? dbms_output.put_line('====' || lv_Price);
end;

綜合存儲過程和調用與過程的對比。
declare
lv_Title class6.cid%type;
lv_Price class6.cname%type;
begin
? select cid, cname into lv_Title, lv_Price from class6 where cid = 2;
? dbms_output.put_line('----' || lv_Title);
? dbms_output.put_line('====' || lv_Price);
end;

#類型的引用可以基于表，也可以基于視圖。!!!#

##函數## 與存儲過程的區別，是函數是有返回值的。
--創建函數
create or replace function funGetTotalQtyByTitleID
(
p_titleid in class6.cid%type
)
return class6.cname%type??????? --返回類型--
as
p_totalqty class6.cname%type;
begin
select cname into p_totalqty from class6 where cid = p_titleid;
return (p_totalqty);??????????? --返回值--與返回類型一一匹配
end;

#函數的調用#與系統函數的調用和使用是一樣的
select funGetTotalQtyByTitleID('4') from dual;

-- 賦值語句
declare
p_totalqty class6.cname%type;
begin
p_totalQty := funGetTotalQtyByTitleID('5');
dbms_output.put_line(p_totalqty);
end;

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