[javaScript] 오픈소스 js-sql-parser 예제

github.com/JavaScriptor/js-sql-parser

JavaScriptor/js-sql-parser

const parser = require("js-sql-parser");

const ast = parser.parse("select empno, deptno from emp");

 

console.log(JSON.stringify(ast, null, 2));

 

console.log(parser.stringify(ast));

 

아래와 같이 console 에 찍힌 걸 볼 수있다.

 

그렇다면 const ast = parser.parse("select * from dual"); 를 사용한 이후 스키마의 값을 변경해서 sql문을 바꿀 수 있을까 ?

 

 

const parser = require("js-sql-parser");

const ast = parser.parse("select * from dual");

 

console.log(JSON.stringify(ast, null, 2));

 

ast.value.selectItems.value[0].value = "empno";

ast.value.from.value[0].value.value.value = "emp";

 

 

console.log(parser.stringify(ast));

 

select empno FROM emp 로 결과가 알맞게 나온 것을 알 수 있다. 

 

 

js-sql-parser를 이용해서 스칼라 서브쿼리, 계층형 질의 등 복잡한 sql을 만들며 테스트를 해보도록 하겠다.

 

우선 parser에 sql문을 삽입하여 정상적으로 파싱 되는지 예제로 테스트를 해보자.

 

const parser = require("js-sql-parser");

const ast = parser.parse("select * from dual");

 

1. Selector 

 

1) alias로 sql 입력

 

const ast = parser.parse("SELECT empno as ENO FROM emp");

 

정상 동작함을 확인 할 수 있다.

 

 

const ast = parser.parse("SELECT empno as 사원 FROM emp"); 으로 예제를 해봤는데 오류가 났다.

 

 

 

한글 alias는 안되는 것으로 추측해본다. 처음에 위의 예제로 해봤는데 안되서 해맸다가 영어로 alias를 사용하니 정상 동작 했다.

 

 

 

1-1) 값으로 넣어 parser 확인 하기 

 

ast.value.selectItems.value[0].value = "empno";

ast.value.selectItems.value[0].alias = "번호";

ast.value.from.value[0].value.value.value = "emp";

2) 컬럼 alias + where 조건절 추가

 

const ast = parser.parse(

"select player_name pname from player where team_id = 'KO2'"

);

 

정상 동작함을 알 수 있다.

 

 

그렇다면 마찬가지로 컬럼alias + where 조건절에 비교연산자를 사용할 경우에는 ?

 

const ast = parser.parse(

"select player_name pname from player where HEIGHT >= 170"

);

 

operator, type 등이 변경된 것을 알 수 있다.

 

3) 컬럼 alias + where 조건절 추가 (IN)

 

const ast = parser.parse(

"select player_name pname from player where TEAM_ID IN('K02', 'K07')"

);

 

보면 where절의 Type이 InExpressionListPredicate로 된것을 볼 수 있다.

 

3-1) 컬럼 alias + where 조건절 추가 (다중 IN)

 

const ast = parser.parse(

"select player_name pname from player where (JOB, DEPTNO) IN(('MANAGER', 20), ('CLERK', 30))"

);

 

where 절의 type은 InExpressionListPredicate 이 되고, right, left value 의 type이 ExpressionList가 된 것 을 알 수 있다.

 

3-2)  where 조건절 추가 (AND 와 IN 함께 사용) 

const ast = parser.parse(

"select player_name pname from player where JOB IN('MANAGER', 'CLERK') AND DEPTNO IN(20, 30)"

);

where 의 type 이 AndExpression 로 바뀌고, operator가 AND가 들어감을 알 수 있다. 그외는 IN을 사용했을 경우와 같은듯 

 

4) where 절 LIKE 연산자 

const ast = parser.parse(

"select player_name pname from player where position LIKE 'MF'"

);

where 연산자의 type이 LikePredicate로 바뀜을 알 수 있다.

 

4-1) where 절 LIKE 연산자 + 특수 문자

 

