人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動態(tài)

Oracle中PL/SQL的用法總結(jié)

發(fā)布日期:2022-07-15 19:23 | 文章來源:源碼中國

一、PLSQL基本結(jié)構(gòu)

1、基本數(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ù)類型變量的定義方法

變量名 類型標(biāo)識符[not null]:=值;

declare 
     age number(3):=26; --長度為3,初始值為26 
begin 
     commit; 
end;

其中,定義常量的語法格式:

常量名 constant 類型標(biāo)識符 [not null]:=值;

declare 
     pi constant number(9):=3.1415926;--為pi的數(shù)字型常量,長度為9,初始值為3.1415926 
begin 
     commit; 
end;

2、表達(dá)式

變量、常量經(jīng)常需要組成各種表達(dá)式來進(jìn)行運(yùn)算,下面介紹在PL/SQL中常見表達(dá)式的運(yùn)算規(guī)則。

1. 數(shù)值表達(dá)式

PL/SQL程序中的數(shù)值表達(dá)式是由數(shù)值型常數(shù)、變量、函數(shù)和算術(shù)運(yùn)算符組成的,
可以使用的算術(shù)運(yùn)算符包括+(加法)、-(減法)、*(乘法)、/(除法)和**(乘方)等。
命令窗口中執(zhí)行下列PL/SQL程序,該程序定義了名為result的整數(shù)型變量,計算的是10+3*4-20+5**2的值,理論結(jié)果應(yīng)該是27。

set serveroutput on 
 Declare 
    result integer; 
 begin 
    result:=10+3*4-20+5**2; 
    dbms_output.put_line('運(yùn)算結(jié)果是:'||to_char(result)); 
 end;

dbms_output.put_line函數(shù)輸出只能是字符串,因此利用to_char函數(shù)將數(shù)值型結(jié)果轉(zhuǎn)換為字符型。

2. 字符表達(dá)式

字符表達(dá)式由字符型常數(shù)、變量、函數(shù)和字符運(yùn)算符組成,唯一可以使用的字符運(yùn)算符就是連接運(yùn)算符“||”。

3. 關(guān)系表達(dá)式

關(guān)系表達(dá)式由字符表達(dá)式或數(shù)值表達(dá)式與關(guān)系運(yùn)算符組成,可以使用的關(guān)系運(yùn)算符包括以下9種。
< 小于
> 大于
= 等于(不是賦值運(yùn)算符:=)
like 類似于
in 在……之中
<= 小于等于
>= 大于等于
!= 不等于 或<>
between 在……之間
關(guān)系型表達(dá)式運(yùn)算符兩邊的表達(dá)式的數(shù)據(jù)類型必須一致。

4. 邏輯表達(dá)式

邏輯表達(dá)式由邏輯常數(shù)、變量、函數(shù)和邏輯運(yùn)算符組成,常見的邏輯運(yùn)算符包括以下3種。
NOT:邏輯非
OR:邏輯或
AND:邏輯與
運(yùn)算的優(yōu)先次序為NOT、AND和OR。

3、PLSQL函數(shù)

PL/SQL程序中提供了很多函數(shù)供擴(kuò)展功能,除了標(biāo)準(zhǔn)SQL語言的函數(shù)可以使用外,最常見的數(shù)據(jù)類型轉(zhuǎn)換函數(shù)有以下3個。
To_char:將其他類型數(shù)據(jù)轉(zhuǎn)換為字符型。
To_date:將其他類型數(shù)據(jù)轉(zhuǎn)換為日期型。
To_number:將其他類型數(shù)據(jù)轉(zhuǎn)換為數(shù)值型。
繼續(xù)追加中..

4、系統(tǒng)輸出打印

利用pl/sql在數(shù)據(jù)庫服務(wù)器端打印一句話:
set serveroutput on--設(shè)置數(shù)據(jù)庫輸出,默認(rèn)為關(guān)閉,每次重新打開窗口需要重新設(shè)置。

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('what''s'); 
 end;

5、PL/SQL中的變量聲明

