2009年3月3日
打開(kāi)Net Manager，選中服務(wù)名稱，檢查服務(wù)標(biāo)識(shí)欄里的服務(wù)名輸入是否正確。該服務(wù)名必須與服務(wù)器端監(jiān)聽(tīng)器配置的全局?jǐn)?shù)據(jù)庫(kù)名一致。
方法二： 在TOAD登陸時(shí)使用全局名稱：
zhouzhenhua:1521/javafan
人品不錯(cuò)，挺讓人放心的，不會(huì)像有的男的看起來(lái)很花花的，還有就是沒(méi)有明顯的缺點(diǎn)，這可是很重要的
我現(xiàn)在看到的男的歪瓜裂棗的太多了
PL/SQL集合方法是用于操縱集合變量的內(nèi)置函數(shù)或過(guò)程。
語(yǔ)法：
collection_name.method_name[(parameter)]
注意：集合方法extend和trim不適用于PL/SQL表。
1. exists
2. count
3. limit
返回VARRAY變量所允許的最大元素個(gè)數(shù)。嵌套表和PL/SQL表的元素個(gè)數(shù)無(wú)限制，所以調(diào)用該方法時(shí)返回NULL。
4. first和last
第一個(gè)元素的下標(biāo)，最后一個(gè)元素的下標(biāo)
ename_table(ename_table.first)
ename_table(ename_table.last)
5. prior和next
前一個(gè)元素的下標(biāo)，后一個(gè)元素的下標(biāo)
ename_table(ename_table.prior(1))
ename_table(ename_table.next(1))
6.extend
該方法為PL/SQL集合變量增加元素，只適用于嵌套表和VARRAY。
注意：當(dāng)使用EXTEND增加元素時(shí)，不能為未初始化的集合變量增加元素。
EXTEND方法有三種調(diào)用方法：
- EXTEND:添加一個(gè)null元素。
- EXTEND(n):添加n個(gè)null元素
- EXTEND(n,i):添加n個(gè)元素，元素值與第i個(gè)元素相同。
7. trim
該方法用于從集合變量尾部刪除元素，并且只適用于嵌套表和VARRAY。
TRIM共有兩種調(diào)用方法：
- TRIM: 從集合變量尾部刪除一個(gè)元素。
- TRIM(n): 從集合變量尾部刪除n個(gè)元素。
8. delete
用于刪除特定元素，只適用于嵌套表和PL/SQL表。
DELETE有三種調(diào)用方法：
- DELETE: 刪除所有的元素
- DELETE(n): 刪除第n個(gè)元素。
- DELETE(m,n):刪除m~n之間所有的元素。
第一，PL/SQL表
它只能作為PL/SQL類型，不能作為表列類型。
下標(biāo)可以為負(fù)值，并且元素個(gè)數(shù)無(wú)限制。
下標(biāo)類型key_type: 包括BINARY_INTEGER、PLS_INTEGER、VARCHAR2。
語(yǔ)法：
TYPE type_name IS TABLE OF element_type
[NOT NULL] INDEX BY key_type;
identifier type_name;
例如：
DECLARE
TYPE category_table_type IS TABLE OF VARCHAR2 (40)
INDEX BY VARCHAR2 (40);
category_table category_table_type;
BEGIN
category_table ('長(zhǎng)沙') := '長(zhǎng)沙，我愛(ài)你！';
DBMS_OUTPUT.put_line (category_table ('長(zhǎng)沙'));
END;
第二，嵌套表
可以作為表列的數(shù)據(jù)類型使用。
當(dāng)使用嵌套表元素時(shí)，需要使用構(gòu)造方法初始化嵌套表變量。
下標(biāo)從1開(kāi)始計(jì)算。
語(yǔ)法：
TYPE type_name IS TABLE OF element_type;
identifier type_name;
例子：
DECLARE
TYPE category_table_type IS TABLE OF tb_out_service.servicename%TYPE;
category_table category_table_type;
BEGIN
category_table := category_table_type ('全省', '長(zhǎng)沙', '常德');
SELECT servicename
INTO category_table (3)
FROM tb_out_service
WHERE serviceid = '&serviceid';
DBMS_OUTPUT.put_line (category_table (1));
DBMS_OUTPUT.put_line (category_table (2));
DBMS_OUTPUT.put_line (category_table (3));
END;
第三，變長(zhǎng)數(shù)組(VARRAY)
需要使用構(gòu)造方法初始化VARRAY變量。
語(yǔ)法：
TYPE type_name is VARRAY(size_limit) OF element_type [NOT NULL];
identifier type_name;
第一，定義PL/SQL記錄
1. 自定義PL/SQL記錄
2. 使用%ROWTYPE屬性定義記錄變量
舉例：
-----------------------------------------------
DECLARE
--自定義記錄類型
TYPE emp_record_type IS RECORD (
serviceid tb_out_service%TYPE,
sericename tb_out_service%TYPE
);
emp_record emp_record_type;
--使用%ROWTYPE屬性定義記錄變量
category_record tb_out_service%ROWTYPE;
BEGIN
NULL;
END;
第二，使用PL/SQL記錄
DECLARE
--使用%ROWTYPE屬性定義記錄變量
category_r tb_out_service%ROWTYPE;
BEGIN
...
UPDATE tb_out_service
SET ROW = category_r --注意ROW關(guān)鍵字
WHERE serviceid = category_r.serviceid;
...
INSERT INTO tb_out_service
VALUES category_r; --在values子句中使用記錄成員播入數(shù)據(jù)。
...
END;
commit
rollback
savepoint a1
隱含游標(biāo)：專門(mén)處理SELECT INTO、INSERT、UPDATE以及DELETE語(yǔ)句。
顯示游標(biāo)：用于處理多行的SELECT語(yǔ)句。
當(dāng)在PL/SQL塊中執(zhí)行INSERT、UPDATE及DELETE語(yǔ)句時(shí)，為了取得DML語(yǔ)句作用的結(jié)果，需要使用SQL游標(biāo)屬性。
第一，游標(biāo)的4種屬性
1. SQL%ISOPEN
該屬性永遠(yuǎn)都是false，沒(méi)有實(shí)際意義。
2. SQL%FOUND
語(yǔ)句是否操作成功。當(dāng)SQL語(yǔ)句有作用行時(shí)為T(mén)RUE.
3. SQL%NOTFOUND
LOOP
FETCH emp_cursor INTO v_name, v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
...
END LOOP;
4. SQL%ROWCOUNT
返回SQL語(yǔ)句所作用的總計(jì)行數(shù)。
該屬性用于返回已提取的實(shí)際行數(shù)。
第二，顯示游標(biāo)專用于處理SELECT語(yǔ)句返回的多行數(shù)據(jù)。
CURSOR cursor_name IS select_statement;
open cursor_name;
FETCH cursor_name INTO var1[, var2, ...];
CLOSE cursor_name;
例子：
DECLARE
CURSOR category_cursor
IS
SELECT *
FROM tb_out_service t
WHERE t.servicename LIKE '長(zhǎng)沙%';
TYPE category_table_type IS TABLE OF tb_out_service%ROWTYPE
INDEX BY BINARY_INTEGER;
category_table category_table_type;
i INT;
BEGIN
OPEN category_cursor;
LOOP
i := category_cursor%ROWCOUNT + 1;
FETCH category_cursor
INTO category_table (i);
EXIT WHEN category_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ( RPAD (category_table (i).serviceid, 30)
|| ' '
|| category_table (i).servicename
);
END LOOP;
CLOSE category_cursor;
END;
第三，游標(biāo)FOR循環(huán)
語(yǔ)法：
FOR record_name IN cursor_name LOOP
statement;
...
END LOOP;
注意：當(dāng)使用游標(biāo)FOR循環(huán)時(shí)，既可以在定義部分定義游標(biāo)，也可以直接在FOR循環(huán)中使用SELECT語(yǔ)句。
第四，參數(shù)游標(biāo)
CURSOR cursor_name(parameter_name datatype) is select_statement;
OPEN cursor_name(parameter_value)
注意：
定義參數(shù)只能指定數(shù)據(jù)類型，不能指定長(zhǎng)度。
另外，應(yīng)該在游標(biāo)的select語(yǔ)句的where子句中引用游標(biāo)參數(shù)，否則失去了定義參數(shù)游標(biāo)的意義。
---------------------------------------------------------------------------------------------------------------------
DECLARE
CURSOR category_cursor (NAME VARCHAR2)
IS
SELECT *
FROM tb_out_service t
WHERE t.servicename LIKE NAME || '%';
BEGIN
FOR category_record IN category_cursor ('吉首')
LOOP
DBMS_OUTPUT.put_line ( RPAD (category_record.serviceid, 30)
|| ' '
|| category_record.servicename
);
END LOOP;
END;
第五，更新或刪除游標(biāo)行
語(yǔ)法：
CURSOR cursor_name IS select_statement
FOR UPDATE [OF column_reference] [NOWAIT];
UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;
注意：
1. OF子句的格式：column_reference為table_name.column_name
2. WHERE CURRENT OF cursor_name, 更新或者刪除游標(biāo)所在行的數(shù)據(jù)。
3. 在使用游標(biāo)更新或刪除數(shù)據(jù)時(shí)，定義游標(biāo)必須帶有FOR UPDATE子句，并且在更新或者刪除游標(biāo)時(shí)必須帶有WHERE CURRENT OF 子句。
當(dāng)查詢語(yǔ)句涉及到多張表時(shí)，如果不帶有OF子句，會(huì)在多張表上同時(shí)加鎖，如果只在特定的表上加鎖，需要帶有OF子句。
DECLARE
CURSOR category_cursor (NAME VARCHAR2)
IS
SELECT *
FROM tb_out_service t
WHERE t.servicename LIKE NAME || '%'
FOR UPDATE OF t.querystr;
v_name CONSTANT VARCHAR2 (20) := '長(zhǎng)沙';
BEGIN
FOR category_record IN category_cursor (v_name)
LOOP
IF INSTR (category_record.servicename, v_name || '——') <> 1
THEN
DBMS_OUTPUT.put_line ( 'delete: '
|| RPAD (category_record.serviceid, 30)
|| ' '
|| category_record.servicename
);
DELETE FROM tb_out_service
WHERE CURRENT OF category_cursor;
ELSE
DBMS_OUTPUT.put_line ( 'upate: '
|| RPAD (category_record.serviceid, 30)
|| ' '
|| category_record.servicename
);
UPDATE tb_out_service t
SET t.querystr = v_name || '——' || t.servicename
WHERE CURRENT OF category_cursor;
END IF;
END LOOP;
END;
第六， 游標(biāo)變量
游標(biāo)變量是基于REF CURSOR類型所定義的變量，它實(shí)際上是指向內(nèi)存地址的指針。顯式游標(biāo)只能定義靜態(tài)游標(biāo)，而游標(biāo)變量可以在打開(kāi)時(shí)指定其所對(duì)應(yīng)的SELECT語(yǔ)句，從而實(shí)現(xiàn)動(dòng)態(tài)游標(biāo)。
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
cursor_variable ref_type_name;
OPEN cursor_name FOR select_statement;
FETCH cursor_variable INTO var1[, var2, ...];
CLOSE cursor_variable;
注意：
不能在遠(yuǎn)程子程序中使用游標(biāo)變量。
當(dāng)指定子查詢時(shí)，不能帶有FOR UPDATE子句。
當(dāng)指定RETURN子句時(shí)，返回類型必須使用PL/SQL記錄類型。
例子：
DECLARE
TYPE category_cursor_type IS REF CURSOR
RETURN tb_out_service%ROWTYPE;
category_cursor category_cursor_type;
category_record tb_out_service%ROWTYPE;
v_name CONSTANT VARCHAR2 (40) := '長(zhǎng)沙';
BEGIN
OPEN category_cursor FOR
SELECT *
FROM tb_out_service t
WHERE t.servicename LIKE v_name || '%';
LOOP
FETCH category_cursor
INTO category_record;
EXIT WHEN category_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ( RPAD (category_record.serviceid, 30)
|| ' '
|| category_record.servicename
);
END LOOP;
END;
編寫(xiě)PL/SQL時(shí)，可以直接使用大多數(shù)的單行SQL函數(shù)，這些單行函數(shù)包括數(shù)字函數(shù)、字符函數(shù)、轉(zhuǎn)換函數(shù)及日期函數(shù)。
注意：某些SQL函數(shù)只能在SQL語(yǔ)句中引用，而不能直接在PL/SQL語(yǔ)句中引用，這些SQL函數(shù)包括GREATEST、LEAST、DECODE及所有的分組函數(shù)（如SUM）。
約束用于確保數(shù)據(jù)庫(kù)數(shù)滿足業(yè)務(wù)規(guī)則。
約束包括：NOT NULL，UNIQUE，PRIMARY KEY，F(xiàn)OREIGN KEY以及CHECK等5種類型。
建立主鍵約束和唯一約束時(shí)，Oralce會(huì)基于約束列自動(dòng)建立唯一索引；主鍵約束不允許為NULL，唯一約束允許為NULL。
一張表只能建立一個(gè)主鍵約束。
建表約束：NOT NULL只能在列級(jí)定義；其它4種既可以在列級(jí)定義，也可以在表級(jí)定義。復(fù)合主鍵約束只能在表級(jí)定義。
維護(hù)約束：增加NOT NULL約束時(shí)必須使用MODIFY子句，而增加其它約束時(shí)需要使用ADD子句。
第一， 定義約束
---------------------------------------------
語(yǔ)法：
CREATE TABLE [SCHEMA.]table_name(
column_name datatype [DEFAULT expr] [column_constraint],
...
[table_constraint][, ...]
);
例子：
CREATE TABLE tt_user_info
(
ID VARCHAR2(20 BYTE),
NAME VARCHAR2(20 BYTE) NOT NULL,
category_id VARCHAR2(20 BYTE) REFERENCES tb_out_service(serviceid),
remark VARCHAR2(1000)
);
ALTER TABLE tt_user_info ADD (
CHECK ( LENGTH(NAME)>2),
PRIMARY KEY (ID),
UNIQUE (NAME)
);
說(shuō)明：
1. NOT NULL，非空約束
not null
2. UNIQUE，唯一約束
UNIQUE (COL_NAME)
3. PRIMARY KEY，主鍵約束
primary key (col_name1 [, col_name2])
4. FOREIGN KEY，外鍵約束
它有三種類型：
references primary_table(primary_col)
on delete cascade
on delete set null
5. CHECK，檢查約束
check (money > 1000)
第二， 維護(hù)約束
----------------------------------------
1. 增加約束
NOT NULL使用ALTER MODIFY子句，其它的使用ALTER ADD子句
-------------------------------
CREATE TABLE tt_user(NAME VARCHAR2(20));
ALTER TABLE tt_user MODIFY user_name NOT NULL;
ALTER TABLE tt_user ADD CONSTRAINT constraint_name UNIQUE(NAME);
ALTER TABLE tt_user ADD CONSTRAINT constraint_name PRIMARY KEY(NAME);
ALTER TABLE tt_user ADD parentid VARCHAR2(20)
CONSTRAINT constraint_name
REFERENCES tb_out_service(serviceid);
2. 修改約束名
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name TO new_constraint_name
3. 刪除約束
ALTER TABLE table_name DROP CONSTRAINT constraint_name
4. 禁止約束
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name [CASCADE];
5.激動(dòng)約束
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
第三. 顯示約束信息
所有約束信息
SELECT *
FROM user_constraints
用戶約束所對(duì)應(yīng)的表列
SELECT *
FROM user_cons_columns;
臨時(shí)表分為兩種，它們只在事務(wù)或者會(huì)話內(nèi)有效。
------------------------------------
A. 事務(wù)臨時(shí)表(缺省)：
CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT);
CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT) ON COMMIT DELETE ROWS;
------------------------------------
B. 會(huì)話臨時(shí)表：
CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT) ON COMMIT PRESERVE ROWS;
A. 刪除數(shù)據(jù)
-----------------------------------------
方法一：
delete(DML)
只是刪除數(shù)據(jù)，并沒(méi)有釋放空間
操作可以回退
例如：
DELETE FROM table_name;
方法二：
truncate table(DDL)
刪除數(shù)據(jù)，釋放表空間，操作不能回退
例如：
TRUNCATE TABLE table_name;
B. 刪除庫(kù)表
------------------------------------------
DROP TABLE table_name [CASCADE CONSTRAINTS] [purge]
CASCADE CONSTRAINTS： 表示是否級(jí)聯(lián)刪除外鍵約束
C. 恢復(fù)刪除的庫(kù)表（10g）
FLASHBACK TABLE table_name TO BEFORE DROP;
當(dāng)插入數(shù)據(jù)時(shí)，必須為NOT NULL列和主鍵列提供數(shù)據(jù);
當(dāng)復(fù)制大批量數(shù)據(jù)時(shí)，使用直接裝載的速度遠(yuǎn)遠(yuǎn)優(yōu)于常規(guī)裝載。
使用常規(guī)裝載方式復(fù)制數(shù)據(jù)：
INSERT INTO table_name
SELECT *
FROM DUAL;
使用直接裝載方式復(fù)制數(shù)據(jù)：
INSERT INTO /*append*/ table_name
SELECT *
FROM DUAL;
*******************************************************************
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
英文解釋：
It is necessary to use an "escape" character to locate the characters '_' and '%' in a column. The keyword ESCAPE followed by the character used as the delimitor of choice is coded after the string search. For example, '+' is used as the escape character. For example:
SELECT NAME
FROM XYZ_TABLE
WHERE NAME LIKE 'XY+_Z+%BC%'ESCAPE '+'
Result: XY_Z%BCAA
...
XY_Z%BC99
The plus sign '+' can still be used as part of the search string as long as a '+' precedes it. For example:
SELECT NAME
FROM XYZ_TABLE
WHERE NAME LIKE 'XY++Z%' ESCAPE '+'
Result: XY+ZAAAAA
...
XY+Z99999
漢語(yǔ)解釋：
定義：escape關(guān)鍵字經(jīng)常用于使某些特殊字符，如通配符：'%','_'轉(zhuǎn)義為它們?cè)?
來(lái)的字符的意義，被定義的轉(zhuǎn)義字符通常使用'\',但是也可以使用其他的符號(hào)。
實(shí)例：
SQL> select * from t11 where name like '%_%';
NAME
----------
aa_a
aaa
SQL> select * from t11 where name like '%\_%' escape '\';
NAME
----------
aa_a
注意：如果是 '/' 作為檢索字符, 必須 用 '/' 作為轉(zhuǎn)義符, 正斜扛也一樣.
select * from wan_test where psid like '%//%' escape '/'
懷孕了才知道老公很搞笑 ！
懷孕已經(jīng)17周了，老公也越來(lái)越搞笑，他常常會(huì)趴在我肚子上問(wèn)我，寶寶怎么沒(méi)反應(yīng)？他還活著嗎？
我老公要和寶寶說(shuō)話，每天只說(shuō)三句：寶寶你好，我是你爸爸。我是好人。
寶寶4個(gè)月以后，我喊老公給兒子做胎教，他就趴在我肚皮上對(duì)寶寶用很像大灰狼的語(yǔ)調(diào)說(shuō)：“蛋蛋你趕快給我睡覺(jué)！不然爸爸打你屁屁”這叫什么胎教啊！
我每次和老公發(fā)嗲，老公就貼著肚皮對(duì)寶寶說(shuō)：踢你媽。
還有我每次躺著讓他數(shù)胎動(dòng)，他都睡得特別快，真是催眠的好辦法。
現(xiàn)在寶寶動(dòng)作大了，每天睡覺(jué)前有時(shí)可以看到肚皮明顯的滾動(dòng)。老公看到總是很緊張，說(shuō)寶寶，你在干嘛？然后摸摸肚子說(shuō)，不要?jiǎng)恿耍焖X(jué)。如果哪天沒(méi)動(dòng)，他又很緊張，左敲敲右敲敲說(shuō)，寶寶，你動(dòng)一下呀。
我每次跟老公說(shuō)，寶寶又在踢我了。他就會(huì)很認(rèn)真地說(shuō)：等他出來(lái)我踢他，幫你報(bào)仇。
我老公聽(tīng)我說(shuō)了胎教的重要性之后一本正經(jīng)地對(duì)寶寶說(shuō)：“寶寶，我是爸爸，今天我們要講的主題是看電視。好，現(xiàn)在開(kāi)始看電視，你要安靜點(diǎn)哦！”
我讓我老公唱歌給寶寶聽(tīng)，他居然唱：我們是害蟲(chóng)，我們是害蟲(chóng)。
記得有一次做B超，老公跟著一起進(jìn)去，當(dāng)看到屏幕上寶寶在動(dòng)時(shí)，他激動(dòng)得連說(shuō)話聲音都不一樣了。當(dāng)看到寶寶那根長(zhǎng)長(zhǎng)的脊椎時(shí)，他居然興奮地說(shuō)：哦，還有尾巴啊！
我看到一個(gè)奇怪的現(xiàn)象，就是看我貼子的一部分人，都在不久后做成了如下事情：
1、戀愛(ài)成功了； 2、生意談成了； 3、升官發(fā)財(cái)了； 4、心情舒暢了；
5、家庭和睦了； 6、身體健康了； 7、萬(wàn)事順意了。
第一、學(xué)習(xí)struts2標(biāo)簽文檔的順序
struts2標(biāo)簽語(yǔ)法
struts-2.0.11.2/docs/docs/tag-syntax.html
OGNL語(yǔ)法
struts-2.0.11.2/docs/docs/ognl.html
標(biāo)簽參考
struts-2.0.11.2/docs/docs/tag-reference.html
第二、容易搞混的符號(hào)：
%{...}: 顯示地表示表達(dá)式
#： 調(diào)用非根的上下文map中的值，如#request, #session, #attr等
@： 調(diào)用靜態(tài)方法或者靜態(tài)屬性
''： 用來(lái)表示字符串常量
""： html元素屬性值
舉例：
調(diào)用靜態(tài)屬性：
<s:property value="@全類名@serialVersionUID"/>
調(diào)用靜態(tài)方法：
<s:property value="@全類名@sayHello()"/>
第三、使用debug調(diào)試標(biāo)簽
<s:debug/>
第四、action的測(cè)試junit測(cè)試用例的編寫(xiě)方法
新建action
填入?yún)?shù)
執(zhí)行action方法，比如execute()
assert輸出結(jié)果
第五、自定義struts2標(biāo)簽
AbstractUITag
ComponentTagSupport
HiddenTag
Hiddeng
ftl模塊文件
1. 在web.xml中不需要同struts1般部署FreeMarker了;原因網(wǎng)上說(shuō)是由于Struts2本身便集成了FreeMarker,< <Struts2權(quán)威指南>>一書(shū)說(shuō)是Struts2的標(biāo)簽本身就是用FreeMarker編寫(xiě)的.
2. 由于 ftl 文件默認(rèn)編碼為 GBK,但頁(yè)面上無(wú)法顯示,后經(jīng)高手指點(diǎn)----需在項(xiàng)目屬性中將 Text file encoding 編碼改為 UTF-8.
3. 在 struts.xml 配置文件中跳轉(zhuǎn)到 ftl 頁(yè)面時(shí),需要在 <result >中添加 type="freemarker ">您要跳轉(zhuǎn)的ftl頁(yè)面</result>
1.頁(yè)面編碼統(tǒng)一utf-8
2.獲取值的時(shí)候?qū)so8859-1轉(zhuǎn)換成utf-8
或者做個(gè)過(guò)濾器，將頭設(shè)置成utf-8
3.數(shù)據(jù)庫(kù)編碼是utf-8，連接的時(shí)候也制定utf-8
4.tomcat里面的server.xml 里面設(shè)置URIEncoding="UTF-8"