使用TRUNC获取美元值，使用MOD（模）获取美分值：

SELECT ProductID,
       ProductName,
       TRUNC( Price ) AS dollars,
       MOD( Price, 1 ) * 100 AS cents
FROM   Products

对于您的示例数据，它将输出：

PRODUCTID | PRODUCTNAME | DOLLARS | CENTS
--------: | :---------- | ------: | ----:
        1 | Apple       |       5 |    17
        2 | Orange      |       6 |  52.7
        3 | Watermelon  |       7 |    12
        4 | Pear        |       3 |    25
        5 | Grape       |       4 |  12.3

小提琴

这是你需要的。

select substr(price, 1, instr(price, '.')-1) dollars, substr(price, instr(price, '.')+1) cents,  round(substr(price, instr(price, '.')),2)* 100 cents_rounded
from products

PRICE为VARHCAR2？为什么？它应该是一个NUMBER，因为它是一个数值。在这种情况下：

SQL> desc products
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCTID                                          NUMBER(38)
 PRICE                                              NUMBER
 PRODUCTNAME                                        VARCHAR2(255)

SQL> SELECT productid,
  2         productname,
  3         price,
  4         --
  5         TRUNC (price) dollars,
  6         (price - TRUNC (price)) * 100 cents
  7    FROM products;

 PRODUCTID PRODUCTNAM      PRICE    DOLLARS      CENTS
---------- ---------- ---------- ---------- ----------
         1 Apple            5,17          5         17
         2 Orange          6,527          6       52,7
         3 Watermelon       7,12          7         12
         4 Pear             3,25          3         25
         5 Grape           4,123          4       12,3

SQL>

查询：

Select productid,trunc(price) As  Dollars, nvl(substr(price - trunc(price),2),0) as Cents, ProductName As Product From Products;

输出：

| 产品ID|美元|分|产品名称|
| - -|- -|- -|- -|
| 一个|五个|十七岁|苹果公司|
| 2个|六个|五二七|橙子|
| 三个|七个|十二|西瓜|
| 四个|三个|二十五个|梨形|
| 五个|四个|一百二十三|葡萄色|
说明：
在这里，我选择ProductId，然后使用trunc函数删除任何小数（只有DOLLARS值），之后使用nvl函数替换null值（如果数字中没有美分），它将被0和行替换：substr(price - trunc(price),2)，它将从DOLLARS值中减去全价，得到cents。

备注：

您可以通过不同的方法和其他功能解决此问题查看本文以了解更多信息：Link

create or replace FUNCTION METE(p_number VARCHAR, p_parent VARCHAR, p_child VARCHAR) RETURN VARCHAR
IS
v___left VARCHAR2(4000);
v___right VARCHAR2(4000);
v___number VARCHAR2(4000);
BEGIN
SELECT p_number INTO v___number FROM DUAL WHERE p_number LIKE '%.%';
IF v___number IS NOT NULL THEN

select rtrim((select rtrim(p_number,(select substr(p_number,(select instr(p_number,'.')from dual)+1) from dual)) from dual),'.') 
into v___left
from dual;
select substr(p_number,(select instr(p_number,'.')from dual)+1)
INTO v___right
from dual;

RETURN NVL(v___left,'0')||' '||p_parent||' '||NVL(v___right,'0')||' '||p_child;
ELSIF v___number IS NULL THEN
select substr(p_number,(select instr(p_number,'.')from dual)+1) 
INTO v___right
from dual;
RETURN NVL(v___right,'0')||' '||p_parent;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
select substr(p_number,(select instr(p_number,'.')from dual)+1) 
INTO v___right
from dual;
RETURN NVL(v___right,'0')||' '||p_parent;
END;

select ProductID, mete(price,'Dollar','Cent') price, productname from Products;