PL/SQL详例和解释(六)
mp_name%type index by pls_integer;
emp_id_cc emp_id_type;
emp_name_cc emp_name_type;
start_time integer;
end_time integer;
v_total number;
em_id number:=10000;
begin
select count(*) into v_total from it_employees;
for i in 1..v_total loop
em_id:=em_id+1;
select employee_id into emp_id_cc(i) from it_employees where employee_id=em_id;
select first_name||'.'||last_name name into emp_name_cc(i) from it_employees where employee_id=em_id;
end loop;
start_time:=dbms_utility.get_time;
for i in 1..v_total loop
insert into cc_emp (emp_id, emp_name)values (emp_id_cc(i),emp_name_cc(i));
end loop;
end_time:=dbms_utility.get_time;
SYS.DBMS_OUTPUT.PUT_LINE('For total time is: '||(end_time-start_time));
start_time:=dbms_utility.get_time;
forall i in 1..v_total
insert into cc_emp (emp_id, emp_name)values (emp_id_cc(i),emp_name_cc(i));
end_time:=dbms_utility.get_time;
SYS.DBMS_OUTPUT.PUT_LINE('Forall total time is: '||(end_time-start_time));
commit;
end;
--使用bulk collect into 批量检索数据并存放到数组中
declare
type emp_id_type is table of it_employees.employee_id%type;
type emp_name_type is table of varchar2(30);
emp_id emp_id_type;
emp_name emp_name_type;
begin
select employee_id, first_name||'.'||last_name name bulk collect into emp_id, emp_name from it_employees;---使用select bluk collect into 不再需要为嵌套表初始化和扩展，自动完成。
for i in emp_id.first..emp_id.last loop
dbms_output.put_line('Employee ID is: '||emp_id(i));
dbms_output.put_line('Employee Name is: '||emp_name(i));
end loop;
end;
--用bulk collect into实现将员工信息放入到员工信息类的集合中
declare
cursor emp_info_cur is s
elect employee_id, first_name||'.'||last_name, salary from it_employees;
type emp_rec is record (emp_id it_employees.employee_id%type, emp_name varchar(30),emp_salary it_employees.salary%type);--定义一种类型
type emp_tab_type is table of emp_rec;
emp_tab emp_tab_type;--定义一个集合
begin
open emp_info_cur;
loop
fetch emp_info_cur bulk collect into emp_tab;
exit when emp_tab.count=0;
for i in emp_tab.first..emp_tab.last loop
dbms_output.put('Employee ID is: '||emp_tab(i).emp_id);
DBMS_OUTPUT.PUT(' Employee name is: '||emp_tab(i).emp_name);
DBMS_OUTPUT.PUT(' Employee Salary is: '||emp_tab(i).emp_salary);
DBMS_OUTPUT.PUT_LINE('');
end loop;
end loop;
end;
--含参过程实现同一地址的员工名字输出
create or replace procedure name_procedure (loc_id in number, v_first_name out varchar2, v_last_name out varchar2)
as
cursor name_cur is select e.first_name, e.last_name from it_employees e, departments d where e.department_id=d.department_id and d.location_id=loc_id;
begin
for i in name_cur loop
v_first_name:=i.first_name;
v_last_name:=i.last_name;
DBMS_OUTPUT.PUT_LINE('Name is: '||v_first_name||'.'||v_last_name);
end loop;
exception
when others then
dbms_output.put_line('Error!');
end name_procedure;
declare
loc_id departments.location_id%type:=&location_id;
v_first_name it_employees.first_name%type;
v_last_name it_employees.last_name%type;
begin
name_procedure(loc_id,v_first_name,v_last_name);
end;
--function实现员工工资输出
create or replace function salary_fuc (emp_id in number)
return number
is
v_salary number(10);
begin
select salary into v_salary from it_employees where employee_i