CREATE OR REPLACE PACKAGE qtone IS
TYPE SUBSTR_ARRAY IS TABLE OF integer INDEX BY BINARY_INTEGER;
TYPE ref_cursor IS REF CURSOR;
END;create or replace FUNCTION split_str(str in varchar2, delimiter in varchar2) RETURN qtone.SUBSTR_ARRAY is
src_str varchar2(300) := '';
prev_index integer := 1;
next_index integer := 1;
retval qtone.substr_array ;
cnt INTEGER := 1;
BEGIN
--去掉开头和结束的分隔符
src_str := trim(both delimiter from str);
while next_index != 0 loop
next_index := instr(src_str,delimiter,prev_index,1);
if(next_index = 0) then
retval(cnt) := substr(src_str,prev_index);
cnt := cnt + 1;
else
retval(cnt) := substr(src_str,prev_index,next_index - prev_index);
cnt := cnt + 1;
end if;
prev_index := next_index + 1;
end loop;
RETURN retval;
end;
create or replace function pro_test(iflag number, vsqlwhere varchar2)
return qtone.ref_cursor is
res_cursor qtone.ref_cursor;
unique_key varchar2(128);
city_id integer;
city_name VARCHAR2(100);
all_frequency_no varchar2(100);
icount integer;
ipos integer;
frequency_no qtone.SUBSTR_ARRAY;
stmt VARCHAR2(100);
varsql varchar2(200);
vsql varchar2(2000);
cursor td3_cell_cur is
select unique_key,city_id,city_name,all_frequency_no from td3_cell;
BEGIN
vsqlwhere := '';
iflag := 0;
stmt := '';
varsql := 'create GLOBAL TEMPORARY TABLE c_test(city_id integer,city_name varchar(200),frequency_no integer) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE varsql;
open td3_cell_cur;
loop
fetch td3_cell_cur into unique_key,city_id,city_name,all_frequency_no;
exit when td3_cell_cur%notfound;
ipos := instr(vsqlwhere,unique_key);
if ((ipos >0 or iflag = 0) and nvl(city_id,0)<>0 and nvl(all_frequency_no,'') <> '') then
frequency_no := split_str(all_frequency_no,',');
stmt := 'insert into c_test(city_id,city_name,frequency_no) values(:city_id,:city_name,:frequency_no)';
IF(frequency_no.COUNT <> 0 ) THEN
FOR i IN frequency_no.first..frequency_no.last LOOP
EXECUTE IMMEDIATE stmt USING city_id,city_name,frequency_no(i);
END LOOP ;
commit;
END IF ;
end if;
end loop;
close td3_cell_cur;
--open res_cursor for
vsql :='select city_id,city_name,frequency_no,pinduan,count(1) frequency_num from
(
select city_id,city_name,frequency_no
,(case when frequency_no >= 1 and frequency_no <= 94 then ''GSM900''
when frequency_no >= 512 and frequency_no <= 636 then ''GSM1800''
when frequency_no >= 975 and frequency_no <= 1024 then ''EGSM''
else ''''
end) pinduan from c_test
) a group by city_id,city_name,frequency_no,pinduan';
open res_cursor for vsql;
return res_cursor;
END;
TYPE SUBSTR_ARRAY IS TABLE OF integer INDEX BY BINARY_INTEGER;
TYPE ref_cursor IS REF CURSOR;
END;create or replace FUNCTION split_str(str in varchar2, delimiter in varchar2) RETURN qtone.SUBSTR_ARRAY is
src_str varchar2(300) := '';
prev_index integer := 1;
next_index integer := 1;
retval qtone.substr_array ;
cnt INTEGER := 1;
BEGIN
--去掉开头和结束的分隔符
src_str := trim(both delimiter from str);
while next_index != 0 loop
next_index := instr(src_str,delimiter,prev_index,1);
if(next_index = 0) then
retval(cnt) := substr(src_str,prev_index);
cnt := cnt + 1;
else
retval(cnt) := substr(src_str,prev_index,next_index - prev_index);
cnt := cnt + 1;
end if;
prev_index := next_index + 1;
end loop;
RETURN retval;
end;
create or replace function pro_test(iflag number, vsqlwhere varchar2)
return qtone.ref_cursor is
res_cursor qtone.ref_cursor;
unique_key varchar2(128);
city_id integer;
city_name VARCHAR2(100);
all_frequency_no varchar2(100);
icount integer;
ipos integer;
frequency_no qtone.SUBSTR_ARRAY;
stmt VARCHAR2(100);
varsql varchar2(200);
vsql varchar2(2000);
cursor td3_cell_cur is
select unique_key,city_id,city_name,all_frequency_no from td3_cell;
BEGIN
vsqlwhere := '';
iflag := 0;
stmt := '';
varsql := 'create GLOBAL TEMPORARY TABLE c_test(city_id integer,city_name varchar(200),frequency_no integer) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE varsql;
open td3_cell_cur;
loop
fetch td3_cell_cur into unique_key,city_id,city_name,all_frequency_no;
exit when td3_cell_cur%notfound;
ipos := instr(vsqlwhere,unique_key);
if ((ipos >0 or iflag = 0) and nvl(city_id,0)<>0 and nvl(all_frequency_no,'') <> '') then
frequency_no := split_str(all_frequency_no,',');
stmt := 'insert into c_test(city_id,city_name,frequency_no) values(:city_id,:city_name,:frequency_no)';
IF(frequency_no.COUNT <> 0 ) THEN
FOR i IN frequency_no.first..frequency_no.last LOOP
EXECUTE IMMEDIATE stmt USING city_id,city_name,frequency_no(i);
END LOOP ;
commit;
END IF ;
end if;
end loop;
close td3_cell_cur;
--open res_cursor for
vsql :='select city_id,city_name,frequency_no,pinduan,count(1) frequency_num from
(
select city_id,city_name,frequency_no
,(case when frequency_no >= 1 and frequency_no <= 94 then ''GSM900''
when frequency_no >= 512 and frequency_no <= 636 then ''GSM1800''
when frequency_no >= 975 and frequency_no <= 1024 then ''EGSM''
else ''''
end) pinduan from c_test
) a group by city_id,city_name,frequency_no,pinduan';
open res_cursor for vsql;
return res_cursor;
END;
解决方案 »
- 数据库大表更新
- 紧急求救:SP2-1503:无法初始化oracle调用界面
- oracle编程、操作不良习惯总结
- 物化视图如果读取物化视图日志进行更新呀?
- 关于 程序关闭时 内存中数据处理问题
- oracle内存占用不断增长问题
- 视图分页查询_如何提高视图分布查询效率
- 关于update,如果在存储过程中,只update,而不commit,也不rollback?会有什么样的结果呢?
- 怎么才可以CONNECT AS SYSDBA?
- select * from table where time=SYS.time ? 对不对? 如何得到当天时间输入的记录?
- 请问为什么Oracle监听后会有大量的等待连接?
- 这样的sql怎么写
--unique_key city_id city_name all_frequency_no
--1 2 南京 6,7,8
--2 2 南京 3,4,6
--3 2 南京 1,23,4
--4 3 上海 4,6,5
--5 4 北京 9,20,5
TYPE SUBSTR_ARRAY IS TABLE OF integer INDEX BY BINARY_INTEGER;
TYPE ref_cursor IS REF CURSOR;
END;
/CREATE OR REPLACE FUNCTION split_str(str in varchar2, delimiter in varchar2) RETURN qtone.SUBSTR_ARRAY is
src_str varchar2(300) := '';
prev_index integer := 1;
next_index integer := 1;
retval qtone.substr_array ;
cnt INTEGER := 1;
BEGIN
--去掉开头和结束的分隔符
src_str := trim(both delimiter from str);
while next_index != 0 loop
next_index := instr(src_str,delimiter,prev_index,1);
if(next_index = 0) then
retval(cnt) := substr(src_str,prev_index);
cnt := cnt + 1;
else
retval(cnt) := substr(src_str,prev_index,next_index - prev_index);
cnt := cnt + 1;
end if;
prev_index := next_index + 1;
end loop;
RETURN retval;
END;
/CREATE OR REPLACE FUNCTION pro_test(iflag number, vsqlwhere varchar2)
RETURN qtone.ref_cursor is
res_cursor qtone.ref_cursor;
unique_key varchar2(128);
city_id integer;
city_name VARCHAR2(100);
all_frequency_no varchar2(100);
icount integer;
ipos integer;
frequency_no qtone.SUBSTR_ARRAY;
stmt VARCHAR2(100);
varsql varchar2(200);
vsql varchar2(2000);
cursor td3_cell_cur is select unique_key,city_id,city_name,all_frequency_no from td3_cell;
BEGIN
stmt := '';
varsql := 'create GLOBAL TEMPORARY TABLE c_test(city_id integer,city_name varchar(200),frequency_no integer) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE varsql;
open td3_cell_cur;
loop
fetch td3_cell_cur into unique_key,city_id,city_name,all_frequency_no;
exit when td3_cell_cur%notfound;
ipos := instr(vsqlwhere,unique_key);
if ((ipos >0 or iflag = 0) and nvl(city_id,0)<>0 and nvl(all_frequency_no,'') <> '') then
frequency_no := split_str(all_frequency_no,',');
stmt := 'insert into c_test(city_id,city_name,frequency_no) values(:city_id,:city_name,:frequency_no)';
IF(frequency_no.COUNT <> 0 ) THEN
FOR i IN frequency_no.first..frequency_no.last LOOP
EXECUTE IMMEDIATE stmt USING city_id,city_name,frequency_no(i);
END LOOP ;
commit;
END IF ;
end if;
end loop;
close td3_cell_cur;
--open res_cursor for
vsql :='select city_id,city_name,frequency_no,pinduan,count(1) frequency_num from
(
select city_id,city_name,frequency_no
,(case when frequency_no >= 1 and frequency_no <= 94 then ''GSM900''
when frequency_no >= 512 and frequency_no <= 636 then ''GSM1800''
when frequency_no >= 975 and frequency_no <= 1024 then ''EGSM''
else ''''
end) pinduan from c_test
) a group by city_id,city_name,frequency_no,pinduan';
open res_cursor for vsql;
return res_cursor;
END;
/
SQL> CREATE OR REPLACE PACKAGE qtone IS
……
……
231 /Warning: Procedure created with compilation errors.
那么你接着执行show errors,看看到底什么错误,如下:
SQL> SHOW ERRORS
Errors for PROCEDURE COMPUTE_INTENSIVE_PROGRAM:LINE/COL ERROR
-------- -----------------------------------------------------------
231/114 PLS-00109: $ERROR: compute_intensive然后具体结合你的代码来分析下。
.....
BEGIN
vsqlwhere := '';
iflag := 0;这里有点问题,没哟声明为out的参数,是不能在方法里赋值的,可以把这两个语句去掉,这里赋值,还要参数干吗呀。如果是实在需要在方法里修改参数的只,把参数定义成out类型的 如 iflag out number, vsqlwhere out varchar2
这里如果是提示有but with compilation errors 这样的错误,表示你的plsql块里有解析错误,可以运行
show errors查看错误,你这里显示no errors,我估计是不是 在运行了上面package和function1的后面运行的show errors,他们没有错误,所以提示no errors。function2是有compile错误的,你运行create function2后,show erros试试。
,(case when frequency_no >= 1 and frequency_no <= 94 then ''GSM900''
when frequency_no >= 512 and frequency_no <= 636 then ''GSM1800''
when frequency_no >= 975 and frequency_no <= 1024 then ''EGSM''
else ''''
end
这儿是不是应该改成单引号