PLSQL學(xué)習(xí)筆記1

1、一、 pl/sql基本語法要素1. 相對其他語言特殊運算符=號是是否相等的比較運算符:=才是賦值運算符 兩個單引號之間表示字符類型的變量 雙引號之間標(biāo)識引用，如果字段名，數(shù)據(jù)庫名等| 表示字符之間的連接符-表示單行注釋%屬性指示器2. pl/sql變量及數(shù)據(jù)類型1. 變量類型標(biāo)量類型：數(shù)值，字符，日期，布爾復(fù)合類型：索引表，嵌套表，數(shù)組引用類型：游標(biāo)類型（cursor）,對象類型（ref）lob類型：大文本等2. 使用%定義變量的類型跟某個表的列屬性一致的變量的聲明 v_name %type 表示v_name 這個變量與表student中的列name 屬性一樣v_stu

2、dent student%rowtype 表示v_student這個變量客戶存student中的一行數(shù)據(jù)3. 用select into個變量賦值的時候select結(jié)果必須只有一行數(shù)據(jù)，如果多行會包too many row錯誤，空的話則會報空錯誤。3. pl/sql中最基本的語句塊結(jié)構(gòu)declare 聲明變量，要使用的變量必須在這里聲明begin 需要執(zhí)行的程序語句exception異常處理語句end二、 pl/sql 程序順序結(jié)構(gòu)（分支和循環(huán)）1. 正常都是從上到下的順序結(jié)構(gòu)2. 使用if進行分支處理if 語句的格式：if condition_1 then statement_1; elsif

3、 condition_n then statement_n;else else_statements; end if;3. 使用case進行分支處理case語句格式：1. 單值比較: case expression when result_1 then statements_1;when result_2 then statements_2;else else_statement;end case;2. 多值比較： case when expression_1 then statement_1;when expression_2 then statement_2;else else_state

4、ments;end case;4. 使用循環(huán)結(jié)構(gòu)1. 使用loop循環(huán)結(jié)構(gòu)：loop statements；exit when expression；end loop；滿足expression條件的時候跳出循環(huán)；2. 使用while循環(huán)結(jié)構(gòu)：while expression loop statements；end loop；expression 的格式：vi=10 不加分號3. 使用for循環(huán)結(jié)構(gòu)：for expression loop statements；end loop;expression 的格式：vi in 1.10 不叫分號三、 游標(biāo)基礎(chǔ)及使用1. 游標(biāo)的定義：游標(biāo)用于表示一查詢結(jié)

5、果的結(jié)果集2. 游標(biāo)使用四個步驟：-1.聲明declare - 2.打開 open -3.讀取fetch -4.關(guān)閉close1. 聲明游標(biāo)：cursor cursor_name is select_statements;例如：cursor cursor_student is select id,name,age,sex from students where name like 林%；2. 使用游標(biāo)是打開：open cursor_name;例如：open cursor_student;打開游標(biāo)的時候系統(tǒng)才會去查詢游標(biāo)聲明的數(shù)據(jù)集；3. 讀取數(shù)據(jù)：fetch cursor_name into

6、variable_name1,variable_name2;例如：fetch cursor_studnet into v_id,v_sname,v_age,v_sex;第一次讀取的是第一行數(shù)據(jù)，再次使用fetch讀取數(shù)據(jù)的時候是下一行所以處理游標(biāo)時一般都是用循環(huán)；4. 關(guān)閉游標(biāo)：close cursor_name;例如:close cursor_student;3. 游標(biāo)中的屬性：四個常用屬性：%isopen 、 %found 、 %notfound 、 %rowcount1. %isopen 返回游標(biāo)是否打開 使用方法 cursor_name%isopen 例如：cursor_student

7、%isopen;2. %notfund 返回最近一次fetch取數(shù)是否有取到數(shù)，沒取到返回true，有取到返回false；一般用于判斷循環(huán)終止使用方法：cursor_name%notfund 例如：cursor_student%notfound3. %found 與%notfound相反4. %rowcount 返回現(xiàn)在已經(jīng)讀取的行數(shù)使用方法：cursor_name%rowcount例如：cursor_student%rowcount4. 簡單使用游標(biāo)循環(huán)讀取數(shù)據(jù)的例子讀取并打印學(xué)生表中“計算機系學(xué)生的信息”declarev_specialty students.specialty%type;

