'IT/ORACLE' 카테고리의 글 목록 (2 Page)

IT/ORACLE

Oracle Update Select(From) 2011.02.28
Oracle Table 변경/삭제 2011.02.28
oracle pl/sql if / loop / for / while 2011.02.28
Oracle Functions 2011.02.28
Oracle Altering 2011.02.28

Oracle Update Select(From)

쓸만한게없네 2011. 2. 28. 09:54

2011. 2. 28. 09:54

MSSQL *

UPDATE table_a
SET table_a.column_a = table_b.column_b

FROM table_a,

table_b,
WHERE table_a.userid = table_b.userid

* 오라클 *

UPDATE table_a
SET column_a = column_b
WHERE userid IN (SELECT userid
FROM b)

UPDATE table_a
   SET (column_1, column__2) = (SELECT column_a, column_b
                                  FROM table_b
                                 WHERE join_column_1 = table_a.join_column_a);

UPDATE table_a
   SET column_1 = (SELECT COLUMN
                     FROM table_b
                    WHERE userid = table_a.userid);

UPDATE table_a
   SET (column_1) = (SELECT COLUMN
                       FROM table_b
                      WHERE userid = table_a.userid AND url IS NOT NULL);

[출처] 펌 [오라클,MSSQL] Update ~ Select 구문 예제 |작성자 견공

저작자표시 비영리

Oracle Table 변경/삭제

쓸만한게없네 2011. 2. 28. 09:54

2011. 2. 28. 09:54

제 9 장. Table변경/삭제

Column 추가
TABLE 에 새로운 COLUMN 을 추가한다.
A LTER TABLE table명
ADD (column명 type(size) [DEFAULT value] [column_constraint], ...........) ;

[ 예제 ]
S_REGION TABLE 에 다음과 같은 COLUMN 을 추가하시오.

COMMENTS VARCHAR2(25)
A LTER TABLE S_REGION
ADD (COMMENTS VARCHAR2(25))
(추가될 COLUMN 의 위치는 지정할 수 없다. 새로운 COLUMN 은 마지막 위치에 생성된다.)

Column 변경
A LTER TABLE 문장의 MODIFY 절을 사용하여 다음과 같은 변경을 할 수 있다.
COLUMN 의 크기를 확장할 수 있다.
데이타가 들어있지 않으면 COLUMN 의 크기를 줄일 수 있다.
데이타가 들어있지 않다면 COLUMN 의 타입을 수정할 수 있다.
COLUMN 에 NULL 값이 없다면 NOT NULL CONSTRAINT 를 지정할 수 있다.
DEFAULT VALUE 를 변경할 수 있다.
이미 생성되어 있는 COLUMN 을 변경한다.
A LTER TABLE table명
MODIFY (column명 type(size) [DEFAULT value] [NOT NULL], .............) ;

Constraint 추가
이미 생성되어 있는 TABLE 에 CONSTRAINT 를 추가한다.
A LTER TABLE table명
ADD (table_constraint) ;

[ 예제 ]
S_EMP TABLE 에 다음과 같은 CONSTRAINT 를 추가하시오.
MANAGER_ID COLUMN 이 S_EMP TABLE 의 ID COLUMN 을 REFERENCE 하는 FOREIGN KEY CONSTRAINT 를 추가하시오.

A LTER TABLE S_EMP
ADD (CONSTRAINT S_EMP_MANAGER_ID_FK FOREIGN KEY(MANAGER_ID)
REFERENCES S_EMP(ID)) ;

Constraint 삭제
이미 생성되어 있는 TABLE 의 CONSTRAINT 를 삭제한다.
A LTER TABLE table명
D ROP PRIMARY KEY |
UNIQUE(column명) |
CONSTRAINT constraint명 [CASCADE] ;

[ 예제 ]
S_EMP TABLE 의 다음과 같은 CONSTRAINT 를 삭제하시오.
MANAGER_ID COLUMN 의 FOREIGN KEY CONSTRAINT

A LTER TABLE S_EMP
D ROP CONSTRAINT S_EMP_MANAGER_ID_FK ;

전체 데이타의 삭제
T RUNCATE 문장은 DDL

이다.
ROLLBACK SEGMENT 를 만들지 않고 모든 데이타를 삭제한다.
데이타가 삭제된 FREE 영역은 환원된다.
TABLE 로부터 모든 데이타를 삭제한다.
T RUNCATE TABLE table명 ;

[ 예제 ]
S_ITEM TABLE 의 모든 데이타를 삭제하시오.

T RUNCATE TABLE S_ITEM ;

Constraint disable/enable
TABLE 에 있는 모든 데이타가 CONSTRAINT 를 만족시켜야 ENABLE 할 수 있다.
PRIMARY KEY, UNIQUE CONSTRAINT 를 ENABLE 하면 그에 따른 INDEX FILE 이 자동적으로 생성된다.
CASCADE OPTION 은 FOREIGN KEY CONSTRAINT 를 DISABLE 할 때 사용한다.
CONSTRAINT 를 삭제하고 새로 만들지 않고 DISABLE, ENABLE 한다.
A LTER TABLE table명
DISABLE | ENABLE PRIMARY KEY |
UNIQUE(column명) |
CONSTRAINT constraint명 [CASCADE] ;

