ORACLEPLSQL存儲(chǔ)過程教程

1、(1)seqname.nextval里面的值如何讀出來？可以直接在insert into test values(seqname.nextval) 是可以用 這樣： select tmp#_seq.nextval into id_temp from dual; 然后可以用id_temp(2)pls-00103: 出現(xiàn)符號(hào) 在需要下列之一時(shí)：代碼如下： if (sum0) then begin insert into emesp.tp_sn_production_log values (r_serial_number, , id_temp); exit; end; 一直報(bào)sum0 這是個(gè)很郁悶的

2、問題 因?yàn)樽兞坑昧藄um 所以不行，后改為i_sum0(3)oracle 語(yǔ)法1. oracle應(yīng)用編輯方法概覽答：1) pro*c/c+/. : c語(yǔ)言和數(shù)據(jù)庫(kù)打交道的方法，比oci更常用; 2) odbc 3) oci: c語(yǔ)言和數(shù)據(jù)庫(kù)打交道的方法，和proc很相似，更底層，很少用; 4) sqlj: 很新的一種用java訪問oracle數(shù)據(jù)庫(kù)的方法，會(huì)的人不多; 5) jdbc 6) pl/sql: 存儲(chǔ)在數(shù)據(jù)內(nèi)運(yùn)行, 其他方法為在數(shù)據(jù)庫(kù)外對(duì)數(shù)據(jù)庫(kù)訪問;2. pl/sql答：1) pl/sql(procedual language/sql)是在標(biāo)準(zhǔn)sql的基礎(chǔ)上增加了過程化處理的語(yǔ)言;

3、2) oracle客戶端工具訪問oracle服務(wù)器的操作語(yǔ)言; 3) oracle對(duì)sql的擴(kuò)充;4. pl/sql的優(yōu)缺點(diǎn)答：優(yōu)點(diǎn)： 1) 結(jié)構(gòu)化模塊化編程，不是面向?qū)ο? 2) 良好的可移植性(不管oracle運(yùn)行在何種操作系統(tǒng)); 3) 良好的可維護(hù)性(編譯通過后存儲(chǔ)在數(shù)據(jù)庫(kù)里); 4) 提升系統(tǒng)性能; 第二章pl/sql程序結(jié)構(gòu)1. pl/sql塊答：1) 申明部分, declare(不可少); 2) 執(zhí)行部分, begin.end; 3) 異常處理，exception(可以沒有);2. pl/sql開發(fā)環(huán)境答：可以運(yùn)用任何純文本的編輯器編輯，例如：vi ；toad很好用3. pl/s

4、ql字符集答：pl/sql對(duì)大小寫不敏感4. 標(biāo)識(shí)符命名規(guī)則答：1) 字母開頭; 2) 后跟任意的非空格字符、數(shù)字、貨幣符號(hào)、下劃線、或# ; 3) 最大長(zhǎng)度為30個(gè)字符(八個(gè)字符左右最合適);5. 變量聲明答：語(yǔ)法 var_name type constantnot null:=value; 注：1) 申明時(shí)可以有默認(rèn)值也可以沒有; 2) 如有constantnot null, 變量一定要有一個(gè)初始值; 3) 賦值語(yǔ)句為“:=”; 4) 變量可以認(rèn)為是數(shù)據(jù)庫(kù)里一個(gè)字段; 5) 規(guī)定沒有初始化的變量為null;第三章1. 數(shù)據(jù)類型答：1) 標(biāo)量型：數(shù)字型、字符型、布爾型、日期型; 2) 組合型

