程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> PL/SQL之--變量，plsql變量

PL/SQL之--變量，plsql變量

編輯：Oracle教程

PL/SQL之--變量，plsql變量

一、PL/SQL 簡介

　　PL/SQL也是一種程序語言，叫做過程化SQL語言（Procedural Language/SQL）。PL/SQL是oracle對sql語句的一種擴展，在普通SQL語句的使用上增加了編程語言的特點，所以PL/SQL就是把數據操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、循環等操作實現復雜的功能或者計算的程序語言，只能在oracle中運行。當然別的數據庫也有自己的"pl/sql"，這不是oracle特有的。如mysql也有，但是每種數據庫的都不一樣，功能也有些差別。oracle中的PL/SQL就比mysql中的強大許多。

　　PL/SQL 優點如下：

能夠使一組SQL語句的功能更具模塊化程序特點；
采用了過程性語言控制程序的結構；
可以對程序中的錯誤進行自動處理，使程序能夠在遇到錯誤的時候不會被中斷；
具有較好的可移植性，可以移植到另一個Oracle數據庫中；
集成在數據庫中，調用更快；
減少了網絡的交互，有助於提高程序性能

　　PL/SQL塊由四個基本部分組成：聲明、執行體開始、異常處理、執行體結束。四個部分的基本結構如下:

DECLARE -- 可選部分
-- 變量、常量、游標、用戶定義異常的聲明
BEGIN -- 必要部分
-- SQL語句和PL/SQL語句構成的執行程序 
EXCEPTION -- 可選部分
-- 程序出現異常時，捕捉異常並處理異常 
END;-- 必須部分

二、PL/SQL變量

　　1、程序變量

　　PL/SQL支持SQL中的數據類型，包括NUMBER,VARCHAR2,DATE等Oracle SQL數據類型。聲明變量必須指明變量的數據類型，也可以聲明變量時對變量設置初始值，變量聲明必須在DECLARE部分。聲明變量的語法如下：

　　變量名數據類型 := 初始值; --設置初始值

　　變量名數據類型; --不設置初始值

　　變量的賦值必須在begin與end直接進行。可以直接使用 := 賦值，也可以使用select語句中的into進行賦值。代碼示例如下：

DECLARE
    v_name VARCHAR2(50); --聲明變量
    v_nickname VARCHAR2(50) := '小王'; --聲明變量，並設置初始值
  v_age NUMBER;
BEGIN
  v_name := '王五'; --為變量賦值
  SELECT age INTO v_age FROM person WHERE id = 1; --通過查詢的方式設置值
    -- 使用oracle dbms輸出每個變量的值
    dbms_output.put_line('v_name:'||v_name);
    dbms_output.put_line('v_nickname:'||v_nickname);
    dbms_output.put_line('v_age:'||v_age);
END;

　　2、程序常量

　　對於查詢常量來說，必須在聲明的時候給它賦值。而且不能再次設置它的值。這類似於java中的常量。程序常量使用constant在聲明的時候進行修飾，聲明語法如下：

　　變量名 constant 數據類型 := 初始值; --設置初始值

　　實例代碼如下；

DECLARE 
    c_nickname constant VARCHAR2(50) := '小王'; --聲明一個常量，並設置初始值 ，不設置初始值會報錯
BEGIN
    --c_nickname := '王五'; --為常量賦值會報錯
    dbms_output.put_line('c_nickname:'||c_nickname); --打印常量的值
END;

　　3、綁定變量

　　　　在sql plus中可以使用綁定變量，使用方式如下：

　　聲明：

var v_name varchar2(50);

　　賦值

execute :v_name :='hello world';

　　打印結果

 print v_name;

　　打印結果如下：

　　該變量僅存在於當前會話中，如果當前會話關閉，重新開啟一個連接，則該變量就會不存在。

三、變量類型

　　以下示例代碼中person表結構如下：

id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

-- ----------------------------
-- Records of PERSON
-- ----------------------------
INSERT INTO PERSON VALUES ('1', '張三', '20', 'zhang123');
INSERT INTO PERSON VALUES ('2', '李四', '20', 'lisi123');
INSERT INTO PERSON VALUES ('3', '王五', '20', 'wang123');
INSERT INTO PERSON VALUES ('4', '趙六', '20', 'zhao123');

　　1、%type

　　　　%type主要是用來定義某個變量的數據類型與已知的變量數據類型或表中某個類的數據類型相同。使用%type的好處如下：　　一、當我們不知道數據表中的某個字段的數據類型我們就可以使用；二、數據庫中字段的數據類型可以在運行時已經發生改變，此時我們無需修改程序，因為程序中的%type會隨字段類型發生相應的變壞。

