以前没搞过oracle ,我这语法都不晓得怎么弄。
这里想用一下表变量@tmptb 返回一下我select的东西。请问,这到底咋写。create or replace procedure PROC_GetTZZBIntersect(QJID in varchar2)
as
@tmptb Table (
ID number,
ZBBH datetime
),str varchar2(1000);
begin insert into @tmptb (
SELECT ID, ZBBH FROM JXKH_T_TZZBXX_R where qjid =:QJID
)
end PROC_GetTZZBIntersect;
as
str varchar2(1000);
begin
execute immediate 'CREATE GLOBAL TEMPORARY TABLE tmptb(
ID number,
ZBBH datetime
) ON COMMIT delete ROWS';
insert into tmptb (SELECT ID, ZBBH FROM JXKH_T_TZZBXX_R where qjid =:QJID);
end PROC_GetTZZBIntersect;你调试看看
我,,,我太失望了。
oracle下还有一些复合数据类型可以使用,这里就可以定义一个记录变量。create or replace procedure PROC_GetTZZBIntersect(QJID in varchar2)
as
Type rec_tmp is record(ID number,ZBBH datetime);
tmptb rec_tmp;
str varchar2(1000);
begin
SELECT ID, ZBBH into tmptb FROM JXKH_T_TZZBXX_R where qjid =:QJID;
end PROC_GetTZZBIntersect; 这可以实现你的要求
修改下:create or replace procedure PROC_GetTZZBIntersect(QJID in varchar2)
as
Type rec_tmp is record(ID number,ZBBH datetime);
tmptb rec_tmp;
begin
SELECT ID, ZBBH into tmptb FROM JXKH_T_TZZBXX_R whereqjid =:QJID;
dbms_output.put_line('ID:'||tmptb.id||',ZBBH:'||tmptb.ZBBH);
end PROC_GetTZZBIntersect;