大神求教,存储过程怎么建表,怎么用?在线等。
尘面霜鬓 2017-07-07 10:30:50 --根据每门专业课的最低分数线和总分的最低分数线自动将考生归类为录取考生、
--调剂考生和落选考生。
报错:
Warning: Procedure created with compilation errors
SQL> show error
Errors for PROCEDURE SYSTEM.GRADUATE_PRO:
LINE/COL ERROR
-------- -----------------------------
38/22 PL/SQL: ORA-00942: 表或视图不存在
38/10 PL/SQL: SQL Statement ignored
--执行的脚本,我之前建了个result表能用,但是我想在存储过程建表,然后再插入数据就不成功呢。
SQL>
create or replace procedure graduate_pro(
p_zhengzhi in graduate.zhengzhi%type,
p_yingyu in graduate.yingyu%type,
p_zhuanye1 in graduate.zhuanye1%type,
p_zhuanye2 in graduate.zhuanye2%type,
p_zhuanye3 in graduate.zhuanye3%type,
p_total in number)is
r_graduate graduate%rowtype;
total_score number;
flag varchar2(20);
cursor cursor_gra is select * from graduate;
errormassage exception;
v_sql varchar2(1000);
begin
v_sql:='create table result2 as select * from result where 1=2 ';
execute immediate v_sql;
commit;
begin
open cursor_gra;
if cursor_gra%notfound then
raise errormassage;
end if;
loop
fetch cursor_gra into r_graduate;
total_score:=r_graduate.zhengzhi+r_graduate.yingyu+r_graduate.zhuanye1+r_graduate.zhuanye2+r_graduate.zhuanye3;
if(r_graduate.zhengzhi>=p_zhengzhi and
r_graduate.yingyu>=p_yingyu and
r_graduate.zhuanye1>=p_zhuanye1 and
r_graduate.zhuanye2>=p_zhuanye2 and
r_graduate.zhuanye3>=p_zhuanye3 and
total_score>=p_total) then
flag:='录取';
else
flag:='落选';
end if;
exit when cursor_gra%notfound;
insert into result2(bh,xm,lb,zhengzhi,yingyu,zhuanye1,zhuanye2,zhuanye3,totalscore,flag)
values(r_graduate.bh,r_graduate.xm,r_graduate.lb,r_graduate.zhengzhi,r_graduate.yingyu,r_graduate.zhuanye1,r_graduate.zhuanye2,r_graduate.zhuanye3,total_score,flag);
end loop;
close cursor_gra;
commit;
exception
when errormassage then
dbms_output.put_line('无法打开数据表');
end;
end;