所有變量必須在declare中聲明,程序中不允許聲明。
沒有初始化的變量默認(rèn)值為null,屏幕上null是看不見的,命名習(xí)慣:PL/SQL中變量一般以v_開頭
(等同于存儲過程中as和begin區(qū)域的變量定義習(xí)慣)。
注意number也能存小數(shù),最長38位,所以以后建議整數(shù)都用binary_integer存。
long是字符類型,boolean類型不能打印。
標(biāo)準(zhǔn)變量類型:數(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提示“調(diào)用'PUT_LINE' 時參數(shù)個數(shù)或類型錯誤” 
end;

備注:
關(guān)于聲明number(4,3)中括號中的兩個數(shù)字的意義,前面的數(shù)字叫精度,后面的叫刻度。
刻度:
當(dāng)刻度為正數(shù)的時候,表示四舍五入到小數(shù)點后面的位數(shù)
當(dāng)刻度為負(fù)數(shù)的時候,表示四舍五入到小數(shù)點前面的位數(shù)
精度:
從數(shù)字的最前面不為零開始到刻度精確到的位置
v_Number number(4,3):=123.12312
1、按刻度進(jìn)行四舍五入得到123.123
2、確定刻度精確到的位置123123處,精度為6位(.符號不算)
2、根據(jù)精度進(jìn)行判斷6位(>4)精度上限值 --報錯不能存儲
number(3,-3):=44445
1、根據(jù)刻度-3進(jìn)行四舍五入得到44000
2、小數(shù)點向前移動3位44.此位置為刻度精確到的位置
3、根據(jù)精度進(jìn)行判斷2位(<3)精度上限值 --不報錯可存儲結(jié)果為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復(fù)合類型

1、記錄類型record

record類型最常用,聲明的時候可以加not null,但必須給初始值,如果record類型一致可以相互賦值,如果類型不同,里面的字段恰好相同,不能互相賦值。引用記錄型變量的方法是“記錄變量名.基本類型變量名”。

declare 
    type t_first is record( 
         idnumber(3), 
         namevarchar2(20) 
    ); 
    v_first t_first; 
begin 
      v_first.id:=1; 
      v_first.name:='cheng'; 
      dbms_output.put_line(v_first.id); 
      dbms_output.put_line(v_first.name); 
 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_first.name:='susu'; 
    
    v_second:=v_first;--相互賦值 
    
    v_first.id:=2; 
    v_first.name:='kettas'; 
    dbms_output.put_line(v_first.id); 
    dbms_output.put_line(v_first.name); 
    dbms_output.put_line(v_second.id); 
    dbms_output.put_line(v_second.name); 
 end;

2、表類型變量table

語法如下:
type 表類型 is table of 類型 index by binary_integer;
表變量名 表類型;
類型可以是前面的類型定義,index by binary_integer子句代表以符號整數(shù)為索引,這樣訪問表類型變量中的數(shù)據(jù)方法就是“表變量名(索引符號整數(shù))”。table類型,相當(dāng)于Java中的Map容器,就是一個可變長的數(shù)組,key(符號整數(shù)索引)必須是整數(shù),可以是負(fù)數(shù),value(類型)可以是標(biāo)量,也可以是record類型??梢圆话错樞蛸x值,但必須先賦值后使用。

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,namevarchar2(20)); 
     type t_tb is table of t_rd index bybinary_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的多維表類型,相當(dāng)于多維數(shù)組,table1是多維表類型變量,將數(shù)據(jù)表tempuser.testtable中recordnumber為60的記錄提取出來
存放在table1中并顯示。

declare 
   type tabletype1 is table of testtable%rowtypeindex by binary_integer; 
   table1 tabletype1; 
 begin 
    select * into table1(60) fromtempuser.testtable where recordnumber=60; 
   dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate); 
 end;

備注:在定義好的表類型變量里,可以使用count、delete、first、last、next、exists和prior等屬性進(jìn)行操作,使用方法為“表變量名.屬性”,返回的是數(shù)字。

