14-1:
	DECLARE
	  TYPE emp_record_type IS RECORD(
	    name emp.ename%TYPE,
	    salary emp.sal%TYPE,
	    title emp.job%TYPE);
	  emp_record emp_record_type;
	BEGIN
	  SELECT ename,sal,job INTO emp_record
	  FROM emp WHERE empno=&no;
	  dbms_output.put_line(''||emp_record.name);
	  dbms_output.put_line('λ'||emp_record.title);
	  dbms_output.put_line('ʣ'||emp_record.salary);
	END;
	/
14-2:
	DECLARE
	  dept_record dept%ROWTYPE;
	BEGIN
	  dept_record.deptno:=50;
	  dept_record.dname:='ADMINISTRATOR';
	  dept_record.loc:='BEIJING';
	  INSERT INTO dept VALUES dept_record;
	END;
	/
14-3:
	DECLARE
	  dept_record dept%ROWTYPE;
	BEGIN
	  dept_record.deptno:=60;
	  dept_record.dname:='SALES';
	  INSERT INTO dept (deptno,dname) VALUES
	   (dept_record.deptno,dept_record.dname);
	END;
	/
14-4:
	DECLARE
	  dept_record dept%ROWTYPE;
	BEGIN
	  dept_record.deptno:=30;
	  dept_record.dname:='SALES';
	  dept_record.loc:='SHANGHAI';
	  UPDATE dept SET ROW=dept_record WHERE deptno=30;
	END;
	/
14-5:
	DECLARE
	  dept_record dept%ROWTYPE;
	BEGIN
	  dept_record.loc:='GUANGZHOU';
	  UPDATE dept SET loc=dept_record.loc WHERE deptno=10;
	END;
	/
14-6:
	DECLARE
	  dept_record dept%ROWTYPE;
	BEGIN
	  dept_record.deptno:=50;
	  DELETE FROM dept WHERE deptno=dept_record.deptno;
	END;
	/
14-7:
	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(':'||ename_table(-1));
	END;
	/
14-8:
	DECLARE
	  TYPE area_table_type IS TABLE OF NUMBER
	    INDEX BY VARCHAR2(10);
	  area_table area_table_type;
	BEGIN
	  area_table('Japan'):=1;
	  area_table('China'):=2;
	  area_table('America'):=3;
	  area_table('England'):=4;
	  area_table('Portugal'):=4;
	  dbms_output.put_line('һԪ:'||area_table.first);
	  dbms_output.put_line('һԪ:'||area_table.last);
	END;
	/
14-9:
	DECLARE
	  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
	  ename_table ename_table_type;
	BEGIN
	  ename_table:=ename_table_type('MARY','MARY','MARY');
	  SELECT ename INTO ename_table(2) FROM emp
	    WHERE empno=&no;
	  dbms_output.put_line('Ա:'||ename_table(2));
	END;
	/
14-10:
	CREATE OR REPLACE TYPE phone_type IS TABLE OF VARCHAR2(20);
	/
	CREATE TABLE person(
	  id NUMBER(4),name VARCHAR2(10),sal NUMBER(6,2),
	  phone phone_type
	)NESTED TABLE phone STORE AS phone_table;
14-11:
	BEGIN
	  INSERT INTO person VALUES(1,'SCOTT',800,
	    phone_type('0471-3456788','13804711111'));
	END;
	/
14-12:
	DECLARE
	  phone_table phone_type;
	BEGIN
	  SELECT phone INTO phone_table
	  FROM person WHERE name='SCOTT';
	  FOR i IN 1..phone_table.COUNT LOOP
	    dbms_output.put_line(''||i||':'||phone_table(i));
	  END LOOP;
	END;
	/
14-13:
	DECLARE
	  phone_table phone_type:=phone_type('0471-3456788',
	    '13804711111','0471-2233066','13056278568');
	BEGIN
	  UPDATE person SET phone=phone_table
	    WHERE id=1;
	END;
	/
14-14:
	DECLARE
	  TYPE job_array_type IS VARRAY(20) OF emp.job%TYPE;
	  job_array job_array_type:=job_array_type('CLERK', 'CLERK');
	BEGIN
	  SELECT job INTO job_array(1) FROM emp
	    WHERE lower(ename)=lower('&name');
	  dbms_output.put_line('λ:'||job_array(1));
	END;
	/
14-15:
	CREATE TYPE phone_array IS VARRAY(20) OF VARCHAR2(20);
	/
	CREATE TABLE worker (
	  id NUMBER(4),name VARCHAR2(10),sal NUMBER(6,2),phone phone_array);
