有两个表:
ta{id, name,classid}
tb{id,classname}
想要得到 {id,name,classname}
其sql语句为:
select ta.id,ta.name,tb,classname
from ta, tb
where ta.classid=tb.id;
如果我想用存储过程实现,应该怎么写?
ta{id, name,classid}
tb{id,classname}
想要得到 {id,name,classname}
其sql语句为:
select ta.id,ta.name,tb,classname
from ta, tb
where ta.classid=tb.id;
如果我想用存储过程实现,应该怎么写?
select ... into
其他一样。
insert into tmp_table
(id,name,classname)
select ta.id,ta.name,tb,classname
from ta, tb
where ta.classid=tb.id;
commit;
is
begin
open v_cur for
select ta.id,ta.name,tb,classname
from ta, tb
where ta.classid=tb.id;
end;
/
DECLARE
V_CUR SYS_REFCURSOR;
TYPE T IS RECORD(ID TA.ID%TYPE,NAME TA.NAME%TYPE,CLASSNAME TB.CLASSNAME%TYPE);
V_C T;
BEGIN
PROC_GET_RECORDS(V_CUR);
LOOP
FETCH V_CUR INTO V_C;
EXIT WHEN V_CUR%NOTFOUND;
DBMS_OUTPUT.put_line(V_C.ID||V_C.NAME||V_C.CLASSNAME);
END LOOP;
CLOSE V_CUR;
END;
/
create or replace view tmp_table_view
select ta.id,ta.name,tb,classname
from ta, tb
where ta.classid=tb.id视图不行的话,也可以用存储过程:
create or replace procedure proc_get_records( v_cur out sys_refcursor)
is
begin
open v_cur for
select ta.id,ta.name,tb,classname
from ta, tb
where ta.classid=tb.id;
end;
/
insert into temp_table_name(
column1,
...
)
select
column1,
...
from table_name,...
where ...
这种形式啊,太开心了!谢谢大家