SP를 만들고 express에서 호출하여 데이터를 출력하는 예제를 만들어 볼 것이다.
exam table
myuser table
각 테이블에서 id와 name만 출력하는 select문
SELECT id, name FROM exam;
SELECT id, name FROM myuser;두 개의 select문을 한 번에 처리하도록 SP만들기
첫 번째 sp : 파라미터에 1 or 2 를 입력하면 그의 맞는 select문이 실행 됨.
DELIMITER//
create procedure 'sselect'(IN select_option INT)
being
if select_option = 1 then
select id, name from exam;
elseif select_option = 2 then
select id, name from myuser;
end if;
end //
DELIMITER ;두 번째 sp : 파라미터에 테이블 명을 입력하면 해당 테이블 관련 select를 출력.
DELIMTER//
create procedure 'sselect'(IN table_name VARCHAR(50))
being
if table_name = 'exam' then
select id, name from exam;
elseif table_name = 'myuser' then
select id, name from myuser;
end if;
end ;
DELIMTER ;express에 sp 호출하기
기본적으로 sp는
call sselect(파라미터)로 실행됨
기존에 작성해 놓은 test.js코드를 실행시키면
const express = require('express');
const app = express();
const port = 3000;
app.use(express.json());
app.use(express.urlencoded({extended: true}));
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
port: '3306',
user: 'root',
password: 'abcd1234',
database: 'userdb'
});
const getConn = async() => {
return await pool.getConnection(async (conn) => conn);
}
app.listen(port, ()=>{
});
app.get('/exam', async(req, res)=>{
const conn = await getConn();
const query = 'select * from exam;';
let [row, fields] = await conn.query(query, []);
conn.release();
const rowsString = row.map((row) => JSON.stringify(row)).join('<br />');
res.send(rowsString);
});
app.get('/myuser', async(req, res)=>{
const conn = await getConn();
const query = 'select * from myuser;';
let [row, fields] = await conn.query(query, []);
conn.release();
const rowsString = row.map((row) => JSON.stringify(row)).join('<br />');
res.send(rowsString);
});
경로에 테이블명을 입력하면 맞는 select 결과가 출력됨.
위 코드를 수정하여 테이블명을 입력했을 경우 sp를 호출할 거임
app.get('/:table', async (req, res) => {
const conn = await getConn();
const query = 'CALL sselect(?)';
const table = req.params.table;
try {
const [rows, fields] = await conn.query(query, [table]);
conn.release();
res.send(rows);
} catch (error) {
console.error(error);
res.status(500).json("검색 실패");
} finally {
conn.release();
}
});위 코드를 보면 query 변수에 프로시저를 호출하고 있으며 httpbody에서 table명을 받고있다. 이 table명을 프로시저 파라미터에 배열형태로 입력하고 결과는 rows에 저장하여 res.send(rows); 로 rows를 출력한다.
위 처럼 id와 name만 출력하는 rows가 json 형식으로 출력된다.
다음은 좀 더
데이터를 가공할 수 있는 예제
이다.
DELIMITER //
create procedure `dataprocess`(in qureyname varchar(50), in id int, in name varchar(50))
being
if queryname = 'select' then
if id is null or name in null then
select * from myuser;
end if;
elseif qureyname = 'insert' then
if id is not null and name is not null then
insert into myuser (id, name) values (id, name);
end if;
end if;
end //
DELIMITER ;위와 같은 프로시저를 만들었다.
call dataprocess('select', NULL, NULL);파라미터에 select를 입력하면
위처럼 select 쿼리가 실행된다.
call dataprocess('insert', 4, 4);파라미터를 insert와 null이 아닌 id, name값을 입력하면
id = 4, name = 4 인 필드가 삽입되었다.
다시 test.js에서 호출
app.get('/:qureyname', async (req, res) => {
const conn = await getConn();
const qureyname = req.params.qureyname;
const query = 'CALL dataprocess(?, null, null)';
try {
const [rows, fields] = await conn.query(query, [qureyname]);
conn.release();
res.send(rows);
} catch (error) {
console.error(error);
res.status(500).json("검색 실패");
} finally {
conn.release();
}
});
app.get('/:qureyname/:id/:name', async (req, res) => {
const conn = await getConn();
const qureyname = req.params.qureyname;
const id = req.params.id;
const name = req.params.name;
const query = 'CALL dataprocess(?, ?, ?)';
try {
await conn.query(query, [qureyname, id, name]);
conn.release();
res.send("입력 성공");
} catch (error) {
console.error(error);
res.status(500).json("입력 실패");
} finally {
conn.release();
}
});위처럼 qureyname을 select와 insert로 받는다면
다시 select를 하면
입력한 정보가 저장되었다.
mysql에서도 확인할 수 있다.
안녕!