{"id":71,"date":"2012-11-23T22:12:28","date_gmt":"2012-11-23T14:12:28","guid":{"rendered":"http:\/\/coderbee.net\/?p=71"},"modified":"2013-04-26T22:16:21","modified_gmt":"2013-04-26T14:16:21","slug":"oralce-%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b-%e5%bf%ab%e9%80%9f%e5%85%a5%e9%97%a8","status":"publish","type":"post","link":"https:\/\/coderbee.net\/index.php\/db\/20121123\/71","title":{"rendered":"Oralce \u5b58\u50a8\u8fc7\u7a0b \u5feb\u901f\u5165\u95e8"},"content":{"rendered":"

\u7b80\u5355\u4ecb\u7ecdOracle\u5b58\u50a8\u8fc7\u7a0b\u91cc\u7684\u53d8\u91cf\u58f0\u660e\u3001\u63a7\u5236\u8bed\u53e5\u3001\u5faa\u73af\u8bed\u53e5\u3001\u6e38\u6807\u3001\u5b58\u50a8\u8fc7\u7a0b\u3001\u51fd\u6570\u7684\u57fa\u672c\u8bed\u6cd5\u53ca\u7528\u6cd5\u3002\u8ba9\u6709SQL\u7684\u4eba\u5feb\u901f\u4e0a\u624bOracle\u5b58\u50a8\u8fc7\u7a0b\u3002<\/p>\n

<\/p>\n

\u8bed\u53e5\u5757\u5b9a\u4e49<\/h2>\n
\ndecalre\n-- \u53d8\u91cf\u58f0\u660e\nvar1 number(2);                -- \u4ec5\u58f0\u660e\nvar2 char(2) := '11';          -- \u5728\u58f0\u660e\u7684\u540c\u65f6\u521d\u59cb\u5316\n\nbegin\n        -- \u8bed\u53e5\nend; -- \u8bed\u53e5\u5757\u7ed3\u675f\n<\/code><\/pre>\n
if \u8bed\u53e5<\/h2>\n
\nif a = 1 or b = 2 then\n\nelsif c = 3 then\n\nelse\n\nend if;\n<\/code><\/pre>\n
case \u8bed\u53e5<\/h2>\n
case\u8bed\u53e5\u5982\u679c\u4f5c\u4e3a\u5206\u652f\u63a7\u5236\u8bed\u53e5\uff0c\u6700\u540e\u7ed3\u675f\u8bed\u53e5\u662fend case<\/code>\uff0c\u5982\u679c\u662f\u4f5c\u4e3aselect<\/code>\u8bed\u53e5\u91cc\u7684\u63a7\u5236\u8bed\u53e5\u5219\u53ea\u9700\u8981end<\/code>\u3002<\/p>\n
\ndeclare\nnum number(10) := 1;\nbegin\n    case\n        when num = 0 then dbms_output.put_line( 'zero');\n        when num = 1 then dbms_output.put_line( 'one');\n        else  dbms_output.put_line( 'default');\n    end case;\n   \n    case num\n        when 0 then dbms_output.put_line( 'zero');\n        when 1 then dbms_output.put_line( 'one');\n        else  dbms_output.put_line( 'default');\n    end case;\nend;\n<\/code><\/pre>\n
for \u5faa\u73af<\/h2>\n
    \n
  • \u5faa\u73af\u4e00\u4e2a\u8303\u56f4
    \n\u683c\u5f0f\uff1afor i in [start .. end] loop ... end loop;<\/code><\/li>\n<\/ul>\n
    \n  for i in 0..9 loop\n      dbms_output.put_line('i:' || i);\n  end loop;\n<\/code><\/pre>\n
      \n
    • \u904d\u5386\u9690\u5f0f\u6e38\u6807
      \n\u9690\u5f0f\u6e38\u6807\u7684\u597d\u5904\u662f\u4e0d\u9700\u8981\u624b\u52a8\u5173\u95ed\uff0c\u65b9\u4fbf\u3002<\/li>\n<\/ul>\n
      \nfor currow in (\n   select t.col1, t.col2\n   from tableName t\n   where ...\n) loop\n    if currow.col1 = 0 then\n       return;    -- \u4e2d\u6b62sp\uff0c\u8fd4\u56de\n   end if;\nend loop;\n<\/code><\/pre>\n
      while \u5faa\u73af<\/h2>\n
      \nisok := 9;\nwhile isok >= 0 loop\n       isok := isok - 1;\n    \n       if isok = 8 then\n          continue;                -- \u4e0e\u7f16\u7a0b\u8bed\u8a00\u7684 continue \u8bed\u4e49\u4e00\u6837\uff0c\u8df3\u8fc7\u5f53\u524d\u5faa\u73af\u7684\u5269\u4f59\u8bed\u53e5\uff0c\u56de\u5230\u5faa\u73af\u5f00\u59cb\n       end if;\n    \n       if isok = 4 then\n          exit;                    -- \u4e0e\u7f16\u7a0b\u8bed\u8a00\u7684 break \u8bed\u4e49\u4e00\u6837\uff0c\u8df3\u51fa\u5faa\u73af\n       end if;\n\n       dbms_output.put_line('isok:' || isok);\nend loop;\n\ndbms_output.put_line('outside while loop .');\n<\/code><\/pre>\n
      cursor<\/h2>\n
      \u9690\u5f0f\u6e38\u6807<\/h3>\n
      \nfor currow in (\n   select t.col1, t.col2\n   from tableName t\n   where ...\n) loop\n    if currow.col1 = 0 then\n       return;    -- \u4e2d\u6b62sp\uff0c\u8fd4\u56de\n   end if;\nend loop;\n<\/code><\/pre>\n
      \u663e\u5f0f\u6e38\u6807<\/h3>\n
      \ndeclare\nisok integer;\nv_event_id number(10);\nv_isagain number(2);\nv_rate number(2);\n\nv_sender char(11) := '13800138000';\n\ncursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender; -- \u58f0\u660e\u6e38\u6807\n\n\nbegin\n    open cursorVar;    -- \u6253\u5f00\u6e38\u6807\n    loop\n         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- \u53d6\u503c\n         exit when cursorVar%notfound;                             --\u5f53\u6ca1\u6709\u8bb0\u5f55\u65f6\u9000\u51fa\u5faa\u73af\n         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);\n    end loop;\n   \n    close cursorVar;   -- \u5173\u95ed\u6e38\u6807\n   \n    --\u6e38\u6807\u7684\u5c5e\u6027\u6709\uff1a%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT; \n    --%FOUND:\u5df2\u68c0\u7d22\u5230\u8bb0\u5f55\u65f6\uff0c\u8fd4\u56detrue \n    --%NOTFOUNRD\uff1a\u68c0\u7d22\u4e0d\u5230\u8bb0\u5f55\u65f6\uff0c\u8fd4\u56detrue \n    --%ISOPEN:\u6e38\u6807\u5df2\u6253\u5f00\u65f6\u8fd4\u56detrue \n    --%ROWCOUNT:\u4ee3\u8868\u68c0\u7d22\u7684\u8bb0\u5f55\u6570\uff0c\u4ece1\u5f00\u59cb \nend;\n<\/code><\/pre>\n
      \u5e26\u53c2\u6570\u6e38\u6807<\/h3>\n
      \ndeclare\nisok integer;\nv_event_id number(10);\nv_isagain number(2);\nv_rate number(2);\n\nv_sender char(11) := '13800138000';\n\ncursor cursorVar(p_sender varchar2) is select event_id, isagain, rate from call_event where sender = p_sender; -- \u58f0\u660e\u6e38\u6807\n\nbegin\n    open cursorVar(v_sender);    -- \u6253\u5f00\u6e38\u6807\uff0c\u5728\u62ec\u53f7\u91cc\u4f20\u53c2\u3002\n    loop\n         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- \u53d6\u503c\n         exit when cursorVar%notfound;                             --\u5f53\u6ca1\u6709\u8bb0\u5f55\u65f6\u9000\u51fa\u5faa\u73af\n         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);\n    end loop;\n   \n    close cursorVar;   -- \u5173\u95ed\u6e38\u6807\nend;\n<\/code><\/pre>\n
      \u5b58\u50a8\u8fc7\u7a0b<\/h2>\n
      \ncreate or replace procedure sp_name (\n        -- \u5165\u53c2\u3001\u51fa\u53c2\u5217\u8868\uff0c \u9017\u53f7\u5206\u9694\u3002\n        uid in varchar2,                          -- \u4e0d\u80fd\u5e26\u957f\u5ea6\u4fe1\u606f\n        startDate in date ,                        -- \u7b2c\u4e8c\u4e2a\u8f93\u5165\u53c2\u6570\n        defaultVar in varchar2 default \"\",        -- \u9ed8\u8ba4\u53c2\u6570\uff0c\u5982\u679c\u4e0d\u4f20\uff0c\u8981\u6ce8\u610f\u53c2\u6570\u7684\u987a\u5e8f\n        isok out number ,                          -- \u8f93\u51fa\u53c2\u6570\n        result out varchar2                       -- \u7b2c\u4e8c\u4e2a\u8f93\u51fa\u53c2\u6570\n)\nas\n-- \u53d8\u91cf\u58f0\u660e\uff0c\u6bcf\u4e2a\u58f0\u660e\u7528\u5206\u53f7\u7ed3\u675f\u3002\u53ef\u4ee5\u5728\u58f0\u660e\u7684\u540c\u65f6\u521d\u59cb\u5316\nvar1 varchar2( 11);\nvar2 number( 2) := 123 ;\n\nbegin\n        -- \u5b57\u7b26\u4e32\u62fc\u63a5\u7528 ||\n        dbms_output.put_line( 'isok:' || 'abc' );\n       \n        -- \u8c03\u7528\u5176\u4ed6\u5b58\u50a8\u8fc7\u7a0b\n        sub_sp_name(param1, prarm2, outParam1, outParam2);\n\nend;        -- \u5b58\u50a8\u8fc7\u7a0b\u7ed3\u675f\n<\/code><\/pre>\n
      \u51fd\u6570<\/h2>\n
      \ncreate or replace function func  (\n        -- \u5165\u53c2\u3001\u51fa\u53c2\u5217\u8868\uff0c \u9017\u53f7\u5206\u9694\u3002\n        uid in varchar2,                          -- \u4e0d\u80fd\u5e26\u957f\u5ea6\u4fe1\u606f\n        startDate in date ,                        -- \u7b2c\u4e8c\u4e2a\u8f93\u5165\u53c2\u6570\n        defaultVar in varchar2 default \"\",        -- \u9ed8\u8ba4\u53c2\u6570\uff0c\u5982\u679c\u4e0d\u4f20\uff0c\u8981\u6ce8\u610f\u53c2\u6570\u7684\u987a\u5e8f\n        isok out number ,                          -- \u8f93\u51fa\u53c2\u6570\n        result out varchar2                       -- \u7b2c\u4e8c\u4e2a\u8f93\u51fa\u53c2\u6570\n)\nreturn number       -- \u5b9a\u4e49\u8fd4\u56de\u7c7b\u578b\nas\n-- \u53d8\u91cf\u58f0\u660e\uff0c\u6bcf\u4e2a\u58f0\u660e\u7528\u5206\u53f7\u7ed3\u675f\u3002\u53ef\u4ee5\u5728\u58f0\u660e\u7684\u540c\u65f6\u521d\u59cb\u5316\nvar1 varchar2( 11);\nvar2 number( 2) := 123 ;\n\nbegin\n        -- \u5b57\u7b26\u4e32\u62fc\u63a5\u7528 ||\n        dbms_output.put_line( 'isok:' || 'abc' );\n       \n\n        return ret_val;\nend;\n<\/code><\/pre>\n
      \u5b58\u50a8\u8fc7\u7a0b\u4e0e\u51fd\u6570\u7684\u5f02\u540c<\/h4>\n
        \n
      1. \u4e24\u8005\u5b9a\u4e49\u7c7b\u4f3c\uff0c\u90fd\u53ef\u4ee5\u5e26\u8f93\u5165\u8f93\u51fa\u53c2\u6570\u3002<\/li>\n
      2. \u51fd\u6570\u6709\u8fd4\u56de\u503c\uff0c\u5b58\u50a8\u8fc7\u7a0b\u6ca1\u6709\u3002<\/li>\n
      3. \u51fd\u6570\u7684\u8c03\u7528\u8981\u5728select\u8bed\u53e5\u91cc\uff1b\u800c\u5b58\u50a8\u8fc7\u7a0b\u4e0d\u7528\uff0c\u53ef\u4ee5\u72ec\u7acb\u8c03\u7528\u3002<\/li>\n<\/ol>\n
        \u4e00\u4e9b\u7ba1\u7406\u8bed\u53e5<\/h2>\n
        \u67e5\u770b\u6700\u8fd1\u6267\u884c\u7684sql<\/h5>\n
        select * from v$sql  order by FIRST_LOAD_TIME desc<\/code><\/p>\n
        \u67e5\u770b\u6b63\u5728\u6267\u884c\u7684sql<\/h5>\n
        \n  SELECT B.SID,\n       B.STATUS,\n       B.OSUSER,\n       B.TERMINAL,\n       B.PROGRAM,\n       B.MACHINE,\n       B.SQL_EXEC_START AS \"SQL STARTTIME\",\n       A.SQL_ID,\n       A.SQLTYPE,\n       A.SQL_TEXT,\n       A.LAST_ACTIVE_TIME AS \"SQL ENDSTIME\"    -- \u8bed\u53e5\u6267\u884c\u5b8c\u6210\u524d\uff0c\u4e00\u76f4\u5728\u53d8\n\n  FROM V$SQL A, V$SESSION B\n WHERE A.SQL_ID = B.SQL_ID\n   AND B.STATUS = 'ACTIVE'\n   AND B.SID <> (SELECT SID FROM V$MYSTAT WHERE ROWNUM <= 1)\n   AND B.SID > 1 ;\n<\/code><\/pre>\n
        \u67e5\u770bsql\u8bed\u53e5\u7684\u6267\u884c\u8fdb\u5ea6\uff1a<\/p>\n
        \n  SELECT A.SID                          AS \"\u8fdb\u7a0bSID\",\n       OPNAME                         AS \"\u6267\u884c\u64cd\u4f5c\",\n       TARGET                         AS \"\u64cd\u4f5c\u5bf9\u8c61\",\n       START_TIME                     AS \"\u5f00\u59cb\u65f6\u95f4\",\n      (SYSDATE - START_TIME) * 24 * 60 * 60 AS \"\u5df2\u8017\u65f6(\u79d2)\",\n       TIME_REMAINING                 AS \"\u5269\u4f59\u65f6\u95f4\",\n       A.USERNAME                     AS \"\u7528\u6237\u540d\u79f0\",\n       MACHINE                        AS \"\u4e3b\u673a\",\n       TERMINAL                       AS \"\u7ec8\u7aef\",\n       PROGRAM                        AS \"\u8fdb\u7a0b\",\n       STATE                          AS \"\u5f53\u524d\u72b6\u6001\",\n       ROUND( SOFAR\/TOTALWORK*100,2 ) AS \"\u5b8c\u6210\u767e\u5206\u6bd4\"\nFROM GV$SESSION_LONGOPS A,\n     GV$SESSION B\nWHERE A.TIME_REMAINING>0 \nAND   A.SID=B.SID\nAND   B.SERIAL#=A.SERIAL# \nAND   A.INST_ID=B.INST_ID\nAND TARGET NOT LIKE 'SYS%';\n<\/code><\/pre>\n
        \u67e5\u770b\u8868\u7a7a\u95f4\u7684\u60c5\u51b5\uff1a<\/p>\n
        \n  SELECT TABLESPACE_NAME AS \"\u8868\u7a7a\u95f4\u540d\u79f0\",\n       BLOCK_SIZE AS \"\u5757\u5927\u5c0f(db_block_size)\",\n       STATUS AS \"\u5f53\u524d\u72b6\u6001\",\n       DECODE(A.CONTENTS,\n              'PERMANENT',\n              '\u6c38\u4e45\u8868\u7a7a\u95f4',\n              'TEMPORARY',\n              '\u4e34\u65f6\u8868\u7a7a\u95f4',\n              'UNDO',\n              '\u56de\u9000\u8868\u7a7a\u95f4') AS \"\u8868\u7a7a\u95f4\u7c7b\u578b\",\n       SEGMENT_SPACE_MANAGEMENT AS \"\u7a7a\u95f4\u7ba1\u7406\u65b9\u5f0f\",\n       ROUND(DECODE(A.CONTENTS,\n                    'PERMANENT',\n                    (SELECT SUM(USER_BYTES) AS \"SIZE_BYTE\"\n                       FROM DBA_DATA_FILES B\n                      WHERE B.TABLESPACE_NAME = A.TABLESPACE_NAME),\n                    'TEMPORARY',\n                    (SELECT SUM(USER_BYTES)\n                       FROM DBA_TEMP_FILES C\n                      WHERE C.TABLESPACE_NAME = A.TABLESPACE_NAME),\n                    'UNDO',\n                    (SELECT SUM(USER_BYTES) AS \"SIZE_BYTE\"\n                       FROM DBA_DATA_FILES D\n                      WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME)) \/ 1024 \/ 1024 \/ 1024) AS \"\u5206\u914d\u7a7a\u95f4(GB)\",\n       ROUND(DECODE(A.CONTENTS,\n                    'PERMANENT',\n                    (SELECT SUM(BYTES)\n                       FROM DBA_SEGMENTS H\n                      WHERE H.TABLESPACE_NAME = A.TABLESPACE_NAME),\n                    'TEMPORARY',\n                    (SELECT ALLOCATED_SPACE\n                       FROM DBA_TEMP_FREE_SPACE F\n                      WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME),\n                    'UNDO',\n                    (SELECT SUM(BYTES)\n                       FROM DBA_UNDO_EXTENTS J\n                      WHERE J.STATUS IN ('ACTIVE', 'UNEXPIRED')\n                        AND J.TABLESPACE_NAME = A.TABLESPACE_NAME)) \/ 1024 \/ 1024 \/ 1024) AS \"\u5df2\u7528\u7a7a\u95f4(GB)\",\n       ROUND(DECODE(A.CONTENTS,\n                    'PERMANENT',\n                    (SELECT SUM(BYTES)\n                       FROM DBA_FREE_SPACE E\n                      WHERE E.TABLESPACE_NAME = A.TABLESPACE_NAME),\n                    'TEMPORARY',\n                    (SELECT FREE_SPACE\n                       FROM DBA_TEMP_FREE_SPACE F\n                      WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME),\n                    'UNDO',\n                    (SELECT SUM(BYTES)\n                       FROM DBA_FREE_SPACE G\n                      WHERE G.TABLESPACE_NAME = A.TABLESPACE_NAME)) \/ 1024 \/ 1024 \/ 1024) AS \"\u53ef\u7528\u7a7a\u95f4(GB)\"\n  FROM DBA_TABLESPACES A\n WHERE A.TABLESPACE_NAME LIKE 'RDC%'\n    OR A.TABLESPACE_NAME LIKE 'UNDO%'\n ORDER BY CONTENTS, TABLESPACE_NAME;\n<\/code><\/pre>\n\n
        \u6b22\u8fce\u5173\u6ce8\u6211\u7684\u5fae\u4fe1\u516c\u4f17\u53f7: coderbee\u7b14\u8bb0<\/strong>\uff0c\u53ef\u4ee5\u66f4\u53ca\u65f6\u56de\u590d\u4f60\u7684\u8ba8\u8bba\u3002<\/p>\n
        <\/p>\n","protected":false},"excerpt":{"rendered":"
        \u7b80\u5355\u4ecb\u7ecdOracle\u5b58\u50a8\u8fc7\u7a0b\u91cc\u7684\u53d8\u91cf\u58f0\u660e\u3001\u63a7\u5236\u8bed\u53e5\u3001\u5faa\u73af\u8bed\u53e5\u3001\u6e38\u6807\u3001\u5b58\u50a8\u8fc7\u7a0b\u3001\u51fd … \u7ee7\u7eed\u9605\u8bfb →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[24,25],"_links":{"self":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/71"}],"collection":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/comments?post=71"}],"version-history":[{"count":2,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/71\/revisions"}],"predecessor-version":[{"id":73,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/71\/revisions\/73"}],"wp:attachment":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/media?parent=71"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/categories?post=71"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/tags?post=71"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}