PLSQL, 동적SQL, Dynamic SQL, 커서OPEN FO에서 Dynamaic SQL사용하기, 참조커서, 오라클교육, SQL교육, 자바교육, SQL학원, 오라클학원, 자바학원

 

PLSQL, 동적SQL, Dynamic SQL, 커서OPEN FO에서 Dynamaic SQL사용하기, 참조커서, 오라클교육, SQL교육, 자바교육, SQL학원, 오라클학원, 자바학원

https://www.youtube.com/watch?v=upkNZLX6CV4&list=PLxU-iZCqT52CE6piTNJMXGGUvDvKw46_T&index=12 

https://www.youtube.com/watch?v=8hc030THr8w&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=16 

https://youtu.be/OCv-7nsq1w0

Native Dynamic SQL

OPEN FOR, FETCH, CLOSE 

강의 : 이종철

동적SQL(Dynamic SQL)이 필요한 이유?

DDL(데이터베이스 정의 언어) 문을 실행해야 하는 프로그램을 작성할 때, 변수를 이용하여 SQL문장을 동적으로 만들어 낼때, 컴파일타임에 존재하지 않는 DB Object를 참조하는 경우(테이블의 이름을 동적으로 생성), PL/SQL블록에서 다른 프로시저등을 호출하는 경우에 사용합니다.

이번 강좌에서는 Dynamic SQL 문이 여러 행을 반환하는 SELECT 문을 나타내는 경우를 실습하는데  다음과 같이 Native  Dynamic SQL로 처리할 수 있습니다.

OPEN FOR문을 사용하여 커서 변수를 Dynamic SQL문과 연결하는데, OPEN FOR 문의 USING 절에서 동적 SQL 문의 각 자리 표시자(:)에 대한 바인드 변수를 지정합니다.

이번 강좌에서는 Native Dynamic SQL의 형태로 커서를 사용하면서 동적 SQL문을 작성해 보겠습니다.

DECLARE   -- 참조커서 타입 정의   TYPE EmpCurTyp  IS REF CURSOR;   v_emp_cur       EmpCurTyp;   v_emp_record    emp%ROWTYPE;   v_sql_text      VARCHAR2(200);   v_deptno        emp.deptno%TYPE; BEGIN   -- 자리표시자와 함께  Dynamic SQL 구문을 작성 합니다.   v_sql_text := 'SELECT * FROM EMP WHERE DEPTNO = :1';   -- 커서를 오픈하고 USING구에서 :1 바인드 변수 값을 지정 합니다.   OPEN v_emp_cur FOR v_sql_text USING 20;   -- 커서에서 한건씩 레코드를 FETCH 합니다.   LOOP     FETCH v_emp_cur INTO v_emp_record;     EXIT WHEN v_emp_cur%NOTFOUND;     DBMS_OUTPUT.PUT_LINE('EMPNO : ' || v_emp_record.empno || ',' || 'ENAME : ' || v_emp_record.ename);   END LOOP;   -- Close cursor:   CLOSE v_emp_cur; END; /

#PLSQL, #동적SQL, #DynamicSQL, #커서, #SQL, #참조커서, #오라클교육, #SQL교육, #자바교육, #SQL학원, #오라클학원, #자바학원, PLSQL, 동적SQL, DynamicSQL, 커서, SQL, 참조커서, 오라클교육, SQL교육, 자바교육, SQL학원, 오라클학원, 자바학원

오라클, SQL, 칼럼의 값이 유일한지 확인하는 쿼리, 부서별로 JOB의 개수를 세는 쿼리

오라클, SQL, 칼럼의 값이 유일한지 확인하는 쿼리, 부서별로 JOB의 개수를 세는 쿼리

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=630

 

오라클, SQL, 칼럼의 값이 유일한지 확인하는 쿼리, 부서별로 JOB의 개수를 세는 쿼리

오라클, SQL, 칼럼의 값이 유일한지 확인하는 쿼리, 부서별로 JOB의 개수를 세는 쿼리COUNT, SUM, DECODE, GROUP BY 활용 이번에는 칼럼의 값이 유일한지 확인하는 쿼리문을 생각해 보자. -- deptno별로 값의 수를 count 후, 최대인것을 골라 0이면 Unique, 아니면 Non Unique를 출력한다. SQL> selec…