5、：record(常用)、table(常用)、varray(較少用) 3) 參考型：ref cursor(游標(biāo))、ref object_type 4) lob(large object)2. %type答：變量具有與數(shù)據(jù)庫(kù)的表中某一字段相同的類型 例：v_firstname studengts.first_name%type;3. record類型答：type record_name is record( /*其中type，is，record為關(guān)鍵字，record_name為變量名稱*/field1 type not null:=expr1, /*每個(gè)等價(jià)的成員間用逗號(hào)分隔*/field2 ty

6、pe not null:=expr2, /*如果一個(gè)字段限定not null，那么它必須擁有一個(gè)初始值*/ . /*所有沒有初始化的字段都會(huì)初始為nullfieldn type not null:=exprn);4. %rowtype答：返回一個(gè)基于數(shù)據(jù)庫(kù)定義的類型 declare v_sturec student%rowtype; /*student為表的名字*/ 注：與3中定一個(gè)record相比，一步就完成，而3中定義分二步：a. 所有的成員變量都要申明; b. 實(shí)例化變量;5. table類型答：type tabletype is table of type index by binar

7、y_integer; 例：declare type t_stutable is table of student%rowtype index by binary_interger; v_student t_stutable;begin select * into v_student(100) from student where id = 1001; end; 注：1) 行的數(shù)目的限制由binary_integer的范圍決定;6. 變量的作用域和可見性答：1) 執(zhí)行塊里可以嵌入執(zhí)行塊; 2) 里層執(zhí)行塊的變量對(duì)外層不可見; 3) 里層執(zhí)行塊對(duì)外層執(zhí)行塊變量的修改會(huì)影響外層塊變量的值;第四章1.

8、 條件語(yǔ)句答：if boolean_expression1 then . elsif boolean_expression2 then /*注意是elsif，而不是elseif*/ . /*else語(yǔ)句不是必須的，但end if;是必須的*/ else . end if;2. 循環(huán)語(yǔ)句答：1) loop . if boolean_expr then /* */ exit; /* exit when boolean_expr */ end if; /* */ end loop; 2) while boolean_expr loop . end loop; 3) for loop_counter

9、in reverse low_blound.high_bound loop . end loop; 注：a. 加上reverse 表示遞減，從結(jié)束邊界到起始邊界，遞減步長(zhǎng)為一; b. low_blound 起始邊界; high_bound 結(jié)束邊界;3. goto語(yǔ)句答：goto label_name; 1) 只能由內(nèi)部塊跳往外部塊; 2) 設(shè)置標(biāo)簽： 3) 示例： loop . if d%rowcount = 50 then goto l_close; end if; . end loop; ; .4. null語(yǔ)句答：在語(yǔ)句塊中加空語(yǔ)句，用于補(bǔ)充語(yǔ)句的完整性。示例： if boolean_

10、expr then . else null; end if;5. sql in pl/sql答：1) 只有dml sql可以直接在pl/sql中使用;第五章1. 游標(biāo)(cursor)答：1) 作用：用于提取多行數(shù)據(jù)集; 2) 聲明：a. 普通申明：delcare cursor cursor_name is select_statement /* cursor的內(nèi)容必須是一條查詢語(yǔ)句*/ b. 帶參數(shù)申明：delcare cursor c_stu(p_id student.id%type) select * from student where id = p_id; 3) 打開游標(biāo)：open c

11、ursor_name; /*相當(dāng)于執(zhí)行select語(yǔ)句，且把執(zhí)行結(jié)果存入cursor; 4) 從游標(biāo)中取數(shù)：a. fetch cursor_name into var1, var2, .; /*變量的數(shù)量、類型、順序要和table中字段一致;*/ b. fetch cursor_name into record_var; 注：將值從cursor取出放入變量中，每fetch一次取一條記錄; 5) 關(guān)閉游標(biāo): close cursor_name; 注：a. 游標(biāo)使用后應(yīng)該關(guān)閉; b. 關(guān)閉后的游標(biāo)不能fetch和再次close; c. 關(guān)閉游標(biāo)相當(dāng)于將內(nèi)存中cursor的內(nèi)容清空;2. 游標(biāo)的屬性