set serveroutput on 
declare 
     type tabletype1 is table ofvarchar2(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定義方法。這樣當(dāng)數(shù)據(jù)表的字段類型修改后,PL/SQL程序中相應(yīng)變量的類型也自動修改。

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 student.name%type; 
 begin 
    select name into v_name2 from studentwhere rownum=1; 
    dbms_output.put_line(v_name2); 
 end; 
--查找多個字段的變量 
declare 
     v_id student.id%type; 
     v_name student.name%type; 
     v_age student.age%type; 
 begin 
   select id,name,age into v_id,v_name,v_age fromstudent 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 whererownum=1; 
    dbms_output.put_line(v_student.id||' '||v_student.name||'  '||v_student.age); 
 end; 
--也可以按字段查找,但是字段順序必須一樣,不推薦這樣做 
declare 
    v_student student%rowtype; 
 begin 
  select id,name,age into v_student from student whererownum=1; 
  dbms_output.put_line(v_student.id||' '||v_student.name||'  '||v_student.age); 
 end; 
 declare 
    v_student student%rowtype; 
 begin 
  select id,name,age intov_student.id,v_student.name,v_student.age from student where id=1; 
  --select * intov_student.id,v_student.name,v_student.age from student where id=1; 
  dbms_output.put_line(); 
 end;

備注:insert,update,delete,select都可以,createtable,droptable不行。DPL,DML,和流程控制語句可以在pl/sql里用,但DDL語句不行。

declare 
    v_name student.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 student.name%type:='hexian'; 
 begin 
    update student set name=v_name where id=1; 
 end; 
 begin 
    update student set name='qinaide' whereid=2; 
 end;

五、PLSQL變量的可見空間

變量的作用域和可見性,變量的作用域為變量申明開始到當(dāng)前語句塊結(jié)束。

當(dāng)外部過程和內(nèi)嵌過程定義了相同名字的變量的時候,在內(nèi)嵌過程中如果直接寫這個變量名是沒有辦法訪問外部過程的變量的,可以通過給外部過程定義一個名字<<outername>>,通過outername變量名來訪問外部過程的變量(待測試..)。

declare 
         v_i1binary_integer:=1; 
begin 
      declare 
         v_i2binary_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會提示“必須說明標(biāo)識符'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)

注意推出exit是推出循環(huán),而不是推出整個代碼塊

declare 
    v_i binary_integer:=0; 
 begin 
  loop 
      if v_i>10 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_i>10; 
      v_i :=v_i+1; 
      dbms_output.put_line('hehe'); 
  end loop; 
      dbms_output.put_line('over'); 
 end;

while循環(huán)

declare 
    v_i binary_integer:=0; 
 begin 
    while v_i<10 loop 
         dbms_output.put_line('hello'||v_i ); 
         v_i:=v_i+1; 
    end loop; 
    dbms_output.put_line('over'); 
 end;

for循環(huán)

注意不需要聲明變量

begin 
  for v_i in 0..10 loop 
     dbms_output.put_line('hello'||v_i); 
  end loop; 
      dbms_output.put_line('over'); 
end;

七、PLSQL異常處理

1、聲明異常

異常名 EXCEPTION;

2、拋出異常

RAISE 異常名

3、處理異常

拋出異常后的邏輯代碼不會被繼續(xù)執(zhí)行
異常的定義使用

begin 
       dbms_output.put_line(1/0); 
 exception 
         when othersthen 
            dbms_output.put_line('error'); 
 end; 
 declare 
        e_myException exception; 
 begin 
        dbms_output.put_line('hello'); 
         raisee_myException; --raise拋出異常,用此關(guān)鍵字,拋出后轉(zhuǎn)到自定義的e_myException ,執(zhí)行其里面的putline函數(shù)后,再跳到end處,結(jié)束PL/SQL塊,raise接下面的2句不會繼續(xù)執(zhí)行。 
        dbms_output.put_line('world'); 
        dbms_output.put_line(1/0); 
 exception 
         whene_myException then 
            dbms_output.put_line(sqlcode); --當(dāng)前會話執(zhí)行狀態(tài),錯誤編碼 
            dbms_output.put_line(sqlerrm); --當(dāng)前錯誤信息 
            dbms_output.put_line('my error'); 
         when othersthen 
            dbms_output.put_line('error'); 
 end;

八、PLSQL游標(biāo)和goto語句

備注:下面提到的游標(biāo)為靜態(tài)cursor,包括顯示和隱式。
游標(biāo),從declare、open、fetch、close是一個完整的生命旅程。當(dāng)然了一個這樣的游標(biāo)是可以被多次open進(jìn)行使用的,顯式cursor是靜態(tài)cursor,她的作用域是全局的,但也必須明白,靜態(tài)cursor也只有pl/sql代碼才可以使用它。靜態(tài)游標(biāo)變量是在定義時就必須指定SQL語句。
cursor 游標(biāo)(結(jié)果集)用于提取多行數(shù)據(jù),定義后不會有數(shù)據(jù),使用后才有。一旦游標(biāo)被打開,就無法再次打開(可以先關(guān)閉,再打開)。

declare 
       cursor c_studentis  select * from book; 
 begin 
       open c_student; 
       close c_student; 
 end;

第二種游標(biāo)的定義方式,用變量控制結(jié)果集的數(shù)量。

declare 
       v_id binary_integer; 
       cursor c_student isselect * from book where id>v_id; 
 begin 
       v_id:=10; 
       open c_student; 
       close c_student; 
 end;

第三種游標(biāo)的定義方式,帶參數(shù)的游標(biāo),用的最多。

declare 
       cursor c_student(v_idbinary_integer) is select * from book where id>v_id; 
 begin 
       open c_student(10); 
       close c_student; 
 end;

游標(biāo)的使用,一定別忘了關(guān)游標(biāo)。

declare 
       v_student book%rowtype; 
       cursor c_student(v_idbinary_integer) is select * from book where id>v_id; 
 begin 
       open c_student(10); 
       fetch c_student intov_student; 
       close c_student; 
       dbms_output.put_line(v_student.name); 
 end;

如何遍歷游標(biāo)fetch

游標(biāo)的屬性 %found,%notfound,%isopen,%rowcount。

  • %found:若前面的fetch語句返回一行數(shù)據(jù),則%found返回true,如果對未打開的游標(biāo)使用則報ORA-1001異常。
  • %notfound,與%found行為相反。
  • %isopen,判斷游標(biāo)是否打開。
  • %rowcount:當(dāng)前游標(biāo)的指針位移量,到目前位置游標(biāo)所檢索的數(shù)據(jù)行的個數(shù),若未打開就引用,返回ORA-1001。

注:
no_data_found和%notfound的用法是有區(qū)別的,小結(jié)如下
1)SELECT. . . INTO 語句觸發(fā) no_data_found;
2)當(dāng)一個顯式光標(biāo)(靜態(tài)和動態(tài))的 where 子句未找到時觸發(fā) %notfound;
3)當(dāng)UPDATE或DELETE語句的where 子句未找到時觸發(fā) sql%notfound;
4)在光標(biāo)的提取(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' wherebookname = i_name; 
     delete from book where bookname =i_name; 
     if sql%notfound then 
        raisee_myException; 
     end if; 
     /* 
 if sql%rowcount = 0 then--寫法2 
        raisee_myException; 
     end if; 
 */ 
     o_result_msg := 'success'; 
  exception 
     when e_myException then 
         rollback; 
         o_result_msg := 'update or delete dail'; 
  end; 
--select 
 create or replace procedure say_hello( 
 i_name in varchar2, 
 o_result_msg out varchar2 
 ) 
  as 
  v_price varchar2(100); 
  e_myException exception; 
 begin 
     select price into v_price from bookwhere bookname = i_name; 
     o_result_msg := 'success'; 
  exception 
     when no_data_found then 
         rollback; 
         o_result_msg := 'select into dail'; 
  end;

loop方式遍歷游標(biāo)

declare 
       v_bookname varchar2(100); 
       cursor c_book(i_idnumber) is select bookname from book where id = i_id; 
 begin 
     Open  c_book(i_id); 
     Loop 
         Fetch c_bookinto v_bookname; 
         exit whenc_student%notfound; 
          update book set price = '33' where bookname = v_bookname; 
     End Loop; 
     Close c_book; 
 end;

declare 
       v_bookname varchar2(100); 
       cursor c_book(i_idnumber) is select bookname from book where id = i_id; 
 begin 
     Open  c_book(i_id); 
       Fetch c_book intov_bookname; 
       While c_book%Found 
       Loop 
          update book set price = '33' where bookname = v_bookname; 
       Fetch  c_book intov_bookname; 
       End Loop; 
     Close c_book; 
 end;

while循環(huán)遍歷游標(biāo),注意,第一次游標(biāo)剛打開就fetch,%found為null,進(jìn)不去循環(huán)
解決方法:while nvl(c_student%found,true) loop

declare 
      v_bookname  varchar2(100); 
      cursor c_book(i_id number) isselect bookname from book where id = i_id; 
 begin 
      Open  c_book(i_id); 
      while nvl(c_book%found,true)--或這種寫法:while c_book%found is null or c_book%foundloop    
          Fetchc_book into v_bookname; 
          updatebook set price = '33' where bookname = v_bookname; 
      End Loop; 
      Close c_book; 
 end;

for循環(huán)遍歷,最簡單,用的最多,不需要 聲明v_student,Open和Close游標(biāo)和fetch操作(不用打開游標(biāo)和關(guān)閉游標(biāo),實現(xiàn)遍歷游標(biāo)最高效方式)

declare 
      cursor c_book(i_id number) isselect bookname from book where id = i_id; 
begin 
      for cur in c_book(i_id) --直接將入?yún)_id傳入cursor即可 
      loop 
          updatebook set price = '53' where bookname = cur.bookname; 
      end loop; 
 end;

