RSS구독하기:SUBSCRIBE TO RSS FEED
즐겨찾기추가:ADD FAVORITE
글쓰기:POST
관리자:ADMINISTRATOR
'Web_developing/Oracle'에 해당되는 글 5
저장프로시저
- 저장 프로시저는 하나 이상으로 구성된 Transact-SQL 문을 데이터베이스에 저장한 개체입니다.

저장프로시저 특징
  • 모듈 프로그래밍
    자주 반복해서 사용하는 T-SQL문을 DB에 저장해 필요한 시점에만 사용함.
    매번 같은 구문을 다시 작성할 필요가 없음
  • 유연한 보완관리
    데이터 조회하는 저장프로시저. 접근권한이 없어도 저장프로시저를 실행할 권한이 있다면 조회가능
  • 네트워크 트래픽 감소
    쿼리전체를 서버로 전송해서 작업하는 것이 아닌 저장 프로시저와 매개변수값만을 전달함으로 데이터량이 작음
  • 빠른실행
    저장프로시저는 실행후 쿼리 실행계획을 메모리에 저장 > 저장된 실행계획 사용 > 구문분석이나 최적화 과정 거치지 않아서 더 빠른 실행을 할수 있고 캐시에 없더라도 구문분석, 표준화등의 작업을 하지 않기에 성능이 빠름

저장프로저의 구성요소
저장 프로시저명, 매개변수들, SQL문, 결과값 반환의 반환값

저장프로시저의 종류
  • 확장프로시저 : C와 같은 언어를 이용해서 구현한 프로시저, master DB에만 추가가능
  • 사용자 정의 저장 프로시저 : T-SQL문을 이용해 저장 프로시저로 구현
  • 시스템 저장 프로시저 : SQL서버관리를 위해 시스템에서 제공해주는 저장 프로시저
    sys의 스키마로 나타남, 데이터베이스 명 필요없이 시스템 저장 프로시저명을 통해서 실행가능

    Sp_who, sp_who2 : 사용자 정보
    Sp_lock : lock 정보
    Sp_help : 지정한 개체 정보
    Sp_helpdb : 지정한 DB정보
    Sp_configure : SQL 서버 설정변경
  • 임시 저장프로시저
    T-SQL 문 또는 일괄처리의 실행계획을 재 사용하지 않던 이전 버전의 방식
    사용자 정의 프로시저와 동일하게 작성하지만, 저장프로시저 명을 #으로 하면 임시저장프로시저가 됨
    SQL Server 2005에서는 T-SQL문과 일괄처리의 실행계획을 재 사용할수 있음으로 임시저장프로시저 사용이 거의없음
  • CLR 저장 프로시저
    T-SQL에서 부족한 프로그래밍 부분을 CLR 저장 프로시저를 통해 T-SQL 저장 프로시저보다는 더 강력한 구조적 프로그래밍이 가능

저장 프로시저의 생성

CREATE PROCEDURE usp_withANumber <- 소문자 : 스키마. 저장 프로시저명
@ EmployeeID INT <- 매개변수, 데이터 형식
AS
SELECT ANumber, AContent, ManagerID
FROM HumanResource.analysisDate
WHERE ManagerID = @ EmployeeID
GO

EXECUTE usp_withANumber

GO
* 임시 저장프로시저의 생성은
CREATE PROCEDURE #usp_withANumber <- 소문자 : 스키마. 저장 프로시저명앞에 샾
* 저장프로시저 생성시, SP_ 접두사는 시스템저장프로시저와 혼란을 줄수 있음으로 사용금지
* 그룹화면 저장프로시저 삭제시 개별적으로 하나씩 삭제할수 없음으로 가급적 사용하지말것
- 그룹화하기 : 같은 저장 프로시저명에 ; 하고 숫자를 매김

CREATE PROCEDURE usp_withANumber;1
AS
[ T-SQL구문 ]
GO

CREATE PROCEDURE usp_withANumber;2
AS
[ T-SQL구문 ]
GO