14-16:
	DECLARE
	  TYPE emp_table_type IS TABLE OF emp%ROWTYPE
	    INDEX BY BINARY_INTEGER;
	  emp_table emp_table_type;
	BEGIN
	  SELECT * INTO emp_table(1) FROM emp
	    WHERE empno=&no;
	  dbms_output.put_line(':'||emp_table(1).ename);
	  dbms_output.put_line(':'||emp_table(1).sal);
	  dbms_output.put_line('λ:'||emp_table(1).job);
	  dbms_output.put_line('ϸ:'||emp_table(1).hiredate);
	END;
	/
14-17:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(6);
	  id_table id_table_type:=id_table_type(1,2,5);
	  no INT;
	BEGIN
	  no:=&i;
	  IF id_table.EXISTS(no) THEN
	    dbms_output.put_line('Ԫֵ'||id_table(no));
	  ELSE
	    dbms_output.put_line('Ԫδʼ');
	  END IF;
	END;
	/
14-18:
	DECLARE
	  TYPE id_array_type IS VARRAY(20) OF NUMBER(6);
	  id_array id_array_type:=id_array_type(1,null,2,5);
	BEGIN
	  dbms_output.put_line('ID_ARRAYԪ'||id_array.count);
	END;
	/
14-19:
	DECLARE
	  TYPE id_array_type IS VARRAY(20) OF NUMBER(6);
	  id_array id_array_type:=id_array_type(1,null,2,5);
	BEGIN
	  dbms_output.put_line('ID_ARRAYԪ'||id_array.count);
	  dbms_output.put_line('ID_ARRAYԪظ'||id_array.limit);
	END;
	/
14-20:
	DECLARE
	  TYPE ename_table_type IS TABLE OF emp.ename%TYPE
	    INDEX BY BINARY_INTEGER;
	  ename_table ename_table_type;
	BEGIN
	  ename_table(-5):='SCOTT';
	  ename_table(1):='SMITH';
	  ename_table(5):='MARY';
	  ename_table(10):='BLAKE';
	  dbms_output.put_line('һԪصֵ:'
	   ||ename_table(ename_table.first));
	  dbms_output.put_line('һԪصֵ:'
	    ||ename_table(ename_table.last));
	END;
	/
14-21:
	DECLARE
	  TYPE ename_table_type IS TABLE OF emp.ename%TYPE
	    INDEX BY BINARY_INTEGER;
	  ename_table ename_table_type;
	BEGIN
	  ename_table(-5):='SCOTT';
	  ename_table(1):='SMITH';
	  ename_table(5):='MARY';
	  ename_table(10):='BLAKE';
	  dbms_output.put_line('Ԫ1ǰһԪֵ:'||
	    ename_table(ename_table.prior(1)));
	  dbms_output.put_line('Ԫ1ĺһԪ:'||
	    ename_table(ename_table.next(1)));
	END;
	/
14-22:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(6);
	  id_table id_table_type:=id_table_type(1);
	BEGIN
	  id_table.EXTEND(&no);
	  dbms_output.put_line('һԪأ'||id_table(id_table.first));
	  dbms_output.put_line('һԪأ'||nvl(to_char(id_table(id_table.last)),'null'));
	  dbms_output.put_line('Ԫܸ'||id_table.count);
	END;
	/
14-23:
	DECLARE
	  TYPE id_array_type IS VARRAY(30) OF CHAR;
	  id_array id_array_type:=id_array_type
	    ('A','B','C','D','E','F','G','H','I','J','K','L');
	BEGIN
	  id_array.TRIM(&no);
	  dbms_output.put_line('һԪأ'||id_array(id_array.first));
	  dbms_output.put_line('һԪأ'||id_array(id_array.last));
	  dbms_output.put_line('Ԫ'||id_array.count);
	END;
	/
14-24:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(6)
	    INDEX BY BINARY_INTEGER;
	  id_table id_table_type;
	BEGIN
	  FOR i IN 1..&no LOOP
	    id_table(i):=i;
	  END LOOP;
	  FOR j IN 1..id_table.count LOOP
	    IF mod(j,2)=0 THEN
	      id_table.delete(j);
	    END IF;
	  END LOOP;
	  dbms_output.put_line('Ԫ:'||id_table.COUNT);
	  dbms_output.put_line('һԪ:'||id_table(id_table.first));
	  dbms_output.put_line('һԪ:'||id_table(id_table.last));
	END;
	/
