PLSQL,프로시저,함수,쿼리수행시간,set timing on,dbms_utility.get_time,systimestamp,오라클교육, SQL교육,자바교육,오라클학원,SQL학원,자바학원

 PLSQL,프로시저,함수,쿼리수행시간,set timing on,dbms_utility.get_time,systimestamp,오라클교육, SQL교육,자바교육,오라클학원,SQL학원,자바학원

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

PLSQL,프로시저,함수,쿼리수행시간,set timing on,dbms_utility.get_time,systimestamp,오라클교육, SQL교육,자바

PLSQL,프로시저,함수,쿼리수행시간,set timing on,dbms_utility.get_time,systimestamp,오라클교육, SQL교육,자바교육,오라클학원,SQL학원,자바학원오라클 PL/SQL프로시저, 함수, 쿼리 수행시간set timing ondbms_utility.g

ojc.asia

https://www.youtube.com/watch?v=YzJK7u7sf6U&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=45 

https://www.youtube.com/watch?v=6Dj4un2o9bQ&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=44 

https://www.youtube.com/watch?v=0CYrvAWrBjk&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=43

https://www.youtube.com/watch?v=XJOkRPZYSYo&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=42 

프로시저, 함수, 쿼리 수행시간

set timing on

dbms_utility.get_time

systimestamp

[실습 : 프로시저를 만들어서 이를 호출하고 수행시간을 측정하고 출력]

create or replace procedure getCount (p_deptno in number) is         v_count number; begin     select count(1)     into   v_count     from   myemp1     group  by deptno     having  deptno = p_deptno;          dbms_output.put_line(' count : ' || v_count); end; set serveroutput on declare     v_start number;     v_end   number;     v_sec   number; begin     v_start := dbms_utility.get_time;     getCount('1');     v_end := dbms_utility.get_time;          v_sec := ( v_end - v_start ) /100;          dbms_output.put_line(' sec : ' || v_sec); end; count : 5000001  sec : 1.53 set timing on declare     v_start timestamp;     v_end   timestamp;     v_sec   varchar2(50); begin     -- systimestamp : 년월일시분초 밀리세컨드 까지 표현      v_start := systimestamp;     getCount('1');     v_end := systimestamp;          v_sec := to_char( v_end  - v_start);          dbms_output.put_line(' v_sec : ' || v_sec); end; count : 5000001  v_sec : +000000000 00:00:01.418000000

#PLSQL,#프로시저,#함수,#쿼리수행시간,#settimingon,#dbms_utility.get_time,#systimestamp,#오라클교육, #SQL교육,#자바교육,#오라클학원,#SQL학원,#자바학원,PLSQL,프로시저,함수,쿼리수행시간,settimingon,dbms_utility.get_time,systimestamp,오라클교육, SQL교육,자바교육,오라클학원,SQL학원,자바학원

오라클PLSQL, 저장 함수(Stored Function)

오라클PLSQL, 저장 함수(Stored Function)

15.2. 저장 함수(Stored Function)

자신을 호출한 곳으로 반드시 하나의 값을 리턴해 줘야되는 PL/SQL Stored Program 입니다. 함수 자체가 오라클 서버에 저장 되므로 Stored Function or User Function or User-Defined Function 이라고 합니다. 

자신의 스키마 계정에 함수를 작성하려면 CREATE PROCEDURE 시스템 권한이 있어야 하며 다른 사용자 계정에서 함수를 만들려면 CREATE ANY PROCEDURE 시스템 권한이 있어야 합니다.

함수를 정의할 때  Header에 리턴되는 데이터 타입을 기술하고 Header의 끝에는 IS[AS]가

와야하며 IS[AS]와 BEGIN사이에 BEGIN ~  END에서 사용할 변수를 선언 합니다.

<기본형식>

CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_type  IS[or AS]      declaration_section  BEGIN      execution_section     RETURN return_variable;  EXCEPTION       exception_section       RETURN exception_return_variable;  END [function_name]; / function_name : 생성하고자 하는 함수의 이름. parameters: 함수에 전달할 파라미터 목록. return_type :함수가 리턴할 값의 데이터 타입. declaration_section : 변수나 커서, 타입등을 선언 하는 영역 execution_section : 실제 비지니스 로직처리, PL/SQL 제어문, 반복문, DML등 RETURN return_variable : 호출한 곳으로 값을 리턴. exception_section : 오류가 발생했을 때의 처리. RETURN exception_return_variable : 오류가 발생했을때 리턴하는 값 / : SQL*Plus에서 실행기호. SQL*Plus에서 함수를 생성하는 경우에는 반드시 기술해야 합니다.