* 다른 사용자의 접근막기위해서는 CREATE아래쪽에 WITH ENCRYPTION을 사용함
시스템뷰에서 저장프로시저의 텍스트가 나타나지 않음. 암호화된 저장프로시저의 내용은 다시 확인할수 있는 방법이 없음으로 암호화되기전의 저장프로시저를 잘 보관해야함



저장 프로시저의 수정

ALTER PROCEDURE 스키마. 저장프로시저명.
@ 매개변수 데이터 형식
AS
[ 변경된 SQL문 ]



저장 프로시저의 삭제

DROP PROCEDURE 스키마.저장프로시저명;



기본값을 지니는 매개변수의 사용과 output사용

CREATE PROCEDURE usp_withANumber
@EmployeeID INT = 10 <- EmployeeID에 기본값을 지정한 것
@outvalue int output <- outvalue에 output 변수를 쓰겠다는것
@currency_cursor CURSOR VARYING OUTPUT <- output 매개변수로 커서를 사용할때
AS
SELECT @outvalue = ManagerID <- 관리자 ID를 output 매개변수에 설정
FROM HumanResource.analysisDate
WHERE ManagerID = @ EmployeeID
GO

DECLARE @ManagerID INT; <- 저장프로시저 output 매개변수에 반환하는 값저장을 위한 지역변수선언

EXECUTE usp_withANumber 20, @ManagerID output <-반환되는값 조회

GO
2010/02/17 17:50 2010/02/17 17:50
이 글에는 트랙백을 보낼 수 없습니다
원문 : http://www.oracle.com/technology/tech/linux/install/xe-on-kubuntu.html

1. 먼저 apt-get 저장소를 연다.
$ sudo gedit /etc/apt/sources.list
2. sources.list에 다음을 추가한다.
deb http://oss.oracle.com/debian unstable main non-free
3. 인증키를 얻고 오라클 XE를 설치한다.
$ wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle  -O- | sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get install oracle-xe
 여기까진 쉽다. 만약에 설치 중에 스왑 파티션이 부족하다고 할 수 있다. 오라클 XE는 최소 1G이상의 스왑 파티션을 요구한다. 나는 이것 때문에 포맷을 두 번이나 하고 우분투를 설치했다. -_-;;
 
 근 데 원문 6번 글을 보면 스왑 파티션을 설정하는 방법이 나온다. 직접 해보지는 않았지만 원문 작성자도 별로 추천하지 않는단다. ㅡ.,ㅡ 그냥 포맷하고 반드시 수동으로 해서 스왑 파티션을 잡아준다. 1G라고 해서 1024이런 식으로 했다가는 또 다시 포맷해야하는 불상사가 생길 수 있다. 적당히 1200정도로 잡아줘야 이상 없다. 이것 때문에 두 번이나 다시 깔았다. ㅠ.ㅠ

4. 아무튼 스왑 파티션에 이상이 없으면 터미널에서 다음과 같이 실행하여 root 로 로그인 한다.
$ su -
 아마 패스워드를 입력하라고 할 것이다. 우분투를 처음 설치한 상황에서는 root의 비밀번호가 설정이 되어 있지 않다. 설정하는 방법은 간단하다.
$ sudo bash
# passwd
    또는
$ sudo passwd
 이 명령으로 root 계정 비밀번호를 설정하고 root로 로그인한다. root 비밀번호를 설정하지 않고 그냥 하려면 아래와 같이 입력하고 그 상태에서 작업한다.
$ sudo bash
    또는
$ sudo -i
5. 이제 root 권한 상태에서 다음 명령을 실행한다.
# /etc/init.d/oracle-xe configure
 그럼 아래와 같은 설정 화면이 나올 것이다.
Oracle Database 10g Express Edition Configuration
-------------------------------------------------
Specify the HTTP port that will be used for Oracle Application Express [8080]: Enter
Specify a port that will be used for the database listener [1521]: Enter

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration: Enter

Confirm the password: 비밀번호 입력

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]: y

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done    Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"
6. 이제 설정이 끝났다. http://127.0.0.1:8080/apex 로 이동해서 Username은 "system", Password는 위에서 입력한 비밀번호로 로그인한다.