14-25:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(6) 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(10);
	  end_time NUMBER(10);
	BEGIN
	  FOR i IN 1..100000 LOOP
	    id_table(i):=i;
	    name_table(i):='Name'||to_char(i);
	  END LOOP;
	  start_time:=dbms_utility.get_time;
	  FOR i IN 1..id_table.COUNT LOOP
	    INSERT INTO demo VALUES(id_table(i),name_table(i));
	  END LOOP;
	  end_time:=dbms_utility.get_time;
	  dbms_output.put_line('ܼʱ():'||to_char((end_time-start_time)/100));
	END;
	/
14-26:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(6) 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(10);
	  end_time NUMBER(10);
	BEGIN
	  FOR i IN 1..100000 LOOP
	    id_table(i):=i;
	    name_table(i):='Name'||to_char(i);
	  END LOOP;
	  start_time:=dbms_utility.get_time;
	  FORALL i IN 1..id_table.COUNT
	     INSERT INTO demo VALUES(id_table(i),name_table(i));
	  end_time:=dbms_utility.get_time;
	  dbms_output.put_line('ܼʱ():'||to_char((end_time-start_time)/100));
	END;
	/
14-27:
	DECLARE
	  TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
	  emp_table emp_table_type;
	BEGIN
	  SELECT * BULK COLLECT INTO emp_table FROM emp WHERE deptno=&no;
	  FOR i IN 1..emp_table.COUNT LOOP
	    dbms_output.put_line(':'||emp_table(i).ename||
	      'λ:'||emp_table(i).job||
	      ':'||emp_table(i).sal);
	  END LOOP;
	END;
	/
14-28:
	DECLARE
	  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
	  TYPE sal_table_type IS TABLE OF emp.sal%TYPE;
	  ename_table ename_table_type;
	  sal_table sal_table_type;
	BEGIN
	  UPDATE emp SET sal=sal*1.1 WHERE deptno=&no
	    RETURNING ename,sal BULK COLLECT INTO ename_table,sal_table;
	  FOR i IN 1..ename_table.COUNT LOOP
	     dbms_output.put_line(':'||ename_table(i)||
	      '¹ʣ'||sal_table(i));
	  END LOOP;
	END;
	/
14-29:
	DECLARE
	  TYPE id_table_type IS TABLE OF demo.id%TYPE INDEX BY BINARY_INTEGER;
	  TYPE name_table_type IS TABLE OF demo.name%TYPE INDEX BY BINARY_INTEGER;
	  id_table id_table_type;
	  name_table name_table_type;
	BEGIN
	  FOR i IN 1..200000 LOOP
	    id_table(i):=i;
	    name_table(i):='Name'||to_char(i);
	  END LOOP;
	  FORALL i IN 1..id_table.COUNT
	     INSERT INTO demo VALUES(id_table(i),name_table(i));
	END;
	/
14-30:
	DECLARE
	  TYPE id_table_type IS TABLE OF demo.id%TYPE INDEX BY BINARY_INTEGER;
	  TYPE name_table_type IS TABLE OF demo.name%TYPE INDEX BY BINARY_INTEGER;
	  id_table id_table_type;
	  name_table name_table_type;
	BEGIN
	  FOR i IN 1..10000 LOOP
	    id_table(i):=i;
	    name_table(i):='N'||to_char(i);
	  END LOOP;
	  FORALL i IN 1..id_table.COUNT
	     UPDATE demo SET name=name_table(i) WHERE id=id_table(i);
	END;
	/
14-31:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
	  id_table id_table_type;
	BEGIN
	  FOR i IN 1..300 LOOP
	    id_table(i):=i;
	  END LOOP;
	  FORALL i IN 100..200
	     DELETE FROM demo WHERE id=id_table(i);
	END;
	/
14-32:
	DECLARE
	  TYPE deptno_table_type IS TABLE OF NUMBER(3);
	  deptno_table deptno_table_type:=deptno_table_type(10,20);
	BEGIN
	  FORALL i IN 1..deptno_table.COUNT
	     UPDATE emp SET sal=sal*1.1 WHERE deptno=deptno_table(i);
	  dbms_output.put_line('10'||sql%bulk_rowcount(1)||'');
	  dbms_output.put_line('20'||sql%bulk_rowcount(2)||'');
	END;
	/
14-33:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(6);
	  id_table id_table_type;
	BEGIN
	  id_table:=id_table_type(100,null,300,null,500);
	  FORALL i IN INDICES OF id_table
	    DELETE FROM demo WHERE id=id_table(i);
	  FOR i IN 1..id_table.count LOOP
	    IF sql%bulk_rowcount(i)<>0 THEN
	       dbms_output.put_line('ID='||id_table(i)||'бɾ');
	    END IF;
	  END LOOP;
	END;
	/
