oracle复习笔记之PL/SQL程序所要了解的知识点(2)

时间:2014-08-23 11:24来源:网络整理作者:网络点击: 次

分享到：

查询出122号员工的job_id,若其值为 ‘IT_PROG', 则打印‘GRADE:A' ‘AC_MGT', 则打印‘GRADE:B' ‘AC_ACCOUNT', 则打印‘GRADE:B' 否则打印‘GRADE:D' declare v_job_id employees.j

查询出122号员工的job_id,若其值为 ‘IT_PROG', 则打印‘GRADE:A'
‘AC_MGT', 则打印‘GRADE:B'
‘AC_ACCOUNT', 则打印‘GRADE:B'
否则打印‘GRADE:D'

declare 
  v_job_id employees.job_id%type;
  v_temp varchar2(20);
begin
  select job_id into v_job_id from employees where employee_id =122;
  v_temp :=
  case v_job_id when 'IT_PROG' then 'A'
            when 'AC_MGT' then 'B'
            when 'AC_ACCOUNT' then 'C'
            else 'D'
            end;
  dbms_output.put_line('job_id:'||v_job_id||' '||v_temp);
end;

使用循环语句打印：1-100

declare
  v_i number(5) :=1;

begin
  loop
  dbms_output.put_line(v_i);
  exit when v_i >=100;
  v_i := v_i + 1;
  end loop;
end;
使用while实现：
declare
  v_i number(5) :=1;
begin
  while v_i <= 100 loop
   dbms_output.put_line(v_i);
   v_i := v_i + 1;
  end loop;
end;
使用for...in...loop...end loop;实现：
begin
  for c in 1..100 loop
   dbms_output.put_line(c);
  end loop;
end;

输出2-100之间的质数

declare
  v_i number(3):= 2;
  v_j number(3):= 2;
  v_flag number(1):= 1;
begin 
  while v_i<=100 loop
   while v_j<=sqrt(v_i) loop
    if mod(v_i,v_j)=0 then v_flag:=0;
    end if;
    v_j:= v_j+1;
   end loop;
  if v_flag = 1 then dbms_output.put_line(v_i);
  end if;
  v_j :=2;
  v_i := v_i + 1;
  v_flag := 1;
  end loop;
end;

利用for循环实现输出2-100之间的质数：

declare
  v_flag number(1):= 1;
begin 
  for v_i in 2..100 loop
   for v_j in 2..sqrt(v_i) loop
    if mod(v_i,v_j)=0 then v_flag:=0;
    end if;
   end loop;
   if v_flag=1 then dbms_output.put_line(v_i);
   end if;
   v_flag := 1;
  end loop;
end;

可以用goto改进一下：

declare
  v_flag number(1):= 1;
begin 
  for v_i in 2..100 loop
    for v_j in 2..sqrt(v_i) loop
     if mod(v_i,v_j)=0 then v_flag:=0;
     goto label;
     end if;
   end loop;
   <<label>>
   if v_flag=1 then dbms_output.put_line(v_i);
   end if;
   v_flag := 1;
  end loop;
end;

打印1-100的自然数，当打印到50时，跳出循环，输出‘打印结束'：

begin 
  for i in 1..100 loop
   if i=50 then goto label;
   end if;
  dbms_output.put_line(i);
  end loop;
<<label>>
  dbms_output.put_line('打印结束');
end;
或者：
begin 
  for i in 1..100 loop
   if i=50 then dbms_output.put_line('打印结束');
   exit;
   end if;
  dbms_output.put_line(i);
  end loop;
end;

游标：
打印出80部门的所有的员工的工资：salary:XXX
declare
v_sal employees.salary%type;
--定义游标
cursor emp_sal_cursor is select salary from employees where department_id = 80;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('salary:'||v_sal);
fetch emp_sal_cursor into v_sal;
end loop;
--关闭游标
close emp_sal_cursor;
end;
可以进行优化如下：

declare
v_empid employees.employee_id%type;
v_lastName employees.last_name%type;
v_sal employees.salary%type;
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where 
department_id = 80;
begin 
open emp_sal_cursor;
fetch emp_sal_cursor into v_empid,v_lastName,v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('employee_id:'||v_empid||', '||'last_name:'||v_lastName||', 
'||'salary:'||v_sal);
fetch emp_sal_cursor into v_empid,v_lastName,v_sal;
end loop;
close emp_sal_cursor;
end;