Oracle multiple result set stitching string; where ID in string

Transferred from: http://blog.sina.com.cn/s/blog_af26e333010194ht.html

A recent modification of an Oracle trigger encountered two problems in the process:

Select LastName from Hrmresource where ID in (waigaunyanshourens);

This SQL as long as the function: according to the ID to the Human Resources table to find the appropriate names;

Here Waigaunyanshourens is a storage appearance acceptance person variable, the original is only a storage appearance of the ID variable, the interface requires the appearance of the acceptance of the person can choose more, such as (41,42,43); Now the question is:

(1.) A SQL cannot receive multiple result sets;

(2.) I also returned a string of people's names, such as (name A, person B, person C)

At first I thought it was quite simple, using a cursor to handle it. But after many tests, it's hard to find a solution.

The first way of thinking:

To define a cursor:

Cursor Cur_wgys is a select LastName from Hrmresource where ID in (waigaunyanshourens);

C_WG Cur_wgys%rowtype;

Using cursors:

For C_WG in Cur_wgys loop
Dbms_output.put_line (c_wg.lastname);
waiguanstr: = Waiguanstr | | ', ' | | c_wg.lastname;
End Loop;
Dbms_output.put_line (LTrim (Waiguanstr, ', '));

As a whole, the two problems are solved, and there is no problem when executing the trigger, but the trigger will

Cursor Cur_wgys is a select LastName from Hrmresource where ID in (waigaunyanshourens);

The error of "Invalid number";

The second way of thinking: because the first way of thinking always do not know what reason, but also find another way;

Select LTrim (Max (Sys_connect_by_path (LastName, ', ')), ', ')

From (select LastName, RowNum t from Hrmresource where ID in (waigaunyanshourens) connect by Prior t = T-1 start with t = 1;

This method is relatively concise, does not need a cursor, can receive multiple result sets directly, and automatically stitching the result set into a comma-separated string;

It is no problem to use this kind of distribution to understand normally, but also in the second line, the "invalid number" is reported.

In two ways: to feel that the two methods are correct, but still where the details are not noticed, resulting in an error;

Find out why: ID type is integer, and this waigaunyanshourens (41,42,43) is a string;

Now the problem also find out, also tried various methods, finally finally solved, on this a toss, in this record, a total reference!!!

The first solution:

To define a cursor:

Cursor Cur_wgys is a select LastName from Hrmresource where

INSTR ( (select ', ' | | Waigaunyanshouren | | ', ' from formtable_main_112 where RequestID =: new.requestid), ', ' | | TRIM (To_char (ID)) | | ', ' ) > 0;

C_WG Cur_wgys%rowtype;

Using cursors:

For C_WG in Cur_wgys loop
Dbms_output.put_line (c_wg.lastname);
waiguanstr: = Waiguanstr | | ', ' | | c_wg.lastname;
end Loop;

Dbms_output.put_line (LTrim (Waiguanstr, ', '));

Output Result:

Name a

Name B

Name C

Name A, person B, name C

Second workaround:

Select LTrim (Max (Sys_connect_by_path (LastName, ', ')), ', ') into Waigaunyanshourens
From (select LastName, RowNum t from Hrmresource where INSTR ( (select ', ' | | Waigaunyanshouren | | ', ' from formtable_main_112 where RequestID =: New.requestid), ', ' | | TRIM (To_char (ID)) | | ', ' ) > 0)
Connect by Prior t = t-1 start with t = 1;

dbms_output.put_line (waigaunyanshourens| | ' ============== ');

Output: Name A, person B, name c==============

This solves the problem of where ID in (string)

The key is where the sentence, seemingly a very long and chaotic, but it is a similar like, but definitely more safe and efficient than the kind;

The problem solves the mood to be comfortable, summarizes here. I hope I can give some help to my friends who have met the same problem.

Oracle multiple result set stitching string; where ID in string (GO)