const ast = parser.parse(

"select player_name pname from player where position LIKE '장%'"

);

이친구는 %, _과 같은 옵션을 사용하는 것인데 bind variable에서 prepared statement를 사용할 경우 특수문자가 안되어 터진 이슈를 급하게 해결했던 기억이 나는 녀석이다.. 

 

 

그저 where에서 value값만 바뀌는군 .. 

 

5) where 절 Between 연산자 

 

const ast = parser.parse(

"select player_name pname from player where height between 170 and 180"

);

 

where절에서 type이 BetweenPredicate가 된 것을 알 수 있다.

 

6) where 절 NULL 사용

 

const ast = parser.parse(

"select player_name pname from player where position = NULL"

);

where절의 type이 ComparisionBoleanPrimary 가 되고 right의 type은 Null, value도 null 이 됨을 알 수 있다.

 

 

7) where 절 조건 두 개 

 

const ast = parser.parse(

"select player_name pname from player where TEAM_ID = 'K02' AND HEIGHT >= 170"

);

 

where 절의 type이 AndExpression 로 바뀌고 left, right에 각각 들어감 

 

7-1) where 절 조건 두 개 이상

const ast = parser.parse(

"select player_name pname from player where TEAM_ID = 'K02' AND (HEIGHT >= 170 OR HEIGHT <=100)"

);

 

left, right에 괄호를 친 부분 두개가 들어가 있다.

 

그렇다면 괄호를 제외하고 총 where 절에 3개의 조건을 단다면 ?

 

7-2) where 절 조건 두 개 이상

const ast = parser.parse(

"select player_name pname from player where TEAM_ID = 'K02' AND HEIGHT >= 170 OR HEIGHT <=100"

);

left, right, right가 됨을 알 수 있다.

 

8) where 절 IN 과 AND 사용할 경우

 

const ast = parser.parse(

"select player_name pname from player where TEAM_ID IN('K02', 'K07') AND POSITION = 'MF'"

);

이제 여러번 하다보니 어떻게 결과가 나올지 대충 예상이 간다. 

9. where절에 다중 조건문을 사용할 경우

const ast = parser.parse(

"select player_name pname from player where TEAM_ID = 'K02' OR TEAM_ID = 'K07' AND POSITION = 'MF' AND HEIGHT >=170"

);

처음 left로 OR 조건 하나에 right 부분에는 AND 조건이 여러개 중첩된것 을 확인했다.

 

10) where 절 NOT 연산자 사용

 

const ast = parser.parse(

"select player_name pname from player where TEAM_ID = 'K02' AND NOT POSITION= 'MF' AND NOT HEIGHT BETWEEN 175 AND 185"

);

 

 

type으로 NotExpression이 들어간 것을 알 수 있다. 

 

10) where 절 NOT 연산자를 <> 로 사용

 

 SELECT player_name pname FROM player WHERE TEAM_ID = 'K02' AND POSITION <> 'MF'

 

이건 예상과 다르게 나왔다. type으로 NotExpression이 나올줄 알았는데 operator로 <> 가 들어가고 type으론ComparisonBooleanPrimary가 들어갔다.

 

 

11) ROWNUM 사용

 SELECT player_name pname FROM player WHERE ROWNUM = 1

 

이것도 예상과 다르게 결과가 나왔다.

 

where 절에서 우리가 일반 조건절을 사용할 경우와 동일하게 left의 value와 right의 value로 결과가 나왔다. 

 

12) TOP 사용 

이것도 예상과 다르게 결과가 나왔다.

selectItems 의 type으로 FunctionCall이 들어갔다.

 

 

예외) TOP WITH TIES(SQL SERVER)

select TOP(1) WITH TIES ename from emp order by sal desc

WITH TIES 는 제공하지 않는듯

 

 

 

 

 

13) 내장함수 사용 (Built In)

 SELECT LENGTH('SQL Expert') FROM dual

 

 

selectItems 의 type이 마찬가지로 FunctionCall 이됨 

 

