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;
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 ('全省', '长沙', '常德');
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;
第三，变长数组(VARRAY)
需要使用构造方法初始化VARRAY变量。
语法：
TYPE type_name is VARRAY(size_limit) OF element_type [NOT NULL];
identifier type_name;
posted @2009-03-18 10:50JavaBegger 阅读(40) |评论 (0)|编辑收藏
PL/SQL记录
第一，定义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
SETROW= category_r --注意ROW关键字
WHERE serviceid = category_r.serviceid;
...
INSERT INTO tb_out_service
VALUEScategory_r; --在values子句中使用记录成员播入数据。
...
END;
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;
例子：
DECLARE
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;
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;
第三，游标FOR循环
语法：
FOR record_name IN cursor_name LOOP
statement;
...
END LOOP;
注意：当使用游标FOR循环时，既可以在定义部分定义游标，也可以直接在FOR循环中使用SELECT语句。
第四，参数游标
CURSOR cursor_name(parameter_name datatype) is select_statement;
OPEN cursor_name(parameter_value)
注意：
定义参数只能指定数据类型，不能指定长度。
另外，应该在游标的select语句的where子句中引用游标参数，否则失去了定义参数游标的意义。
---------------------------------------------------------------------------------------------------------------------
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;
第五，更新或删除游标行
语法：
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子句。
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) := '长沙';
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;
第六，游标变量
游标变量是基于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记录类型。
例子：
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) := '长沙';
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;
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;
第三. 显示约束信息
所有约束信息
SELECT *
FROM user_constraints
用户约束所对应的表列
SELECT *
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
SELECT *
FROM DUAL;
使用直接装载方式复制数据：
INSERT INTO /*append*/ table_name
SELECT *
FROM DUAL;
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"
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
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:
SELECT NAME
FROM XYZ_TABLE
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 '\';
NAME
----------
aa_a
注意：如果是 '/' 作为检索字符, 必须 用 '/' 作为转义符, 正斜扛也一样.
select * from wan_test where psid like '%//%' escape '/'
