--%type,可以用来指明一个变量的类型与指定表中列的类型相同 --当表的结构修改时,你不必去修改此变量的类型,%type为你自动绑定; --%rowtype与%type相似,只不过这是将变量绑定到表中一行上; --下面是一个实例: SQL> create table dept( 2 deptno varchar2(5), 3 dname varchar2(20), 4 loc varchar2(20));Table createdSQL> SQL> create or replace procedure pro_insert( 2 deptno_in in varchar2, 3 dname_in in varchar2, 4 loc_in in varchar2 5 ) 6 as 7 v_dept dept%rowtype; 8 begin 9 begin 10 insert into dept 11 select deptno_in,dname_in,loc_in 12 from dual; 13 commit; 14 dbms_output.put_line('inserting successed'); 15 end; 16 begin 17 select deptno_in,dname_in,loc_in 18 into v_dept from dual; 19 dbms_output.put_line( 20 'the data having been inserted:'|| 21 'deptno:'||v_dept.deptno|| 22 ',dname:'||v_dept.dname|| 23 ',loc:'||v_dept.loc 24 ); 25 end; 26 end pro_insert; 27 /Procedure createdSQL> set serveroutput on; SQL> exec pro_insert('111','财务部','福州');inserting successed the data having been inserted:deptno:111,dname:财务部,loc:福州PL/SQL procedure successfully completed
create or replace procedure pro_insert( deptno_in in varchar2, dname_in in varchar2, loc_in in varchar2 ) as v_dept dept%rowtype; begin begin insert into dept select deptno_in,dname_in,loc_in from dual; commit; dbms_output.put_line('inserting successed'); exception --具体点,应该包括异常处理部分 when others then rollback; end; begin select deptno_in,dname_in,loc_in into v_dept from dual; dbms_output.put_line( 'the data having been inserted:'|| 'deptno:'||v_dept.deptno|| ',dname:'||v_dept.dname|| ',loc:'||v_dept.loc ); end; end pro_insert;
DECLARE row_dept dept%ROWTYPE; BEGIN row_dept.deptno:=11; row_dept.dname:='财务部'; row_dept.loc:='福州'; INSERT INTO dept VALUES(row_dept.deptno,row_dept.dname,row_dept.loc); END;
/*用%ROWTYPE属性声明的记录变量自动具有和引用表的列名一致的字段名*/ DECLARE
selPart tb_phone_no%ROWTYPE;
BEGIN
SELECT phone_no,city_code,city_name,state,type
INTO selPart
FROM tb_phone_no
WHERE phone_no= '1333241 ';
DBMS_OUTPUT.PUT_LINE( 'phone_no: ' || selPart.phone_no ||
' city_code: ' || selPart.city_code ||
' city_name: ' || selPart.city_name ||
' state: ' || selPart.state ||
' type: ' || selPart.type);
END; ------------------------------
SQL> desc tb_phone_no;
名称 是否为空?类型
------------------------------- -------- ----
PHONE_NO NOT NULL VARCHAR2(7)
CITY_CODE NOT NULL VARCHAR2(4)
CITY_NAME NOT NULL VARCHAR2(8)
STATE NOT NULL VARCHAR2(6)
TYPE NOT NULL VARCHAR2(6)
不用考虑数据类型问题呀。
--%type,可以用来指明一个变量的类型与指定表中列的类型相同
--当表的结构修改时,你不必去修改此变量的类型,%type为你自动绑定;
--%rowtype与%type相似,只不过这是将变量绑定到表中一行上;
--下面是一个实例:
SQL> create table dept(
2 deptno varchar2(5),
3 dname varchar2(20),
4 loc varchar2(20));Table createdSQL>
SQL> create or replace procedure pro_insert(
2 deptno_in in varchar2,
3 dname_in in varchar2,
4 loc_in in varchar2
5 )
6 as
7 v_dept dept%rowtype;
8 begin
9 begin
10 insert into dept
11 select deptno_in,dname_in,loc_in
12 from dual;
13 commit;
14 dbms_output.put_line('inserting successed');
15 end;
16 begin
17 select deptno_in,dname_in,loc_in
18 into v_dept from dual;
19 dbms_output.put_line(
20 'the data having been inserted:'||
21 'deptno:'||v_dept.deptno||
22 ',dname:'||v_dept.dname||
23 ',loc:'||v_dept.loc
24 );
25 end;
26 end pro_insert;
27 /Procedure createdSQL> set serveroutput on;
SQL> exec pro_insert('111','财务部','福州');inserting successed
the data having been inserted:deptno:111,dname:财务部,loc:福州PL/SQL procedure successfully completed
create or replace procedure pro_insert(
deptno_in in varchar2,
dname_in in varchar2,
loc_in in varchar2
)
as
v_dept dept%rowtype;
begin
begin
insert into dept
select deptno_in,dname_in,loc_in
from dual;
commit;
dbms_output.put_line('inserting successed');
exception --具体点,应该包括异常处理部分
when others then
rollback;
end;
begin
select deptno_in,dname_in,loc_in
into v_dept from dual;
dbms_output.put_line(
'the data having been inserted:'||
'deptno:'||v_dept.deptno||
',dname:'||v_dept.dname||
',loc:'||v_dept.loc
);
end;
end pro_insert;
row_dept dept%ROWTYPE;
BEGIN
row_dept.deptno:=11;
row_dept.dname:='财务部';
row_dept.loc:='福州';
INSERT INTO dept VALUES(row_dept.deptno,row_dept.dname,row_dept.loc);
END;