oralce 集合比较 和常用方法

PL/SQL中没有数组的概念，他的集合数据类型和数组是相似的。在7.3以前的版本中只有一种集合，称为PL/SQL表，在这之后又有两种集合数据类型:嵌套表和varray。其中varray集合中的元素是有数量限制的，index_by表和嵌套表是没有这个限制的。index-by表是稀疏的，也就是说下标可以不连续，varray类型的集合则是紧密的，他的下标没有间隔。index_by表不能存储在数据库中，但是嵌套表和varray可以被存储在数据库中。
集合在使用时必须先使用type进行定义方可使用

1.index_by表
type type_name is table of element_type [NOT NULL] index by binary_integer

2.嵌套表
type type_name is table of element_type [NOT NULL]

3.varray
type type_name is [varray ¦varying array](max_size) of element_type[NOT NULL]

一，index_by表

TYPE TYPE1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

1.使用的时候需要先赋值后读取，至少也要先初期化一下，否则会出现异常：ORA-01403: no data found。
2.这种数组不需要事先指定上限，下标可以不连续，可以是0或负数。
例：v1 TYPE1;
v1(-1) := '-1';
v1(0) := '0';
v1(1) := '1';
DBMS_OUTPUT.put_line(v1(-1)); --访问合法
DBMS_OUTPUT.put_line(v1(2)); --访问非法

二，嵌套表

TYPE TYPE2 IS TABLE OF VARCHAR2(10);

1.必须进行初期化，否则会出现异常：ORA-06531: Reference to uninitialized collection
2.初期化方法：
v1 TYPE2 := TYPE2(); --声明时初期化数组为空
v2 TYPE2 := TYPE2('1','2','3','4','5'); --声明时初期化数组为5个元素
v1 := TYPE2(); --初期化后数组为空
v2 := TYPE2('1','2','3','4','5'); --初期化后数组为5个元素
3.数组元素的访问：
下标从1开始，不能超过数组所有元素的总和，当下标超出允许范围时，出现异常：ORA-06532: Subscript outside of limit
因为不能访问空数组，所以空数组的场合，必须进行数组扩展。
例：v1.EXTEND;
V1(1):= ‘1’; --访问合法
v1(2):= ‘2’; --访问非法，之前必须再次执行v1.EXTEND;
例：v2的下标范围是1～5。
v2(5):= ‘Hello’; --访问合法
DBMS_OUTPUT.put_line(v2(6)); --访问非法

三，Varray

TYPE TYPE3 IS ARRAY(5) OF VARCHAR2(10);
由于类型定义时的元素个数限制，所以TYPE3的变量在使用时最大的元素个数不能超过5个。
与嵌套表基本相同（略）

四，集合内建函数
集合还有很多内建函数，这些函数称为方法，调用方法的语法如下：
collection.method
下表中列出oracle中集合的方法
方法 描述 使用限制
COUNT 返回集合中元素的个数
DELETE 删除集合中所有元素
DELETE(x) 删除元素下标为x的元素，如果x为null,则集合保持不变 对VARRAY非法
DELETE(x,y) 删除元素下标从X到Y的元素，如果X>Y集合保持不变 对VARRAY非法
EXIST(x) 如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
EXTEND 在集合末尾添加一个元素 对Index_by非法
EXTEND(x) 在集合末尾添加x个元素 对Index_by非法
EXTEND(x,n) 在集合末尾添加元素n的x个副本 对Index_by非法
FIRST 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT。
LIMIT 返回VARRY集合的最大的元素个数，对于嵌套表和Index_by集合无用。
NEXT(x) 返回在元素x之后及紧挨着它的元素的值，如果该元素是最后一个元素，则返回null。
PRIOR(x) 返回集合中在元素x之前紧挨着它的元素的值，如果该元素是第一个元素，则返回null。
TRIM 从集合末端开始删除一个元素 对index_by不合法
TRIM(x) 从集合末端开始删除x个元素 对index_by不合法

oralce

oracle：

1、利用伪列号来查询中间列和后几列：

后几列：SELECT fkfs,(SELECT mc FROM  w_jsfs WHERE dm = t.fkfs) mc , COUNT(*) FROM y_khda t GROUP BY fkfs;

中间列：select * from (select rownum row_id ,wjm ,czsj ,czy FROM c_zwrz) where row_id between 5 and 9;

2、翻页的SQL语句的处理
语句一：
SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3; 
  
语句二：
SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;

