以hr用户下的employees、departments、locations这三张表为列,sin1得到的是雇员的全名和对应的部门id,并将该部门的id作为sin2查询时的条件,sin2得到的是该部门id所对应的部门名和对应的位置id,并将该位置id作为sin3查询使得条件,最后sin3得到的就是该位置id所应得城市,并且在sin3这个循环里将sin1里雇员的全名,sin2里的部门名以及sin3里的city作为一条记录插入到sin_insert表里.
附上代码:
first:
createtablesin_insert(full_namevarchar2(50),department_namevarchar2(30),city
varchar2(30));
then:
createorreplaceproceduretestloop
as
begin
forsin1in(selectfirst_name||last_namefull_name,department_idfrom
employees)loop
forsin2in(selectdepartment_name,location_idfromdepartmentswhere
department_id=sin1.department_id)loop
forsin3in(selectcityfromlocationswhere
location_id=sin2.location_id)loop
insertintosin_insertvalues
(sin1.full_name,sin2.department_name,sin3.city);
endloop;
endloop;
endloop;
end;
<h4class=\"ask\">追问
能运行,没有插入数据!方便吗?能加QQ说嘛?
|