14-34:
	DECLARE
	  TYPE id_table_type IS TABLE OF demo.id%TYPE;
	  TYPE name_table_type IS TABLE OF demo.name%TYPE;
	  id_table id_table_type;
	  name_table name_table_type;
	  TYPE index_pointer_type IS TABLE OF PLS_INTEGER;
	  index_pointer index_pointer_type;
	BEGIN
	  SELECT * BULK COLLECT INTO id_table,name_table FROM demo;
	  index_pointer:=index_pointer_type(6,8,10);
	  FORALL i IN VALUES OF index_pointer
	    INSERT INTO new_demo VALUES(id_table(i),name_table(i));
	END;
	/
14-35:
	DECLARE
	  TYPE id_array_type IS VARRAY(40) OF NUMBER(6);
	  id1_array id_array_type:=id_array_type(1,2,3,4,5,6);
	  id2_array id_array_type:=id_array_type(0);
	BEGIN
	  id2_array:=id1_array;
	  dbms_output.put_line('ID2_ARRAYԪ'||id2_array.count);
	  dbms_output.put_line('ID2_ARRAYĵһԪأ'||id2_array(id2_array.first));
	  dbms_output.put_line('ID2_ARRAYһԪأ'||id2_array(id2_array.last));
	END;
	/
14-36:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(6);
	  id1_table id_table_type:=id_table_type(1,2,1,4,1,6);
	  id2_table id_table_type:=id_table_type(0);
	BEGIN
	  id2_table:=SET(id1_table);
	  dbms_output.put_line('ID2_tableԪ'||id2_table.count);
	  dbms_output.put_line('ID2_tableĵһԪأ'||id2_table(id2_table.first));
	  dbms_output.put_line('ID2_tableһԪأ'||id2_table(id2_table.last));
	END;
	/
14-37:
	DECLARE
	  TYPE nt_table_type IS TABLE OF NUMBER;
	  nt1 nt_table_type:=nt_table_type(1,2,3,4);
	  nt2 nt_table_type:=nt_table_type(3,4,5);
	BEGIN
	  nt2:=nt1 MULTISET UNION nt2;
	  dbms_output.put(':');
	  FOR i IN 1..nt2.count LOOP
	     dbms_output.put('  '||nt2(i));
	  END LOOP;
	  dbms_output.new_line;
	END;
	/
14-38:
	DECLARE
	  TYPE nt_table_type IS TABLE OF NUMBER;
	  nt1 nt_table_type:=nt_table_type(1,2,3,4);
	  nt2 nt_table_type:=nt_table_type(3,4,5);
	BEGIN
	  nt2:=nt1 MULTISET UNION DISTINCT nt2;
	  dbms_output.put(':');
	  FOR i IN 1..nt2.count LOOP
	    dbms_output.put('  '||nt2(i));
	  END LOOP;
	  dbms_output.new_line;
	END;
	/
14-39:
	DECLARE
	  TYPE nt_table_type IS TABLE OF NUMBER;
	  nt1 nt_table_type:=nt_table_type(1,2,3,4);
	  nt2 nt_table_type:=nt_table_type(3,4,5);
	BEGIN
	  nt2:=nt1 MULTISET INTERSECT nt2;
	  dbms_output.put(':');
	  FOR i IN 1..nt2.count LOOP
	    dbms_output.put('  '||nt2(i));
	  END LOOP;
	  dbms_output.new_line;
	END;
	/
14-40:
	DECLARE
	  TYPE nt_table_type IS TABLE OF NUMBER;
	  nt1 nt_table_type:=nt_table_type(1,2,3,4);
	  nt2 nt_table_type:=nt_table_type(3,4,5);
	BEGIN
	  nt2:=nt1 MULTISET EXCEPT nt2;
	  dbms_output.put(':');
	  FOR i IN 1..nt2.count LOOP
	    dbms_output.put('  '||nt2(i));
	  END LOOP;
	  dbms_output.new_line;
	END;
	/
14-41:
	DECLARE
	  TYPE id_array_type IS VARRAY(30) OF NUMBER(4);
	  TYPE id_table_type IS TABLE OF NUMBER(4);
	  id_array id_array_type:=id_array_type(1);
	  id_table id_table_type;
	BEGIN
	  IF id_array IS NULL THEN
	    dbms_output.put_line('ID_ARRAYδʼ');
	  ELSE
	    dbms_output.put_line('ID_ARRAYԪ'||id_array.COUNT);
	  END IF;
	  IF id_table IS NULL THEN
	    dbms_output.put_line('ID_TABLEδʼ');
	  ELSE
	    dbms_output.put_line('ID_TABLEԪ'||id_table.COUNT);
	  END IF;
	END;
	/
