'데이터베이스/PL/SQL'에 해당되는 글 19건

 create or replace procedure my_proc as
     begin
     dbms_output.put_line ( 'Hello World' );
     end my_proc;
    /

my_proc 프로시저네임으로 생성

출력문은 hello world

이후에

begin

my_proc;

end;

/

my_proc;프로시저만 불러옴

declare
    first_variable varchar(20):= 'first block';
    begin
        dbms_output.put_line(first_variable);

declare
    second_variable varchar(20) := 'second block';
    begin
        dbms_output.put_line(second_variable);

end;

end;

결과는 first block

          second block

이 나온다.

 declare
      myNumber number := 1;
    begin
      myNumber := 1 + 1;
      dbms_output.put_line( '1 + 1 = ' || to_char( myNumber ) || '!' );
    exception
      when others then
        dbms_output.put_line( 'We encountered an exception!' );
    end;
   /

결과는 1+1 = 2! 라고나온다

put_line에서 텍스트끼리 구분은 ||로 한다.

declare

선언부로써 변수명 자료형 := 1;

//값을 초기화할때 := 를 이용

begin

실행부에서 선언된 변수를 불러와 간단한 계산식을 사용할수있다.

SQL>
SQL>
SQL> select PRODUCT_NAME,
  2         to_char(PRODUCT_PRICE, '$9,999.00') "Price",
  3         to_char(QUANTITY_ON_HAND, '999,999') "On Hand",
  4         '  '|| to_char(LAST_STOCK_DATE, 'MON DD, YYYY') "Last Stocked"
  5  from   PRODUCT;

// product테이블에 product_price를price로 컬럼머리글을 지정하고 컬럼에 값에대한 포맷형식을 $9,999,00으로 지정했다. 나머지도 같은방식

PRODUCT_NAME              Price      On Hand  Last Stocked
------------------------- ---------- -------- --------------
Small Widget                  $99.00        1   JAN 15, 2003
Medium Widget                 $75.00    1,000   JAN 15, 2002
Product Number                $50.00      100   JAN 15, 2003
Round Church Station          $25.00   10,000

SQL>
SQL>
SQL>
SQL> select ename, sal, sal * 1.1 from emp;

컬럼 머리글이 ENAME ,SAL,SAL*1.1이 그대로나온다.

ENAME             SAL    SAL*1.1
---------- ---------- ----------
SMITH             800        880
ALLEN            1600       1760
WARD             1250       1375
JONES            2975     3272.5
MARTIN           1250       1375
BLAKE            2850       3135
CLARK            2450       2695
SCOTT            3000       3300
KING             5000       5500
TURNER           1500       1650
ADAMS            1100       1210

ENAME             SAL    SAL*1.1
---------- ---------- ----------
JAMES             950       1045
FORD             3000       3300
MILLER           1300       1430

14 rows selected.

SQL>
SQL> select ename, sal, sal * 1.1 raise from emp;

아까와비슷 하지만 sla*1.1에 raise라는 머리글을 지정해줌

ENAME             SAL      RAISE
---------- ---------- ----------
SMITH             800        880
ALLEN            1600       1760
WARD             1250       1375
JONES            2975     3272.5
MARTIN           1250       1375
BLAKE            2850       3135
CLARK            2450       2695
SCOTT            3000       3300
KING             5000       5500
TURNER           1500       1650
ADAMS            1100       1210

ENAME             SAL      RAISE
---------- ---------- ----------
JAMES             950       1045
FORD             3000       3300
MILLER           1300       1430

14 rows selected.

SQL>
SQL>
SQL> column ename   heading "Employee"
SQL> column sal     heading "Salary"
SQL> column raise   heading "Raise"
SQL>
SQL> select ename, sal, sal * 1.1 raise from emp;

기존에 컬럼 머리글마다 새로 지정해줬다.

Employee       Salary      Raise
---------- ---------- ----------
SMITH             800        880
ALLEN            1600       1760
WARD             1250       1375
JONES            2975     3272.5
MARTIN           1250       1375
BLAKE            2850       3135
CLARK            2450       2695
SCOTT            3000       3300
KING             5000       5500
TURNER           1500       1650
ADAMS            1100       1210

Employee       Salary      Raise
---------- ---------- ----------
JAMES             950       1045
FORD             3000       3300
MILLER           1300       1430

14 rows selected.

SQL>
SQL> drop table emp;

Table dropped.

SQL>

레코드란?

Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc.  Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.

레코드 선언 

To declare a record, you must first define a composite datatype; then declare a record for that type.

일반적인 구문에 복합데이터유형 선언