3、
表达式：
数字表达式符号：+ - * / **
DECLARE
  result INTEGER;
BEGIN
     result := 10+3*4-20+5**2;
     dbms_output.put('运算结果：'||to_char(result));
     dbms_output.put_line('');
END;
字符表达式符号：||
关系表达式符号：< > = like in <= >= != between
逻辑表达式：not or and
函数：to_char to_date to_number
事务三个关键字：commit rollback savepoint
其savepoint的用法：
执行处理语句；
savepoint thispoint;
继续其他的处理语句；
rollback to thispoint;（回滚到定义thispoint处）
继续运行；

自治事务:
8i以上版本，不影响主事务。
在存储过程的is\as
后面声明PRAGMA AUTONOMOUS_TRANSACTION; 
自治事务防止嵌套提交，使事务在自己的事务区内提交或回滚不会影响其他的事务。

函数的一些用法
SELECT c.*,SYSDATE,upper(concat('liu','nihao')) a,
instr('liuqiuanyi','i',1,2) b,lpad('liu',8,'hi!') c,
rtrim('   liuquanyi    ',' ') d,TRIM('liu quan yi') e,
REPLACE('you love me!','you','i') AS f,
translate('you love meu!','you','???') AS f2,
TRANSLATE('fumblfe','uf','aa') f3,
last_day(sysdate) g,convert('liuquanyi','we8hp','f7dec') h
FROM dual c;

4、保留字
begin end; 
loop end loop;
if then elsif end if;
while;
for 变量 in 上边界..下边界;
declare;
:=; %type;into;type;
commit;rollback;
exception;
exception when others then;
is;as;

exit;exit then;

DECLARE
  num1 INTEGER := 0;
  i INTEGER /*:= 0*/;
BEGIN
     FOR i IN 1..10 LOOP
         num1 := num1 + 1;
     END LOOP;  
     dbms_output.put_line('结果：'||num1);
END; 

定义常变量：

[常/]变量名称 [constant/] 类型标识符【（长度）】 【not null】:= 值;

a 定义与数据库表字段相适应的变量：用%type;
变量名称 用户名.表.字段%type;(注意，必须到字段加%type)

b 定义记录类型变量：
[b1]、定义记录类型的数据类型
type 数据类型名 is record(
  变量名1 int，
  变量名2 date，
  变量名3 varchar(20)
)
[b2]、根据[1]来定义一个变量
变量名 数据类型名;
select * into 变量名 from 表 where 条件;
必须返回一行记录；（可以用rownum=1）

c 定义与数据库表行记录相适应的变量；用%rowtype(可以使用游标，方法一样)
变量名 表名%rowtype;
select * into 变量名 from 表名 where 条件;
必须返回一行记录；（可以用rownum=1）

