How to use PL SQL Constants in Insert statement
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_
.
精彩评论