goto例子,一般不推薦使用goto,會使程序結(jié)構(gòu)變亂

declare 
      i number:=0; 
 begin 
      if i=0 then 
          gotohello; 
      end if; 
      <<hello>> 
      begin 
         dbms_output.put_line('hello'); 
          gotoover; 
      end; 
      <<world>> 
      begin 
         dbms_output.put_line('world'); 
          gotoover; 
      end; 
      <<over>> 
         dbms_output.put_line('over'); 
  end;

九、Oracle存儲過程

在談存儲過程書寫中的一些規(guī)則時,先看一下執(zhí)行它的規(guī)則,在命令窗口執(zhí)行存儲過程sp_get_product_prompt

set serveroutput on 
  var ret1 varchar2(200); 
  var ret2 varchar2(200); 
  exec sp_get_product_prompt(83,:ret1,:ret2); --或execute 
  print ret1; 
  print ret2; 
  或 
  set serveroutput on 
  declare 
     ret1 varchar2(200); 
     ret2 varchar2(200); 
  begin 
     sp_get_product_prompt(83,ret1,ret2); 
     dbms_output.put_line(ret1); 
     dbms_output.put_line(ret2); 
  end;

存儲過程入?yún)?,不論類型,缺省情況下值都為null,入?yún)⒑统鰠⒉荒苡虚L度,其中關(guān)鍵字as可以替換成is,存儲過程中變量聲明在as和begin之間,同時,存儲過程中可以再調(diào)用其它的存儲過程,如果要保證存儲過程之間的事務(wù)處理不受影響,可以定義為自治事務(wù)。

