oracle的集合類型

标量變量

用于存放單行單列的資料

---複合資料類型

PL/SQL記錄

處理單行多列資料

PL/SQL集合

(1)索引表(Associative arrays, also known as index-by tables)

(2)嵌套表：一種用于處理PL/SQL數組的資料類型。嵌套表的元素下标從1開始，并且元素個數沒有限制。

(3)變長數組

用于處理多行單列資料

(4)PL/SQL記錄表

處理多行多列資料

1.pl/sql記錄

TYPE type_name IS RECORD(

field_declaration1[,field_declaration2]...

);

identifier type_name;

type_name:記錄類型的名稱(IS RECORD 表示記錄類型)

field_declaration：記錄成員的定義，多個記錄成員之間用逗号(,)隔開.

identifier:指定記錄變量名

方法一：

type emp_record_type is record(

names emp.ename%type,

salary emp.sal%type,

dno emp.deptno%type

);

emp_record是基于記錄類型emp_record_type所定義的記錄變量

emp_record emp_record_type;

方法2：

%rowtype屬性可以基于表或者視圖定義記錄變量

identified table_name%rowtype;

identified view_name%rowtype;

dept_record dept%rowtype;

擷取雇員姓名 ,薪水,部門号

set serveroutput on;

declare

type emp_record_type is record(

names emp.ename%type,

salary emp.sal%type,

dno emp.deptno%type

);

emp_record emp_record_type;

begin

---select ename,sal,deptno into emp_record from emp where empno=&no;

select ename,sal,deptno into emp_record.names,emp_record.salary,emp_record.dno from emp where empno=&no;

dbms_output.put_line('names: '||emp_record.names);

dbms_output.put_line('salary: '||emp_record.salary);

dbms_output.put_line('dno: '||emp_record.dno);

end;

在insert 的values字句中使用記錄變量/記錄成員，向dept表中插入一條資料

set serveroutput on;

declare

dept_record dept%rowtype;

begin

dept_record.deptno :=&no;

dept_record.dname := '&names';

dept_record.loc :='&city';

---insert into dept(deptno,dname,loc) values(dept_record.deptno,dept_record.dname,dept_record.loc);

insert into dept values dept_record;

end;

2.嵌套表(nested table)

嵌套表一種用于處理PL/SQL數組的資料類型。嵌套表的元素下标從1開始，并且元素個數沒有限制。

文法：

table type_name is table of element_type;

identifier type_name;

type_name指嵌套表的類型名。

element_type指嵌套表元素的資料類型。

identifier指嵌套表變量。

使用嵌套表元素時，需要先使用構造方法初始化嵌套表

set serveroutput on;

declare

type ename_table_type is table of emp.ename%type;

ename_table ename_table_type;

begin

ename_table :=ename_table_type('y1','y2');

select ename into ename_table(1) from emp where empno=&no;

dbms_output.put_line('employee name: ' || ename_table(1));

dbms_output.put_line('employee name: ' || ename_table(2));

end;

定義一個表 ename_table初始化有兩行資料y1,y2 把y1的值改為&no的ename

y2沒有改輸出時還顯示y2，此表隻能插入兩行資料

3.集合方法

COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST, LIMIT, NEXT, PRIOR, and TRIM

4.批量綁定

forall文法

forall index in lower_bound..upper_bound

sql_statement;

index隐含定義的整形變量(作為集合元素下标使用)

lower_bound|upper_bound集合元素的上界和下界。

forall批量插入

create table tmp01(id numvber primary key,name varchar2(50));

declare

type id_table_type is table of number index by binary_integer;

type name_table_type is table of varchar2(10) index by binary_integer;

id_table id_table_type;

name_table name_table_type;

start_time number;

end_time number;

begin

for i in 1..5000 loop

id_table(i):=i;

name_table(i):='name'||i;

end loop;

start_time:=DBMS_UTILITY.GET_TIME;

forall i in 1..id_table.count

insert into tmp01 values(id_table(i),name_table(i));

end_time:=dbms_utility.get_time;

commit;

dbms_output.put_line('total time is '||to_char((end_time-start_time)/100));

end;

for循環單行插入

truncate table tmp01 ;

declare

type id_table_type is table of number index by binary_integer;

type name_table_type is table of varchar2(10) index by binary_integer;

id_table id_table_type;

name_table name_table_type;

start_time number;

end_time number;

begin

for i in 1..5000 loop

id_table(i):=i;

name_table(i):='name'||i;

end loop;

start_time:=DBMS_UTILITY.GET_TIME;

for i in 1..5000 loop

insert into tmp01 values(id_table(i),name_table(i));

end loop;

commit;

end_time:=dbms_utility.get_time;

dbms_output.put_line('total time is '||to_char((end_time-start_time)/100));

end;

5.索引表

---文法

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;

indentifer type_name;

type_name指定使用者自定義資料類型名稱

element_type 指定索引表元素的資料類型

key_type指定索引表元素下标的資料類型(varchar2,binary_integer,pls_integer等)

indentifer指定索引表變量

索引表的下标可以為負值，而且元素個數沒有限制。

set serveroutput on;

declare

type ename_table_type is table of emp.ename%type index by binary_integer;

ename_table ename_table_type;

begin

select ename into ename_table(1) from emp where empno=&no;

dbms_output.put_line('employee name : ' || ename_table(1));

end;

set serveroutput on;

declare

type city_table_type is table of number index by varchar2(10);

city_table city_table_type;

begin

city_table('zhouzq'):=1;

city_table('lidd'):=2;

city_table('lijian'):=3;

dbms_output.put_line('the first element: ' || city_table.first);

dbms_output.put_line('the last element: ' || city_table.last);

end;

-- 定義記錄集