8、 -聲明變量v_name %type;v_dob students.dob%type;cursor student_cur -聲明游標(biāo)is select name,dob from students where specialty=v_specialty;begin v_specialty:=計算機; -給變量賦值open student_cur; -打開游標(biāo)dbmes_output.put_line(學(xué)生姓名 出生日期); -輸出loop fetch student_cur into v_name,v_dob; -讀取游標(biāo)中的數(shù)據(jù)exit when student_

9、cur%notfund; -設(shè)定退出條件dbms_output.put_line(v_name| |v_dob); -輸出結(jié)果end loop; -循環(huán)體結(jié)束標(biāo)志close sutent_cur; -關(guān)閉游標(biāo)end; -塊結(jié)構(gòu)結(jié)束標(biāo)志5. 游標(biāo)的應(yīng)用：1. 對游標(biāo)指定的數(shù)據(jù)進行修改：需要對游標(biāo)指定的數(shù)據(jù)修改必須在聲明游標(biāo)的時候帶有for update 關(guān)鍵字語法：cursor cursor_name is select_statement for update of table_name notwait;of table_name 可以不寫，用于自動選擇多表的時候確定那些表需要鎖定；notwa

10、it可以不寫，用于確認(rèn)是否等待鎖。對數(shù)據(jù)操作的語句使用where current of 關(guān)鍵字表示修改游標(biāo)指向的當(dāng)前行。例如根據(jù)職稱調(diào)整教師工資：declarev_title teachers.title%type;cursor teachers_cur -聲明游標(biāo)并指定為可修改isselect title from teachers for update;begin open teachers_cur; -打開游標(biāo) loop fetch teachers_cur into v_title; -讀取游標(biāo)數(shù)據(jù) exit when teacher_cur%notfound; -退出循環(huán)條件 cas

11、e -使用case分支判斷 when v_title=教授 then update teachers set wage=1.1*wage where current of teachers_cur; -使用where current of 指定修改的當(dāng)前行 when v_title=高工 then update teachers set wage=1.05*wage where current of teachers_cur; end case; -結(jié)束case分支end loop;close teachers_cur;end;2. 使用游標(biāo)對數(shù)據(jù)進行刪除跟修改類似，參考修改的例子，同樣需要使

12、用 where current of 關(guān)鍵字。3. 游標(biāo)while循環(huán)open student_curfetch student_cur into v_name,v_specialty;while student_cur%found loop if v_specialty=計算機 then delete from students where current of students_cur; end if; fetch student_cur into v_name,v_specialty;end loop;close student_cur;4. 游標(biāo)for循環(huán)使用for循環(huán)不用使用fetc

