ORACLEPLSQL存儲過程教程

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)符號 在需要下列之一時：代碼如下： if (sum0) then begin insert into emesp.tp_sn_production_log values (r_serial_number, , id_temp); exit; end; 一直報sum0 這是個很郁悶的

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

3、2) oracle客戶端工具訪問oracle服務器的操作語言; 3) oracle對sql的擴充;4. pl/sql的優(yōu)缺點答：優(yōu)點： 1) 結(jié)構(gòu)化模塊化編程，不是面向?qū)ο? 2) 良好的可移植性(不管oracle運行在何種操作系統(tǒng)); 3) 良好的可維護性(編譯通過后存儲在數(shù)據(jù)庫里); 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)境答：可以運用任何純文本的編輯器編輯，例如：vi ；toad很好用3. pl/s

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

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

6、pe not null:=expr2, /*如果一個字段限定not null，那么它必須擁有一個初始值*/ . /*所有沒有初始化的字段都會初始為nullfieldn type not null:=exprn);4. %rowtype答：返回一個基于數(shù)據(jù)庫定義的類型 declare v_sturec student%rowtype; /*student為表的名字*/ 注：與3中定一個record相比，一步就完成，而3中定義分二步：a. 所有的成員變量都要申明; b. 實例化變量;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í)行塊的變量對外層不可見; 3) 里層執(zhí)行塊對外層執(zhí)行塊變量的修改會影響外層塊變量的值;第四章1.

8、 條件語句答：if boolean_expression1 then . elsif boolean_expression2 then /*注意是elsif，而不是elseif*/ . /*else語句不是必須的，但end if;是必須的*/ else . end if;2. 循環(huán)語句答：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é)束邊界到起始邊界，遞減步長為一; b. low_blound 起始邊界; high_bound 結(jié)束邊界;3. goto語句答：goto label_name; 1) 只能由內(nèi)部塊跳往外部塊; 2) 設置標簽： 3) 示例： loop . if d%rowcount = 50 then goto l_close; end if; . end loop; ; .4. null語句答：在語句塊中加空語句，用于補充語句的完整性。示例： if boolean_

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

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

12、答：1) %found: 是否有值; 2) %notfound: 是否沒有值; 3) %isopen: 是否是打開狀態(tài); 4) %rowcount: cursor當前的記錄號;3. 游標的fetch循環(huán)答：1) loop fetch cursor into . exit when cursor%notfound; /*當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ù)庫編程(下)1. 存儲過程(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ù)不能被賦值(只能位于等號右邊); 2) out:表示該參數(shù)只能被賦值(只能位于等號左邊); 3) in out: 表示該類型既能被賦值也能傳值;2. 存儲過程例子答：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; 注：此時v_var1等于74. 指定實參的模式答：1) 位置標示法：調(diào)用時添入所有參數(shù)，實參與形參按順序一一對應; 2) 名字標示法：調(diào)用時給出形參名字，并

16、給出實參 modetest(p_inparm=12, p_outparm=v_var1, p_inout=10); 注：a. 兩種方法可以混用; b. 混用時第一個參數(shù)必須通過位置來指定。5. 函數(shù)(function)與過程(procedure)的區(qū)別答：1) 過程調(diào)用本身是一個pl/sql語句(可以在命令行中通過exec語句直接調(diào)用); 2) 函數(shù)調(diào)用是表達式的一部分;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) 沒有返回語句的函數(shù)將是一個錯誤;7. 刪除過程與函數(shù)答：drop procedure proc_name; drop function func_name;第八章1. 包答：1) 包是可以將相關對象存儲在一起的pl/sql的結(jié)構(gòu); 2) 包只能存儲在數(shù)據(jù)庫中，不能是本地的; 3) 包是一個帶有名字的聲明; 4) 相當于一個pl/sql塊的聲明部分; 5) 在塊的聲明部分出現(xiàn)的任何東西都能出現(xiàn)在包中; 6) 包中可以包含過程、函數(shù)、游標與變量; 7) 可以從其他pl/sql塊中引用包，包提供了可用于pl/sql的全局變量。 8) 包有包頭和

18、包主體，如包頭中沒有任何函數(shù)與過程，則包主體可以不需要。2. 包頭答：1) 包頭包含了有關包的內(nèi)容的信息，包頭不含任何過程的代碼。 2) 語法： 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) 在包主體中可以直接使用包頭中聲明的對象和過程(不需加包名);5. 包中子程序的重載答：1) 同一個包中的過程

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

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

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

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

26、特別的要求。(5)在store procedure中執(zhí)行 ddl語句一 是：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. 這是一個創(chuàng)建一個表的過程的例子。該過程有兩個參數(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ù)庫表的主鍵字段的名稱?sqlselect * from user_constraintswhere constraint_type=p and table_name=

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

29、rsen 結(jié)尾且以介于 c 與 p 之間的任何單個字符開始的作者姓氏，例如，carsen、larsen、karsen 等。 不屬于指定范圍 (a-f) 或集合 (abcdef) 的任何單個字符。 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去掉重復的minus 相減在第一個表但不在第二個表se

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

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

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

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

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

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