TYPE yang_rec IS RECORD( ename varchar2(30), eid NUMBER );

-- 定義索引表類型

TYPE yang_tab IS TABLE OF yang_rec INDEX BY BINARY_INTEGER;

-- 定義索引表對象的執行個體

test_tab yang_tab;

對索引表中進行操作

1) 插入：見上例。

2) 引用：

IF emps.EXIST(10) THEN

DBMS_OUTPUT.PUT_LINE('存在第10條記錄。');

END IF;

3) 修改：

修改emps 表中的第100個條目：

emps(100).emp_name := 'yang linker';

4) 删除：

-- 删除emps 表中的第100個條目：

emps.DELETE(100);

-- 删除emps 表中的從1到100的條目：

emps.DELETE(1, 100);

-- 删除emps 表中的的所有條目：

emps.DELETE;

索引表中的函數

1) count：傳回表的條目數量：

num_rows := emps.COUNT;

2) EXISTS：如果指定的條目存在，則傳回為真；否則為假。

IF emps.EXIST(10) THEN

DBMS_OUTPUT.PUT_LINE('存在第10條記錄。');

END IF;

3) LIMIT：該方法傳回集合可以包含的最大元素數目。隻有變長數組才有上限。将LIMIT 用于嵌套表和索引

表時，其傳回為NULL。

4) FRIST：該方法傳回集合中使用的最小的索引值。

5) LAST：該方法傳回集合中使用的最大的索引值。

6) NEXT：該方法傳回集合中目前使用的下一個索引值。

7) PRIOR：該方法傳回集合中目前使用的上一個索引值。

8) DELETE：删除集合中的條目，見前例。

9) TRIM：從集合的尾部删除一個或多個條目，無傳回值，隻适用于變長數組和嵌套表。

emps.TRIM(1); -- 從集合的尾部删除一個條目

emps.TRIM(3); -- 從集合的尾部删除三個條目

10) EXTEND：在集合的尾部添加條目或複制已有的條目，隻适用于變長數組和嵌套表。

emps.EXTEND(1); -- 從集合的尾部添加一個條目

emps.EXTEND(3); -- 從集合的尾部添加三個條目

emps.EXTEND(1, 3);-- 複制集合的第三個條目，并将其添加到表的末尾。

6.遊标

使用顯示遊标包括定義遊标，打開遊标，提取遊标和關閉遊标。

(1)定義遊标

CURSOR cursor_name IS select_statement;

(2)打開遊标

OPEN cursor_name;

(3)提取遊标

---提取一行

FETCH cursor_name into var1,var2...;

---提取多行

FETCH cursor_name BULK COLLECT INTO collect1,collect2...[LIMIT rows];

---collect1用于指定接受遊标的集合變量

(4)關閉遊标

CLOSE cursor_name;

顯示遊标屬性：%isopen,%found,%notfound,%rowcount

---顯示遊标中使用fetch...into

set serveroutput on;

declare

cursor emp_cursor is select ename,sal from emp where deptno=10;

v_name emp.ename%type;

v_salary emp.sal%type;

begin

open emp_cursor;

fetch emp_cursor into v_name,v_salary;

for ename,sal in v_name,v_salary

loop

dbms_output.put_line('ename: '||ename);

dbms_output.put_line('salary: '||sal );

end loop;

close emp_sursor;

end;

set serveroutput on;

declare

cursor emp_cursor is select ename,sal from emp where deptno=10;

v_ename emp.ename%type;

v_salary emp.sal%type;

begin

open emp_cursor;

loop

fetch emp_cursor into v_ename,v_salary;

exit when emp_cursor%notfound;

dbms_output.put_line(v_ename || ' : ' || v_salary);

end loop;

close ename_cursor;

end;

顯示遊标中使用fetch...bulk collect into

###顯示部門10的所有雇員名

set serveroutput on;

declare

type ename_table_type is table of emp.ename%type;

ename_table ename_table_type;

cursor ename_cursor is select ename from emp where deptno=10;

begin

open ename_cursor;

fetch ename_cursor bulk collect into ename_table;

for i in 1..ename_table.count

loop

dbms_output.put_line(i||':'||ename_table(i));

end loop;

close ename_cursor;

set serveroutput on;

declare

cursor ename_cursor is select ename from emp where deptno=10;

begin

open ename_cursor;

fetch ename_cursor bulk collect into ename_table;

for i in 1..ename_table.count

loop

dbms_output.put_line(i||':'||ename_table(i));

end loop;

close ename_cursor;

end;

---遊标FOR循環

文法：

FOR record_name IN cursor_name loop

statement1;...

end loop;

cursor_name:已定義的遊标名稱

record_name:隐含定義的記錄變量名

在執行循環體内容之前，oracle會隐含地打開遊标，并且每循環一次提取一次資料，提取完所有資料後，會自動退出循環并隐含關閉遊标。

---FOR循環遊标

set serveroutput on;

declare

cursor ename_cursor is select ename from emp;

begin

for cc_record in ename_cursor loop

dbms_output.put_line(ename_cursor%rowcount ||'： ' || cc_record.ename);

end loop;

end;

---遊标FOR循環中直接使用子查詢

set serveroutput on;

begin

for cc_record in (select ename from emp where deptno=10) loop

DBMS_OUTPUT.PUT_LINE(cc_record.ename);

end loop;

end;

原文連結：https://blog.csdn.net/weixin_33755554/article/details/92117596

oracle的集合類型

繼續閱讀

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Cloud Studio初體驗

使用 ctypes 進行 Python 和 C 的混合程式設計

【python】【資料處理】畫多元資料分布圖

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

【python】netconf協定對接管理裝置

「Python 網絡自動化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 網絡裝置

在python中建立excel并寫入