Request user data conditionally in PL / SQL

0

Problem

Using the Oracle SQL Developer version 4.1.0.19 I am asking the user for data but the program asks me for more data than necessary, skipping the statements if and elsif .

Code

declare
  v_nombre varchar2(500);
  v_caracteres number;
  v_edad number;
begin
  select '&nombre' into v_nombre from dual;
  dbms_output.put_line(v_nombre);
  v_caracteres := length(replace(v_nombre,' ',''));
  --dbms_output.put_line(replace(v_nombre,' ',''));
  if v_caracteres > 50 then
    dbms_output.put_line('Tu nombre parece un poema! tiene ' || v_caracteres || ' letras');
  elsif v_caracteres <= 50 then
    begin
      dbms_output.put_line('Tu nombre tiene '|| v_caracteres || ' letras');
      v_edad := '&edad';
      if v_edad > 17 then
        dbms_output.put_line('Ese mi chavoruco ¿Qué transacción por tu base de datos?');
       else
         dbms_output.put_line('Estás chavo!');
      end if;
    end;
  end if;
  EXCEPTION
        WHEN others THEN
          dbms_output.put_line('diagonal cero');
end;
/

I have tried the same using : but the result is similar. I hope someone knows some way to solve this problem. I understand that the code has no error, but the desired result would be that by giving it a name with more than 50 characters it would not ask for anything else.

    
asked by Ruslan López 23.01.2016 в 17:24
source

2 answers

2

Can not. PL / SQL is not interactive. What happens is that SQL Plus (or the program you use to communicate with the DB) accepts "parameters". These work in the following way:

  • SQL Plus asks for a value for every &parámetro
  • Replace each parameter with the value entered
  • Send the resulting text to the DB for execution
  • Therefore, the DB never sees the & , but receives the code with the parameter already replaced. The order of the parameters is prior to the execution of the code.

        
    answered by 23.01.2016 в 21:14
    0

    to request the data in oracle 10g would be like this:

    SELECT last_name, salary, hire_date FROM employees
       WHERE last_name=:Apellido
    
        
    answered by 31.07.2016 в 18:59