oracle约束数据类型

wxclj1h5  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(438)

我创建了一个数据类型为smallint的表。

create table test(
     A smallint, 
     constraints ACHECK check(A between 1 and 5));

我想添加限制,只允许用户添加1~5之间的整数值范围。但是,即使有限制,我仍然能够插入自动取整的浮点值。

insert into test values(3.2);

如何使此代码显示错误?我不允许更改数据类型。

nukf8bse

nukf8bse1#

这太长了,不能发表评论。
你不能轻易地做你想做的事。oracle正在转换输入值 3.2 一个整数。整数满足约束。价值 3 就是插入的东西。转换发生在幕后。甲骨文的开发者认为这种转换是一件“好事”。
您可以通过将列声明为数字,然后检查它是否为整数来解决此问题:

create table test (
     A number, 
     constraints ACHECK check(A between 1 and 5 and mod(A, 1) = 0)
);
krcsximq

krcsximq2#

至于要求是不改变数据类型,但它没有说明任何关于创建新对象的内容,我提出了一个非常复杂的解决方案,它可以做到这一点,但到目前为止,我更希望将数据类型改为数字并使用普通约束。
这里的主要问题是,值的取整是在解析语句之后执行之前完成的。作为一种内在机制,你不能做任何事。如果使用触发器并显示 :NEW 在插入或更新列之前。
然而,有一个技巧。f、 g.a.在解析之前将原始值传递给语句。因此,使用带有一个处理程序和两个触发器的策略就可以达到目的。
让我详细谈谈

SQL> create table testx ( xsmall smallint );

Table created.

SQL> create table tracex ( id_timestamp timestamp , who_was varchar2(50) , sqltext varchar2(4000) );

Table created.

SQL> create or replace procedure pr_handle_it (object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2) 
is
  begin
    -- dbms_output.put_line('SQL was: ' || SYS_CONTEXT('userenv','CURRENT_SQL'));
    insert into tracex values ( systimestamp , sys_context('userenv','session_user') , sys_context('userenv','current_sql') );
    commit;
end;
/

Procedure created.

SQL> BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'MYSCHEMA',
      object_name => 'TESTX',
      policy_name => 'MY_NEW_POLICY',
      audit_condition => null,
      audit_column => 'XSMALL',
      handler_schema => 'MYSCHEMA',
      handler_module => 'PR_HANDLE_IT',
      enable => true,
      statement_types => 'INSERT, UPDATE, DELETE'
    );
END;
/

PL/SQL procedure successfully completed.

SQL> create or replace trigger trg_testx before insert or update on testx 
referencing new as new old as old
for each row
begin
if inserting or updating
then
    dbms_output.put('  New value is: ' || :new.xsmall);
    dbms_output.put_line('TRIGGER : The value for CURRENT_SQL is '||sys_context('userenv','current_sql'));
    insert into tracex values ( systimestamp , sys_context('userenv','session_user') , sys_context('userenv','current_sql') );
end if;
end;
/

Trigger created.

SQL> create or replace trigger trg_testx2 after insert or update on cpl_rep.testx 
referencing new as new old as old
for each row
declare
v_val pls_integer;
begin
    if inserting or updating 
    then
        select regexp_replace(sqltext,'[^0-9]+','') into v_val 
        from ( select upper(sqltext) as sqltext from tracex order by id_timestamp desc ) where rownum = 1 ;
        if v_val > 5 
        then 
            raise_application_error(-20001,'Number greater than 5 or contains decimals');
        end if;
    end if;
end ;
/ 

Trigger created.

这些是要素:
-一个跟踪表,用于在解析之前获取查询
-一个fga策略覆盖更新和插入
-处理程序的一个过程
-两个触发器一个在前面(获取查询和原始值),一个在后面,用于计算语句中的值,而不是取整的值。
由于触发器是按顺序计算的,before-one插入带小数点的原始值并在取整之前执行,after分析存储在跟踪表中的值以引发异常。

SQL> insert into testx values ( 1 ) ;

1 row created.

SQL>  insert into testx values ( 5 ) ;

1 row created.

SQL>  insert into testx values ( 2.1 ) ;
 insert into testx values ( 2.1 )
             *
ERROR at line 1:
ORA-20001: Number greater than 5 or it contains decimals
ORA-06512: at "CPL_REP.TRG_TESTX2", line 10
ORA-04088: error during execution of trigger 'CPL_REP.TRG_TESTX2'

SQL> insert into testx values ( 6 ) ;
insert into testx values ( 6 )
            *
ERROR at line 1:
ORA-20001: Number greater than 5 or it contains decimals
ORA-06512: at "CPL_REP.TRG_TESTX2", line 10
ORA-04088: error during execution of trigger 'CPL_REP.TRG_TESTX2'

总结:正如@gordon linoff所说的,要实现所要求的目标没有简单的方法。我相信这个方法对于要求来说是非常复杂的。我只是想证明这是可能的。

相关问题