PL/SQL 저장 프로시저 및 함수

저장 프로시저 개요

저장 프로시저는 일부 작업을 완료할 수 있는 일종의 서브루틴이며 데이터베이스에 스키마 개체로 저장됩니다. 매개변수 수신 여부를 지원하고 매개변수 출력도 지원하는 명명된 PL/SQL 코드 블록입니다. 저장 프로시저에는 일반적으로 정의 부분, 실행 부분 및 예외 부분이 포함되어 있으며 다른 서브루틴에서 호출할 수도 있고 재사용할 수도 있습니다.
프로시저 정의
CREATE [OR REPLACE] PROCEDURE 프로시저_이름
[(argument_name [IN | OUT | IN OUT] 인수_유형)]
AS | IS
BEGIN
​​​​procedure_body;
END [프로시저_이름];
저장 프로시저의 매개변수 유형
IN: 입력 매개변수이며 기본값을 지정할 수 있음을 나타냅니다. 매개변수 유형을 생략하면 기본값은
유형입니다. OUT: 출력 매개변수를 나타냅니다.
IN OUT: 결과를 출력하기 위해 입력 매개변수 또는 출력 매개변수로 사용할 수 있습니다.
프로시저 호출
EXECUTE |CALL 프로시저_이름 [(인수_목록)]

예: JOB의 최대 급여, 최소 급여, 평균 급여를 쿼리하기 위해 JOB을 매개 변수로 사용하는 프로시저를 정의합니다.

query_sal(emp.job%type의 v_job)
프로시저를
v_min_sal emp.sal%type;
v_max_sal emp.sal%type;
v_avg_sal emp로 생성 또는 교체합니다. sal%type;
begin
min(sal)을 emp에서 v_min_sal로 선택(job = v_job;
max(sal)을 emp에서 v_max_sal로 선택 where job = v_job;
avg(sal) 선택) v_avg_sal from emp where job = v_job;
dbms_output.put_line('이 직업의 최저 급여는 ' || v_min_sal);
dbms_output.put_line('이 직업의 최대 급여는 ' || v_max_sal);
dbms_output.put_line('이 직업의 평균 급여는 ' || v_avg_sal);
예외
no_data_found일 때
dbms_output.put_line('기록을 찾을 수 없음');
end;

SQL> set serveroutput on
SQL> exec query_sal('SALESMAN');
이 직업의 최저 급여는 1250
이 직업의 최고 급여는 1600입니다
이 직업은 평균 급여입니다. 1400
PL/SQL 프로시저가 성공적으로 완료되었습니다.

매개변수 및 해당 전달 방법
프로시저 설정 시 전달되는 매개변수는 선택사항입니다. 매개변수 옵션을 생략하면 프로시저는 매개변수가 없습니다. 절차( 정의할 때 매개변수가 지정되지 않으며 호출할 때 매개변수가 필요하지 않습니다). 매개변수 옵션이 지정되면 프로세스는 매개변수화된 프로세스입니다(정의 시 매개변수 이름, 모드 및 데이터 유형을 지정해야 하며, 정의 시 해당 매개변수 값을 제공해야 함). 매개변수, 호출 시 매개변수를 실제 매개변수라고 합니다.

매개변수 프로시저 없음
display_systime 프로시저 생성 또는 교체
as
begin
dbms_output.put_line('현재 시간은 ' || sysdate);
end;
SQL> exec display_systime;
현재 시간은 24-FEB-13

매개변수 절차
정의할 때 매개변수의 이름, 모드 및 데이터 유형을 지정해야 합니다
예: 레코드 추가 방법 정의 프로시저(모든 입력 매개변수)
프로시저 생성 또는 교체 add_emp
(
v_no in emp.empno%type,
v_name in emp.ename%type,
v_dept emp.deptno%에서 기본 20을 입력하세요. --기본 부서 번호
)
as
begin
emp(empno,ename,deptno) 값(v_no,v_name,v_dept)에 삽입;
예외
when dup_val_on_index then
dbms_output.put_line('Record Exists');
end ;

SQL> exec add_emp(7369,'TEST',20) --Call
기록이 존재함

예: 입력된 사원 번호를 정의하고, 기록을 수정한 후 수정된 결과(이름 및 급여)를 반환합니다.

ed_emp 프로시저 생성 또는 교체
(
v_no in emp.empno%type, -- 하나의 in 유형 매개변수와 두 개의 out 유형 매개변수 정의
v_name out emp.ename%type ,
v_sal out emp.sal%type)
as
start
업데이트 emp set sal = sal + 100 여기서 empno = v_no;
ename,sal을 v_name으로 선택, v_sal from emp where empno = v_no ;
예외
no_data_found일 때
dbms_output.put_line('데이터를 찾을 수 없음');
end;
/
프로시저가 생성되었습니다.

