007 SG SQL注意 05

最新推荐文章于 2024-09-27 16:27:44 发布

moveofgod

最新推荐文章于 2024-09-27 16:27:44 发布

阅读量472

点赞数

分类专栏： Oracle 文章标签： sql table insert integer access date

Oracle 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

1.INSERT语句一次插入多行( 包括复杂情况 )

INSERT [ ALL ] [ conditional_insert_clause ]

[ insert_into_clause values_clause ] ( subquery )

conditional_insert_clause

[ ALL ] [ FIRST ]

[ WHEN condition THEN ] [ insert_into_clause values_clause ]

[ ELSE ] [ insert_into_clause values_clause]

注意 : You can perform multitable inserts only on tables , not on views or materialized views .

You cannot perform a multitable insert into a remote table .

You cannot specify a table collection expression when performing a multitable insert .

In a multitable insert , all of the insert_into_clauses cannot combine to specify more than 999 target columns .

Unconditional insert all

INSERT ALL --> 一次性插入两个表

INTO sal_history VALUES( EMPID, HIREDATE , SAL )

INTO mgr_history VALUES ( EMPID , MGR,SAL )

SELECT employee_id EMPID , hir_date HIREDATE , SALARY SAL , manager_id MGR

FROM employees

WHERE employ_id > 200

Conditional insert all

INSERT ALL

WHEN SAL > 10000 THEN

INTO sal_history VALUES(EMPID, HIREDATE , SAL )

WHEN MGR > 200 THEN

INTO mgr_history VALUES(EMPID, MGR, SAL)

SELECT employee_id EMPID , hire_date HIREDATE , salary SAL , manager_id MGR

FROM employees

WHERE employee_id > 200

Conditional insert first

INSERT FIRST -->满足一个就插入其中的一个TABLE , 不是全部 TABLE都插入

WHEN SAL > 25000 THEN

INTO special_sal VALUES( DEPTID , SAL )

WHEN HIREDATE like ( '%00%' ) THEN

INTO hiredate_history_00 VALUES( DEPTID , HIREDATE )

WHEN HIREDATE like ( ' %99%') THEN

INTO hiredate_history_99 VALUES ( DEPTID , HIREDATE )

ELSE

INTO hiredate_history VALUES ( DEPTID , HIREDATE )

SELECT department_id DEPTID , SUM( salary ) SAL , MAX(hire_date ) HIREDATE

FROM employees

GROUP BY department_id

Pivoting insert ( 旋转 / 列转变行 / 透析法 )

支持从非关系格式中接受数据 , 使用pivoting insert操作，可以构建一个转换，将来自任意输入流的记录(比如非关系数据库)转换成关系数据库表中的多条记录

旋转insert的不同之处在于所有的into子句都使用同一张表

table : sales_source_date ( EMP_ID , WEEK_ID , SALES_MON , SALES_TUE , SALES_WED , SALES_THUR, SALES_FRI ) --> 1行

table : sales_info ( EMP_ID , WEEK_ID , SALES ) --> 5 行

INSERT ALL

INTO sales_info VALUES ( emp_id , week_id , sales_mon )

INTO sales_info VALUES ( emp_id , week_id , sales_tue )

INTO sales_info VALUES ( emp_id , week_id , sales_wed )

INTO sales_info VALUES ( emp_id , week_id , sales_thur )

INTO sales_info VALUES (emp_id , week_id , sales_fri )

SELECT emp_id , week_id ,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri

FROM sales_source_date ;

运行结果 1 行变 5 行

2.External table

External tables are read-only tables , 可以是用 sql 查询数据 , 但是不能使用索引和 DML . ( 可以被看作为一个 view )

You are not in fact creating a table , Rather , you are creating metadata in the data dictionary that you can use to access external data .

CREATE TABLE oldemp(

empno NUMBER , empname CHAR( 20 ) , birthdate DATE )

ORGANIZATION EXTERIAL --> 指示这是一个外部表

( TYPE ORACLE_LOADER --> access_driver_type, 默认的是 ORACLE_LOADER

DEFAULT DIRECTORY emp_dir --> 这块用到 dircetory ( 指定 metadata reside , 就是创建table的metadata 寄存在哪里 )

ACCESS PARAMETERS --> 可选的 , 不是必须的

( RECORDS DELIMITED BY NEWLINE

BAD FILE 'bad_emp'

LOGFILE 'log_emp'

FIELDS TERMINATED BY ' , ' --> fields are all terminated by a ' , ' , 有这个符号分隔字符

( empno CHAR ,

empname CHAR ,

birthdate CHAR date_format date mask "dd-mon-yyyy"))

LOCATION ( 'emp1.txt' )) --> 文件

