옛날 문서가 보여 그냥 올려 둡니다.

참조만 하시기 바랍니다.

전혀 technical 문서는 아닙니다.

=========================================

stored procedure 작성법

CREATE OR REPLACE PROCEDURE change_salary
(v_emp_id IN NUMBER, -------형식 매개변수
v_new_salary IN NUMBER)
IS
BEGIN ------PL/SQL 블록의 시작
UPDATE s_emp
SET salary = v_new_salary
WHERE id= v_emp_id;
COMMIT;
END change_salary;
/

SQL 함수

숫자 함수의 예
ROUND(1869.9)
TRUNC(1869,-2)
그룹함수의 예
SUM(s_emp.salary)
AVG(s_ord.total)

기타 시스템 함수
USER -사용자명
SYSDATE -현재날자와 시각
s_dept_id.NEXTVAL -sequence(연속적인)값

함수작성 문법
CREATE OR REPLACE FUNCTION 이름
argument
RETURN datatype
IS
pl/sql block;

작성예
CREATE OR REPLACE FUNCTION tax
(v_value IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN(v_value * 0.7);
END tax;
/
주의점:함수는 하나의 형만 반환하므로 OUT과 IN OUT을 함께 쓰지 마십시요.

실행시
SQL> EXECUTE change_salary(17,1000);

SQL*PLUS에서 함수 실행예
SQL>VARIABLE x NUMBER -----return값을 저장하기 위한 global변수를 정의
SQL>EXECUTE:x:= tax(100); ----함수를 실행
SQL>PRINT x -----변수값을 확인한다.

x
----------------------------
7
SQL문에서 함수실행시 장점

SQL에서는 쉽게 쓸수 없는 계산이 가능하다.
Query 효율 증가

함수의 한계

내장 함수는 SQL문에서만 사용할 수 있다.
단일행 함수로 작용한다.
DML문 사용불가이다.
모든 argument는 IN만 허용한다.
PL/SQL의 datatype이 아닌 Oracle7 Server의 내부 datatype만 허용한다.
EXECUTE 함수의 소유자 이거나 권한이 필요하다.

사용예
SELECT total,tax(total)
FROM s_ord
WHERE id=100;

TOTAL TAX(TOTAL)
---------------------
601100.00 42077

간단한 PL/SQL블록 생성

PL/SQL블록의 구조

DECLARE -Optional -----변수의 선언과 초기화
변수,상수,커서,사용자 정의 예외처리
BEGIN -명령 -실행부에서 변수에 새로운값 지정
SQL문 -parameter를 통해 PL/SQL블록으로 값을 전달한다.
PL/SQL제어문
EXCEPTION -Optional --출력변수를 통해 결과 출력
예외처리구문
END;

PL/SQL의 변수와 상수선언 문법

Identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT 수식]

%TYPE 속성

아래사항에 따라 변수를 선언하게 해줍니다.
- 이미 선언된 다른 변수
-데이타베이스 column 정의
아래 항목뒤에 %TYPE을 붙입니다.
-데이타베이스 테이블과 column
-이미선언한 변수명
PL/SQL은 변수의 datatype과 크기를 결정한다.

%TYPE 예
DECLARE
v_last_name s_emp.last_name%TYPE;
v_first_name s_emp.first_name%TYPE;
v_balance NUMBER(7,2);
v_minimum_balance v_balance%TYPE :=10;

복합(Composite) Datatype
-PL/SQL Table
-PL/SQL RECORDS
-내부 구성요소 포함
-재사용 가능
PL/SQL TABLES
-두가지 요소로 구성
-스칼라 datatype이 BINARY_INTEGER인 primary key
-제한되어 잇지 않으므로 동적으로 크기 증가

PL/SQL TABLE의 선언 문법

DECLARE
TYPE 유형이름 IS TABLE OF scalar datatype
[NOT NULL] INDEX BY BINARY_INTEGER;
Identifier 유형이름;

