-- 不明白楼主的意思,给个例子给你: CREATE OR REPLACE PROCEDURE tj_key_search_proc2 IS v_cnt NUMBER(18,0); v_fromdate date; v_todate date; BEGIN v_cnt := 0; v_fromdate := to_date('2010-06-29','YYYY-MM-DD'); v_todate := trunc(sysdate-1);/* DELETE FROM tj_key_search_status; DELETE FROM tj_key_search; DELETE FROM keyword; drop sequence tj_key_search_seq; CREATE SEQUENCE tj_key_search_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 1000; */ -- CREATE TABLE tj_key_search_bak as select * from tj_key_search; -- create table keyword_bak as select * from keyword; WHILE v_fromdate <= v_todate LOOP DELETE FROM keyword WHERE sflag=0; INSERT INTO keyword(keyword,sflag) SELECT distinct upper(trim(t1.v1)) as keyword, 0 as sflag FROM musicloguser_detail t1 WHERE t1.mn = 'sztysearchmusic' AND t1.k='lst' AND t1.v1 is not null AND t1.timed>=v_fromdate AND t1.timed<v_fromdate+1 AND trim(t1.v1) IS NOT NULL AND NOT EXISTS (SELECT 1 FROM keyword t2 WHERE t2.keyword=upper(trim(t1.v1)) ); COMMIT; UPDATE keyword t1 SET t1.sflag=1 WHERE t1.sflag=0 AND EXISTS (SELECT 1 FROM musicmusic t2 WHERE instr(upper(t2.musicname),t1.keyword)>=1 OR instr(upper(t2.singername),t1.keyword)>=1 OR instr(upper(t2.searchtags),t1.keyword)>=1); COMMIT; INSERT INTO tj_key_search(keyword,upper_keyword,searchtimes,cdate,sflag) SELECT trim(t1.v1) as keyword, upper(trim(t1.v1)) as upper_keyword, count(t1.logid) as searchtimes, trunc(t1.timed) cdate, decode(t2.sflag,1,1,0) as sflag FROM musicloguser_detail t1 left join keyword t2 on t2.keyword=upper(trim(t1.v1)) and t2.sflag=1 WHERE t1.mn = 'sztysearchmusic' and t1.k='lst' and trim(t1.v1) is not null and t1.timed>=v_fromdate and t1.timed<v_fromdate+1 GROUP BY trim(t1.v1), upper(trim(t1.v1)),trunc(t1.timed),decode(t2.sflag,1,1,0) ORDER BY trunc(t1.timed), trim(t1.v1); INSERT INTO tj_key_search_status(cdate) VALUES(v_fromdate); COMMIT; v_fromdate := v_fromdate + 1; END LOOP;END; /
这个和你有没有权限没有关系啊,写sql文神马的都米有关系! 但是你不能在上面创建东西鸟~
你亲自尝试下不久知道了么?declare v_ename emp.ename%type; begin for i in (select ename from emp where empno=7369) LOOP v_ename:= i.ename ; Dbms_Output.put_line('ename='||v_ename); end loop; end;
--你亲自尝试下不久知道了么?declare v_ename emp.ename%type; begin for i in (select ename from emp ) LOOP v_ename:= i.ename ; Dbms_Output.put_line('ename='||v_ename); end loop; end; PL/SQL block, executed in 0 sec. ename=SMITH ename=ALLEN ename=WARD ename=JONES ename=MARTIN ename=BLAKE ename=CLARK ename=SCOTT ename=KING ename=TURNER ename=ADAMS ename=JAMES ename=FORD ename=MILLER ename=BLACK ename=MAXTON Total execution time 0.032 sec.
CREATE OR REPLACE PROCEDURE tj_key_search_proc2
IS
v_cnt NUMBER(18,0);
v_fromdate date;
v_todate date;
BEGIN
v_cnt := 0;
v_fromdate := to_date('2010-06-29','YYYY-MM-DD');
v_todate := trunc(sysdate-1);/*
DELETE FROM tj_key_search_status;
DELETE FROM tj_key_search;
DELETE FROM keyword;
drop sequence tj_key_search_seq;
CREATE SEQUENCE tj_key_search_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 1000;
*/
-- CREATE TABLE tj_key_search_bak as select * from tj_key_search;
-- create table keyword_bak as select * from keyword; WHILE v_fromdate <= v_todate LOOP
DELETE FROM keyword WHERE sflag=0;
INSERT INTO keyword(keyword,sflag)
SELECT distinct upper(trim(t1.v1)) as keyword, 0 as sflag
FROM musicloguser_detail t1
WHERE t1.mn = 'sztysearchmusic' AND t1.k='lst' AND t1.v1 is not null
AND t1.timed>=v_fromdate
AND t1.timed<v_fromdate+1
AND trim(t1.v1) IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM keyword t2 WHERE t2.keyword=upper(trim(t1.v1)) );
COMMIT; UPDATE keyword t1
SET t1.sflag=1
WHERE t1.sflag=0
AND EXISTS (SELECT 1 FROM musicmusic t2
WHERE instr(upper(t2.musicname),t1.keyword)>=1
OR instr(upper(t2.singername),t1.keyword)>=1
OR instr(upper(t2.searchtags),t1.keyword)>=1);
COMMIT; INSERT INTO tj_key_search(keyword,upper_keyword,searchtimes,cdate,sflag)
SELECT trim(t1.v1) as keyword,
upper(trim(t1.v1)) as upper_keyword,
count(t1.logid) as searchtimes,
trunc(t1.timed) cdate,
decode(t2.sflag,1,1,0) as sflag
FROM musicloguser_detail t1 left join keyword t2 on t2.keyword=upper(trim(t1.v1)) and t2.sflag=1
WHERE t1.mn = 'sztysearchmusic' and t1.k='lst' and trim(t1.v1) is not null
and t1.timed>=v_fromdate
and t1.timed<v_fromdate+1
GROUP BY trim(t1.v1), upper(trim(t1.v1)),trunc(t1.timed),decode(t2.sflag,1,1,0)
ORDER BY trunc(t1.timed), trim(t1.v1);
INSERT INTO tj_key_search_status(cdate) VALUES(v_fromdate);
COMMIT;
v_fromdate := v_fromdate + 1;
END LOOP;END;
/
这个和你有没有权限没有关系啊,写sql文神马的都米有关系!
但是你不能在上面创建东西鸟~
你亲自尝试下不久知道了么?declare
v_ename emp.ename%type;
begin
for i in (select ename from emp where empno=7369)
LOOP
v_ename:= i.ename ;
Dbms_Output.put_line('ename='||v_ename);
end loop;
end;
--你亲自尝试下不久知道了么?declare
v_ename emp.ename%type;
begin
for i in (select ename from emp )
LOOP
v_ename:= i.ename ;
Dbms_Output.put_line('ename='||v_ename);
end loop;
end; PL/SQL block, executed in 0 sec.
ename=SMITH
ename=ALLEN
ename=WARD
ename=JONES
ename=MARTIN
ename=BLAKE
ename=CLARK
ename=SCOTT
ename=KING
ename=TURNER
ename=ADAMS
ename=JAMES
ename=FORD
ename=MILLER
ename=BLACK
ename=MAXTON
Total execution time 0.032 sec.