~SingleForum~无废话记录~: Forum: other

~SingleForum~无废话记录~: Forum: other - Recent Topics http://www.ll19.com/bb/ ~19~记录点有用的好吗？少说废话少装b~ en Wed, 08 Feb 2012 16:08:35 +0000 admin on "innodb和myisam" http://www.ll19.com/bb/topic/123#post-123 Tue, 12 Jan 2010 03:13:54 +0000 admin 123@http://www.ll19.com/bb/ myisam属于非事务安全型，innodb和bdb属于事务安全型，在使用上，包括建立表，insert，select等等操作上有没有什么区别。但是myisam引擎速度比较快、比较简单、而InnoDB支持外键、其速度相对就慢了。 MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。这样就可以根据数据表不同的用处是用不同的存储类型。另外，MyISAM类型的二进制数据文件可以在不同操作系统中迁移。也就是可以直接从Windows系统拷贝到linux系统中使用。所以我一般都是使用myisam，因为这次用的也不是很深入的。 如果把这类型从MyISAM转成 InnoDB会不会有问题、但是全文索引失效. 使用两种的选择：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。如果执行大量的SELECT，MyISAM是更好的选择。若需要使用事务处理，但是原来的数据表使用的是myisam，就需要改为bdb或者innodb，这样基于myisam的程序，将类型改为innodb后，其程序不用改动…… admin on "MySQL 更改表的字符编码" http://www.ll19.com/bb/topic/122#post-122 Tue, 12 Jan 2010 02:41:06 +0000 admin 122@http://www.ll19.com/bb/ MySQL 更改表的字符编码

PLAIN TEXT

SQL:

ALTER TABLE privilege(1) convert TO character SET utf8 collate utf8_general_ci(2);