SQL> ;VARIABLE t_name varchar2(20);
SQL>VARIABLE t_sal number;
SQL> exec ed_emp(7369,:t_name,:t_sal);
PL/SQL 프로시저가 성공적으로 완료되었습니다.
SQL> >T_NAME
----------------------------------------------- --- -----------
SMITH
SQL> 인쇄 t_sal
T_SAL
----------
900
예: IN OUT 유형 매개변수 사용

comp 프로시저 생성 또는 교체
(외부 번호의 num1, 외부 번호의 num2)
as
v1 number;
v2 number;
begin
v1 := num1 + num2;
v2 := num1 * num2;
num1 := v1;
num2 := v2;
end;
SQL> var v1 숫자
SQL> 🎜>SQL> exec :v1 := 3
PL/SQL 프로시저가 성공적으로 완료되었습니다.
SQL> exec :v2 := 5
PL/SQL 프로시저가 성공적으로 완료되었습니다.
SQL> v1,:v2);
SQL> 인쇄 v1 v2
V1
---------
8
V2
--------- --
15
SQL> exec comp(:v1,:v2);
PL/SQL 절차가 성공적으로 완료되었습니다.
SQL> print v1 v2
V1
--- ------
23
V2
----------
120

in out 유형 매개변수가 모두 입력 매개변수임을 알 수 있습니다. 출력 매개변수로 사용됩니다.

저장 프로시저 매개변수 전송 방법:

위치별 전송:
실제 매개변수는 형식 매개변수에 순서대로 전달됩니다.
EXECUTE ED_EMP(7900,:t_name,:t_sal);
EXECUTE ED_EMP(8000,'TEST2',20);
이름으로 전달
EXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003)
혼합 패스
EXECUTE ED_EMP(8005,v_dept=>20,v_name=>'TEST5');

프로세스 관리
시스템 프로세스 정보 보기
DBA_OBJECTS
DBA_PROCEDURES
DBA_SOURCE
desc Procedure_name을 사용하여 저장 프로시저의 매개변수 정보 보기
SQL>desc ed_emp;
PROCEDURE ed_emp
인수 이름 유형 --- ------ --------
 V_NO                          NUMBER(4)               IN
 V_NAME                       VARCHAR2 (10)            OUT
V_SAL NUMBER (7,2) OUT
DBA_Objects의 저장 절차 정보
SQL & GT; Select Owner, Object_name, Object_type, Status from DBA_OBJECTS WHERE OBJECT_NAME = 'ED_EMP';
소유자 Object_name Object_type 상태
------------------ ------------ ------- - --------------- -------
SCOTT ED_EMP PROCEDURE VALID
SQL>select object_name,procedure_name,interface,authid user_procedures에서;
OBJECT_NAME PROCEDURE_NAME INT AUTHID
--------------- ----------- ------------- ------ --- ------------
DISPLAY_SAL                                                                           ER
저장 프로시저의 소스 코드 보기
SQL>선택 라인, name='ED_EMP';인 user_source의 텍스트
라인 텍스트
---------- -------------- --------- --------------- --------- --------
1 절차 ed_emp
2 (
3 v_no IN emp.empno%TYPE,
4 v_name OUT emp.ename % TYPE,
5 v_sal OUT emp .sal%TYPE
6 )
7 AS
8 BEGIN
​​​​9 UPDATE emp SET sal=sal+100 WHER E empno=v_no;
10 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE('NOT FOUND RECO RD!');
14       END ed_emp;
오류 메시지 보기
오류 표시

함수 개요

