Oracle gets the desired data analysis from string resources

Oracle gets the desired data analysis from string resources
the
Requirements: order analysis, analyze the trend of various transaction data according to the game, account level, game account occupation, regional server, and price range.
Target: order table (order)
Processing analysis: The indications that can be read directly in the order include the game, regional server, and price. And there are already relevant configurations that can be associated with this class
information.
Problem Analysis 1: Account occupation, account level and other transaction information are not marked in detail in the order, and can only be obtained through other information
Pick.
the
Solution 1: Analyze the occupation and account level roughly through the title in the order.
The format of the title is as follows:


the
Processing idea: extract the occupation and account level from the title.
Profession:
substr
(www.2cto.com
o5.bizooffername,
instr(o5.bizoffername, ”【’,1,1)+1,
instr(o5.bizoffername,' ',1,1)-(instr(o5.bizoffername,'【',1,1)+1)
)
level:
substr
(
o5.bizooffername,
instr(o5. bizoffername, ”, ”, 1, 3)+1,
instr(o5.bizoffername,'level',1,1)-(instr(o5.bizoffername,' ',1,3)+1)
)
The processing result is shown in the figure:


the
Problem Analysis 2: Levels like ’50’ are extracted from strings and compared with account level interval dimensions as parameters
invalid characters occur.
Processing idea 2: Decisively think of converting characters into numbers, to_number.
Problem Analysis 3: Invalid characters will still be encountered after conversion to to_number.
Solution 3: There are other problems, so a custom function is_number is created.
the
CREATE OR REPLACE FUNCTION is_number(parameter VARCHAR2) RETURN NUMBER IS
val NUMBER; www.2cto.com
BEGIN
val := TO_NUMBER(NVL(parameter, 'a'));–The character a is copied if the parameter is empty
RETURN 1;
EXCEPTION–When encountering ’40’ such TO_NUMBER conversion is normal, when encountering ’40’, TO_NUMBER returns 0 abnormally,
WHEN OTHERS THEN
RETURN 0;
END;
the
Then I looked at the basic data processed with idea 1, and used where IS_NUMBER (level)=0, and found that there are indeed some few
When dealing with non-standard ‘Hello 20’ and similar level fields and number interval judgments, an error is reported, and the root cause is. So we had to rule out
These dozens of irregular data. Finally get the business needs:
the


the
the
Author Little People.