PL/SQL 学习笔记2

变量声明

 

Syntax:

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

 

SQL> declare 2 a date; 3 b number(20) not null :=100; 4 c varchar2(10); 5 d constant number(20) default 1000; 6 begin 7 null; 8 end; 9 / PL/SQL procedure successfully completed.

 

SQL> var v_bind number SQL> exec :v_bind := 2984; PL/SQL procedure successfully completed. SQL> r 1 declare 2 v_a number(20); 3 v_b constant number := 1000; 4 v_c constant number default 2000; 5 v_d varchar2(20) not null default 'asf'; 6 begin 7 v_a := 2003; 8 dbms_output.put_line(v_a ||' '|| v_b ||' '|| v_c ||' ' || v_d || ' ' || '&a' || ' '||:v_bind) ; 9* end; Enter value for a: 34567 old 8: dbms_output.put_line(v_a ||' '|| v_b ||' '|| v_c ||' ' || v_d || ' ' || '&a' || ' '||:v_bind) ; new 8: dbms_output.put_line(v_a ||' '|| v_b ||' '|| v_c ||' ' || v_d || ' ' || '34567' || ' '||:v_bind) ; 2003 1000 2000 asf 34567 2984 PL/SQL procedure successfully completed.

 

Scalar 标量 data type holds a single value and has no internal components.

number,

character,

date, 

Boolean.

Character and number data types have subtypes that
associate a base type to a constraint. For example, INTEGERand POSITIVEare subtypes of the NUMBER
base type.

 

?CHAR [(maximum_length)]
? VARCHAR2(maximum_length)
? LONG
? LONG RAW
? NUMBER [(precision, scale)]
? BINARY_INTEGER
? PLS_INTEGER
? BOOLEAN

 

SQL> declare 2 v_job varchar2(20); 3 v_count binary_integer := 0; 4 v_total_sal number(9,2) := 0; 5 v_orderdate date := sysdate + 7; 6 c_tax_rate constant number(3,2) := 8.02; 7 v_valid boolean not null :=true; 8 begin 9 null; 10 end; 11 /

v_job: variable to store an employee job title
v_count: variable to count the iterations of a loop and initialized to 0
v_total_sal: variable to accumulate the total salary for a department and initialized to 0
v_orderdate: variable to store the ship date of an order and initialize to one week from today
c_tax_rate: a constant variable for the tax rate, which never changes throughout the PL/SQL block
v_valid: flag to indicate whether a piece of data is valid or invalid and initialized to TRUE