sql语句的长度限制 我想写一个Update语句,形式如下:Update tab_name Set Sign=1 where Id in (......);in 语句可能非常长.不知通行不行? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 id 长度为20, in(...) 中可能有大几万个id 转别人的,看看:Oracle-SQL长度限制 最近遇见一个ORA-01795: maximum number of expressions in a list is 1000 错误,才知道In-list有1000个元素的限制;可以使用array bind或者temporary table解决,而且还能绑定变量。[Limit and conversion very long IN list : WHERE x IN ( ,,, ...) ]再想到SQL的长度是否在SQLPLUS里面还有其他的限制?Logical Database LimitsSQL Statement LengthMaximum length of statements64 K maximum; particular tools may impose lower limits9i的文档写的最大64K,其实不正确。在10g的文档中作了修改 Logical Database LimitsThe limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory仔细想想,我们数据库中 package 几十K有很多,当初都是用sqlplus执行创建的;sqlpplus对日常执行的SQL因该不会有长度限制。先测试一下使用DBMS_SQL执行很长的SQL语句。SQL> declare2 l_stmt dbms_sql.varchar2s;l_cursor integer default dbms_sql.open_cursor;l_rows number default 0;3 4 5 l_length number := 0;6 begin7 l_stmt(1) := 'select 1 c1';8 for i in 2 .. 150009 loop10 l_stmt(i) := ', 1 c' || i;11 end loop;12 l_stmt(15000) := 'from dual';13 for i in l_stmt.first .. l_stmt.last14 loop15 l_length := l_length + length(l_stmt(i));16 end loop;17 dbms_output.put_line( 'length = ' || l_length );18 dbms_sql.parse( c => l_cursor,19 statement => l_stmt,20 lb => l_stmt.first,21 ub => l_stmt.last,22 lfflg => TRUE,23 language_flag => dbms_sql.native );25 l_rows := dbms_sql.execute(l_cursor);26 dbms_sql.close_cursor( l_cursor );27 end;28 /length = 138898PL/SQL procedure successfully completed.通过dbms_sql.varchar2s数组,可以执行很长的SQL。再将如上pl/sql产生的SQL使用dbms_output输出longSQL.sql.SQL> host ls -l longSQL.sql-rw-r--r-- 1 oracle dba 1215082 Dec 4 05:19 longSQL.sqlSQL> host head longSQL.sqlselect 1 c1, 1 c2...........................SQL> host tail longSQL.sql................, 1 c14999, 1 c15000from dual;这个1M的SQL在sqlplus里完全可以执行。看来,oracle完全支持很长的SQL语句;但该sql会占用很多share pool空间,因此不提倡经常执行。SQL> select sql_text, SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM from v$sql where sql_text like '%, 1 c2%';SQL_TEXT--------------------------------------------------------------------------SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM------------ -------------- -----------select 1 c1 , 1 c2 , 1 c3 , 1 c4 , 1 c5 , 1 c6 , 1 c7 , 1 c8 , 1 c9 , 1 c10 , 1c11 , 1 c12 , 1 c13 , 1 c14 , 1 c15 , 1 c16 , 1 c17 , 1 c18 , 1 c19 , 1 , 1 .............................................................................................1978774 780448 241252 按时间段统计的SQL oracle基础问题怎么建库及用户 求新版oracle(中文版)! 一个insert的问题,大侠指教,在线等,急! 帮忙看一下,这个句子为什么出不来结果?谢谢。语法好像没错,只是一执行就处于等待状态 如何用exp导出远程数据库? 问一个简单的关于表空间的问题,请不要见笑。 Oracle中嵌套表的问题 约束最复杂能到什么程度? 在线急等!读取CLOB字段,字符串缓冲区太小,怎么解决? ORA-12154 无法处理服务名 循环插入数据的问题--存储过程
最近遇见一个ORA-01795: maximum number of expressions in a list is 1000 错误,才知道In-list有1000个元素的限制;可以使用array bind或者temporary table解决,而且还能绑定变量。[Limit and conversion very long IN list : WHERE x IN ( ,,, ...) ]再想到SQL的长度是否在SQLPLUS里面还有其他的限制?Logical Database LimitsSQL Statement Length
Maximum length of statements
64 K maximum; particular tools may impose lower limits9i的文档写的最大64K,其实不正确。在10g的文档中作了修改 Logical Database LimitsThe limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory仔细想想,我们数据库中 package 几十K有很多,当初都是用sqlplus执行创建的;sqlpplus对日常执行的SQL因该不会有长度限制。先测试一下使用DBMS_SQL执行很长的SQL语句。SQL> declare
2 l_stmt dbms_sql.varchar2s;
l_cursor integer default dbms_sql.open_cursor;
l_rows number default 0;
3 4 5 l_length number := 0;
6 begin
7 l_stmt(1) := 'select 1 c1';
8 for i in 2 .. 15000
9 loop
10 l_stmt(i) := ', 1 c' || i;
11 end loop;
12 l_stmt(15000) := 'from dual';
13 for i in l_stmt.first .. l_stmt.last
14 loop
15 l_length := l_length + length(l_stmt(i));
16 end loop;
17 dbms_output.put_line( 'length = ' || l_length );
18 dbms_sql.parse( c => l_cursor,
19 statement => l_stmt,
20 lb => l_stmt.first,
21 ub => l_stmt.last,
22 lfflg => TRUE,
23 language_flag => dbms_sql.native );
25 l_rows := dbms_sql.execute(l_cursor);
26 dbms_sql.close_cursor( l_cursor );
27 end;
28 /
length = 138898PL/SQL procedure successfully completed.通过dbms_sql.varchar2s数组,可以执行很长的SQL。再将如上pl/sql产生的SQL使用dbms_output输出longSQL.sql.SQL> host ls -l longSQL.sql
-rw-r--r-- 1 oracle dba 1215082 Dec 4 05:19 longSQL.sqlSQL> host head longSQL.sql
select 1 c1
, 1 c2
...........................SQL> host tail longSQL.sql
................
, 1 c14999
, 1 c15000
from dual;这个1M的SQL在sqlplus里完全可以执行。看来,oracle完全支持很长的SQL语句;但该sql会占用很多share pool空间,因此不提倡经常执行。SQL> select sql_text, SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM from v$sql where sql_text like '%, 1 c2%';SQL_TEXT
--------------------------------------------------------------------------
SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
------------ -------------- -----------
select 1 c1 , 1 c2 , 1 c3 , 1 c4 , 1 c5 , 1 c6 , 1 c7 , 1 c8 , 1 c9 , 1 c10 , 1
c11 , 1 c12 , 1 c13 , 1 c14 , 1 c15 , 1 c16 , 1 c17 , 1 c18 , 1 c19 , 1 , 1 .............................................................................................
1978774 780448 241252