TYPE record_type_name IS RECORD

(first_col_name column_datatype,

second_col_name column_datatype, ...);

record_type_name – 정의한 복합유형에 이름

first_col_name, second_col_name, etc.,- 레코드 내에 필드와 열 이름

column_datatype 은 필드에 스칼라데이터타입유형을 정의한다.

There are different ways you can declare the datatype of the fields.

1) You can declare the field in the same way as you declare the fieds while creating the table.
2) If a field is based on a column from database table, you can define the field_type as follows:

col_name table_name.column_name%type; 

다음의 코드는 사용자정의유형 기반에 employee_rec 기록하고 선언하는 방법을 보여준다.

DECLARE 

TYPE employee_type IS RECORD 

(employee_id number(5), 

 employee_first_name varchar2(25), 

 employee_last_name employee.last_name%type, 

 employee_dept employee.dept%type); 

 employee_salary employee.salary%type;

 employee_rec employee_type; 

만약 모든 필드가 테이블 열 기반으로 기록 됬다면, 기록하고 선언 할 수 있는 방법은 다음과 같다.

record_name table_name%ROWTYPE; 

예를들어, 위 employee_rec을 선언 하는 방법은 다음과같다.

DECLARE 

 employee_rec employee%ROWTYPE; 

RECORD를 ROWTYPE으로 선언 했을 떄 장점

1. 모든 테이블안에 열대한 변수선언을 명시적으로 할 필요없다.

2. 데이터베이스 테이블안에 열사양을 변경하려고 할때, 코드를 업데이트 할 필요가 없다.

RECORD를 ROWTYPE으로 선언 했을 때 단점

1. RECORD에 ROWTYPE생성 시 , 필드 생성에대한 테이블안에 모든 열과 모든필드에 대한 데이터유형 생성에 사용된 메모리. 그래서 프로그램 안에있는 테이블에 모든 열이 사용 될때만 ROWTYPE을 사용.

RECORD생성 시, 데이터유형을 항상 생성하고, 변수를 생성하는 것과 비슷하다. 레코드는 사용할때 값을 지정해야된다 필요한만큼.

상수선어에 일반적인 구문
constant_name CONSTANT datatype := VALUE; 
 
For example, to declare salary_increase, you can write code as follows: 
 
DECLARE 
salary_increase CONSTANT number (3) := 10; 
 
 
You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error. 
 
DECLARE 
 salary_increase CONSTANT number(3); 
BEGIN 
 salary_increase := 100; 
 dbms_output.put_line (salary_increase); 
END; 

변수선언하기

variable_name datatype [NOT NULL := value ];

알아둘것들

variable_name은 변수이름

데이터유형은 PL/SQL에 유요한 데이터 유형

NOT NULL은 변수의 선택사양

value 또는 기본값 또한 선택사양이고, 어디서든 변수를 초기하할수있다.

각 변수선언은 별도의 성명과 세미콜론으로 종료해야한다.

사원의 현재급여를 저장하려면 아래와같이

DECLARE

salary  number (6);

"salary"라는 변수의 데이터유형은 number고 길이는 6이다.

언제 변수를 NOT NULL로 지정하고 , 변수를 선언하고 언제 초기화해야되는지

아래 두개변수가 선언되었고, 그중 하나는 NOT NULL이다.

DECLARE

salary number(4);

dept varchar2(10) NOT NULL := “HR Dept”;

PL/SQL BLOCK에서 실행 또는 예외영역안에서 값과 변수가 바꿀수 있다. 
변수에값을 지정하는 2가지 방법을 아래 제시한다.
 
직접 변수의 값을 지정할수 있다.
일반적인 구문은  variable_name:=  value;
 
변수의 직접 지정한 값으로부터 데이터베이스 열로 SELECT INTO문을 사용할수있다.
일반적인 구문으로
 
SELECT column_name
INTO variable_name 
FROM table_name 
[WHERE condition]; 
 
 
예제: 아래 프로그램은 사원의 급여를 얻어와서 아이디가 1116인 것들을 화면에 표시해준다.
 
 
DECLARE 
 var_salary number(6); 
 var_emp_id number(6) = 1116; 
BEGIN
 SELECT salary 
 INTO var_salary 
 FROM employee 
 WHERE emp_id = var_emp_id; 
 dbms_output.put_line(var_salary); 
 dbms_output.put_line('The employee ' 
  	|| var_emp_id || ' has  salary  ' || var_salary); 
END; 
/
 
이 역슬래쉬는 위 PL/SQL블록에 위 프로그램으르 실행하기 위해 표시한다.