- ------------------------------------定义使用变量----------------------------------
- --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++变量