13-1) 내장함수 CONCAT 사용

 

 

마찬가지로 type은 FunctionCall 

 

14) 숫자형 함수(ROUND, TRUNC .. 등)

SELECT ENAME, ROUND(SAL / 12, 1), TRUNC(SAL / 12, 1) FROM EMP

 

 

type은 FunctionCall 이며 name이 숫자형 함수이고 사이 값은 BitExpression, Operator로 각 값을 넣어주고 있다. 

 

15) 날짜형 함수(Oracle)

SELECT ENAME, HIREDATE, TO_NUMBER(TO_CHAR(HIREDATE, 'YYYY')) FROM EMP

 

오호 이제뭔가 딱 직관적으로 어캐 결과가 나올지 느낌이 오는것 같다.

 

Oracle 날짜형 함수

 

 

15-1) 날짜형 함수(SQL)

 SELECT ENAME, HIREDATE, YEAR(HIREDATE) FROM EMP

 

SQL 날짜형 함수

Oracle 날짜형 함수와 동일하게 name으로 함수가 들어감을 확인했다.

 

16) 변환형 함수

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD')

 

그렇다면  'YYYY. MON, DAY' 형식은? 

 

SELECT TO_CHAR(SYSDATE, 'YYYY. MON, DAY')

 

selectItems.value[0].params[0].value값으로 어떤 형식이 들어가냐 차이이고 나머지는 동일하다.

 

17) CASE 문 사용

SELECT ENAME, CASE WHEN SAL > 2000 THEN SAL ELSE 2000 END REVISED_SALARY FROM EMP

 

selectItems type으로 CaseWhen 이 들어가고 when, then, else로 들어감.

 

 

17-1) case 문의 WHEN , THEN 여러개 사용

SELECT ENAME, CASE WHEN SAL >= 3000 THEN 'HIGH' WHEN SAL >= 1000 THEN 'MID' ELSE LOW END AS SALARY_GRADE FROM EMP

CASE문을 하나 사용할 경우와 별 차이 없이 when, then 조건이 하나더 추가된 결과가 나옴

 

18) ISNULL 사용 

 SELECT ISNULL(NULL, 'NVL-OK') ISNULL_TEST

 

마찬가지로 type은 FunctionCall이고 name에 ISNULL이 들어갔음

 

19) 집계함수

SELECT count(*) total FROM emp e

 

마찬가지로 name에 count가 들어가고 params 에 *가 들어감

 

19-1) CASE 문 여러개 

SELECT TEAM_ID, NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END), 0) FW, 

NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END), 0) MF, 

NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END), 0) DF, 

COUNT(*) SUM FROM PLAYER GROUP BY TEAM_ID

 

 

 

이하 생략

중첩되어 있는 채로 나타남

 

20) ORDER BY 

 SELECT player_name FROM player ORDER BY player_name DESC

orderby value의 type으로 GroupByOrderByItem이 들어감. sortOpt로 desc, asc를 구분 할 수 있을 듯

 

 

20-1) ORDER BY 조건 여러개

 SELECT player_name FROM player ORDER BY player_name DESC, back_no

 

order by로 여러 개의 컬럼을 기준을 두어 sort 할 시 dsec, asc 를 적어주지 않으면 null 로 들어가는듯.

 

 

20-2) ORDER BY 조건 1, 2 로 표현

SELECT player_name, position FROM player ORDER BY 2 DESC, 1

숫자를 이용해서 ORDER BY 조건절을 표현할 수 있는데 value로 값이 들어감

 

 

2. FROM

 

1) alias

 

const ast = parser.parse("SELECT a.empno FROM emp a");

정상적으로 들어간 것을 확인 할 수 있다.

 

2) 서브쿼리 (인라인 뷰)

SELECT ENAME, SAL FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM < 4

 

