oracle 中的存储过程,oracle中的存储过程

------存储过程

--------不带参数

---------打印所有男生的信息

create or replace procedure p1

as

cursor cur is

select sname,sno from student where ssex='男';

v_sname varchar2(10);

v_sno number;

begin

open cur;

loop

fetch cur into v_sname,v_sno;

exit when cur%notfound;

dbms_output.put_line(v_sname||v_sno);

end loop;

close cur;

-----带参数

----打印员工表中工资最高的前四个人的姓名，工资

create or replace procedure p1(v_n number)

as

cursor ep_cursor is select first_name,salary from

(select * from employees order by salary desc)

where rownum

v_sname varchar2(10);

v_salary number;

v_m number;

v_error exception;

begin

select count(*) into v_m from employees;

if v_n<=0 or v_n>v_m then

raise v_error;

end if;

open ep_cursor;

loop

fetch ep_cursor into v_sname,v_salary;

exit when ep_cursor%notfound;

dbms_output.put_line(v_sname||v_salary);

end loop;

close ep_cursor;

exception

when v_error then

dbms_output.put_line('输入行数不符');

end;

--------当用varchar或char时不能输入长度(如varchar(10)中的10不能输入)

create or replace procedure p1(v_n varchar)

as

cursor ep_cursor is select first_name,salary from

(select * from employees order by salary desc)

where rownum

v_sname varchar2(10);

v_salary number;

v_m number;

v_error exception;

begin

select count(*) into v_m from employees;

if v_n<=0 or v_n>v_m then

raise v_error;

end if;

open ep_cursor;

loop

fetch ep_cursor into v_sname,v_salary;

exit when ep_cursor%notfound;

dbms_output.put_line(v_sname||v_salary);

end loop;

close ep_cursor;

exception

when v_error then

dbms_output.put_line('输入行数不符');

end;

1.插入数据

create or replace procedure p1(v_sno number,

v_sname varchar(10),

v_ssex varchar(10),

v_sage varchar(10),

v_sdept varchar(10),

as

begin

insert into student values(

v_sno,v_sname,v_ssex,v_sage,v_sdept)

end;

2.删除数据

通过过程p7实现输入学号，删除学生，如果没有打印没有这个人

create or replace procedure p7(v_sno student.sno%type)

as

begin

delete from student where sno=v_sno;

if sql%rowcount=0 then dbms_output.put_line('无此人 ');

elsif sql%rowcount<>0 then

dbms_output.put_line('删除成功');

end if;

end;

与下面类似

2.2

create or replace procedure p7(v_sno student.sno%type)

as

begin

delete from student where sno=v_sno;

if sql%notfound then

dbms_output.put_line('无此人 ');

else

dbms_output.put_line('删除成功');

end if;

end;

-----输入学号，打印是否可以入党(入党条件为选课数大于3)

create or replace procedure p1(v_sno student.sno%type)

as

v_cou number;

begin

select count(cno) into v_cou from sc where sno=v_sno;

if v_cou>3 then

dbms_output.put_line('neng');

else

dbms_output.put_line('bu');

end if;

end;

------带输出参数的存储过程

create or replace procedure p12(v_sno sc.sno%type,v_n out number)

as

begin

select count(*) into v_n from sc where sno=v_sno;

end;

/

create or replace procedure p11(v_sno sc.sno%type)

as

n number;

begin

p12(v_sno, n );

if n>3 then

dbms_output.put_line('可以入党');

else dbms_output.put_line('不可以入党');

end if;

end;

/

exec p11('2000012');