【oracle】练习与作业3

编写一个PL/SQL程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位。 答案： set serveroutput on; select ename 姓名，sal 薪水,job 职位 from scott.emp; declare x scott.emp.ename%type:=‘SMITH’; y scott.emp.sal%type; z scott.emp.job%type; begin sys.dbms_output.put_line(‘本题要求：编写一个SQL程序块,从emp表中显示名为SMITH的雇员的薪水和职位。’); select sal,job into y,z from scott.emp where ename = x; sys.dbms_output.put_line(‘更新后：员工姓名为：’||x||’，薪水为：’||y||‘元，职位是’||z); end; / 保存成 q1.txt;编写一个PL/SQL程序块，接受用户任意输入一个部门号（提示：接受用户任意输入，需要使用 ‘&’），从dept表中显示该部门的名称与所在位置。 答案： set serveroutput on; select deptno 部门编号,dname 部门名称, loc 所在位置 from scott.dept; declare var_deptno scott.dept.deptno%type:=&部门编号; var_dname scott.dept.dname%type; var_loc scott.dept.loc%type; begin sys.dbms_output.put_line(‘本题要求：编写一个PL/SQL程序块，接受用户任意输入一个部门号，从dept表中显示该部门的名称与所在位置。’); select dname,loc into var_dname,var_loc from scott.dept where deptno = var_deptno; sys.dbms_output.put_line(‘部门编号为’||var_deptno||‘的部门是：’||var_dname||’,它的所在位置是：’||var_loc); end; / 保存成q2.txt;编写一个PL/SQL程序块，注意使用%type属性定义变量，接受用户任意输入一个雇员号，从emp表中显示该雇员的整体薪水(即，工资sal加上奖金comm)。 答案： set serveroutput on; select empno 员工编号,sal 工资,comm 奖金 from scott.emp; declare x scott.emp.empno%type:=&员工编号; y scott.emp.comm%type; z scott.emp.sal%type; s scott.emp.sal%type; begin sys.dbms_output.put_line(‘本题要求：编写一个PL/SQL程序块，注意使用%type属性定义变量，接受用户任意输入一个雇员号，从emp表中显示该雇员的整体薪水’); select sal,nvl(comm,0) into z,y from scott.emp where empno = x; s := z+y; sys.dbms_output.put_line(‘员工编号为’||x||‘的员工的整体薪水是：’||s||‘元’); end; / 保存成q3.txt;某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理： 职位 增长工资 ------------ ----------- Clerk 500 Salesman 1000 Analyst 1500 Otherwise 2000 编写一个PL/SQL程序块, 接受用户任意输入一个雇员号，从emp表中实现上述加薪处理。（提示：使用多分支）