12、答：1) %found: 是否有值; 2) %notfound: 是否沒有值; 3) %isopen: 是否是打開狀態(tài); 4) %rowcount: cursor當(dāng)前的記錄號(hào);3. 游標(biāo)的fetch循環(huán)答：1) loop fetch cursor into . exit when cursor%notfound; /*當(dāng)cursor中沒記錄后退出*/ end loop; 2) while cursor%found loop fetch cursor into . end loop; 3) for var in cursor loop fetch cursor into. end loop; 第

13、六章1. 異常答：declare . e_toomanystudents exception; /* 申明異常 */ . begin . raise e_toomanystudents; /* 觸發(fā)異常 */ . exception when e_toomanystudents then /* 觸發(fā)異常 */ . when others then /* 處理所有其他異常 */ . end;2004-9-8 星期三 陰pl/sql數(shù)據(jù)庫(kù)編程(下)1. 存儲(chǔ)過程(procedure)答：創(chuàng)建過程： create or replace procedure proc_name (arg_namein|

14、out|in outtype, arg_namein|out|in outtype) is|as procedure_body 1) in: 表示該參數(shù)不能被賦值(只能位于等號(hào)右邊); 2) out:表示該參數(shù)只能被賦值(只能位于等號(hào)左邊); 3) in out: 表示該類型既能被賦值也能傳值;2. 存儲(chǔ)過程例子答：create or replace procedure modetest( p_inparm in number, p_outparm out number, p_inoutparm in out number) is v_localvar number; /* 聲明部分 */ b

15、egin v_localvar:=p_inparm; /* 執(zhí)行部分 */ p_outparm:=7; p_inoutparm:=7; . exception . /* 異常處理部分 */ end modetest; 3. 調(diào)用procedure的例子答：1) 匿名塊可以調(diào); 2) 其他procdeure可以調(diào)用; 例： declare v_var1 number; begin modetest(12, v_var1, 10); end; 注：此時(shí)v_var1等于74. 指定實(shí)參的模式答：1) 位置標(biāo)示法：調(diào)用時(shí)添入所有參數(shù)，實(shí)參與形參按順序一一對(duì)應(yīng); 2) 名字標(biāo)示法：調(diào)用時(shí)給出形參名字，并

16、給出實(shí)參 modetest(p_inparm=12, p_outparm=v_var1, p_inout=10); 注：a. 兩種方法可以混用; b. 混用時(shí)第一個(gè)參數(shù)必須通過位置來指定。5. 函數(shù)(function)與過程(procedure)的區(qū)別答：1) 過程調(diào)用本身是一個(gè)pl/sql語(yǔ)句(可以在命令行中通過exec語(yǔ)句直接調(diào)用); 2) 函數(shù)調(diào)用是表達(dá)式的一部分;6. 函數(shù)的聲明答：create or replace procedure proc_name (arg_namein|out|in outtype, arg_namein|out|in outtype) return typ

17、e is|as procedure_body 注：1) 沒有返回語(yǔ)句的函數(shù)將是一個(gè)錯(cuò)誤;7. 刪除過程與函數(shù)答：drop procedure proc_name; drop function func_name;第八章1. 包答：1) 包是可以將相關(guān)對(duì)象存儲(chǔ)在一起的pl/sql的結(jié)構(gòu); 2) 包只能存儲(chǔ)在數(shù)據(jù)庫(kù)中，不能是本地的; 3) 包是一個(gè)帶有名字的聲明; 4) 相當(dāng)于一個(gè)pl/sql塊的聲明部分; 5) 在塊的聲明部分出現(xiàn)的任何東西都能出現(xiàn)在包中; 6) 包中可以包含過程、函數(shù)、游標(biāo)與變量; 7) 可以從其他pl/sql塊中引用包，包提供了可用于pl/sql的全局變量。 8) 包有包頭和

