oracle函数及存储过程教程.pdfVIP

u--常用变量类型

1.binary_integer：整数，主要用来计数而不是用来表示字段类型

2.number数字类型3.char定长字符串4.date日期5.long长字符串，最长

2GG

6.boolean类型：可以取值true、false、null，默认为null，

declare

v_enameemp.ename%type;

v_salemp.sal%type;

begin

selectename,salintov_ename,v_salfromempwhereempno=7902;

ifv_enameisnullthen

dbms_output.put_line(null);

else

dbms_output.put_line(v_ename||||v_sal);

endif;

end;

Record变量类型，相当于类

declare

typeemp_recordisrecord

(v_enameemp.ename%type,

v_salemp.sal%type,

v_empnoemp.empno%type

);

v_empemp_record;

begin

selectename,sal,empnointov_empfromempwhereempno=7902;

dbms_output.put_line(v_emp.v_ename||,||v_emp.v_sal||,||v_emp.v_empno);

end;

declare

typetest_recordisrecord(

v_enameemp.ename%type,

v_salemp.sal%type,

v_locdept.loc%type,

v_dnamedept.dname%type,

v_deptnodept.deptno%type

);

v_testtest_record;

begin

selecte.ename,e.sal,d.loc,d.dname,d.deptno

intov_testfromempejoindeptd

on(e.deptno=d.deptno)

wheree.empno=7902;

dbms_output.put_line(v_test.v_ename||,||v_test.v_sal);

end;

--使用%rowtype声明record变量

declare

v_tempdept%rowtype;

begin

v_temp.deptno:=50;

v_temp.dname:=yugang;

v_temp.loc:=beijing;

dbms_output.put_line(v_temp.deptno||||v_temp.dname||||

v_temp.loc);

end;

Table变量类型,相当于数组类型

while循环

declare

typetype_tableistableofnumberindexbybinary_integer;

v_tabletype_table;

v_indexbinary_integer;

begin

v_table(20):=20;

v_table(-1):=-1;

v_index:=v_table.first;

whilev_index=v_table.lastloop

dbms_output.put_line(v_table(v_index));

v_index:=v_table