07-2. 스토어드 함수와 커서
👉🏻 스토어드 함수
- SUM(), CAST(), CONCAT(), CURRENT_DATE() 등 MySQL에서 제공하는 함수 외에 내가 만들어 쓰는 함수
🧩 형식
- 스토어드 프로시저와 형태 비슷
- 매개변수는 모두 입력 매개변수이다
- 출력매개변수는 형식은 RETURNS의 반환형식 / 변수는 RETURN 반환값에 써주면 된다
- 대부분 SELECT로 호출한다
🎮 손코딩
- 스토어드 함수를 사용해기 위해 형식적으로 실행
- MySQL안에서 사용하는 변수
- 한번 설정하면 다음에는 쓰지 않아도 된다(?)
SET GLOBAL log_bin_trust_function_creators = 1;은 MySQL 데이터베이스에서 사용되는 SQL 쿼리 중 하나입니다. 이 쿼리는 MySQL 서버에서 실행되며, 특정 기능과 관련이 있습니다.
여기에서 각 부분을 간단하게 설명하겠습니다:
- SET GLOBAL: 이 부분은 MySQL 서버의 전역 설정 값을 변경하는 명령입니다. 즉, 현재 세션뿐만 아니라 전체 서버에 영향을 미치게 됩니다.
- log_bin_trust_function_creators: 이 부분은 MySQL에서 바이너리 로그(binlog)를 사용하는데, 이 바이너리 로그는 데이터베이스에서 수행되는 모든 변경 사항을 기록하는 데 사용됩니다. 이 설정은 함수 생성자에 대한 권한 검사를 완화하는 데 사용됩니다.
- = 1: 이 부분은 설정 값을 1로 지정하는 것으로, 함수 생성자에 대한 권한 검사를 무시하고 실행하도록 허용하는 역할을 합니다.
쉽게 말하면, 이 쿼리는 MySQL에서 바이너리 로그를 사용하는 경우에 함수를 만들 때 권한 검사를 더 관대하게 처리하도록 설정을 변경하는 것입니다. 이렇게 하면 특정 기능을 만들 때 권한 문제로 인해 발생하는 일부 오류를 방지할 수 있습니다.
하지만 주의해야 할 점은, 보안상의 이유로 이 설정을 계속해서 활성화하는 것은 권장되지 않습니다. 실제 운영 환경에서는 보안을 위해 최소한의 권한만 부여하는 것이 좋습니다. 이 설정을 변경할 때는 보안 측면에서의 영향을 신중하게 고려해야 합니다.
SET GLOBAL log_bin_trust_function_creators = 1; 쿼리를 실행하면 해당 설정은 현재 MySQL 서버에서 영구적으로 변경됩니다. 이 설정은 MySQL 서버 전역에 영향을 미치기 때문에 이번 실행동안에만 적용되는 것이 아니라, 그 이후에도 계속 유지됩니다.
설정을 변경한 후에는 MySQL 서버를 다시 시작할 필요가 없으며, 변경 내용이 즉시 적용됩니다. 그러므로 한 번 설정하면 해당 설정은 계속 유지되며, 다음에도 해당 설정을 변경하지 않는 한 계속 적용됩니다.
이러한 변경은 데이터베이스가 실행 중인 동안에도 계속 유지됩니다. 다시 실행하지 않아도 됩니다. 그러나 보안 및 규모 확장성을 고려하여 설정을 변경하는 것은 주의가 필요하며, 가능하면 보안 최소화를 유지하는 것이 좋습니다.
- 기존에 만든 적 있으면 삭제하라
- 함수 이름은 내가 지어준 것
- 매개변수는 무조건 입력 매개변수 - 두 개의 매개변수를 받아서
- 돌려주는 타입은 INT
- 넘겨 받은 두 숫자의 합을 돌려줘라
- 실행은 SELECT
- 100과 200이 number1과 number2에 전달
🎮 데뷔 연도로 몇 년 활동했는지
- 데뷔 연도를 dYear로 받음
- 돌려주는 것은 INT 형
- 변수명을 DCLARE를 통해 지정할 수 있다
- 현재 날짜의 연도만 뽑아내서 넘겨받은 연도를 빼면 활동기간을 알 수 있다
- 실행하는 연도에 따라 값은 달라진다
🎮 2013년과 2007년이 얼마나 차이나는지 알아보기
- 각각의 데이터를 조회해서 INTO를 통해 각 변수에 넣어주기
🧩 SELECT 안에서의 활용
- SELECT는 숫자를 넘겨도 되지만 더 많이 사용하는 것이 실제 데이터에서 쿼리한 결과에서부터 많이 쓴다??
- 쿼리 안에서 스토어드 함수를 활용할 수 있다
- 함수를 이용해, 열을 넘겨받을 수 있다
- 데뷔일 열 데이터들에서 연도만 뽑아 calcYearFunc 함수에 넘겨준다
- AS를 통해 열 이름을 지정해줄 수 있다
🧩 함수의 삭제
- DROP FUNCTION을 사용한다
👉🏻 커서로 한 행씩 처리하기
🧩 커서의 개념
- 한 행씩 처리하는 것
🧩 작동 순서
- 커서는 커서 자체라고 보면 된다(??)
- 커서를 가리키는 지시자라고 이해하면 된다
- 반복 조건 선언은 언제까지 반복할 것인지 지정
- 대개는 행이 끝날 때 까지
- 다 끝나면 커서를 닫음
- 코드로도 복잡
🎮 평균 회원수 구하기
- 커서는 프로시저 안에 들어가는 개념이라 프로시저로 만들면 된다
1️⃣ 사용할 변수 준비
- 회원의 평균 인원수를 구하기 위해
- 회원의 인원수, 전체 인원 합계, 읽은 행의 수 변수 3개를 준비
- 초기값 설정(0)
- 행의 끝을 파악하기 위한 변수를 설정(보통 endOfRow)
- 처음에는 FALSE로 둔다. 아직 행의 끝이 아니니까
2️⃣ 커서 선언하기
- 커서 이름 (내가 지정)
- SELECT문
3️⃣ 반복 조건 선언
- 행의 끝을 확인하는 문장
- 행의 끝에 오면 endOfRow를 TRUE로 바꿔라
4️⃣ 커서 열기
- OPEN으로 연다
5️⃣ 행 반복하기
- cursor_loop는 내가 정한 것
- LOOP ~ END LOOP 부분을 반복
- 회원의 수를 계속 누적
- 무한 루프가 되면 안되니까 빠져나갈 조건을 넣어줘야함
- LEAVE
- endOfRow가 TRUE라면 빠져나가라
- FETCH를 통해 한 행씩 접근 (읽어옴)
- 읽음 → 멤버 수를 넣음 → 아직 마지막 행이 아님 → 카운트 증가 → 회원수 누적 → 반복...
- 행이 모두 읽어지면 빠져나감
- 누적한 회원 수 / 카운트로 평균을 구함
6️⃣ 커서 닫기
-호출
- 평균 6.6명이 있음
출처: https://youtu.be/bMQ_dAoaMzA?si=L-rxQZKgbs31BK85
'MySQL > 혼공SQL' 카테고리의 다른 글
| 21강. SQL과 파이썬 연결을 위한 파이썬 설치하기 (0) | 2023.12.13 |
|---|---|
| 20강. 자동으로 실행되는 트리거(trigger)의 개념과 트리거를 활용하여 데이터 백업하는 방법 (0) | 2023.12.13 |
| 18강. 스토어드 프로시저(stored procedure)의 개념과 사용 방법(입출력 매개변수의 활용 방법) (1) | 2023.12.06 |
| 17강. 인덱스의 생성과 제거 문법(CREATE INDEX, DROP INDEX) (2) | 2023.12.06 |
| 16강.인덱스의 내부 작동 원리와 구조, 인덱스에서 데이터 검색하기 (2) | 2023.12.06 |