创表
CREATE TABLE "SYSTEM"."COURSE"
( "CNO" NUMBER(*,0),
"CNAME" VARCHAR2(6 BYTE),
"TNAME" VARCHAR2(8 BYTE)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
表结构和数据
cno cname tname
2 artist guojun
3 music aojun
4 exsise yazhou
5 meishu jiajia
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
TYPE arry_var IS VARRAY(4) OF VARCHAR2(50); /*定义数组（VARRAY(4)表示动态数组，即数组中的索引值可以根据实际需求任意调整，此处索引长度为4，代表输出4个结果）
arry_name arry_var; /*定义数组名*/
begin
select cname bulk collect into arry_name from course; /*获取到结果放在数组中*/
for i in 1..arry_name.count loop /*遍历出结果*/
dbms_output.put_line(arry_name(i));
end loop;
end;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
TYPE t_table IS TABLE OF VARCHAR2(30) INDEX BY binary_INTEGER; /*定义字符类型数组*/
v_table t_table;
v_cnt NUMBER;
BEGIN
select cname bulk collect into v_table from course;
v_cnt := v_table.COUNT; /*计算出数组长度并赋值*/
FOR i IN 1 .. v_cnt LOOP
dbms_output.put_line(v_table(i));
END LOOP;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from course;
declare
type shuzu is table of varchar2(10) index by binary_integer; /*字符类型数组*/
sz shuzu;
begin
sz(1):=1;
sz(2):=2;
sz(3):=4;
for i in 1..sz.count loop
DBMS_OUTPUT.PUT_LINE (sz(i));
end loop;
end;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE /*记录是多维数组，我们需要多少个维度的数据，就可定义多少个记录变量，此处定义了两个为维度，user_cno和user_cno*/
-- only 2 fileds /*记录类型数组*/
TYPE t_record_user IS RECORD(
user_cno course.cno%type,
user_cno course.cname%type);
TYPE t_user IS TABLE OF t_record_user INDEX BY BINARY_INTEGER;
v_arry_user t_user;
BEGIN
SELECT cno, cname BULK COLLECT INTO v_arry_user FROM course;
FOR i IN 1 .. v_arry_user.COUNT LOOP
dbms_output.put_line(v_arry_user(i).user_cname||‘ ‘||v_arry_user(i).user_cno);
END LOOP;
END;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*宽表*/
DECLARE
type kuanb is table of course%ROWTYPE index by binary_integer; /*宽表类型数组*/
BB kuanb;
BEGIN
select * bulk collect into BB from course ;
for i in 1..BB.count loop
dbms_output.put_line(BB(i).cno||‘ ‘||BB(i).cname||‘ ‘||BB(i).tname);
end loop;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------