(1)表示要改的表的字符编码 (2)表示要改成什么样的字符编码 admin on "LONG CLOB BLOB LONG RAW 区别" http://www.ll19.com/bb/topic/104#post-104 Sun, 03 Jan 2010 16:08:22 +0000 admin 104@http://www.ll19.com/bb/ 要保存图片、文本文件、Word文件各自最好用哪种数据类型? BLOB最好，LONG　RAW也不错，但Long是 oracle将要废弃的类型，因此建议用BLOB。 个人理解，基本上来说可以理解LONG存储大于4000的字符而BLOB用于存储附件流的二进制数据 LONG: 可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，可以存储长文本一个表中最多一个LONG列 LONG RAW: 可变长二进制数据，最长2G（注意是二进制不是字符） 　　CLOB: 字符大对象Clob 用来存储单字节的字符数据 　　NCLOB: 用来存储多字节的字符数据 BLOB: 用于存储二进制数据 ORACLE建议在存储大字段使用LOB型 　　BFILE: 存储在文件中的二进制数据，这个文件中的数据只能被只读访。但该文件不包含在数据库内。 bfile字段实际的文件存储在文件系统中,字段中存储的是文件定位指针.bfile对oracle来说是只读的,也不参与事务性控制和数据恢复. 　　 　　CLOB，NCLOB，BLOB都是内部的LOB(Large Object)类型，最长4G，没有LONG只能有一列的限制 admin on "使用urlencode" http://www.ll19.com/bb/topic/98#post-98 Sun, 03 Jan 2010 16:01:02 +0000 admin 98@http://www.ll19.com/bb/ 使用urlencode 　　UrlEncode 　　将字符串以 URL 编码。 　　返回值: 字符串 　　函数种类: 编码处理 　　内容说明 　　本函数将字符串以 URL 编码。例如空格就会变成加号。Homepage 中 form 资料传送就是用 urlencode 编码后再送出。 　　ASP中的用法: 　　Server.URLEncode("内容") 　　例: 　　<% 　　response.write Server.UrlEncode("七七IT中国") 　　%> 　　PHP中的用法: 　　urlencode("内容"); 　　例: 　　<? 　　echo urlencode("七七IT中国"); 　　?> 　　JSP中的用法: 　　URLEncoder.encode("七七IT中国"); 　　<% 　　java.net.URLEncoder.encode("七七IT中国"); 　　%> admin on "ORACLE行转列" http://www.ll19.com/bb/topic/95#post-95 Sun, 03 Jan 2010 15:54:32 +0000 admin 95@http://www.ll19.com/bb/ 物料需要数量需要仓库现存量仓库现存量仓库数量批次 A1 2 C1 C1 20 123 A1 2 C1 C2 30 111 A1 2 C1 C2 20 222 A1 2 C1 C3 10 211 A2 3 C4 C1 40 321 A2 3 C4 C4 50 222 A2 3 C4 C4 60 333 A2 3 C4 C5 70 223 我需要把上面的查询结果转换为下面的。 物料需要数量需要仓库 C1 C2 C3 C4 C5 A1 2 C1 20 50 10 0 0 A2 3 C4 40 0 0 110 70 */ ---------------------------------------------------------------建表 ----------------判断表是否存在 declare num number; begin select count(1) into num from user_tables where table_name='TEST'; if num>0 then execute immediate 'drop table TEST'; end if; end; ----------------建表 CREATE TABLE TEST( WL VARCHAR2(10), XYSL INTEGER, XYCK VARCHAR2(10), XCLCK VARCHAR2(10), XCLCKSL INTEGER, PC INTEGER ); ----------------第一部分测试数据 INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223); COMMIT; --select * from test; ---------------------------------------------------------------行转列的存储过程 CREATE OR REPLACE PROCEDURE P_TEST IS V_SQL VARCHAR2(2000); CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK; BEGIN V_SQL := 'SELECT WL,XYSL,XYCK'; FOR V_XCLCK IN CURSOR_1 LOOP V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK || ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK; END LOOP; V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK'; --DBMS_OUTPUT.PUT_LINE(V_SQL); V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '|| V_SQL; --DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL; END; ----------------------------------------------------------------结果 ----------------执行存储过程，生成视图 BEGIN P_TEST; END; ----------------结果 SELECT * FROM RESULT T; WL XYSL XYCK C1 C2 C3 C4 C5 ---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- A1 2 C1 20 50 10 0 0 A2 3 C4 40 0 0 110 70 ----------------第二部分测试数据 INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' , 20, 124); INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' , 30, 121); INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' , 20, 322); COMMIT; ----------------报告存储过程，生成视图 BEGIN P_TEST; END; ----------------结果 SELECT * FROM RESULT T; WL XYSL XYCK C1 C2 C3 C4 C5 C6 C7 C8 ----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ---------- A1 2 C1 20 50 10 0 0 20 0 0 A2 2 C1 0 0 0 0 0 0 30 0 A2 3 C4 40 0 0 110 70 0 0 0 A3 2 C1 0 0 0 0 0 0 0 20 --------------- 删除实体 DROP VIEW RESULT; DROP PROCEDURE P_TEST; DROP TABLE TEST; admin on "ORACLE多行合并" http://www.ll19.com/bb/topic/94#post-94 Sun, 03 Jan 2010 15:53:17 +0000 admin 94@http://www.ll19.com/bb/ 例如有如下数据：m_researcher_stock_rel表 N_SEC_CODE C_RESEARCHER_CODE 000297 chenpeng 000297 liusu 合并处理后需显示为： 000297 chenpeng,liusu wmsys.wm_concat 的用法 select N_SEC_CODE, wmsys.wm_concat(C_RESEARCHER_CODE) p from m_researcher_stock_rel GROUP BY N_SEC_CODE admin on "存储过程返回结果集" http://www.ll19.com/bb/topic/93#post-93 Sun, 03 Jan 2010 15:50:08 +0000 admin 93@http://www.ll19.com/bb/ 首先要明确一个概念 PROCEDURE TRIGGER PACKAGE 的区别 简单的说，PROCEDURE, TRIGGER都是一段程序 但PROCEDURE需要手工调用，TRIGGER满足你设定的条件会自动执行 PACKAGE是把几个程序封装在一起，和JAVA里的PACKAGE的概念差不多。 得到返回的结果集需要建立PACKAGE，并且返回列表需要通过返回游标实现 由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分。 1、在SQL*PLUS中建一个程序包（在包中定义了一个类型游标） CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE Test_CURSOR IS REF CURSOR; procedure TESTC(cur_ref out Test_CURSOR); end TESTPACKAGE; 2、建立包之后建立存储过程 create or replace package body TESTPACKAGE as procedure TESTC(cur_ref out Test_CURSOR) is begin OPEN cur_ref FOR select * from test; end TESTC; END TESTPACKAGE; 可以看到，它是把游标(cursor)（可以理解为一个指针），作为一个out 参数来返回值的。 JAVA调用程序如下：

