PLSQL開發(fā)筆記和小結.docx

* PLSQL基本結構 * 基本數(shù)據(jù)類型變量基本數(shù)據(jù)類型變量基本數(shù)據(jù)類型變量基本數(shù)據(jù)類型變量 1. 基本數(shù)據(jù)類型 Number 數(shù)字型 Int 整數(shù)型 Pls_integer 整數(shù)型，產(chǎn)生溢出時出現(xiàn)錯誤 Binary_integer 整數(shù)型，表示帶符號的整數(shù) Char 定長字符型，最大255個字符 Varchar2 變長字符型，最大2000個字符 Long 變長字符型，最長2GB Date 日期型 Boolean 布爾型（TRUE、FALSE、NULL三者取一） 在PL/SQL中使用的數(shù)據(jù)類型和Oracle數(shù)據(jù)庫中使用的數(shù)據(jù)類型，有的含義是完全一致的，有的是有不同的含義的。 2. 基本數(shù)據(jù)類型變量的定義方法 變量名 類型標識符 not null:=值; declare age number(3):=26; -長度為3，初始值為26 begin commit; end; 其中，定義常量的語法格式： 常量名 constant 類型標識符 not null:=值; declare pi constant number(9):=3.1415926;-為pi的數(shù)字型常量，長度為9，初始值為3.1415926 begin commit; end; 表達式表達式表達式表達式 變量、常量經(jīng)常需要組成各種表達式來進行運算，下面介紹在PL/SQL中常見表達式的運算規(guī)則。 1. 數(shù)值表達式 PL/SQL程序中的數(shù)值表達式是由數(shù)值型常數(shù)、變量、函數(shù)和算術運算符組成的，可以使用的算術運算符包括+（加法）、-（減法）、*（乘法）、/（除法 ）和*（乘方）等。 命令窗口中執(zhí)行下列PL/SQL程序，該程序定義了名為result的整數(shù)型變量，計算的是10+3*4-20+5*2的值，理論結果應該是27。 set serveroutput on Declare result integer; begin result:=10+3*4-20+5*2; dbms_output.put_line(運算結果是：|to_char(result); end; dbms_output.put_line函數(shù)輸出只能是字符串，因此利用to_char函數(shù)將數(shù)值型結果轉換為字符型。 2. 字符表達式 字符表達式由字符型常數(shù)、變量、函數(shù)和字符運算符組成，唯一可以使用的字符運算符就是連接運算符“|”。 3. 關系表達式 關系表達式由字符表達式或數(shù)值表達式與關系運算符組成，可以使用的關系運算符包括以下9種。 大于 = 等于（不是賦值運算符:=） like 類似于 in 在之中 = 大于等于 != 不等于 或 between 在之間 關系型表達式運算符兩邊的表達式的數(shù)據(jù)類型必須一致。 4. 邏輯表達式 邏輯表達式由邏輯常數(shù)、變量、函數(shù)和邏輯運算符組成，常見的邏輯運算符包括以下3種。 NOT：邏輯非 OR：邏輯或 AND：邏輯與 運算的優(yōu)先次序為NOT、AND和OR。 PLSQL函數(shù)函數(shù)函數(shù)函數(shù) PL/SQL程序中提供了很多函數(shù)供擴展功能，除了標準SQL語言的函數(shù)可以使用外，最常見的數(shù)據(jù)類型轉換函數(shù)有以下3個。 To_char：將其他類型數(shù)據(jù)轉換為字符型。 To_date：將其他類型數(shù)據(jù)轉換為日期型。 To_number：將其他類型數(shù)據(jù)轉換為數(shù)值型。 繼續(xù)追加中. 系統(tǒng)輸出打印系統(tǒng)輸出打印系統(tǒng)輸出打印系統(tǒng)輸出打印 利用pl/sql在數(shù)據(jù)庫服務器端打印一句話： set serveroutput on-設置數(shù)據(jù)庫輸出，默認為關閉，每次重新打開窗口需要重新設置。 BEGIN DBMS_OUTPUT.PUT_LINE(Hello PL/SQL); END; pl/sql程序中對大小寫不敏感程序中對大小寫不敏感程序中對大小寫不敏感程序中對大小寫不敏感（打印聲明的變量） set serveroutput on DECLARE v_char varchar2(20):=a; v_char1 varchar2(20):=b; BEGIN DBMS_OUTPUT.PUT_LINE(v_char); DBMS_OUTPUT.PUT_LINE(v_char1); END; pl語句塊是pl/sql里最小的編程塊，其中可以再嵌套begin end begin dbms_output.put_line(Hello World); dbms_output.put_line(2*3=|(2*3); dbms_output.put_line(whats); end; PL/SQL中的變量聲明中的變量聲明中的變量聲明中的變量聲明 所有變量必須在declare中聲明，程序中不允許聲明。 沒有初始化的變量默認值為null，屏幕上null是看不見的，命名習慣：PL/SQL中變量一般以v_開頭（等同于存儲過程中as和begin區(qū)域的變量定義習慣）。 注意number也能存小數(shù)，最長38位，所以以后建議整數(shù)都用binary_integer存。 long是字符類型，boolean類型不能打印。 標準變量類型：數(shù)字，字符，時間，布爾。 declare v_number1 number; v_number2 number(3,2) ; v_number3 binary_integer :=1; v_name varchar2(20) :=kettas; v_date date :=sysdate; v_long long :=ni hao; v_b boolean := true; begin if (v_number1 is null) then dbms_output.put_line( hello); end if; dbms_output.put_line(v_number1); dbms_output.put_line(v_number2); dbms_output.put_line(v_number3); dbms_output.put_line(v_name); dbms_output.put_line(v_date); dbms_output.put_line(v_long); -dbms_output.put_line(v_b); -執(zhí)行該句ORACLE提示“調用 PUT_LINE 時參數(shù)個數(shù)或類型錯誤” end; 備注備注備注備注： 關于聲明number(4,3)中括號中的兩個數(shù)字的意義，前面的數(shù)字叫精度，后面的叫刻度。 刻度： 當刻度為正數(shù)的時候，表示四舍五入到小數(shù)點后面的位數(shù) 當刻度為負數(shù)的時候，表示四舍五入到小數(shù)點前面的位數(shù) 精度： 從數(shù)字的最前面不為零開始到刻度精確到的位置 v_Number number(4,3):=123.12312 1、按刻度進行四舍五入得到123.123 2、確定刻度精確到的位置123123處，精度為6位（.符號不算） 2、根據(jù)精度進行判斷6位（4）精度上限值 -報錯不能存儲 number(3,-3):=44445 1、根據(jù)刻度3進行四舍五入得到44000 2、小數(shù)點向前移動3位44.此位置為刻度精確到的位置 3、根據(jù)精度進行判斷2位（3）精度上限值 -不報錯可存儲結果為44000 DECLARE v_Number number(4,3):=123.12312;-實際精度6位大于上限精度值4位，提示“ORA-06502: PL/SQL: 數(shù)字或值錯誤 : 數(shù)值精度太高” BEGIN DBMS_OUTPUT.PUT_LINE(v_Number); END ; DECLARE v_Number number(7,3):=4555; -實際精度7位等于上限精度值，可以存儲 BEGIN DBMS_OUTPUT.PUT_LINE(v_Number); END ; * 變量賦值方式 * oracle中變量賦值方式是值拷貝而非引用中變量賦值方式是值拷貝而非引用中變量賦值方式是值拷貝而非引用中變量賦值方式是值拷貝而非引用 declare v_number1 number:=100; v_number2 number; begin v_number2:=v_number1; v_number1:=200; dbms_output.put_line(v_number1); -200 dbms_output.put_line(v_number2); -100 end; * PLSQL復合類型 * 記錄類型記錄類型記錄類型記錄類型record record類型最常用，聲明的時候可以加not null，但必須給初始值，如果record類型一致可以相互賦值，如果類型不同，里面的字段恰好相同，不能互相賦值。引用記錄型變量的方法是“記錄變量名.基本類型變量名”。 declare type t_first is record( id number(3), name varchar2(20) ); v_first t_first; begin v_first.id:=1; v_:=cheng; dbms_output.put_line(v_first.id); dbms_output.put_line(v_); end; record類型變量間賦值 declare type t_first is record( id number, name varchar2(20) ); v_first t_first; v_second t_first; begin v_first.id:=1; v_:=susu; v_second:=v_first;-相互賦值 v_first.id:=2; v_:=kettas; dbms_output.put_line(v_first.id); dbms_output.put_line(v_); dbms_output.put_line(v_second.id); dbms_output.put_line(v_); end; 表類型變量表類型變量表類型變量表類型變量table 語法如下： type 表類型 is table of 類型 index by binary_integer; 表變量名 表類型; 類型可以是前面的類型定義，index by binary_integer子句代表以符號整數(shù)為索引，這樣訪問表類型變量中的數(shù)據(jù)方法就是“表變量名(索引符號整數(shù))”。table類型，相當于java中的Map容器，就是一個可變長的數(shù)組，key（符號整數(shù)索引）必須是整數(shù)，可以是負數(shù)，value（類型）可以是標量，也可以是record類型。可以不按順序賦值，但必須先賦值后使用。 1. 定義一維表類型變量 declare type t_tb is table of varchar2(20) index by binary_integer; v_tb t_tb; begin v_tb(100):=hello; v_tb(98):=world; dbms_output.put_line(v_tb(100); dbms_output.put_line(v_tb(98); end; 類型為record的表類型變量 declare type t_rd is record(id number,name varchar2(20); type t_tb is table of t_rd index by binary_integer; v_tb2 t_tb; begin v_tb2(100).id:=1; v_tb2(100).name:=hello; -dbms_output.put_line(v_tb2(100).id); -dbms_output.put_line(v_tb2(100).name); dbms_output.put_line(v_tb2(100).id| |v_tb2(100).name); end; 2. 定義多維表類型變量 該程序定義了名為tabletype1的多維表類型，相當于多維數(shù)組，table1是多維表類型變量，將數(shù)據(jù)表tempuser.testtable中recordnumber為60的記錄提取出來 存放在table1中并顯示。 declare type tabletype1 is table of testtable%rowtype index by binary_integer; table1 tabletype1; begin select * into table1(60) from tempuser.testtable where recordnumber=60; dbms_output.put_line(table1(60).recordnumber|table1(60).currentdate); end; 備注備注備注備注：在定義好的表類型變量里，可以使用count、delete、first、last、next、exists和prior等屬性進行操作，使用方法為“表變量名.屬性”，返回的是數(shù)字。 set serveroutput on declare type tabletype1 is table of varchar2(9) index by binary_integer; table1 tabletype1; begin table1(1):=成都市; table1(2):=北京市; table1(3):=青島市; dbms_output.put_line(總記錄數(shù)：|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(2); end; * %type和%rowtype * 使用%type定義變量，為了讓PL/SQL中變量的類型和數(shù)據(jù)表中的字段的數(shù)據(jù)類型一致，Oracle 9i提供了%type定義方法。這樣當數(shù)據(jù)表的字段類型修改后，PL/SQL程序中相應變量的類型也自動修改。 create table student( id number, name varchar2(20), age number(3,0) ); insert into student(id,name,age) values(1,susu,23); -查找一個字段的變量 declare v_name varchar2(20); v_name2 %type; begin select name into v_name2 from student where rownum=1; dbms_output.put_line(v_name2); end; -查找多個字段的變量 declare v_id student.id%type; v_name %type; v_age student.age%type; begin select id,name,age into v_id,v_name,v_age from student where rownum=1; dbms_output.put_line(v_id| |v_name| |v_age); end; -查找一個類型的變量，推薦用* declare v_student student%rowtype; begin select * into v_student from student where rownum=1; dbms_output.put_line(v_student.id| |v_| |v_student.age); end; -也可以按字段查找，但是字段順序必須一樣，不推薦這樣做 declare v_student student%rowtype; begin select id,name,age into v_student from student where rownum=1; dbms_output.put_line(v_student.id| |v_| |v_student.age); end; declare v_student student%rowtype; begin select id,name,age into v_student.id,v_,v_student.age from student where id=1; -select * into v_student.id,v_,v_student.age from student where id=1; dbms_output.put_line(); end; 備注備注備注備注：insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制語句可以在pl/sql里用，但DDL語句不行。 declare v_name %type:=wang; begin insert into student(id,name,age) values(2,v_name,26); end; begin insert into student(id,name,age) values(5,hehe,25); end; declare v_name %type:=hexian; begin update student set name=v_name where id=1; end; begin update student set name=qinaide where id=2; end; * PLSQL變量的可見空間 * 變量的作用域和可見性，變量的作用域為變量申明開始到當前語句塊結束。當外部過程和內嵌過程定義了相同名字的變量的時候，在內嵌過程中如果直接寫這個變量名是沒有辦法訪問外部過程的變量的，可以通過給外部過程定義一個名字,通過outername變量名來訪問外部過程的變量（待測試.）。 declare v_i1 binary_integer:=1; begin declare v_i2 binary_integer:=2; begin dbms_output.put_line(v_i1); dbms_output.put_line(v_i2); end; dbms_output.put_line(v_i1); -dbms_output.put_line(v_i2); 解開后執(zhí)行Oracle會提示“必須說明標識符 V_I2” end; * PLSQL流程控制 * if判斷判斷判斷判斷 declare v_b boolean:=true; begin if v_b then dbms_output.put_line(ok); end if; end; if else判斷判斷判斷判斷 declare v_b boolean:=true; begin if v_b then dbms_output.put_line(ok); else dbms_output.put_line(false); end if; end; if elsif else判斷判斷判斷判斷 declare v_name varchar2(20):=cheng; begin if v_name=0701 then dbms_output.put_line(0701); elsif v_name=cheng then dbms_output.put_line(cheng); else dbms_output.put_line(false); end if; end; loop循環(huán)循環(huán)循環(huán)循環(huán)，注意推出exit是推出循環(huán)，而不是推出整個代碼塊 declare v_i binary_integer:=0; begin loop if v_i10 then exit; end if; v_i:=v_i+1; dbms_output.put_line(hehe); end loop; dbms_output.put_line(over); end; loop簡化寫法簡化寫法簡化寫法簡化寫法 declare v_i binary_integer :=0; begin loop exit when v_i10; v_i :=v_i+1; dbms_output.put_line(hehe); end loop; dbms_output.put_line(over); end; while循環(huán)循環(huán)循環(huán)循環(huán) declare v_i binary_integer:=0; begin while v_iv_id; begin v_id:=10; open c_student; close c_student; end; 第三種游標的定義方式，帶參數(shù)的游標，用的最多。 declare cursor c_student(v_id binary_integer) is select * from book where idv_id; begin open c_student(10); close c_student; end; 游標的使用游標的使用游標的使用游標的使用，一定別忘了關游標一定別忘了關游標一定別忘了關游標一定別忘了關游標。 declare v_student book%rowtype; cursor c_student(v_id binary_integer) is select * from book where idv_id; begin open c_student(10); fetch c_student into v_student; close c_student; dbms_output.put_line(v_); end; 如何遍歷游標如何遍歷游標如何遍歷游標如何遍歷游標fetch 游標的屬性 %found，%notfound,%isopen,%rowcount。 %found:若前面的fetch語句返回一行數(shù)據(jù)，則%found返回true，如果對未打開的游標使用則報ORA-1001異常。 %notfound，與%found行為相反。 %isopen，判斷游標是否打開。 %rowcount:當前游標的指針位移量，到目前位置游標所檢索的數(shù)據(jù)行的個數(shù)，若未打開就引用，返回ORA-1001。 注注注注： no_data_found和%notfound的用法是有區(qū)別的，小結如下 1）SELECT . . . INTO 語句觸發(fā) no_data_found； 2）當一個顯式光標(靜態(tài)和動態(tài))的 where 子句未找到時觸發(fā) %notfound； 3）當UPDATE或DELETE 語句的where 子句未找到時觸發(fā) sql%notfound； 4）在光標的提取(Fetch)循環(huán)中要用 %notfound 或%found 來確定循環(huán)的退出條件，不要用no_data_found。 下面是幾個實例： create table BOOK ( ID VARCHAR2(10) not null, BOOKNAME VARCHAR2(10) not null, PRICE VARCHAR2(10) not null, CID VARCHAR2(10) not null ); -insert create or replace procedure say_hello( i_name in varchar2, o_result_msg out varchar2 ) as v_price varchar2(100); e_myException exception; begin insert into book(id,bookname,price) values (1,2,3); o_result_msg := success; exception when others then rollback; o_result_msg := substr(sqlerrm, 1, 200); end; -update or delete create or replace procedure say_hello( i_name in varchar2, o_result_msg out varchar2 ) as v_price varchar2(100); e_myException exception; begin update book set price = 55 where bookname = i_name; delete from book where book