oracle是否提供了一个内置的货币表让我用作约束？ [英] Does oracle provide a built-in currency table for me to use as constraints?

问题描述

我在Oracle 11g中创建一个表格，如下所示：

I'm creating a table in Oracle 11g like this:

CREATE TABLE EXAMPLE (
    ID VARCHAR2(10) PRIMARY KEY,
    NAME VARCHAR2(100), 
    SHORT VARCHAR2(50),
    CURRENCY CHAR(3)
);

可以创建外键约束，甚至 > code> CURRENCY 中的<>检查约束可以包含 ISO货币？

Is it possible to create a foreign key constraint or even a check constraint on CURRENCY to a built-in Oracle table that contains the ISO currencies?

对数据库没有很好的了解我还把输入其他解决方案作为输入，但是我不想为此保留自己的表，如果它是太多的工作，我会忍受用户错误。

Not having a great understanding of databases I also take as input other solutions that might be out there, however I do not want to maintain my own table for this, if it's too much work, I'll live with user errors.

谢谢。

推荐答案

注意：已编辑为包含@ ABCade的建议。

Note: Edited to include @A.B.Cade's suggestion.

不幸的是，您无法直接添加约束，因为货币数据可通过系统查看。但是，您可以使用该信息创建自己的表，然后创建外键。

Unfortunately you can't directly add a constraint, as the currencies data is available through a system a view. You can, however, create your own table with that information and then create the foreign key. Here's an example of how you can do it.

CREATE TABLE currencies (
  country VARCHAR2(30) PRIMARY KEY,
  currency VARCHAR2(3) NOT NULL
);

INSERT INTO currencies
  SELECT value country, utl_i18n.get_default_iso_currency(value) currency
    FROM v$nls_valid_values
   WHERE parameter = 'TERRITORY';

CREATE INDEX currencies_iso_idx ON currencies(currency) COMPUTE STATISTICS;

ALTER TABLE example ADD CONSTRAINT example_currency_fk FOREIGN KEY (currency)
  REFERENCES currencies(currency);

上面的例子包括货币价值的索引，因为我怀疑会是什么查询。

The example above includes an index on the currency value, as I suspect that will what you'll be querying on.

这篇关于oracle是否提供了一个内置的货币表让我用作约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！