orcale过程PROCEDURE执行操作

最新推荐文章于 2023-06-02 15:54:57 发布

夏微凉秋微暖

最新推荐文章于 2023-06-02 15:54:57 发布

阅读量939

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/pengbin790000/article/details/79891140

版权

sql 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE 
PROCEDURE "DELETE_SAME_JSZGXX_TMP" AS
v_jsid VARCHAR2(50);
v_num VARCHAR2(50);
v_idd VARCHAR2(50);
Cursor  cursor1 is  SELECT jsid,count(1) as num from TB_BIZ_JSZGXX_tmp GROUP BY jsid HAVING count(jsid) > 1 ORDER BY count(1) desc;
Cursor  cursor2 is  SELECT id  from TB_BIZ_JSZGXX_tmp t where t.jsid = v_jsid and ROWNUM < v_num;

begin 
					 DBMS_OUTPUT.ENABLE(buffer_size => null);	--表示输出buffer不受限制
           for x1 in cursor1 loop --循环cursor1的数据
           v_jsid := x1.jsid;
					 v_num := x1.num;
					 dbms_output.put_line('jsid:'|| v_jsid || ' num: ' || v_num); --输出
                     
					 for x2 in cursor2 loop
							 v_idd := x2.id;
							 dbms_output.put_line('id:' || v_idd);
							 DELETE from TB_BIZ_JSZGXX_tmp tt where tt.id = v_idd; --删除操作
           end loop;
           
           end loop;
end;