함수는 일반적으로 특정 데이터를 반환하는 데 사용됩니다. 그 핵심은 명명된 PL/SQL 블록으로, 데이터베이스에 스키마 개체로 저장되고 반복적으로 실행될 수 있습니다. 함수는 일반적으로 저장 프로시저의 표현식이나 매개변수로 호출되며 반환 값을 갖습니다.
1. 함수 생성 구문
CREATE [ OR REPLACE ] FUNCTION function_name
(argument1 [mode1] datatype1,
인수2 [mode2] datetype2,
...)
RETURN 데이터 유형
IS | AS
[local_variable_declarations;...]
BEGIN
​​​​--actions;
RETURN 표현식;
END [함수_이름];
에 대한 참고 사항 함수 설정 관련 사항
1. 매개변수 데이터 유형(인수) 지정 시 길이를 지정할 수 없습니다
2. 함수 헤더에는 return 절을 지정해야 하며, 함수 본문에는 return 문이 하나 이상 포함되어야 합니다
3. in 매개변수를 지정할 수 있고, out 매개변수도 지정할 수 있으며, in out 매개변수도 지정할 수 있습니다.
4. 매개변수에 대한 기본값을 지정할 수 있습니다. 기본값을 지정할 때 default 키워드를 사용하십시오. 예를 들어 arg1 varchar2 기본 'SCOTT'
함수 사용의 장점:
1. 코드의 유연성을 높이고 SQL을 통해서만 완료할 수 없는 작업은 물론 좀 더 복잡한 작업을 완료할 수 있습니다
2. 데이터를 필터링하기 위해 where 절의 함수를 직접 사용할 수 있습니다
3. 저장 프로시저를 보완하는 저장 프로시저의 매개변수로 사용할 수 있습니다

function
매개변수 없이 함수 만들기

get_user 함수 만들기 또는 바꾸기
return varchar2
as
v_user varchar2(20);
begin
사용자 이름을 v_user로 선택 user_users;
return v_user;
end;

전역 변수를 사용하여 함수의 반환 값을 받습니다

SQL> var v1 varchar2(20);
SQL> exec :v1 := get_user;
PL/SQL 프로시저가 성공적으로 완료되었습니다.
SQL> print v1
V1
------------------- -------------- -----
SCOTT
지역 변수를 사용하여 함수의 반환 값을 받습니다

SQL> );
4 dbms_output.put_line('현재 사용자: ' || user_name);
5 end;
6 /
현재 사용자: SCOTT
PL/SQL 프로시저가 성공적으로 완료되었습니다.

SQL 문에서 직접 함수 호출

SQL> select get_user from Dual;

GET_USER

-- --------- ---
SCOTT
dbms_output을 사용하여 함수를 호출합니다.(이 호출은 저장 프로시저의 매개 변수로 이루어집니다. )
SQL> set serveroutput on;
SQL> exec dbms_output.put_line('현재 사용자: '||get_user);
현재 사용자: SCOTT

in 매개변수를 사용하여 함수 생성

raise_sal 함수 생성 또는 교체(varchar2의 이름)

반환 번호

as
new_sal emp.sal%type;
begin
sal * 1.2를 emp where upper(ename) = upper( name);
return new_sal;
예외
no_data_found일 때
raise_application_error(-20000,'현재 직원이 존재하지 않습니다');
end ;

SQL> ename='SCOTT';

SAL RAISE_SAL('SCOTT')

----에서 emp에서 sal,raise_sal('SCOTT') 선택 ------ ---- ---------------

3000 3600

SQL> SCOTTT') from emp where ename='SCOTT' ;

select sal,raise_sal('SCOTTT') from emp where ename='SCOTT'

*

ERROR at line 1:
ORA-20000 : 현재 직원이 존재하지 않습니다
ORA- 06512: "SCOTT.RAISE_SAL", 11행

외부 매개변수가 있는 함수 생성
get_info 함수 생성 또는 교체

(이름 varchar2,titile out varchar2)

return varchar2
as
deptname dept.dname%type;
begin
select e.job,d.dname into titile,deptname
from emp e,dept d
여기서 e.deptno = d.deptno
및 upper(e.ename) = upper(name);
return deptname;
예외
no_data_found 경우
raise_application_error(-20000 ,'현재 직원이 존재하지 않습니다.');
end;
/

out 매개변수를 사용하는 함수는 SQL 문을 사용하여 호출할 수 없습니다. 대신, 매개변수와 함수의 반환 값을 수신하도록 변수를 정의해야 합니다.
통화 내용은 다음과 같습니다

SQL> var job varchar2(20);
SQL> var dname varchar2(20);
SQL> exec :dname := get_info('scott',:job);
PL/SQL 프로시저가 성공적으로 완료되었습니다.
SQL> dname 작업 인쇄
DNAME
---------------------------- ---------
연구
직업
------------------------------- ------
분석가
SQL> select get_info('scott') from 듀얼
  2  ;
select get_info('scott') from 듀얼
       *
라인 1의 오류:
ORA-06553: PLS-306: 잘못됨 'GET_INFO'

建立带有in out参数的函数
함수 comp 만들기 또는 바꾸기
(num1 number,num2 in out number)
반환 번호에 대한 호출의 인수 수 또는 유형
as
  v_result 숫자(6);
  v_remainder 숫자;
begin
  v_result := num1 * num2;
  v_remainder := mod(num1,num2);
  num2 := v_remainder;
  return v_result;
