Oracle PL/SQL 저장 프로시저 및 함수와 예제

작성자 : 피오나 브라운 피오나 브라운
Hours 업데이트

이 튜토리얼에서는 명명된 블록(프로시저 및 함수)을 생성하고 실행하는 방법에 대한 자세한 설명을 볼 수 있습니다.

프로시저와 함수는 데이터베이스 개체로 데이터베이스에 생성되고 저장될 수 있는 하위 프로그램입니다. 다른 블록 내에서도 호출하거나 참조할 수 있습니다.

이 외에도 이 두 하위 프로그램 간의 주요 차이점을 다룰 것입니다. 또한, 우리는 다음에 대해 논의할 예정이다. Oracle 내장된 기능.

PL/SQL 서브프로그램의 용어

PL/SQL 서브 프로그램에 대해 배우기 전에 이러한 서브 프로그램의 일부인 다양한 용어에 대해 논의하겠습니다. 다음은 우리가 논의할 용어입니다.

매개 변수

매개변수는 유효한 변수 또는 자리 표시자입니다. PL/SQL 데이터 유형 이를 통해 PL/SQL 하위 프로그램은 기본 코드와 값을 교환합니다. 이 매개변수를 사용하면 하위 프로그램에 입력을 제공하고 이러한 하위 프로그램에서 추출할 수 있습니다.

  • 이러한 매개변수는 생성 시 서브프로그램과 함께 정의되어야 합니다.
  • 이러한 매개변수는 해당 서브프로그램의 호출 문에 포함되어 서브프로그램과 값을 상호 작용합니다.
  • 서브프로그램에 있는 매개변수의 데이터 유형과 호출 명령문은 동일해야 합니다.
  • 이 유형의 크기는 동적이므로 매개변수 선언 시 데이터 유형의 크기를 언급해서는 안 됩니다.

목적에 따라 매개변수는 다음과 같이 분류됩니다.

  1. IN 매개변수
  2. 아웃 매개변수
  3. IN OUT 매개변수

IN 매개변수

  • 이 매개변수는 서브프로그램에 입력을 제공하는 데 사용됩니다.
  • 서브프로그램 내부의 읽기 전용 변수입니다. 해당 값은 서브프로그램 내에서 변경할 수 없습니다.
  • 호출 명령문에서 이러한 매개변수는 변수, 리터럴 값 또는 표현식일 수 있습니다. 예를 들어 '5*8' 또는 'a/b'와 같은 산술 표현식일 수 있습니다. 여기서 'a' 및 'b'는 변수입니다. .
  • 기본적으로 매개변수는 IN 유형입니다.

아웃 매개변수

  • 이 매개변수는 서브프로그램에서 출력을 얻는 데 사용됩니다.
  • 서브프로그램 내부의 읽기-쓰기 변수입니다. 해당 값은 서브프로그램 내에서 변경될 수 있습니다.
  • 호출 문에서 이러한 매개변수는 항상 현재 하위 프로그램의 값을 보유하는 변수여야 합니다.

IN OUT 매개변수

  • 이 매개변수는 서브프로그램에서 입력을 제공하고 출력을 얻는 데 사용됩니다.
  • 서브프로그램 내부의 읽기-쓰기 변수입니다. 해당 값은 서브프로그램 내에서 변경될 수 있습니다.
  • 호출 명령문에서 이러한 매개변수는 항상 서브프로그램의 값을 보유하는 변수여야 합니다.

이러한 매개변수 유형은 서브프로그램을 생성할 때 언급되어야 합니다.

RETURN

RETURN은 서브프로그램에서 호출 명령문으로 제어를 전환하도록 컴파일러에 지시하는 키워드입니다. 서브프로그램에서 RETURN은 단순히 제어장치가 서브프로그램을 종료해야 함을 의미합니다. 컨트롤러가 서브프로그램에서 RETURN 키워드를 찾으면 이 이후의 코드는 건너뜁니다.

일반적으로 부모 또는 메인 블록은 서브프로그램을 호출하고, 그런 다음 제어는 부모 블록에서 호출된 서브프로그램으로 이동합니다. 서브프로그램의 RETURN은 제어를 다시 부모 블록으로 반환합니다. 함수의 경우 RETURN 문도 값을 반환합니다. 이 값의 데이터 유형은 항상 함수 선언 시 언급됩니다. 데이터 유형은 모든 유효한 PL/SQL 데이터 유형일 수 있습니다.

PL/SQL의 프로시저란 무엇입니까?

A 순서 PL/SQL에서 이름으로 호출할 수 있는 PL/SQL 문 그룹으로 구성된 하위 프로그램 단위입니다. PL/SQL의 각 프로시저에는 참조하고 호출할 수 있는 고유한 이름이 있습니다. 이 하위 프로그램 단위는 Oracle 데이터베이스는 데이터베이스 객체로 저장됩니다.

