PL-SQL Basic

---
--- ======================PL-SQL Basic====================
---

/**create table stu*/
create table stu(
 id number(3),
 name char(8),
 score number(3),
 age number(2),
 sex char(2)
)

---
--- =========================over========================
---

/**insert one result*/
insert into stu values(3,'zheng',88,24,'f')

---
--- =========================over========================
---

/**query table stu*/
select * from stu

---
--- ==========================over=======================
---

/**select the count of score>60*/
declare
       totle number(3);
begin 
 select count(*) into totle 
 from stu
 where score>60;
 /*if -- end if,please remember it*/
 if totle<>0 then
    dbms_output.put_line('the number of score>60 is '||to_char(totle));
 end if;
end;

---
--- ==========================over=======================
---

/**select score >95,exist?*/
declare
   totle number(3);
begin 
   select count(*) into totle 
   from stu
   where score>80;
   if totle<>0 then
       /**add one condition*/
     if totle>1 then
        dbms_output.put_line('score>95 exist! ^^...the totle is:'||totle);
     end if;
  end if;
end;

---
--- =========================over========================
---


/**function avg*/
declare
  avgage number(3,1);
    
begin
  select avg(age) into avgage 
  from stu;
  
  if avgage>24 then
     dbms_output.put_line('the age is beyond 24! and avg(age) is:'||avgage);
  else
     dbms_output.put_line('the age is low to 24!');
  end if;
end;

---
--- ========================over=========================
---

/**for the math function*/

declare
a number;
b number;
c number;
d number;
x1 number(4,2);
x2 number(4,2);

begin
a:=2;
b:=8;
c:=4;

/**sqrt() for d*/
d:=sqrt(b*b-4*a*c);

/**if -- else series*/
if a=0 then
   x1:=-b/a;
   dbms_output.put_line('only one result:'||to_char(x1));
elsif d<0 then
   dbms_output.put_line('the result doesnt exist!');
else
   x1:=(-b-d)/(2*a);
   x2:=(-b+d)/(2*a);
   dbms_output.put_line('there are two fun_result:x1='||x1);
   dbms_output.put_line('there are two fun_result:x2='||x2);
end if;
end;

---
--- ========================over=========================
---

/**10! --method1*/
declare
i number;
step number;

begin
  i:=1;
  step:=1;
  
  loop
      i:=i*step;
      step:=step+1;
      if step>10 then
         exit;
       end if;
  end loop;
  
  dbms_output.put_line('10!='||i);
end;

---
--- ========================over=========================
---

/**10! --method2*/
declare
i number;
step number;

begin
  i:=1;
  step:=1;
  
  loop
      i:=i*step;
      step:=step+1;
      exit when(step>10);
  end loop;
  
  dbms_output.put_line('10!='||i);
end;

---
--- ========================over=========================
---

/**10! --method3*/
declare
i number;
step number;

begin
  i:=1;
  step:=1;
  
  /**
   * while loop...end loop;
   */
  while step<11
  loop
      i:=i*step;
      step:=step+1;
  end loop;
  
  dbms_output.put_line('10!='||i);
end;

---
--- ========================over=========================
---

/**10! --method4*/
declare
i number:=1;
step number;

begin
  for step in 1..10
  loop
    i:=i*step;
  end loop;
  
  dbms_output.put_line('10!='||i);
end;

---
--- ========================over=========================
---

/**case -- when*/
declare
 stuname char(8);
 stuscore number(2);
 
begin
  select name into stuname 
  from stu
  where id=2;
  
  case stuname
       when 'master' then stuscore:='98';
       when 'lang' then stuscore:='91';
       when 'zheng' then stuscore:='88';
       else
         stuscore:='name is nulll!';
  end case;
  
  dbms_output.put_line(stuname||':'||stuscore);
  
end;

---
--- ========================over=========================
---