平时积累的一些SQL语句(转) 1

/* Formatted on 2011-1-12 10:48:19 (QP5 v5.149.1003.31008) */
---1.各个部门工资排名前几名的员工信息----------
--a.

SELECT *
  FROM emp a
 WHERE (SELECT COUNT (*)
          FROM emp
         WHERE deptno = a.deptno AND sal > a.sal) <= 2         --改变该值即可以得到相应名次
       AND a.deptno IS NOT NULL;

/*b.c.d区别注意：
    1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果         
    2.rank()和dense_rank()的区别是：
      --rank()是跳跃排序，有两个第二名时接下来就是第四名
      --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
*/
--b.

SELECT *
  FROM (SELECT deptno,
               ename,
               sal,
               ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) rn
          FROM emp)
 WHERE rn <= 3;

--c.

SELECT *
  FROM (SELECT deptno,
               ename,
               sal,
               RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rn
          FROM emp)
 WHERE rn <= 3;

--d.

SELECT *
  FROM (SELECT deptno,
               ename,
               sal,
               DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rn
          FROM emp)
 WHERE rn <= 3;

--e

  SELECT a.deptno,
         a.empno,
         a.ename,
         a.sal,
         -- 可跳跃的rank
         RANK () OVER (PARTITION BY a.deptno ORDER BY a.sal DESC) r1,
         -- 密集型rank
         DENSE_RANK () OVER (PARTITION BY a.deptno ORDER BY a.sal DESC) r2,
         -- 不分组排序
         RANK () OVER (ORDER BY sal DESC) r3
    FROM scott.emp a
ORDER BY a.deptno, a.sal DESC;

---2.获取所有员工中工资第几高的员工信息

SELECT *
  FROM (SELECT t.*, DENSE_RANK () OVER (ORDER BY t.sal DESC) RANK
          FROM emp t)
 WHERE RANK = 3;

--3.获取员工表中员工信息以及员工所在部门的最高工资和平均工资信息--------

SELECT e.*, sa.maxsal, sa.avgsal
  FROM emp e,
       (  SELECT MAX (sal) maxsal, AVG (sal) avgsal, deptno
            FROM emp ine
        GROUP BY deptno) sa
 WHERE e.deptno = sa.deptno OR (e.deptno IS NULL AND sa.deptno IS NULL);

--4.求员工工资所占部门总工资的比率

  SELECT ename,
         deptno,
         sal,
         TRUNC (sal * 100 / SUM (sal) OVER (PARTITION BY deptno), 2) percent
    FROM emp
ORDER BY deptno;

--5.获取所有员工中工资最高的几位
--a.

SELECT *
  FROM (  SELECT *
            FROM emp
        ORDER BY sal DESC)
 WHERE ROWNUM <= 2;

--b.

SELECT *
  FROM (SELECT ename,
               deptno,
               sal,
               ROW_NUMBER () OVER (ORDER BY sal DESC) ee
          FROM emp)
 WHERE ee <= 4 AND ee >= 2;

--6.获取各个部门工资排名在2到3 位的员工信息
--a.

SELECT *
  FROM (SELECT deptno,
               ename,
               sal,
               ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) rn
          FROM emp)
 WHERE rn <= 3 AND rn >= 2;

--b.

SELECT *
  FROM (SELECT deptno,
               ename,
               sal,
               RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rn
          FROM emp)
 WHERE rn <= 3 AND rn >= 2;

--c.

SELECT *
  FROM (SELECT deptno,
               ename,
               sal,
               DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rn
          FROM emp)
 WHERE rn <= 3 AND rn >= 2;

--7查出各个部门中工资大于平均工资的员工信息
--a(good)

SELECT *
  FROM emp e
 WHERE e.sal > (  SELECT AVG (sal)
                    FROM emp ine
                GROUP BY deptno
                  HAVING e.deptno = ine.deptno);

--b(not good)

  SELECT e.*, TRUNC (d.sal) avgsal
    FROM emp e,
         (  SELECT AVG (sal) sal, deptno
              FROM emp
          GROUP BY deptno) d
   WHERE e.deptno = d.deptno AND e.sal > d.sal