PLAIN TEXT

JAVA:

package com.rizon.tree.excelApi;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CursorTest {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
Connection conn = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@10.0.41.100:1521:orcl", "forestrytree",
"forestrytree");
oracle.jdbc.OracleCallableStatement stmt = (oracle.jdbc.OracleCallableStatement) conn
.prepareCall("{call TESTPACKAGE.TESTC(?)}");
// stmt.setString(1, "1");
stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
stmt.execute();
rs = stmt.getCursor(1);
for (int i = 0; rs.next();) {
System.out.println(" ----[" + i + "]name:" + rs.getString(1)
+ "telephone:" + rs.getString(2));
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (conn != null) {
conn.close();
conn = null;
}
if (rs != null) {
rs.close();
rs = null;
}
}
}
}

admin on "存储过程记录（只有输入参数）" http://www.ll19.com/bb/topic/91#post-91 Sun, 03 Jan 2010 15:46:05 +0000 admin 91@http://www.ll19.com/bb/ 过程 过程是为了执行一定任务而组合在一起的SQL语句和PLSQL语句。（个人理解过程就是ORACLE的程序（或者说是方法、函数）） 只有输入参数时候的例子 创建存储过程 创建或替换一个存储过程myproc 参数为id ORACLE中参数默认为输入参数 in （之后用is 或者 as 都可以） CREATE or replace procedure myproc(id in varchar2) Is 定义一个变量注意：定义变量一定要给出长度，参数可以不给出长度 Name varchar2(10); Begin 将books_name放置到变量里面 Select books_name into Name form books where books_id = id; Dbms_Output.put_line(Name); End myproc; 执行存储过程 Declare 定义变量 Tid vachar2(10); Begin 赋值 Tid := ‘001’; Myproc(Tid); End; 完整内容： CREATE or replace procedure myproc(idcs in varchar2) Is Namebl varchar2(10); Begin select name into Namebl from test where id = idcs; Dbms_Output.put_line(Namebl); End myproc; Declare Tid integer(10); Begin Tid := 1; Myproc(Tid); End; 或者 Begin Myproc(1); End; JAVA程序 import java.sql.*; import java.io.OutputStream; import java.io.Writer; import java.sql.PreparedStatement; import java.sql.ResultSet; import oracle.jdbc.driver.*; public class TestProcedureOne { public TestProcedureOne() { } public static void main(String args ){ String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@192.168.10.216:1521:ctbu"; Statement stmt = null; ResultSet rs = null; Connection conn = null; CallableStatement cstmt = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "dbname", "password"); CallableStatement proc = null; proc = conn.prepareCall("{ call TESTA(?,?) }"); proc.setString(1, 1); proc.setString(2, "232323"); proc.execute(); } catch (SQLException ex2) { ex2.printStackTrace(); } catch (Exception ex2) { ex2.printStackTrace(); } finally{ try { if(rs != null){ rs.close(); if(stmt!=null){ stmt.close(); } if(conn!=null){ conn.close(); } } } catch (SQLException ex1) { } } } } admin on "游标的记录" http://www.ll19.com/bb/topic/90#post-90 Sun, 03 Jan 2010 15:42:39 +0000 admin 90@http://www.ll19.com/bb/ 游标的作用 游标可以对数据进行逐条的控制 平常写SELECT语句的时候得到的是一个统一的结果集，无法对结果进行逐条的控制，游标便可以实现逐条的控制。 游标通过操作结果集中的数据，逐行进行处理来达某种目的。 游标的使用方法 比如需要对BOOKS表的每条记录进行逐条处理，首先在内存中申请空间，例如需要处理的select语句是Select * form books；，则： Declare CURSOR mycur is Select * form books； 需要对此结果进行逐行的处理，则将游标对应的数据取出放在一个变量里，事先需要准备好一个变量，由于是表的所有字段，则这么写： Myrecord books%ROWTYPE; 接着就是将结果集打开 Begin Open mycur; 打开之后则把数据放到事先声明的变量里面 FETCH mycur INTO Myrecord; 之后循环判断取数据 WHILE mycur%FOUND 表示当游标中有数据可取 %FOUND为游标的四个属性的一个返回布尔值 之后循环取值将数据显示： WHILE mycur%FOUND LOOP Dbms_Output.put_line(Myrecord.id || Myrecord.name); FETCH mycur INTO Myrecord; END LOOP; Close mycur; End; / 其中id就是BOOKS表中的字段id name同样 当数据量很大，不建议使用游标，比较消耗资源。 admin on "ORA-01795:列表中的最大表达式数为1000的错误" http://www.ll19.com/bb/topic/88#post-88 Sun, 03 Jan 2010 15:37:37 +0000 admin 88@http://www.ll19.com/bb/ Oracle数据库的in操作，最多只能支持1000个对象，假如超过了1000个对象，就会出现报错：

