oracle编程

<span style="font-size:14px;">--从sc数据表中求出学号为s7同学的平均成绩，如果此平均成绩大于或者等于60分，则输出“pass”信息
set serveroutput on;
if (select avg(score) from sc where sno='s7' group by sno)>=60
  begin 
    DBMS_OUTPUT.PUT_LINE('pass');
  end;

--变量初始化和赋值
set serveroutput on;
declare
   l_dept    integer := 20;
   currtime  date := sysdate; 
 begin
      l_dept := 30;
      dbms_output.put_line(l_dept);
      dbms_output.put_line(currtime);    
 end;

--case...when语句
 set serveroutput on;
 declare
  i   integer;
  str varchar2(20);
begin
  i   := 3;
  str := case
           when i = 1 then 'a'
           when i = 2 then 'b'
           when i = 3 then 'c'
         end ;
  dbms_output.put_line(str);
end;

--从学生表s中，选取sno，sex，如果sex为‘男’则输出‘M’，如果为‘女’输出‘F’
set serveroutput on;
select sno,
  case 
    when sex = '男' then 'M'
    when sex = '女' then 'F'
    end
  case from s;

--从学生表s中，选取sno，sex，如果sex为‘男’则输出‘M’，如果为‘女’输出‘F’
set serveroutput on;
select sno, 
  case sex
    when '男' then 'M'
    when '女' then 'F'
    end
  case from s;

--计算1至100之间所有能被3整除的数的个数和总和
set serveroutput on;
declare 
  a smallint:=0;
  b smallint:=1;
  sums smallint:=0;
begin
  while b <= 100 loop
    if (mod(b,3) = 0)
      then
        a := a + b; 
        sums := sums + 1;
     end if;
     b := b + 1;
    end loop;
    dbms_output.put_line(a);
    dbms_output.put_line(sums);
end;

--计算1至100之间所有能被3整除的数的个数和总和
set serveroutput on;
declare 
  a smallint:=0;
  b smallint:=1;
  sums smallint:=0;
begin
  for i in 1..100 loop
    if (mod(b,3) = 0)
      then
        a := a + b; 
        sums := sums + 1;
     end if;
     b := b + 1;
    end loop;
    dbms_output.put_line(a);
    dbms_output.put_line(sums);
end;

--goto语句的使用计算前10个数字的总和
set serveroutput on;
declare 
  s smallint :=0;
  i smallint :=0;
begin
 <<sta>>
  if(i<=10)
    then 
      s:=s+i;
      i:=i+1;
    goto sta;
  end if;
  dbms_output.put_line(s);
end;
</span>