文章标题

declare
cursor aa_cur
is select empno,ename,job,sal from emp2;
v_emp aa_cur%rowtype;
begin
for v_emp in aa_cur loop
exit when aa_cur%notfound;
dbms_output.put_line(v_emp.empno||’–’||v_emp.ename||’–’||v_emp.job);
end loop;
end;

declare
cursor aa_cur
is select empno,ename,job,sal from emp2 where job=’MANAGER’;
v_emp aa_cur%rowtype;
begin
open aa_cur;
loop
fetch aa_cur into v_emp;
exit when aa_cur%notfound;
dbms_output.put_line(v_emp.empno||’–’||v_emp.ename||’–’||v_emp.job);
end loop;
close aa_cur;
end;

/任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。/
begin
update emp2 set sal=1000 where empno=7369;
if sql%isopen then
dbms_output.put_line(‘cursor is opening’);
else
dbms_output.put_line(‘cursor is closing’);
end if;
if sql%found then
dbms_output.put_line(‘OK’);
else
dbms_output.put_line(‘SORRY’);
end if;
if sql%notfound then
dbms_output.put_line(‘SORRY’);
else
dbms_output.put_line(‘haha’);
end if;
dbms_output.put_line(sql%rowcount);
exception
when no_data_found then
dbms_output.put_line(‘aaaa’);
when too_many_rows then
dbms_output.put_line(‘row too many’);
end;

/按顺序输出部门表 部门编号和部门地址/
declare
cursor dept_cur is select dname,loc from dept;
row_dept dept_cur%rowtype;
begin
open dept_cur;
fetch dept_cur into row_dept;
while dept_cur%found loop
dbms_output.put_line(row_dept.dname||’地址是’||row_dept.loc);
fetch dept_cur into row_dept;
end loop;
end;

/接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)/
–CURSOR cursor_name[(parameter[,parameter],…)] IS select_statement;
–定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]
declare
cursor bb_cur(in_deptno in number) is select * from emp where deptno=in_deptno;
v_row emp%rowtype;
begin
for v_row in bb_cur(20) loop
dbms_output.put_line(v_row.empno||’–’||v_row.ename||’–’||v_row.sal);
end loop;
end;

/向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)/
declare
cursor cc_cur (in_job in emp2.job%type) is select empno,ename,sal from emp2 where job=in_job;
v_row emp%rowtype;
begin
for v_row in cc_cur(‘MANAGER’) loop
dbms_output.put_line(v_row.empno||’–’||v_row.ename||’–’||v_row.sal);
end loop;
end;

/用更新游标来为雇员加佣金/
declare
cursor update_cur is select * from emp2 for update of sal;
v_row update_cur%rowtype;
v_salnew emp2.sal%type;
begin
for v_row in update_cur loop
FOR empInfo IN csr_Update LOOP
IF empInfo.SAL<1500 THEN
salInfo:=empInfo.SAL*1.2;
elsif empInfo.SAL<2000 THEN
salInfo:=empInfo.SAL*1.5;
elsif empInfo.SAL<3000 THEN
salInfo:=empInfo.SAL*2;
end if;
update emp2 set sal=v_salnew where current of update_cur;
end loop; /切记！在for in循环语句中不要关闭游标，否则会报错/
end;

/编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪/
declare
cursor update_cur is select * from emp2 where ename like ‘A%’ OR ename like ‘S%’ for update of sal;
v_row update_cur%rowtype;
v_salnew emp2.sal%type;
begin
for v_row in update_cur loop
dbms_output.put_line(v_row.ENAME||’原来的工资:’||v_row.SAL);
v_salnew :=v_row.sal*1.1;
update emp2 set sal=v_salnew where current of update_cur;
dbms_output.put_line(v_row.ENAME||’现在的工资:’||v_salnew);
end loop;
end;

/编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)50/
declare
cursor update_cur(in_job emp2.job%type) is select * from emp2 where job=in_job for update of comm;
v_row update_cur%rowtype;
v_newcomm emp2.comm%type;
begin
open update_cur(‘SALESMAN’);
fetch update_cur INTO v_row;
while update_cur%found loop
dbms_output.put_line(v_row.ENAME||’原来的奖金:’||v_row.comm);
v_newcomm:=v_row.comm+50;
update emp2 set comm=v_newcomm where current of update_cur;
dbms_output.put_line(v_row.ENAME||’现在的奖金:’||v_newcomm);
fetch update_cur INTO v_row;
end loop;
end;

