topic Re: Question about insert statemnet in a while true loop in Operating System - HP-UX

Question about insert statemnet in a while true loop

Deanna Tran — Wed, 12 Jun 2002 19:12:09 GMT

this is the script that I used to fillup the tablespace....and my question is the following..
I created a table with 3 field :
empid number, employee_name and salary number.
a. base on the script, the only value that will be changing is the numcount...but if i also want the system to generate the random data for the second value in term of varchar, how do i do that?
do i just add more insert state like
insert into employee value (numcount,'ddgdg')...?
b. if the table has 3 field, can i just insert 1 field instead of 3?
c. in addition, i want to perform update as well, within the while loop...as welll

declare
my_empid employee.empid%TYPE:=234;
my_salary salary.employee%TYPE:=10,000;
numcount number;
begin
numcount:=0;
while true loop
numcount:=numcount+1;
insert into employee value (numcount,'dfadf', 789.90);
update employee set salary = my_salary + 10,000
where empid = my_empid;
end loop;
end;
thank you for your help...

Re: Question about insert statemnet in a while true loop

Jeanine Kone — Wed, 12 Jun 2002 19:42:32 GMT

a) I'll have to think about a good way to do that one.

b) Yes, as long as the other columns do not have not null constraints. If your are not inserting into all of the fields in the order that they exist in the table than you specify which fields you are inserting as part of the insert statement. Your statement would look like "insert into employee (empid) values (numcount);"

c) for the update, I would do "update empolyee set salary = salary + 10000 where empid = numcount;" This would update the row that you just inserted.

Re: Question about insert statemnet in a while true loop

Deanna Tran — Wed, 12 Jun 2002 21:39:08 GMT

hi jeanine,
I thought of an idea, I learn on how to write
a cursor for loops, and in this i do the update
a. can a for loop to be in a while true loop..
i don't see any problem..as i write it, do you?
b. i can also create a procedure to do this?
c. please let me know if you have any idea on
how to generate a bunch of character.

create procedure tester0 IS
declare
high_pctinc constant number(10,5) :=1.50;
med_pctinc constant number (10,5) :=1.20;
low_pctinc constant number (10,5) :=1.10;
my_salary employee1.salary%TYPE;
my_empid employee1.empid%TYPE;
cursor employee1_crsr (low_end in VARCHAR2, high_end in VARCHAR2)
IS select empid, salary, from employee1
WHERE UPPER(substr(lastname,1,1))
BETWEEN UPPER(low_end) and UPPER(high_end);
numcount number;
begin
numcount:=0;
while true loop
numcount:=numcount+1;
insert into employee values (numcount,'dfadf', 789.90);
insert into employee (salary) values (800.00);
insert into employee (emp_name) values ('dlkjgl');
for my_employeerec IN employee1_crsr('B','X') loop
if my_emprec.empid < 100000 then
UPDATE employee1 set salary = my_employeerec.salary*high_pctinc;
where empid = numcount;
elsif my_emprec.empid < 3000000 then
update employee1 set salary = my_employeerec.salary*low_pctinc;
where empid = numcount;
else
update employee1 set salary = myemployeerec.salary*med_pctinc;
where empid = numcount;
end if;
end loop;
end loop;
end;