常用Sql语句：
1 SELECT * FROM HR.EMPLOYEES; 2 DESC HR.EMPLOYEES; 3 SELECT LAST_NAME, SALARY*12 FROM HR.EMPLOYEES; 4 SELECT LAST_NAME, SALARY*12 annual_sal FROM HR.EMPLOYEES; 5 SELECT LAST_NAME, SALARY*12 "annual_sal" FROM HR.EMPLOYEES; 6 SELECT DEPARTMENT_ID from HR.EMPLOYEES; 7 SELECT DISTINCT DEPARTMENT_ID from HR.EMPLOYEES; 8 SELECT DISTINCT DEPARTMENT_ID, JOB_ID from HR.EMPLOYEES; 9 10 SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 10; 11 SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID > 10; 12 SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID <> 10; 13 SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME = 'King'; 14 SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME > 'King'; 15 SELECT LAST_NAME,SALARY FROM HR.EMPLOYEES WHERE LAST_NAME > 'King'; 16 SELECT LAST_NAME,SALARY,DEPARTMENT_ID FROM HR.EMPLOYEES WHERE DEPARTMENT_ID > 50; 17 SELECT LAST_NAME,SALARY FROM HR.EMPLOYEES WHERE SALARY BETWEEN 800 AND 3000; 18 SELECT LAST_NAME,SALARY FROM HR.EMPLOYEES WHERE SALARY >= 800 AND SALARY <= 3000; 19 20 SELECT LAST_NAME,SALARY,COMMISSION_PCT FROM HR.EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL; 21 SELECT LAST_NAME,SALARY,COMMISSION_PCT FROM HR.EMPLOYEES WHERE COMMISSION_PCT IS NULL; 22 23 SELECT LAST_NAME,SALARY,COMMISSION_PCT FROM HR.EMPLOYEES WHERE LAST_NAME IN ('King','De Haan', 'Chen'); 24 SELECT LAST_NAME,SALARY,COMMISSION_PCT FROM HR.EMPLOYEES WHERE LAST_NAME NOT IN ('King','De Haan', 'Chen'); 25 SELECT LAST_NAME,SALARY,COMMISSION_PCT FROM HR.EMPLOYEES WHERE SALARY IN (9000,2800,7900); 26 27 SELECT LAST_NAME,SALARY,HIRE_DATE FROM HR.EMPLOYEES; 28 SELECT LAST_NAME,SALARY,HIRE_DATE FROM HR.EMPLOYEES WHERE HIRE_DATE = '05-2月 -06'; 29 30 SELECT LAST_NAME,SALARY,HIRE_DATE,DEPARTMENT_ID FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 10 AND SALARY > 500; 31 SELECT LAST_NAME,SALARY,HIRE_DATE,DEPARTMENT_ID FROM HR.EMPLOYEES WHERE DEPARTMENT_ID < 50 OR SALARY < 7500; 32 SELECT LAST_NAME,SALARY,HIRE_DATE,DEPARTMENT_ID FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 10 AND SALARY NOT IN (500,7000); 33 34 SELECT LAST_NAME FROM HR.EMPLOYEES WHERE LAST_NAME LIKE '%in%'; 35 SELECT LAST_NAME FROM HR.EMPLOYEES WHERE LAST_NAME LIKE '_in%'; 36 SELECT LAST_NAME FROM HR.EMPLOYEES WHERE LAST_NAME LIKE '%in_%'; 37 38 SELECT LAST_NAME,DEPARTMENT_ID FROM HR.EMPLOYEES ORDER BY DEPARTMENT_ID DESC; 39 SELECT LAST_NAME,DEPARTMENT_ID,SALARY FROM HR.EMPLOYEES ORDER BY DEPARTMENT_ID DESC, SALARY ASC; 40 SELECT LAST_NAME,DEPARTMENT_ID,SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID < 50 ORDER BY DEPARTMENT_ID DESC, SALARY ASC; 41 SELECT LAST_NAME,DEPARTMENT_ID,SALARY*12 ANNUAL_SAL FROM HR.EMPLOYEES WHERE DEPARTMENT_ID < 50 and LAST_NAME NOT LIKE '_o%' ORDER BY DEPARTMENT_ID DESC, SALARY ASC; 42 43 SELECT LOWER(LAST_NAME) FROM HR.EMPLOYEES; 44 SELECT UPPER(LAST_NAME) FROM HR.EMPLOYEES; 45 SELECT LAST_NAME FROM HR.EMPLOYEES WHERE lower(LAST_NAME) like '_a%'; 46 SELECT LAST_NAME FROM HR.EMPLOYEES WHERE LAST_NAME like '_a%' OR LAST_NAME like '_A%'; 47 SELECT SUBSTR('ABCDEFG',3,4) SUBSTR FROM DUAL; 48 SELECT CHR(67)||CHR(65) CHR FROM DUAL; 49 SELECT ASCII('a') ASCI FROM DUAL; 50 51 SELECT ROUND(15.193,1) ROUD FROM DUAL; 52 SELECT ROUND(15.193,-1) ROUD FROM DUAL; 53 54 SELECT TO_CHAR(SALARY, '$99,999.9999') SAL FROM HR.EMPLOYEES; 55 SELECT TO_CHAR(SALARY, '$00,000.0000') SAL FROM HR.EMPLOYEES; 56 57 SELECT TO_CHAR(SALARY, 'L99,999.9999') SAL FROM HR.EMPLOYEES; 58 SELECT TO_CHAR(SALARY, 'L00,000.0000') SAL FROM HR.EMPLOYEES; 59 60 SELECT HIRE_DATE FROM HR.EMPLOYEES; 61 SELECT TO_CHAR(HIRE_DATE,'YYYY-MM-DD HH:MI:SS') HIREDATE FROM HR.EMPLOYEES; 62 SELECT TO_CHAR(HIRE_DATE,'YYYY-MM-DD HH24:MI:SS') HIREDATE FROM HR.EMPLOYEES; 63 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH12:MI:SS') FROM DUAL; 64 65 SELECT LAST_NAME, HIRE_DATE FROM HR.EMPLOYEES; 66 SELECT LAST_NAME, HIRE_DATE FROM HR.EMPLOYEES WHERE HIRE_DATE > TO_DATE('2005-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS'); 67 68 SELECT SALARY FROM HR.EMPLOYEES WHERE SALARY > 8888.88; 69 SELECT SALARY FROM HR.EMPLOYEES WHERE SALARY > TO_NUMBER('$7,250.00', '$9,999.99'); 70 71 SELECT MAX(SALARY) MAXSAL FROM HR.EMPLOYEES; 72 SELECT MIN(SALARY) MINSAL FROM HR.EMPLOYEES; 73 SELECT TO_CHAR(AVG(SALARY),'99999999.99') AVGSAL FROM HR.EMPLOYEES; 74 SELECT ROUND(AVG(SALARY),2) AVGSAL FROM HR.EMPLOYEES; 75 SELECT SUM(SALARY) FROM HR.EMPLOYEES; 76 SELECT COUNT(*) FROM HR.EMPLOYEES; 77 SELECT COUNT(*) FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50; 78 SELECT COUNT(LAST_NAME) FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50; 79 SELECT COUNT(DISTINCT DEPARTMENT_ID) COUNTDISTINCTID FROM HR.EMPLOYEES; 80 SELECT DISTINCT DEPARTMENT_ID FROM HR.EMPLOYEES; 81 82 SELECT ROUND(AVG(SALARY),2) AS SALARY FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID; 83 SELECT ROUND(AVG(SALARY),2) AS SALARY,DEPARTMENT_ID FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID; 84 85 SELECT DEPARTMENT_ID,JOB_ID, MAX(SALARY) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID,JOB_ID; 86 SELECT LAST_NAME FROM HR.EMPLOYEES WHERE SALARY = (SELECT MAX(SALARY) FROM HR.EMPLOYEES); 87 SELECT DEPARTMENT_ID, MAX(SALARY) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID; 88 89 SELECT MIN(SALARY) FROM HR.EMPLOYEES GROUP BY EMPLOYEE_ID; 90 91 create or replace procedure P_DTMS_REP_WHITEBOXMEMBERS(v_startDate in date, v_endDate in date, v_scope in varchar2, v_cur out sys_refcursor) 92 is 93 v_startDate1 timestamp := to_timestamp('2015-01-01 00:00:00.000','YYYY-MM-DD HH24:MI:SS.ff9'); 94 v_endDate1 timestamp := to_timestamp('2015-01-02 23:59:59.999','YYYY-MM-DD HH24:MI:SS.ff9'); 95 v_date_condition varchar2(500) := ' between ' || 'to_timestamp(''' || to_char(v_startDate1, 'yyyy-mm-dd hh24:mi:ss.ff9') || ''', ''yyyy-mm-dd hh24:mi:ss.ff9'')' || ' and ' || 'to_timestamp(''' || to_char(v_endDate1, 'yyyy-mm-dd hh24:mi:ss.ff9') || ''', ''yyyy-mm-dd hh24:mi:ss.ff9'')'; 96 v_totalgroup number; 97 v_sql long; 98 v_fixedUsers varchar2(1000) := '(''developManager2'', ''developManager1'')'; 99 begin 100 for rc in (select labelgroup.id as groupid, labelgroup.name as groupname from dtms_issuelabelgroup labelgroup where labelgroup.scope = 'WHITEBOX') loop 101 v_sql := v_sql || ' ( 102 select count(labelvalueTmp.value) from dtms_issuelabelvalue labelvalueTmp left join 103 dtms_issuelabel labelTmp on labelvalueTmp.issuelabelid = labelTmp.id left join 104 dtms_issue issueTmp on labelvalueTmp.issueid = issueTmp.id where labelTmp.groupid = ' || rc.groupid || 105 ' and issueTmp.fixeduser = issue.fixeduser and issueTmp.createTime ' || v_date_condition || ') as ' || rc.groupname || ','; 106 end loop; 107 108 --dbms_output.put_line(substr(v_sql, 1, length(v_sql) - 1)); 109 open v_cur for 'select issue.fixeduser as fixeduser, ' || substr(v_sql, 1, length(v_sql) - 1) || 110 ' from dtms_issue issue' || 111 ' where issue.fixeduser in ' || v_fixedUsers || 112 ' group by issue.fixeduser'; 113 end; 114 115 116 create or replace procedure P_DTMS_REP_WHITEBOXMEMBERS(v_startDate in TIMESTAMP, v_endDate in TIMESTAMP, v_scope in varchar2, v_members in varchar2, v_cur out sys_refcursor) 117 is 118 v_startDate1 timestamp := to_timestamp('2015-01-01 00:00:00.000','YYYY-MM-DD HH24:MI:SS.ff9'); 119 v_endDate1 timestamp := to_timestamp('2015-01-15 23:59:59.999','YYYY-MM-DD HH24:MI:SS.ff9'); 120 v_fixedusers varchar2(1000) := '''developManager2'', ''developManager1'''; 121 122 v_date_condition varchar2(500) := ' between ' || 'to_timestamp(''' || to_char(v_startDate1, 'yyyy-mm-dd hh24:mi:ss.ff9') || ''', ''yyyy-mm-dd hh24:mi:ss.ff9'')' || ' and ' || 'to_timestamp(''' || to_char(v_endDate1, 'yyyy-mm-dd hh24:mi:ss.ff9') || ''', ''yyyy-mm-dd hh24:mi:ss.ff9'')'; 123 --v_date_condition varchar2(500) := ' between ' || 'to_timestamp(''' || to_char(v_startDate, 'yyyy-mm-dd hh24:mi:ss.ff9') || ''', ''yyyy-mm-dd hh24:mi:ss.ff9'')' || ' and ' || 'to_timestamp(''' || to_char(v_endDate, 'yyyy-mm-dd hh24:mi:ss.ff9') || ''', ''yyyy-mm-dd hh24:mi:ss.ff9'')'; 124 v_sql long; 125 126 begin 127 for rc in (select labelgroup.id as groupid, labelgroup.name as groupname from dtms_issuelabelgroup labelgroup where labelgroup.scope = 'WHITEBOX') loop 128 v_sql := v_sql || ' ( 129 select count(labelvalueTmp.value) from dtms_issuelabelvalue labelvalueTmp left join 130 dtms_issuelabel labelTmp on labelvalueTmp.issuelabelid = labelTmp.id left join 131 dtms_issue issueTmp on labelvalueTmp.issueid = issueTmp.id where labelTmp.groupid = ' || rc.groupid || 132 ' and issueTmp.fixeduser = issue.fixeduser and issueTmp.createTime ' || v_date_condition || ') as ' || rc.groupname || ','; 133 end loop; 134 135 --dbms_output.put_line(substr(v_sql, 1, length(v_sql) - 1)); 136 open v_cur for 'select issue.fixeduser as fixeduser, ' || substr(v_sql, 1, length(v_sql) - 1) || 137 ' from dtms_issue issue' || 138 ' where issue.fixeduser in (' || v_fixedusers || 139 --' where issue.fixeduser in (' || v_members || 140 ') group by issue.fixeduser'; 141 end; 142 143 create or replace procedure P_DTMS_REP_WHITEBOXMEMBERS(v_startDate in date, v_endDate in date, v_scope in varchar2, v_cur out sys_refcursor) 144 is 145 v_startDate1 timestamp := to_timestamp('2015-01-01 00:00:00.000','YYYY-MM-DD HH24:MI:SS.ff9'); 146 v_endDate1 timestamp := to_timestamp('2015-01-15 23:59:59.999','YYYY-MM-DD HH24:MI:SS.ff9'); 147 v_date_condition varchar2(500) := ' between ' || 'to_timestamp(''' || to_char(v_startDate1, 'yyyy-mm-dd hh24:mi:ss.ff9') || ''', ''yyyy-mm-dd hh24:mi:ss.ff9'')' || ' and ' || 'to_timestamp(''' || to_char(v_endDate1, 'yyyy-mm-dd hh24:mi:ss.ff9') || ''', ''yyyy-mm-dd hh24:mi:ss.ff9'')'; 148 -- v_totalgroup number; 149 v_sql long; 150 v_fixedusers varchar2(1000) := '(''developManager2'', ''developManager1'')'; 151 begin 152 for rc in (select labelgroup.id as groupid, labelgroup.name as groupname from dtms_issuelabelgroup labelgroup where labelgroup.scope = 'WHITEBOX') loop 153 v_sql := v_sql || ' ( 154 select count(labelvalueTmp.value) from dtms_issuelabelvalue labelvalueTmp left join 155 dtms_issuelabel labelTmp on labelvalueTmp.issuelabelid = labelTmp.id left join 156 dtms_issue issueTmp on labelvalueTmp.issueid = issueTmp.id where labelTmp.groupid = ' || rc.groupid || 157 ' and issueTmp.fixeduser = issue.fixeduser and issueTmp.createTime ' || v_date_condition || ') as ' || rc.groupname || ','; 158 end loop; 159 160 --dbms_output.put_line(substr(v_sql, 1, length(v_sql) - 1)); 161 open v_cur for 'select issue.fixeduser as fixeduser, ' || substr(v_sql, 1, length(v_sql) - 1) || 162 ' from dtms_issue issue' || 163 ' where issue.fixeduser in ' || v_fixedusers || 164 ' group by issue.fixeduser'; 165 end;
浙公网安备 33010602011771号