PLAIN TEXT

SQL:

SQL&gt; CREATE TABLE T AS SELECT ROWNUM ID, OBJECT_NAME,
OBJECT_TYPE FROM DBA_OBJECTS;

表已创建。

PLAIN TEXT

SQL:

SQL&gt; SELECT COUNT(*) FROM T
2 WHERE ID IN
3 (
4 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,
5 22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,
6 40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
7 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,
8 76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,
9 94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,
10 109,110,111,112,113,114,115,116,117,118,119,120,121,122,
11 123,124,125,126,127,128,129,130,131,132,133,134,135,136,
12 137,138,139,140,141,142,143,144,145,146,147,148,149,150,
13 151,152,153,154,155,156,157,158,159,160,161,162,163,164,
14 165,166,167,168,169,170,171,172,173,174,175,176,177,178,
15 179,180,181,182,183,184,185,186,187,188,189,190,191,192,
16 193,194,195,196,197,198,199,200,201,202,203,204,205,206,
17 207,208,209,210,211,212,213,214,215,216,217,218,219,220,
18 221,222,223,224,225,226,227,228,229,230,231,232,233,234,
19 235,236,237,238,239,240,241,242,243,244,245,246,247,248,
20 249,250,251,252,253,254,255,256,257,258,259,260,261,262,
21 263,264,265,266,267,268,269,270,271,272,273,274,275,276,
22 277,278,279,280,281,282,283,284,285,286,287,288,289,290,
23 291,292,293,294,295,296,297,298,299,300,301,302,303,304,
24 305,306,307,308,309,310,311,312,313,314,315,316,317,318,
25 319,320,321,322,323,324,325,326,327,328,329,330,331,332,
26 333,334,335,336,337,338,339,340,341,342,343,344,345,346,
27 347,348,349,350,351,352,353,354,355,356,357,358,359,360,
28 361,362,363,364,365,366,367,368,369,370,371,372,373,374,
29 375,376,377,378,379,380,381,382,383,384,385,386,387,388,
30 389,390,391,392,393,394,395,396,397,398,399,400,401,402,
31 403,404,405,406,407,408,409,410,411,412,413,414,415,416,
32 417,418,419,420,421,422,423,424,425,426,427,428,429,430,
33 431,432,433,434,435,436,437,438,439,440,441,442,443,444,
34 445,446,447,448,449,450,451,452,453,454,455,456,457,458,
35 459,460,461,462,463,464,465,466,467,468,469,470,471,472,
36 473,474,475,476,477,478,479,480,481,482,483,484,485,486,
37 487,488,489,490,491,492,493,494,495,496,497,498,499,500,
38 501,502,503,504,505,506,507,508,509,510,511,512,513,514,
39 515,516,517,518,519,520,521,522,523,524,525,526,527,528,
40 529,530,531,532,533,534,535,536,537,538,539,540,541,542,
41 543,544,545,546,547,548,549,550,551,552,553,554,555,556,
42 557,558,559,560,561,562,563,564,565,566,567,568,569,570,
43 571,572,573,574,575,576,577,578,579,580,581,582,583,584,
44 585,586,587,588,589,590,591,592,593,594,595,596,597,598,
45 599,600,601,602,603,604,605,606,607,608,609,610,611,612,
46 613,614,615,616,617,618,619,620,621,622,623,624,625,626,
47 627,628,629,630,631,632,633,634,635,636,637,638,639,640,
48 641,642,643,644,645,646,647,648,649,650,651,652,653,654,
49 655,656,657,658,659,660,661,662,663,664,665,666,667,668,
50 669,670,671,672,673,674,675,676,677,678,679,680,681,682,
51 683,684,685,686,687,688,689,690,691,692,693,694,695,696,
52 697,698,699,700,701,702,703,704,705,706,707,708,709,710,
53 711,712,713,714,715,716,717,718,719,720,721,722,723,724,
54 725,726,727,728,729,730,731,732,733,734,735,736,737,738,
55 739,740,741,742,743,744,745,746,747,748,749,750,751,752,
56 753,754,755,756,757,758,759,760,761,762,763,764,765,766,
57 767,768,769,770,771,772,773,774,775,776,777,778,779,780,
58 781,782,783,784,785,786,787,788,789,790,791,792,793,794,
59 795,796,797,798,799,800,801,802,803,804,805,806,807,808,
60 809,810,811,812,813,814,815,816,817,818,819,820,821,822,
61 823,824,825,826,827,828,829,830,831,832,833,834,835,836,
62 837,838,839,840,841,842,843,844,845,846,847,848,849,850,
63 851,852,853,854,855,856,857,858,859,860,861,862,863,864,
64 865,866,867,868,869,870,871,872,873,874,875,876,877,878,
65 879,880,881,882,883,884,885,886,887,888,889,890,891,892,
66 893,894,895,896,897,898,899,900,901,902,903,904,905,906,
67 907,908,909,910,911,912,913,914,915,916,917,918,919,920,
68 921,922,923,924,925,926,927,928,929,930,931,932,933,934,
69 935,936,937,938,939,940,941,942,943,944,945,946,947,948,
70 949,950,951,952,953,954,955,956,957,958,959,960,961,962,
71 963,964,965,966,967,968,969,970,971,972,973,974,975,976,
72 977,978,979,980,981,982,983,984,985,986,987,988,989,990,
73 991,992,993,994,995,996,997,998,999,1000,1001,1002,1003
74 );