참고 : 서브프로그램은 프로시저일 뿐이며 요구사항에 따라 수동으로 생성해야 합니다. 일단 생성되면 데이터베이스 개체로 저장됩니다.

PL/SQL의 프로시저 서브 프로그램 단위의 특징은 다음과 같습니다.

  • 프로시저는 다음 위치에 저장될 수 있는 독립 실행형 프로그램 블록입니다. 데이터베이스.
  • PL/SQL 문을 실행하기 위해 해당 이름을 참조하여 이러한 PLSQL 프로시저를 호출할 수 있습니다.
  • 주로 PL/SQL에서 프로세스를 실행하는 데 사용됩니다.
  • 중첩된 블록을 가질 수도 있고, 정의되어 다른 블록이나 패키지 내에 중첩될 수도 있습니다.
  • 선언 부분(선택), 실행 부분, 예외 처리 부분(선택)으로 구성됩니다.
  • 값은 다음으로 전달될 수 있습니다. Oracle 프로시저를 사용하거나 매개변수를 통해 프로시저에서 가져옵니다.
  • 이러한 매개변수는 호출 문에 포함되어야 합니다.
  • SQL의 프로시저에는 호출 블록에 제어를 반환하는 RETURN 문이 있을 수 있지만 RETURN 문을 통해 어떤 값도 반환할 수는 없습니다.
  • 프로시저는 SELECT 문에서 직접 호출할 수 없습니다. 다른 블록이나 EXEC 키워드를 통해 호출할 수 있습니다.

통사론

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE는 컴파일러에게 새로운 프로시저를 생성하도록 지시합니다. Oracle. 'OR REPLACE' 키워드는 기존 프로시저(있는 경우)를 현재 프로시저로 바꾸도록 컴파일에 지시합니다.
  • 프로시저 이름은 고유해야 합니다.
  • 저장 프로시저가 실행될 때 키워드 'IS'가 사용됩니다. Oracle 다른 블록에 중첩되어 있습니다. 프로시저가 독립형인 경우 'AS'가 사용됩니다. 이 코딩 표준 외에는 둘 다 동일한 의미를 갖습니다.

예제1: 프로시저 생성 및 EXEC를 사용하여 호출