18、包主體，如包頭中沒有任何函數(shù)與過程，則包主體可以不需要。2. 包頭答：1) 包頭包含了有關(guān)包的內(nèi)容的信息，包頭不含任何過程的代碼。 2) 語(yǔ)法： create or replace package pack_name is|as procedure_specification|function_specification|variable_declaration|type_definition|exception_declaration|cursor_declaration end pack_name; 3) 示例： create or replace package pak_test as

19、procedure removestudent(p_stuid in students.id%type); type t_stuidtable is table of students.id%type index by binary_integer; end pak_test;3. 包主體答：1) 包主體是可選的，如包頭中沒有任何函數(shù)與過程，則包主體可以不需要。 2) 包主體與包頭存放在不同的數(shù)據(jù)字典中。 3) 如包頭編譯不成功，包主體無法正確編譯。 4) 包主體包含了所有在包頭中聲明的所有過程與函數(shù)的代碼。 5) 示例： create or replace package body pak_

20、test as procedure removestudent(p_stuid in students.id%type) is begin . end removestudent; type t_stuidtable is table of students.id%type index by binary_integer; end pak_test;4. 包的作用域答：1) 在包外調(diào)用包中過程(需加包名)：pak_test.addstudent(100010, cs, 101); 2) 在包主體中可以直接使用包頭中聲明的對(duì)象和過程(不需加包名);5. 包中子程序的重載答：1) 同一個(gè)包中的過程

21、與函數(shù)都可以重載; 2) 相同的過程或函數(shù)名字，但參數(shù)不同;6. 包的初始化答：1) 包存放在數(shù)據(jù)庫(kù)中; 2) 在第一次被調(diào)用的時(shí)候，包從數(shù)據(jù)庫(kù)中調(diào)入內(nèi)存并被初始化; 3) 包中定義的所有變量都被分配內(nèi)存; 4) 每個(gè)會(huì)話都將擁有自己的包內(nèi)變量的副本。第九章1. 觸發(fā)器答：1) 觸發(fā)器與過程/函數(shù)的相同點(diǎn) a. 都是帶有名字的執(zhí)行塊; b. 都有聲明、執(zhí)行體和異常部分; 2) 觸發(fā)器與過程/函數(shù)的不同點(diǎn) a. 觸發(fā)器必須存儲(chǔ)在數(shù)據(jù)庫(kù)中; b. 觸發(fā)器自動(dòng)執(zhí)行;2. 創(chuàng)建觸發(fā)器答：1) 語(yǔ)法： create or replace trigger trigger_name before|after

22、 triggering_event on table_reference for each row when trigger_condition trigger_body; 2) 范例： create or replace trigger updatemajorstats after insert or delete or update on students declare cursor c_statistics is select * from students group by major; begin . end up;3. 觸發(fā)器答：1) 三個(gè)語(yǔ)句(insert/update/del

23、ete); 2) 二種類型(之前/之后); 3) 二種級(jí)別(row-level/statement-level); 所以一共有 3 x 2 x 2 = 124. 觸發(fā)器的限制答：1) 不應(yīng)該使用事務(wù)控制語(yǔ)句; 2) 不能聲明任何long或long raw變量; 3) 可以訪問的表有限。5. 觸發(fā)器的主體可以訪問的表答：1) 不可以讀取或修改任何變化表(被dml語(yǔ)句正在修改的表); 2) 不可以讀取或修改限制表(帶有約束的表)的主鍵、唯一值、外鍵列。(4)java開發(fā)中使用oracle的ora-01000很多朋友在java開發(fā)中，使用oracle數(shù)據(jù)庫(kù)的時(shí)候，經(jīng)常會(huì)碰到有ora-01000: m

