PL/SQL集合用法

PL/SQL集合方法是用于操縱集合變量的内置函數或過程。

文法：

collection_name.method_name[(parameter)]

注意：集合方法extend和trim不适用于PL/SQL表。

1. exists

2. count

3. limit

傳回VARRAY變量所允許的最大元素個數。嵌套表和PL/SQL表的元素個數無限制，是以調用該方法時傳回NULL。

4. first和last

第一個元素的下标，最後一個元素的下标

ename_table(ename_table.first)

ename_table(ename_table.last)

5. prior和next

前一個元素的下标，後一個元素的下标

ename_table(ename_table.prior(1))

ename_table(ename_table.next(1))

6.extend

該方法為PL/SQL集合變量增加元素，隻适用于嵌套表和VARRAY。

注意：當使用EXTEND增加元素時，不能為未初始化的集合變量增加元素。

EXTEND方法有三種調用方法：

•EXTEND:添加一個null元素。

•EXTEND(n):添加n個null元素

•EXTEND(n,i):添加n個元素，元素值與第i個元素相同。

7. trim

該方法用于從集合變量尾部删除元素，并且隻适用于嵌套表和VARRAY。

TRIM共有兩種調用方法：

•TRIM: 從集合變量尾部删除一個元素。

•TRIM(n): 從集合變量尾部删除n個元素。

8. delete

用于删除特定元素，隻适用于嵌套表和PL/SQL表。

DELETE有三種調用方法：

•DELETE: 删除所有的元素

•DELETE(n): 删除第n個元素。

•DELETE(m,n):删除m~n之間所有的元素。

posted @2009-03-18 11:14JavaBegger 閱讀(114) |評論 (0)|編輯收藏

PL/SQL集合

第一，PL/SQL表

它隻能作為PL/SQL類型，不能作為表列類型。

下标可以為負值，并且元素個數無限制。

下标類型key_type: 包括BINARY_INTEGER、PLS_INTEGER、VARCHAR2。

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 ('長沙') := '長沙，我愛你！';

DBMS_OUTPUT.put_line (category_table ('長沙'));

END;

第二，嵌套表

可以作為表列的資料類型使用。

當使用嵌套表元素時，需要使用構造方法初始化嵌套表變量。

下标從1開始計算。

TYPE type_name IS TABLE OF element_type;

例子：

TYPE category_table_type IS TABLE OF tb_out_service.servicename%TYPE;

category_table := category_table_type ('全省', '長沙', '常德');

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));

第三，變長數組(VARRAY)

需要使用構造方法初始化VARRAY變量。

TYPE type_name is VARRAY(size_limit) OF element_type [NOT NULL];

posted @2009-03-18 10:50JavaBegger 閱讀(40) |評論 (0)|編輯收藏

PL/SQL記錄

第一，定義PL/SQL記錄

1. 自定義PL/SQL記錄

2. 使用%ROWTYPE屬性定義記錄變量

舉例：

-----------------------------------------------

--自定義記錄類型

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;

NULL;

第二，使用PL/SQL記錄

category_r tb_out_service%ROWTYPE;

...

UPDATE tb_out_service

SETROW= category_r --注意ROW關鍵字

WHERE serviceid = category_r.serviceid;

INSERT INTO tb_out_service

VALUEScategory_r; --在values子句中使用記錄成員播入資料。

posted @2009-03-18 09:45JavaBegger 閱讀(38) |評論 (0)|編輯收藏

使用事務控制語句

commit

rollback

savepoint a1

posted @2009-03-17 11:08JavaBegger 閱讀(34) |評論 (0)|編輯收藏

SQL遊标隐含遊标：專門處理SELECT INTO、INSERT、UPDATE以及DELETE語句。

顯示遊标：用于處理多行的SELECT語句。

當在PL/SQL塊中執行INSERT、UPDATE及DELETE語句時，為了取得DML語句作用的結果，需要使用SQL遊标屬性。

第一，遊标的4種屬性

1. SQL%ISOPEN

該屬性永遠都是false，沒有實際意義。

2. SQL%FOUND

語句是否操作成功。當SQL語句有作用行時為TRUE.

3. SQL%NOTFOUND

LOOP

FETCH emp_cursor INTO v_name, v_deptno;

EXIT WHEN emp_cursor%NOTFOUND;

END LOOP;

4. SQL%ROWCOUNT

傳回SQL語句所作用的總計行數。

該屬性用于傳回已提取的實際行數。

第二，顯示遊标專用于處理SELECT語句傳回的多行資料。

•定義遊标：

CURSOR cursor_name IS select_statement;

•打開遊标：

open cursor_name;

•提到資料：

