![](https://img-blog.csdnimg.cn/20201014180756927.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
PL/SQL编程
执着_QQ
这个作者很懒,什么都没留下…
展开
-
批量游标处理大数据
--批量游标处理大数据declare type t_rid is table of rowid index by binary_integer; l_t_rid t_rid; cursor cur_rid is( select rowid from bigtable where owner = 'SCOTT'); ln_rowcnt numbe原创 2015-03-19 16:51:10 · 1148 阅读 · 0 评论 -
利用分析函数生成连续数据
WITH cat AS ( select '1000' ID, '1010'+1 PID FROM dual union SELECT '1011' ID, '1050'+1 PID FROM dual UNION SELECT '1051' ID, '1056'+1 PID FROM dual UNION SELECT '1059' ID, '1原创 2015-03-19 16:53:56 · 365 阅读 · 0 评论 -
ROWID更新提升UPDATE性能,不能用merge时
declare maxrows number default 100000; row_id_table dbms_sql.urowid_table; --currcount_table dbms_sql.number_Table; object_name_table dbms_sql.varchar2_Table; cursor cur_b is原创 2015-03-19 16:53:57 · 589 阅读 · 0 评论 -
删除表中重复的数据
SQL1:DELETE FROM HXL WHERE ROWID IN (SELECT ROWID FROM (SELECT A.*, ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS RN_ FROM HXL A)原创 2015-03-19 16:56:40 · 264 阅读 · 0 评论 -
查找连续的数据2
WITH t AS (SELECT 1 idd,175093762 nu FROM dual UNION SELECT 1,175093763 FROM dual UNION SELECT 1,175093764 FROM dual UNION SELECT 1,175093765 FROM dual UNION SELECT 1,175093766 FROM dual UNI原创 2015-03-19 17:00:54 · 298 阅读 · 0 评论 -
使用DBLINK迁移表结构
DECLARE CURSOR c_get_table_sql IS SELECT a.table_name FROM dba_tables@dblink a WHERE a.owner = 'GXKF' AND ROWNUM = 1; --源数据 从DBLINK中找出i NUMBER(10);v_tab_name VARCHAR2(100);原创 2015-03-19 17:01:58 · 964 阅读 · 0 评论 -
查找某张表中未使用到的连续数值
SELECT * FROM HXL_CL_ZH_20150108select 1106.99 * 8 / 1024 MB FROM dual;SELECT 18633.78 * 26796 FROM dual;--分析数据WITH T AS (SELECT 12340000 beg,12349999 end_n FROM dual UNION ALL原创 2015-03-19 17:02:40 · 352 阅读 · 0 评论