|
1月8日
--学习内容:综合练习,游标,函数,过程,包体
------
--找出各部门,各岗位的平均工资
--如果某个部门没有某岗位,就写0
--格式如下
--部门名称-岗位1-岗位2-岗位3
--部门1 0 100 200
--部门2 200 0 300
--部门3 100 111 0
--创建两个游标来接收GROUP JOB和GROUP DEPTNO的值
--然后用FOR循环把游标里面的值存到创建的表里面
--把值写入表,应该是insert into jacky_cur values()
--select job,avg(sal) from (select * from jacky_emp where deptno = 30) group by job;
--上面语句得到编号为30部门的岗位平均工资
---想一下,怎么在某个部门里面找出存在的岗位
--先用jacky_cur表中的job来check(部门编号)
--check的结构:
---------成功打出空表格--------------
declare
cursor deptno_cur is select deptno from jacky_emp group by deptno;
cursor job_cur is select job from jacky_emp group by job;
str_sql varchar2(500);
crt_tab varchar2(200);
begin
--建表
crt_tab := 'create table jacky_cur(id number(3))';
execute immediate crt_tab;
--用下面的游标来写表行
for i in deptno_cur
loop
str_sql := 'insert into jacky_cur values(:1)';
execute immediate str_sql using i.deptno;
--动态执行插入操作
end loop;
for x in job_cur
loop
str_sql := 'alter table jacky_cur add '||x.job||' varchar2(10)';
--上面''里面的空格也很重要,错了好多次了.要和自己写SQL语句一模一样
execute immediate str_sql;
--execute immediate str_sql using x.job;
--动态执行插入操作
dbms_output.put_line(str_sql);
end loop;
end;
---------成功打出空表格--------------
---修改中
declare
str_sql varchar2(500);
crt_tab varchar2(200);
cursor deptno_cur is select deptno from jacky_emp group by deptno;
cursor job_cur is select job from jacky_emp group by job;
cursor job_avg(d_no jacky_emp.deptno%type)
is select job,avg(sal) as fee from (select * from jacky_emp where deptno = d_no) group by job;
--上面返回某个部门的各种职业的平均工资
begin
--建表
crt_tab := 'create table jacky_cur(id number(3))';
execute immediate crt_tab;
--用下面的游标来写表行
for i in deptno_cur
loop
str_sql := 'insert into jacky_cur values(:1)';
execute immediate str_sql using i.deptno;
--动态执行插入操作
end loop;
for x in job_cur
loop
str_sql := 'alter table jacky_cur add '||x.job||' varchar2(10)';
--上面''里面的空格也很重要,错了好多次了.要和自己写SQL语句一模一样
execute immediate str_sql;
--execute immediate str_sql using x.job;
--动态执行插入操作
end loop;
for z in deptno_cur
loop
if (z.deptno)
end loop;
end;
----
-----------------------------------------------------------------
declare
cursor deptno_cur is select deptno from jacky_emp group by deptno;
--cursor job is select job from jacky_emp group by job;
str_sql varchar2(500);
crt_tab varchar2(200);
begin
--建表
crt_tab := 'create table jacky_cur(id number(3))';
execute immediate crt_tab;
--用下面的游标来写表行
--open deptno_cur;
for i in deptno_cur
loop
str_sql := 'insert into jacky_cur values(:1)';
execute immediate str_sql using i.deptno;
--动态执行插入操作
end loop;
end;
-----------------------------------------------------------------
---用FOR循环是不用开游标的...-____-!
----下面代码研究了好久才得出的
declare
str_sql varchar2(500);
begin
str_sql:='
create table jacky_oh (
id number(3),
name varchar2(6),
age number(2),
native varchar2(5),
position varchar2(6),
c_group varchar2(6),
score number(3)
)
';
execute immediate str_sql;
--str_sql里面不能用;号,换句话说,就是execute immediate解释代码时,不支持同时使用两个DDL命令 -___-!;
end;
---------------------------------------
declare
str_sql varchar2(500);
begin
str_sql:='create table jacky_oh(
id number(3),
name varchar2(6)
);';
--execute immediate str_sql;
execute immediate str_sql;
end;
----下面这个没有错误.找找原因
declare
str_sql varchar2(500);
begin
str_sql := 'create table jacky_oh(
id number(3),
name varchar2(6))
';
execute immediate str_sql;
end;
/*declare
begin
execute immediate 'create table jacky_cur(id number(3))';
end;*/
---------表备份
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 1500.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
create table jacky_emp (
empno number(5),
ename varchar2(10),
job varchar2(9),
mgr number(5),
hiredate date,
sal number(9,2),
comm number(9,2),
deptno number(6)
);
insert into jacky_emp values(7369,'SMITH' ,'CLERK', 7902,'17-12月-80' ,800.00 ,20);
insert into jacky_emp values(7499, 'ALLEN','SALESMAN',7698,'20-2月-81',1600.00,300.00,30);
insert into jacky_emp values(7521, 'WARD','SALESMAN',7698,'22-2月-81',1250.00,500.00,30);
insert into jacky_emp values(7566, 'JONES','MANAGER',7839,'2-4月-81',2975.00,null,20);
insert into jacky_emp values(7654, 'MARTIN','SALESMAN',7698,'28-9月-81',1250.00,1400.00,30);
insert into jacky_emp values(7698, 'BLAKE','MANAGER',7839,'1-5月-81',2850.00,null,30);
insert into jacky_emp values(7782,'CLARK','MANAGER',7839,'9-6月-81',2450.00,null,10);
insert into jacky_emp values(7788,'SCOTT','ANALYST',7566,'19-4月-87',1500.00,null,20);
insert into jacky_emp values(7839,'KING','PRESIDENT',null,'17-11月-81',5000.00,null,10);
insert into jacky_emp values(7844,'TURNER','SALESMAN',7698,'9-8月-81',1500.00,0.00 ,30);
insert into jacky_emp values(7876,'ADAMS','CLERK',7788,'23-5月-87',1100.00,null,20);
insert into jacky_emp values(7900,'JAMES','CLERK',7698,'3-12月-81',950.00,null,30);
insert into jacky_emp values(7902,'FORD','ANALYST',7566,'3-12月-81',3000.00,null,20);
insert into jacky_emp values(7934,'MILLER','CLERK',7782,'23-1月-82',1300.00,null,10);
---测试版
declare
cursor deptno_cur
is select deptno from jacky_emp group by deptno;
cursor job_cur is select job from jacky_emp group by job;
str_sql varchar2(500);
crt_tab varchar2(200);
job_gz number(8);
--cursor job_avg(d_no jacky_emp.deptno%type)
-- is select job,avg(sal) as fee from (select * from jacky_emp where deptno = d_no) group by job;
--上面返回某个部门的各种职业的平均工资
/*cursor job_avg(d_no jacky_emp.deptno%type)
is select fee (select job ,avg(sal) as fee from (select * from jacky_emp where deptno = d_no) group by job) where job = 'CLERK';*/
cursor job_avg(d_no jacky_emp.deptno%type,j_name jacky_emp.job%type)
is select fee from (select job ,avg(sal) as fee from (select * from jacky_emp where deptno = d_no) group by job) where job = j_name;
/*cursor desc_job
is desc jacky_cur;*/
---可以写 平均工资 = cursor(部门,职位)
begin
--建表
crt_tab := 'create table jacky_cur(id number(3))';
execute immediate crt_tab;
--用下面的游标来写表行
for i in deptno_cur
loop
str_sql := 'insert into jacky_cur values(:1)';
execute immediate str_sql using i.deptno;
--动态执行插入操作
end loop;
for x in job_cur
loop
str_sql := 'alter table jacky_cur add '||x.job||' varchar2(10)';
--上面''里面的空格也很重要,错了好多次了.要和自己写SQL语句一模一样
execute immediate str_sql;
--execute immediate str_sql using x.job;
--动态执行插入操作
--dbms_output.put_line(str_sql);
end loop;
for z in deptno_cur
loop
for zz in job_cur
loop
open job_avg(z.deptno,zz.job);
fetch job_avg into job_gz;
--如果10里面有jacky_cur里面的岗位就insert平均工资进10的某岗位字段
if job_avg%found
then
dbms_output.put_line(job_gz);
else
dbms_output.put_line('无');
end if;
close job_avg;
end loop;
dbms_output.put_line('--------------------------------');
end loop;
end;
--测试版
-------------最终版--每个部门各个岗位的平均工资
declare
cursor deptno_cur
is select deptno from jacky_emp group by deptno;
cursor job_cur is select job from jacky_emp group by job;
str_sql varchar2(500);
crt_tab varchar2(200);
job_gz number(8);
--cursor job_avg(d_no jacky_emp.deptno%type)
-- is select job,avg(sal) as fee from (select * from jacky_emp where deptno = d_no) group by job;
--上面返回某个部门的各种职业的平均工资
/*cursor job_avg(d_no jacky_emp.deptno%type)
is select fee (select job ,avg(sal) as fee from (select * from jacky_emp where deptno = d_no) group by job) where job = 'CLERK';*/
cursor job_avg(d_no jacky_emp.deptno%type,j_name jacky_emp.job%type)
is select fee from (select job ,avg(sal) as fee from (select * from jacky_emp where deptno = d_no) group by job) where job = j_name;
--上面返回指定部门的指定JOB的平均工资
/*cursor desc_job
is desc jacky_cur;*/
---可以写 平均工资 = cursor(部门,职位)
begin
--建表
crt_tab := 'create table jacky_cur(deptno number(3))';
execute immediate crt_tab;
--用下面的游标来写表行
/* for i in deptno_cur
loop
str_sql := 'insert into jacky_cur values(:1)';
execute immediate str_sql using i.deptno;
--动态执行插入操作
end loop;*/
for x in job_cur
loop
str_sql := 'alter table jacky_cur add '||x.job||' varchar2(10)';
--上面''里面的空格也很重要,错了好多次了.要和自己写SQL语句一模一样
execute immediate str_sql;
--execute immediate str_sql using x.job;
--动态执行插入操作
--dbms_output.put_line(str_sql);
end loop;
for z in deptno_cur
loop
str_sql := 'insert into jacky_cur values'||'('||z.deptno;
for zz in job_cur
loop
open job_avg(z.deptno,zz.job);
fetch job_avg into job_gz;
--如果10里面有jacky_cur里面的岗位就insert平均工资进10的某岗位字段
if job_avg%found
then
--dbms_output.put_line(job_gz);
str_sql := str_sql||','||job_gz;
else
--dbms_output.put_line('无');
--没有则写入0到数据库中
str_sql := str_sql||','||0;
end if;
close job_avg;
end loop;
str_sql := str_sql||')';
execute immediate str_sql;
dbms_output.put_line('成功执行'||z.deptno);
end loop;
end;
|
