Oracle APEX数据库应用表单PL/SQL三类验证需求咨询

Hey there, let's break down how to implement each of these three PL/SQL validations for your Oracle APEX form. I'll give you step-by-step guidance and the exact code snippets you need for each case.

First, navigate to your form's Validation page and click Create Validation. Choose the PL/SQL Function Returning Error Text type. Replace P100_INPUT_FIELD with your actual page item name in the code below:

BEGIN
  IF :P100_INPUT_FIELD IS NULL THEN
    RETURN '此字段不能为空，请输入内容';
  END IF;
  RETURN NULL; -- 返回NULL表示验证通过
END;

Note: You could also use APEX's built-in "Not Null" validation, but the PL/SQL approach lets you customize the error message or add extra logic if needed later.

Again, create a PL/SQL Function Returning Error Text validation. This example includes a check for valid numeric input (skip this if your page item is already set to a numeric type). Replace P100_NUMERIC_FIELD with your item name:

BEGIN
  -- 可选：验证输入为有效数字
  IF NOT REGEXP_LIKE(:P100_NUMERIC_FIELD, '^[0-9]+(\.[0-9]+)?$') THEN
    RETURN '请输入有效的数字';
  END IF;
  -- 检查值是否在1-100范围内
  IF TO_NUMBER(:P100_NUMERIC_FIELD) < 1 OR TO_NUMBER(:P100_NUMERIC_FIELD) > 100 THEN
    RETURN '输入值必须介于1和100之间';
  END IF;
  RETURN NULL;
END;

For date validations, make sure to handle the dd/mm/yyyy format correctly. We'll add exception handling to catch invalid date inputs too. Replace P100_DATE_FIELD with your date item name:

BEGIN
  -- 将输入字符串转换为日期（按dd/mm/yyyy格式），并比较范围
  IF TO_DATE(:P100_DATE_FIELD, 'dd/mm/yyyy') < TO_DATE('05/10/2000', 'dd/mm/yyyy') THEN
    RETURN '所选日期不得早于2000年10月5日';
  END IF;
  RETURN NULL;
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN '请输入正确的日期格式（dd/mm/yyyy）';
END;

Pro tip: If you're using APEX's date picker for this field, the format should already align, but the exception handling ensures you catch any manual input mistakes.

内容的提问来源于stack exchange，提问作者Fido Dido