14-42:
	DECLARE
	  TYPE id_table_type IS TABLE OF NUMBER(4);
	  Id1_table id_table_type:= id_table_type(1);
	  Id2_table id_table_type:= id_table_type(1,2);
	BEGIN
	  IF id1_table=id2_table THEN
	    dbms_output.put_line('ID1_TABLE=ID2_TABLE');
	  ELSE
	    dbms_output.put_line('ID1_TABLE!=ID2_TABLE');
	  END IF;
	END;
	/
14-43:
	DECLARE
	  TYPE nt_table_type IS TABLE OF NUMBER;
	  nt1 nt_table_type:=nt_table_type(1,2,3,1);
	BEGIN
	  dbms_output.put_line('Ԫ:'||cardinality(nt1));
	END;
	/
14-44:
	DECLARE
	  TYPE nt_table_type IS TABLE OF NUMBER;
	  nt1 nt_table_type:=nt_table_type(1,2,3);
	  nt2 nt_table_type:=nt_table_type(1,2,3,4);
	BEGIN
	  IF nt1 SUBMULTISET OF nt2 THEN
	    dbms_output.put_line('nt1nt2Ӽ');
	  END IF;
	END;
	/
14-45:
	DECLARE
	  TYPE nt_table_type IS TABLE OF NUMBER;
	  nt1 nt_table_type:=nt_table_type(1,2,3,5);
	  v1 NUMBER:=&no;
	BEGIN
	  IF v1 MEMBER OF nt1 THEN
	    dbms_output.put_line(v1||'nt1Ԫ');
	  ELSE
	    dbms_output.put_line(v1||'nt1Ԫ');
	  END IF;
	END;
	/
14-46:
	DECLARE
	  TYPE nt_table_type IS TABLE OF NUMBER;
	  nt1 nt_table_type:=nt_table_type(1,2,3,5,&no);
	BEGIN
	  IF nt1 IS A SET  THEN
	    dbms_output.put_line('Ƕױnt1ظֵ');
	  ELSE
	    dbms_output.put_line('Ƕױnt1ظֵ');
	  END IF;
	END;
	/
14-47:
	DECLARE
	  TYPE id_array_type IS VARRAY(10) OF INT;
	  TYPE id_table_type IS TABLE OF id_array_type;
	  id_table id_table_type:=id_table_type(
	    id_array_type(58,100,102),
	    id_array_type(55,6,73),
	    id_array_type(2,4));
	BEGIN
	  FOR i IN 1..id_table.COUNT LOOP
	    FOR j IN 1..id_table(i).COUNT LOOP
	      dbms_output.put_line('id_table('||i||','||j||')='||id_table(i)(j));
	    END LOOP;
	  END LOOP;
	END;
	/
14-48:
	DECLARE
	  TYPE id_table_type IS TABLE OF INT;
	  TYPE id_array_type IS VARRAY(10) OF id_table_type;
	  id_array id_array_type:=id_array_type(
	      id_table_type(58,100,102),
	      id_table_type(55,6,73)
	  );
	BEGIN
	  FOR i IN 1..id_array.COUNT LOOP
	    FOR j IN 1..id_array(i).COUNT LOOP
	      dbms_output.put_line('id_array('||i||','||j||')='||id_array(i)(j));
	    END LOOP;
	  END LOOP;
	END;
	/
14-49:
	DECLARE
	  TYPE id_table_type IS TABLE OF INT INDEX BY BINARY_INTEGER;
	  id1_table id_table_type;
	  id2_table id_table_type;
	  TYPE id_array_type IS VARRAY(10) OF id_table_type;
	  id_array id_array_type;
	  j INT;
	BEGIN
	  id1_table(-8):=1; id1_table(-2):=2;
	  id1_table(3):=3; id1_table(8):=4;
	  id2_table(-100):=5; id2_table(-15):=6;
	  id2_table(15):=7; id2_table(34):=8;
	  id_array:=id_array_type(id1_table,id2_table);
	  FOR i IN 1..id_array.COUNT LOOP
	    j:=id_array(i).first;
	    LOOP
	      dbms_output.put_line('id_array('||i||','||j||')='||id_array(i)(j));
	      j:=id_array(i).NEXT(j);
	      EXIT WHEN j IS NULL;
	    END LOOP;
	  END LOOP;
	END;
	/














































