大家帮看一下,这个存储过程错在哪里啊?是想做个存储过程用来在数据库中建表,表结构写已有的表相同,但表名不一样,存储的也不是一样的数据.
create or replace procedure createDB(citycode in char)
is
BEGIN
if citycode is not null then
CREATE TABLE "idxTEST"."idxSPATIAL"||citycode as select * from idxspatial020 where rownum<1;
ALTER TABLE "idxTEST"."idxSPATIAL"||citycode ADD CONSTRAINT "PK_idxSPATIAL"||citycode PRIMARY KEY("idxID"); --空间索引
INSERT INTO USER_SDO_GEOM_METADATA
VALUES(
'idxSPATIAL'||citycode,
'location',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude',-180,180,10),
MDSYS.SDO_DIM_ELEMENT('Latitude',-90,90,10)
),
8307
);
CREATE 'INDEX idx_'||citycode||'_SPATIAL_IDX'
ON idxSPATIAL'||citycode||'(location)'
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE TABLE "idxTEST"."idxATTR"||citycode as select * from idxATTR020 where rownum<1;
ALTER TABLE "idxTEST"."idxATTR"||citycode ADD CONSTRAINT "PK_idxATTR"||citycode PRIMARY KEY(idxID,USERID);CREATE TABLE "idxTEST"."idxIMG"||citycode as select * from idxIMG020 where rownum<1;
ALTER TABLE "idxTEST"."idxIMG"||citycode ADD CONSTRAINT "PK_idxIMG"||citycode PRIMARY KEY("idxID", "USERID","IMGFILENAME");
end if;
END编译时报下面的错:Line # = 7 Column # = 36 Error Text = PLS-00103: 出现符号 "|"在需要下列之一时:
. , @ in <an identifier>
<a double-quoted delimited-identifier> partition subpartitionLine # = 7 Column # = 1 Error Text = PLS-00103: 出现符号 "CREATE"在需要下列之一时:
begin case declare exit
for goto if loop mod null pragma raise return select update
while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
符号 "lock在 "CREATE" 继续之前已插入。
create or replace procedure createDB(citycode in char)
is
BEGIN
if citycode is not null then
CREATE TABLE "idxTEST"."idxSPATIAL"||citycode as select * from idxspatial020 where rownum<1;
ALTER TABLE "idxTEST"."idxSPATIAL"||citycode ADD CONSTRAINT "PK_idxSPATIAL"||citycode PRIMARY KEY("idxID"); --空间索引
INSERT INTO USER_SDO_GEOM_METADATA
VALUES(
'idxSPATIAL'||citycode,
'location',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude',-180,180,10),
MDSYS.SDO_DIM_ELEMENT('Latitude',-90,90,10)
),
8307
);
CREATE 'INDEX idx_'||citycode||'_SPATIAL_IDX'
ON idxSPATIAL'||citycode||'(location)'
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE TABLE "idxTEST"."idxATTR"||citycode as select * from idxATTR020 where rownum<1;
ALTER TABLE "idxTEST"."idxATTR"||citycode ADD CONSTRAINT "PK_idxATTR"||citycode PRIMARY KEY(idxID,USERID);CREATE TABLE "idxTEST"."idxIMG"||citycode as select * from idxIMG020 where rownum<1;
ALTER TABLE "idxTEST"."idxIMG"||citycode ADD CONSTRAINT "PK_idxIMG"||citycode PRIMARY KEY("idxID", "USERID","IMGFILENAME");
end if;
END编译时报下面的错:Line # = 7 Column # = 36 Error Text = PLS-00103: 出现符号 "|"在需要下列之一时:
. , @ in <an identifier>
<a double-quoted delimited-identifier> partition subpartitionLine # = 7 Column # = 1 Error Text = PLS-00103: 出现符号 "CREATE"在需要下列之一时:
begin case declare exit
for goto if loop mod null pragma raise return select update
while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
符号 "lock在 "CREATE" 继续之前已插入。
EXECUTE IMMEDIATE SQL语句..
begin
execute immediate '你要执行的sql';
end;注:''之前字符串形式
id NUMBER(18,0),
content VARCHAR2(4000)
);INSERT INTO fruit(id, content) values(1,'"西瓜"');
INSERT INTO fruit(id, content) values(2,'"桃"');
INSERT INTO fruit(id, content) values(3,'"菠萝;西瓜"');
INSERT INTO fruit(id, content) values(4,'"西瓜;荔枝;苹果;李子"');
INSERT INTO fruit(id, content) values(5,'"苹果"');
INSERT INTO fruit(id, content) values(6,'"李子"');
INSERT INTO fruit(id, content) values(7,'"李子"');
INSERT INTO fruit(id, content) values(8,'"荔枝"');COMMIT;select * from fruit;CREATE GLOBAL TEMPORARY TABLE mem_fruit
(
fruit_name VARCHAR2(4000),
times NUMBER(18,0)
)
ON COMMIT DELETE ROWS;CREATE OR REPLACE PROCEDURE fruit_count_proc(i_fruit_name VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
sqlstr VARCHAR2(4000);
v_fruit_name VARCHAR2(1000);
v_content VARCHAR2(4000);
v_loop NUMBER(18,0);
v_split VARCHAR2(20);
CURSOR c_ur1 IS SELECT a.content FROM fruit a WHERE instr(a.content,';')>0;
CURSOR c_ur2 IS SELECT a.content FROM fruit a WHERE instr(a.content,';')>0 AND ( content LIKE '"'||i_fruit_name||';'||'%' OR content LIKE '%'||';'||i_fruit_name||'"' OR content LIKE '%'||';'||i_fruit_name||';'||'%' );
BEGIN
IF i_fruit_name IS NULL THEN -- 如果传入的水果名称为空,统计所有水果出现的次数
-- 先汇总一行记录中只出现单个水果名称的记录行(假设你当有多个水果名称时,名称之间以“;”字符分隔
sqlstr := 'INSERT INTO mem_fruit SELECT a.content fruit_name, count(1) as times FROM fruit a WHERE instr(a.content,'';'')=0 GROUP BY a.content';
EXECUTE IMMEDIATE sqlstr; FOR r_ur in c_ur1 LOOP
v_content :=';'||replace(r_ur.content,'"','')||';';
v_loop := length(v_content)-length(replace(v_content,';',''));
FOR i in 2 .. v_loop LOOP
v_split := substr(v_content, instr(v_content,';',1,i-1)+1, instr(v_content,';',1,i)-instr(v_content,';',1,i-1)-1);
v_split := '"'||v_split||'"';
INSERT INTO mem_fruit(fruit_name,times) VALUES(v_split,1);
END LOOP;
END LOOP; ELSE
v_fruit_name := '"'||i_fruit_name||'"';
sqlstr := 'INSERT INTO mem_fruit SELECT a.content fruit_name, count(1) as times FROM fruit a WHERE instr(a.content,'';'')=0 AND content=:v_fruit_name GROUP BY a.content';
EXECUTE IMMEDIATE sqlstr USING v_fruit_name; FOR r_ur in c_ur2 LOOP
v_content :=';'||replace(r_ur.content,'"','')||';';
v_loop := length(v_content)-length(replace(v_content,';',''));
FOR i in 2 .. v_loop LOOP
v_split := substr(v_content, instr(v_content,';',1,i-1)+1, instr(v_content,';',1,i)-instr(v_content,';',1,i-1)-1);
v_split := '"'||v_split||'"';
INSERT INTO mem_fruit(fruit_name,times) VALUES(v_split,1);
END LOOP;
END LOOP;
END IF; sqlstr := 'SELECT fruit_name, sum(times) as cnt FROM mem_fruit WHERE ( fruit_name=:v_fruit_name OR :i_fruit_name IS NULL) GROUP BY fruit_name ORDER BY sum(times) desc';
OPEN o_cur FOR sqlstr USING v_fruit_name, i_fruit_name;
END;
/set serveroutput on;
var c_cur refcursor;
exec fruit_count_proc('李子',:c_cur);
print c_cur;set serveroutput on;
var c_cur refcursor;
exec fruit_count_proc('',:c_cur);
print c_cur;
-- 来自:http://topic.csdn.net/u/20100716/09/1d150dc0-2f39-44b8-bc32-41f89591056a.html