d 定义一维表类型变量 (一维数组）
type 表类型 is table of 类型 index by binary_integer;
 表变量名 表类型;
表类型 为 类型的线性数组。用 pls_integer当溢出会出现错误。

DECLARE
  TYPE tabletype1 IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
  TYPE tabletype2 IS TABLE OF test_plsql.NAME%TYPE INDEX BY BINARY_INTEGER;
  t1 tabletype1;
  t2 tabletype2;
BEGIN
  t1(1) := '大学';
  t1(2) := '大专';
  t1(8) := '客户学习不拿';
  t2(1) := 88;
  t2(2) := 55;
  t2(3) := '送电否撒发';
  dbms_output.put_line(t1(1)||'--'||t1(8)||'--'||t2(1));
  dbms_output.put_line(t1(2)||'--'||t2(3)||'--'||t2(2));
END; 

e 定义多维表类型变量 （生成一个表类型，二维数组变量）
DECLARE
  TYPE tabletype1 IS TABLE OF test_plsql%ROWTYPE INDEX BY BINARY_INTEGER;
  t1 tabletype1;
BEGIN
  SELECT * INTO t1(9)
  FROM test_plsql
  WHERE id = 9;
  dbms_output.put_line(t1(9).id||'--'||t1(9).NAME||'--'||t1(9).curdate);
END;  
表类型变量的一些属性：count/delete/first/last/next/exists/prior.

DECLARE
  TYPE tabletype1 IS TABLE OF Varchar2(9) INDEX BY BINARY_INTEGER;
  t1 tabletype1;
BEGIN
  t1(1) := '成都市';
  t1(2) := '北京市';
  t1(3) := '青岛市';
  t1(4) := ' 222 ';
  t1(5) := ' 555 ';
  dbms_output.put_line('总记录：'||to_char(t1.COUNT));
  dbms_output.put_line('第一条记录'||t1.FIRST);
  dbms_output.put_line('最后一条记录'||t1.LAST);
  dbms_output.put_line('第二条的前一条记录'||t1.PRIOR(2));
  dbms_output.put_line('第二条的后一条记录'||t1.NEXT(2));

  dbms_output.put_line('第四条的前一条记录：'||t1.PRIOR(4));
  dbms_output.put_line('第四条的后一条记录：'||t1.NEXT(4));
END; 

根据游标来生成的记录类型变量：变量名 游标名%ROWTYPE;

5、
类型标识符
boolean;
number 数字型；
int pls_integer binary_integer（带符号）;
char 定长字符;
varchar2 （2000）long（2G）；
date；
 
系统包：dbms_output(系统输出包)

6、序列器
create sequence DLYX.SEQ_BDZBH
minvalue 1
maxvalue 99999
start with 56
increment by 1
cache 10;

create sequence user.sequencename
 increate by 1 start with 1
 maxuvalue 1.0E28 minvalue 1
 nocycle cache 20 noorder;

使用方法：SEQ_BDZBH.NEXTVAL

7、建立临时表
-- Create table
create global temporary table DLYX.C_KBTK_TEMP
(
  HH    VARCHAR2(10),
  JFDBH VARCHAR2(10),
  JFLX  VARCHAR2(4),
  TDL   NUMBER(10),
  TDF   NUMBER(12,2),
  DFID  VARCHAR2(15),
  DJM   VARCHAR2(5),
  DJJC  VARCHAR2(30),
  DJ    NUMBER(7,5)
)
on commit delete rows;

8、
create table 的表结构：
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

9、
对sql语言的解释
UPDATE a
 SET a.rec1 = (SELECT b.rec1 FROM b WHERE a.hh= b.hh)
  WHERE a.ny ='200512';
意义：表 a 的条件  a.ny ='200512'取出一行，然后根据SELECT b.rec1 FROM b WHERE a.hh= c.hh交流来 SET a.rec1 =的结果。循环修改。

SELECT * FROM a WHERE  
exists(SELECT * FROM b WHERE a.rec1 = b.rec1)
先检索一行a，再根据条件 SELECT * FROM b WHERE a.rec1 = b.rec1
来确定该行的处理。
循环列出。

10、
游标定义：cursor 游标名 is select 语句;
打开游标：open 游标名;(游标记录送入内存，并指向第一第一条记录)
取出游标：fetch 游标名 into 变量名1，变量名2，……
或者
fetch 游标名 into 记录型变量名;
关闭游标：close 游标名;
游标属性：%isopen 测试游标是否打开！如果没有打开就用fetch就提示错误！
%found  必须fetch运行过才能用%found;判断游标是否还有数据;
%notfound 该游标是否有数据，不论是否fetch过！
%rowcount 表示目前游标的位置，即具体数据行数;

-- Created on 2005-12-27 by LONGSHINE 
declare 
  field test_plsql.num%TYPE;
  CURSOR mycursor IS
         SELECT * FROM test_plsql
         WHERE id < 4;
  record1 mycursor%ROWTYPE;
BEGIN
     field := 800;
     OPEN mycursor;
/*     LOOP
     dbms_output.put_line('kaishi!');
     FETCH mycursor INTO record1;
     IF mycursor%FOUND THEN
        dbms_output.put_line('if!');
      --  FETCH mycursor INTO record1;
        dbms_output.put_line(to_char(record1.id));
     ELSE 
        dbms_output.put_line('没有记录了');
        EXIT;
     END IF;
     END LOOP;*/
     FETCH mycursor INTO record1;
     WHILE mycursor%FOUND LOOP
        dbms_output.put_line(to_char(record1.NAME));
        dbms_output.put_line(mycursor%ROWCOUNT);
        FETCH mycursor INTO record1;
 
 IF mycursor%NOTFOUND THEN
           dbms_output.put_line('nofound');
        ELSE
            dbms_output.put_line('have found');
        END IF;
     END LOOP;
     close mycursor;
end;

11、
过程：
create or replace procedure 过程名 as
 声明语句段；
begin
 执行语句段；
exception
 异常处理语句段；
end;

as代替declare;
不带参数，带参数;
create or replace procedure testPro
IS
  tempdate test_plsql.curdate%TYPE;
begin
  SELECT curdate INTO tempdate
  FROM test_plsql WHERE id = 1;
  dbms_output.put_line(to_char(tempdate));
end testPro;

create or replace procedure testPro2(
       rq in test_plsql.ID%TYPE,
       sj in out VARCHAR2) 
IS
  temp_sj VARCHAR2(50);
BEGIN
  BEGIN
    SELECT NAME INTO temp_sj
    FROM test_plsql
    WHERE ID = rq AND rownum = 1;
    EXCEPTION 
      WHEN NO_DATA_FOUND THEN
         --  temp_sj := NULL;
           sj :='发生错误！';
     -- RETURN;
  END;
  IF temp_sj IS NULL THEN
     temp_sj := 'noname';
  END IF;
  temp_sj := '原来：'||temp_sj;
  dbms_output.put_line(temp_sj);
  sj := temp_sj||'－》现在名称:'||sj;
end testPro2;

12、异常
a定义：
declare
   异常名 exception;
b触发异常处理
   raise 异常名;
c处理异常
exception
when 异常名1 then
   异常处理语句段1;
when 异常名2 then
   异常处理语句段2;
d例子：
DECLARE 
     error EXCEPTION;
     i INTEGER := 2;
BEGIN
     SELECT num INTO i
     FROM test_plsql 
     WHERE rownum = 1;
   
     IF i<10 THEN
        RAISE error;
     END IF;  
     EXCEPTION
       WHEN error THEN
        -- BEGIN
          dbms_output.put_line('error'||to_char(i));
        --RETURN;
        --END;
    dbms_output.put_line(i);
END;

－－可以使用RAISE_APPLICATION_ERROR来创建自己的错误处理！
常用的系统异常：
1、NO_DATA_FOUND
A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised
2、TOO_MANY_ROWS
A SELECT INTO statement returns more than one row.
3、DUP_VAL_ON_INDEX
Your program attempts to store duplicate values in a database column that is constrained by a unique index.

    BEGIN
    END LOOP;
    EXCEPTION
        WHEN no_data_found THEN
             dbms_output.put_line('在kh_cbkp没有客户编号！');
             RETURN;
        WHEN TOO_MANY_ROWS THEN
             dbms_output.put_line('在kh_cbkp有重复的客户编号！');
             RETURN;
        WHEN OTHERS THEN
             dbms_output.put_line('处理重复的客户编号发生异常！');
             RETURN;
    END;

用户定义的的异常处理　　

1
exception_init语句  允许为ORACLE错误命名  
调用格式：
 
pragma exception_init(<表达式>,);  
例  a 
declare  
  deadlock_detected exception;  
  pragma exception_init(deadlock_detected,-60);  
例 b
    ex_b_dlxx_null EXCEPTION;
    PRAGMA EXCEPTION_INIT(ex_b_dlxx_null,-1407);
    begin
      ***
    EXCEPTION
      WHEN ex_b_dlxx_null THEN
      ***
    end;

    2
    可以使用RAISE_APPLICATION_ERROR来创建自己的错误处理并立即抛出：
　　RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);
　　其中error_number是从-20000到-20999之间的参数；error_message是相应的提示信息，小于512字节。如：
　　
　　CREATE OR REPLACE PROCEDURE Register (
　　p_StudentID IN students.id%TYPE,
　　p_Department IN classes.department%TYPE,
　　p_Course IN classes.course%TYPE) AS
　　v_CurrentStudents NUMBER;　-- 班上学生的当前号
　　v_MaxStudents NUMBER;　　　-- 班上学生的最大号
　　
　　BEGIN
　　/* 找出学生的当前号和最大号 */
　　SELECT current_students, max_students
　　INTO v_CurrentStudents, v_MaxStudents
　　FROM classes
　　WHERE course = p_Course
　　AND department = p_Department;
　　
　　/* 确认另外的学生是否有足够的教室*/
　　IF v_CurrentStudents + 1 > v_MaxStudents THEN
　　RAISE_APPLICATION_ERROR(-20000, 'Can''t add more students to ' ||
　　p_Department || ' ' || p_Course);
　　END IF;
　　
　　/* 加一个学生在本班 */
　　ClassPackage.AddStudent(p_StudentID, p_Department, p_Course);
　　
　　EXCEPTION
　　WHEN NO_DATA_FOUND THEN
　　RAISE_APPLICATION_ERROR(-20001, p_Department || ' ' || p_Course ||
　　' doesn''t exist!');
　　END Register;