流程练习

1、输入员工编号，如果他的工资高于他所在部门的平均工资，输出工资较高，否则输出工资较低

declare
v_sal emp.sal%type;
v_avgsal emp.sal%type;
v_empno emp.empno%type;
begin
v_empno:=&empno;
select avg(sal) into v_avgsal from emp where deptno=(select deptno from emp where empno=v_empno);
select sal into v_sal from emp where empno=v_empno;
if v_avgsal<v_sal then
dbms_output.put_line('工资较高');
else
dbms_output.put_line('工资较低');
end if;
end;

2、输入一个员工号，输出他入职的季节（春夏秋冬）

declare
v_date number(3);
begin
select to_number(to_char(hiredate,'mm')) into v_date from emp where empno=&no;
case
when v_date<=3 then dbms_output.put_line('春');
when v_date<=6 then dbms_output.put_line('夏');
when v_date<=9 then dbms_output.put_line('秋');
when v_date<=12 then dbms_output.put_line('冬');
else dbms_output.put_line('未入职');
end case;
end;

方法2：
select emp.*,to_char(hiredate,'q') from emp;
q代表季节-----1为春，2为夏，3为秋，4为冬

1、九九乘法表

begin
for v_i in 1 .. 9
  loop
  for v_j in 1 .. v_i
loop
dbms_output.put(v_i||'*'||v_j||'='||v_i*v_j||'      ');
end loop;
dbms_output.put_line(' ');
end loop;
end;

2、

           *
          ***
         *****

begin
  for v_i in 1 .. 6 loop    dbms_output.put_line(lpad(lpad('*',2*v_i,'*'),6+v_i,' ')); 
    end loop;
    end;

1、输入每页的行数和第几页

5------每页5行

2------当前第二页

共X页 当前第二页

员工姓名，职位，入职日期，薪水等级，部门名称

count(*)/5  向上取整--------------ceil(count(*)/5)

declare
type recordtype is record
(
ename emp.ename%type,
job emp.job%type,
hiredate emp.hiredate%type,
grade salgrade.grade%type,
dname dept.dname%type
);
type mycurtype is ref cursor return recordtype;--游标
mycur mycurtype;
emprow recordtype;--保存数据
v_pagesize number(5);
v_pageindex number(5);
v_pagecount number(5);
begin
  v_pageindex:=&当前页码;
  v_pagesize:=&每页的行数;
  select ceil(count(*)/v_pagesize) into v_pagecount from emp
  inner join dept on dept.deptno=emp.deptno
  inner join salgrade on emp.sal between losal and hisal;
    dbms_output.put_line('共'||v_pagecount||'页当前第'||v_pageindex||'页');
    if v_pageindex>v_pagecount then
      dbms_output.put_line('页码无效');
      else
    dbms_output.put_line('员工姓名， 职位，入职日期，薪水等级，部门名称');
  open mycur for 
  select ename,job,hiredate,grade,dname from(
  select t.*,rownum as rn from(
  select ename,job,hiredate,grade,dname from emp
  inner join dept on dept.deptno=emp.deptno
  inner join salgrade on emp.sal between losal and hisal 
  order by emp.deptno asc
  )t
  )t1 where t1.rn between v_pagesize*(v_pageindex-1)+1 and v_pagesize*v_pageindex;
  end if;
  loop
    fetch mycur into emprow;
    exit when mycur%notfound;
    --dbms_output.put_line(emprow.ename||' '||emprow.job||' '||emprow.hiredate||' '||emprow.dname||' '||emprow.grade);
    dbms_output.put(rpad(emprow.ename,10,' '));
    dbms_output.put(rpad(emprow.job,10,' '));
    dbms_output.put(rpad(to_char(emprow.hiredate,'yyyy-mm-dd'),14,' '));
    dbms_output.put(rpad(emprow.grade,5,' '));
    dbms_output.put(rpad(emprow.dname,10,' '));
    dbms_output.put_line(' ');
end loop;
close mycur;
end;