*******************************************************************
1. ROUND
*******************************************************************
The ROUND function returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.
Syntax
PL/SQL Example
ROUND (153.46) ==> 153
ROUND (153.46, 1) ==> 153.5
ROUND (153, -1) ==> 150
SQL Examples
SELECT ROUND(15.193,1) "Round"
FROM DUAL
Round
----------
15.2
SELECT ROUND(15.193,-1) "Round"
FROM DUAL
Round
----------
20
*******************************************************************
2. TRUNC
*******************************************************************
The TRUNC function truncates the number supplied to the specified number of places. If no place number is supplied, it rounds to zero decimal places. If the place number is negative, the number is truncated that many places to the right of the decimal place. This function does no rounding, it simply deletes the un-wanted numbers and returns the rest.
Syntax
PL/SQL Example
TRUNC (153.46) ==> 153
TRUNC (153.46, 1) ==> 153.4
TRUNC (-2003.16, -1) ==> -2000
SQL Example
SELECT TRUNC(15.79,1) "Truncate"
FROM DUAL
Truncate
----------
15.7
SELECT TRUNC(15.79,-1) "Truncate"
FROM DUAL
Truncate
----------
10
*******************************************************************
3. INSTR
*******************************************************************
The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters. INSTRC uses unicode complete characters. INSTR2 uses UCS2 codepoints. INSTR4 uses UCS4 codepoints.
position is an nonzero integer indicating the character of string where Oracle begins the search. If position is negative, Oracle counts and searches backward from the end of string.
occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive.
Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype.
The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string), the return value is 0.
Syntax
PL/SQL Example
Find the first occurrence of archie in “bug-or-tv-character?archie”:
INSTR ('bug-or-tv-character?archie', 'archie') ==> 21
The starting position and the nth appearance both defaulted to 1.
Find the first occurrence of archie in the following string starting from position 14:
INSTR ('bug-or-tv-character?archie', 'ar', 14) ==> 21
In this example a starting position was specified, which overrides the default of 1; the answer is still the same though. No matter where you start your search, the character position returned by INSTR is always calculated from the beginning of the string.
Find the second occurrence of archie in the following string:
INSTR ('bug-or-tv-character?archie', 'archie', 1, 2) ==> 0
There is only one archie in the string, so INSTR returns 0. Even though the starting point is the default, it cannot be left out if a nondefault nth appearance (2 in this case, for "second occurrence" ) is specified.
Find the second occurrence of "a" in "bug-or-tv-character?archie":
INSTR ('bug-or-tv-character?archie', 'a', 1, 2) ==> 15
The second "a" in this string is the second "a" in "character", which is in the fifteenth position in the string.
Find the last occurrence of "ar" in "bug-or-tv-character?archie".
INSTR ('bug-or-tv-character?archie', 'ar', -1) ==> 21
Use INSTR to confirm that a user entry is valid.
In the code below, we check to see if the command selected by the user is found in the list of valid commands. If so, that command is executed :
IF INSTR ('|ADD|DELETE|CHANGE|VIEW|CALC|', '|' || cmd || '|') > 0
THEN
execute_command (cmd);
ELSE
DBMS_OUTPUT.PUT_LINE
(' You entered an invalid command. Please try again...');
END IF;
SQL Examples
The following example searches the string "CORPORATE FLOOR", beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)
"Instring" FROM DUAL;
Instring
----------
14
The next example searches beginning with the third character from the end:
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)
"Reversed Instring"
FROM DUAL;
Reversed Instring
-----------------
2
This example assumes a double-byte database character set.
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes"
FROM DUAL;
Instring in bytes
-----------------
27
*******************************************************************
4. SUBSTR
*******************************************************************
The substring functions return a portion of string, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses unicode complete characters. SUBSTR2 uses UCS2 codepoints. SUBSTR4 uses UCS4 codepoints.
If position is 0, it is treated as 1.
If position is positive, Oracle counts from the beginning of string to find the first character.
If position is negative, Oracle counts backwards from the end of string.
If substring_length is omitted, Oracle returns all characters to the end of string. If substring_length is less than 1, a null is returned.
string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as string. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.
Syntax
PL/SQL Example
If the absolute value of the starting position exceeds the length of the input string, return NULL:
SUBSTR ('now_or_never', 200) ==> NULL
SUBSTR ('now_or_never', -200) ==> NULL
If starting position is 0, SUBSTR acts as though the starting position was actually 1:
SUBSTR ('now_or_never', 0, 3) ==> 'now'
SUBSTR ('now_or_never', 0) ==> 'now_or_never'
If the substring length is less than or equal to zero, return NULL:
SUBSTR ('now_or_never', 5, -2) ==> NULL
SUBSTR ('now_or_never', 1, 0) ==> NULL
Return the last character in a string:
SUBSTR ('Another sample string', -1) ==> 'g'
Remove an element from a string list.
This is, in a way, the opposite of SUBSTR: we want to extract a portion or substring of a string--and leave the rest of it intact. Suppose the screen maintains a list of selected temperatures, as follows:
|HOT|COLD|LUKEWARM|SCALDING|
The vertical bar delimits the different items on the list. When the user deselects "LUKEWARM," we now have to remove it from the list, which becomes:
|HOT|COLD|SCALDING|
The best way to accomplish this task is to determine the starting and ending positions of the item to be removed, and then use SUBSTR to take apart the list and put it back together without the specified item.
The list used in the above example contains 29 characters:
String: |HOT|COLD|LUKEWARM|SCALDING|
Character index: 1234567890123456789012345679
To extract this item from the list, we need to pull off the portion of the string before "LUKEWARM" as follows:
SUBSTR ('|HOT|COLD|LUKEWARM|SCALDING|', 1, 10)
and then we need to extract the trailing portion of the list (after "LUKEWARM"). Notice that we do not want to keep both of the delimiters when we put these pieces back together, so this next SUBSTR does not include the vertical bar at position 19:
SUBSTR ('|HOT|COLD|LUKEWARM|SCALDING|', 20)
We use the following concatenation of calls to SUBSTR:
SUBSTR ('|HOT|COLD|LUKEWARM|SCALDING|', 1, 10)
||
SUBSTR ('|HOT|COLD|LUKEWARM|SCALDING|', 20)
==>
'|HOT|COLD|SCALDING|'
Remove the middle word in a three-word string (in which each word is separated by an underscore) and switch the order of the first and last words.
FUNCTION bite_and_switch (tripart_string_in IN VARCHAR2)
RETURN VARCHAR2
IS
/* Location of first underscore */
first_delim_loc NUMBER := INSTR (tripart_string_in, '_', 1, 1);
/* Location of second underscore */
second_delim_loc NUMBER := INSTR (tripart_string_in, '_', 1, 2);
/* Return value of function, set by default to incoming string. */
return_value VARCHAR2(1000) := tripart_string_in;
BEGIN
/* Only switch words if two delimiters are found. */
IF second_delim_loc > 0
THEN
/* Pull out first and second words and stick them together. */
return_value :=
SUBSTR (tripart_string_in, 1, first_delim_loc - 1) || '_' ||
SUBSTR (tripart_string_in, second_delim_loc + 1);
END IF;
/* Return the switched string */
RETURN return_value;
END bite_and_switch;
SQL Examples
The following example returns several specified substrings of "ABCDEFG":
SELECT SUBSTR('ABCDEFG',3,4) "Substring"
FROM DUAL;
Substring
---------
CDEF
SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
FROM DUAL;
Substring
---------
CDEF
Assume a double-byte database character set:
SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes"
FROM DUAL;
Substring with bytes
--------------------
CD
*******************************************************************
5. RPAD
*******************************************************************
The RPAD or Right Pad function returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.RPAD will also perform a trim function on the string if the specified length is less than the actual string length.
Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char1.
The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.
Syntax
PL/SQL Example
Display the number padded right with zeros to a length of 10:
RPAD ('55', 10, '0') ==> '5500000000'
You could also use TO_CHAR to convert from a number to a character:
TO_CHAR (55 * 10000000) ==> '5500000000'
Display the number padded right with zeros to a length of 5:
RPAD ('12345678', 5) ==> '12345'
RPAD interprets its padded_length as the maximum length of the string that it may return. As a result, it counts padded_length number of characters from the left (start of the string) and then simply returns that substring of the incoming value. This is the same behavior as that found with LPAD. Remember: RPAD does not return the rightmost five characters (in the above case "45678").
Place the phrase "sell!" after the names of selected stocks, up to a string length of 45:
RPAD ('HITOP TIES', 45, 'sell!')
==>
' HITOP TIESsell!sell!sell!sell!sell!sell!sell!'
Since the length of "HITOP TIES" is 10 and the length of "sell!" is 5, there is room for seven repetitions of the pad string. RPAD does, in fact, generate a repetition of the pattern specified in the pad string.
Place the phrase "sell!" after the names of selected stocks, up to a string length of 43:
RPAD ('HITOP TIES', 43, 'sell!')
==>
'HITOP TIESsell!sell!sell!sell!sell!sell!sel'
Because the length of "HITOP TIES" is 10 and the length of "sell!" is 5, there is no longer room for seven full repetitions of the pad string. As a result, the seventh repetition of "sell!" lost its last two characters.
Create a string of 60 dashes to use as a border in a report:
RPAD ('-', 60, '-')
==>
'------------------------------------------------------------'
SQL Example
The following example rights-pads a name with the letters "ab" until it is 12 characters long:
SELECT RPAD('MORRISON',12,'ab') "RPAD example"
FROM DUAL;
RPAD example
-----------------
MORRISONabab
一.什么是重構(gòu)？
A series of small steps, each of which changes the program’s internal structure without changing its external behavior。
重構(gòu)是一系列的小部驟,每一步只修改程序的內(nèi)部結(jié)構(gòu),但是不改變它的外部行為.
二.為什么要重構(gòu)
- To improve the software design
- 為了提高軟件設(shè)計
-Combat's "it rot"
抵抗代碼的腐化
-Makes the program easier to change
使程序更容易修改
- To make the software easier to understand
- 使軟件更容易理解
-Write for people, not the compiler
程序是寫給人看的,不是給編譯器看的.
-Understand unfamiliar code
理解不熟悉的代碼
- To help find bugs
- 幫助找到bug
-Refactor while debugging to clarify the code
在為了理清代碼而DEBUG時,進行重構(gòu).
三.我們應(yīng)該在什么時候重構(gòu)?
- To add new functionality
- 添加新的功能時
-Refactor existing code until you understand it
重構(gòu)現(xiàn)有的代碼直到你理解它們
-Refactor the design to make it easy to add
重構(gòu)設(shè)計使它容易添加新的功能
- To find bugs
- 在發(fā)現(xiàn)bug時
-Refactor to understand the code
重構(gòu)直到你理解代碼
-Rmmediate effect of code review
-Allows for higher level suggestions
Don’t set aside time for refactoring,include it in your normal activities
在日常活動中進行重構(gòu),而不是另外找時間去重構(gòu).
四.最后的思想
-
The one benefit of objects is that they make it easier to change.
-
有一個好處就是使得對象更容易修改.
-
Refactoring allows you to improve the design after the code is written
-
重構(gòu)允許你在代碼已經(jīng)寫完后改進自己的設(shè)計.
-
Up front design is still important,but not so critical
-
事先的設(shè)計仍然是很重要的,但是并不那么關(guān)鍵了.
五.例子中用到的的重構(gòu)條目:
Find temp with a single assignment
Extract Right Hand Side of assignment
Replace all references of temp with new method
Remove declaration and assignment of temp
Compile and test
Create method named after intention of code
Copy extracted code
Look for local variables and parameters
Turn into parameter
Turn into return value
Declare within method
Compile
Replace code fragment with call to new method
Compile and test
Declare method in target class
Copy and fit code
Set up a reference from the source object to the target
Turn the original method into a delegating method
-amountOf(Rental each) {return each.charge()}
-Check for overriding methods
Compile and test
Find all users of the method
-Adjust them to call method on target
Remove original method
Compile and test
Create a new state class for the type code
Add subclasses of the state object, one for each type code
Create an abstract query in the superclass to return the type code. Override in subclasses to return correct type code
Compile
Create field in old class for the state object
Change the type code query to delegate to the state object
Change the type code setting methods to assign an instance of the subclass
Compile and test
Move switch to superclass of inheritance structure
Copy one leg of case statement into subclass
Compile and test
Repeat for all other legs
Replace case statement with abstract method
Take two methods with similar overall structure but varying pieces
Use subclasses of current class, or create a strategy and move the methods to the strategy
At each point of variation extract methods from each source with the the same signature but different body
Declare signature of extracted method in superclass and place varying bodies in subclasses
When all points of variation have been removed,move one source method to superclass and remove the other
六.例子中提到的模式:
策略模式
模版方法模式
狀態(tài)模式