答案： set serveroutput on; sys.dbms_output.put_line(‘更新前： ‘); select empno 员工编号, ename 姓名, job 职位，sal 工资 from scott.emp; declare var_empno scott.emp.empno%type:=&员工编号; var_job scott.emp.job%type; var_sal scott.emp.sal%type; begin select job,sal into var_job,var_sal from scott.emp where empno = var_empno; if var_job = ‘CLERK’ then var_sal := var_sal+500; sys.dbms_output.put_line(‘更新成功！，工资上涨500元’); elsif var_job = ‘SALESMAN’ then var_sal := var_sal+1000; sys.dbms_output.put_line(‘更新成功！，工资上涨1000元’); elsif var_job = ‘ANALYST’ then var_sal := var_sal+1500; sys.dbms_output.put_line(‘更新成功！，工资上涨1500元’); else var_sal := var_sal+2000; sys.dbms_output.put_line(‘更新成功！，工资上涨2000元’); end if; update scott.emp set sal = var_sal where empno = var_empno; select job,sal into var_job,var_sal from scott.emp where empno = var_empno; sys.dbms_output.put_line(‘员工编号为’||var_empno||‘的职工的职位是’||var_job||’，他现在的工资是’||var_sal||‘元’); end; / 保存成q4.txt; 5. 编写一个PL/SQL程序块，利用显式游标，将emp表中雇员名字全部显示出来（要求：使用for循环）。 答案： set serveroutput on; declare cursor cur_emp is select * from scott.emp; begin sys.dbms_output.put_line(‘本题要求：编写一个PL/SQL程序块，利用显式游标，将emp表中雇员名字全部显示出来（要求：使用for循环）。’); sys.dbms_output.put_line(‘姓名’); for var_emp in cur_emp loop sys.dbms_output.put_line(var_emp.ename); end loop; end; / 保存成q5.txt 6. 编写PL/SQL程序块，通过使用显式游标，来显示dept表中的部门名称。（要求：使用while循环） 答案： set serveroutput on; declare cursor cur_dept is select dname from scott.dept; var_dname scott.dept.dname%type; begin sys.dbms_output.put_line(‘本题要求： 编写PL/SQL程序块，通过使用显式游标，来显示dept表中的部门名称。（要求：使用while循环）’); sys.dbms_output.put_line(‘部门名称’); open cur_dept; fetch cur_dept into var_dname; while cur_dept%found loop sys.dbms_output.put_line(var_dname); fetch cur_dept into var_dname; end loop; close cur_dept; end; / 保存成q6.txt; 7. 编写PL/SQL程序块，利用隐式游标，使用For循环，从emp表中查询该部门号为10的所有雇员的姓名，工作和工资。 答案： set serveroutput on; begin sys.dbms_output.put_line(‘编写PL/SQL程序块，利用隐式游标，使用For循环，从emp表中查询该部门号为10的所有雇员的姓名，工作和工资。’); for x in (select ename,job,sal from scott.emp where deptno = 10) loop sys.dbms_output.put_line(‘雇员姓名: ‘||x.ename||’,雇员工作：’||x.job||’,雇员工资： '||x.sal); end loop; end; / 保存成q7.txt; 8. 模仿课本P122的例题6-19，练习带参数的游标。要求将部门号作为参数，初始值为10号部门。使用游标时，部门号是由用户随意输入（提示：随意输入，需要使用 ‘&’）。要求显示出用户任意输入的部门号的所有雇员的姓名，工作和工资。 （要求：使用while循环） 答案： set serveroutput on; declare var_ename scott.emp.ename%type; var_job scott.emp.job%type; var_sal scott.emp.sal%type; cursor cur_emp(var_deptno in int :=10) is select ename,job,sal from scott.emp where deptno = var_deptno;

begin sys.dbms_output.put_line(‘模仿课本P122的例题6-19，练习带参数的游标。要求将部门号作为参数，初始值为10号部门。使用游标时，部门号是由用户随意输入（提示：随意输入，需要使用 ‘&’）。要求显示出用户任意输入的部门号的所有雇员的姓名，工作和工资。 （要求：使用while循环）’); open cur_emp(&部门编号); fetch cur_emp into var_ename,var_job,var_sal; while cur_emp%found loop sys.dbms_output.put_line(‘员工姓名：’||var_ename||’, 员工职位：’||var_job||’, 员工工资：’||var_sal); fetch cur_emp into var_ename,var_job,var_sal; end loop; close cur_emp; end; / 保存成q8.txt; 9. 模仿课本P122的例题6-19，练习带参数的游标。要求将部门号作为参数，无初始值。部门号可由用户随意输入。要求显示出用户任意输入的部门号的所有雇员的姓名，工作和工资。 （要求：使用for循环） 答案： set serveroutput on; declare cursor cur_emp(var_deptno in int) is select ename,job,sal from scott.emp where deptno = var_deptno; begin sys.dbms_output.put_line(‘模仿课本P122的例题6-19，练习带参数的游标。要求将部门号作为参数，无初始值。部门号可由用户随意输入。要求显示出用户任意输入的部门号的所有雇员的姓名，工作和工资。 （要求：使用for循环）’); for var in cur_emp(&部门编号) loop sys.dbms_output.put_line(‘员工姓名：’||var.ename||‘员工职位：’||var.job||‘员工工资：’||var.sal); end loop; end; / 保存成q9.txt; 10. 编写一个PL/SQL程序块，利用游标（显式或隐式游标），从emp表中对名字以“A”或“S”开头的所有雇员，按他们基本工资的10%给他们加薪。（提示：在循环中使用update更新） 答案： set serveroutput on; select ename 员工姓名,sal 工资 from scott.emp where ename like ‘A%’ or ename like ‘S%’; declare cursor cur_emp is select ename,sal from scott.emp where ename like ‘A%’ or ename like ‘S%’; var_sal scott.emp.sal%type; var_ename scott.emp.ename%type; begin sys.dbms_output.put_line(‘编写一个PL/SQL程序块，利用游标（显式或隐式游标），从emp表中对名字以“A”或“S”开头的所有雇员，按他们基本工资的10%给他们加薪。（提示：在循环中使用update更新）’); for var in(select * from scott.emp where ename like ‘A%’ or ename like ‘S%’) loop update scott.emp set sal = var.sal1.1 where ename = var.ename; end loop; sys.dbms_output.put_line(‘更新后：’); sys.dbms_output.put_line(‘员工姓名 工资’); sys.dbms_output.put_line(’---------- ----------’); open cur_emp; fetch cur_emp into var_ename,var_sal; while cur_emp%found loop sys.dbms_output.put_line(var_ename||’ '||var_sal); fetch cur_emp into var_ename,var_sal; end loop; close cur_emp; end; / 保存成q10.txt; 11. 编写一个PL/SQL程序块，利用游标（显式或隐式游标），在emp表中，所有雇员按他们基本工资的10%给他们加薪，但是只给加薪后的工资小于5000的雇员涨工资。 答案： set serveroutput on; select ename 员工姓名,sal 工资 from scott.emp; declare cursor cur_emp is select ename,sal from scott.emp; var_sal scott.emp.sal%type; var_ename scott.emp.ename%type; begin sys.dbms_output.put_line(‘编写一个PL/SQL程序块，利用游标（显式或隐式游标），在emp表中，所有雇员按他们基本工资的10%给他们加薪，但是只给加薪后的工资小于5000的雇员涨工资。’); for var in (select * from scott.emp) loop if var.sal1.1 < 5000 then update scott.emp set sal = var.sal*1.1 where ename = var.ename; end if; end loop; sys.dbms_output.put_line(‘更新后：’); sys.dbms_output.put_line(‘员工姓名 工资’); sys.dbms_output.put_line(’---------- ----------’); open cur_emp; fetch cur_emp into var_ename,var_sal; while cur_emp%found loop sys.dbms_output.put_line(var_ename||’ '||var_sal); fetch cur_emp into var_ename,var_sal; end loop; close cur_emp; end; / 保存成q11.txt;