1. Zepline [제플린]
2. Sketch
3. Adobe XD
4. Figma -> DevMode, Ai ==> Starter 버전만 무료,
..
파워포인트, Draw.io...
-- 응용프로그램에서 호출(ex.Java)에서 사용함.
오라클 IS[AS] 있지만, MariaDB는 없음.
-- p.410 저장 프로시저(Stored Procedure)
-- 프로그램 코드를 일괄처리하기 위한 프로그래밍 ==> 데이터베이스 개체로 저장해두고 (프로시저)
-- 응용프로그램에서 호출(ex.Java)하여 사용함.
-- ※ 보안성 향상에 도움이 된다, SQL Injection 취약점에 대비
-- DELIMITER - 구분자, C나 JAVA의 세미콜론(;)이라고 생각하면 된다.
-- 1) 프로시저 생성
DELIMITER $$
CREATE OR REPLACE PROCEDURE 프로시저이름([IN|OUT] 매개변수 자료형)
BEGIN NOT ATOMIC
END $$
DELIMITER;
-- 2) 프로시저 호출
CALL 프로시저명(매개변수)
-- p.412 sp_test1 이라는 프로시저를 생성하시오
sp_test1DELIMITER $$
CREATE OR REPLACE PROCEDURE sp_test1()
BEGIN
SELECT memid, memname FROM members;
END $$
DELIMITER ;
CALL sp_test1;
-- Q. members 테이블에 신규 회원 등록, 회원 삭제, 회원정보 수정 프로시저를 생성해보시오
DESC members;
-- 1) 신규 회원 등록
DELIMITER $$
CREATE OR REPLACE PROCEDURE ADD_MEMBER(IN id VARCHAR(9), NAME VARCHAR(20), STATUS ENUM('Y'), enter DATETIME)
BEGIN
INSERT INTO members (memid, memname, stat, enterdtm) VALUES (id, NAME, STATUS, enter);
COMMIT;
END $$
DELIMITER ;
CALL ADD_MEMBER('kim3', '김연순', 'Y', NOW());
SELECT *
FROM members;
-- 2) 회원 삭제
DELIMITER $$
CREATE OR REPLACE PROCEDURE REMOVE_MEMBER(IN id VARCHAR(9))
BEGIN
DELETE FROM members WHERE memid = id;
COMMIT;
END $$
DELIMITER ;
CALL REMOVE_MEMBER('kim2');
CALL REMOVE_MEMBER('kim1');
SELECT *
FROM members;
===========
-- 오라클 PLSQL : Procedural Language extion to SQL (SQL로 확장한 절차적 프로그래밍언어)
-- ※ 보기 > DBMS 출력 창을 켜두고 실습
-- MariaDB : SQL 절차적 프로그래밍 (교재 p.364~)
-- ※ 절차적 프로그래밍 대상 : 테이블 대상
-- I. 오라클 > 익명블럭 (Anonymous Block) : 단순 실행~ 결과 처리~
-- 1) 오라클 블럭 구조
DECLARE
-- 변수 (선언 및 초기화)
-- 상수 (선언 및 초기화)
BEGIN
-- 변수 초기화
-- 일반 DML 문장
END;
-- 2) MariaDB 블럭 | 교재 p.373 참고
-- ※ 저장 프로시저(Stored Procedure)를 작성하거나 사용자 정의 함수등을 만들때 : NOT ATOMIC
-- └ INSERT INTO employees VALUES (,,,,)
-- └ EXEC ADD_EMP(이름)
-- ※ MySQL 에서는 NOT ATOMIC 속성이 지원되지 않음 ==> SQL 문장을 묶는 BEGIN ... END를 사용할 수 없다
BEGIN NOT ATOMIC
END;
-- II. 오라클 > 명명블럭 (Named Block) ==> Stored Procedure / 프로시저, 함수, (패키지), 트리거
-- ※ 테이블의 데이터 입력/출력/수정/삭제(DML) 처리용 + COMMIT / ROLLBACK
-- ※ Sub Program (서브 프로그램) ==> (자바등) 애플리케이션으로 SQL을 프로그램 단위로 호출
-- =============================================================================
-- MariaDB 교재 p.383, p.385
-- =============================================================================
-- 1) 보통 $$ 나 // 를 사용
DELIMITER $$
BEGIN
END $$
DELIMITER;
DELIMITER //
BEGIN
END //
DELIMITER;
-- 오라클 명명블럭 ==> 서브 프로그램 | 이름을 갖고 있음
-- ※ 데이터베이스 객체(OBJECT)중 하나 ==> CREATE 문장 생성, ALTER 수정, DROP 삭제
-- 1. 프로시저(Procedure) vs 함수(Function) : RETURN 없음 vs RETURN 있음
-- 예) ADD_EMP(이름, 업무) vs GET_TAX(사번) : 사용자 정의 함수 vs SUM(), AVG()...:그룹함수
-- 1) 프로시저 생성 규칙 | 교재 p.411
-- 1-1) 오라클
/*
CREATE OR REPLACE PROCEDURE 프로시저이름 ([IN | OUT] 매개변수 자료형) AS[IS]
-- 추가 변수
-- 추가 상수
BEGIN
-- 실행블럭
END;
*/
-- EXEC 프로시저이름;
-- EXECUTE 프로시저이름(매개변수);
-- EXEC ADD_EMP('홍길동', 'SA_MAN'); ===> EMPLOYEES 테이블에 '홍길동'사원을 'SA_MAN'업무로 등록
-- 1-2) MariaDB
/*
DELIMTER $$
CREATE OR REPLACE PROCEDURE 프로시저이름 ([IN | OUT] 매개변수 자료형)
BEGIN NOT ATOMIC
-- 실행블럭
END $$
DELIMITER;
*/
CALL 프로시저이름(매개변수)
--ex) CALL ADD_NEW('김길동','광주 서구');
-- 2) 프로시저 실습
-- 2-1) 오라클 프로시저 생성 및 실습
-- Q. 신규 사원 등록용 프로시저를 생성하고, '홍길동' 사원을 등록하시오
INSERT INTO employees (employee_id, first_name, email, hire_date, job_id)
VALUES (,,,,);
INSERT INTO employees (employee_id, first_name, email, hire_date, job_id)
VALUES (,,,,);
INSERT INTO employees (employee_id, first_name, email, hire_date, job_id)
VALUES (,,,,);
-- ... 빈번하게 자주 처리해야하는 작업단위 (반복)
-- vs
ADD_EMP(employee_id, first_name, email, hire_date, job_id)
DESC hr.employees;
--이름 널? 유형
---------------- -------- ------------
--EMPLOYEE_ID NOT NULL NUMBER(6)
--FIRST_NAME VARCHAR2(20)
--LAST_NAME NOT NULL VARCHAR2(25)
--EMAIL NOT NULL VARCHAR2(25)
--PHONE_NUMBER VARCHAR2(20)
--HIRE_DATE NOT NULL DATE
--JOB_ID NOT NULL VARCHAR2(10)
--SALARY NUMBER(8,2)
--COMMISSION_PCT NUMBER(2,2)
--MANAGER_ID NUMBER(6)
--DEPARTMENT_ID NUMBER(4)
-- CTAS : 1) 테이블의 구조 또는 2) 테이블의 구조와 데이터를 복사하여 새로운 테이블 객체 생성하는 문법
CREATE TABLE hr_emp AS
SELECT employee_id AS emp_id,
last_name AS l_name,
email,
hire_date,
job_id
FROM hr.employees;
DESC hr_emp;
-- 선언부 : DECLARE
CREATE OR REPLACE PROCEDURE add_hr_emp(
emp NUMBER,
lname VARCHAR2,
email VARCHAR2,
hiredate DATE,
jobid VARCHAR2
) IS
BEGIN
-- 실행부
INSERT INTO hr_emp (emp_id, l_name, email, hire_date, job_id)
VALUES (emp, lname, email, hiredate, jobid);
COMMIT;
-- 예외 처리부
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END;
-- Procedure ADD_HR_EMP이(가) 컴파일되었습니다.
EXEC add_hr_emp(300,'Hong', 'GILDONG', SYSDATE, 'SA_MAN');
EXEC add_hr_emp(301,'PARK', 'JINSU', SYSDATE, 'SA_REP');
--PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM hr_emp
ORDER BY 1 DESC;
-- Q. 사원목록 삭제 프로시저를 생성하시오
DELETE FROM hr_emp
WHERE emp_id = 403;
--1 행 이(가) 삭제되었습니다.
CREATE OR REPLACE PROCEDURE DEL_HR_EMP(empid NUMBER) IS
BEGIN
DELETE FROM hr_emp
WHERE emp_id = empid;
DBMS_OUTPUT.PUT_LINE('요청이 처리되었습니다');
COMMIT;
EXCEPTION WHEN OTHERS THEN -- try ~ catch ~ finally
ROLLBACK;
END;
-- Procedure DEL_HR_EMP이(가) 컴파일되었습니다.
EXEC DEL_HR_EMP(402);
EXEC DEL_HR_EMP(401);
SELECT *
FROM hr_emp;
-- 부서코드 컬럼 추가
ALTER TABLE hr_emp
ADD dept_id NUMBER(4)
-- 부서배치 프로시저를 등록하고, 300번 사원을 50번 부서로 301번 사원을 80번 부서로 부서배치 하시오
-- ex> MOVE_HR_EMP(사번, 대상부서)
UPDATE hr_emp
SET dept_id = 50
WHERE emp_id = 300;
ROLLBACK;
SELECT *
FROM hr_emp;
CREATE OR REPLACE PROCEDURE MOVE_HR_EMP (
deptid NUMBER,
empid NUMBER
) IS
BEGIN
UPDATE hr_emp
SET dept_id = deptid
WHERE emp_id = empid;
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('========= 예외발생, 관리자 확인바람 ========');
ROLLBACK;
END;
-- Procedure MOVE_HR_EMP이(가) 컴파일되었습니다.
EXEC MOVE_HR_EMP(50, 300);
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
EXEC MOVE_HR_EMP(80, 301);
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM hr_emp
ORDER BY 1 DESC;
-- 2) 함수(FUNCTION) : RETURN 유무
-- ※ 기본 함수 이외의 추가 기능의 함수가 필요할때 사용 ==> 반환값
-- 2-1) 함수 정의 : ex. SUM(컬럼명) ..
-- ※ 프로시저 정의
CREATE OR REPLACE PROCEDURE 프로시저명 (매개변수) IS[AS]
BEGIN
END;
-- 그룹함수 : SUM(), AVG(),...
-- 업무에서 필요한 처리함수 (그리고 반환값) : BONUS(사원번호)
CREATE OR REPLACE FUNCTION 함수명(매개변수1 자료형1, 매개변수2 자료형2,...)
RETURN 리턴자료형 IS
-- 추가 변수
-- 추가 상수
BEGIN
-- 처리로직 : ex. 보너스 = 반올림함수(월급여 x 거래수수료율, 자릿수)
RETURN 리턴값;
END;
-- Q. 사원의 번호를 입력하면, 사원의 월급여를 반환하는 함수를 만든다고 하면?
CREATE TABLE hr_emp2 AS
SELECT employee_id AS emp_id,
last_name AS lname,
salary AS sal,
email AS mail,
job_id AS jobid,
department_id AS dept_id
FROM hr.employees
WHERE employee_id BETWEEN 100 AND 110;
-- Table HR_EMP2이(가) 생성되었습니다.
DESC hr_emp2;
CREATE OR REPLACE FUNCTION getSalary(empid NUMBER)
RETURN NUMBER IS
nSalary NUMBER(8,2);
-- 추가변수 선언 및 초기화, 실행부에서 초기화만 할 수 있음.
-- 추가상수 CONSTANT 선언 및 초기화를 선언부에서 진행.
BEGIN
nSalary := 0;
SELECT sal
INTO nSalary
FROM hr_emp2
WHERE emp_id = 100;
RETURN nSalary;
END;
-- Function GETSALARY이(가) 컴파일되었습니다.
SELECT lname,jobid, dept_id, getSalary(100) AS salary
FROM hr_emp2
WHERE emp_id = 100;
-- Q. 보너스를 조회하는 함수 ex.get_bonus(emp_id)
-- hr_emp3 : sal, commission_pct 포함되어야 함.
CREATE OR REPLACE FUNCTION get_bonus (empid NUMBER)
RETURN NUMBER IS
nBonus NUMBER(6); -- n: Number Type, Bonus: What to Do
nSalary NUMBER(6);
nCommission NUMBER(6)
BEGIN
SELECT sal,
END;
-- 2-2) 함수 호출 : SELECT 절, WHERE 절...
SELECT *, BONUS(employee_id)
FROM employees;