Oracle's knowledge about cursors

A: Preface

Today, I write the cursor for the second time, I wipe, I think I need a great improvement in the database. There are three problems today, the first is the problem of data splitting in Oracle database, which I use REGEXP_SUBSTR to solve, the second problem is regexp_substr inside parameters, I did not successfully replace with the SELECT statement (I do not know now). And the third one is when I finish writing the cursor and I don't know how to say it. Gets the value returned. O Heavens, o earth.

The following detailed description;

Two:

1. Regexp_substr function

Regexp_substr (String, pattern, position, occurrence, modifier):

The first parameter: a string that needs to be matched;

The second parameter: a regular expression to match;

The third argument: the starting position, starting with the first few characters to match the regular expression. (Default is 1)

The fourth parameter: Indicates the first number of matches. The default is also 1;

The Fifth parameter: pattern ("I" indicates case-insensitive retrieval, "C" is case-sensitive for retrieval.) The default is "C");

　　　　

Then we get the results we want to find out:

、

The above split says this, basically is divert bar.

2, that's what I want to say. I wanted to rewrite the first parameter string with a SELECT statement, but it didn't overwrite successfully.

SelectREGEXP_SUBSTR ('3w,3x,5,57,58,0','[^,]+',1, Level,'I') as Str  fromDual Connect by  Level <=Length'3w,3x,5,57,58,0')-Length (Regexp_replace ('3w,3x,5,57,58,0',',',"'))+1;

The result failed. I hope whoever succeeds, tell me.

3, that is, I now get the results I want, we based on this split data in a circular query to get the data we want, I get the loop of data serialized together as a string return.

So here I'm going to use cursors to loop.

First give the wrong code, then I just wrote a cursor, I do not know how to return to create or replace function get_bclb (str inch varchar2, V_group_code in varchar2)

Create or Replace functionGET_BCLB (Str inch varchar2, V_group_codeinch varchar2)return varchar2  isV_namesvarchar2(4000);DeclareV_code Pf_common_config.code%Type;nvarchar2( -); CURSORC_cursor is   SelectREGEXP_SUBSTR (Str,'[^,]+',1, Level,'I') as Str  fromDual Connect by  Level <=LengthStr)-Length (Regexp_replace (Str,',',"'))+1;begin      OpenC_cursor; LoopFetchC_cursor intoV_code; Exit  whenC_cursor%NOTFOUND; SelectC.name intoN fromPf_common_config CwhereC.code=V_code andC.group_code= 'BCLB'; --Dbms_output. Put_Line (' converted data: ' | | v_code| | ' = ' | | n);V_names:=V_names||N||','; EndLoop; CloseC_cursor; --Dbms_output. Put_Line (' converted data: ' | | V_names);    returnV_names; EndGET_BCLB;

The last code to modify the success is:

Create or Replace functionGET_BCLB (Str inch varchar2, V_group_codeinch varchar2)return varchar2  isV_namesvarchar2(4000); nvarchar2( -); V_codevarchar2( -); CURSORC_cursor isSelectREGEXP_SUBSTR (Str,'[^,]+',1, Level,'I') as Str  fromDualconnect by  Level <=LengthStr)-Length (Regexp_replace (Str,',',"'))+1;begin     OpenC_cursor; LoopFetchC_cursor intoV_code; Exit  whenC_cursor%NOTFOUND; SelectC.name intoN fromPf_common_config CwhereC.code=V_code andC.group_code=V_group_code; --Dbms_output. Put_Line (' converted data: ' | | v_code| | ' = ' | | n);V_names:=V_names||N||','; EndLoop; CloseC_cursor; --Dbms_output. Put_Line (' converted data: ' | | V_names);   returnV_names;EndGET_BCLB;

A few days there is a function: In the calculation of age is used, I went to check the next. Trunc ((SYSDATE-CSRQ)/365,0) as age, where the table is rounded, if Trunc ((SYSDATE-CSRQ)/365,2) represents two decimal places.

Three: oneself now in the internship, every day although do not have a lot of things, I am now in the report, but I still feel that they are yards nong Ah, this is not what I want to do, I want to learn a lot of my own knowledge, and not just in this day knock code. Try to refuel. Learn and learn to learn Android recently. Go! Go! Go!