　　聲明語法如下：

　　　　變量名稱　　表名.字段名%type;

　　示例代碼如下：

DECLARE
    v_id person.id%TYPE ; -- v_id變量數據類型是person表中id的數據類型
    v_username person.username%TYPE ; -- v_id變量數據類型是person表中username的數據類型
    v_age person.age%TYPE ;  -- v_id變量數據類型是person表中age的數據類型
    v_password person.password%TYPE ; -- v_id變量數據類型是person表中password的數據類型
BEGIN
    SELECT
        ID, username, age, password 
    INTO 
        v_id, v_username, v_age, v_password
    FROM
        person
    WHERE
        ID = 1 ; 
    dbms_output.put_line ('id:' || v_id) ; 
    dbms_output.put_line ('username:' || v_username) ; 
    dbms_output.put_line ('age:' || v_age) ; 
    dbms_output.put_line ('password:' || v_password) ;
END ;

　　查詢id為1的記錄，並打印結果。

　　2、%rowtype

　　　　%rowtype也是用於定義不確定類型的變量，可以理解成數據庫記錄一行提取出來的一個副本。通過%rowtype，我們可以獲取一行記錄，然後再使用變量.屬性名，獲取單個的屬性值。聲明%rowtype的語法如下：
　　變量名　　表名%rowtype;

　　示例代碼如下：

 1 DECLARE
 2     r_person person%rowtype; -- 表示person表的一行
 3 BEGIN 
 4     -- 方式一賦值
 5   SELECT id, username, age, password INTO r_person.id, r_person.username, r_person.age, r_person.password FROM person WHERE id=1;
 6     dbms_output.put_line('id:'||r_person.id);
 7   dbms_output.put_line('username:'||r_person.username);
 8   dbms_output.put_line('age:'||r_person.age);
 9   dbms_output.put_line('password:'||r_person.password);
10     -- 方式二賦值
11     SELECT * INTO r_person FROM person WHERE id = 2;
12   dbms_output.put_line('id:'||r_person.id);
13   dbms_output.put_line('username:'||r_person.username);
14   dbms_output.put_line('age:'||r_person.age);
15   dbms_output.put_line('password:'||r_person.password);
16 END ;

　　我們可以使用上面兩種方式進行賦值。在游標中可以用到。

　　3、varray

　　　　varray(varing array)是PL/SQL中的動態數組類型.我們可以動態地對數組的大小進行擴展，但是擴展後的總大小，不能超過聲明時候的大小。使用varray的時候，需要先聲明一個varray數組的類型，然後聲明一個變量並把這個類型賦值給它。使用的時候都是操作這個聲明的變量。

　　　　示例如下：

 1 DECLARE
 2     TYPE arrays IS VARRAY(7) OF VARCHAR2(10) ; -- 定義一個數組，數組元素是5個，每個元素類型為varchar2（10）
 3     -- arrays 是一個對象，使用前需要用一個變量去引用，v_list是變量
 4     v_list arrays := arrays ('zhangsan', 'lisi', 'wangwu', 'zhaoliu', 'wangw') ; 
 5     asize NUMBER ; -- 記錄數組大小
 6 BEGIN
 7     dbms_output.put_line (v_list(1)) ; 
 8     dbms_output.put_line (v_list(2)) ; 
 9     dbms_output.put_line (v_list(3)) ; 
10     dbms_output.put_line (v_list(4)) ; 
11     dbms_output.put_line (v_list(5)) ; 
12     --    dbms_output.put_line(v_list(6)); -- 此時打印會保持下標越界。
13     asize := v_list.COUNT() ; 
14     dbms_output.put_line ('total:' || asize) ; 
15     -- 對v_list大小進行增加
16     v_list.EXTEND(2) ; -- 此時增加後的總共元素不能超過聲明時候的大小
17     asize := v_list.COUNT() ; 
18     dbms_output.put_line('total:' || asize) ; 
19     v_list (6) := 'xiaozhao' ; 
20     v_list (7) := 'xiaoli' ; 
21     dbms_output.put_line (v_list(6)) ; 
22     dbms_output.put_line (v_list(7)) ; 
23     -- 對v_list大小進行減少
24     v_list.TRIM(2) ; 
25     asize := v_list.COUNT() ; 
26     dbms_output.put_line('total:' || asize) ;
27 END ;

　　16行對數組進行擴展，17行對數組大小進行減小。運行結果如下：

　　4、table

