本文共 2736 字，大约阅读时间需要 9 分钟。

Oracle?????SQL??????????

??Oracle SQL??????

1. ??????

SELECT p_id, idFROM table1 cSTART WITH c.p_id = '0000000'CONNECT BY prior c.id = c.p_idAND c.use_yn = 'Y'ORDER BY id;

2. ??????

SELECT   RPAD(' ', 2*(LEVEL-1), '-') || DEPNAME AS dep_path,  CONNECT_BY_ROOT DEPNAME AS root_dep,  CONNECT_BY_ISLEAF AS is_leaf,  LEVEL,  SYS_CONNECT_BY_PATH(DEPNAME, '/', 1) AS pathFROM depSTART WITH upperDepid IS NULLCONNECT BY prior depId = upperDepid;

3. ?????

ALTER TABLE taxasset ADD (NEXTDATE VARCHAR2(30));ALTER TABLE tax_dep_manager MODIFY FDDBRXM VARCHAR2(120);ALTER TABLE test1 DROP COLUMN name;

??PLSQL????

1. ??????

DECLARE  con NUMBER := 1;BEGIN  LOOP    DBMS_OUTPUT.PUT_LINE(con);    con := con + 1;    EXIT WHEN con > 100;  END LOOP;  DBMS_OUTPUT.PUT_LINE(' ');END;

2. ??????

CREATE OR REPLACE PROCEDURE InsertBranch(  tableName IN VARCHAR2) AS  counts NUMBER;  num NUMBER;BEGIN  CREATE TABLE tempdata (column1 VARCHAR2, column2 VARCHAR2, column3 VARCHAR2);  INSERT INTO tempdata  SELECT column1, column2, column3  FROM tableName;  dbms_output.put_line('?? ' || counts);  num := 1;  WHILE num <= counts LOOP    dbms_output.put_line('?');    dbms_output.put_line('? ' || num || ' ????');    INSERT INTO com_department    VALUES (      brno, brname, upbrno, upbrno, 'N', null, null, null, '1', null, 'Y', '2', null, null, null, 2, 'N', null, null, null, 'A', 'N', 'N', 0, 0, 3, null, null, null, '0', '0', 0, null, null, null, 0);    num := num + 1;  END LOOP;  END;

?????????

1. ??????

• ?????LEFT OUTER JOIN
• ?????RIGHT OUTER JOIN
• ????INNER JOIN
• ????FULL JOIN
• ?????ON a.id = b.id AND a.id = 5

2. ???????

SELECT tablespace_name, count(*) AS extends,        ROUND(sum(bytes) / 1024 / 1024, 2) AS MB,       sum(blocks) AS blocksFROM dba_free_spaceGROUP BY tablespace_name;

SELECT tablespace_name, sum(bytes) / 1024 / 1024 AS MBFROM dba_data_filesGROUP BY tablespace_name;

3. ??????

SELECT tablespace_name,        To_char(Round(BYTES / 1024, 2), '99990.00') || 'M',       To_char(Round(FREE / 1024, 2), '99990.00') || 'G',       To_char(Round((BYTES - FREE) / 1024, 2), '99990.00') || 'G',       To_char(Round(10000 * USED / BYTES) / 100, '99990.00') || '%'FROM (  SELECT tablespace_name,          Floor(BYTES / (1024 * 1024)) BYTES,         Floor(FREE / (1024 * 1024)) FREE,         Floor((BYTES - FREE) / (1024 * 1024)) USED  FROM (    SELECT tablespace_name, Sum(BYTES) BYTES    FROM dba_data_files    GROUP BY tablespace_name  ) A,  (    SELECT tablespace_name, Sum(BYTES) FREE    FROM dba_free_space    GROUP BY tablespace_name  ) B  WHERE A.tablespace_name = B.tablespace_name) DORDER BY Floor(10000 * USED / BYTES) DESC;

????????

???????Oracle???????????SQL???????????PLSQL?????????????????????????????????????????Oracle?????????????????