开发者

How to use PL SQL Constants in Insert statement

开发者 https://www.devze.com 2023-02-22 14:43 出处:网络
I am new to PL SQL Code and need help writing Insert query statement . eg: I want to insert Employee in Employees table where only employee name changes but the city is constant.

I am new to PL SQL Code and need help writing Insert query statement . eg: I want to insert Employee in Employees table where only employee name changes but the city is constant.

DECLARE
  emp_var_prefix varchar2(12) := 'QATEST开发者_开发技巧';
  emp_var_temp varchar2(12) := '';
  city constant varchar(30) := 'dallas'

begin
  DBMS_OUTPUT.ENABLE;
  for i in 1..2 loop
    emp_var_temp  := emp_var_prefix;
    emp_var_temp := emp_var_prefix ||i;

    INSERT INTO EMPLOYEE TABLE ('EMPLOYEE_NAME', 'CITY') values ('emp_var_temp', '<what should I put here for constant dallas for city name>');

    DBMS_OUTPUT.PUT_LINE(emp_var_temp);
  end loop;

END;


It appears that you would want something like

DECLARE
  l_emp_var_prefix varchar2(12) := 'QATEST';
  l_emp_var_temp varchar2(12) := '';
  l_city constant varchar(30) := 'dallas'
begin
  DBMS_OUTPUT.ENABLE;
  for i in 1..2 loop
    l_emp_var_temp := l_emp_var_prefix ||i;

    INSERT INTO EMPLOYEE(employee_name, city) 
      values (l_emp_var_temp, l_city );

    DBMS_OUTPUT.PUT_LINE(l_emp_var_temp);
  end loop;

END;

It is a bad idea in general to use variable names that are also column names-- that makes it exceedingly likely that you're going to encounter scope bugs where you intend to refer to a variable and the compiler inadvertently assumes that you meant to refer to the column. Those are rather nasty to debug. It's easier to adopt some convention like prefixing local variables with l_ to differentiate them from column names or parameters that can be prefixed with p_.

0

精彩评论

暂无评论...
验证码 换一张
取 消