"from": {
      "type": "TableReferences",
      "value": [
        {
          "type": "TableReference",
          "value": {
            "type": "TableFactor",
            "value": {
              "type": "SubQuery",
              "value": {
                "type": "Select",
                "distinctOpt": null,
                "highPriorityOpt": null,
                "maxStateMentTimeOpt": null,
                "straightJoinOpt": null,
                "sqlSmallResultOpt": null,
                "sqlBigResultOpt": null,
                "sqlBufferResultOpt": null,
                "sqlCacheOpt": null,
                "sqlCalcFoundRowsOpt": null,
                "selectItems": {
                  "type": "SelectExpr",
                  "value": [
                    {
                      "type": "Identifier",
                      "value": "ENAME",
                      "alias": null,
                      "hasAs": null
                    },
                    {
                      "type": "Identifier",
                      "value": "SAL",
                      "alias": null,
                      "hasAs": null
                    }
                  ]
                },
                "from": {
                  "type": "TableReferences",
                  "value": [
                    {
                      "type": "TableReference",
                      "value": {
                        "type": "TableFactor",
                        "value": {
                          "type": "Identifier",
                          "value": "EMP"
                        },
                        "partition": null,
                        "alias": null,
                        "hasAs": null,
                        "indexHintOpt": null
                      }
                    }
                  ]
                },
                "partition": null,
                "where": null,
                "groupBy": null,
                "having": null,
                "orderBy": {
                  "type": "OrderBy",
                  "value": [
                    {
                      "type": "GroupByOrderByItem",
                      "value": {
                        "type": "Identifier",
                        "value": "SAL"
                      },
                      "sortOpt": "DESC"
                    }
                  ],
                  "rollUp": null
                },
                "limit": null,
                "procedure": null,
                "updateLockMode": null
              }
            },
            "alias": null,
            "hasAs": null
          }
        }
      ]
    },
    "partition": null,

 

인라인 뷰로 사용했더니 fro 안에 type이 subQuery가 된다.

 

3) 서브쿼리 where 절 

 

select team_id, player_name, position, back_no, height from player x

where x.team_id = 'K02'

and

not exists (select 1 from player y where y.player_id = x.player_id and position = 'MF')

 

3). 서브쿼리 where 절 

where 절 안에 서브쿼리가 들어감. right로 type이 SubQuery로 알맞게 들어감.

 

4). 다중 행 서브쿼리

 

select region_name, team_name, e_team_name

from team

where team_id IN(select team_id from player where player_name='정현수')

 

4). 다중 행 서브쿼리 

오호 where절에 type으로 InSubQueryPredicate로 제공했다. 

 

 

5). 다중 컬럼 서브 쿼리

 

select team_id, player_name, position, back_nom height from player

where (team_id, height) IN

(select team_id, min(height) from player group by team_id)

 

5). 다중 컬럼 서브쿼리

아까와 차이점은 where.value[0] 안에 team_id, height두개가 다 들어가 있다는 것임.

IN으로 사용 할 값이 두개다 들어가있고 이후는 위의 다중 행 서브쿼리와 매우 유사함.

 

6). 연관 서브쿼리

-> 서브쿼리 내에 메인쿼리의 칼럼을 사용한 서브쿼리임.

 

select t.team_name, m.player_name, m.position, m.back_no, m.height

from player m, team t

where m.team_id = t.team_ID

and m.height < (select avg(s.height)

from player s

where s.team_id = m.team_id

and s.height is not null

group by s.team_id)

 

6). 연관 서브쿼리

 

보면 left로 M.HEIGHT가 들어가고 right의 type으로 SubQuery가 들어감. 

 

7). 서브쿼리 HAVING 절에서 사용

 

SELECT P.TEAM_ID, T.TEAM_NAME, AVG(P.HEIGHT)

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID

GROUP BY P.TEAM_ID, T.TEAM_NAME

HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT)

FROM PLAYER

WHERE TEAM_ID = 'K02')

 

7).having 절 서브쿼리 

having 안의 having.type으로 FunctionCall 이 들어가고, 그 안에 selectItems, from , where로 서브쿼리 부분이 들어감

 