create or replace procedure say_hello( 
    v_name in varchar2, 
    v_flag number, 
    o_ret out number 
  ) 
  as 
  begin 
    if v_name is null and v_flag is null then --v_name和v_flag都等于null 
       o_ret := 10; 
    else 
        o_ret := 100; 
    end if; 
  end;

對于入?yún)閚ull情況下給予缺省值

create or replace procedure say_hello( 
    i_name in varchar2, 
    i_flag number, 
    o_ret out number 
  ) 
  as 
    v_name  varchar2(100); 
  begin 
    if i_name is null then 
       v_name := '0'; 
    else 
       v_name := i_name; 
    end if; 
    insert into phone(..,wname..,)values(..,v_name,..);  
  
  end;

或直接在insert語句中調(diào)用nvl函數(shù)賦缺省值

insert intophone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果將' '寫成'',則insert進(jìn)來的v_name值還是為''等價于null值

帶一個參數(shù)的存儲過程
輸入?yún)?shù)in,輸入?yún)?shù)不能進(jìn)行:=賦值,但可以將它賦給as后面定義的變量;
輸入?yún)?shù)in,可以作為變量進(jìn)行條件判斷;
默認(rèn)不寫就是in;
存儲過程沒有重載,這個有參的say_hello會替代已經(jīng)存在的無參say_hello。

create or replace procedure say_hello(v_name in varchar2) 
  as 
  begin 
     --v_name:='a'; --存儲過程入?yún)_name不能做為賦值目標(biāo) 
    dbms_output.put_line('hello '||v_name); 
  end;

存儲過程輸入?yún)?shù)作為變量進(jìn)行條件判斷

create or replace procedure say_hello( 
     i_opFlag in number 
  ) 
  as 
     v_name varchar2(100); 
  begin 
     if i_opFlag = 1 then 
v_name :='0'; 
     else 
v_name :='haha'; 
     end if; 
     dbms_output.put_line('hello'||v_name); 
  end;

利用存儲過程中定義的變量對入?yún)⒌目罩堤幚恚?/h3>
create or replace procedure say_hello( 
     i_name in varchar2 
  ) 
  as 
     v_name varchar2(100); 
  begin 
     if i_name is null then 
v_name :='0'; 
     else 
v_name :=i_name;--將入賦值給定義變量 
     end if; 
     dbms_output.put_line('hello'||v_name); 
  end;

多個參數(shù)的存儲過程

