oracle %type和%rowtype
来源:互联网 发布:网络表情包弹棉花 编辑:程序博客网 时间:2024/06/04 19:46
create table dept( deptno varchar2(5), dname varchar2(20), loc varchar2(20) ); create or replace procedure pro_insert( deptno_in in dept.deptno%type, dname_in in dept.dname%type, loc_in in dept.loc%type ) 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; SQL> set serveroutput on; SQL> exec pro_insert('111','财务部','福州'); inserting successed The data having been inserted.deptno:111,dname:财务部,loc:福州 PL/SQL procedure successfully completed declare v_dept dept%rowtype; begin select deptno,dname,loc into v_dept from dept; dbms_output.put_line( 'The data having been inserted.'|| 'deptno:'||v_dept.deptno|| ',dname:'||v_dept.dname|| ',loc:'||v_dept.loc ); end; declare cursor cv_dept is select * from dept; begin for v_dept in cv_dept loop dbms_output.put_line( 'deptno:'||v_dept.deptno|| ',dname:'||v_dept.dname|| ',loc:'||v_dept.loc ); end loop; end; deptno:111,dname:财务部,loc:福州 deptno:120,dname:销售部,loc:大连 deptno:130,dname:科研部,loc:北京 PL/SQL procedure successfully completed