oracle变量
<div style="border-right: #cccccc 1px solid; padding-right: 5px; border-top: #cccccc 1px solid; padding-left: 4px; font-size: 13px; padding-bottom: 4px; border-left: #cccccc 1px solid; width: 98%; padding-top: 4px; border-bottom: #cccccc 1px solid; background-color: #eeeeee;">http://www.blogjava.net/Images/OutliningIndicators/None.gif--1定义标量变量http://www.blogjava.net/Images/OutliningIndicators/None.gif--（1）定义语法
http://www.blogjava.net/Images/OutliningIndicators/None.gif变量名 [constant] 数据类型 [not null] [:= | default expr]
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- constant:用于指定常量。必须指定初始值
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- := 用于为变量和常量指定初始值
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- expr初始值的pl/sql表达式，可以是文本值、变量、函数等
http://www.blogjava.net/Images/OutliningIndicators/None.gif--示例1：
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_name varchar2(10);
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_rate constant number(3,2) := 5.5;
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_valid boolean not null default false;
http://www.blogjava.net/Images/OutliningIndicators/None.gif--（2）使用
http://www.blogjava.net/Images/OutliningIndicators/None.gif--变量赋值使用等号前加冒号（:=）
http://www.blogjava.net/Images/OutliningIndicators/None.gif--示例2：
http://www.blogjava.net/Images/OutliningIndicators/None.gifdeclare
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_name varchar2(10);
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_money number(6,2);
http://www.blogjava.net/Images/OutliningIndicators/None.gifc_tax_rate constant number(3,2) := -0.03;
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_tax_money number(6,2);
http://www.blogjava.net/Images/OutliningIndicators/None.gifbegin
http://www.blogjava.net/Images/OutliningIndicators/None.gif select user_name,game_money into v_name,v_money
http://www.blogjava.net/Images/OutliningIndicators/None.gif from user_info where user_id = 100000;
http://www.blogjava.net/Images/OutliningIndicators/None.gif v_tax_money := v_money*c_tax_rate;
http://www.blogjava.net/Images/OutliningIndicators/None.gif dbms_output.put_line('name:'||v_name);
http://www.blogjava.net/Images/OutliningIndicators/None.gif dbms_output.put_line('money:'||v_money);
http://www.blogjava.net/Images/OutliningIndicators/None.gif dbms_output.put_line('rate:'||v_tax_money);
http://www.blogjava.net/Images/OutliningIndicators/None.gifend
http://www.blogjava.net/Images/OutliningIndicators/None.gif--（3）使用%TYPE属性
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_name user_info.user_name%TYPE;
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_money user_info.game_money%TYPE;
http://www.blogjava.net/Images/OutliningIndicators/None.gifc_tax_rate constant number(3,2) := -0.03;
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_tax_money v_money%TYPE;
http://www.blogjava.net/Images/OutliningIndicators/None.gif--变量v_name，v_money与user_info表的user_name，game_money列的类型和长度完全一致
http://www.blogjava.net/Images/OutliningIndicators/None.gif--变量v_tax_money与变是v_money的类型和长度完全一致
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif--2复合变量
http://www.blogjava.net/Images/OutliningIndicators/None.gif--（1）pl/sql记录（类似于结构）
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 在定义部分定义记录类型和记录变量，在执行部分引用该记录变量
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 引用记录成员时必须要加记录变量作为前缀（记录变量。记录成员）
http://www.blogjava.net/Images/OutliningIndicators/None.gif--示例3：
http://www.blogjava.net/Images/OutliningIndicators/None.gifdeclare
http://www.blogjava.net/Images/OutliningIndicators/None.gifTYPE record_type IS RECORD(
http://www.blogjava.net/Images/OutliningIndicators/None.gif v_name user_info.user_name%TYPE,
http://www.blogjava.net/Images/OutliningIndicators/None.gif v_money user_info.game_money%TYPE
http://www.blogjava.net/Images/OutliningIndicators/None.gif);
http://www.blogjava.net/Images/OutliningIndicators/None.gifemp_record record_type;
http://www.blogjava.net/Images/OutliningIndicators/None.gifbegin
http://www.blogjava.net/Images/OutliningIndicators/None.gif select user_name,game_money into emp_record
http://www.blogjava.net/Images/OutliningIndicators/None.gif from user_info where user_id = 100000;
http://www.blogjava.net/Images/OutliningIndicators/None.gif dbms_output.put_line('name:'||emp_record.v_name);
http://www.blogjava.net/Images/OutliningIndicators/None.gif dbms_output.put_line('money:'||emp_record.v_money);
http://www.blogjava.net/Images/OutliningIndicators/None.gifend
http://www.blogjava.net/Images/OutliningIndicators/None.gif--（2）pl/sql表（类似于数组）
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- pl/sql表与数组区别：下标没有上下限，个数年没有限制，下票可以为负值
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 必须先在定义部分定义pl/sql表类型和pl/sql表变量，在执行部分引用该pl/sql表变量
http://www.blogjava.net/Images/OutliningIndicators/None.gif--示例4：
http://www.blogjava.net/Images/OutliningIndicators/None.gifdeclare
http://www.blogjava.net/Images/OutliningIndicators/None.gifTYPE name_table_type IS TABLE OF user_info.user_name%TYPE
http://www.blogjava.net/Images/OutliningIndicators/None.gif INDEX BY BINARY_INTEGER;
http://www.blogjava.net/Images/OutliningIndicators/None.gifv_name name_table_type;
http://www.blogjava.net/Images/OutliningIndicators/None.gifbegin
http://www.blogjava.net/Images/OutliningIndicators/None.gif select user_name into v_name(-1)
http://www.blogjava.net/Images/OutliningIndicators/None.gif from user_info where user_id = 100000;
http://www.blogjava.net/Images/OutliningIndicators/None.gif dbms_output.put_line('name:'||v_name(-1));
http://www.blogjava.net/Images/OutliningIndicators/None.gifend
http://www.blogjava.net/Images/OutliningIndicators/None.gif--（3）嵌套表
http://www.blogjava.net/Images/OutliningIndicators/None.gif--（4）VARRAY（变长数组）
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif--3 参照变量
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 用于存放数值指针的变量。
http://www.blogjava.net/Images/OutliningIndicators/None.gif--（1）游标变量（REF CURSOR）
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 静态游标：需要在定义游标时指定相应的select语句
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 示例5：
http://www.blogjava.net/Images/OutliningIndicators/None.gifdeclare
http://www.blogjava.net/Images/OutliningIndicators/None.gif type c1 is ref cursor;--c1为ref cursor类型
http://www.blogjava.net/Images/OutliningIndicators/None.gif emp_cursor c1;--emp_cursor为游标变量
http://www.blogjava.net/Images/OutliningIndicators/None.gif v_name user_info.user_name%TYPE;
http://www.blogjava.net/Images/OutliningIndicators/None.gif v_money user_info.game_money%TYPE;
http://www.blogjava.net/Images/OutliningIndicators/None.gifbegin
http://www.blogjava.net/Images/OutliningIndicators/None.gif open emp_cursor for --打开游标变量时指定了对应的select语句
http://www.blogjava.net/Images/OutliningIndicators/None.gif select user_name,game_money from user_info where user_id = 100000;
http://www.blogjava.net/Images/OutliningIndicators/None.gif loop
http://www.blogjava.net/Images/OutliningIndicators/None.gif fetch emp_cursor into v_name,v_money;
http://www.blogjava.net/Images/OutliningIndicators/None.gif exit when emp_cursor%notfound;
http://www.blogjava.net/Images/OutliningIndicators/None.gif dbms_output.put_line(v_name);
http://www.blogjava.net/Images/OutliningIndicators/None.gif end loop;
http://www.blogjava.net/Images/OutliningIndicators/None.gifend;
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 动态游标：在定义游标变量时不要需指定相应的select语句，而是打开游标时指定select语句
http://www.blogjava.net/Images/OutliningIndicators/None.gif--（2）对象类型变量（REF obj_type）
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 是指向对象实例的指针
http://www.blogjava.net/Images/OutliningIndicators/None.gif-- 示例6：
http://www.blogjava.net/Images/OutliningIndicators/None.gifcreate or replace type home_type as object(--建立对象类型
http://www.blogjava.net/Images/OutliningIndicators/None.gif street varchar2(50),city varchar2(20),
http://www.blogjava.net/Images/OutliningIndicators/None.gif state varchar2(20),zipcode varchar2(6),
http://www.blogjava.net/Images/OutliningIndicators/None.gif owner varchar2(10)
http://www.blogjava.net/Images/OutliningIndicators/None.gif);
http://www.blogjava.net/Images/OutliningIndicators/None.gifcreate table homes of home_type;--建表
http://www.blogjava.net/Images/OutliningIndicators/None.gifinsert into homes values('上海路100号','上海','200000','junly');
http://www.blogjava.net/Images/OutliningIndicators/None.gifcommit;
http://www.blogjava.net/Images/OutliningIndicators/None.gif--对象表homes存放家庭地址及户主姓名，如每个家庭有四口人，为了同一家庭成员共享家庭地址，
http://www.blogjava.net/Images/OutliningIndicators/None.gif--可使用REF引用home_type对象类型，从而降低占用空间。
http://www.blogjava.net/Images/OutliningIndicators/None.gifcreate table person(
http://www.blogjava.net/Images/OutliningIndicators/None.gif id number(6) primary key,
http://www.blogjava.net/Images/OutliningIndicators/None.gif name varchar2(10),
http://www.blogjava.net/Images/OutliningIndicators/None.gif addr ref home_type
http://www.blogjava.net/Images/OutliningIndicators/None.gif);
http://www.blogjava.net/Images/OutliningIndicators/None.gifinsert into person select 1,'junly',ref(p) from homes p where p.owner='junly'
http://www.blogjava.net/Images/OutliningIndicators/None.gifinsert into person select 2,'junl2',ref(p) from homes p where p.owner='junly'
http://www.blogjava.net/Images/OutliningIndicators/None.gif--person表插入数据时，addr列将存入指向homes表相应数据的地址指针
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif--4 LOB变量
http://www.blogjava.net/Images/OutliningIndicators/ExpandedBlockStart.gifhttp://www.blogjava.net/Images/OutliningIndicators/ContractedBlock.gif/**//*用于存储大批量数据的变量
http://www.blogjava.net/Images/OutliningIndicators/InBlock.gif（1）内部LOB
http://www.blogjava.net/Images/OutliningIndicators/InBlock.gif CLOB 支技事务操作 存储数据库中 用于存储大批量字符数据
http://www.blogjava.net/Images/OutliningIndicators/InBlock.gif BLOB 支技事务操作 存储数据库中 用于存储大批量二进制数据
http://www.blogjava.net/Images/OutliningIndicators/InBlock.gif NCLOB 支技事务操作 存储数据库中 用于存储大批量字符数据
http://www.blogjava.net/Images/OutliningIndicators/InBlock.gif（2）外部LOB
http://www.blogjava.net/Images/OutliningIndicators/InBlock.gif BFILE 不支技事务 存在OS文件中 存储指向OS文件的指针
http://www.blogjava.net/Images/OutliningIndicators/ExpandedBlockEnd.gif*/
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif--5非PL/SQL变量
页:
[1]