ORDER BY e.deptno;

--8根据排序来取得后一个值和当前值相加的结果

SELECT *
  FROM (SELECT ename,
               deptno,
               sal,
               SUM (sal)
               OVER (ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                  ee
          FROM emp);

---9.分页处理的相关语句--------
--a最正确

SELECT *
  FROM (SELECT ROWNUM AS num, ine.*
          FROM (  SELECT *
                    FROM emp
                ORDER BY empno) ine
         WHERE ROWNUM <= 7)
 WHERE num >= 3;

--b

SELECT *
  FROM (  SELECT ROWNUM AS num, emp.*
            FROM emp
           WHERE ROWNUM <= 7
        ORDER BY empno)
 WHERE num >= 3;

--c

SELECT *
  FROM (  SELECT ROWNUM AS num, emp.*
            FROM emp
        ORDER BY empno)
 WHERE num >= 3 AND num <= 7;

--11.根据工资高低来判断该员工的等级

SELECT e.ename,
       e.sal,
       DECODE (SUBSTR (TO_CHAR (TRUNC (sal, -3)), 1, 1),
               0, 'sorry',
               1, '1ok',
               2, '2ok',
               3, '3ok',
               4, '4ok',
               5, '5ok',
               6, '6ok',
               7, 'higth',
               'to enough')
  FROM emp e;

/*--12.
  sal<1000  显示低工资   sal-1000<0   sign(sal-1000) = -1
   1000<=sal<=3000  正常工资
   3000<sal<=5000  高工资
*/

SELECT sal,
       DECODE (
          SIGN (sal - 1000),
          -1, '低工资',
          DECODE (
             SIGN (sal - 3000),
             -1, '正常工资',
             0, '正常工资',
             1, DECODE (SIGN (sal - 5000), -1, '高工资', '高工资')))
          AS 工资状态
  FROM emp;

SELECT CASE WHEN sal >= 5000 THEN '高工资' END sal FROM emp;

--13.一年以后的今天

SELECT ADD_MONTHS (SYSDATE, 12) FROM DUAL;

--14.一年以前的今天

SELECT ADD_MONTHS (SYSDATE, -12) FROM DUAL;

--15.本月第3天的日期

SELECT ADD_MONTHS (LAST_DAY (SYSDATE) + 3, -1) FROM DUAL;

--20.求最大值

SELECT GREATEST (100,
                 90,
                 80,
                 101,
                 01,
                 19)
  FROM DUAL;

--21.求最小值

SELECT LEAST (100,
              0,
              -9,
              10)
  FROM DUAL;

--30计算工资在2000以上的各种工作的平均工资

SELECT job, AVG (sal)
  FROM emp
 WHERE sal > 2000
-- set amount mask


set_amount_mask;


END pre_form;

 

PROCEDURE set_amount_mask
IS
BEGIN
   -------------------------------------


   -- setup amount field's format mask


   -- ----------------------------------

 

   SET_ITEM_PROPERTY (
      'HEADERS.TOTAL_AMOUNT',
      FORMAT_MASK,
      fnd_currency.
       get_format_mask (
         :parameter.currency_code,
         GET_ITEM_PROPERTY ('HEADERS.TOTAL_AMOUNT', MAX_LENGTH)));

 

   SET_ITEM_PROPERTY (
      'LINES.LINE_AMOUNT',
      FORMAT_MASK,
      fnd_currency.
       get_format_mask (:parameter.currency_code,
                        GET_ITEM_PROPERTY ('LINES.LINE_AMOUNT', MAX_LENGTH)));
END set_amount_mask;

 

动态提交请求

APPS.FND_REQUEST.SUBMIT_REQUEST

(

APPLICATION IN VARCHAR2 DEFAULT NULL,

PROGRAM IN VARCHAR2 DEFAULT NULL,

DESCRIPTION IN VARCHAR2 DEFAULT NULL,

START_TIME IN VARCHAR2 DEFAULT NULL,

SUB_REQUEST IN BOOLEAN DEFAULT FALSE,

chr(0),'','','','','','','','','','','','','','','','','','','',

'','','','','','','','','','', '','','','','','','','','','',

'','','','','','','','','','', '','','','','','','','','','',

'','','','','','','','','','', '','','','','','','','','','',

'','','','','','','','','','', '','','','','','','','','',''

)

RETURN NUMBER;

 

状态判断
get_block_property('headers',status)

:SYSTEM.Mode
GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),WINDOW_NAME
Get_Block_Property( 'LINES_PROMPT', PREVIOUSBLOCK)
GET_RECORD_PROPERTY(:SYSTEM.CURSOR_RECORD,'SHOPPEDAYOVERTB_V',Status );
--:SYSTEM.RECORD_STATUS ;

