CREATE PROC sp_TMP
@lc单据编号 varchar(200)
AS
--declare @lc单据编号 varchar(200)
--set @lc单据编号='a01'
CREATE TABLE #T1(单据编号 varchar(200))
insert into #T1 (单据编号) values (@lc单据编号)
insert into #T1 (单据编号) values (@lc单据编号+'1')
insert into #T1 (单据编号) values (@lc单据编号+'2')
insert into #T1 (单据编号) values (@lc单据编号+'3')
insert into #T1 (单据编号) values (@lc单据编号+'4')
select * from #T1在JAVA中 编写 call sp_TMP 'a01'
就可以得到这样的结果单据编号
a01
a011
a012
a013
a014用ORACLE 如何实现
@lc单据编号 varchar(200)
AS
--declare @lc单据编号 varchar(200)
--set @lc单据编号='a01'
CREATE TABLE #T1(单据编号 varchar(200))
insert into #T1 (单据编号) values (@lc单据编号)
insert into #T1 (单据编号) values (@lc单据编号+'1')
insert into #T1 (单据编号) values (@lc单据编号+'2')
insert into #T1 (单据编号) values (@lc单据编号+'3')
insert into #T1 (单据编号) values (@lc单据编号+'4')
select * from #T1在JAVA中 编写 call sp_TMP 'a01'
就可以得到这样的结果单据编号
a01
a011
a012
a013
a014用ORACLE 如何实现
create global temporary table t1(单据编号 varchar2(20)) on commit delete rows;
--2、建立存储过程
CREATE OR REPLACE PROCEDURE sp_TMP(v_lc VARCHAR2, o OUT SYS_REFCURSOR) AS
lc单据编号 VARCHAR(200);
BEGIN
lc单据编号 := v_lc;
INSERT INTO T1 (单据编号) VALUES (lc单据编号);
INSERT INTO T1 (单据编号) VALUES (lc单据编号 || '1');
INSERT INTO T1 (单据编号) VALUES (lc单据编号 || '2');
INSERT INTO T1 (单据编号) VALUES (lc单据编号 || '3');
INSERT INTO T1 (单据编号) VALUES (lc单据编号 || '4');
OPEN o FOR
SELECT * FROM T1;
END;
--建立表T1
create table t1(单据编号 varchar2(20)) ;--建立存储过程
CREATE OR REPLACE PROCEDURE sp_TMP(rs OUT SYS_REFCURSOR)
AS
lc单据编号 VARCHAR(200);
BEGIN
lc单据编号 := 'a01';
INSERT INTO T1 (单据编号) VALUES (lc单据编号);
INSERT INTO T1 (单据编号) VALUES (lc单据编号 || '1');
INSERT INTO T1 (单据编号) VALUES (lc单据编号 || '2');
INSERT INTO T1 (单据编号) VALUES (lc单据编号 || '3');
INSERT INTO T1 (单据编号) VALUES (lc单据编号 || '4');
COMMIT;
OPEN rs FOR SELECT * FROM T1;
END;--测试:
var cur refcursor;
exec sp_TMP(:cur);
print cur;
scott@ORCL> set serveroutput on
scott@ORCL> create or replace procedure sp_TMP(lc单据编号 varchar2,cur out sys_refcursor)
2 as
3 begin
4 open cur for with tb as
5 (select lc单据编号 from dual union all
6 select lc单据编号 || '1' from dual union all
7 select lc单据编号 || '2' from dual union all
8 select lc单据编号 || '3' from dual union all
9 select lc单据编号 || '4' from dual)
10 select * from tb;
11 end;
12 /过程已创建。scott@ORCL> var cur refcursor
scott@ORCL> exec sp_TMP('a01',:cur)PL/SQL 过程已成功完成。scott@ORCL> print cur:B1
---------------------------------
a01
a011
a012
a013
a014