　　table 類型與javascript中數組類似，可以理解成可變數組。可以理解成它的大小是無窮的，我們可以給它的任何索引對應的空間進行賦值，聲明的時候也無需指定它的大小。

　　示例代碼如下：

 1 DECLARE
 2    TYPE strings IS TABLE OF VARCHAR2(10) --元素類型是varchar2(10)
 3    INDEX BY binary_integer;
 4    v_list strings; -- 定義的table不能直接使用，必須賦給另一個變量
 5 BEGIN
 6   v_list(1) := 'hello';
 7   v_list(9999) := 'world';
 8   v_list(99) := 33; --賦值數字，會被轉換成字符 ,如果類型是數字，賦值字符的時候，則會報錯
 9   dbms_output.put_line(v_list(1));  
10   dbms_output.put_line(v_list(99));
11   dbms_output.put_line(v_list(9999));   
12 END;

　　運行結果如下：

　　5、record

　　　　record可以理解成java中的集合，可以存放多種類型的數據。record也是需要先聲明，然後用聲明變量來賦值，再操作變量。

　　　　示例代碼如下：

 1 DECLARE
 2     TYPE v_record IS RECORD (
 3         id number,
 4         username person.username%TYPE,
 5         r_tb_person person%ROWTYPE
 6     );
 7   v_person v_record; -- record 不能直接使用，必須賦值給變量
 8 BEGIN
 9     /*  select id, username, age, password into  v_person.r_tb_person.id,  v_person.r_tb_person.username,   v_person.r_tb_person.age,   v_person.r_tb_person.password   from person where id=1; 該種方式賦值也可以*/
10     SELECT * INTO  v_person.r_tb_person FROM person WHERE id = 1 ;
11     dbms_output.put_line('id:'||v_person.r_tb_person.id);
12   dbms_output.put_line('username:'||v_person.r_tb_person.username);
13   dbms_output.put_line('age:'||v_person.r_tb_person.age);
14   dbms_output.put_line('password:'||v_person.r_tb_person.password);
15 END ;

pl/sql like 子句怎使用變量

如ss是varchar類型的話
sql Server:
like '%'+@ss+'%' (SQL Server中所有用戶定義的變量都以“@”開頭)
oracle:
like '%'||ss||'%'

pl/sql變量的使用

定義並使用變量

PL/SQL有四種類型:標量類型,復合類型,引用類型 (reference),LOB(Large Obejct)類型

一、標量類型

最常用的就是標量類型，是指只能存放單個數值的變量,包括數字類型、字符類型、日期類型和布爾類型，每種類型又包含相應的子類型。

常量標量類型如下：

VARCHAR2 (n) , CHAR (n), NUMBER (p,s),DATE, TIMESTAMP , LONG , LONG RAW ,BOOLEAN,BINARY_INTEGER(僅 PL / SQL使用），BINARY_FLOAT和BINARY_DOUBLE(10g新引入的）

定義標量：

identifier [CONSTANT] datatype [NOT NULL] [:=| DEFAULT expr]

使用標量需要注意的是=號被:=取代，與delphi一樣的賦值符號@_@

例子：

v_name VARCHAR2 ( 10 );
v_rate CONSTANTS NUMBER ( 4 , 2 ) : = 3.04 ;

為了防止定義的變量類型與表中的字段類型不一致，可以使用%TYPE來定義：

v_name employee.name % TYPE;

如上面所示，v_name的類型就與表 employee中的name字段類型一樣！！

二、復合變量：

用於存放多個值的變量稱為復合變量，包括PL/SQL記錄，PL/SQL表，嵌套表和VARRAY四種類型

1.PL/SQL記錄

類似於C/C++中的結構概念：

declare
TYPE employee_record is RECORD(
id employee.id % TYPE,
name employee.name % TYPE,
email employee.email % TYPE);
em_record employee_record;
begin
select id,name,email into em_record from employee where name =& name;
dbms_output.put_line( ' 雇員名: ' || em_record.name || ' 雇員ID： ' || em_record.id);
end ;

2.PL/SQL表，類似於數組概念，不同的是PL/SQL表允許負值下標，而且沒有上下限，如：

declare
TYPE employee_table is table of employee.name % TYPE index by BINaRY_INTEGER;
em_table employee_table;
begin
select name into em_table( - 1 ) from employee where name =& name;
dbms_output.put_line( ' 雇員名: ' || em_table( - 1 ));
end ;

3.嵌套表，與PL/SQL 表相似，不同的是嵌套表可以做表列的數據類型，而PL/SQL表不能，使用嵌套表作為表列時，必須為其指定專門的存儲表，如：

create ......余下全文>>