ojc.asia

COUNT, SUM, DECODE, GROUP BY 활용

이번에는 칼럼의 값이 유일한지 확인하는 쿼리문을 생각해 보자.

-- deptno별로 값의 수를 count 후, 최대인것을 골라 0이면 Unique, 아니면 Non Unique를 출력한다. SQL> select decode(max(count(deptno)),1,'Unique','Non Unique') "Unique ?" from emp group by deptno ; Unique ? ---------- Non Unique

EMP테이블에서 JOB별, DEPTNO별로 급여의 급여를 합계를 아래처럼 구하는 Matrix Report를 생각해 보자.

JOB 10번부서 20번부서 30번부서 40번부서

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

ANALYST 6000

CLERK 1300 1900 950

MANAGER 2450 2975 2850

PRESIDENT 5000

SALESMAN 5600

-- 첫번째 칼럼이 job이 므로 먼저 job별로 group by를 하고, 나머지열은 하나씩 sum, decode를 이용해서 만들어 나가면 된다. SQL> select * from ( select job, sum(decode(deptno, 10, sal)) "10번부서", sum(decode(deptno, 20, sal)) "20번부서", sum(decode(deptno, 30, sal)) "30번부서", sum(decode(deptno, 40, sal)) "40번부서" from emp group by job ) order by job ; JOB 10번부서 20번부서 30번부서 40번부서 --------- ---------- ---------- ---------- ---------- ANALYST 6000 CLERK 1300 1900 950 MANAGER 2450 2975 2850 PRESIDENT 5000 SALESMAN 5600

이번에는 EMP 테이블에서 부서별로 JOB의 개수를 세는 쿼리를 생각해 보자. 이 역시 부서별이니 deptno로 group by를 해야 하며 sum, decode를 이용하여 나머지 job들의 칼럼을 만들어 내야 한다.

SQL> select deptno "부서코드" , sum(decode(job, 'CLERK', 1, 0)) Clerk , sum(decode(job, 'SALESMAN', 1, 0)) Salesman , sum(decode(job, 'MANAGER', 1, 0)) Manager , sum(decode(job, 'ANALYST', 1, 0)) Analyst , sum(decode(job, 'PRESIDENT', 1, 0)) President FROM emp e GROUP BY deptno; 부서코드 CLERK SALESMAN MANAGER ANALYST PRESIDENT ---------- ---------- ---------- ---------- ---------- ---------- 30 1 4 1 0 0 20 2 0 1 2 0 10 1 0 1 0 1

이번에는 EMP 테이블에서 부서별로 해당 급여 대 인원수를 추출하는 SQL문을 생각해 보자.

부서 $4001-$9999 $3001-$4000 $2001-$3000 $1001-$2000 < $1000

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

30 0 0 1 4 1

20 0 0 3 1 1

10 1 0 1 1 0

SQL> select deptno "부서", sum(decode(greatest(SAL,4001), least(SAL,9999), 1, 0)) "$4001-$9999", sum(decode(greatest(SAL,3001), least(SAL,4000), 1, 0)) "$3001-$4000", sum(decode(greatest(SAL,2001), least(SAL,3000), 1, 0)) "$2001-$3000", sum(decode(greatest(SAL,1001), least(SAL, 2000), 1, 0)) "$1001-$2000", sum(decode(greatest(SAL, 0), least(SAL, 1000), 1, 0)) "< $1000" from emp group by deptno; 부서 $4001-$9999 $3001-$4000 $2001-$3000 $1001-$2000 < $1000 ---------- ----------- ----------- ----------- ----------- ---------- 30 0 0 1 4 1 20 0 0 3 1 1 10 1 0 1 1 0 CASE문을 이용하면 다음과 같다. SELECT deptno "부서", NVL(SUM((case when sal between 4001 and 9999 then 1 end)),0) "$4001-$9999" , NVL(SUM((case when sal between 3001 and 4000 then 1 end)),0) "$3001-4000" , NVL(SUM((case when sal between 2001 and 3000 then 1 end)),0) "$2001-$3000" , NVL(SUM((case when sal between 1001 and 2000 then 1 end)),0) "$1001-$2000" , NVL( SUM((case when sal between 1 and 1000 then 1 end)),0) "$1-$1000" FROM emp GROUP BY deptno

​