FETCH cursor_name INTO var1[, var2, ...];

•關閉遊标：

CLOSE cursor_name;

CURSOR category_cursor

IS

SELECT *

FROM tb_out_service t

WHERE t.servicename LIKE '長沙%';

TYPE category_table_type IS TABLE OF tb_out_service%ROWTYPE

INDEX BY BINARY_INTEGER;

i INT;

OPEN category_cursor;

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

CLOSE category_cursor;

第三，遊标FOR循環

FOR record_name IN cursor_name LOOP

statement;

注意：當使用遊标FOR循環時，既可以在定義部分定義遊标，也可以直接在FOR循環中使用SELECT語句。

第四，參數遊标

CURSOR cursor_name(parameter_name datatype) is select_statement;

OPEN cursor_name(parameter_value)

注意：

定義參數隻能指定資料類型，不能指定長度。

另外，應該在遊标的select語句的where子句中引用遊标參數，否則失去了定義參數遊标的意義。

---------------------------------------------------------------------------------------------------------------------

CURSOR category_cursor (NAME VARCHAR2)

WHERE t.servicename LIKE NAME || '%';

FOR category_record IN category_cursor ('吉首')

DBMS_OUTPUT.put_line ( RPAD (category_record.serviceid, 30)

|| category_record.servicename

第五，更新或删除遊标行

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, 更新或者删除遊标所在行的資料。

3. 在使用遊标更新或删除資料時，定義遊标必須帶有FOR UPDATE子句，并且在更新或者删除遊标時必須帶有WHERE CURRENT OF 子句。

當查詢語句涉及到多張表時，如果不帶有OF子句，會在多張表上同時加鎖，如果隻在特定的表上加鎖，需要帶有OF子句。

WHERE t.servicename LIKE NAME || '%'

FOR UPDATE OF t.querystr;

v_name CONSTANT VARCHAR2 (20) := '長沙';

FOR category_record IN category_cursor (v_name)

IF INSTR (category_record.servicename, v_name || '——') <> 1

THEN

DBMS_OUTPUT.put_line ( 'delete: '

|| RPAD (category_record.serviceid, 30)

DELETE FROM tb_out_service

WHERE CURRENT OF category_cursor;

ELSE

DBMS_OUTPUT.put_line ( 'upate: '

UPDATE tb_out_service t

SET t.querystr = v_name || '——' || t.servicename

END IF;

第六，遊标變量

遊标變量是基于REF CURSOR類型所定義的變量，它實際上是指向記憶體位址的指針。顯式遊标隻能定義靜态遊标，而遊标變量可以在打開時指定其所對應的SELECT語句，進而實作動态遊标。

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;

不能在遠端子程式中使用遊标變量。

當指定子查詢時，不能帶有FOR UPDATE子句。

當指定RETURN子句時，傳回類型必須使用PL/SQL記錄類型。

TYPE category_cursor_type IS REF CURSOR

RETURN tb_out_service%ROWTYPE;

category_cursor category_cursor_type;

v_name CONSTANT VARCHAR2 (40) := '長沙';

OPEN category_cursor FOR

WHERE t.servicename LIKE v_name || '%';

INTO category_record;

posted @2009-03-17 11:07JavaBegger 閱讀(176) |評論 (0)|編輯收藏

在PL/SQL塊中可以使用的SQL函數

編寫PL/SQL時，可以直接使用大多數的單行SQL函數，這些單行函數包括數字函數、字元函數、轉換函數及日期函數。

注意：某些SQL函數隻能在SQL語句中引用，而不能直接在PL/SQL語句中引用，這些SQL函數包括GREATEST、LEAST、DECODE及所有的分組函數（如SUM）。

posted @2009-03-16 10:51JavaBegger 閱讀(23) |評論 (0)|編輯收藏

oracle資料庫限制限制用于確定資料庫數滿足業務規則。

限制包括：NOT NULL，UNIQUE，PRIMARY KEY，FOREIGN KEY以及CHECK等5種類型。

建立主鍵限制和唯一限制時，Oralce會基于限制列自動建立唯一索引；主鍵限制不允許為NULL，唯一限制允許為NULL。

一張表隻能建立一個主鍵限制。

建表限制：NOT NULL隻能在列級定義；其它4種既可以在列級定義，也可以在表級定義。複合主鍵限制隻能在表級定義。

維護限制：增加NOT NULL限制時必須使用MODIFY子句，而增加其它限制時需要使用ADD子句。

第一， 定義限制

---------------------------------------------

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)

說明：

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)

第二， 維護限制

----------------------------------------

1. 增加限制

NOT NULL使用ALTER MODIFY子句，其它的使用ALTER ADD子句

-------------------------------

CREATE TABLE tt_user(NAME VARCHAR2(20));

ALTER TABLE tt_userMODIFYuser_name NOT NULL;

ALTER TABLE tt_userADDCONSTRAINT 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.激動限制

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

第三. 顯示限制資訊

所有限制資訊

FROM user_constraints

使用者限制所對應的表列

FROM user_cons_columns;

posted @2009-03-12 15:03JavaBegger 閱讀(207) |評論 (0)|編輯收藏

Oracle建立臨時表

臨時表分為兩種，它們隻在事務或者會話内有效。

------------------------------------

A. 事務臨時表(預設)：

CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT);

CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT) ON COMMIT DELETE ROWS;

B. 會話臨時表：

CREATE GLOBAL TEMPORARY TABLE table_name(col_name INT) ON COMMIT PRESERVE ROWS;

posted @2009-03-12 11:43JavaBegger 閱讀(185) |評論 (0)|編輯收藏

oracle資料庫表删除操作

A. 删除資料

-----------------------------------------

方法一：

delete(DML)

隻是删除資料，并沒有釋放空間

操作可以回退

DELETE FROM table_name;

方法二：

truncate table(DDL)

删除資料，釋放表空間，操作不能回退

TRUNCATE TABLE table_name;

B. 删除庫表

------------------------------------------

DROP TABLE table_name [CASCADE CONSTRAINTS] [purge]

CASCADE CONSTRAINTS： 表示是否級聯删除外鍵限制

C. 恢複删除的庫表（10g）

FLASHBACK TABLE table_name TO BEFORE DROP;

posted @2009-03-12 11:35JavaBegger 閱讀(89) |評論 (0)|編輯收藏

oracle中的幾個資料字典視圖

摘要:閱讀全文

posted @2009-03-12 11:20JavaBegger 閱讀(32) |評論 (0)|編輯收藏

Oracle Append，使用子查詢複制資料

當插入資料時，必須為NOT NULL列和主鍵列提供資料;

當複制大批量資料時，使用直接裝載的速度遠遠優于正常裝載。

使用正常裝載方式複制資料：

INSERT INTO table_name

FROM DUAL;

使用直接裝載方式複制資料：

INSERT INTO /*append*/ table_name

posted @2009-03-11 11:52JavaBegger 閱讀(107) |評論 (0)|編輯收藏

數字函數

*******************************************************************

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"

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.

TRUNC (153.46) ==> 153

TRUNC (153.46, 1) ==> 153.4

TRUNC (-2003.16, -1) ==> -2000

SQL Example

SELECT TRUNC(15.79,1) "Truncate"

Truncate

15.7

SELECT TRUNC(15.79,-1) "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.

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

execute_command (cmd);

DBMS_OUTPUT.PUT_LINE

(' You entered an invalid command. Please try again...');

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"

Reversed Instring

-----------------

2

This example assumes a double-byte database character set.

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes"

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.

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:

||

==>

'|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

/* 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;

/* Only switch words if two delimiters are found. */

IF second_delim_loc > 0

/* 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);

/* Return the switched string */

RETURN return_value;

END bite_and_switch;

The following example returns several specified substrings of "ABCDEFG":

SELECT SUBSTR('ABCDEFG',3,4) "Substring"

Substring

---------

CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"

Assume a double-byte database character set:

SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes"

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.

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, '-')

'------------------------------------------------------------'

The following example rights-pads a name with the letters "ab" until it is 12 characters long:

SELECT RPAD('MORRISON',12,'ab') "RPAD example"

RPAD example

MORRISONabab

posted @2009-03-11 09:53JavaBegger 閱讀(15) |評論 (0)|編輯收藏

ORACLE中ESCAPE關鍵字用法 換字元用法

英文解釋：

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:

WHERE NAME LIKE 'XY++Z%' ESCAPE '+'

Result: XY+ZAAAAA

XY+Z99999

漢語解釋：

定義：escape關鍵字經常用于使某些特殊字元，如通配符：'%','_'轉義為它們原

來的字元的意義，被定義的轉義字元通常使用'\',但是也可以使用其他的符号。

執行個體：

SQL> select * from t11 where name like '%_%';

NAME

aa_a

aaa

SQL> select * from t11 where name like '%\_%' escape '\';

注意：如果是 '/' 作為檢索字元, 必須 用 '/' 作為轉義符, 正斜扛也一樣.

select * from wan_test where psid like '%//%' escape '/'

本文轉自 wws5201985 51CTO部落格，原文連結：http://blog.51cto.com/wws5201985/785383，如需轉載請自行聯系原作者