------------------------------------定义使用变量---------------------------------- --scalar 变量 declare v_ename varchar2(5); v_sal number(6,2); c_tax_rate constant number(3,2) :=0.03; v_tax_sal number(6,2); begin select ename,sal into v_ename,v_sal from emp where empno=&eno; v_tax_sal:=v_sal*c_tax_rate; dbms_output.put_line('雇员名:'||v_ename); dbms_output.put_line('雇员工资:'||v_sal); dbms_output.put_line('所得税:'||v_tax_sal); end; / --最好使用%type declare v_ename emp.ename%type; v_sal emp.sal%type; v_tax_rate constant number(3,2) :=0.03; v_tax_sal v_sal%type; begin select ename,sal into v_ename ,v_sal from emp where empno=&no; v_tax_sal := v_sal*v_tax_rate; dbms_output.put_line('雇员名:'||v_ename); dbms_output.put_line('雇员工资:'||v_sal); dbms_output.put_line('所得税:'||v_tax_sal); end; / --composite 变量 1:pl/sql记录 记录类型:emp_record_type 记录变量:emp_record 记录成员:记录变量.记录成员 emp_record.name declare type emp_record_type is record( name emp.ename%type, salary emp.sal%type, title emp.job%type); emp_record emp_record_type; begin select ename,sal,job into emp_record from emp where empno=&no; dbms_output.put_line('雇员名:'||emp_record.name); dbms_output.put_line('雇员工资:'||emp_record.salary); dbms_output.put_line(' 岗位:'||emp_record.title); end; / 2:pl/sql表 declare type ename_table_type is table of emp.ename%type index by binary_integer; ename_table ename_table_type; begin select ename into ename_table(-1) from emp where empno=&no; dbms_output.put_line('雇员名:'||ename_table(-1)); end; / 3:嵌套表 4:VARRAY --Reference 变量 1:ref cursor 游标变量 fetch into declare type c1 is ref cursor; dyn_cursor c1; col1 varchar2(20); col2 varchar2(20); begin open dyn_cursor for select &col1,&col2 from &tab where &con; fetch dyn_cursor into col1,col2; dbms_output.put_line('col1:'||col1); dbms_output.put_line(' col2:'||col2); close dyn_cursor; end; 2:ref obj_type --lob 变量 clob、blob、nclob、bfile clob和nclob储存大字符数据 bolb储存大二进制数据 bfile储存指向os文件的指针 --使用子类型定义变量 subtype subtype_name is base_type [(constrain)] [not null]; declare subtype my_type is varchar2(20); v_name my_type(20); begin select ename into v_name from emp where empno=&no; dbms_output.put_line('name:'||v_name); end; / --非pl/sql变量 1:sql*plus变量 2:pro*c/c++变量