[ 예제 ]
S_DEPT TABLE 의 PRIMARY KEY CONSTRAINT 를 DISABLE 시키시오.

A LTER TABLE S_DEPT
DISABLE CONSTRAINT S_DEPT_ID_PK CASCADE;
S_EMP TABLE 의 S_EMP_DEPT_ID_FK CONSTRAINT 도 자동적으로 DISABLE 된다.

Table 삭제
TABLE 을 삭제하면 그 TABLE 에 딸린 INDEX FILE 도 삭제된다.
VIEW, SYNONYM, STORED PROCEDURE, FUNCTION, TRIGGER 등은 삭제되지 않는다.
CASCADE CONSTRAINTS 는 모 TABLE 을 삭제하고 자 TABLE 의 FOREIGN KEY CONSTRAINT 도 삭제한다.
D ROP TABLE table명 [CASCADE CONSTRAINTS] ;

[ 예제 ]
S_DEPT TABLE 을 삭제하시오.

D ROP TABLE S_DEPT CASCADE CONSTRAINTS ;

이름의 변경
TABLE, VIEW, SEQUENCE, SYNONYM 의 이름을 변경한다.
RENAME old명 TO new명 ;

[ 예제 ]
S_ORD TABLE 의 이름을 S_ORDER 로 변경하시오.

RENAME S_ORD TO S_ORDER ;

저작자표시 비영리

oracle pl/sql if / loop / for / while

쓸만한게없네 2011. 2. 28. 09:52

2011. 2. 28. 09:52

@ if문.
* 1이맞으면 출력해라.
declare
i_con number:=1;
begin
if i_con=1 then
dbms_output.put_line('맞고마이~');
end if;
end;
/

@ if..else문.
* 1보다 크면 '1보다크다', else는 '1보다크지 않아요'
declare
i_con number:=2;
begin
if i_con>1 then
dbms_output.put_line('1보다 큽니다');
else
dbms_output.put_line('1보다 크지않아요');
end if;
end;
/
* 1보다 크면 '1보다커요',1과 같으면 '1과 같아요',
else는 '1보다크지 않아요'
declare
i_con number:=1;
begin
if i_con>1 then
dbms_output.put_line('1보다 커요.');
elsif i_con=1 then
dbms_output.put_line('1과 같아요');
else
dbms_output.put_line('1보다 크지않아요');
end if;
end;
/
===============

@ loop문.
* 변수에 1을 더한 값을 출력.(20까지 출력)
declare
i_num number:=0;
begin
loop
i_num:=i_num+1;
dbms_output.put_line(i_num);
exit when i_num=20; *exit when : 끝나는 지점.
end loop;
end;
/

@ for문.
* 1~20까지의 홀수만 출력해라.
declare
i_num number:=0;
begin
for i_num in 1..20 loop
if(mod(i_num,2)=1) then
dbms_output.put_line(i_num);
end if;
end loop;
end;
/

---------

@ while문.
* 별 찍기.
declare
i_num number:=1;
i_str varchar2(20):=null;
begin
while i_num<10 loop
i_str:=i_str||'*';
dbms_output.put_line(i_str);
i_num:=i_num+1;
end loop;
end;
/

-------------------

[출처] oracle pl/sql if / loop / for / while|작성자 장갱쓰

저작자표시 비영리

Oracle Functions

쓸만한게없네 2011. 2. 28. 09:52

2011. 2. 28. 09:52

SQL Functions

SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL.

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle implicitly converts the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.

In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. When the parameter "function" appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values.

Note:

When you apply SQL functions to LOB columns, Oracle creates temporary LOBs during SQL and PL/SQL processing. You should ensure that temporary tablespace quota is sufficient for storing these temporary LOBs for your application.

See Also:

"User-Defined Functions" for information on user functions
Oracle Text Reference for information on functions used with Oracle Text
"Data Conversion" for implicit conversion of datatypes

The syntax showing the categories of functions follows:

function::=

Text description of function

single_row_function::=

Text description of single_row_function

The sections that follow list the built-in SQL functions in each of the groups illustrated in the preceding diagrams except user-defined functions. All of the built-in SQL functions are then described in alphabetical order. User-defined functions are described at the end of this chapter.

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.

Number Functions

Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The number functions are:

ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
POWER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET

Character Functions Returning Character Values

Character functions that return character values return values of the same datatype as the input argument.

Functions that return CHAR values are limited in length to 2000 bytes.
Functions that return VARCHAR2 values are limited in length to 4000 bytes.

For both of these types of functions, if the length of the return value exceeds the limit, then Oracle truncates it and returns the result without an error message.
Functions that return CLOB values are limited to 4 GB.

For CLOB functions, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.

The character functions that return character values are:

CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER

Character Functions Returning Number Values

