SQL> declare
2 c_tax_rate number(3,2):=0.03;
3 v_name varchar2(20);
4 v_passwd varchar2(20);
5 v_sale number(7,2);
6 v_tax_sale number(7,2);
7 begin
8 select name,passwd,salary into v_name,v_passwd,v_sale from mytest where id=&inpt;
9 v_tax_sale:=v_sale*c_tax_rate;
10 dbms_output.put_line('name:'||v_name||' passwd:'||v_passwd||' taxsale:'||v_tax_sale);
11 end;
12 / name:123 passwd:123 taxsale:3 PL/SQL procedure successfully completed
在声明变量的时候可以设置类型为动态的取表中的字段的类型一致
SQL> insert into mytest values('','','passwd','');
1 row inserted
SQL> select * from mytest;
ID NAME PASSWD SALARY
----- -------------------- -------------------- --------------------------------------------------------------------------------
1 123 123 100
2 gaodingle!!!! 123 100
3 gagaga 123 100
4 125555 passwd 50
SQL>
SQL> declare
2 c_tax_rate number(3,2):=0.03;
3 v_name varchar2(4);
4 v_passwd varchar2(20);
5 v_sale number(7,2);
6 v_tax_sale number(7,2);
7 begin
8 select name,passwd,salary into v_name,v_passwd,v_sale from mytest where id=&inpt;
9 v_tax_sale:=v_sale*c_tax_rate;
10 dbms_output.put_line('name:'||v_name||' passwd:'||v_passwd||' taxsale:'||v_tax_sale);
11 end;
12 /
declare
c_tax_rate number(3,2):=0.03;
v_name varchar2(4);
v_passwd varchar2(20);
v_sale number(7,2);
v_tax_sale number(7,2);
begin
select name,passwd,salary into v_name,v_passwd,v_sale from mytest where id=4;
v_tax_sale:=v_sale*c_tax_rate;
dbms_output.put_line('name:'||v_name||' passwd:'||v_passwd||' taxsale:'||v_tax_sale);
end;
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 9
SQL>
SQL> declare
2 c_tax_rate number(3,2):=0.03;
3 v_name mytest.name%type;
4 v_passwd varchar2(20);
5 v_sale number(7,2);
6 v_tax_sale number(7,2);
7 begin
8 select name,passwd,salary into v_name,v_passwd,v_sale from mytest where id=&inpt;
9 v_tax_sale:=v_sale*c_tax_rate;
10 dbms_output.put_line('name:'||v_name||' passwd:'||v_passwd||' taxsale:'||v_tax_sale);
11 end;
12 /
name:125555 passwd:passwd taxsale:1.5
PL/SQL procedure successfully completed
SQL> declare
2 --定义一个pl/sql记录类型叫做 test_record_type 可以理解为定义一个类，这个类型中包括了name,passwd,salary
3 type test_record_type is record (name mytest.name%type,passwd mytest.passwd%type,salary_gaga mytest.salary%type);
4 --定义一个变量这个变量是test_record_type类型 好比类new一个对象
5 sp_record test_record_type;
6 begin
7 select name,passwd,salary into sp_record from mytest where id=&inpt;
8 dbms_output.put_line('name:'||sp_record.name||' salary:'||sp_record.salary_gaga);
9 end;
10 / name:123 salary:100
SQL> declare
2 type sp_test_table is table of mytest.name%type index by binary_integer;
3 test_table sp_test_table;
4 begin
5 select name into test_table(0) from mytest where id=&inpt;
6 dbms_output.put_line('name:'||test_table(0));
7 end;
8 / name:gagaga PL/SQL procedure successfully completed