991,992,993,994,995,996,997,998,999,1000,1001,1002,1003 * 第 73 行出现错误: ORA-01795: 列表中的最大表达式数为 1000 当然使用拆分表达式的方式可以实现：

PLAIN TEXT

SQL:

SQL&gt; SELECT COUNT(*) FROM T
2 WHERE ID IN
3 (
4 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,
5 22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,
6 40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
7 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,
8 76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,
9 94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,
10 109,110,111,112,113,114,115,116,117,118,119,120,121,122,
11 123,124,125,126,127,128,129,130,131,132,133,134,135,136,
12 137,138,139,140,141,142,143,144,145,146,147,148,149,150,
13 151,152,153,154,155,156,157,158,159,160,161,162,163,164,
14 165,166,167,168,169,170,171,172,173,174,175,176,177,178,
15 179,180,181,182,183,184,185,186,187,188,189,190,191,192,
16 193,194,195,196,197,198,199,200,201,202,203,204,205,206,
17 207,208,209,210,211,212,213,214,215,216,217,218,219,220,
18 221,222,223,224,225,226,227,228,229,230,231,232,233,234,
19 235,236,237,238,239,240,241,242,243,244,245,246,247,248,
20 249,250,251,252,253,254,255,256,257,258,259,260,261,262,
21 263,264,265,266,267,268,269,270,271,272,273,274,275,276,
22 277,278,279,280,281,282,283,284,285,286,287,288,289,290,
23 291,292,293,294,295,296,297,298,299,300,301,302,303,304,
24 305,306,307,308,309,310,311,312,313,314,315,316,317,318,
25 319,320,321,322,323,324,325,326,327,328,329,330,331,332,
26 333,334,335,336,337,338,339,340,341,342,343,344,345,346,
27 347,348,349,350,351,352,353,354,355,356,357,358,359,360,
28 361,362,363,364,365,366,367,368,369,370,371,372,373,374,
29 375,376,377,378,379,380,381,382,383,384,385,386,387,388,
30 389,390,391,392,393,394,395,396,397,398,399,400,401,402,
31 403,404,405,406,407,408,409,410,411,412,413,414,415,416,
32 417,418,419,420,421,422,423,424,425,426,427,428,429,430,
33 431,432,433,434,435,436,437,438,439,440,441,442,443,444,
34 445,446,447,448,449,450,451,452,453,454,455,456,457,458,
35 459,460,461,462,463,464,465,466,467,468,469,470,471,472,
36 473,474,475,476,477,478,479,480,481,482,483,484,485,486,
37 487,488,489,490,491,492,493,494,495,496,497,498,499,500,
38 501,502,503,504,505,506,507,508,509,510,511,512,513,514,
39 515,516,517,518,519,520,521,522,523,524,525,526,527,528,
40 529,530,531,532,533,534,535,536,537,538,539,540,541,542,
41 543,544,545,546,547,548,549,550,551,552,553,554,555,556,
42 557,558,559,560,561,562,563,564,565,566,567,568,569,570,
43 571,572,573,574,575,576,577,578,579,580,581,582,583,584,
44 585,586,587,588,589,590,591,592,593,594,595,596,597,598,
45 599,600,601,602,603,604,605,606,607,608,609,610,611,612,
46 613,614,615,616,617,618,619,620,621,622,623,624,625,626,
47 627,628,629,630,631,632,633,634,635,636,637,638,639,640,
48 641,642,643,644,645,646,647,648,649,650,651,652,653,654,
49 655,656,657,658,659,660,661,662,663,664,665,666,667,668,
50 669,670,671,672,673,674,675,676,677,678,679,680,681,682,
51 683,684,685,686,687,688,689,690,691,692,693,694,695,696,
52 697,698,699,700,701,702,703,704,705,706,707,708,709,710,
53 711,712,713,714,715,716,717,718,719,720,721,722,723,724,
54 725,726,727,728,729,730,731,732,733,734,735,736,737,738,
55 739,740,741,742,743,744,745,746,747,748,749,750,751,752,
56 753,754,755,756,757,758,759,760,761,762,763,764,765,766,
57 767,768,769,770,771,772,773,774,775,776,777,778,779,780,
58 781,782,783,784,785,786,787,788,789,790,791,792,793,794,
59 795,796,797,798,799,800,801,802,803,804,805,806,807,808,
60 809,810,811,812,813,814,815,816,817,818,819,820,821,822,
61 823,824,825,826,827,828,829,830,831,832,833,834,835,836,
62 837,838,839,840,841,842,843,844,845,846,847,848,849,850,
63 851,852,853,854,855,856,857,858,859,860,861,862,863,864,
64 865,866,867,868,869,870,871,872,873,874,875,876,877,878,
65 879,880,881,882,883,884,885,886,887,888,889,890,891,892,
66 893,894,895,896,897,898,899,900,901,902,903,904,905,906,
67 907,908,909,910,911,912,913,914,915,916,917,918,919,920,
68 921,922,923,924,925,926,927,928,929,930,931,932,933,934,
69 935,936,937,938,939,940,941,942,943,944,945,946,947,948,
70 949,950,951,952,953,954,955,956,957,958,959,960,961,962,
71 963,964,965,966,967,968,969,970,971,972,973,974,975,976,
72 977,978,979,980,981,982,983,984,985,986,987,988,989,990,
73 991,992,993,994,995,996,997,998,999,1000)
74 OR ID IN (1001,1002,1003);