예외
  zero_divide일 때
    raise_application_error(-20000,'0으로 나누기');
end;
/

SQL> var n1 번호    
SQL> var n2 숫자
SQL> exec :n2 := 10;
PL/SQL 프로시저가 성공적으로 완료되었습니다.
SQL> exec :n1 := comp(16,:n2);
PL/SQL 프로시저가 성공적으로 완료되었습니다.
SQL> print n1 n2
        N1
---------
       160
        N2
---------
         6
函数的调사용及限제
1.函数적 사용(其具体调사용방법법参光上face的演示)
a.使사용 전체局变weight接收函数적返回值
b.使사용본지점수接受函数的返回值
c.재SQL语句中直接调사용函数
d.使用dbms_output调用函数
注：函数에서 时候需要按位置指定参数,没有存储过程参数传递灵活必须具有函数的权限를 실행
2 .函数에서SQL中调사용주접
由于函数必须要返回数据，因此只能为表达式的一达式的一分调用。此外函数可以在SQL语句的以下分调사용
a.命令的选择列表或子查询中
을 선택합니다.b. 条件表达式where, 子句中
c. 로 연결하고, 로 시작하고, 子句中
d로 以及그룹으로 주문하세요.命令의 값을 삽입합니다子句中
f.命令의 세트 子句中 업데이트
3.函数在SQL中调사용용限제
a. SQL은 SQL을 사용하여 실행하는 데 사용할 수 없습니다.
b. SQL语句中调사용용函数只能带有输入参数IN，而不能带有输流参数OUT 以及输入输流参数IN OUT
c. SQL은 SQL에 사용되는 SQL 유형이 아니며 PL/SQL에 사용되지 않는 유형, 부울, 테이블, 레코드
d. SQL语句中调사용할 수 있는 函数不能包含, 업데이트 및 삭제 语句
创建一张表tb_emp

SQL> emp에서 *를 선택하여 tb_emp 테이블을 생성합니다.
테이블이 생성되었습니다.

创建一个函数，用于删除tb_emp表中指定的empno号的雇员信息，并返回其薪资
SQL& gt; del_emp
  2  (숫자 없음)
  3  반환 번호
  4  as
  5    v_sal emp.sal%type;
  6  start
  7    sal을 emp에서 v_sal로 선택 where empno = no;
  8    delete tb_emp where empno = no;
  9    return v_sal;
 10  end;
 11  /
함수가 생성되었습니다.

使用SQL语句调用时，收到了错误信息，지금은 DML操작
SQL> 이중에서 del_emp(7788) 선택;
이중에서 del_emp(7788) 선택
       *
라인 1의 오류:
ORA-14551: 쿼리 내에서 DML 작업을 수행할 수 없습니다
ORA- 06512: "SCOTT.DEL_EMP", 8행

使用exec执行时函数被成功执行
SQL> var n1 숫자
SQL> exec :n1 := del_emp(7788);
PL/SQL 프로시저가 성공적으로 완료되었습니다.
SQL> n1 인쇄
        N1
------------
      3000

函数使用了与存储过程关的视图，可以从系统视图中(系统视图中)获得函数的상상关信息
DBA_OBJECTS
DBA_SOURCE
USER_OBJECTS
USER_SOURCE

查看函数的源码

SQL& gt;줄 선택, user_source의 텍스트, 여기서 이름 ='DEL_EMP' 줄 단위로 정렬

라인 텍스트
-------------------------------------------- ---------------
1 함수 del_emp
2(번호 없음)
3 반환 번호
4 as
5 v_sal emp.sal%type;
6 시작
7 emp에서 v_sal로 sal 선택 empno = no;
8 tb_emp 삭제 empno = no;
9 반환 v_sal; 10 end; 10 ROWS 선택

SQL & GT Desc Del_emp; /기본값 해제?

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

아니오 ~ 기능

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

부담용 작문                                    只能作为表达式被调用
声明头部关键字为procedure                            声明头部关键字为function
선언 헤더에는 반환 유형을 설명하는 return 키워드가 포함되어 있지 않습니다. 헤더에는 return 키워드가 포함되어야 하며 PL/SQL 블록에는 유효한 하나 이상이 포함되어 있습니다. return 문
out, in out을 통해 0개 이상의 값을 반환할 수 있습니다. Return 문을 통해 head 선언에 있는 유형과 일치하는 값을 반환할 수 있으며, SQL 문, 저장 프로시저 SQL 문이라고 할 수 있는 삭제, 업데이트, 삽입 등의 작업과 기타 DML 작업은 선택 등의 특정 데이터에 주로 사용됩니다.