oracle存储过程语法累加,Oracle 存储过程语法总结及练习

---------------------------------------------

--1.存储过程之if

clear;

create or replace procedure mydel(

in_a in integer)

as

begin

if in_a<100 then

dbms_output.put_line('小于100.');

elsif in_a<200 then

dbms_output.put_line('大于100小于200.');

else

dbms_output.put_line('大于200.');

end if;

end;

/

set serveroutput on;

begin

mydel(1102);

end;

/

---------------------------------------------

--2.存储过程之case1

clear;

create or replace procedure mydel(

in_a in integer)

as

begin

case in_a

when 1 then

dbms_output.put_line('小于100.');

when 2 then

dbms_output.put_line('大于100小于200.');

else

dbms_output.put_line('大于200.');

end case;

end;

/

set serveroutput on;

begin

mydel(2);

end;

/

------------------------------------------------

--1.存储过程之loop1

clear;

create or replace procedure mydel(

in_a in integer)

as

a integer;

begin

a:=0;

loop

dbms_output.put_line(a);

a:=a+1;

exit when

a>301;

end loop;

end;

/

set serveroutput on;

begin

mydel(2);

end;

/

--------------------------------------------------

--1.存储过程之loop2

clear;

create or replace procedure mydel(

in_a in integer)

as

a integer;

begin

a:=0;

while a<300 loop

dbms_output.put_line(a);

a:=a+1;

end loop;

end;

/

set serveroutput on;

begin

mydel(2);

end;

--------------------------------------------------

--1.存储过程之loop3

clear;

create or replace procedure mydel(

in_a in integer)

as

a integer;

begin

for a in 0..300

loop

dbms_output.put_line(a);

end loop;

end;

/

set serveroutput on;

begin

mydel(2);

end;

/

clear;

select ename,cc:=(case

when comm=null then sal*12;

else (sal+comm)*12;

end case from emp order by salpersal;

----------------------------------------------------

clear;

create or replace procedure getstudentcomments(

i_studentid in int,o_comments out varchar)

as

exams_sat int;

avg_mark int;

tmp_comments varchar(100);

begin

select count(examid) into exams_sat from studentexam

where studentid=i_studentid;

if exams_sat=0 then

tmp_comments:='n/a-this student did not attend the exam!';

else

select avg(mark) into avg_mark from studentexam

where studentid=i_studentid;

case

when avg_mark<50 then tmp_comments:='very bad';

when avg_mark<60 then tmp_comments:='bad';

when avg_mark<70 then tmp_comments:='good';

end case;

end if;

o_comments:=tmp_comments;

end;

/

set serveroutput on;

declare

pp studentexam.comments%type;

begin

getstudentcomments(8,pp);

dbms_output.put_line(pp);

end;

/

--------------------------------------------------------

delete from emp where empno<6000;

clear;

create or replace procedure insertdata(

in_num in integer)

as

myNum int default 0;

emp_no emp.empno%type:=1000;

begin

while myNum

insert into emp values(emp_no,'hui'||myNum,'coder',7555,current_date,8000,6258,30);

emp_no:=emp_no+1;

myNum:=myNum+1;

end loop;

end;

/

set serveroutput on;

begin

insertdata(10);

end;

/

select * from emp;

------------------------------------------------------------------

clear;

select studentname,averageMark,case

when averageMark<60 then '不及格'

when averageMark<70 then '考得好'

when averageMark<80 then '考得很好'

end case

from (select (

select b.name from student b where b.studentid=a.studentid) as studentname,

round(avg(mark),2) as averageMark from studentexam a group by a.studentid);