admin on "Oracle递归查询" http://www.ll19.com/bb/topic/87#post-87 Sun, 03 Jan 2010 15:35:43 +0000 admin 87@http://www.ll19.com/bb/ Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。 创建示例表：

PLAIN TEXT

SQL:

CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);

插入测试数据：

PLAIN TEXT

SQL:

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归

PLAIN TEXT

SQL:

SELECT * FROM TBL_TEST
start with id=1
connect by prior id = pid

从末梢往树ROOT递归

PLAIN TEXT

SQL:

SELECT * FROM TBL_TEST
start with id=5
connect by prior pid = id

admin on "TOMCAT为单个SERVLET设置超时" http://www.ll19.com/bb/topic/82#post-82 Mon, 08 Jun 2009 08:14:38 +0000 admin 82@http://www.ll19.com/bb/ 设置全局SESSION在WEB.XML中

PLAIN TEXT

XML:

&lt;session-config&gt;
&lt;session-timeout&gt;30&lt;/session-timeout&gt;//单位为分钟
&lt;/session-config&gt;

为单个Servlet指定会话超时时间

PLAIN TEXT

XML:

&lt;servlet&gt;
&lt;servlet-name&gt;Servlet名称&lt;/servlet-name&gt;
&lt;servlet-class&gt;Servlet类路径&lt;/servlet-class&gt;
&lt;init-param&gt;
&lt;param-name&gt;timeout&lt;/param-name&gt;
&lt;param-value&gt;600&lt;/param-value&gt;
&lt;/init-param&gt;
&lt;/servlet&gt;

