Oracle connect by notes

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
A colleague asked me a question today, not the query tables and views in any circumstances, how to put the 12 month of the year month out, but also need to have a column as a number, like in such:

I told him, Oracle is extremely powerful, almost do not do things. The words out, encounter problems not easily retreat? I'm not a professional to do Oracle development, is not only in favor of DBA, Oracle, technology is also very rough, but has a benefit, like thinking, believe that Oracle can fulfill my needs.
After some thought, no solution, had to resort to the valley teacher, found a knowledge point, before the less contact, that is the syntax of connect by, the above requirements can use this syntax to accomplish:

select lpad(level,2,0) , to_number(lpad(level,2,0))||'Month'   from dual connect by level<13
Note: 1, lpad, this is not a tablet computer, but "left filled", level is the Oracle keyword, is dealing with the source, 2 said after the filling length, 0 said the fill character, lpad (level, 2,0) said the two, if level is less than two bits in the level left with 0 fill, if more than two, will start on the left two.
2, Level is the Oracle keyword query, said the depth, combined with the use of connect by
Well, this needs met, and then we explore the usage of connect by.
CONNECT BY hierarchical query clause, generally used for tree or hierarchical result sets of queries, the grammatical structure:

[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition

Start width is the beginning of traversal of the location, such as empno is null, is also from empno is the record null to start the cycle.
Connect width condition is relational operations, such as empno = Mgr
The user Scott EMP as an example:

select rpad(' ', 2 * (level - 1), '-') || ename as username,
       connect_by_root ename "ROOT",
       connect_by_isleaf "isleaf",
       level,
       sys_connect_by_path(ename, '/')
  from emp
 start with mgr is null
CONNECT BY PRIOR empno = mgr

Results as shown in Fig.:

Explain:
1, Connect_by_root is found in the root node, grammar structure for the connect_by_root field_name [alias], note the alias is usedDouble quotation marksThe single quotation mark cannot be,.
2, The connect_by_isleaf determines whether it is a leaf node, value is divided into 0 (not) and 1 (is), the grammatical structure of connect_by_isleaf [alias], ibid, alias use double quotes.
3, Sys_connect_by_path is a function, lists the current record path, of course contents of path can own definition, you can use the ID or name, the path connector, but also of your own definition. Grammatical structure
sys_connect_by_path(field_name,'conn_char')
4,Start with said the start position, in this case for the Mgr is null, or ename = 'JONES'
5, CONNECT by is to control the traversal of the statement, indispensable, prior is control the traverse direction, if the above statement last line turns:


CONNECT BY PRIOR mgr = empno
The result set into:

That is to say, if king be opinionated, starting from their own traversal, but not downward traversal, but upward, then he is the Regal isolationist.

Of course, if it is a leaf node, by the upward traversal, can find its parent node, the parent node parent node...... To find out, until the root node.
Example 2: the use of connect by to convert sixteen hex number as a decimal number
First say if the use of mental arithmetic or written calculation, how to put the sixteen hexadecimal number to a decimal number, has a total of the algorithm. Don't tell me to column vertical, not have time. In fact, a formula, forget Comrades go back.
For example: 89D is the hexadecimal number sixteen, converted to decimal.
Analysis: the number of n=3
Split: 8*16^(n-1)+9*16(n-2)+13*(n-3)=8*256+9*16+13*1

After the split into a mathematical operation, the Oracle can be calculated. A function that uses the pl/sql language to realize the need for business logic, to write the most.
Here use a others written:

CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    - the object name: f_hex_to_dec
    - the object description: Sixteen hexadecimal conversion from decimal
    Input parameters: p_str sixteen hex string
    -- return results: decimal string
    Test case: SELECT f_hex_to_dec('78A') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return  VARCHAR2(4000);
  BEGIN
    SELECT SUM(DATA) INTO v_return
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
                     WHEN 'A' THEN '10'
                     WHEN 'B' THEN '11'
                     WHEN 'C' THEN '12'
                     WHEN 'D' THEN '13'
                     WHEN 'E' THEN '14'
                     WHEN 'F' THEN '15'
                     ELSE substr(p_str, rownum, 1)
                   END) * power(16, length(p_str) - rownum) DATA
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;

This function may also improve, but so do some explanation here,.
1, The use of rownum as a loop variable, defined with other circulating inside the I, this is from 1 to 1 increase in stride, don't give out.
2, CONNECT BY rownum <= length(p_str) This sentence is equivalent to the for loop in i<length (p_str) in the for loop, when the condition is fulfilled, the body of the loop is executed, it is when the conditions hold, execute select.
3, The inside of the select will take every (sixteen m) value (decimal); all seek out as printed records, the results as shown in Fig.:

The use of sum (data) to all records are added, correct results can be obtained.
The power of Oracle not only so, is the so-called love the deep, shallow knowledge of the shallow. Teach and trapped, study then knows the insufficiency. If one day you feel almost, that it is most in need of reflection, its poor life learning Oracle

I'm afraid also said not to school, open-minded attitude, flexible mind, the spirit of hard work, has been studying.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Gabriel at December 11, 2013 - 12:32 AM