이 예에서는 Oracle 이름을 입력으로 사용하고 환영 메시지를 출력으로 인쇄하는 프로시저입니다. EXEC 명령을 사용하여 프로시저를 호출하겠습니다.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) 
IS
BEGIN
dbms_output.put_line (‘Welcome '|| p_name);
END;
/
EXEC welcome_msg (‘Guru99’);

코드 설명 :

  • 코드 라인 1: 'welcome_msg'라는 이름과 'IN' 유형의 'p_name' 매개변수 하나를 사용하여 프로시저를 생성합니다.
  • 코드 라인 4: 입력된 이름을 연결하여 환영 메시지를 인쇄합니다.
  • 프로시저가 성공적으로 컴파일되었습니다.
  • 코드 라인 7: 'Guru99' 매개변수와 함께 EXEC 명령을 사용하여 프로시저를 호출합니다. 절차가 실행되고 “Welcome Guru99”라는 메시지가 출력됩니다.

관련 기사

기능이란 무엇입니까?

Functions는 독립형 PL/SQL 하위 프로그램입니다. PL/SQL 프로시저와 마찬가지로 함수에는 참조할 수 있는 고유한 이름이 있습니다. 이는 PL/SQL 데이터베이스 객체로 저장됩니다. 다음은 함수의 몇 가지 특징입니다.

  • 함수는 주로 계산 목적으로 사용되는 독립형 블록입니다.
  • 함수는 RETURN 키워드를 사용하여 값을 반환하며, 이에 대한 데이터 유형은 생성 시 정의됩니다.
  • 함수는 값을 반환하거나 예외를 발생시켜야 합니다. 즉, 함수에서는 반환이 필수입니다.
  • DML 문이 없는 함수는 SELECT 쿼리에서 직접 호출할 수 있는 반면, DML 작업이 있는 함수는 다른 PL/SQL 블록에서만 호출할 수 있습니다.
  • 중첩된 블록을 가질 수도 있고, 정의되어 다른 블록이나 패키지 내에 중첩될 수도 있습니다.
  • 선언 부분(선택), 실행 부분, 예외 처리 부분(선택)으로 구성됩니다.
  • 값은 함수에 전달되거나 매개변수를 통해 프로시저에서 가져올 수 있습니다.
  • 이러한 매개변수는 호출 문에 포함되어야 합니다.
  • PLSQL 함수는 RETURN을 사용하는 것 외에 OUT 매개변수를 통해 값을 반환할 수도 있습니다.
  • 항상 값을 반환하므로 호출문에서는 항상 할당 연산자를 사용하여 변수를 채웁니다.

PL/SQL의 함수

통사론

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END;
  • CREATE FUNCTION은 컴파일러에게 새 함수를 생성하도록 지시합니다. 'OR REPLACE' 키워드는 컴파일러에게 기존 함수(있는 경우)를 현재 함수로 바꾸도록 지시합니다.
  • 함수 이름은 고유해야 합니다.
  • RETURN 데이터 유형이 언급되어야 합니다.
  • 프로시저가 다른 블록에 중첩될 때 키워드 'IS'가 사용됩니다. 프로시저가 독립형인 경우 'AS'가 사용됩니다. 이 코딩 표준 외에는 둘 다 동일한 의미를 갖습니다.

예1: 함수 생성 및 익명 블록을 사용하여 호출

이 프로그램에서는 이름을 입력으로 사용하고 환영 메시지를 출력으로 반환하는 함수를 만들 것입니다. 익명 블록과 select 문을 사용하여 함수를 호출하겠습니다.

PL/SQL의 함수

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
IS
BEGIN
RETURN (‘Welcome ‘|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func (‘Guru99’);
dbms_output.put_line(lv_msg);
END;
SELECT welcome_msg_func(‘Guru99:) FROM DUAL;

코드 설명 :

  • 코드 라인 1: 만들기 Oracle 이름이 'welcome_msg_func'이고 매개변수 'p_name'이 'IN' 유형인 함수입니다.
  • 코드 라인 2: 반환 유형을 VARCHAR2로 선언
  • 코드 라인 5: 'Welcome' 값과 매개변수 값을 연결하여 반환합니다.
  • 코드 라인 8: 위 함수를 호출하기 위한 익명 블록입니다.
  • 코드 라인 9: 함수의 반환 데이터 유형과 동일한 데이터 유형으로 변수를 선언합니다.
  • 코드 라인 11: 함수를 호출하고 반환 값을 'lv_msg' 변수에 채웁니다.
  • 코드 라인 12: 변수 값을 인쇄합니다. 여기서 얻을 수 있는 출력은 "Welcome Guru99"입니다.
  • 코드 라인 14: SELECT 문을 통해 동일한 함수를 호출합니다. 반환 값은 표준 출력으로 직접 전달됩니다.

절차와 기능의 유사점

  • 둘 다 다른 PL/SQL 블록에서 호출할 수 있습니다.
  • 서브 프로그램에서 발생한 예외가 서브 프로그램에서 처리되지 않는 경우 예외 처리 섹션을 호출하면 호출 블록으로 전파됩니다.
  • 둘 다 필요한 만큼 많은 매개변수를 가질 수 있습니다.
  • 둘 다 PL/SQL에서는 데이터베이스 객체로 처리됩니다.

절차 대. 기능: 주요 차이점

순서 함수
주로 특정 프로세스를 실행하는 데 사용됩니다. 주로 일부 계산을 수행하는 데 사용됩니다.
SELECT 문에서 호출할 수 없습니다. DML 문이 포함되지 않은 함수는 SELECT 문에서 호출할 수 있습니다.
OUT 매개변수를 사용하여 값을 반환합니다. 값을 반환하려면 RETURN을 사용하세요.
값을 반환하는 것이 필수는 아닙니다. 값을 반드시 반환해야 합니다.
RETURN은 단순히 서브프로그램의 제어를 종료합니다. RETURN은 서브프로그램의 제어를 종료하고 값도 반환합니다.
반환 데이터 유형은 생성 시 지정되지 않습니다. 생성 시 반환 데이터 유형은 필수입니다.

PL/SQL의 내장 함수

PL / SQL 문자열 및 날짜 데이터 유형으로 작업할 수 있는 다양한 내장 함수가 포함되어 있습니다. 여기서는 일반적으로 사용되는 기능과 사용법을 살펴보겠습니다.

변환 기능

이러한 내장 함수는 한 데이터 유형을 다른 데이터 유형으로 변환하는 데 사용됩니다.

기능 명 용법 예시
TO_CHAR 다른 데이터 유형을 문자 데이터 유형으로 변환합니다. TO_CHAR(123);
TO_DATE(문자열, 형식) 주어진 문자열을 날짜로 변환합니다. 문자열은 형식과 일치해야 합니다.

TO_DATE('2015-JAN-15', 'YYYY-MON-DD');

산출: 1 / 15 / 2015
TO_NUMBER(텍스트, 형식)

텍스트를 주어진 형식의 숫자 유형으로 변환합니다.

정보 '9'는 자릿수를 나타냅니다.

 듀얼에서 TO_NUMBER('1234′,'9999')를 선택하세요.

산출: 1234

듀얼에서 TO_NUMBER('1,234.45′,'9,999.99')를 선택하세요.

산출: 1234

문자열 함수

이는 문자 데이터 유형에 사용되는 함수입니다.

기능 명 용법 예시
INSTR(텍스트, 문자열, 시작, 발생) 주어진 문자열에서 특정 텍스트의 위치를 ​​제공합니다.

  • 텍스트 – 기본 문자열
  • 문자열 - 검색해야 할 텍스트
  • start - 검색 시작 위치(선택 사항)
  • 일치 – 검색된 문자열의 발생(선택 사항)
 이중에서 INSTR('AEROPLANE','E',2,1)을 선택합니다.

산출: 2

이중에서 INSTR('AEROPLANE','E',2,2)을 선택합니다.

산출: 9(2nd E)의 발생
SUBSTR(텍스트, 시작, 길이) 기본 문자열의 하위 문자열 값을 제공합니다.

  • 텍스트 - 기본 문자열
  • 시작 - 시작 위치
  • length – 하위 문자열로 묶일 길이
 이중에서 substr('aeroplane',1,7) 을 선택하십시오.

산출: 에어로플라
UPPER( 텍스트 ) 제공된 텍스트의 대문자를 반환합니다. 듀얼에서 upper('guru99')를 선택합니다.

산출: 구루99
하단( 텍스트 ) 제공된 텍스트의 소문자를 반환합니다. 듀얼에서 낮은('AerOpLane')을 선택합니다.

산출: 비행기
INITCAP(텍스트) 시작 문자가 대문자인 지정된 텍스트를 반환합니다. 듀얼에서 ('guru99')를 선택하세요

산출: 전문가99

듀얼에서 ('나의 이야기')를 선택하세요.

산출: 내 이야기
길이( 텍스트 ) 주어진 문자열의 길이를 반환합니다. 듀얼에서 LENGTH('guru99')를 선택합니다.

산출: 6
LPAD(텍스트, 길이, pad_char) 주어진 길이(전체 문자열)만큼 왼쪽 문자열을 주어진 문자로 채웁니다. 듀얼에서 LPAD('guru99', 10, '$')를 선택합니다.

산출: $$$$guru99
RPAD(텍스트, 길이, pad_char) 주어진 길이(전체 문자열)만큼 오른쪽 문자열을 주어진 문자로 채웁니다. 듀얼에서 RPAD('guru99′,10,'-')를 선택합니다.

산출: guru99—-
LTRIM(텍스트) 텍스트의 선행 공백을 잘라냅니다. 듀얼에서 LTRIM('Guru99')을 선택합니다.

산출: 전문가99
RTRIM(텍스트) 텍스트에서 후행 공백을 잘라냅니다. 듀얼에서 RTRIM('Guru99')을 선택합니다.

산출; 구루99

날짜 함수

날짜를 조작하는 데 사용되는 함수입니다.

기능 명 용법 예시
ADD_MONTHS(날짜, 개월 수) 주어진 달을 날짜에 추가합니다. ADD_MONTH('2015-01-01',5);

산출: 05 / 01 / 2015
시스템 날짜 서버의 현재 날짜와 시간을 반환합니다. 이중에서 SYSDATE를 선택하십시오.

산출: 10년 4월 2015일 오후 2시 11분 43초
트렁크 날짜 변수를 가능한 낮은 값으로 반올림합니다. 듀얼에서 sysdate, TRUNC(sysdate)를 선택합니다.

산출: 10년 4월 2015일 오후 2시 12분 39초 10년 4월 2015일
라운드 날짜를 가장 가까운 한계값으로 반올림하여 더 높거나 낮게 지정합니다. 듀얼에서 sysdate, ROUND(sysdate)를 선택하세요.

산출: 10년 4월 2015일 오후 2시 14분 34초 10년 5월 2015일
MONTHS_BETWEEN 두 날짜 사이의 개월 수를 반환합니다. 듀얼에서 MONTHS_BETWEEN(sysdate+60, sysdate)을 선택합니다.

산출: 2

요약

이 장에서는 다음 내용을 배웠습니다.

  • 프로시저를 만드는 방법과 이를 호출하는 다양한 방법
  • 함수를 만드는 방법과 함수를 호출하는 다양한 방법
  • 프로시저와 함수의 유사점과 차이점
  • PL/SQL 하위 프로그램의 매개변수 및 RETURN 일반 용어
  • 일반적인 내장 함수 Oracle PL / SQL

당신은 좋아할 것입니다 :