admin on "黄金12宫单挑能力排行" http://www.ll19.com/bb/topic/63#post-63 Thu, 02 Apr 2009 10:22:53 +0000 admin 63@http://www.ll19.com/bb/ 12：阿鲁迪巴 ——被N多人一拳放倒过的大汉，再次印证了“巴西人除了卡卡绝对信不过”的真理 11：阿布罗狄 ——圣斗士里的郭敬明，2B一个，喜欢被爆菊，遇到瞬之后如愿以偿！ 10：修罗 ——事实证明手刀根本不好使，尤其是和加藤鹰学的手刀，对男人没用 9：迪斯马斯克 ——这逼比绿豆蛙动画片里那个蓝色小螃蟹还弱，还喜欢装B，果然遭了雷劈，该！ 8：艾欧里亚 ——圣斗士里的马超，拳大无脑，不需多说 7：米罗 ——指甲油的托，女人精一个！打架基本靠挠，亮相基本靠骚！ 6：卡谬 ——连徒孙都打不过，那招什么女神曙光根本没用，给女神当按摩捧还差不多 5：穆 ——只会防守，进攻乏力 4：艾俄罗斯 ——挺厉害的，但是选择星矢作为接班人，证明他眼神不好！ 3：沙加 ——阿三只能排第三！ 2：双子那俩卵 ——双子嘛！二打一总是有优势的！就像3P那样 1：童虎 ——中国人无敌，何况还能蜕一次皮！ admin on "如果出熊放下BL再三本无任何意义~" http://www.ll19.com/bb/topic/45#post-45 Mon, 16 Mar 2009 09:16:28 +0000 admin 45@http://www.ll19.com/bb/ 由于熊出太慢总是在熊没出来时候被日，特此记录： 2本好了直接就3本没必要放BL再3本，你放下BL又怎样没有任何意义还容易被对手打爆。 admin on "oracle监听不能启动的问题及处理过程" http://www.ll19.com/bb/topic/44#post-44 Mon, 16 Mar 2009 09:09:32 +0000 admin 44@http://www.ll19.com/bb/ oracle监听不能启动的问题及处理过程！ oracle环境如下： Q：链接oracle时报错：ORA-12541: TNS: 无监听程序 A： 1〉查看监听有没有启动： 一：运行lsnrctl C:\Documents and Settings\Admin>lsnrctl LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-4月 -2007 09: 1:43 Copyright (c) 1991, 2005, Oracle. All rights reserved. 欢迎来到LSNRCTL, 请键入"help"以获得信息。 二：查看stauts LSNRCTL> status 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) TNS-12541: TNS: 无监听程序 TNS-12560: TNS: 协议适配器错误 TNS-00511: 无监听程序 32-bit Windows Error: 2: No such file or directory 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zxt)(PORT=1521))) TNS-12541: TNS: 无监听程序 TNS-12560: TNS: 协议适配器错误 TNS-00511: 无监听程序 32-bit Windows Error: 61: Unknown error 三：发现监听没有启动，现在启动监听 LSNRCTL> start 启动tnslsnr: 请稍候... Failed to start service, error 3. TNS-12560: TNS: 协议适配器错误 TNS-00530: 协议适配器错误 2〉发现监听启动不起来，估计是注册表有点问题，登录注册表 C:\Documents and Settings\Admin>regedit 进入注册表到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleOraDb10g_home1TNSListener 发现ImagePath关键值没有了，增加可扩充字符串值，取名为ImagePathImagePath，编辑字符串的数值数据为：G:\oracle\product\10.2.0\db_2\BIN\TNSLSNR（对应oracle的TNSLSNR的位置），退出注册表。 3〉启动监听 LSNRCTL> start 启动tnslsnr: 请稍候... TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production 系统参数文件为d:\oracle\product\10.2.0\db_1\network\admin\listener.ora 写入d:\oracle\product\10.2.0\db_1\network\log\listener.log的日志信息 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zxt)(PORT=1521))) 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ ction 启动日期 20-4月 -2007 09:41:29 正常运行时间 0 天 0 小时 0 分 3 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 d:\oracle\product\10.2.0\db_1\network\admin\listener.o ra 监听程序日志文件 d:\oracle\product\10.2.0\db_1\network\log\listener.log 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zxt)(PORT=1521))) 服务摘要.. 服务 "PLSExtProc" 包含 1 个例程。 例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 命令执行成功 4>监听启动成功后尝试登录 C:\Documents and Settings\Admin>sqlplus <a href="mailto:zxt@orcl">zxt@orcl</a> SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 20 09:42:33 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. 输入口令: 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options 登录成功！ 总结：估计是windows优化大师或者别的工具在修复注册表时候删掉了ImagePath字段，补上后就可以了！ 补充：登录sqlplus时报 ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务错误！ 处理办法： 1〉oracle_home\NETWORK\ADMIN\tnsnames.ora中修改(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) 为(ADDRESS = (PROTOCOL = TCP)(HOST = 本地计算机名)(PORT = 1521))，保存即可。 2〉有的人介绍oracle_home\NETWORK\ADMIN\sqlnet.ora中修改SQLNET.AUTHENTICATION_SERVICES = (NTS)为 SQLNET.AUTHENTICATION_SERVICES = (NONE) 这2种方法，第一个最佳！