테이블 컬럼(스네이크케이스)을 자바 필드(카멜 케이스)로 바꾸는 쿼리

SELECT 
  'private ' || 
  (CASE A.DATA_TYPE
           WHEN 'NUMBER'
           THEN 'Integer'
           WHEN 'DATE'
           THEN 'Date'
           WHEN 'VARCHAR'
           THEN 'String'
           WHEN 'CHAR'
           THEN 'String'
           ELSE ''
       END
    ) || 
   ' ' || 
   LOWER(SUBSTR(A.CAMEL_CASE,1, 1)) || 
   SUBSTR(A.CAMEL_CASE,2) || 
   ';'AS RESULT
FROM (
  SELECT 
    COLUMN_NAME, 
    DATA_TYPE,
    REPLACE(initcap(COLUMN_NAME),'_') AS CAMEL_CASE
   FROM ALL_TAB_COLUMNS
   WHERE TABLE_NAME= '테이블명'
   ORDER BY COLUMN_ID
)A;

계정 생성

create user recruit identified by recruit;
grant connect, resource, dba to recruit;

SQL

테이블 생성

create table members (id varchar2(100) primary key,
                      password varchar2(100) not null,
                      role number(2) constraint role_ck check(role in (0,1)));

DDL

---- alter
-- 칼럼 추가
alter table members add phone_num number(11);

-- 칼럼명 변경
alter table members rename column id to member_id;

-- 칼럼 타입 변경
alter table members modify member_id varchar2(50);

-- 칼럭 삭제
alter table members drop column phone_num;     

추후 정리 예정

------ DDL
---- create
-- 테이블 생성
create table members(id varchar2(30) primary key,
                    pwd varchar2(30) not null,
                    name varchar2(30) not null,
                    gender varchar2(3) constraint gender_ck check (gender in ('M', 'F')));
                    
create table members(id varchar2(30),
                    pwd varchar2(30) not null,
                    name varchar2(30) not null,
                    primary key (id),
                    foreign key (name) references messages(user_name));

create table messages(id varchar2(30) primary key,
                      content varchar2(1000),
                      send_date date,
                      user_id varchar2(30) constraint name_fk references members (id));
                      
drop table messages;
drop table members;
                      
desc messages;

-- 뷰 생성
create view memeber_vw
as 
select *
from members 
where id between 0 and 10;

-- 인덱스 생성
create index name_idx on members(name);

-- 시퀀스 생성
create sequence id_seq
    increment by 1
    start with 1
    minvalue 1
    maxvalue 9999
    nocycle
    nocache;

---- alter
-- 칼럼 추가
alter table members add phone_num number(11);

-- 칼럼명 변경
alter table members rename column id to member_id;

-- 칼럼 타입 변경
alter table members modify member_id varchar2(50);

-- 칼럭 삭제
alter table members drop column phone_num;                   

------ DCL
-- grant
grant select on members to hr;

-- revoke
revoke select on members from hr;

------ DML
-- insert
insert into members
values('user1', 'userA', '1234', 'M');

insert into members(member_id, pwd, name)
values('user2', 'userB', '1234');

-- delete
delete from members;

delete from members where member_id = 'user1';

rollback;

-- update
update members set pwd = '1111' where member_id = 'user1';

update members set gender = 'F' where member_id = 'user2';

select * from members;

-- select 
select department_id, count(*), avg(salary)
from employees
group by department_id;
                    
select department_id, count(*), min(salary) as 최저연봉, max(salary) as 최대연봉
from employees
group by department_id
having avg(salary) > 10000;

프로그래머스 SQL 입양시각 구하기(1)