存储过程中使用临时表

[小林寒风]

现有一学生表STUDENT，包含三个字段，分别为学号sno（数值）、姓名sname（字符串）、成绩score（数值 两位小数），其中学号为主键，要求：写一个存储过程，输入四个参数，第一个，类型，第二个，学号，第三个，姓名，第四个，成绩。（输入类型包括Q查询,U修改,D删除）
输入Q，查询输入的第一个学号的信息
输入U，根据学号修改学生的姓名和程序，姓名和成绩为第二个第三个参数.并将修改后的信息查询出来
输入D，删除输入学号的学生信息。并显示要删除的学生的信息.用临时表.

create or replace procedure pro_student (typ char,stuno number,stuname varchar2,stuscore number) as
v_stuno number;
v_stuname varchar2(50);
v_stuscore number;
begin
case lower(typ)
when 'q' then
select sno into v_stuno from student where sno=stuno;
select sname into v_stuname from student where sno=stuno;
select score into v_stuscore from student where sno=stuno;
dbms_output.put_line(to_char(v_stuno)||' '||v_stuname||' '||to_char(v_stuscore));
when 'u' then
update student set sname=stuname,score=stuscore
where sno=stuno;
commit;
when 'd' then
execute immediate
'create global temporary table student_temp as select * from student
where sno=stuno on commit delete rows';
delete from student where sno=stuno;
--select * from student_temp;
end case;
end;

查询和修改都没问题。
用临时表时，提示未正确结束，求高手指点下怎么写

[小灰狼W]

create global temporary table TABNAME on commit delete rows as ....;
或者不加on commit delete rows 默认就是事务级
建议可以先创建好临时表，在过程中使用，而不是在过程里动态创建

[zhangchu_63]

[Quote=引用 1 楼 wildwave 的回复:]
create global temporary table TABNAME on commit delete rows as ....;
或者不加on commit delete rows 默认就是事务级
建议可以先创建好临时表，在过程中使用，而不是在过程里动态创建
[/Quote]

同意
而其，你这样写，就算能执行成功，也只是一次性的。
第二次执行就会报“表名已存在”！

[mcawxd]

create or replace procedure pro_student(p_Type varchar,p_sno number,p_sname varchar,p_score number) as
DELCLARE
v_sno student.sno%type;
v_sname student.sname%type;
v_score student.score%type;

BEGIN
CASE LOWER(p_type)
WHEN 'q' then
Select sno,sname,score Into v_sno,v_sname,v_score from student where sno =p_sno
if SQL%FOUND then
DBMS_OUPT.PUTLINE(CHAR(v_sno)||v_sname||CHAR(v_score));
else
DBMS_OUPT.PUTLINE(not found student infomation!);
endif
WHEN 'u' THEN
Update student set sname=p_sname,score=p_score where sno=p_sno;
commit;
Select sno,sname,score Into v_sno,v_sname,v_score from student where sno =p_sno;
DBMS_OUPT.PUT_LINE(CHAR(v_sno)||v_sname||CHAR(v_score));
WHEN 'd' THEN
DROP Table Temp_Table;
CREATE TABLE Temp_Table as select * from student where sno=p_sno;
Delete from student where sno=p_sno;
COMMIT;
Select sno,sname,score Into v_sno,v_sname,v_score from Temp_Table where sno=p_sno;
DBMS_OUPT.PUTLINE(CHAR(v_sno)||v_sname||CHAR(v_score));
END CASE;
END;

仅供参考!

[ojuju10]

--说的很详细
http://blog.csdn.net/jsyzzcx/archive/2008/10/28/3164018.aspx

[真的不知道取什么名]

按照一楼的方法先创建好临时表，在过程中使用，而不是在过程里动态创建

select sno into v_stuno from student where sno=stuno;
select sname into v_stuname from student where sno=stuno;
select score into v_stuscore from student where sno=stuno;
-- 你的这三条语句可以写成这样
select sno,sname,score into v_stuno,v_stuname,v_stuscore
from student where sno=stuno;