{"id":1387,"date":"2018-06-02T09:55:11","date_gmt":"2018-06-02T01:55:11","guid":{"rendered":"http:\/\/www.821121.com\/?p=1387"},"modified":"2021-02-02T09:57:37","modified_gmt":"2021-02-02T01:57:37","slug":"oracle-%e5%8d%81%e8%bf%9b%e5%88%b6%e4%b8%8e%e4%ba%8c%e8%bf%9b%e5%88%b6%e4%ba%92%e8%bd%ac%e5%87%bd%e6%95%b0","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1387","title":{"rendered":"ORACLE \u5341\u8fdb\u5236\u4e0e\u4e8c\u8fdb\u5236\u4e92\u8f6c\u51fd\u6570"},"content":{"rendered":"<p>\u5341\u8fdb\u5236\u8f6c\u6362\u4e8c\u8fdb\u5236<\/p>\n<pre><code class=\"language-sql\">\nSQL&gt; select NUMBER_TO_BIT(208) from dual;\n\nNUMBER_TO_BIT(208)\n-----------------------------\n11010000CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) \nRETURN VARCHAR IS V_RTN VARCHAR(8);--\u6ce8\u610f\u8fd4\u56de\u5217\u957f\u5ea6\n  V_N1  NUMBER;\n  V_N2  NUMBER;\nBEGIN\nV_N1 := V_NUM;\n    LOOP\n      V_N2  := MOD(V_N1, 2);\n      V_N1  := ABS(TRUNC(V_N1 \/ 2));\n      V_RTN := TO_CHAR(V_N2) || V_RTN;\n      EXIT WHEN V_N1 = 0;\n    END LOOP;\n--\u8fd4\u56de\u4e8c\u8fdb\u5236\u957f\u5ea6\n SELECT lpad(V_RTN,8,0) \n    INTO   V_RTN\n    FROM dual;\nreturn V_RTN;\nend;\n\nSQL&gt; select NUMBER_TO_BIT(208) from dual;\n\nNUMBER_TO_BIT(208)\n-----------------------------\n11010000\nCREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) \nRETURN VARCHAR IS V_RTN VARCHAR(8);--\u6ce8\u610f\u8fd4\u56de\u5217\u957f\u5ea6\n  V_N1  NUMBER;\n  V_N2  NUMBER;\nBEGIN\nV_N1 := V_NUM;\n    LOOP\n      V_N2  := MOD(V_N1, 2);\n      V_N1  := ABS(TRUNC(V_N1 \/ 2));\n      V_RTN := TO_CHAR(V_N2) || V_RTN;\n      EXIT WHEN V_N1 = 0;\n    END LOOP;\n--\u8fd4\u56de\u4e8c\u8fdb\u5236\u957f\u5ea6\n SELECT lpad(V_RTN,8,0) \n    INTO   V_RTN\n    FROM dual;\nreturn V_RTN;\nend;<\/code><\/pre>\n<p>\u4e8c\u8fdb\u5236\u8f6c\u6362\u5341\u8fdb\u5236<\/p>\n<pre><code class=\"language-sql\">\nSQL&gt; select NUMBER_TO_BIT(208) from dual;\n\nNUMBER_TO_BIT(208)\n-----------------------------\n11010000CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) \nRETURN VARCHAR IS V_RTN VARCHAR(8);--\u6ce8\u610f\u8fd4\u56de\u5217\u957f\u5ea6\n  V_N1  NUMBER;\n  V_N2  NUMBER;\nBEGIN\nV_N1 := V_NUM;\n    LOOP\n      V_N2  := MOD(V_N1, 2);\n      V_N1  := ABS(TRUNC(V_N1 \/ 2));\n      V_RTN := TO_CHAR(V_N2) || V_RTN;\n      EXIT WHEN V_N1 = 0;\n    END LOOP;\n--\u8fd4\u56de\u4e8c\u8fdb\u5236\u957f\u5ea6\n SELECT lpad(V_RTN,8,0) \n    INTO   V_RTN\n    FROM dual;\nreturn V_RTN;\nend;\n\nSQL&gt; select NUMBER_TO_BIT(208) from dual;\n\nNUMBER_TO_BIT(208)\n-----------------------------\n11010000\nCREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS\n  V_SQL    VARCHAR2(30000) := &#039;SELECT BIN_TO_NUM(&#039;;\n  V_RETURN NUMBER;\nBEGIN\n  IF LENGTH(P_BIN) &gt;= 256 THEN\n    RAISE_APPLICATION_ERROR(-20001, &#039;INPUT BIN TOO LONG!&#039;);\n  END IF;\n  IF LTRIM(P_BIN, &#039;01&#039;) IS NOT NULL THEN\n    RAISE_APPLICATION_ERROR(-20002, &#039;INPUT STR IS NOT VALID BIN VALUE!&#039;);\n  END IF;\n  FOR I IN 1 .. LENGTH(P_BIN) LOOP\n    V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || &#039;,&#039;;\n  END LOOP;\n  V_SQL := RTRIM(V_SQL, &#039;,&#039;) || &#039;) FROM DUAL&#039;;\n  EXECUTE IMMEDIATE V_SQL\n    INTO V_RETURN;\n  RETURN V_RETURN;\nEND;\n\nSQL&gt; SELECT BIT_TO_NUMBER(&#039;11010000&#039;) FROM DUAL;\n\nBIT_TO_NUMBER(&#039;11010000&#039;)\n-------------------------\n                      208CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) \nRETURN VARCHAR IS V_RTN VARCHAR(8);--\u6ce8\u610f\u8fd4\u56de\u5217\u957f\u5ea6\n  V_N1  NUMBER;\n  V_N2  NUMBER;\nBEGIN\nV_N1 := V_NUM;\n    LOOP\n      V_N2  := MOD(V_N1, 2);\n      V_N1  := ABS(TRUNC(V_N1 \/ 2));\n      V_RTN := TO_CHAR(V_N2) || V_RTN;\n      EXIT WHEN V_N1 = 0;\n    END LOOP;\n--\u8fd4\u56de\u4e8c\u8fdb\u5236\u957f\u5ea6\n SELECT lpad(V_RTN,8,0) \n    INTO   V_RTN\n    FROM dual;\nreturn V_RTN;\nend;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5341\u8fdb\u5236\u8f6c\u6362\u4e8c\u8fdb\u5236 SQL&gt; select N<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-1387","post","type-post","status-publish","format-standard","hentry","category-oracle-10g"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1387","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1387"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1387\/revisions"}],"predecessor-version":[{"id":1388,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1387\/revisions\/1388"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1387"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1387"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1387"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}