8.) 서브쿼리 UPDATE 문 SET 절(X)

 

UPDATE TEAM A SET A.E_TEAM_NAME = (SELECT X.STADIUM_NAME FROM STADIUM X WHERE X.STADIUM_ID = A.STADIUM_ID)

8).서브쿼리 update문

지원안하는듯

 

9.) 서브쿼리 INSERT 문의 VALUES 절(X)

INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID) VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06')

 

지원안하는듯

 

 

 

 

 

3. JOIN

 

1) WHERER 절을 이용한 JOIN 

const ast = parser.parse(

"select player.palyer_name, team.team_name from player, team where player.team_ID = team.team.ID"

);

 

 

join 쿼리같은 경우에는 where 로 값이 들어갔다. 

 

2) INNER JOIN 

 

select player.palyer_name, team.team_name from player INNER JOIN team ON player.team_ID = team.team.ID

 

INNER JOIN

오호 INNER JOIN같은 경우에는 from.value[0].value.type으로 InnerCrossJoinTable,  from.value[0].value.innerCrossOpt 로 INNER 가 들어 갔다. 그러고 left, right에 각각 테이블이름이 들어가고 condition으로 JoinKey와 관련된 부분이 들어감 

 

3) Non EQUI JOIN 

 

select e.ename, e.job, e.sal, s.grade from EMP E, SALGRADE S WHERE E.sal between s.losal and s.hisal

 

 

where절에서 BetweenPredicate로 들어간 것을 볼 수 있다.

 

4) Table 이 3개 이상 JOIN 

 

 SELECT p.player_name, p.position, t.region_name, t.team_name, s.stadium_name FROM player p INNER JOIN team t ON p.team_ID = t.team_ID INNER JOIN STADIUM s ON T.stadium_ID = S.stadium_ID

 

 

"from": {
      "type": "TableReferences",
      "value": [
        {
          "type": "TableReference",
          "value": {
            "type": "InnerCrossJoinTable",
            "innerCrossOpt": "INNER",
            "left": {
              "type": "InnerCrossJoinTable",
              "innerCrossOpt": "inner",
              "left": {
                "type": "TableFactor",
                "value": {
                  "type": "Identifier",
                  "value": "player"
                },
                "partition": null,
                "alias": {
                  "type": "Identifier",
                  "value": "p"
                },
                "hasAs": false,
                "indexHintOpt": null
              },
              "right": {
                "type": "TableFactor",
                "value": {
                  "type": "Identifier",
                  "value": "team"
                },
                "partition": null,
                "alias": {
                  "type": "Identifier",
                  "value": "t"
                },
                "hasAs": false,
                "indexHintOpt": null
              },
              "condition": {
                "type": "OnJoinCondition",
                "value": {
                  "type": "ComparisonBooleanPrimary",
                  "left": {
                    "type": "Identifier",
                    "value": "p.team_ID"
                  },
                  "operator": "=",
                  "right": {
                    "type": "Identifier",
                    "value": "t.team_ID"
                  }
                }
              }
            },
            "right": {
              "type": "TableFactor",
              "value": {
                "type": "Identifier",
                "value": "STADIUM"
              },
              "partition": null,
              "alias": {
                "type": "Identifier",
                "value": "s"
              },
              "hasAs": false,
              "indexHintOpt": null
            },
            "condition": {
              "type": "OnJoinCondition",
              "value": {
                "type": "ComparisonBooleanPrimary",
                "left": {
                  "type": "Identifier",
                  "value": "T.stadium_ID"
                },
                "operator": "=",
                "right": {
                  "type": "Identifier",
                  "value": "S.stadium_ID"
                }
              }
            }
          }
        }
      ]
    },

 

보면 from 절 안에서 InnerCrossJoinTable이 중첩되어 두개로 나타나고 각 left, right로 이루어 진것을 볼 수 있음. 

 

5) Natural JOIN 

 

natrule join

 

