[1028 SQL]Ⅱ. SQL 기본과 활용-제3장 DML, TCL, DDL, DCL, 절차형SQL(프로시저)

제3장 관리 구문
제1절 DML
제2절 TCL
제3절 DDL
제4절 DCL

DML	Data Manipulation Language 조작어	SELECT	SELECT * FROM employees;
		INSERT	INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe')            , ('Alice', 'Johnson' ); --ORACLE INSERT ALL   INTO employees (employee_id, first_name) VALUES (1, 'John')   INTO employees (employee_id, first_name) VALUES (2, 'Jane') SELECT * FROM dual; INSERT INTO employees (employee_id, first_name) SELECT employee_id FROM temp_employees;
		UPDATE	UPDATE employees SET department = 'Marketing' WHERE employee_id = 123;
		DELETE	DELETE FROM employees WHERE employee_id = 123; DELETE FROM employees;
	산술연산자	(), * / + /
	합성연산자	||
	SQL 연산자	between a and b in () like '' is null	와일드카드 : % 1개인 단일문자 _
	논리연산자	and, or , not
	부정비교연산자	!= ^= <> not 컬럼명 =  not 컬럼명 >	1. char 유형이면 space 추가해 길이 같게한후 다른문자 나올때까지 비교  2. varchar 유형이면 서로 다른문자가 나올때까지 비교 3. 상수값 이면 상수쪽을 변수타입과 동일하게 바꾸고비교
	부정sql 연산자	not between a and b not in () is not null
	연산자 우선순위	() > not > 비교, sql비교 > and > or
	Rownum, top

TCL	Transaction Control Language 트랜젝션 제어어	COMMIT	COMMIT;
		ROLLBACK	ROLLBACK;
		SAVEPOINT	SAVEPOINT my_savepoint; ROLLBACK TO SAVEPOINT my_savepoint;
	특성	1. 원자성	모두실행 or 전혀실행안됨
		2. 일관성
		3. 고립성	다른트랜젝셩 영향 없어야
		4. 지속성

DDL	Data Definition Language 정의어	CREATE	CREATE TABLE employees (             employee_id INT PRIMARY KEY            , first_name VARCHAR(50) ); create table employees_temp as select * from employees  ; CREATE USER OT IDENTIFIED BY 1234;
		ALTER	ALTER TABLE employees ADD email VARCHAR(100); ALTER TABLE employees MODIFY COLUMN hire_date TIMESTAMP; ALTER TABLE employees RENAME COLUMN department TO dept; ALTER USER OT IDENTIFIED BY 1234; ALTER TABLE employees MODIFY email VARCHAR (200)
		DROP	DROP TABLE employees; DROP INDEX idx_department;
		TRUNCATE	TRUNCATE TABLE employees;  --테이블모든행삭제
	자주쓰이는데이터 유형	1. chartacter
		2. varchar
		3. numeric
		4. datetime
	테이블명	1. 숫자가아닌문자로시작 2. 특수문자- 혀용하지않음
	제약조건	Primary key
		Unique key
		not null
		check	입력범위
		foreign key

 

 

DCL	Data Control Language 제어어 - DBA_ROLE_PRIVS 테이블
명령어	GRANT	GRANT privilege ON object TO user;
		Role	역할 그룹화 CREATE ROLE manager_role; GRANT SELECT, INSERT, UPDATE ON employees TO manager_role; GRANT manager_role TO john_doe; DROP ROLE manager_role;
		CONNECT	데이터베이스 로그인, Session을 생성가능
			GRANT  CREATE SESSION	GRANT SESSION TO USER_NAME; - 접속권한만
		RESOURCE	- 객체 생성, 관리 - Table : SELECT, INSERT, UPDATE, DELETE, EXECUTE - view, procedure, sequence, trigger
			GRANT INSERT	GRANT INSERT, UPDATE ON employees TO user2;
			GRANT CREATE TABLE
		DBA	-  Resource, User, System Setting(성능) - All권한  SELECT, INSERT, UPDATE, DELETE, EXECUTE GRANT DBA TO USER_NAME; - 객체제어, 권한부여, 성능관리
			GRANT DROP USER TO admin_user;
		SYSDBA	- 데이터베이스 전체 제어 , 복구, 시스템수준작업 GRANT SYSDBA TO USER_NAME; + 인스턴스시작중지, 모든작업
	REVOKE	REVOKE privilege ON object FROM user;
		REVOKE INSERT ON employees FROM user2;