create or replace procedure say_hello( 
     v_first_name in varchar2, 
     v_last_name in varchar2) 
  as 
  begin 
     dbms_output.put_line('hello'||v_first_name||'.'||v_last_name); 
  end;

out輸出參數(shù)

用于利用存儲過程給一個或多個變量賦值,類似于返回值

create or replaceprocedure say_hello( 
     v_name in varchar2, 
     v_content out varchar2 
  ) 
  begin 
     v_content:='hello'||v_name; 
  end;

調(diào)用:

declare 
     v_con varchar2(200); 
     v_in varchar2(20):='wang'; 
  begin 
     say_hello(v_in,v_con); 
     dbms_output.put_line(v_con); 
  end;

in out參數(shù),既賦值又取值

create or replace procedure say_hello(v_name in outvarchar2) 
  as 
  begin 
     v_name:='hi '||v_name; 
  end;

調(diào)用:

declare 
     v_inout varchar2(20):='wangsu'; 
  begin 
     say_hello(v_inout); 
     dbms_output.put_line(v_inout); 
  end;

對存儲過程入?yún)①x缺省值

create or replace procedure say_hello( 
     v_name varchar2 default 'susu', 
     v_content varchar2 default 'hello' 
  ) 
  as 
  begin 
     dbms_output.put_line(v_name||''||v_content); 
  end;

調(diào)用:(用指明形參名的方式調(diào)用更好)

begin 
     say_hello(); 
  end;

begin 
     say_hello('cheng'); 
  end;

begin 
  say_hello(v_name=>'cheng'); 
 end;

十、PLSQL中的function

FUNCTION和PROCEDURE的區(qū)別

1、函數(shù)有返回值,過程沒有
2、函數(shù)調(diào)用在一個表達(dá)式中,過程則是作為pl/sql程序的一個語句
過程和函數(shù)都以編譯后的形式存放在數(shù)據(jù)庫中,函數(shù)可以沒有參數(shù)也可以有多個參數(shù)并有一個返回值。

過程有零個或多個參數(shù),沒有返回值。

函數(shù)和過程都可以通過參數(shù)列表接收或返回零個或多個值,函數(shù)和過程的主要區(qū)別不在于返回值,而在于他們的調(diào)用方式,過程是作為一個獨立執(zhí)行語句調(diào)用的,函數(shù)以合法的表達(dá)式的方式調(diào)用。

create or replace function func(v_name in varchar2) 
return varchar2 
is 
begin 
    return(v_name||' hello'); 
 end;

調(diào)用:

declare 
    v_name varchar2(20); 
begin 
    v_name:=func('cheng'); 
    dbms_output.put_line(v_name); 
 end;

帶out參數(shù)的函數(shù)

create or replace function func( 
    v_name in varchar2, 
    v_content out varchar2 
) 
return varchar2 
is 
begin 
    v_content:=v_name||' hello'; 
    return v_content; 
 end;

調(diào)用:

declare 
    v_name varchar2(20); 
    v_name1 varchar2(20); 
begin 
    v_name1:=func('susu',v_name);--返回v_name值 
    dbms_output.put_line(v_name1);--打印func結(jié)果 
    dbms_output.put_line(v_name);--打印v_name結(jié)果 
end;

帶in out 參數(shù)的函數(shù)

create or replace function func( 
    v_name in out varchar2) 
return varchar2 
is 
begin 
    v_name:=v_name||' hello'; 
    return 'cheng'; 
 end;

調(diào)用:

declare 
    v_inout varchar2(20):='world'; 
    v_ret varchar2(20); 
begin 
    v_ret:=func(v_inout);--返回調(diào)用v_inout值(作為出參) 
    dbms_output.put_line(v_ret);--打印func結(jié)果    
    dbms_output.put_line(v_inout);--返回v_name結(jié)果 
end;

到此這篇關(guān)于Oracle中PL/SQL用法的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助,也希望大家多多支持本站。

香港服務(wù)器租用

版權(quán)聲明:本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學(xué)習(xí)參考,不代表本站立場,如有內(nèi)容涉嫌侵權(quán),請聯(lián)系alex-e#qq.com處理。

實時開通

自選配置、實時開通

免備案

全球線路精選!

全天候客戶服務(wù)

7x24全年不間斷在線

專屬顧問服務(wù)

1對1客戶咨詢顧問

在線
客服

在線客服:7*24小時在線

客服
熱線

400-630-3752
7*24小時客服服務(wù)熱線

關(guān)注
微信

關(guān)注官方微信
頂部