마찬가지로 from.value[0].value.type으로 NatrualJoinTable이 들어가고 left, right에 각 테이블이 들어간 것을 볼 수 있다. 

 

6) USING 조건절 

const ast = parser.parse("select * from dept join dept_team using(deptno)");

6. USING 조건절 

condition으로 type으로 UsingJoinCondition이 들어감 

 

7. ON 조건절

 

select e.empno, e.ename, e.deptno, d.dname from emp e join dept d on(E.deptbo = d.deptno)

7. ON 조건절

condition type으로  OnJoinCondition 이 들어감. 

 

7-1). ON 조건절 + where 절 추가

select e.empno, e.ename, e.deptno, d.dname from emp e join dept d on(E.deptbo = d.deptno) where e.mgr = 7698

 

7.1 ON 조건절 + where 절 

8. 다중 테이블 JOIN

 

select e.empno, d.deptno, d.dname, t.dname new_dname

from emp e join dept d

on (e.deptno = d.deptno)

JOIN dept_team t on(e.deptno = t.deptno)

8. 다중 테이블 JOIN

condition type으로 OnJoinCondition들어가고, 첫 번재 table같은 경우에는 left , right 이후에 right로 들어감

구조가 left: (left :  emp, right : dept) right : dept_team 

 

이런식으로 들어간것

 

9. CROSS JOIN

select ename, dname from emp cross join dept

 

9. CROSS JOIN 

from.value[0].type 은 InnerCrossJoinTable, from.value[0].innerCrossOpt 는 cross로 들어감

 

10. OUTER JOIN

 

SELECT stadium_name, stadium.stadium_id, seat_count, hometeam_id, team_name 

FROM stadium 

LEFT OUTER JOIN team 

ON stadium.hometeam_id = team.team_id

 

10. OUTER JOIN

마찬가지로 value[0].value.type으로 LeftRightJoinTable이 들어가고  value[0].value.outOpt로 outer가 들어감. value[0].value.leftRight 는 left가 들어감 

 

아마 RIGHT OUTER JOIN은 위와  동일하되 value[0].value.leftRight는 right가 들어갈 듯.

 

 

11. FULL OUTER JOIN (X)

 

"select * from dept FULL OUTER join dept_team on dept.deptno = dept_team.deptno" 이렇게 쓰면 오류남

 

select * from dept FULL join dept_team on dept.deptno = dept_team.deptno  이렇게 써야함.. 

 

자세히보니 table alias로 들어가있음 FULL OUTER JOIN은 안됨 지원이

 

11. full outer join 

11-2). SELF JOIN (where 절 사용)

 

select worker.empno, worker.ename, manager.ename

from emp worker, emp manager

where worker.mgr = manager.empno

11-2 self join

11-3). SELF JOIN(ON 절 사용)

 

(X) 

select worker.empno, worker.ename, manager.ename

from emp worker

self join

emp manager on(worker.mgr = manager.empno)

 

self join은 inner join으로 대체해서 사용해야 할듯

 

select worker.empno, worker.ename, manager.ename

from emp worker

inner join

emp manager on(worker.mgr = manager.empno)

11-3). self join을 inner join으로 대체

 

12. UNION

 

 

18. UNION

 

type으로 Union이 들어감.

 

19. MINUS, INTERSECT (X)

19. MINUS, INTERSECT

 

 

4. 계층형 질의 (X)

 

select level, lpad(' ', 4 * (level-1)) + empno, mgr, CONNECT_BY_ISLEAF ISLEAF

FROM EMP

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR

4. 계층형 질의

제공안하는듯.. 

 

4-1).CTE(Common Table Expression) 재귀  (X) 

 

WITH EMPLOYEES_ANCHOR AS (SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 as LEVEL

FROM EMPLOYEES

WHERE REPORTSTO IS NULL

UNION ALL

SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL+1

FROM EMPLOYEES_ANCHOR A, EMPLOYEES R

WHERE A.EMPLOYEEID = R.REPORTSTO)

SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO FROM EMPLOYEES_ANCHOR GO

 

제공안하는듯.. 

SQL 전문가 가이드 pg.348

 

5. 뷰(View)

 

5-1). 뷰 생성(X)

CREATE VIEW V_PLAYER_TEAM AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, t.TEAM_NAME FROM PLAYER P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID

 

 

 

6. 그룹함수

6-1) ROLLUP (x) 인듯 

 

ROLLUP 적용 전

6-1).ROLLUP 적용 후

 

ast.value.groupBy.rollUp = "Dname"; 로 적용을 해도 

 

sql parser로 결과가 나오지 않음. 

 

 

6-2). ROLLUP, CASE 문 함께 사용 (X) 

 

SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'ALL DEPARTMENTS' ELSE DNAME END AS DNAME,

CASE GROUPING(JOB) WHEN 1 THEN 'ALL JOBS' ELSE JOB END AS JOB,

COUNT(*) 'TOTAL EMPL', SUM(SAL) 'TOTAL SAL'

FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB)

 

 

7). CUBE

 

SELECT DNAME, JOB, MGR, SUM(SAL)

FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY CUBE(DNAME, (JOB, MGR))

 

7). CUBE 사용

cube는 되고 rollup은 안되는 이유가 뭘까 .. 

 

우선 ast.value.groupBy.value[0].value.name = "rollup"; 이렇게 넣어주면 rollup도 되기는 함.

ast . value . groupBy . value [ 0 ]. value . name  =  "rollup" 결과

 

8). GROUPING SETS 함수 (x)

SELECT DNAME, JOB, MGR FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY GROUPING SETS((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR))

 

이것도 마찬가지 .. rollup, cube, grouping sets ..

 

 

7. Window 함수 

 

1). RANK 함수(X)

SELECT JOB, ENAME, SAL,

RANK() OVER (ORDER BY SAL DESC) ALL_RANK,

RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP

 

지원 안하는듯..

 

2). DENSE_RANK 함수(X)

SELECT JOB, ENAME, SAL, DENSE_RANK() OVER (ORDER BY SAL DESC) ALL_RANK, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP

 

3).ROW_NUMBER 함수(X)

SELECT JOB, ENAME, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER FROM EMP

 

4). SUM 함수 + PARTITION BY 구문 (X)

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTION BY MGR) MGR_SUM FROM EMP

 

5). FIRST_VALUE 함수(X)

FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구함

 

부서별 직원들을 연봉이 높은 순으로 정렬하고, 파니션 내 가장 먼저 나온 값을 출력

 

SELECT DEPTNO, ENAME, SAL,

FIRST_VALUE(ENAME) OVER (PARTION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH FROM EMP

 

6). RATIO_TO_REPORT(X)

SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (),2) AS R_R FROM EMP

 

7). PERCENT_RANK() (X)

SELECT ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS P_R FROM EMP

 

8). CUME_DIST (X)

SELECT ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST FROM EMP

 

9). NTILE (X)

SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE FROM EMP

 

 

 

8. PROCEDURE (X)

CREATE OR REPLACE PROCEDURE USER_PROC (

V_VALUE_1 IN VARCHAR2,

V_VALUE_2 IN VARCHAR2,

V_DEPTNO IN EMP.DEPTNO%TYPE ,

O_ERRORCODE OUT VARCHAR2,

O_ERRORMESG OUT VARCHAR2)

IS

V_ENAME VARCHAR2(8) := '';

 

BEGIN DBMS_OUTPUT.ENABLE;

 

SELECT ENAME

INTO V_ENAME FROM EMP WHERE ENAME LIKE 'A%';

 

DBMS_OUTPUT.PUT_LINE ('사원명은'||V_ENAME||' 입니다') ;

 

EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다') ;

WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('사원이 두 명 이상입니다') ;

WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE(' 0 으로 나눌 수 없습니다.') ;

WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE(' 동일한 데이터가 존재합니다.') ;

WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(' 기타오류') ; END;