oracle中的类型转换函数。带⼩数点的字符串（除⼩数点外其它的都是数字）转换成数值
TO_NUMBER
Converts a string to the NUMBER data type TO_NUMBER(<value>[, <format>, <NLS parameter>]) RETURN NUMBER
CREATE TABLE test (
testcol VARCHAR2(10));
INSERT INTO test VALUES ('12345.67');
SELECT TO_BINARY_DOUBLE(testcol) BIN_DOUBLE, TO_BINARY_FLOAT(testcol) BIN_FLOAT, TO_NUMBER(testcol) NMBR
FROM test;
Converts a HEX number to FLOAT TO_NUMBER(<value>, <format>);
SELECT TO_NUMBER('0A', 'XX') FROM dual;
Converts a HEX number to DECIMAL TO_NUMBER(<binary_float | binary_double | number>,
'<hex mask>') RETURN <binary_float | binary_double | number>;
SELECT TO_NUMBER(100000,'XXXXXXXX')
FROM dual;
1.语法：TO_NUMBER(string[,format[,nlsparams]])
⽬的：将CHAR或VARCHAR2类型的string转换为⼀个NUMBER类型的数值，如果指定了format，那么string应该遵循相应的数字格式。
2.范例
DECLARE
v_Num  NUMBER;
BEGIN
v_Num  :=  TO_NUMBER( '$12345.67 ', '$99999.99 ');
END;
ORACLE UTL_RAW
General Information
Source{ORACLE_HOME}/rdbms/admin/utlraw.sql
First Available7.3.4
Constants Name Data Type Value big_endian PLS_INTEGER1 little_endian PLS_INTEGER2 machine_endian PLS_INTEGER3
Dependencies 179 objects
SELECT name FROM dba_dependencies
WHERE referenced_name = 'UTL_RAW'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'UTL_RAW';
WHERE name = 'UTL_RAW';
Exceptions Error #Name Description
VALUE_ERROR
ORA-
6502
An arithmetic, conversion, truncation, or size-constraint error. Usually raised by trying to cram a 6 character
string into a VARCHAR2(5).
Required Object Privileges
GRANT execute on UTL_RAW
GRANT execute ON utl_raw TO UWCLASS; BIT_AND
Perform bitwise logical "and" of the values in raw r1 with raw r2 and return the "anded" result raw utl_raw.bit_and(r1 IN RAW, r2 IN RAW) RETURN RAW;
SELECT utl_raw.bit_and('0102F3', 'F30201')
FROM dual;
BIT_COMPLEMENT
Perform bitwise logical "complement" of the values in raw and return the "complement'ed" result raw utl_raw.bit_complement(r IN RAW) RETURN RAW;
SELECT utl_raw.bit_complement('0102F3') FROM dual;
BIT_OR
Perform bitwise logical "or" of the values in raw r1 with raw r2 and return the "or'd" result raw utl_raw.bit_or(r1 IN RAW, r2 IN RAW) RETURN RAW;
SELECT utl_raw.bit_or('0102F3', 'F30201')
FROM dual;
BIT_XOR
Perform bitwise logical "exclusive or" of the values in raw r1 with raw r2 and return the "xor'd" result raw utl_raw.bit_xor(r1 IN RAW, r2 IN RAW) RETURN RAW;
SELECT utl_raw.bit_xor('0102F3', 'F30201')
FROM dual;
CAST_FROM_BINARY_DOUBLE
Return the RAW representation of a binary_double value utl_raw.cast_from_binary_double(n IN BINARY_DOUBLE, endianess IN PLS_INTEGER DEFAULT 1) RETURN RAW;
SELECT utl_raw.cast_from_binary_double(123.45) FROM dual;
CAST_FROM_BINARY_FLOAT
Return the RAW representation of a binary_float value utl_raw.cast_from_binary_float(n IN BINARY_
FLOAT, endianess IN PLS_INTEGER DEFAULT 1) RETURN RAW;
SELECT utl_raw.cast_from_binary_float(123.45)
FROM dual;
CAST_FROM_BINARY_INTEGER
Return the RAW representation of a binary_integer value utl_raw.cast_from_binary_integer(
n        IN BINARY_INTEGER,
endianess IN PLS_INTEGER DEFAULT 1) RETURN RAW;
SELECT utl_raw.cast_from_binary_integer(100)
FROM dual;
CAST_FROM_NUMBER
Returns the binary representation of a NUMBER in RAW utl_raw.cast_from_number(n IN NUMBER) RETURN RAW;
SELECT utl_raw.cast_from_number(100)
FROM dual;
CAST_TO_BINARY_DOUBLE
Perform a casting of the binary representation of the raw into a binary_double.cast_to_binary_double(
r        IN RAW,
endianess IN PLS_INTEGER DEFAULT 1) RETURN BINARY_DOUBLE;
SELECT utl_raw.cast_to_binary_double('405EDCCCCCCCCCCD') FROM dual;
CAST_TO_BINARY_FLOAT
Perform a casting of the binary representation of the raw into a binary_float utl_raw.cast_to_binary_float(
n        IN BINARY_FLOAT,
endianess IN PLS_INTEGER DEFAULT 1) RETURN RAW;
SELECT utl_raw.cast_to_binary_float('42F6E666') FROM dual;
CAST_TO_BINARY_INTEGER
Perform a casting of the binary representation of the raw into a binary integer utl_raw.cast_to_binary_integer(
r        IN RAW,
endianess IN PLS_INTEGER DEFAULT 1) RETURN BINARY_INTEGER;
SELECT utl_raw.cast_to_binary_integer('00000064')
FROM dual;
CAST_TO_NUMBER
Perform a casting of the binary representation of the number (in RAW) into a NUMBER utl_raw.cast_to_number(r IN RAW) RETURN NUMBER;
SELECT utl_raw.cast_to_number('C202')
FROM dual;
CAST_TO_NVARCHAR2
Converts a RAW represented using n data bytes into NVARCHAR2 with n data bytes utl_raw.cast_to_nvarchar2(r IN RAW) RETURN NVARCHAR2; set serveroutput on
BEGIN
FOR i IN 100..200 LOOP
dbms_output.put_line(
utl_raw.cast_to_nvarchar2(TO_CHAR(i)));
END LOOP;
END LOOP;
END;
/
CAST_TO_RAW
Converts a VARCHAR2 represented using n data bytes into a RAW with n data bytes utl_raw.cast_to_raw(r IN RAW) RETURN RAW;
SELECT utl_raw.cast_to_raw('ABC')
FROM dual;
CAST_TO_VARCHAR2oracle decimal类型
To extract a substring from a BLOB using a PL/SQL program use dbms_lob.substr(). The problem is that it returns a string in hexadecimal characters.
CAST_TO_VARCHAR2 turns the hexadecimal string into readable ascii format.utl_raw.cast_to_nvarchar2(r IN RAW) RETURN VARCHAR2;
set serveroutput on
BEGIN
FOR i IN 100..200 LOOP
dbms_output.put_line(utl_raw.cast_to_varchar2(TO_CHAR(i)));  END LOOP;
END;
/
COMPARE
Compares raw r1 against raw r2. Returns 0 if r1 and r2 are identical, otherwise, returns the position of the first byte from r1 that does not match r2utl_rawpare(r1 IN RAW, r2 IN RAW,
pad IN RAW DEFAULT NULL) RETURN NUMBER;
SELECT utl_rawpare(utl_raw.cast_to_raw('ABC'), utl_raw.cast_to_raw('ACC'))
FROM dual;
CONCAT
Concatenate a set of 12 raws into a single raw (up to 32K)at(r1 IN RAW DEFAULT NULL, r2  IN RAW DEFAULT NULL,
r3  IN RAW DEFAULT NULL,
r4  IN RAW DEFAULT NULL,
r5  IN RAW DEFAULT NULL,
r6  IN RAW DEFAULT NULL,
r7  IN RAW DEFAULT NULL,
r8  IN RAW DEFAULT NULL,
r9  IN RAW DEFAULT NULL,
r10 IN RAW DEFAULT NULL,
r11 IN RAW DEFAULT NULL,
r12 IN RAW DEFAULT NULL) RETURN RAW;
SELECT at('A','41','B','42') FROM dual;
CONVERT
vert(
r            IN RAW,
to_charset  IN VARCHAR2,
from_charset IN VARCHAR2) RETURN RAW;
DECLARE
fr_charset CONSTANT VARCHAR2(30) :=
Convert raw from one character to a different character set and return the resulting raw  SUBSTR(SYS_CONTEXT('USERENV', 'LANGUAGE'),
INSTR(SYS_CONTEXT('USERENV', 'LANGUAGE'),'.')+1);
to_charset VARCHAR2(30) := 'TR8EBCDIC1026S';
rawvar    RAW(100);
BEGIN
dbms_output.put_line(fr_charset);
dbms_output.put_line(to_charset);
rawvar := vert(UTL_RAW.CAST_TO_RAW('Morgan'),  'AMERICAN_AMERICA.'||to_charset,
'AMERICAN_AMERICA.'||fr_charset);
dbms_output.put_line(rawvar);
END;
/
COPIES
Return n copies of r concatenated together pies(r IN RAW, n IN NUMBER) RETURN RAW;
SELECT pies('A', 6)
FROM dual;
LENGTH
Return the length in bytes of a raw utl_raw.length(r IN RAW) RETURN NUMBER;
SELECT utl_raw.length('ABC')
FROM dual;
OVERLAY
Overlay the specified portion of target raw with overlay raw, starting from byte position pos of target and proceeding for len bytes utl_raw.overlay(overlay_str IN RAW, target IN RAW, pos IN BINARY_INTEGER DEFAULT 1,
len IN BINARY_INTEGER DEFAULT NULL,
pad IN RAW DEFAULT NULL) RETURN RAW; SELECT utl_raw.overlay('1', 'AAABBBCCC', 4) FRO
M dual;
REVERSE
Reverse a byte sequence in raw r from end to end verse(r IN RAW) RETURN RAW;
SELECT verse('123')
FROM dual;
SUBSTR
Return a substring portion of raw r beginning at pos for len bytes utl_raw.substr(r IN RAW,
pos IN BINARY_INTEGER,
len IN BINARY_INTEGER DEFAULT NULL) RETURN RAW;
set serveroutput on
DECLARE
tmp  VARCHAR2(250) := '';
vraw RAW(200) := utl_raw.cast_to_raw('ABCDEABCDEABCDE'); BEGIN

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。