PARALLET 5 --> 同时被访问的限制量

REJECT LIMIT 200 ; --> specify how many conversion errors can occur during a query of the external data before an Oracle error is returned

--> 默认值是 0 .

假如现在有个文件 ( /flat_files/emp1.txt ) 内部格式为 : ( 10, jones , 11-Dec-1934 )

CREATE OR REPLACE DIRECTORY emp_dir AS '/flat_files' ;

When the database server needs to access data in an external source , it calls the appropriate access driver to get the data from an external

source in a form that database server expects .

3.Composite Data Types

PL/SQL RECORDS , PL/SQL COLLECTIONS ( include --> index by table , nested table , varry )

PL/SQL RECORDS ( 就跟 scalar variable 一样 , 一样在 declare 中定义 )

跟struct差不多 , 将多个字段 ( fields ) 作为一个处理单元 , 方便从表中取一行数据时, 接收数据 . 可以嵌套使用 .

A record is a group of related data items stored in fields , each with its own name and data type .

TYPE type_name IS RECORD

( field_declaration ... ) ;

identifier type_name ;

file_declaration

filed_name { field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE }

[ [ NOT NULL ] { := | DEFAULT } expr ]

例如 :

DECLARE

TYPE emp_record_type IS RECORD --> 不用非的是一个table中的内容 , 可以多个 table的列的组合

( first_name VARCHAR2( 25 ) ,

employee_id NUMBER( 5 ) NOT NULL := 100 ,

last_name employees.last_name%TYPE ,

job_id employees.job_id%TYPE ) ;

emp_record emp_record_type ;

使用 : emp_record.job_id := ' ST_CLERK ' ; 这样使用

DECLARE

emp_record employees%ROWTYPE --> 相当于上面将这个table的所有column都包括进来

使用 : 同上 emp_record.job_id

%ROWTYPE 是动态的 , 就是说底层的 table 或者 view 列的定义变化了 , 那么 %ROWTYPE 也自动变化 .

PL/SQL COLLECTION

INDEX BY TABLES : Objects of the TABLE type are called INDEX BY TABLES . They are modeled as ( but not the same as )

database tables . INDEX BY tables use a primary key to provide you with array-like access to rows .

Must contain two components

- A primary key of data type BINARY_INTEGER that indexes the INDEX BY table

- A column of a scalar or record data type , which stores the INDEX BY table elements

TYPE type_name IS TABLE OF

{ column_type | variable%TYPE | table.column%TYPE [ NOT NULL ] } | table.%ROWTYPE

[ INDEX BY BINARY_INTEGER] ;

identifier type_name ;

TYPE ename_table_type IS TABLE OF

employees.last_name%TYPE

INDEX BY BINARY_INTEGER ;

ename_table ename_table_types ;

结构

Unique identifier	Column
...	...
1	Jones
2	Smith
3	maduro
...	...

BINARY_INTEGER Scalar

INDEX BY tables can have one column and a unique identifier to that one column ,

例如 : DECLARE

TYPE ename_table_type IS TABLE OF

employees.last_name%TYPE

INDEX BY BINARY_INTEGER ;

ename_table ename_table_type ;

BEGIN

ename_table(1) := 'Cameron' ;

注意此处的 (1) , 有点类似高级语言的数组 .

( INDEX TABLE Methods ( ”数组“所带的方便使用的函数 )

EXISTS NEXT COUNT TRIM FIRST and LAST DELETE PRIOR ename_table.method_name

Method	Decription
EXISTS(n)	Returns TRUE if the nth element in a PL/SQL table exists.
COUNT	Returns the number of elements that a PL/SQL table currently contains
FIRST LAST	Returns the first and last( samllest and lagest )index numbers in a PL/SQL table . Returns NULL if the PL/SQL table is empty .
PRIOR(n)	Returns the index number tahta precedes index n in a PL/SQL table
NEXT(n)	Returns the index number that succeeds index n in a PL/SQL table
TRIM	TRIM removes one element from the end of a PL/SQL table. TRIM(n) removes n elements from the end of a PL/SQL table
DELETE	DELETE removes all elements from a PL/SQL table. DELETE( n ) removes the nth element from a PL/SQL table. DELETE(m ,n) removes all elements in the range m ... n from a PL/SQL table .