예
DECLARE
TYPE name_table_type IS TABLE OF VARCHAR(25)
INDEX BY BINARY_INTEGER;
first_name_table name_table_type;
last_name_table name_table_type;

PL/SQL RECORDS
스칼라,RECORD,또는 PL/SQL TABLE datatype 중
하나이상의 요소로 구성됩니다.
3GL의 레코드와 구조가 유사합니다.
데이타베이스 테이블의 행과 동일하지 않습니다.
논리적 단위로서 필드 집합을 처리할수 있도록 해줍니다.
테이블의 행을 읽어올 때 편리합니다.

PL/SQL RECORD 선언
DECLARE
TYPE 유형이름 IS RECORD
(필드이름1 필드유형 [NOT NULL {:= | DEFAULT} 식,
(필드이름2 필드유형 [NOT NULL {:= | DEFAULT} 식,....);
Identifier 유형이름;

예
DECLARE
TYPE emp_record_type IS RECORD
(last_name VARCHAR2(25),
first_name VARCHAR2(25),
gender CHAR(1));
employee_record emp_record_type;

%ROWTYPE 속성
데이터베이스 테이블이나 뷰의 일련의 칼럼들에 대해 변수 선언
데이터베이스 테이블명을 접두어로 표기
record에 있는 필드는 테이블이나 뷰의 칼럼명과 datatype을 취함

%ROWTYPE 속성의 장점
중요한 데이타베이스 칼럼의 갯수와 datatype을 모르게 할수 있습니다.
SELECT문을 사용하여 행을 검색하거나 Explicit 커서로 여러행을
검색할때 유용합니다.

예
DECLARE
dept_record s_dept%ROWTYPE;
emp_record s_emp%ROWTYPE;

PL/SQL 블록의 문법

1.문장은 여러 행에 걸쳐 쓸 수 잇습니다.
2.어휘단위는 공백으로 분리할 수 있습니다.
3.Identifiers(변수)
-30자까지 쓸수 있다.
-반드시 영문자로 시작
-데이타베이스 테이블의 칼럼명과 같은 이름은 사용 불가.
4.문자와 날짜 리터럴에는 ''로 표시
5.숫자는 일반적인 수치나 과학적 표기법으로 표현 가능하다.
6.주석은
-여러행에 걸쳐 쓰려면 /*와 */ 기호를 사이에 쓴다.
-한행에 쓰는 주석은 라인 처음에 --을 표시한다.

변수에 값을 할당하는 방법의 예
DECLARE
v_weight NUMBER(3) := 600;
v_message VARCHAR2(255) := 'hello hahaha';
BEGIN
DECLARE
v_weight NUMBER(3) := 1;
v_message VARCHAR2(255) = 'Seoul Korea';
v_new_locn VARCHAR2(50) := 'Republic of Korea';
BEGIN
v_weight := v_weight + 1;
v_message := v_message || v_new_locn;
END;
v_weight := v_weight + 1;
v_message := v_message || 'is babu';
v_new_locn := 'Earth of' || v_new_locn;
END;

데이터 검색의 예
CREATE OR REPLACE PROCEDURE ship_date
(v_ord_id IN NUMBER)
IS
v_date_ordered s_ord.date_ordered%TYPE;
v_date_shipped s_ord.date_shipped%TYPE;
BEGIN
SELECT date_ordered, date_shipped
INTO v_date_ordered, v_date_shipped
FROM s_ord
WHERE id = v_ord_id;
END ship_date;
/

Stored Procedure를 Web Browser로 실행시키는 방법
http://주소/loginID/owa/Stored Procedure_name

변수 전달방식
get
name=value--->직접써준다. 아니면 '||variable_name ||'형식으로 쓴다.
post
input tag의 name 만약 name=abc이면 Stored Procedure의 변수들이 name이
이 되고 html tag의 name이 value가 된다.
참고> get이나post방식으로 변수를 전달할때 Stored Procedure의 변수리스트
순서와 일치해야 에러가 안난다.