/编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER/
declare
cursor update_cur is select * from emp2 order by hiredate for update of job;
v_row update_cur%rowtype;
v_num number:=2;
begin
open update_cur;
fetch update_cur into v_row;
while v_num>0 loop
dbms_output.put_line(v_row.ename||’–’||v_row.job);
update emp2 set job=’MANAGER’ where current of update_cur;
v_num:=v_num-1;
fetch update_cur into v_row;
end loop;
end;

/*编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的5%为他们加薪
如果增加的薪水大于100就取消加薪，并将更新前后的数据输出出来) */

declare
cursor update_cur is select empno,ename,sal from emp2 for update of sal;
v_saladd emp.sal%type;
v_row update_cur%rowtype;
begin
for v_row in update_cur loop
v_saladd:=v_row.sal*0.05;
if v_saladd <=300 then
update emp2 set sal=v_saladd+v_row.sal where current of update_cur;
dbms_output.put_line(v_row.sal||’–’||(v_saladd+v_row.sal)); /* v_saladd+v_row.sal加括号就可以成功打印了，哈哈*/
end if;
end loop;
end;

/11:将每位员工工作了多少年零多少月零多少天输出出来 /
declare
cursor emp_cur is select ename,hiredate,
trunc(months_between(sysdate,hiredate)/12) years,trunc(mod(months_between(sysdate,hiredate),12)) as monthd,
trunc(mod(mod(sysdate-hiredate,365),12)) days from emp2; –前面的年、月都好理解，‘日’理解不了
v_row emp_cur%rowtype;
begin
open emp_cur;
loop
exit when emp_cur%notfound;
fetch emp_cur into v_row;
–退出需在打印条件的前面，否则最后一项会打印两次
dbms_output.put_line(v_row.ename||’工作了’||v_row.years||’年，零’||v_row.monthd||’月，零’||v_row.days||’日’);
end loop;
end;

/输入部门编号，按照下列加薪比例执行(用CASE实现),并将更新前后的数据输出出来/
–deptno raise(%)
– 10 5%
– 20 10%
– 30 15%

declare
cursor update_cur is select deptno,sal from emp2 for update of sal; –选择部分字段同样可以使用%rowtype定义的游标变量
v_row update_cur%rowtype;
v_newsal emp2.sal%type;
begin
for v_row in update_cur loop
case
when v_row.deptno=10 then
v_newsal:=v_row.sal*1.05;
when v_row.deptno=20 then
v_newsal:=v_row.sal*1.1;
when v_row.deptno=30 then
v_newsal:=v_row.sal*1.15;
else v_newsal:=v_row.sal; –有其他情况存在，一定要补上else语句
end case; –只有当执行完整个循环语句之后，才会update整个表，但会执行打印语句
update emp2 set sal=v_newsal where current of update_cur;
dbms_output.put_line(‘原工资：’||v_row.sal||’现工资是：’||v_newsal);
end loop;
end;

/*对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，
则将其薪水减300元，输出更新前后的薪水，员工姓名，所在部门编号。*/
–AVG([distinct|all] expr) over (analytic_clause)
—作用：
–按照analytic_clause中的规则求分组平均值。
–分析函数语法:
–FUNCTION_NAME(,…)
–OVER
–()
–PARTITION子句
–按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组

declare
cursor update_cur is select deptno,ename,sal,avg(sal) over(partition by deptno) as dept_avg
from emp2 for update of sal;
v_row update_cur%rowtype;
v_newsal emp2.sal%type;
begin
open update_cur;
fetch update_cur into v_row;
while update_cur%found loop
if v_row.sal>v_row.dept_avg then
v_newsal:=v_row.sal-300;
else
v_newsal:=v_row.sal; /* 此处必须加else语句，否则dbms不知道对其它情况如何处理，会产生错误*/
end if;
update emp2 set sal=v_newsal where current of update_cur;
dbms_output.put_line(‘原工资：’||v_row.sal||’–部门平均工资’||v_row.dept_avg||’–现工资：’||v_newsal);
fetch update_cur into v_row;
end loop;
end;
/第二种方式/
declare
cursor
crs_testavg
is
select empno,ename,job,sal,deptno,avg(sal) over (partition by deptno ) as dep_avg
from emp2 for update of sal;
r_testavg crs_testavg%rowtype;
salinfo emp2.sal%type;
begin
for r_testavg in crs_testavg loop
if r_testavg.sal>r_testavg.dep_avg then
salinfo:=r_testavg.sal-300;
else
salinfo:=r_testavg.sal;
end if;
update emp2 set sal=salinfo where current of crs_testavg;
dbms_output.put_line(‘原工资：’||r_testavg.sal||’–部门平均工资’||r_testavg.dep_avg||’–现工资：’||salinfo);
end loop;
end;