24、aximum open cursors exceeded.的錯(cuò)誤。實(shí)際上，這個(gè)錯(cuò)誤的原因，主要還是代碼問題引起的。 ora-01000: maximum open cursors exceeded. 表示已經(jīng)達(dá)到一個(gè)進(jìn)程打開的最大游標(biāo)數(shù)。 這 樣的錯(cuò)誤很容易出現(xiàn)在java代碼中的主要原因是：java代碼在執(zhí)行conn.createstatement()和 conn.preparestatement()的時(shí)候，實(shí)際上都是相當(dāng)與在數(shù)據(jù)庫(kù)中打開了一個(gè)cursor。尤其是，如果你的 createstatement和preparestatement是在一個(gè)循環(huán)里面的話，就會(huì)非常容易出現(xiàn)這個(gè)問題。因?yàn)橛螛?biāo)

25、一直在不停的打開，而且沒 有關(guān)閉。一般來說，我們?cè)趯慾ava代碼的時(shí)候，createstatement和preparestatement都應(yīng)該要放在循環(huán)外面，而且 使用了這些statment后，及時(shí)關(guān)閉。最好是在執(zhí)行了一次executequery、executeupdate等之后，如果不需要使用結(jié)果集 （resultset）的數(shù)據(jù)，就馬上將statment關(guān)閉。對(duì)于出現(xiàn)ora-01000錯(cuò)誤這種情況，單純的加大open_cursors并不是好辦法，那只是治標(biāo)不治本。實(shí)際上，代碼中的隱患并沒有解除。 而且，絕大部分情況下，open_cursors只需要設(shè)置一個(gè)比較小的值，就足夠使用了，除非有非常

26、特別的要求。(5)在store procedure中執(zhí)行 ddl語(yǔ)句一 是：execute immediate update |table_chan| set |column_changed| = |v_trans_name| where empid = |v_empid| ;二是：the dbms_sql package can be used to execute ddl statements directly from pl/sql. 這是一個(gè)創(chuàng)建一個(gè)表的過程的例子。該過程有兩個(gè)參數(shù)：表名和字段及其類型的列表。create or replace procedure ddlproc (ta

27、blename varchar2, cols varchar2) as cursor1 integer; begin cursor1 := dbms_sql.open_cursor; dbms_sql.parse(cursor1, create table | tablename | ( | cols | ), dbms_sql.v7); dbms_sql.close_cursor(cursor1); end; / 2 如何找數(shù)據(jù)庫(kù)表的主鍵字段的名稱?sqlselect * from user_constraintswhere constraint_type=p and table_name=

28、table_name;3 如何查詢數(shù)據(jù)庫(kù)有多少表?sqlselect * from all_tables;4 使用sql統(tǒng)配符通 配符 描述 示例 % 包含零個(gè)或更多字符的任意字符串。 where title like %computer% 將查找處于書名任意位置的包含單詞 computer 的所有書名。 _（下劃線） 任何單個(gè)字符。 where au_fname like _ean 將查找以 ean 結(jié)尾的所有 4 個(gè)字母的名字（dean、sean 等）。 指定范圍 (a-f) 或集合 (abcdef) 中的任何單個(gè)字符。 where au_lname like c-parsen 將查找以a

29、rsen 結(jié)尾且以介于 c 與 p 之間的任何單個(gè)字符開始的作者姓氏，例如，carsen、larsen、karsen 等。 不屬于指定范圍 (a-f) 或集合 (abcdef) 的任何單個(gè)字符。 where au_lname like del% 將查找以 de 開始且其后的字母不為 l 的所有作者的姓氏。5使普通用戶有查看v$session的權(quán)限grant select on sys.v_$open_cursor to sfism4; grant select on sys.v_$session to sfism4;常用函數(shù)distinct去掉重復(fù)的minus 相減在第一個(gè)表但不在第二個(gè)表se

30、lect * from football minus select * from softball;intersect 相交intersect 返回兩個(gè)表中共有的行。select * from footbal；union all 與union 一樣對(duì)表進(jìn)行了合并但是它不去掉重復(fù)的記錄。匯總函數(shù)countselect count(*) from test;sumsum 就如同它的本意一樣它返回某一列的所有數(shù)值的和。select sum(singles) total_singles from test;sum 只能處理數(shù)字如果它的處理目標(biāo)不是數(shù)字你將會(huì)收到如下信息輸入/輸出sqlselect su