13、h 和close 游標(biāo)格式一：(使用聲明的游標(biāo))for record_name in cursor_name loop statement1; statement2;end loop;格式二：(使用子查詢(匿名游標(biāo))for record_name in selectstatement loop statement1; statement2;end loop;例如顯示計算機系所有學(xué)生信息：格式一：declare v_specialty students.specialty%type; cursor students_cur is select name,dob from students whe

14、re specialty=v_specialty; begin v_specialty:=計算機; dbms_output.put_line(序號 學(xué)生姓名 出生日期); for student_record in students_cur loop dbms_output.put_line(students_cur%rowcount| |student_| |student_record.dob); end loop; end;格式二：declare v_specialty students.specialty%type; begin v_specialty:=計算機;

15、 dbms_output.put_line(學(xué)生姓名 出生日期); for student_record in (select name,dob from students where specialty=v_specialty) loop -直接使用只查詢 dbms_output.put_line( |student_| |student_record.dob); end loop; end;5. 使用游標(biāo)參數(shù)：(給游標(biāo)傳遞參數(shù))語法格式：cursor cursor_name(para_name1 datatype,para_name2 datatype,) is se

16、lect_statement;例如用計算機系作為參數(shù)顯示計算機系的學(xué)生信息：declare v_name students.name%type; v_dob students.dob%type; cursor students_cur(v_specialty students.specialty%type) is select name, dob from students where specialty = v_specialty;begin open students_cur(計算機系);-打開游標(biāo)的時候使用計算機系作為參數(shù) fetch students_cur into v_name,

17、v_dob; while students_cur%found loop dbms_output.put_line(v_name | | v_dob); fetch students_cur into v_name, v_dob; end loop; close students_cur;end;6. 游標(biāo)變量游標(biāo)變量可以指定動態(tài)的變更指定不同的游標(biāo)。游標(biāo)變量的使用也要經(jīng)過四個步驟：1.定義 2.打開 3.讀取數(shù)據(jù) 4.關(guān)閉數(shù)據(jù)1. 定義游標(biāo)變量：(1).定義游標(biāo)類型type ref_type_name is ref cursor return return_type;ref_type_nam

18、e 指定游標(biāo)變量使用的數(shù)據(jù)類型；return return_type 為ref_type_name指定返回的類型，可以省略，而且類型必須是record類型的數(shù)據(jù)；沒有指定return的時候游標(biāo)變量能跟任何select語句關(guān)聯(lián)；(2).定義游標(biāo)類型變量：cursor_variable ref_type_name;cursor_variable 游標(biāo)變量名；2. 打開游標(biāo)變量：open cursor_variable for select_statement;select_statement 查詢子句3. 讀取游標(biāo)變量的數(shù)據(jù)：fetch cursor_variable into variable1

19、,variable2,.;4. 關(guān)閉游標(biāo)變量：close cursor_variable;7. 游標(biāo)變量使用實例1. 顯示學(xué)生姓名和出生日期，不實用returndeclare type student_cur is ref cursor; -定義游標(biāo)類型stucursor student_cur; -聲明游標(biāo)變量students_record students%rowtype;begin if not stucursor%isopen then open stucursor for select * from students;-打開游標(biāo)變量并附select子句 end if; dbms_ou

20、tput.put_line(學(xué)生姓名 出生日期); loop fetch stucursor into students_record; -讀取游標(biāo)變量數(shù)據(jù) exit when stucursor%notfound; dbms_output.put_line(students_| |students_record.dob); end loop; close stucursor; -關(guān)閉游標(biāo)end;2. 顯示學(xué)生姓名和出生日期，使用returndeclare type students_record is record( -定義一個記錄類型的數(shù)據(jù) stuname varch

21、ar2(10), studob date ); sturecord students_record; -聲明一個記錄type students_cur is ref cursor return students_record; -定義一個游標(biāo)類型并且游標(biāo)類型的數(shù)據(jù)是上面的記錄類型 stucursor students_cur; -聲明一個游標(biāo)變量begin if not stucursor%isopen then open stucursor for select name,dob from students; -打開游標(biāo)select子句要和record一致 end if; dbms_outp

22、ut.put_line(學(xué)生姓名 出生日期); loop fetch stucursor into sturecord; exit when stucursor%notfound; dbms_output.put_line(sturecord.stuname| |sturecord.studob ); end loop; close stucursor; end;8. 游標(biāo)表達式游標(biāo)表達式在select語句中使用，構(gòu)成嵌套游標(biāo)。(在聲明游標(biāo)時候的select語句中使用)，返回是是游標(biāo)變量（ref cursor）。游標(biāo)表達式的格式:cursor(subquery)例子：循環(huán)顯示個系部的所有老師姓

23、名和職稱declare v_tname %type; v_title teachers.title%type; v_dname departments.department_name%type; type cursor_type is ref cursor; -定義一個游標(biāo)類型 -聲明一個游標(biāo)，并且一個游標(biāo)表達式嵌套 cursor departments_cur(dept_id number) is select d.department_name,cursor(select name,title from teachers where department_id=d

24、.department_id) from departments d where d.department_id=dept_id; teachers_cur cursor_type; -聲明一個游標(biāo)變量begin open departments_cur(101); -打開外層游標(biāo) loop fetch departments_cur into v_dname,teachers_cur; -取外層游標(biāo)數(shù)據(jù)同時把內(nèi)層游標(biāo)打開 exit when departments_cur%notfound; dbms_output.put_line(系部名稱：|v_dname); dbms_output.p

25、ut_line(教師名 職稱); loop fetch teachers_cur into v_tname,v_title; -取內(nèi)層游標(biāo)數(shù)據(jù) exit when teachers_cur%notfound; dbms_output.put_line(v_tname| |v_title);end loop;end loop;四、 復(fù)合數(shù)據(jù)類型1. 記錄類型顯示的定義記錄類型格式：type record_type_name is record( -定義一個記錄類型filed1_name datatype1, -記錄類型的成員 名稱和數(shù)據(jù)類型filed2_name datatype2,filed3

26、_name datatype3);variable_name record_type_name; -聲明一個記錄變量2. 記錄表類型數(shù)據(jù)記錄類型的數(shù)據(jù)類似二維數(shù)組定義記錄表類型的格式：type recordtable_type_name is table of table_name%rowtype| column_name%type index by binay_integer; -定義記錄表variable_name recordtable_type_name; -聲明記錄表table_name%rowtype跟column_name%type 為二選一指定一行數(shù)據(jù)中包含的變量binay_i

27、nteger 指定表元素下標(biāo)使用的數(shù)據(jù)類型使用記錄表是要指定列variable_name(index_number)如：variable_name(2) 表示下表為2的記錄type student_recordtable is table of students%rowtype index by binay_integer;select * into student_recordtable(1) from students where id=101;五、 子程序1. 存儲過程定義存儲過程的語句：create or replace procedure procedure_name (argume

28、nt_list in |out | in out argument_type,)is | asvariable_name1 datatype1; -聲明變量variable_name2 datatype2;beginprocedure_body;end procedure_name;or replace 可加可不加，加的話會覆蓋相同名稱的存儲過程argument_list 表示參數(shù)列表argument_type 表示參數(shù)類型in |out | in out 三選一 in 表示外部輸入給過程，out表示參數(shù)在過程中可以被賦值is | as 二選一variable_name1 datatype1

29、聲明變量和變量類型調(diào)用或執(zhí)行存儲過程：call| execute procedure_name(argument_list);例如：顯示某系的老師的平均工資，最大工資，最小工資create or replace procedure display_teacher(v_no teachers.department_id%type)as v_wage teachers.wage%type; v_maxwage teachers.wage%type; v_minwage teachers.wage%type;begin select avg(wage) into v_wage from teache

30、rs where department_id=v_no; select max(wage) into v_maxwage from teachers where department_id=v_no; select min(wage) into v_minwage from teachers where department_id=v_no; dbms_output.put_line(該系平均工資：|v_wage); dbms_output.put_line(該系最大工資：|v_maxwage); dbms_output.put_line(該系最低工資：|v_minwage);exceptio

31、n when no_data_found then dbms_out.put_line(該系不存在。);end display_teacher;存儲過程管理：在系統(tǒng)里面查找存儲過程 select object_name,created,status from user_objects where object_name=procedure_name查看存儲過程代碼：select text from user_source where name=procedure_name2. 函數(shù)函數(shù)與過程的不同在于函數(shù)可以換回值，過程不能有返回值函數(shù)定義格式：create or replace functi

32、on function_name (argument_list in |out | in out argument_type,)return datatype -返回數(shù)據(jù)類型is | asvariable_name1 datatype1; -聲明變量variable_name2 datatype2;begin procedure_body; return expression; -換回語句end procedure_name;調(diào)用函數(shù)方法：直接用表達式：function_name(argument_list)函數(shù)管理:查找函數(shù)：select object_name,created,status

33、 from user_objects where object_name=function_name;查看函數(shù)代碼:select text from user_source where name=function_name;3. 觸發(fā)器觸發(fā)器是特殊的子程序，不能被調(diào)用。只能被觸發(fā)。由select insert update delete 等dml操作觸發(fā)觸發(fā)關(guān)鍵字after 和 before 之后和之前觸發(fā)觸發(fā)器格式：create or replace trigger trigger_name before | after insert or update or delete of colum

34、n_name on table_namebegin trigger_statement1; raise_application_error(-20000, error message); -定義觸發(fā)器執(zhí)行時檢測條件不符合時的錯誤 end trigger_name;before | after 二選一 before就是在觸發(fā)事件前觸發(fā)insert or update or delete 可以一個或者多個column_name 表示修改對應(yīng)的列時觸發(fā) 可不寫表示對整張表簡單的觸發(fā)器實例：非工作時間無法修改教師信息：create or replace trigger change_teacher b

35、efore insert or update or delete on teachersbegin if to_char(sysdate, hh24) not between 8 and 17 or to_char(sysdate, dy, nls date_langudage=american) in (sat, sun) then raise_application_error(-20000, 非工作時間不能改變教師信息！); end if end change_teacher;觸發(fā)器管理：查詢系統(tǒng)中的觸發(fā)器：select * from user_triggers where trigger_name=trigger_name;啟用，禁用，刪除觸發(fā)器alter trigger trigger_name enable;alter trigger trigger_name disable;drop trigger trigger_name;4. 創(chuàng)建臨時表：會話級的臨時表：create global temporary table table_name (col1 type1,col2 type2.) on commit preserve rows;例：create global temporary table