CREATE OR REPLACE PROCEDURE PRO_TEST (WO_ID IN VARCHAR2) AS BEGIN INSERT INTO TABLE_TEST( ID , 监控中心名称 , 操作员名称 , 通信名称 ) SELECT WO_ID, t1.监控中心名称 , t2.操作员名称, t3.通信名称 from t1,t2,t3 where t1.通信出口id=t3.id and t1.操作员id=t3.id and t1.id='111'; END; /
这么简单的语句,你为什么要用过程啊? select table1.监控中心名称,table2.操作员名称,table3.通信名称 from table1,table2,table3 where table1.通信出口id=table3.id and table1.操作员id=table3.id and table1.id='111';
create package test_age as type my_rc is ref cursor; end; / create procedure pro(p_id in varchar2,p_rc out test_age.my_rc) as str varchar2(100); begin str:='select t1.监控中心名称,t2.操作员名称,t3.通信名称 from t1,t2,t3 where t1.通信出口id=t3.id and t1.操作员id=t3.id and t1.id='||p_id; open p_rc for str; end; /
(ID VARCHAR2(20),
监控中心名称 VARCHAR2(100) ,
操作员名称 VARCHAR2(100) ,
通信名称 VARCHAR2(100) );
CREATE OR REPLACE PROCEDURE PRO_TEST (WO_ID IN VARCHAR2)
AS
BEGIN
INSERT INTO TABLE_TEST(
ID ,
监控中心名称 ,
操作员名称 ,
通信名称 )
SELECT WO_ID,
t1.监控中心名称 ,
t2.操作员名称,
t3.通信名称
from t1,t2,t3
where t1.通信出口id=t3.id
and t1.操作员id=t3.id
and t1.id='111';
END;
/
select table1.监控中心名称,table2.操作员名称,table3.通信名称 from
table1,table2,table3 where table1.通信出口id=table3.id and table1.操作员id=table3.id and table1.id='111';
as
type my_rc is ref cursor;
end;
/
create procedure pro(p_id in varchar2,p_rc out test_age.my_rc)
as
str varchar2(100);
begin
str:='select t1.监控中心名称,t2.操作员名称,t3.通信名称 from
t1,t2,t3 where t1.通信出口id=t3.id and t1.操作员id=t3.id and t1.id='||p_id;
open p_rc for str;
end;
/