---
--- ======================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=========================
---