网站目录改版,seo初学教程,shop后缀的网站,山东规划 建设部门的网站1、Oracle 新建员工表和部门表.sql。
集合类型
1、Oracle 集合是相同类型元素的组合#xff0c;在集合中#xff0c;使用唯一的下标来标识其中的每个元素#xff0c;与 Java 的 List 很像。
2、常用集合方式#xff1a;
类型语法下标元素个数初始值.extend能否存在DB中…1、Oracle 新建员工表和部门表.sql。
集合类型
1、Oracle 集合是相同类型元素的组合在集合中使用唯一的下标来标识其中的每个元素与 Java 的 List 很像。
2、常用集合方式
类型语法下标元素个数初始值.extend能否存在DB中索引表 TYPE type_name IS TABLE OF element_type INDEX BY index_type; table_name type_name : type_name(); 整数或字符 可以为负数无限制不用不用只能用在PLSQL中嵌套表 TYPE type_name IS TABLE OF element_type; table_name type_name : type_name(); 只能为整数无限制用同名构造函数赋值用可以使用create type 创建, 存在数据库中变长数组 TYPE type_name IS VARRAY(n) OF element_type; varray_name type_name : type_name(); 有限制 索引表不需要初始化或者extend且无个数限制迄今为止最高效的集合类型优先选择。唯一不足的一点是它只能用于PL/SQL而不能直接用于数据库。嵌套表如果需要使用10g11g中的那些集合操作则可以选择Nested table嵌套表;变长数组需要限制集合元素个数时可以使用。 3、集合常用方法集合方法是集合对象的内置函数可以使用.标记来调用。
方法描述 exists(n) 索引处的元素是否存在, 返回TRUE|FALSEcount当前集合中的元素总个数 limit集合元素索引的最大值 (索引表和嵌套表是不限个数的所以返回null变长数组返回定义时的最大索引 )first / last 返回集合第一个/最后一个元素的下标prior / next当前元素的前一个 / 后一个元素extend 增加元素,扩展集合的容量,不能用于索引表 x.extend 增加一个null元素 x.extend(n) 增加n个null元素 x.extend(n,i) 增加n个元素元素值与第i个元素相同 trim 从尾部删除, 被删元素不保留占位符不能用于索引表。 x.trim 删除一个元素 x.trim(n) 删除n个元素 delete 按索引删除集合元素, 被删元素保留占位符 delete 删除所有 delete(n) 删除第n个 delete(a,b) 删除a--b之间的所有元素
索引表
语法TYPE type_name IS TABLE OF element_type INDEX BY index_type; table_name type_name;描述type_name类型名称自定义即可。element_type集合中存放的元素类型如 number、char、varchar2也可以是整行记录index_type只能是整型或者字符串可选值有pls_integer, binary_integer or chartable_name索引表/集合变量名称自定义即可。
示例一
declare--查询员工姓名cursor ename_cusor isselect ename from emp where rownum 100;--定义[索引表]元素类型为员工姓名TYPE ename_type IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER;--定义索引表变量v_ename_index_table ename_type;--索引表定义为 index by pls_integer 时需要一个整数变量做计数器下标v_idx number : 0;
begin--循环游标将元素添加到索引表集合中for ename_row in ename_cusor loopv_idx : v_idx 1;v_ename_index_table(v_idx) : ename_row.ename;end loop;--索引表集合for循环--引用不存在的元素会报错ORA-01403: 未找到任何数据for i in v_ename_index_table.first .. v_ename_index_table.last LOOPdbms_output.put_line(i || || v_ename_index_table(i));end loop;
end;
示例二 declare--查询员工信息cursor emp_cusor isselect t.* from emp t where rownum 100;--定义[索引表]元素类型为员工表整行记录TYPE emp_type IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;--定义集合变量v_emp_index_table emp_type;--索引表定义为 index by pls_integer 时需要一个整数变量做计数器下标v_idx number : 0;
begin--循环游标将元素添加到索引表集合中for emp_row in emp_cusor loopv_idx : v_idx 1;v_emp_index_table(v_idx) : emp_row;end loop;--索引表集合for循环--引用不存在的元素会报错ORA-01403: 未找到任何数据for i in v_emp_index_table.first .. v_emp_index_table.last LOOPdbms_output.put_line(empno || v_emp_index_table(i).empno || ename || v_emp_index_table(i).ename || comm || v_emp_index_table(i).comm);end loop;
end;
嵌套表
语法TYPE type_name IS TABLE OF element_type; table_name type_name : type_name();描述type_name类型名称自定义即可。element_type集合中存放的元素类型如 number、char、varchar2也可以是整行记录table_name索引表/集合变量名称自定义即可。
1、和索引表区别: 嵌套表没有 index of其下标固定为整型 必须使用和其同名的构造器对其初始化 添加元素前必须先使用.extend分配存储空间; 2、嵌套表的构造器函数() 与嵌套表类型完全同名可以带参数也可以无参 带参数初始化table_name type_name : type_name(a,b,c,...); 不带参数初始化table_name type_name : type_name(); 示例一
declare--查询员工信息cursor emp_cusor isselect t.empno, t.ename, t.comm, t.sal from emp t where rownum 100;--定义[嵌套表]元素类型为员工表整行记录TYPE emp_type IS TABLE OF emp%ROWTYPE;--定义集合变量, 并初始化嵌套表使用和其同名的构造器对其初始化v_emp_index_table emp_type : emp_type();--集合元素下标(嵌套表下标只能是整数)v_idx number : 0;
begin--循环游标将元素添加到索引表集合中for emp_row in emp_cusor loop--集合添加元素前必须先调用它的extend方法给集合添加存储空间v_emp_index_table.extend;v_idx : v_idx 1;v_emp_index_table(v_idx).empno : emp_row.empno;v_emp_index_table(v_idx).ename : emp_row.ename;v_emp_index_table(v_idx).comm : emp_row.comm;end loop;--索引表集合for循环for i in 1 .. v_emp_index_table.count LOOPdbms_output.put_line(empno || v_emp_index_table(i).empno || ename || v_emp_index_table(i).ename || comm || v_emp_index_table(i).comm);end loop;
end;
自定义 split 函数
-- 创建类型(Type)——split_table_type
create or replace type split_table_type as table of varchar2(32676);
/
-- 创建函数—— split
create or replace function split(p_str clob, p_sep varchar2 : ,)
-- 根据匹配给定的分隔符来拆分字符串没有匹配时返回空对象。--参数 p_str: 待分割的字符串。--参数 p_sep: 分隔符不明确指定时默认为逗号。--返回字符串(p_str)被指定字符(p_sep)分割后的嵌套表类型/集合。return split_table_type is--定义集合变量, 并初始化嵌套表使用和其同名的构造器对其初始化v_split_table split_table_type : split_table_type();--集合元素下标(嵌套表下标只能是整数)v_idx number : 0;--分隔符找到的上一位置的索引v_sep_pre_idx number : 0;--分隔符找到的当前位置的索引v_sep_cur_idx number;
beginloopv_sep_cur_idx : instr(p_str, p_sep, v_sep_pre_idx 1);if v_sep_cur_idx 0 then--集合添加元素前必须先调用它的extend方法给嵌套表(集合)添加存储空间v_split_table.extend;v_idx : v_idx 1;--切割需要的目标元素(需要去掉分隔符本身)示例split(7369#7521#7900#9000,#)if v_sep_pre_idx 0 then v_split_table(v_idx) : substr(p_str, v_sep_pre_idx, v_sep_cur_idx - 1);else v_split_table(v_idx) : substr(p_str, v_sep_pre_idx length(p_sep), v_sep_cur_idx - (v_sep_pre_idx length(p_sep)));end if;--将上一位置切换到当前位置v_sep_pre_idx : v_sep_cur_idx;elsif v_sep_pre_idx 0 and v_sep_pre_idx length(p_sep) length(p_str) then--处理最后一个分隔符到内容结尾的部分(比如 1##2##3,1##2##3##)v_split_table.extend;v_idx : v_idx 1;v_split_table(v_idx) : substr(p_str, v_sep_pre_idx length(p_sep), length(p_str));exit;elseexit;end if;end loop;return v_split_table;
end;
/
--调用示例--
--这样查询时返回的原生的集合可视化效果不明显
SELECT split(1,223,32,56565) FROM dual T;
--table 函数用于专门读取集合内容列名默认为 column_value
select * from table(split(7369,7521,7900));
select * from table(split(,7369,7521,,7900,));
select * from table(split(7369,7521,7900,#));
select * from table(split(,#));--空结果
select * from table(split(#,#));--返回一个null
select column_value as empno from table(split(7369#7521#7900#9000,#));
select column_value as empno from table(split(#7369#7521#7900#9000#,#));
select * from emp t where t.empno in (select * from table(split(7369,7521,7900)));
select * from emp t where t.empno in (select column_value as empno from table(split(7369,7521,7900)));
存储函数返回多行多列结果集
1、存储过程存储函数返回单个结果时比较容易处理返回多行多列时则需要借助其它一些知识点比如引用游标以及本文的 Type 类型。
-- 创建类型(Type)对象指定每一行的多列信息相当于列。
create or replace type emp_qury_col_type is object
(empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate TIMESTAMP(6),sal NUMBER(7, 2),comm NUMBER(7, 2),deptno NUMBER(2)
);
/
-- 创建类型(Type)嵌套表元素为上面的对象相当于行。
create or replace type emp_qury_row_type is table of emp_qury_col_type;
/
create or replace function emp_qury_fun(pageNo in number : 1,pageSize in number : 20)--分页查询员工信息--参数 pageNo查询的页码从1开始默认为1--参数 pageSize每页的条数默认为20--返回查询的集合——多行多列return emp_qury_row_type is--定义集合变量, 并初始化嵌套表使用和其同名的构造器对其初始化v_emp_qury_row emp_qury_row_type : emp_qury_row_type();--集合元素下标(嵌套表下标只能是整数)v_idx number : 0;
begin--将查询的结果放入到嵌套表/集合中for row in (select t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.comm, t.deptnofrom (select rownum r, t1.* from emp t1) t where t.r between ((pageNo - 1) * pageSize 1) and pageNo * pageSize) loop--嵌套表集合添加元素前必须先调用它的extend方法给嵌套表(集合)添加存储空间v_emp_qury_row.extend;v_idx : v_idx 1;v_emp_qury_row(v_idx) : emp_qury_col_type(row.empno, row.ename, row.job, row.mgr, row.hiredate, row.sal, row.comm, row.deptno);end loop;return v_emp_qury_row;
end;
/
--调用示例使用table函数将函数返回的嵌套表转换成查询可以使用的目的表--
select T.* from table(emp_qury_fun(1,50)) T;
select T.* from table(emp_qury_fun(1,5)) T;
select T.* from table(emp_qury_fun(2,10)) T;
SELECT T.* FROM table(emp_qury_fun()) T;
变长数组
语法 TYPE type_name IS VARRAY(size) OF element_type; varray_name type_name : type_name(); 描述type_name类型名称自定义即可。size表示数组的最大长度一旦超过则报错ORA-06532下标超出限制element_type集合中存放的元素类型如 number、char、varchar2也可以是整行记录varray_name变长数组/集合变量名称自定义即可。
示例一
DECLARE--查询员工姓名CURSOR ename_cusor ISSELECT ename FROM emp WHERE ROWNUM 100;--定义[变长数组]元素类型为员工姓名TYPE ename_type IS VARRAY(100) OF emp.ename%TYPE;---定义集合变量, 并使用和其同名的构造器对其初始化v_ename_array ename_type : ename_type();--集合元素下标(变长数组下标只能是整数)v_idx NUMBER : 0;
BEGIN--循环游标将元素添加到索引表集合中FOR ename_row IN ename_cusor LOOPv_idx : v_idx 1;--集合添加元素前必须先调用它的extend方法给集合添加存储空间v_ename_array.EXTEND;v_ename_array(v_idx) : ename_row.ename;END LOOP;--索引表集合for循环--引用不存在的元素会报错ORA-01403: 未找到任何数据FOR i IN v_ename_array.FIRST .. v_ename_array.LAST LOOPDBMS_OUTPUT.PUT_LINE(i || || v_ename_array(i));END LOOP;
END;二维数组
1、Oracle 9i 开始可以创建多层集合相当于 Java 的二维数组。
declaretype varray_in is varray(6) of pls_integer; --内层集合元素是整型相当于列type varray_out is varray(4) of varray_in; --外层集合元素是变成数组相当于行--定义变量通过构造器函数初始化变长数组。v_varray_in varray_in : varray_in(1, 2, 3, 4, 5, 6);--外层集合赋值为内层集合v_varray_out varray_out : varray_out(v_varray_in);
begin--继续使用构造器赋值数据v_varray_out.extend;v_varray_out(2) : varray_in(5, 6, 7, 8, null, null);v_varray_out.extend;v_varray_out(3) : varray_in(9, 10, 11, 12, 13, null);--遍历二维数组for i in v_varray_out.first .. v_varray_out.last loopfor j in v_varray_in.first .. v_varray_in.last loopdbms_output.put_line([ || i || ][ || j || ] ||v_varray_out(i) (j));end loop;if i v_varray_out.count thendbms_output.new_line();end if;end loop;
end;
pipelined 管道
1、plsql 中使用 dbms_output 输出的信息需要等服务器执行完成后才能一次性返回给客户端。如果需要在客户端 实时 输出函数执行过程中的一些信息在 Oracle9i 以后可以使用管道函数。
2、管道函数为 并行执行。用关键字 pipelined 声明这是一个管道函数返回值类型必须为集合函数中 pipe row 语法被用来返回该集合的单个元素可以以一个空的 return 语句提前结束。
示例1
--自定义类型(嵌套表/集合)元素类型为字符串最长38位
create or replace type uuid_table_type is table of varchar2(38);
/
--自定义函数
create or replace function uuid_fun(p_size number :20)return uuid_table_type--生成指定个数的uuid值,小写。--参数 p_size每次生成的个数默认为 20个。--返回随机的uuid字符串多行单列。pipelined is
begin
for i in 1 .. p_size loop--uuid转为小写然后通过管道输出返回.pipe row(lower(sys_guid()));
end loop;
--经过测试return 写不写效果都一样。
return;
end;
/
--调用示例--
select t.* from table(uuid_fun()) t;
select t.* from table(uuid_fun(100)) t;
示例2dbms_output 输出日志不仅慢而且手动缓冲区的大小限制而采用管道则不仅快而已不受大小限制。
--自定义类型(嵌套表/集合)元素类型为字符串
create or replace type emp_init_data_log_type is table of varchar2(32767);
/
--自定义函数
create or replace function emp_init_data_fun(p_empno_start number :1,p_empno_end number :5000)return emp_init_data_log_type--生成初始化员工表的 insert SQL语句--参数 p_empno_start员工表主键开始的数值--参数 p_empno_end员工表主键结束的数值pipelined isv_sql varchar2(2048);pragma autonomous_transaction;
begin
for i in p_empno_start .. p_empno_end loopv_sql : INSERT INTO EMP VALUES ( || i || , SMITH || i || , CLERK, 7902,TO_DATE(17-08-1980, DD-MM-YYYY), 100 abs(mod(dbms_random.random,9999)) , NULL, 20);;--如果想在这里直接执行Insert语句而在 SELECT 语句中调用该函数则会报错ORA-14551:无法在查询中执行DML操作--因为 SELECT 语句无法更改数据库的状态一般来说此时推荐创建为存储过程而不是存储函数。pipe row(v_sql);
end loop;
--经过测试return 写不写效果都一样。
return;
end;
/
--调用示例--
select t.* from table(emp_init_data_fun(110)) t;
select t.* from table(emp_init_data_fun()) t; 自定义 split 函数
示例1管道Type(类型)实现其它语言中的 split 分割函数。
-- 创建类型(Type)——split_table_type
create or replace type split_table_type as table of varchar2(32676);
/
-- 创建函数—— split
create or replace function split(p_list clob, p_sep varchar2 : , )
-- 根据匹配给定的分隔符来拆分字符串没有匹配时返回空对象。
--参数 p_list: 待分割的字符串。
--参数 p_sep: 分隔符不明确指定时默认为逗号。
--返回字符串(p_list)被指定字符(p_sep)分割后的表类型/集合。return split_table_typepipelined isv_idx pls_integer;--直接使用 number 类型也可以 v_list varchar2(32676) : p_list;
beginloop--获取分隔符所在的位置v_idx : instr(v_list, p_sep);if v_idx 0 then--通过管道输出/返回截取的目标元素pipe row(substr(v_list, 1, v_idx - 1));--去掉截取过的内容保留未截取的内容用于下次循环继续截取v_list : substr(v_list, v_idx length(p_sep));elsif v_list ! p_list then--处理最后一个分隔符到内容结尾的部分(比如 1##2##3,1##2##3##)pipe row(v_list);exit;elseexit;end if;end loop;
end;
/
--调用示例--
--这样查询时返回的原生的集合可视化效果不明显
SELECT split(1,223,32,56565) FROM dual T;
--table 函数用于专门读取集合内容列名默认为 column_value
select * from table(split(7369,7521,7900));
select * from table(split(,7369,7521,,7900,));
select * from table(split(7369,7521,7900,#));
select * from table(split(,#));--空结果
select * from table(split(#,#));--返回一个null
select column_value as empno from table(split(7369#7521#7900#9000,#));
select column_value as empno from table(split(#7369#7521#7900#9000#,#));
select * from emp t where t.empno in (select * from table(split(7369,7521,7900)));
select * from emp t where t.empno in (select column_value as empno from table(split(7369,7521,7900)));
