case语法 loop语法 集合类型nested tables

我的目的是取出

reply.send_person_id和reply所连接的messagebox表中,messagebox.send_person_id和messagebox_getperson_id与reply_send_person_id不同的列的值集合

和对应的reply.id的列的集合

然后循环update reply.getperson_id的值

declare
  type getpersons is table of number(19,0);
  type updatereplys is table of varchar2(255);
  getperson_ids getpersons;
  updatereply_ids updatereplys;
  begin
    select r.id,case r.send_reply_person_id when m.sendperson_id then m.getperson_id when m.getperson_id then m.sendperson_id end as selectid bulk collect into updatereply_ids,getperson_ids from messagebox m join reply r on m.id = r.messagebox_id;
     FOR i IN  updatereply_ids.FIRST ..  updatereply_ids.LAST LOOP  -- For first to last element
       DBMS_OUTPUT.PUT_LINE (updatereply_ids(i) ||  '   ' || getperson_ids(i));

      update reply r set r.get_reply_person_id = getperson_ids(i) where r.id=updatereply_ids(i);
      commit;
    END LOOP;
  end;

这里面涉及了一些知识

首先是case

select r.id,r.title, case r.send_reply_person_id when m.sendperson_id then m.getperson_id when m.getperson_id then m.sendperson_id end as selectid from messagebox m join reply r on m.id = r.messagebox_id;

咱们先看看文档

 

CASE expressions let you use IF ... THEN ...ELSE logic in SQL statements without having to invoke procedures. The syntax is:

Description of the illustration case_expression.gif

simple_case_expression::=

Description of the illustration simple_case_expression.gif

In a simpleCASE expression, Oracle Database searches for the first WHEN ...THEN pair for which expr is equal tocomparison_expr and returns return_expr. If none of the WHEN ...THEN pairs meet this condition, and an ELSE clause exists, then Oracle returnselse_expr. Otherwise, Oracle returns null. You cannot specify the literalNULL for every return_expr and theelse_expr.

发现找到的id是集合，于是我查了如何声明集合

发现有3种集合类型

PL/SQL has three kinds of collection types:

• Associative array (formerly called PL/SQL table orindex-by table)

• Variable-size array (varray)

• Nested table

 

开始我声明的是

Variable-size array (varray)

但是由于数组的大小无法确定(只能在begin end里面查询才能得到)

于是网上找到

If you don't want the limit, don't use varrays. They have limited functionality compared to nested table collection types anyway. I could never see much use for them.

参照https://forums.oracle.com/forums/thread.jspa?threadID=2166074

然后改用nested table

Nested Tables

In the database, a nested table isa column type that stores an unspecified number of rowsin no particular order. When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.

The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements.

An uninitialized nested table variable is a null collection. You must initialize it, either by making it empty or by assigning a non-NULL value to it. For details, see"Collection Constructors" and "Assigning Values to Collection Variables".

注意:nested table是一个类型，就相当于varchar2,integer等等一样,所有声明变量的时候没什么特别的,而且他能存储不确定多少数量的行

比如文档的列子

Example 5-5 Nested Table of Local Type

DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);  -- nested table type
 
  -- nested table variable initialized with constructor:
 
  names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

Roster就是定义的一个类型

而names则是一个变量，他的类型是定义的Roster

于是我照猫画虎定义了

declare
  type getpersons is table of number(19,0);
  type updatereplys is table of varchar2(255);
  getperson_ids getpersons;
  updatereply_ids updatereplys;

那怎么把查询出的值assign给变量呢？

 

于是我写成了

 begin
    select r.id,case r.send_reply_person_id when m.sendperson_id then m.getperson_id when m.getperson_id then m.sendperson_id end as selectid bulk collect into updatereply_ids,getperson_ids from messagebox m join reply r on m.id = r.messagebox_id;
       end;

最后就是如何循环了

 

Collection Methods

A collection method is a PL/SQL subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use and your applications easier to maintain. Table 5-2 summarizes the collection methods.

Note:

With a null collection, EXISTS is the only collection method that does not raise the predefined exception COLLECTION_IS_NULL.

Table 5-2 Collection Methods

Method	Type	Description
DELETE	Procedure	Deletes elements from collection.
TRIM	Procedure	Deletes elements from end of varray or nested table.
EXTEND	Procedure	Adds elements to end of varray or nested table.
EXISTS	Function	Returns TRUE if and only if specified element of varray or nested table exists.
FIRST	Function	Returns first index in collection.
LAST	Function	Returns last index in collection.
COUNT	Function	Returns number of elements in collection.
LIMIT	Function	Returns maximum number of elements that collection can have.
PRIOR	Function	Returns index that precedes specified index.
NEXT	Function	Returns index that succeeds specified index.

The basic syntax of a collection method invocation is:

collection_name.method

就写成了

FOR i IN  updatereply_ids.FIRST ..  updatereply_ids.LAST LOOP  -- For first to last element
            update reply r set r.get_reply_person_id = getperson_ids(i) where r.id=updatereply_ids(i);
      commit;
    END LOOP;

要有一个要注意的就是普通的select不能写到begin end里面，而要写成select into的语法形式

These are the PL/SQL static SQL statements, which have the same syntax as the corresponding SQL statements, except as noted:

• SELECT (this statement is also called a query)

  For the PL/SQL syntax, see "SELECT INTO Statement".