Character functions that return number values can take as their argument any character datatype.

The character functions that return number values are:

ASCII
INSTR
LENGTH

Datetime Functions

Datetime functions operate on values of the DATE datatype. All datetime functions return a datetime or interval value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number. The datetime functions are:

Conversion Functions

Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. The SQL conversion functions are:

ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR

Miscellaneous Single-Row Functions

The following single-row functions do not fall into any of the other single-row function categories:

BFILENAME
COALESCE
DECODE
DEPTH
DUMP
EMPTY_BLOB, EMPTY_CLOB
EXISTSNODE
EXTRACT (XML)
EXTRACTVALUE
GREATEST
LEAST
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
NVL2
PATH
SYS_CONNECT_BY_PATH
SYS_CONTEXT
SYS_DBURIGEN
SYS_EXTRACT_UTC
SYS_GUID
SYS_TYPEID
SYS_XMLAGG
SYS_XMLGEN
UID
UPDATEXML
USER
USERENV
VSIZE
XMLAGG
XMLCOLATTVAL
XMLCONCAT
XMLFOREST
XMLSEQUENCE
XMLTRANSFORM

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

See Also:

"Using the GROUP BY Clause: Examples" and the "HAVING Clause" for more information on the GROUP BY clause and HAVING clauses in queries and subqueries

Many (but not all) aggregate functions that take a single argument accept these clauses:

DISTINCT causes an aggregate function to consider only distinct values of the argument expression.
ALL causes an aggregate function to consider all values, including all duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL.

All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema hr:

SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;

AVG(MAX(SALARY))
----------------
           10925

This calculation evaluates the inner aggregate (MAX(salary)) for each group defined by the GROUP BY clause (department_id), and aggregates the results again.

The aggregate functions are:

AVG
CORR
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_ (Linear Regression) Functions
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

Analytic Functions

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

analytic_function::=

Text description of functions10.gif follows

Text description of analytic_function

analytic_clause::=

Text description of functions14.gif follows

Text description of analytic_clause

query_partition_clause::=

Text description of functions17.gif follows

Text description of query_partition_clause

order_by_clause::=

Text description of functions20.gif follows

Text description of order_by_clause

windowing_clause::=

Text description of functions177.gif follows

Text description of windowing_clause

The semantics of this syntax are discussed in the sections that follow.

analytic_function

Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).

arguments

Analytic functions take 0 to 3 arguments.

analytic_clause

Use OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.

Notes:

You cannot specify any analytic function in any part of the analytic_clause. That is, you cannot nest analytic functions. However, you can specify an analytic function in a subquery and compute another analytic function over it.
You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION .

query_partition_clause

Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr. If you omit this clause, then the function treats all rows of the query result set as a single group.

You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.

Note:

If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause, then the function computations are parallelized as well.

Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.

order_by_clause

Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions except PERCENTILE_CONT and PERCENTILE_DISC (which take only a single key), you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.

Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.

Note:

Whenever the order_by_clause results in identical values for multiple rows, the function returns the same result for each of those rows. Please refer to the analytic example for SUM for an illustration of this behavior.

Restriction on the ORDER BY Clause

When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

ASC | DESC

Specify the ordering sequence (ascending or descending). ASC is the default.

NULLS FIRST | NULLS LAST

Specify whether returned rows containing nulls should appear first or last in the ordering sequence.

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

Note:

Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.

See Also:

order_by_clause of SELECT for more information on this clause

windowing_clause

Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).

ROWS | RANGE

These keywords define for each row a "window" (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window "slides" through the query result set or partition from top to bottom.

ROWS specifies the window in physical units (rows).
RANGE specifies the window as a logical offset.

You cannot specify this clause unless you have specified the order_by_clause.

Note:

The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.

BETWEEN ... AND

Use the BETWEEN ... AND clause to specify a start point and end point for the window. The first expression (before AND) defines the start point and the second expression (after AND) defines the end point.

If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.

UNBOUNDED PRECEDING

Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.

UNBOUNDED FOLLOWING

Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.

CURRENT ROW

As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the end point cannot be value_expr PRECEDING.

As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the start point cannot be value_expr FOLLOWING.

value_expr PRECEDING or value_expr FOLLOWING

For RANGE or ROW:

If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING.
If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING.

If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.

See Also:

NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals

If you specified ROWS:

value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.
If value_expr is part of the start point, then it must evaluate to a row before the end point.

If you specified RANGE:

value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal.

See Also:
"Literals" for information on interval literals
You can specify only one expression in the order_by_clause
If value_expr evaluates to a numeric value, then the ORDER BY expr must be a NUMBER or DATE datatype.
If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.

If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Analytic functions are commonly used in data warehousing environments. The analytic functions follow. Functions followed by an asterisk (*) allow the full syntax, including the windowing_clause.

AVG *

CORR *

COVAR_POP *

COVAR_SAMP *

COUNT *

CUME_DIST

DENSE_RANK

FIRST

FIRST_VALUE *

LAG

LAST

LAST_VALUE *