---- 여기서 부턴 웅자 --- config



난 server 기 때문에 로컬에서 접속이 불가능하다 -_-;; 장난하나 ;;;;
브라우져를 어케 까나;;;
결론은 외부로 접속을 시켜야되는데.
어쩔수 없이 sqlplus 를 쓰자고 생각했다.

** root 로 생각한다 **

*. 루트 쉘 환경변수 추가
# cd ~
# vi .bashrc
source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

*. 환경변수 설정
# cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
# ./oracle_env.sh

*. 114: [[: not fount 에러시 쉘 변경
# vi nls_lang.sh
- 첫줄의 #!/bin/sh -> #!/bin/bash 로 변경

그리고 또 위와 같이 했는데
LC TYPE... 어쩌구 저쩌구 나오면

$ apt-get install language-pack-ko
$ locale-gen ko_KR.EUC-KR (root로)
$ dpkg-reconfigure locales
해서 재설정해주고. 해봐라 .. 난 됐다 ...
아참 root 에 .profile 에 LANG=C 이딴게 있는데 이거 다 주석 체크 해버려라.


1. oracle 계정에 .bash_profile , 그리고 /usr/sbin/apachectl 이부분에 .

아래와 같은 허접을 껴둔다.
# oracle path
source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
# oracle path end...

아차 맨위에는 무조건 #/bin/bash 를 선언해줘야 된다 . !

후에...

chmod -R 755 $ORACLE_HOME/sqlplus

chmod 755 $ORACLE_HOME/nls

chmod 755 $ORACLE_HOME/nls/data
chmod 744 $ORACLE_HOME/nls/data/*
chmod 755 $ORACLE_HOME/nls/data/lx1boot.nlb
chmod 755 $ORACLE_HOME/oracore
chmod 755 $ORACLE_HOME/oracore/zoneinfo
chmod 755 $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

이렇게 해주고!!!!

그리고 su - oracle 로 접속을 한뒤

sqlplus 오나클/비밀번호@XE or sqlplus system 후 비밀번호 치던지...
이렇게만 하면 잘 접속 된다.

==================================================================
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE); <= 이거 명령어다.

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>
===================================================================

우왕ㅋ굳ㅋ 이제 외부에서 접속 가능 !
보안이 후달리니. 설정할때만 잠깐 올리고 다시 닫자 .
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(TRUE);

자 ~ 외부에서 접속이 될꺼다 ~ ㅋㅋㅋ 아이피:8080/apex 우왕ㅋ굳ㅋ

그럼 ~~ 이번건은 php 에 연동해보자.


1. apt-get install 로 php-pear패키지와 php5-dev패키지를 설치한다.
2. pecl install oci8 한다. (난 /usr/lib/php5/20060613+lfs/oci8.so 여기에 생겨뜸...)
3. /etc/php5/apache2/php.iniextention=oci8.so 혹은 vi /etc/php5/conf.d/oracle.ini 하고 옆에 extention=oci8.so 추가해줘라...그리고 phpinfo(); 로 oci8관련된 항목이 있는지 확인한다.
4.난 이렇게 그냥 검사해봤당.

<?

   //echo PHPINFO();
    $db = "//127.0.0.1/xe";
    $c1 = ocilogon("system","비밀번호",$db);

    echo $c1;

    exit;
?>

잘나오던데 ;;; 난 연동 끝이었다.
2008/10/09 12:36 2008/10/09 12:36
이 글에는 트랙백을 보낼 수 없습니다
오라클을 통해 작업할 시
insert시점에서 정상적으로 인덱싱이 되지 않는 경우가 발생한다.
특히 결합인덱스를 많이 사용하고 있는 경우 발생될 확률이 높다.
 
이런경우 오라클의 Analyzed를 통해서 해결이 가능하고
어느정도의 실행속도를 향상 시킬 수있다.
(실제 오라클사에서도 3개월에 한번씩은 Analyze를 실행하라 권고하고 있다.)
 
[Analyzed 확인 방법]
         select table_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_tables
         select index_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_indexes
 
ex) select table_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_tables;
TABLE_NAME                       NUM_ROWS TO_CHAR(
------------------------------ ---------- --------
ABS_TYPE                               38 20040101
ANNIVERS                              183 20040101
APPRFLDRHISTORY                       570 20040101
APPRFOLDER                          16885 20040101
APPRFOLDER_ERR                       3670 20040101
APPRFORM                              359 20040101
.
.
.
USR_INFO_ADMIN                          0 20040101
VAR_DEPT_INFO                           0 20040101
VIEW_TYPE                               0 20040101
WASTEBOX                                0 20040101
ZIP_CODE                            44195 20040101
252 rows selected.
 
※ 참고 : desc user_tables 에서 보통 num_rows 로도 확인 가능
             
 
[특정 Table만 Analyze 하는 방법]
 
analyze table document compute statistics
ex) DOCUMENT Table 만 Analyze
 
analyze index xpkdocbox compute statistics
ex) XPKDOCBOX Index 만 Analyze
 
[전체 Table Analyze 하는 간단한 방법]
 
1. vi analyze_all.sql
    select 'analyze table || table_name || estimate statistics;' from user_tables
 
2. @analyze_all.sql
 
3. set heading off
     set echo off
     set feedback off
     set pagesize 300  (line 이 300 미만일 경우)
     spool analyze_table.sql
     /
     spool off
 
4. vi analyze_table.sql
    필요없는 Line 제거 및 정리
 
5. @analyze_table.sql
 
[전체 Index Analyze 하는 간단한 방법]
 
1. vi analyze_all.sql
    select 'analyze index || index_name || estimate statistics;' from user_indexes
 
2. @analyze_all.sql
 
3. set heading off
     set echo off
     set feedback off
     set pagesize 300  (line 이 300 미만일 경우)
     spool analyze_index.sql
     /
     spool off
 
4. vi analyze_index.sql
    필요없는 Line 제거 및 정리
 
5. @analyze_index.sql
2008/09/03 20:22 2008/09/03 20:22
이 글에는 트랙백을 보낼 수 없습니다

출처 :

http://comeng.andong.ac.kr/%7Echi23/bbs/view.php?id=downdoli_db&page=1&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=hit&desc=desc&no=2

 

보통 my-sql에서는 PK로 잡고 auto-increment를 많이 사용하는데
이를 오라클 DB로 마이그레이션 하면서 다음과 같이 한다.....

원본 my-sql 쿼리:
select no, gno, ono, nested, id, name, title, registerDate, readno ]
from tableName
order by gno desc, ono asc limit 0, 15

오라클로 변환된 쿼리
select *
from (select a.*,rownum rnum from
(select * from table_notice order by gno desc, ono asc) a)
where rnum >  페이지당 리스트 수* ( 원하는페이지 번호 - 1) and rownum <= 페이지당 리스트 수

rownum으로 번호를 매기는 것이 order by보다 우선 순위가 높단다.
그리고 위의 방식 말고도 인덱스를 이용한 방법도 있는데..

뭐 이것도 쓸만하지만 더 쓸만한건 ;;;; 내가 품고 이찌롱 ^_^

2007/09/27 17:41 2007/09/27 17:41
이 글에는 트랙백을 보낼 수 없습니다
출처 블로그 > The Secret To Success Is To Never Give Up
원본 http://blog.naver.com/nkmin80/140032103146

※ 인덱스란?

  인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서,
오라클 데이터베이스 테이블내의
원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조
입니다.  

자동 인덱스 : 프라이머리 키 또는 uinque 제한 규칙에 의해 자동적으로 생성되는 인덱스 입니다.

수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스들 입니다.  

※  Index를 생성하는 것이 좋은 Column

WHERE절이나 join조건 안에서 자주 사용되는 컬럼
null 값이 많이 포함되어 있는 컬럼
WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들


※  다음과 같은 경우에는 index 생성이 불필요 합니다.
table이 작을 때
테이블이 자주 갱신될 때

※  오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있습니다.

  B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 냅니다.

이 알고리즘 원리는

 ① 주어진 값을 리스트의 중간점에 있는 값과 비교합니다.    
     만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다.
     만약 그 값이 더 작다면 위쪽 반을 버립니다.

 ② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도
     반복합니다.



 ※  인덱스는 B-tree 구조를 가지며 크게 다음 네 가지로 분류될수 있습니다.


Bitmap 인덱스

  비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동합니다.
  그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킵니다.
  테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있습니다.

SQL>CREATE BITMAP INDEX emp_deptno_indx
        ON emp(deptno);


Unique 인덱스

  Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.
  프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스입니다.

SQL>CREATE UNIQUE INDEX emp_ename_indx
        ON  emp(ename);


Non-Unique 인덱스

   Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있습니다.

SQL>CREATE INDEX  dept_dname_indx
        ON  dept(dname);


결합 (Concatenated(=Composite)) 인덱스

   복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개입니다

SQL>CREATE UNIQUE INDEX emp_empno_ename_indx
        ON  emp(empno, ename);


※  인덱스의 삭제

 
 - 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지
않습니다.

 - 인덱스를 삭제하려면 INDEX의 소유자이거나 DROP ANY INDEX권한
을 가지고 있어야 합니다.


 - INDEX는 ALTER를 할 수 없습니다.


SQL>DROP INDEX emp_empno_ename_indx ;


※  인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수
      있습니다.

※ 인덱스 생성

 

Oralce 9i에서 인덱스는 Primary Key와 Unique Key와는 별도로 CREATE TABLE의 USING INDEX CREATE INDEX 문법을 이용해서 정의하는 것이 가능해 졌습니다.


아마도 대부분의 사용자들은 다음과 같은 방식을 알고 계실텐데…

예제를 보시면서 어떤 것이 바뀌었는지 확인토록 해보세요~~

SQL> create table test (
 c1 varchar2(4) not null,
 c2 number(10)  not null,
 constrint pk_test primary key(c1) using index );


테이블이 생성되었습니다.


위의 create table문은 pk_test라는 이름을 가지는 primary key 제약 조건을 만들며 아울러 pk_test라는 이름을 가진 인덱스를 만듭니다. 이 두가지는 user_ind_colums 뷰와 user_constraints 뷰에서 table_name = ‘TEST’라는 조건을 주시면 확인이 가능합니다.

그러나 9i이후에서는 인덱스에 대해 명시적으로 이름을 주는 것이 가능해 졌는데…

우선 아래의 예제를 참고 하도록 하죠…


SQL> create table test (

 c1 varchar2(4) not null,
 c2 number(7),
 constraint pk_test primary key(c1)
 using index
 (create index idx_test_c1 on test(c1))
 );


테이블이 생성되었습니다.

이 경우는 테이블을 만들면서 primary key를 만드는데 (원래는 default로 pk를 만들게 되면 그 컬럼으로 인덱스를 만듭니다.) 인덱스의 이름은 primary key 이름과 다르게 주기 위해 using index 구안에 create index문을 이용해서 인덱스를 생성했습니다.


--------------------------------------------------------------------
아래의 SQL문중 하나를 이용해 인덱스는 놔두고 PK만 삭제할 수 있습니다.
--------------------------------------------------------------------

SQL> alter table test drop primary key keep index;


테이블이 변경되었습니다.


또는


SQL> alter table test drop constraint pk_test;


테이블이 변경되었습니다.

2007/09/25 13:59 2007/09/25 13:59
이 글에는 트랙백을 보낼 수 없습니다
웅쓰:웅자의 상상플러스
웅자의 상상플러스
전체 (379)
게임 (5)
영화 (2)
기타 (23)
맛집 (5)
영어 (2)
대수학 (3)
형태소 (5)
Hacking (9)
Linux (112)
HTML (48)
Application_developing (48)
Web_developing (102)
Window (11)
«   2023/02   »
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28        
  1. 2016/01 (1)
  2. 2015/12 (3)
  3. 2015/10 (3)
  4. 2015/03 (2)
  5. 2015/01 (4)