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 i