Oracle数据库学习总结(2)

Oracle数据库编程学习心得

包

类似与c#中的命名空间。它分为两个部分：包规范和包体。它还支持重载能力，在包中可以包含相同名称的函数或存储过程，只有它们的参数不同就不妨碍它们各自的调用。下面写几个包，看代码：

Oracle数据库编程学习心得

as begin dbms_output.put_line('Hello World By Procedure!'); end; function HelloWorld return varchar2 as begin return 'Hello World By Function!'; end; end; 学生管理系统的包 包重载 create or replace package mypackage_student as myVersion nvarchar2(250); procedure printfCopyRight; procedure printfCopyRight(message varchar2); procedure addStudent(stuId number,stuName varchar2,stuAge number); function getStuNumbersByTeacher(tName varchar2) return number; function getStuNumbersByTeacherId(tId number) return number; end; --包规范 create or replace package body mypackage_student as procedure printfCopyRight as begin dbms_output.put_line(myVersion); end; procedure printfCopyRight(message varchar2) as begin dbms_output.put_line(message); end; procedure addStudent(stuId number,stuName varchar2,stuAge number) as begin insert into student values(stuId,stuName,stuAge); end; function getStuNumbersByTeacher(tName varchar2) return number

Oracle数据库编程学习心得

索引表，嵌套表，可变长的表

Oracle数据库编程学习心得

select name into stu_name(-1) from student where id=&id; dbms_output.put_line(stu_name(-1)); end; 嵌套表 declare type nameTable is table of varchar2(50); stu_name nameTable:=nameTable('abc','abc');--用前要初始化 begin select name into stu_name(1) from student where id=&id; dbms_output.put_line(stu_name(1)); end; 可变成的表，这里将它们嵌套起来可以做出 2 维数组的形式。 declare type a1_varray_type is varray(10) of int; type na1_varray_type is varray(10) of a1_varray_type; nv1 na1_varray_type:= na1_varray_type( a1_varray_type(1,2,3), a1_varray_type(4,5,6), a1_varray_type(7,8,9) ); begin for i in 1..nv1.count loop for j in 1..nv1(i).count loop dbms_output.put_line(nv1(i)(j)); end loop; end loop; end; 嵌套表的二维数组形式: declare type family is table of varchar2(20); type familyCollection is table of family; --MyFamily family:=family('jim','ann','bank'); myFamilyCollection familyCollection:=familyCollection( family('jim','ann','bank'), family('jim2','ann2','bank2'), family('jim3','ann3','bank3') ); begin for i in 1..myFamilyCollection.count loop for j in 1..myFamilyCollection(i).count loop

Oracle数据库编程学习心得

上面说的这3类表都支持下面这些方法：

Count,limit,first,last,prior,next,extend,trim