저장 함수를 만들고 호출하는 실습을 해보겠습니다.

15.2.1. 저장 함수 작성 실습 : CREATE OR REPLACE FUNCTION

EMP 테이블에서 사번을 인자로 입력받아 해당 사원의 이름을 리턴하는 저장 함수를 F_GET_ENAME 이라는 이름으로 작성하세요.

-- 이미 F_GET_ENAME 함수가 있다면 대체 합니다. 리턴 타입은 VARCHAR2 입니다. -- 함수를 호출할 때는 숫자형으로 사번을 던져줘야 합니다. CREATE OR REPLACE  FUNCTION F_GET_ENAME(P_EMPNO  IN  NUMBER)           RETURN  VARCHAR2 IS            /* IS ~ BEGIN사이는 이름없는 블럭을 만들때의 DECLARE ~ BEGIN과     동일하게 변수를 선언하는 선언부 입니다.    V_EANE 변수는 EMP 테이블의 ENAME과 타입이 같습니다. */    V_ENAME  EMP.ENAME%TYPE; BEGIN       /* BEGIN ~ END 사이의 SELECT절은 반드시 한건만 SELECT 되야 하며        INTO절이 반드시 필요합니다.       WHERE절의 사원이 없다면 EXCEPTION절의        WHEN NO_DATA_FOUND THEN 절로 이동합니다. */       SELECT   ENAME       INTO        V_ENAME       FROM      EMP       WHERE   EMPNO  =  P_EMPNO;              -- V_ENAME 변수값을 리턴 합니다.       RETURN V_ENAME; -- 예외처리 절 입니다. EXCEPTION       /* NO_DATA_FOUND는 PL/SQL에 사전에 정의되어 있는 예외 입니다.                    ORA-01403 오류 코드이며  SELECT시 데이터를 찾을 수 없을때 발생하는 예외 입니다.        WHEN 절에서 일일이 예외를 처리하기가 번거러우면         모든 예외를 다 처리할 수 있는 WHEN OTHERS THEN만 사용해도 무방 합니다. */       WHEN NO_DATA_FOUND THEN          DBMS_OUTPUT.PUT_LINE('해당 사원은 없습니다.');          WHEN OTHERS THEN          -- 기타 오류인 경우 SQL 에러코드와 에러 메시지를 출력 합니다.          DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM); END F_GET_ENAME;

<실행결과>

Function F_GET_ENME이(가) 컴파일되었습니다.

SQL Developer에서 실행할 경우 END F_GET_ENME; 다음에 슬래시(‘/’)를 하지 않아도 실행 되지만 SQL*Plus에서 실행을 하는 경우에는 반드시 ‘/’를 뒷부분에 표시 해야만 실행이 됩니다. 아래 그림을 참조하세요.

작성한 함수를 SELECT절에서 호출하는 실습 입니다.

F_GET_ENME 함수를 SELECT 쿼리에서 호출 하세요.

SELECT ❶F_GET_ENAME(7369) FROM DUAL;

<실행결과>

	F_GET_ENAME(7369)
1	SMITH

❶ 오라클에서 제공하는 수많은 함수를 SELECT 쿼리에서 사용하듯이 우리가 작성한 함수도 같은 형태로 사용 가능 합니다.

SELECT F_GET_ENAME(7777) FROM DUAL;

<실행결과>

ORA-06503: PL/SQL: 함수가 값을 리턴하지 못했습니다

해당 사원은 없습니다.

작성한 함수를 EXECUTE 명령으로 호출하는 실습 입니다. 아래 실습의 실행은 SQL*Deveoper 또는 SQL*PLUS 모두 가능 합니다.

F_GET_ENME 함수를 EXECUTE 명령으로 호출 하세요.

-- 변수를 선언 합니다. 이렇게 선언한 변수를 참조할 때는 앞에 ‘:’을 표시해야 합니다. VARIABLE ENAME VARCHAR2(50); -- F_GET_ENAME 함수를 호출하면서 7369 사번을 인자로 전달하고  -- EXEC[UTE] 명령으로 함수를 호출하고 그 결과를 ENAME 변수로 받습니다. -- ENAME 변수는 반드시 앞에 콜론(‘:’)을 기술해야 합니다. EXEC :ENAME := F_GET_ENAME(7369); -- ENAME 변수를 출력합니다. PRINT ENAME;

<실행결과>

ENAME

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

SMITH