\u5982\u4f55\u4f7f\u7528ROWNUM\u662f\u4e2a\u8001\u751f\u5e38\u8c08\u7684\u95ee\u9898\u4e86\uff0c\u672c\u6765\u6ca1\u6709\u6253\u7b97\u4e13\u95e8\u5f3a\u8c03\u8fd9\u4e2a\u95ee\u9898\uff0c\u4f46\u662f\u6700\u8fd1\u5728\u770bOracle\u7684\u5b98\u65b9PL\/SQL\u6587\u6863\u65f6\u53d1\u73b0\u4e86\u4e00\u4e2a\u4e25\u91cd\u7684\u9519\u8bef\uff0c\u501f\u8fd9\u4e2a\u673a\u4f1a\u8fd8\u662f\u7b80\u5355\u8bf4\u4e00\u4e0b\u3002
\n\u9996\u5148\u6765\u770bOracle\u6587\u6863\u7684\u63cf\u8ff0\uff0c\u572810.2\u7684PL\/SQL\u6587\u6863\u4e2d\uff0cOracle\u5173\u4e8ePL\/SQL\u4e2d\u76f4\u63a5\u4f7f\u7528SELECT\u7684\u67e5\u8be2\u63cf\u8ff0\u4e3a\uff1a<\/p>\n
Selecting At Most One Row: SELECT INTO Statement\r\nIf you expect a query to only return one row, you can write a regular SQL SELECT statement with an additional INTO clause specifying the PL\/SQL variable to hold the result.\r\nIf the query might return more than one row, but you do not care about values after the first, you can restrict any result set to a single row by comparing the ROWNUM value. If the query might return no rows at all, use an exception handler to specify any actions to take when no data is found.<\/pre>\n\u8fd9\u4e2a\u63cf\u8ff0\u662f\u6ca1\u6709\u95ee\u9898\u7684\uff0c\u4f46\u662f\u5230\u4e8611.2\u4e2d\uff0c\u6587\u6863\u7684\u63cf\u8ff0\u53d8\u6210\u4e86\uff1a<\/p>\n
Single-Row Result Sets\r\nIf you expect the query to return only one row, then use the SELECT INTO statement to store values from that row in either one or more scalar variables (see \"Assigning Values to Variables with the SELECT INTO Statement\") or one record variable (see \"SELECT INTO Statement for Assigning Row to Record Variable\").\r\nIf the query might return multiple rows, but you care about only the nth row, then restrict the result set to that row with the clause WHERE ROWNUM=n. For more information about the ROWNUM pseudocolumn, see Oracle Database SQL Language Reference.<\/pre>\n\u7b2c\u4e00\u4e2a\u53cd\u5e94\u662f\u4e0d\u662f\u6211\u770b\u9519\u4e86\uff0c\u5c45\u7136\u53ef\u4ee5\u901a\u8fc7WHERE ROWNUM = N\u6765\u9650\u5236\u53ea\u8fd4\u56de\u7b2cN\u6761\u8bb0\u5f55 \uff0c\u518d\u4ed4\u7ec6\u770b\u4e86\u4e00\u904d\uff0c\u5e76\u548c10g\u7684\u6587\u6863\u5bf9\u6bd4\uff0c\u53d1\u73b011.2\u548c10.2\u4e2d\u7684\u4e0d\u540c\u3002\u4e8e\u662f\u7b2c\u4e8c\u4e2a\u53cd\u5e94\u662fOracle\u572811.2\u4e2d\u63d0\u4f9b\u4e86\u65b0\u7279\u6027\uff0c\u4f7f\u5f97PL\/SQL\u8bed\u53e5\u4e2d\u76f4\u63a5SELECT\u53ef\u4ee5\u901a\u8fc7WHERE ROWNUM\u6765\u76f4\u63a5\u63a7\u5236\u6e38\u6807\uff0c\u4e8e\u662f\u7279\u610f\u572811.2\u4e0a\u8fdb\u884c\u4e86\u6d4b\u8bd5\uff0c\u53d1\u73b0\u7ed3\u679c\u548c10.2\u4e0a\u6ca1\u6709\u533a\u522b\uff0cROWNUM = N\u662f\u884c\u4e0d\u901a\u7684\uff0c\u9664\u975eN\u7b49\u4e8e1\u3002<\/p>\n
SQL> select * from v$version;\r\nBANNER\r\n------------------------------------------------------------------------------\r\nOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production\r\nPL\/SQL Release 11.2.0.1.0 - Production\r\nCORE 11.2.0.1.0 Production\r\nTNS for 32-bit Windows: Version 11.2.0.1.0 - Production\r\nNLSRTL Version 11.2.0.1.0 - Production\r\nSQL> select * from tab;\r\nTNAME TABTYPE CLUSTERID\r\n------------------------------ ------- ----------\r\nCOMPANIES TABLE\r\nMLOG$_T_F TABLE\r\nMLOG$_T_P TABLE\r\nMV_T_ORACLE TABLE\r\nSERVICES TABLE\r\nSERVICE_RATES TABLE\r\nSERVICE_USAGE TABLE\r\nSERVICE_USERS TABLE\r\nT TABLE\r\nT_DEFER TABLE\r\nT_F TABLE\r\nT_LOAD_LOB TABLE\r\nT_P TABLE\r\nT_PART TABLE\r\n\u5df2\u9009\u62e914\u884c\u3002\r\nSQL> set serverout on\r\nSQL> declare\r\n 2 v_name varchar2(30);\r\n 3 begin\r\n 4 select tname into v_name from tab where rownum = 5;\r\n 5 dbms_output.put_line(v_name);\r\n 6 exception \r\n 7 when no_data_found then\r\n 8 dbms_output.put_line('rownum equal the num bigger than 1 is incorrect!');\r\n 9 end;\r\n 10 \/\r\nrownum equal the num bigger than 1 is incorrect!\r\nPL\/SQL \u8fc7\u7a0b\u5df2\u6210\u529f\u5b8c\u6210\u3002<\/pre>\n\u663e\u7136Oracle\u6587\u6863\u8fd9\u91cc\u51fa\u73b0\u4e86\u4e25\u91cd\u7684\u9519\u8bef\uff0c\u5982\u679c\u8981\u4f7f\u7528ROWNUM\u6765\u63a7\u5236\u8fd4\u56de\u7b2c\u51e0\u884c\u7ed3\u679c\uff0c\u90a3\u4e48\u81f3\u5c11\u9700\u89812\u5c42\u5d4c\u5957\u67e5\u8be2\u624d\u53ef\u4ee5\u3002
\n\u6700\u540e\u7b80\u5355\u603b\u7ed3\u4e00\u4e0bROWNUM\uff0c\u5f88\u591a\u4eba\u90fd\u77e5\u9053ROWNUM\u53ea\u9002\u7528\u4e8e\u5c0f\u4e8e\u6216\u5c0f\u4e8e\u7b49\u4e8e\uff0c\u5982\u679c\u8fdb\u884c\u7b49\u4e8e\u5224\u65ad\uff0c\u90a3\u4e48\u53ea\u80fd\u7b49\u4e8e1\uff0c\u4e0d\u80fd\u8fdb\u884c\u5927\u4e8e\u7684\u6bd4\u8f83\u3002\u4f46\u662f\u5374\u5e76\u4e0d\u4e86\u89e3\u9020\u6210\u8fd9\u79cd\u9650\u5236\u6761\u4ef6\u7684\u673a\u5236\u662f\u4ec0\u4e48\u3002
\n\u5176\u5b9eROWNUM\u7684\u8fd4\u56de\u5f88\u7b80\u5355\uff0cROWNUM\u603b\u662f\u4ece1\u5f00\u59cb\uff0c\u4e0d\u7ba1\u5f53\u524d\u7684\u8bb0\u5f55\u662f\u5426\u6ee1\u8db3\u67e5\u8be2\u7ed3\u679c\uff0cROWNUM\u8fd4\u56de\u7684\u503c\u90fd\u662f1\uff0c\u5982\u679c\u8fd9\u6761\u8bb0\u5f55\u7684\u503c\u6700\u7ec8\u6ee1\u8db3\u6240\u6709\u7684\u6761\u4ef6\uff0c\u90a3\u4e48ROWNUM\u4f1a\u9012\u52a0\uff0c\u4e0b\u4e00\u6761\u8bb0\u5f55\u7684ROWNUM\u4f1a\u8fd4\u56de2\uff0c\u5426\u5219\u4e0b\u4e00\u6761\u8bb0\u5f55\u7684ROWNUM\u4ecd\u7136\u8fd4\u56de1\u3002
\n\u7406\u89e3\u4e86\u8fd9\u4e00\u70b9\uff0c\u5c31\u6e05\u695a\u4e3a\u4ec0\u4e48\u4e00\u822c\u7684ROWNUM\u5927\u4e8e\u67d0\u4e2a\u503c\u6216\u7b49\u4e8e\u67d0\u4e2a\u4e0d\u4e3a1\u7684\u503c\u662f\u65e0\u6cd5\u8fd4\u56de\u7ed3\u679c\u7684\uff0c\u56e0\u6b64\u5bf9\u4e8e\u6bcf\u6761\u8bb0\u5f55\u7684ROWNUM\u90fd\u662f1\uff0c\u800cROWNUM\u4e3a1\u4e0d\u6ee1\u8db3\u67e5\u8be2\u7684\u7ed3\u679c\uff0c\u6240\u4ee5\u4e0b\u4e00\u6761\u8bb0\u5f55\u7684ROWNUM\u4e0d\u4f1a\u9012\u589e\uff0c\u4ecd\u7136\u662f1\uff0c\u56e0\u6b64\u6240\u6709\u7684\u8bb0\u5f55\u90fd\u4e0d\u6ee1\u8db3\u6761\u4ef6\u3002
\n\u4e86\u89e3\u4e86\u539f\u7406\uff0c\u5c31\u53ef\u4ee5\u5f88\u5bb9\u6613\u7684\u5199\u51faROWNUM\u5927\u4e8e\u67d0\u503c\u7684\u4f8b\u5b50\uff1a<\/p>\n
SQL> select * from tab where rownum = 1 or rownum > 1;\r\nTNAME TABTYPE CLUSTERID\r\n------------------------------ ------- ----------\r\nCOMPANIES TABLE\r\nMLOG$_T_F TABLE\r\nMLOG$_T_P TABLE\r\nMV_T_ORACLE TABLE\r\nSERVICES TABLE\r\nSERVICE_RATES TABLE\r\nSERVICE_USAGE TABLE\r\nSERVICE_USERS TABLE\r\nT TABLE\r\nT_DEFER TABLE\r\nT_F TABLE\r\nT_LOAD_LOB TABLE\r\nT_P TABLE\r\nT_PART TABLE\r\n\u5df2\u9009\u62e914\u884c\u3002<\/pre>\n","protected":false},"excerpt":{"rendered":"\u5982\u4f55\u4f7f\u7528ROWNUM\u662f\u4e2a\u8001\u751f\u5e38\u8c08\u7684\u95ee\u9898\u4e86\uff0c\u672c\u6765\u6ca1\u6709\u6253\u7b97\u4e13\u95e8\u5f3a\u8c03\u8fd9\u4e2a\u95ee\u9898\uff0c\u4f46\u662f\u6700\u8fd1\u5728\u770bOracle\u7684\u5b98\u65b9PL\/SQL\u6587\u6863\u65f6\u53d1\u73b0\u4e86\u4e00\u4e2a\u4e25\u91cd\u7684\u9519\u8bef\uff0c\u501f\u8fd9\u4e2a\u673a\u4f1a\u8fd8\u662f\u7b80\u5355\u8bf4\u4e00\u4e0b\u3002 \u9996\u5148\u6765\u770bOracle\u6587\u6863\u7684\u63cf\u8ff0\uff0c\u572810.2\u7684PL\/SQL\u6587\u6863\u4e2d\uff0cOracle\u5173\u4e8ePL\/SQL\u4e2d\u76f4\u63a5\u4f7f\u7528SELECT\u7684\u67e5\u8be2\u63cf\u8ff0\u4e3a\uff1a Selecting At Most One Row: SELECT INTO Statement If you expect a query to only return one row, you can write a regular SQL SELECT statement with an additional INTO clause specifying the PL\/SQL variable to hold the … Continue reading