create or replace
PROCEDURE pr_simple_variable
AS
BEGIN
DECLARE
/*pls_integer为pl/sql类型、不能用于表列的数据类型、范围-2147483647~2147483647*/
v_pls_int_min pls_integer :=-2147483647;
v_pls_int_max pls_integer :=2147483647;
/*NATURAL为pls_integer的子类型、自然数、0~2147483647*/
v_natual_min NATURAL :=0;
v_natual_max NATURAL :=2147483647;
/*simple_integer为pls_integer的子类型、具有not null约束*/
v_simple_int_min simple_integer:=-2147483647;
v_simple_int_max simple_integer:=2147483647;
/*binary_float为单精度浮点数、为变量赋值时、后面要加f*/
v_binary_float_min binary_float :=-9999999999999.9F;
v_binary_float_max binary_float :=9999999999999.9F;
/*binary_double为双精度浮点数、为变量赋值时、后面要加d*/
v_binary_double_min binary_double:=-9999999999999.9;
V_Binary_Double_Max Binary_Double:=9999999999999.9;
/*number(p,s)、p为数字总位数、s为小数位数*/
v_number_min NUMBER(10,2):=-99999999.99;
v_number_max NUMBER(10,2):=99999999.99;
/*decimal是number的子类型、最大精度38位*/
v_decimal DECIMAL(10,2):=99999999.99;
/*float是number的子类型、最大精度38位单精度浮点型*/
v_float FLOAT:=999999.9999;
/*double是number的子类型、最大精度38位双精度浮点型*/
v_double DOUBLE PRECISION:=9999999.999;
/*real定义精度为18位的实数*/
v_real REAL:=999999.999;
/*int、integer、smallint定义为38位的整数*/
v_int INT :=999999;
v_integer INTEGER :=99999999;
v_smallint SMALLINT:=99999;
/*****************************************************************************************/
/*varchar2、nvarchar2变长字符串、最长32767*/
v_varchar2 VARCHAR2(100) :='Hello World!!';
v_nvarchar2 NVARCHAR2(100):='测试变量数据!';
/*char、nchar定长字符串、最长32767*/
v_char CHAR(5) :='Hello';
v_nchar nchar(2):='测试';
/*raw变长二进制字符串;rowid物理存储的rowid*/
v_raw raw(100):='10100011111';
v_rowid rowid;
/*boolean为pl/sql类型、true、false、null*/
v_boolean BOOLEAN:=true;
/*date固定长度、7个字节*/
v_date DATE;
v_timestamp TIMESTAMP;
/*使用%type定义数据类型那个*/
v_no dept.deptno%type;
v_name dept.dname%type;
v_loc dept.loc%type;
BEGIN
dbms_output.put_line('pls_integer最小值:'||v_pls_int_min);
dbms_output.put_line('pls_integer最大值:'||v_pls_int_max);
dbms_output.put_line('NATURAL最小值:'||v_natual_min);
dbms_output.put_line('NATURAL最大值:'||v_natual_max);
dbms_output.put_line('simple_integer最小值:'||v_simple_int_min);
dbms_output.put_line('simple_integer最大值:'||v_simple_int_max);
dbms_output.put_line('binary_float最小值:'||v_binary_float_min);
dbms_output.put_line('binary_float最大值:'||v_binary_float_max);
dbms_output.put_line('binary_double最小值:'||v_binary_double_min);
dbms_output.put_line('binary_double最大值:'||v_binary_double_max);
dbms_output.put_line('NUMBER最小值:'||v_number_min);
dbms_output.put_line('NUMBER最大值:'||v_number_max);
dbms_output.put_line('decimal数据:'||v_decimal);
dbms_output.put_line('float数据:'||v_float);
dbms_output.put_line('double数据:'||v_double);
dbms_output.put_line('real数据:'||v_real);
dbms_output.put_line('int数据:'||v_int||';integer数据'||v_integer||';smallint数据'||v_smallint);
dbms_output.put_line('varchar2数据:'||v_varchar2||';nvarchar2数据:'||v_nvarchar2);
dbms_output.put_line('char2数据:'||v_char||';nchar2数据:'||v_nchar);
SELECT rowid INTO v_rowid FROM dept WHERE rownum=1;
dbms_output.put_line('raw数据:'||v_raw||';rowid数据:'||v_rowid);
IF v_boolean=true THEN
dbms_output.put_line('boolean数据:true');
elsif v_boolean=false THEN
dbms_output.put_line('boolean数据:false');
ELSE
dbms_output.put_line('boolean数据:null');
END IF;
SELECT sysdate INTO v_date FROM dual;
dbms_output.put_line('date数据:'||TO_CHAR(v_date,'yyyy-mm-dd hh24:mm:ss'));
SELECT systimestamp INTO v_timestamp FROM dual;
dbms_output.put_line('timestamp数据:'||v_timestamp);
v_name:='Hello World';
SELECT deptno,dname,loc INTO v_no,v_name,v_loc FROM dept WHERE rownum=1;
dbms_output.put_line('编号:'||v_no||';名称:'||v_name||';注释:'||v_loc);
END;
END pr_simple_variable;