DuckDevelope

SELECT 문은 가장 기본이면서 가장 중요한 구문이다.

SELECT문이란 데이터베이스 내의 테이블에서 원하는 정보를 추출하는 명령

WITH <Sub Query>

SELECT select_list

FROM table_source WHERE search_condition

GROUP BY group_by_expression

HAVING search_condition

ORDER BY order_expression ASC : DESC

SELECT 열이름

FROM 테이블이름

WHERE 조건

기본개념)

 - 자신을 호출한 곳으로 리턴 해주는 값은 없고 호출되어 실행만 된다.

 - 실행환경과 Stored Program 사이에 값을 전달하기 위해 파라미터를 사용한다.

 - 파라미터 종류

1. IN : 호출하는 곳에서 함수 or 프로시저로 값을 전달

2. OUT : 함수 or 프로시저에서 호출한 곳으로 값을 전달 // 빈 깡통을 생각하면 쉽다. 비어이는 깡통에 결과값이 담긴다.

3. IN OUT : 호출하는 곳에서 함수 or 프로시저로 값을 전달하고 동시에 함수 or 프로시저에서 호출한 곳으로 값을 전달하는 변수

 - 프로시저 Hearder의 끝에는 IS[AS]가 와야하고 IS와 BEGIN사이에 BEGIN~END에서 사용할 변수를 선언

 - 자신의 스키마에서 프로시저를 만들기 위해서는 CREATE PROCEDURE 시스템권한이 있어야 하며,

   다른 스키마 계정에서 프로시저를 만들기 위해서는 CREATE ANY PROCEDURE 시스템 권한이 있어야 한다.

기본형식)

CREATE [OR REPLACE] PROCEDURE procedure_name[parameters]

IS[AS]

선언부

BEGIN

도입부

EXCEPTION

예외처리

END;

예제)

CREATE OR REPLACE PROCEDURE ojc3 (p_empno IN NUMBER, p_new_sal IN NUMBER) // 외부에서 값을 넣어주어야 한다.

IS 

BEGIN

UPDATE emp

SET sal = p_new_sal

WHERE empno = p_empno;

COMMIT;

END ojc3;

SELECT empnp, ename, sal FROM emp WHERE empno = 7369;

EMPNO  ENAME  SAL

7369      smith    800

호출)

exec ojc3(7369, 4500);

SELECT empnp, ename, sal FROM emp WHERE empno = 7369;

EMPNO  ENAME  SAL

7369      smith    4500

기본개념)

 - 자신을 호출한 곳으로 반드시 하나의 값을 리턴해 줘야되는 PL/SQL Stored Program이다.

 - 프로시저와 함수의 가장 큰 차이는 값을 리턴하느냐 마느냐

 - 자신의 스키마 계정에 함수를 작성하려면 CREATE PROCEDURE 시스템 권한이 필요

 - 다른 사용자 계정에 함수를 작성하려면 CREATE ANY PROCEDURE 시스템 권한이 필요

 - 함수 Header에 리턴되는 데이터 타입을 기술해야 하며, Header의 끝에는 IS[AS]가 와야하고, IS[AS]와 BEGIN사이에 Begin~End에서 사용할 변수를 선언

기본 형식)

CREATE [OR REPLACE] FUNCTION function_name[parameters]

RETURN 리턴되는 데이터 타입

IS[AS]

선언부

BEGIN

실행부

EXCEPTION

예외처리

END;

예제)

CREATE OR REPLACE FUNCTION ojc2(p_empno IN NUMBER) // 외부에서 ojc2라는 함수를 호출하면서 파라메터를 넣어줘야 한다.

RETURN VARCHAR2 // 리턴되는 타입 가변길이 문자열 형태

IS

v_ename emp.ename%TYPE; // emp테이블의 ename컬럼과 타입을 같게해라. 동적타입

BEGIN

SELECT ename

INTO v_ename // 반드시 INTO절이 있어야 한다.

FROM emp

WHERE empno = p_empno;

RETURN v_ename;

END ojc2;

실행예시)

SELECT ojc2(7000) FROM DUAL; // SELECT문에는 반드시 테이블 명이 필요함 임시 테이블 사용

CREATE TABLE EMP_TEMP AS SELECT EMPNO, SAL, ENAME FROM EMP WHERE 1=2;

INSERT INTO EMP_TEMP VALUES(7788, 9999, ojc2(7788));

결과값)

EMPNO   SAL   ENAME

7788       9999  (7788의 문자열)

==================================================================================================

CREATE OR REPALCE FUNCTION "함수명"(

"파라미터" 데이터타입, ( EX : FUNC_V_NAME (IN) VARCHAR2 )

"파라미터" 데이터타입

)

RETURN 데이터타입( VARCHAR2 )INTO

"변수이름"( V_TEST_NAME ) 데이터타입( VARCHAR2(300) ) ;

BEGIN

V_TEST_NAME := 'HELLOWORLD'; ( =앞에 ':'가 들어간다. )

SELECT testName

INTO V_TEST_NAME

FROM TEST_TABLE

WHERE funcVname = FUNC_V_NAME;

RETURN V_TEST_NAME;

END;

사용예)

SELECT "함수명" FROM DUAL;