*******************************************************************
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
一.什么是重构？
A series of small steps, each of which changes the program’s internal structure without changing its external behavior。
重构是一系列的小部骤,每一步只修改程序的内部结构,但是不改变它的外部行为.
二.为什么要重构
- To improve the software design
- 为了提高软件设计
-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时,进行重构.
三.我们应该在什么时候重构?
- To add new functionality
- 添加新的功能时
-Refactor existing code until you understand it
重构现有的代码直到你理解它们
-Refactor the design to make it easy to add
重构设计使它容易添加新的功能
-Refactor to understand the code
重构直到你理解代码
-Rmmediate effect of code review
-Allows for higher level suggestions
Don’t set aside time for refactoring,include it in your normal activities
在日常活动中进行重构,而不是另外找时间去重构.
四.最后的思想
-
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
-
重构允许你在代码已经写完后改进自己的设计.
-
Up front design is still important,but not so critical
-
事先的设计仍然是很重要的,但是并不那么关键了.
五.例子中用到的的重构条目:
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
六.例子中提到的模式:
策略模式
模版方法模式
状态模式