31、m(name) from teamstats;errorora-01722 invalid numberno rows selected該錯(cuò)誤信息當(dāng)然的合理的因?yàn)閚ame 字段是無法進(jìn)行匯總的。avgavg 可以返回某一列的平均值。select avg(so) ave_strike_outs from teamstats；max如果你想知道某一列中的最大值請(qǐng)使用max。select max(hits) from teamstats;minmin 與max 類似它返回一列中的最小數(shù)值。variancevariance 方差不是標(biāo)準(zhǔn)中所定義的但它卻是統(tǒng)計(jì)領(lǐng)域中的一個(gè)至關(guān)重要的數(shù)值。select v

32、ariance(hits) from teamstats;stddev這是最后一個(gè)統(tǒng)計(jì)函數(shù)stddev 返回某一列數(shù)值的標(biāo)準(zhǔn)差。select stddev hits from teamstats；日期時(shí)間函數(shù)add_months add_months也可以工作在select 之外該函數(shù)的功能是將給定的日期增加一個(gè)月舉例來說由于一些特殊的原因上述的計(jì)劃需要推遲兩個(gè)月那么就用到了。last_daylast_day 可以返回指定月份的最后一天.months_between如果你想知道在給定的兩個(gè)日期中有多少個(gè)月可以使用months_between。select task, startdate, en

33、ddate ,months between(startdate,enddate) duration from project;返回結(jié)果有可能是負(fù)值.可以利用負(fù)值來判斷某一日期是否在另一個(gè)日期之前下例將會(huì)顯示所有在1995 年5 月19 日以前開始的比賽.select * from projectwhere months_between (19-may-95, startdate)0;new_time如果你想把時(shí)間調(diào)整到你所在的時(shí)區(qū)你可以使用new_time.sqlselect enddate edt, new_time(enddate, edt, pdt) from project;next_

34、daynext_day 將返回與指定日期在同一個(gè)星期或之后一個(gè)星期內(nèi)的你所要求的星期天數(shù)的確切日期如果你想知道你所指定的日期的星期五是幾號(hào)可以這樣做.sqlselect startdate, next_day(startdate, friday) from project;sysdatesysdate 將返回系統(tǒng)的日期和時(shí)間。select distinct sysdate from project；數(shù)學(xué)函數(shù)absabs 函數(shù)返回給定數(shù)字的絕對(duì)值ceil 和floorceil 返回與給定參數(shù)相等或比給定參數(shù)在的最小整數(shù).floor 則正好相反它返回與給定參數(shù)相等或比給定參數(shù)小的最大整數(shù).cos

35、cosh sin sinh tan tanhcos sin tan 函數(shù)可以返回給定參數(shù)的三角函數(shù)值默認(rèn)的參數(shù)認(rèn)定為弧度制.expexp 將會(huì)返回以給定的參數(shù)為指數(shù)以e 為底數(shù)的冪.ln and log這是兩個(gè)對(duì)數(shù)函數(shù)其中l(wèi)n 返回給定參數(shù)的自然對(duì)數(shù).mod知道在ansi 標(biāo)準(zhǔn)中規(guī)定取模運(yùn)算的符號(hào)為%在一些解釋器中被函數(shù)mod 所取代.power該函數(shù)可以返回某一個(gè)數(shù)對(duì)另一個(gè)數(shù)的冪在使用冪函數(shù)時(shí)第一個(gè)參數(shù)為底數(shù)第二個(gè)為指數(shù)。sign如果參數(shù)的值為負(fù)數(shù)那么sign 返回-1 如果參數(shù)的值為正數(shù)那么sign 返回1,如果參數(shù)為零那么sign 也返回零.sqrt該函數(shù)返回參數(shù)的平方根,由于負(fù)數(shù)是不能開平方的所以我們不能將該