유저 (ORACLE)	SYS	DBA  ROLE을 부여받은 유저
	SYSTEM	데이터베이스의 모든 시스템 권한을 부여받은 DBA 유저 Oracle 설치 완료시에 패스워드 설정

 

Object 에대한 권한부여	테이블	views	sequence	procedure
alter	table		sequence
delete	table	views		procedure
execute
index	table
insert	table	views
references	table
select	table	views	sequence
update	table	views

 

 

 

절차형SQL
Oracle PL/SQL	실행	- 프로시저, 함수, 트리거 사용 PL/SQL Block 프로그램을 입력받아 SQL 문장과 (SQL statement executer 처리) 프로그램문장(PL/SQL 엔진) 구분처리
	구조	Declare - 선언부(변수,상수) - 필수 Begin - 실행부 -필수 exeception - 예외처리부- 선택 END - 종료 - 필수
	예시	-- 저장 프로시저 생성 -- 저장 프로시저 생성 CREATE OR REPLACE PROCEDURE CalculateAvgSalary (     p_department_id IN NUMBER,       -- 입력 파라미터: 부서 ID     p_avg_salary OUT NUMBER          -- 출력 파라미터: 평균 급여 ) AS     -- 지역 변수 선언     v_avg_salary NUMBER;            -- 평균 급여 저장 변수     v_department_name VARCHAR2(50); -- 부서 이름 저장 변수 BEGIN     -- 부서 이름을 전역 변수로부터 가져오기     v_department_name := EmployeePkg.g_department_name;     -- 평균 급여 계산     SELECT AVG(salary)     INTO v_avg_salary     FROM employees     WHERE department_id = p_department_id;     -- 출력 파라미터에 결과 할당     p_avg_salary := v_avg_salary;     -- 부서 이름과 평균 급여 출력 (디버깅용)     DBMS_OUTPUT.PUT_LINE('Department Name: ' || v_department_name);     DBMS_OUTPUT.PUT_LINE('Average Salary: ' || v_avg_salary); END; / -- 저장 프로시저 삭제 DROP PROCEDURE CalculateAvgSalary; /
SQL Server T-SQL		프로시저, 함수, 트리거등 사용.  @@전역변수 @지역변수
	예시	-- 저장 프로시저 생성 CREATE PROCEDURE GetEmployeeDetails     @EmployeeID INT,                 -- 입력 파라미터     @Name NVARCHAR(50) OUTPUT,      -- 출력 파라미터     @Salary DECIMAL(10, 2) OUTPUT   -- 출력 파라미터 AS BEGIN     SELECT @Name = Name, @Salary = Salary     FROM Employees     WHERE EmployeeID = @EmployeeID; END;

 

바인드변수		변수값을 동적으로 할당 1. 쿼리재사용- 캐싱 2. SQL injection 방지
예	PL/SQL 블록	v_variable :=500;	: 기호를 사용하여 선언
	프로시저	CREATE OR REPLACE PROCEDURE GetEmployeesAboveSalary ( p variable  IN NUMBER -- 바인드 변수 (입력 파라미터) ) AS 호출: BEGIN     -- 저장 프로시저 호출, p_salary_threshold에 값 바인딩     GetEmployeesAboveSalary(6000); END; /
	T-SQL 블록	@ variable DECIMAL(10, 2)  =500;
	프로시저	CREATE PROCEDURE GetEmployeesAboveSalary @SalaryThreshold DECIMAL(10, 2) -- 바인드 변수 (입력 파라미터) AS 호출: EXEC GetEmployeesAboveSalary @SalaryThreshold = 6000;
주의	Peeking	변수값 확인후 실행계획을 세우는데 성능은 향상되나 자주 변수값이 변경되면 실행계획 비효율 - 기본값은 비활성화 대부분
	Snipping	캡쳐기능

 

	프로시저	특징
Static SQL	-- Static SQL 예제 BEGIN     -- SQL 문이 프로그램 컴파일 시 결정됨     INSERT INTO Employees (EmployeeID, Name, Salary)     VALUES (1, 'Alice', 5000); END; /	SQL 컴파일시 정적결정 성능좋음, 보안강화 SQL문이 변하지 않는경우 적합
Dynamic SQL	DECLARE     v_sql VARCHAR2(1000);     v_name VARCHAR2(50) := 'Alice'; BEGIN     -- Dynamic SQL 예제     v_sql := 'INSERT INTO Employees (EmployeeID, Name, Salary) VALUES (1, :name, 5000)';     EXECUTE IMMEDIATE v_sql USING v_name; END; /	SQL 문 실행시 동적생성 유연성, 다양한 쿼리 처리가능.  성능저하, SQL 인젝션취약점