공부기록

오라클 설치 후 계정을 생성하던 중 ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다. 라는 오류 메시지가 발생하였다. 오라클 12c부터는 계정앞에 c##을 붙여줘야 사용이 가능하도록 변경되었다고 한다.

오류발생

SQL> CREATE USER test IDENTIFIED BY 1234;

해결방법1

SQL> CREATE USER c##test IDENTIFIED BY test;

해결방법2

c##을 안붙이고 사용하려면 아래와 명령어를 실행 후 생성하면 된다.

SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=true;
SQL> CREATE USER test2 IDENTIFIED BY 1234;

CMD창에서 SQLPLUS로 원격지의 DB 접속을 하려면 아래와 같이 입력해주면 된다.

sqlplus id/pw@ip:port/sid

프로시저(Procedure)란?

- 오라클에서의 프로시저는 PL/SQL을 통해 생성된다. 

- 자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출하여 작업 효율을 늘린다.

- 함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램이다.

프로시저(Procedure) 문법

CREATE [OR REPLACE] Procedure [Procedure_name](
 argument1 [mode] data_type1,
 argument2 [mode] data_type2,
 )
IS [AS]
...
BEGIN
...
EXCEPTION
...
END;
/

※ 인수 값을 프로시저명 뒤에 명시, 변수 선언은 IS 뒤에 명시, 동작은 BEGIN 뒤에 명시

<인수 타입 선언부분>

1. IN : 운영체제에서 프로시저로 전달

변수이름 IN VARCHAR2; => 인수 선언 시 byte 수 지정안함

변수이름 IN 테이블이름.컬럼명%TYPE;

변수이름 IN 테이블이름.컴럼명%TYPE := 값;

변수이름 IN 테이블이름.컬럼명%TYPE DEFAULT 값;

2. OUT : 프로시저에서 운영체제로 전달

변수이름 OUT VARCHAR2;

3. INOUT : 둘다 가능

변수이름 IN OUT VARCHAR2;

프로시저(Procedure) 생성

테이블에 데이터를 입력하는 프로시저를 생성한다.

CREATE OR REPLACE PROCEDURE EXPROC(
 IN_ID IN VARCHAR2,
 IN_NAME IN VARCHAR2,
 IN_DEPT IN VARCHAR2
 )
 IS
 P_SAL NUMBER := 600;
 BEGIN
 INSERT INTO EMP(ID,NAME,DEPT,SAL)
 VALUES(IN_ID,IN_NAME,IN_DEPT,P_SAL);
 COMMIT;
 
 END EXPROC;

프로시저(Procedure) 실행

EXEC EXPROC('EMP_011','정사원','기획팀');

프로시저를 실행하면 아래와 같이 EMP 테이블에 데이터가 입력된다.

뷰(View)란?

- 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체

- 여러개의 테이블에서 필요한 정보를 뽑아 사용하거나 숨기고 싶은 정보가 존재할 경우 빼고 생성할 수 있음

- 컬럼과 데이터만 공개되므로 원천 테이블은 알 수 없음

사용방법

1. 뷰(View) 생성 및 수정

CREATE OR REPLACE VIEW [스키마.][뷰 NAME] AS
SELECT문;

2. 뷰(View) 삭제

DROP VIEW [스키마.][뷰 NAME];

3. 뷰(View) 구조확인

DESC [스키마.][뷰 NAME];

사용예제

1. 먼저 V1이라는 뷰를 생성한다.

CREATE OR REPLACE VIEW V1 AS
 SELECT A.ID,
          A.NAME,
          A.AMT,
          B.ID AS MANAGER_ID,
          B.NAME AS MANAGER_NAME
   FROM TB_TEST A JOIN TB_TEST B
   ON A.MANAGER_ID=B.ID
   ORDER BY ID;

2. 생성한 VIEW를 SELECT 한다.

 SELECT * FROM V1;