[ 1번째 SQL BLOCK 참조 ]
V$SQLAREA.PROGRAM_ID 를 통한 SQL 확인 하기
가. 1차원 적인 호출은 확인된다.
EX) EXEC PROCEDURE('XXX') 이런 식의 호출을 정확하게 확인 된다.
나. 2차원 적인 호출은 역시나 확인된다.
( 패키지에서 트로시져를 호출 하는 형태)
[ 2번째 SQL BLOCK 참조 ]
덧) 추가로, 급하게 호출이 잦은프로시져를 컴파일 하려고,기존이름2로 컴파일 및 권한 작업 후에
Public Synonym 을 재생성 했다 ( 1초 소요 )
보통 Public Synomym 을 통해서 접근 하는 경우엔 기존이름2로 실제 수행 되나.
(나) 경우처럼 내부 호출 이고, 패키지, 프로시져 모두 OWNER 인 경우엔
Public Synonum 을 타지 않아서 의미가 없다. !!!
--------------------------------------------------------------------------------------------------------------------------
-- PROCDURE 호출 테스트
--------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE UPDATE_SAL
/* IN Parameter */
(v_empno IN NUMBER)
IS
BEGIN
UPDATE /*+ TG_UPDATE_FLAG */ emp
SET sal = sal * 1.1
WHERE empno = v_empno;
COMMIT;
END UPDATE_SAL;
/
EXEC UPDATE_SAL(7369 ) ;
SELECT SQL_ID, EXECUTIONS, FIRST_LOAD_TIME, SQL_TEXT
FROM GV$SQLAREA
WHERE PROGRAM_ID = ( SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OWNER ='SCOTT' AND OBJECT_NAME ='UPDATE_SAL');
SQL_ID EXECUTIONS FIRST_LOAD_TIME SQL_TEXT
------------- ----------- --------------------- -------------------------------------------------------------------------
fzkk5hamhmk64 1 2023-04-01/13:27:06 UPDATE /*+ TG_UPDATE_FLAG */ EMP SET SAL = SAL * 1.1 WHERE EMPNO = :B1
1 rows selected.
ROLLBACK ;
--> (결과) SQL -> PL/SQL -> PL/SQL 내 SQL 호출 확인 됨
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
-- PACKAGE - > PROCEDURE 호출 테스트
CREATE OR REPLACE PACKAGE EMP_INFO AS
PROCEDURE ALL_EMP_INFO; -- 모든 사원의 사원 정보
PROCEDURE ALL_SAL_INFO; -- 모든 사원의 급여 정보
-- 특정 부서의 사원 정보
PROCEDURE DEPT_EMP_INFO (V_DEPTNO IN NUMBER) ;
-- 특정 부서의 급여 정보
PROCEDURE DEPT_SAL_INFO (V_DEPTNO IN NUMBER) ;
END EMP_INFO;
--------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY EMP_INFO AS
-- 모든 사원의 사원 정보
PROCEDURE ALL_EMP_INFO
IS
CURSOR EMP_CURSOR IS
SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'RRRR/MM/DD') HIREDATE
FROM EMP
ORDER BY HIREDATE;
BEGIN
FOR AA IN EMP_CURSOR LOOP
DBMS_OUTPUT.PUT_LINE('사번 : ' || AA.EMPNO);
DBMS_OUTPUT.PUT_LINE('성명 : ' || AA.ENAME);
DBMS_OUTPUT.PUT_LINE('입사일 : ' || AA.HIREDATE);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
END ALL_EMP_INFO;
-- 모든 사원의 급여 정보
PROCEDURE ALL_SAL_INFO
IS
CURSOR EMP_CURSOR IS
SELECT ROUND(AVG(SAL),3) AVG_SAL, MAX(SAL) MAX_SAL, MIN(SAL) MIN_SAL
FROM EMP;
BEGIN
FOR AA IN EMP_CURSOR LOOP
DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || AA.AVG_SAL);
DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || AA.MAX_SAL);
DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || AA.MIN_SAL);
END LOOP;
-- FOR TEST HARD CODEING
UPDATE_SAL(7369 ) ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
END ALL_SAL_INFO;
--특정 부서의 사원 정보
PROCEDURE DEPT_EMP_INFO (V_DEPTNO IN NUMBER)
IS
CURSOR EMP_CURSOR IS
SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'RRRR/MM/DD') HIREDATE
FROM EMP
WHERE DEPTNO = V_DEPTNO
ORDER BY HIREDATE;
BEGIN
FOR AA IN EMP_CURSOR LOOP
DBMS_OUTPUT.PUT_LINE('사번 : ' || AA.EMPNO);
DBMS_OUTPUT.PUT_LINE('성명 : ' || AA.ENAME);
DBMS_OUTPUT.PUT_LINE('입사일 : ' || AA.HIREDATE);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
END DEPT_EMP_INFO;
--특정 부서의 급여 정보
PROCEDURE DEPT_SAL_INFO (V_DEPTNO IN NUMBER)
IS
CURSOR EMP_CURSOR IS
SELECT ROUND(AVG(SAL),3) AVG_SAL, MAX(SAL) MAX_SAL, MIN(SAL) MIN_SAL
FROM EMP
WHERE DEPTNO = V_DEPTNO;
BEGIN
FOR AA IN EMP_CURSOR LOOP
DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || AA.AVG_SAL);
DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || AA.MAX_SAL);
DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || AA.MIN_SAL);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
END DEPT_SAL_INFO;
END EMP_INFO;
EXEC EMP_INFO.ALL_SAL_INFO;
SQL> SELECT SQL_ID, EXECUTIONS, FIRST_LOAD_TIME, SQL_TEXT
FROM GV$SQLAREA
WHERE PROGRAM_ID = ( SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OWNER ='SCOTT' AND OBJECT_NAME ='UPDATE_SAL');
SQL_ID EXECUTIONS FIRST_LOAD_TIME SQL_TEXT
------------- ----------- --------------------- ------------------------------------------------------------------------
fzkk5hamhmk64 2 2023-04-01/13:27:06 UPDATE /*+ TG_UPDATE_FLAG */ EMP SET SAL = SAL * 1.1 WHERE EMPNO = :B1
--> (결과) 마찬가지로 호출 확인됨( 1 ->2 )
--------------------------------------------------------------------------------------------------------------------------
-- UPDATE_SAL -> UPDATE_SAL2 이름 변경
-- TG_UPDATE_FLAG -> TG_UPDATE_FLAG2 주석 변경
-- 주의 : PL/SQL 에선 주석 처리로 하면 ,나중에 SQL 확인 시, 주석 부분이 사라짐, 반드시 힌트 형식으로 기재해야함
CREATE OR REPLACE PROCEDURE UPDATE_SAL2
/* IN Parameter */
(v_empno IN NUMBER)
IS
BEGIN
UPDATE /*+ TG_UPDATE_FLAG2 */ emp
SET sal = sal * 1.1
WHERE empno = v_empno;
COMMIT;
END UPDATE_SAL2;
-- 의미 없는 Public Synonym 생성
CREATE PUBLIC SYNONYM UPDATE_SAL FOR UPDATE_SAL2 ;
-- 샘플로 수행
EXEC EMP_INFO.ALL_SAL_INFO;
-- 기존 소스를 그대로 호출 확인됨
SELECT SQL_ID, EXECUTIONS, FIRST_LOAD_TIME, SQL_TEXT
FROM GV$SQLAREA
WHERE PROGRAM_ID = ( SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OWNER ='SCOTT' AND OBJECT_NAME ='UPDATE_SAL');
SQL_ID EXECUTIONS FIRST_LOAD_TIME SQL_TEXT
------------- ----------- -------------------- -----------------------------------------------------------------------
fzkk5hamhmk64 3 2023-04-01/13:27:06 UPDATE /*+ TG_UPDATE_FLAG */ EMP SET SAL = SAL * 1.1 WHERE EMPNO = :B1
-- 혹시나 싶어 추가 확인
SQL> SELECT SQL_ID, EXECUTIONS, FIRST_LOAD_TIME, SQL_TEXT
FROM GV$SQLAREA
WHERE PROGRAM_ID = ( SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OWNER ='SCOTT' AND OBJECT_NAME ='UPDATE_SAL2');
SQL_ID EXECUTIONS FIRST_LOAD_TIME SQL_TEXT
------------- ----------- ------------------- ----------------