键弹性域定义和更新
定义


fnd_key_flex.define(

BLOCK=>'Items',

FIELD=>'EXPENSE_ACCID_DSP',

APPL_SHORT_NAME=>'SQLGL',

CODE=>'GL#',

ID=>'EXPENSE_CCID',

REQUIRED=>'Y',

USEDBFLDS=>'N',

updateable=>'',

VALIDATE=> 'FULL',

VRULE=> '\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN',

NUM=>'ARAMETER.CHART_OF_ACCOUNTS_ID');

Form中执行SQL语句


sql1:=' TRUNCATE TABLE cfnd_matrix_cells';


FORMS_DDL(sql1);

  SELECT a.empno,
         a.ename,
         a.sal,
         -- 上面一行
         LAG (a.sal) OVER (ORDER BY a.sal DESC) lag_1,
         -- 下面三行
         LEAD (a.sal, 1) OVER (ORDER BY a.sal DESC) lead_1
    FROM scott.emp a
ORDER BY a.sal DESC;

-- 用exists替代distinct的例子

SELECT DISTINCT d.deptno, dname
  FROM dept d, emp e
 WHERE d.deptno = e.deptno;

SELECT deptno, dname
  FROM dept d
 WHERE EXISTS
          (SELECT 'x'
             FROM emp e
            WHERE d.deptno = e.deptno);

-------=============行列转换部分处理================----------
--101.行列转换 行转列
DROP TABLE t_change_lc;

CREATE TABLE t_change_lc
(
   card_code   VARCHAR2 (3),
   q           NUMBER,
   bal         NUMBER
);

INSERT INTO t_change_lc
       SELECT '001' card_code, ROWNUM q, TRUNC (DBMS_RANDOM.VALUE * 100) bal
         FROM DUAL
   CONNECT BY ROWNUM <= 4
   UNION
       SELECT '002' card_code, ROWNUM q, TRUNC (DBMS_RANDOM.VALUE * 100) bal
         FROM DUAL
   CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

  SELECT a.card_code,
         SUM (DECODE (a.q, 1, a.bal, 0)) q1,
         SUM (DECODE (a.q, 2, a.bal, 0)) q2,
         SUM (DECODE (a.q, 3, a.bal, 0)) q3,
         SUM (DECODE (a.q, 4, a.bal, 0)) q4
    FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

--102.行列转换 列转行
DROP TABLE t_change_cl;

CREATE TABLE t_change_cl
AS
     SELECT a.card_code,
            SUM (DECODE (a.q, 1, a.bal, 0)) q1,
            SUM (DECODE (a.q, 2, a.bal, 0)) q2,
            SUM (DECODE (a.q, 3, a.bal, 0)) q3,
            SUM (DECODE (a.q, 4, a.bal, 0)) q4
       FROM t_change_lc a
   GROUP BY a.card_code
   ORDER BY 1;

