oracle 一問

我create一個table customer
咁入面有一個credit card既number(16)
我點樣check佢入左16個digit?

create table payment
(
        hkid                    varchar2(8) not null primary key references customer,
        method                  char(4) not null check(method='cash' or method='card'),
        card_no                 number(16),    <----------呢個
        card_owner              varchar2(30),
        expiry_date             date
);

原帖由 dX_ 於 2009-3-22 22:15 發表
我create一個table customer
咁入面有一個credit card既number(16)
我點樣check佢入左16個digit?

create table payment
(
        hkid                    varchar2(8) not null primary key references customer, ...


You can't at database level (besides stored procedure). It should be the business of application program.
credit card number should be a char(16) field.

However, there is a hack. I have not seen one credit card number with first digit be zero. You may check if a number is larger than 1,000,000,000,000,000

TOP

原帖由 thinkpanda 於 2009-3-22 22:27 發表


You can't at database level (besides stored procedure). It should be the business of application program.
credit card number should be a char(16) field.

However, there is a hack. I have not seen on ...


但係如果用char又要check 係咪數字...
>100000...我都諗過..好似唔係咁好..
咁如果用char(16) 點check 入左16個數字?
麻煩哂


另外想問如果要出error message係咪淨係可以係trigger度用raise_application_error先得?

TOP

原帖由 dX_ 於 2009-3-22 22:31 發表


但係如果用char又要check 係咪數字...
>100000...我都諗過..好似唔係咁好..
咁如果用char(16) 點check 入左16個數字?
麻煩哂


另外想問如果要出error message係咪淨係可以係trigger度用raise_application_error先得?


呢D野應該係application 度做,唔好咁依賴stored procedure.

TOP

原帖由 thinkpanda 於 2009-3-22 22:38 發表


呢D野應該係application 度做,唔好咁依賴stored procedure.


姐係係trigger做?
我份project只係可以交create.sql,drop.sql同trigger.sql..

TOP

原帖由 dX_ 於 2009-3-22 22:31 發表
但係如果用char又要check 係咪數字...
>100000...我都諗過..好似唔係咁好..
咁如果用char(16) 點check 入左16個數字?
麻煩哂
另外想問如果要出error message係咪淨係可以係trigger度用raise_application_error先得?


NUMBER(16)
length(to_char(card_no))

CHAR
length(card_no)

TOP

In oracle, we use "varchar2" in most cases but not "char", read http://akdora.wordpress.com/2009/02/09/oracle-varchar2-vs-char/ for details.

If you declare "card_no" as a varchar2(16) column, try "check(length(card_no)=16 and translate(card_no, '@0123456789','@') is null)"

[ 本帖最後由 henrywho 於 2009-3-25 00:47 編輯 ]

TOP