Oracle流程控制语句

declare
v_num number(3):=1;
begin
while v_num<101 loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end;
create procedure print(r in number,c number) is
begin
for i in 1..r loop
for j in 1..c loop
dbms_output.put(‘* ‘);
end loop;
dbms_output.put_line(”);
end loop;
end;
begin
print(5,6);
end;
create or replace function water return number is
v_sum number(10):=0;
v_n1 number(1);
v_n2 number(1);
v_n3 number(1);
begin
for i in 100..999 loop
v_n1:=trunc(i/100);—百位
v_n2:=trunc(mod(i/10,10));—十位
v_n3:=mod(i,10);—-个位
if power(v_n1,3)+power(v_n2,3)+power(v_n3,3)=i
then v_sum:=v_sum+i;
end if;
end loop;
return v_sum;
end;
declare
sum1 number(10);
begin
sum1:=water;
dbms_output.put_line(sum1);
end;
create or replace procedure salbyno(v_no in number) is
v_job varchar2(20);
begin
select job into v_job from emp where empno=v_no;
update emp set sal=sal+
case v_job
when ‘总经理’ then 1000
when ‘经理’ then 800
when ‘分析师’ then 500
else 300
end
where empno=v_no;
end;

begin
salbyno(7369);
end;
–3.录入员工的工号,如果该员工的工资小于其所在部门的平局工资
–则给其加上其所在部门的平局工资的20%,然后将加薪后的工资返回
select * from emp where empno=7369;
create or replace function avgsalbyno(v_no in number) return number is
v_sal emp.sal%type;
v_addsal emp.sal%type;
v_avgsal emp.sal%type;
begin
select a.sal,b.avgsal into v_sal,v_avgsal from emp a,
(select deptno,avg(sal) avgsal from emp group by deptno) b where empno=v_no and a.deptno=b.deptno;
if v_sal