문서번호 : 11-332392
Document Information
•
최초 작성일 : 2022.06.23
•
최종 수정일 : 2022.06.27
•
이 문서는 아래 버전을 기준으로 작성되었습니다.
◦
SinglestoreDB : 7.8
Goal
•
Scalar 와 Collect 를 활용한 SingleStore의 Dynamic SQL 작성 방법 과 Oracle 의 Dynamic SQL 과 비교
Solution
•
Dynamic SQL 을 작성할 때 각 목적에 맞게 Excute Immediate, Echo Select 이외에도,
Scalar 또는 Collect 를 사용할 수 있다.
명령어 | 목적 |
Execute Immediate | Select 쿼리문 이외의 UPDATE 같은 동적 쿼리문을 실행 할 때 , DDL문을 실행 할 때 |
Execute Immediate & Echo Select | Procedure에서 Ad-Hoc Query를 실행하고 그 결과를 SingleStore CLI에 출력하거나, application에 Multiple Result Set으로 반환하도록 할 때 |
Scalar | 하나의 행과 하나의 칼럼만 반환하는 동적 SELECT 쿼리문을 실행 할 때 |
Collect | 여러 행을 반환하는 동적 SELECT 쿼리문을 실행하고, 각 행에 대한 반복 작업을 수행 할 때 |
•
SingleStore Dynamic SQL 작성 방법
◦
예시 (Execute Immediate 와 Echo Select 사용)
# 사원 테이블에서 사원 번호를 입력받아 해당하는 행 출력
DELIMITER //
CREATE or REPLACE PROCEDURE emp_sample(emp_number INT(11)) AS # 사원번호를 파라미터로 받음
DECLARE
qry TEXT; # 쿼리 변수 정의
BEGIN
qry = CONCAT('ECHO SELECT dept_no, emp_no, emp_name, job_title from emp where emp_no = ', emp_number); # Echo Select 를 사용하여 쿼리 작성
EXECUTE IMMEDIATE qry; # Execute Immediate를 사용하여 쿼리 실행
END
//
DELIMITER ;
CALL emp_sample(1000288); # 동적 쿼리 실행
+---------+---------+----------+----------------+
| dept_no | emp_no | emp_name | job_title |
+---------+---------+----------+----------------+
| 200 | 1000288 | SCOTT | SYSTEM ANALYST |
+---------+---------+----------+----------------+
# 사원번호에 해당하는 정보 출력
SQL
복사
•
Dynamic SQL 작성 예시(Scalar, Collect)
◦
Scalar 를 활용한 Dynamic SQL
# 사원 테이블에서 사원번호를 입력받아 해당 사원 이름을 출력하는 동적 쿼리
DELIMITER //
CREATE or REPLACE PROCEDURE emp_no_name(emp_number INT(11)) AS # 사원번호를 파라미터로 받음
DECLARE
qry TEXT;
BEGIN
qry = SCALAR(CONCAT('SELECT emp_name from emp where emp_no = ', emp_number), QUERY(str VARCHAR(50))); # 사원번호를 받아 사원명을 출력하는 쿼리, 리턴 데이터 타입
ECHO SELECT qry AS result; # 쿼리 결과 출력
END
//
DELIMITER ;
CALL emp_no_name(1000245); # 동적 쿼리 실행
+---------+
| result |
+---------+
| PRADEEP |
+---------+
# 사원번호 1000245에 해당하는 사원명 PRADEEP 출력
SQL
복사
◦
Oracle Dynamic SQL 코드 비교
CREATE OR REPLACE PROCEDURE emp_no_name(emp_number in emp.emp_no%TYPE)
IS
qry VARCHAR2(100);
names emp.emp_name%TYPE;
BEGIN
qry := 'SELECT emp_name FROM emp WHERE emp_no = :1';
EXECUTE IMMEDIATE qry INTO names USING emp_number;
DBMS_OUTPUT.PUT_LINE(names);
END;
/
EXEC emp_no_name(1000245);
PRADEEP
SQL
복사
◦
Collect 를 활용한 Dynamic SQL
# 사원 테이블에서 부서번호를 입력받아 해당 부서 사원들의 이름과 10% 인상된 급여를 새 테이블에 삽입하는 동적 쿼리
CREATE table name_sal(name VARCHAR(50), salary DECIMAL(18,2)); # 새 테이블 생성
DELIMITER //
CREATE or replace procedure dept_sample(num int(11)) AS # 부서번호를 파라미터로 받음
DECLARE
BEGIN
FOR x in COLLECT(CONCAT('SELECT emp_name, salary FROM emp where dept_no = ', num), QUERY(emp_name VARCHAR(50), salary DECIMAL(18,2))) LOOP # 부서번호에 해당하는 개별 사원명과 급여를 출력하는 쿼리, 리턴 데이터 타입
INSERT INTO name_sal VALUES(x.emp_name, x.salary*1.1); # 새 테이블에 특정 부서 사원이름과 10% 인상 된 급여 삽입
END LOOP;
END //
DELIMITER ;
CALL dept_sample(100); # 동적 쿼리 실행
Query OK, 0 rows affected (0.38 sec)
SELECT* FROM name_sal; # 결과 확인
+---------+---------+
| name | salary |
+---------+---------+
| PRADEEP | 5500.00 |
| CLARK | 2695.00 |
| MILLER | 1430.00 |
+---------+---------+
# 부서번호 100에 해당하는 사원명과 10% 인상 된 급여 출력
SQL
복사
◦
Oracle Dynamic SQL 코드 비교
CREATE table name_sal(name VARCHAR(50), salary DECIMAL(18,2)); # 새 테이블 생성
CREATE OR REPLACE PROCEDURE dept_sample(num in emp.dept_no%TYPE)
IS
TYPE qry_cur IS REF CURSOR;
e_cur qry_cur;
qry VARCHAR2(2000);
names emp.emp_name%TYPE;
sal emp.salary%TYPE;
BEGIN
qry := 'SELECT emp_name, salary FROM emp WHERE dept_no = :1';
OPEN e_cur FOR qry USING num;
LOOP
FETCH e_cur INTO names, sal;
EXIT WHEN e_cur%NOTFOUND;
INSERT INTO name_sal VALUES(names, sal*1.1);
END LOOP;
CLOSE e_cur;
END;
/
EXEC dept_sample(100); # 동적 쿼리 실행
SELECT* FROM name_sal; # 결과 확인
NAME SALARY
-------------------------------------------------- ----------
MILLER 1430.00
PRADEEP 5500.00
CLARK 2695.00
SQL
복사
References
History
일자 | 작성자 | 비고 |
2022.06.23 | min | 최초 작성 |
2022.06.24 | jnshin | review |
2022.06.27 | min | 편집 |
