PLSQL - 如何检查&输入是一个数字

debugcn 发表于 Dev

斯塔维尔

DECLARE
  TEAM_ID NUMBER := &INPUT;
  CURSOR C_WORKER IS
  SELECT FIRST_NAME, LAST_NAME
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = TEAM_ID;
  V_LAST_NAME EMPLOYEES.LAST_NAME%TYPE;
  V_FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
  OPEN C_WORKER;
    LOOP
      FETCH C_WORKER INTO V_LAST_NAME, V_FIRST_NAME;
      EXIT WHEN C_WORKER%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(V_LAST_NAME || ' ' || V_FIRST_NAME);
    END LOOP;
  CLOSE C_WORKER;
END;

我如何更改此代码以检查 TEAM_ID(&input) 是否为数字？如果是 - 打开光标，如果不是，打印“请写一个数字”。

最小值是 1，最大值是 TEAM_ID 的最大数量？或者它只是一个数字？

阿列克谢

要处理替换变量，您需要将它们用引号括起来并将它们视为字符串。

例如，这可能是一种执行您需要的操作的方法：

declare
    -- define a varchar2 variable to host your variable; notice the quotes
    vStringInput        varchar2(10) := '&input';
    vNumInput           number;
    vVal                number;
    -- define a parametric cursor, to avoid references to variables
    cursor cur(num number) is select num from dual;
begin
    -- try to convert the string to a number
    begin
        vNumInput :=  to_number(vStringInput);
    exception
        when others then 
            vNumInput := null;
    end;
    --
    -- check the values, to understand if it is a number ( vNumInput NULL or NOT NULL)
    -- and, in case it's a number, if it suits your criteria
    case
        when vNumInput is null then 
            dbms_output.put_line('not a number');
        when vNumInput < 1 then 
            dbms_output.put_line('less than 1');
        -- whatever check you need on the numeric value
        else
            -- if the value is ok, open the cursor
            open cur(vNumInput); 
            loop
                fetch cur into vVal;                
                exit when cur%NOTFOUND;
                dbms_output.put_line('value from cursor: ' || vVal);
            end loop;            
    end case;    
end;
/

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。