SELECT * FROM t_change_cl;

  SELECT t.card_code,
         t.rn q,
         DECODE (t.rn,  1, t.q1,  2, t.q2,  3, t.q3,  4, t.q4) bal
    FROM (SELECT a.*, b.rn
            FROM t_change_cl a,
                 (    SELECT ROWNUM rn
                        FROM DUAL
                  CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;

--103.行列转换 行转列 合并
DROP TABLE t_change_lc_comma;

CREATE TABLE t_change_lc_comma
AS
   SELECT card_code, 'quarter_' || q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

    SELECT t1.card_code, SUBSTR (MAX (SYS_CONNECT_BY_PATH (t1.q, ';')), 2) q
      FROM (SELECT a.card_code,
                   a.q,
                   ROW_NUMBER () OVER (PARTITION BY a.card_code ORDER BY a.q) rn
              FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn
  GROUP BY t1.card_code;

--104.行列转换 列转行 分割
DROP TABLE t_change_cl_comma;

CREATE TABLE t_change_cl_comma
AS
       SELECT t1.card_code, SUBSTR (MAX (SYS_CONNECT_BY_PATH (t1.q, ';')), 2) q
         FROM (SELECT a.card_code,
                      a.q,
                      ROW_NUMBER () OVER (PARTITION BY a.card_code ORDER BY a.q)
                         rn
                 FROM t_change_lc_comma a) t1
   START WITH t1.rn = 1
   CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn
     GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;

  SELECT t.card_code,
         SUBSTR (t.q, INSTR (';' || t.q,
                             ';',
                             1,
                             rn), INSTR (t.q || ';',
                                         ';',
                                         1,
                                         rn)
                                  - INSTR (';' || t.q,
                                           ';',
                                           1,
                                           rn))
            q
    FROM (SELECT a.card_code, a.q, b.rn
            FROM t_change_cl_comma a,
                 (    SELECT ROWNUM rn
                        FROM DUAL
                  CONNECT BY ROWNUM <= 100) b
           WHERE INSTR (';' || a.q,
                        ';',
                        1,
                        rn) > 0) t
ORDER BY 1, 2;

-------------==================end 行列转换====================--------------
--105如果存在就更新，不存在就插入用一个语句实现
DROP TABLE t_mg;

CREATE TABLE t_mg
(
   code   VARCHAR2 (10),
   NAME   VARCHAR2 (10)
);

SELECT * FROM t_mg;

MERGE INTO t_mg a
     USING (SELECT 'the code' code, 'the name' NAME FROM DUAL) b
        ON (a.code = b.code)
WHEN MATCHED
THEN
   UPDATE SET a.NAME = b.NAME
WHEN NOT MATCHED
THEN
   INSERT     (code, NAME)
       VALUES (b.code, b.NAME);

---106-----========集合交并处理======================
DROP TABLE t1;
DROP TABLE t2;

CREATE TABLE t1
(
   id     NUMBER,
   NAME   VARCHAR2 (20)
);

CREATE TABLE t2
(
   id     NUMBER,
   NAME   VARCHAR2 (20)
);

INSERT INTO t1
     VALUES (1, 'a');

INSERT INTO t1
     VALUES (2, 'b');

INSERT INTO t1
     VALUES (3, 'c');

INSERT INTO t1
     VALUES (4, 'd');

INSERT INTO t1
     VALUES (5, '');

SELECT * FROM t1;

SELECT *
  FROM t1
 WHERE NAME IS NULL;

INSERT INTO t2
     VALUES (1, 'a');

INSERT INTO t2
     VALUES (2, 'b');

INSERT INTO t2
     VALUES (3, 'c');

INSERT INTO t2
     VALUES (12, 'a');

INSERT INTO t2
     VALUES (22, '');

INSERT INTO t2
     VALUES (32, '');

SELECT * FROM t2;

SELECT * FROM t1;

SELECT * FROM t2;

SELECT * FROM t1
UNION                                                      --两表取并集，无重复值，且对结果排序
SELECT * FROM t2;

SELECT * FROM t1
UNION ALL                                                  --两表取并集，有重复值，且结果不排序
SELECT * FROM t2;

SELECT * FROM t1
MINUS                                                            --ti-t2 两表去差集
SELECT * FROM t2;

SELECT *
  FROM t1
INTERSECT --两表取交集

DELETE
FROM fnd_lobs
WHERE file_id = l_gfm_id;

FORMS_DDL('commit');

 FORMS_DDL('commit');

END IF;

END IF;
END IF;

END;

---未完转下