ORACLE SQL基础知识

问：什么是NULL？
答：在我们不知道具体有什么数据的时候，也即未知，可以用NULL，
    我们称它为空，ORACLE中，含有空值的表列长度为零。
ORACLE允许任何一种数据类型的字段为空，除了以下两种情况：
1、主键字段（primary key），
2、定义时已经加了NOT NULL限制条件的字段

说明：
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同。
3、对空值做加、减、乘、除等运算操作，结果仍为空。
4、NULL的处理使用NVL函数。
5、比较时使用关键字用“is null”和“is not null”。
6、空值不能被索引，所以查询时有些符合条件的数据可能查不出来，
   count(*)中，用nvl(列名,0)处理后再查。
7、排序时比其他数据都大（索引默认是降序排列，小→大），
   所以NULL值总是排在最后。

SQL> select 1+null from dual;
SQL> select 1-null from dual;
SQL> select 1*null from dual;
SQL> select 1/null from dual;
查询到一个记录.
注：这个记录就是SQL语句中的那个null
设置某些列为空值
update table1 set 列1=NULL where 列1 is not null;
============================
union 与 union all的区别
UNION ALL selects all rows from each table and combines them into a single table. The difference between UNION and UNION ALL is that UNION ALL will not eliminate duplicate rows. Instead, it just pulls all rows from all tables fitting your query specifics and combines them into a table.

==============================================
在count函数中使用case函数，可以用于统计符合条件的行数，注意不符合条件的要置为null
这样计数才不会加1，其它值会增1。

select n_jcy,count(case when n_cskh_zp=1 then 1 else null end)zpkh,count(case when n_cskh_bmkh=1 then 1 else null end)bmkh,count(case when n_cskh_bykh=1 then 1 else null end)bykh,count(case when n_cskh_syywkh=1 then 1 else null end)sykh from v_gakh_khjg_pub group by n_jcy;

count 函数，如果是统计在有null的列上，返回的数可能少于整张表的记录数。

=======================================================================
union all 连接子句中不能用order by
以下写法是错误的：

 select n_ajbh from t_ywpz_gakh_fkjl where d_fkrq is not null and n_dqyx=1 and n_sfyyy=1  order by n_ajbh
 
 union all 
 
 select n_ajbh from t_ywpz_gakh_fkjl where d_fkrq is not null and n_dqyx=1 and n_sfyyy=3  order by n_ajbh;

但是可以这样写，把排序放在一个集合中：

select * from (
 select n_ajbh,n_sfyyy from t_ywpz_gakh_fkjl where d_fkrq is not null and n_dqyx=1 and n_sfyyy=1  order by n_ajbh)
 
 union all 
 select * from (
 select n_ajbh,n_sfyyy from t_ywpz_gakh_fkjl where d_fkrq is not null and n_dqyx=1 and n_sfyyy=3  order by n_ajbh);

========================================================
in 的子查询语句中不能包含ORDER BY 这是错误的：

select * from t_gakh_khjg where n_ajbh in( 
select n_ajbh
  from t_ywpz_gakh_fkjl
 where d_fkrq is not null
   and n_dqyx = 1
   and n_sfyyy = 2 order by n_ajbh
 )

==============================================
查询某个对象被其它的对象引用，很方便呀，有时项目维护时搜索某个统计表的数据来源，找不到源文件，只好在数据中搜索了。
select * from user_dependencies;
==============================================
select n_bh into ajbh from t_aj where n_flag<1;
当上面没有查到记录时，会报错，所以在没有肯定有记录前不能这么用。

for aj in(select * from t_aj where n_flag=2) loop;
    update t_aj set n_flag=1;
    commit;
   ---既使这这样，游标aj引用的数据还是其第一次查询时的数据。
   --aj.n_flag 值为2，并不是1。
   --这时查询的逻辑读会增加，会把原来的数据块拷贝一份到（撤销段？？？）
end loop;
======================================
insert into test_table values(v1,v2,...);
当省略了columns列表时，无论列是否有默认值，都必须把所有的列对应的值写完整。

======================================
与表table操作相关的语法：
无法指定列在表中出现的位置，新列将成为表的最后一列。
如果是新增加列，原来表中有记录，且新增的列有默认值，列所有的列都会加上默认值，这与修改列的默认值不一样，

如果原来的列有记录，某列记录中有NULL值，为该列增加一个默认值，原来记录的NULL并不会变成默认值，而是从新增记录才会使用默认值。
相当于原来列在添加记录时为该列指定了值为NULL。

ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);


ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
1、可以修改数据类型、大小、默认值的更改，
准则：
1、可以增大数字列的宽度或精确度
2、呆以增大数字列或字符列的宽度
3、只有在列仅包含空值或表没有行时，才可以减少列的宽度
4、只有在列包含空值时，才可以更改数据类型。
5、只有在列包含空值或您没有更改列的大小时，才可以将char类型转换为varchar2,或是将varchar2类型的列转换为char。
6、对列的默认值的更改只影响以后对表的插入操作
----------------------------------------------------
综上所述，应该是在不改变原来数据的基础上才可以更改。

ALTER TABLE table
DROP (column);
1、一次只能删一列
2、表至少剩下一列

alter table test_hztj_aj add (d_sarq date default sysdate,n_zt number(1));
alter table test_hztj_aj modify c_ah varchar2(100 char);
alter table test_hztj_aj drop (n_zt);
SQL> describe test_hztj_aj;
Name   Type               Nullable Default Comments
------ ------------------ -------- ------- --------
N_BH   NUMBER(15)         Y                        
C_AH   VARCHAR2(100 CHAR) Y                        
N_AJLB NUMBER(5)          Y                        
D_SARQ DATE               Y        sysdate         
V_CHAR VARCHAR2(30)       Y        'a'
-------------------------------------------------------
insert into test_hztj_aj(n_bh) values(6);--D_SARQ，V_CHAR会插入默认值
insert into test_hztj_aj(n_bh,v_char) values(6,null);--V_CHAR值为NULL，不会插入默认值
===================================================
修改表、视图、同义词、序列的名称
SQL> rename d_hztj_aj to test_hztj_aj;

Table renamed
SQL> create view hzaj as select n_ajlb,d_sarq from test_hztj_aj;

View created
SQL> rename hzaj to hzaj2;

Table renamed(???实际修改的是视图)
===================================================
truncate table table_name;
删除表中的所有行，不能回退，是DDL语句。delete 语句可以删除该表的所有行，但是不能释放存储空间。
truncate 语句不生成回退信息，不激活删除触发器，如果表是引用完整性约束的父表，则不能执行操作，需要先解除。
=====================================================
COMMENT用法
SQL> comment on table test_hztj_aj is '测试用表';

Comment added

SQL> comment on column test_hztj_aj.d_sarq is '这是日期';

Comment added
------------------------------
SQL> create table t_aj as select * from test_hztj_aj;

Table created

SQL> truncate table t_aj;

Table truncated

SQL> drop table t_aj;

Table dropped

SQL>







=====================================
视图view
create view hztjaj as select t_aj.n_ajlb,test_aj.n_ajlb from test_aj,t_aj

ORA-00957: 重复的列名
创建视图时与select不同，有相同的列名加限定符也不能解决冲突，必须对列重命名



=============
约束：
1、not null / unique / primary key / foreign key / check
语法：
列约束级别：
column [constraint constraint_name] constraint_type,
表约束级别
column,...
[constraint constraint_name] constraint_type
(column,...)
表级的约束除not null外都可以。
unique 约束的列可以包含多行NULL值，因为空值被认为不等于任何值。
由于多列上的unique约束的搜索机制，在部分空值组合unique关键字约束的非空列中不能有完全相同的值。
但是多列上都为NULL值存在多行是可以的。
ORACLE 通过隐式创建唯一索引的方式从而强制实行UNIQUE约束。
alter table constr_sample modify c_name varchar2(20)  default 'a' not null;
如果default 'a' not null换位置如下会报错
SQL> alter table constr_sample modify c_name varchar2(20) not null default 'a';

alter table constr_sample modify c_name varchar2(20) not null default 'a'

ORA-30649: 缺少 DIRECTORY 关键字
---------------------------------------------
create table constr_sample(
      n_id number(10) constraint con_samp_id_non not null,
      c_name varchar2(20)  default 'a' not null,
      n_dl number(10),
      n_xl number(5),
      constraint lxuniq unique(n_dl,n_xl)
);
以下测试主要有两个目的：
1、同时有unique约束且有默认值的c_name能否插入成功
2、对组合的unique约束字段，对其部分插入相同非空值，另一个都为空，能否成功
insert into constr_sample(n_id,n_dl) values(1,1);
1 row inserted

SQL> insert into constr_sample(n_id,n_dl) values(3,1)

ORA-00001: 违反唯一约束条件 (JCYUSER.LXUNIQ)



对于primary key 约束，如果是组合列，其中任一列都不能为NULL值，会隐式为primary key约束创建unique索引。
一个表只能有一个主键、组成主键的（任一）列不能为空，唯一标识一行记录。
SQL> create table constr_sample(
  2        n_id1 number(10) ,
  3        n_id2 number(10),
  4        c_name varchar2(20)  default 'a' not null,
  5        n_dl number(10),
  6        n_xl number(5),
  7        constraint lxuniq unique(n_dl,n_xl)
  8  );

Table created

SQL> alter table constr_sample add constraint pk_constr_samp primary key(n_id1,n_id2);

Table altered

SQL>insert into constr_sample(n_id1) values(1);

insert into constr_sample(n_id1) values(1)

ORA-01400: 无法将 NULL 插入 ("JCYUSER"."CONSTR_SAMPLE"."N_ID2")
以下SQL表明对于unique约束，如果n_dl ,n_xl都为NULL可以存在多列。

SQL> insert into constr_sample(n_id1,n_id2) values(1,1);

1 row inserted
----------------------------
foreign key约束 将指定一个列或列组合作为外键，并建立与同一表或不同表中主键或唯一关键字之间的关系。
外键必须与父表中的现有值匹配或者为NULL。可以定义在列或表约束级别上。


SQL> insert into constr_sample(n_id1,n_id2) values(1,2);

1 row inserted

SQL> insert into constr_sample(n_id1,n_id2) values(1,3);

1 row inserted

定义在表级：
constraint emp_dept_fk foreign key (departent_id) references departments(department_id);
如果是定义在列上：
department_id number(4) constraint emp_deptid_fk references departments(department_id),--这里没有关键字foreign key

create table depart(n_bh number(12) primary key,c_departname varchar2(200));
create table emp(n_id number(12) primary key,depart_id number(12),c_name varchar2(200));
alter table emp add constraint fk_emp_dep foreign key(depart_id) references depart(n_bh);

SQL> insert into depart values(1,'部门1');

1 row inserted

SQL> insert into emp values(1,2,'员工1');

insert into emp values(1,2,'员工1')

ORA-02291: 违反完整约束条件 (JCYUSER.FK_EMP_DEP) - 未找到父项关键字
SQL> insert into emp values(1,1,'员工1');

1 row inserted

SQL> delete from depart where n_bh=1;

delete from depart where n_bh=1

ORA-02292: 违反完整约束条件 (JCYUSER.FK_EMP_DEP) - 已找到子记录

SQL> alter table emp drop constraint fk_emp_dep ;
SQL> alter table emp add constraint fk_emp_dep foreign key(depart_id) references depart(n_bh) on delete set null;--值置为NULL
SQL> delete from depart where n_bh=1;

1 row deleted
SQL> select * from emp;

N_ID     DEPART_ID C_NAME
---- ------------- --------------
   1               员工1
可以看到depart_id设为了null;

SQL> alter table emp drop constraint fk_emp_dep ;

Table altered
SQL> alter table emp add constraint fk_emp_dep foreign key(depart_id) references depart(n_bh) on delete cascade;--级联删除

Table altered

在没有on delete cascade 或者 on delete set null 选项的情况下，如果子表中引用了父表中的行，则无法删除父表中该行。
==========================
check 约束
check 约束用于定义每行都必须满足的一个条件，该条件可以使用和查询条件同样的构造，但是以下情况除外：
1、对currval nextval level rownum 伪例的引用
2、sysdate uid user userenv函数的调用
3、涉及到其它行中的其它值的查询。

单独一列可以有多个check约束，这些约束在它们的定义中涉及到该列，对于在一列上可以定义的CHECK约束的数量没有限制。CHECK约束可以在列级别或表级别上定义。

alter table可以添加或是删除约束，但是不修改它的结构，可以启用或禁用约束，使用modify子句添加NOT NULL 约束。
只有在表为空时或该列对于每行都有值时，您才可以定义NOT NULL列。

alter table table_name add [constraint constraint_name] type (column);
alter table table_name drop constraint constraint_name;
删除表上的主键约束，并删除关联的FOREIGN KEY 约束。
alter table table_name drop primary key cascade;

alter table table_name drop primary key | unique(column) | constraint  constraint_name [cascade];

alter table table_name disable constraint constraint_name [cascade];
如果禁用唯一关键字或主键约束将会删除唯一索引。
如果启用唯一关键字或主键约束将会创建索引。但是并不会启用关联的约束
alter table table_name enable  constraint constraint_name ;

cascade constraints子句是和drop column子句一起使用，删除涉及到在已删除列上定义的主键或是唯一关键字的所有引用完整性约束。将删除在已删除列上定义的所有多列约束。
alter table table_name drop (column1[,column2...]) cascade constraints;
==================
create view

CREATE [ OR REPLACE] [ FORCE|NOFORCE ] VIEW view_name [(column_alias[,[column_alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]] ;
定义视图的子查询不能包含ORDER BY 子句
force:基本不存在也可以创建视图 ，noforce基表存在时才可以创建视图（默认值）
column_alias:指定由视图的查询所选择的列的别名。
WITH CHECK OPTION：指定只能插入或更新视图可以访问的行（比如一个视图只列出部门为20的员工，如果插入一个部门为30的员工会失败，如果更新一个员工的部门改为非20的值也会失败）
WITH READ ONLY 确保不能对此视图执行DML操作。

在视图上执行DML操作：
包含以下内容时不能删除行：
1、分组函数
2、group by 子句
3、distinct 关键字
4、伪列ROWNUM关键字

包含以下内容时不能修改数据：
1、分组函数
2、group by 子句
3、distinct 关键字
4、伪列ROWNUM关键字
5、由表达式定义的列

包含以下内容时不能通过视图添加数据：
1、分组函数
2、group by 子句
3、distinct 关键字
4、伪列ROWNUM关键字
5、由表达式定义的列
6、基表中未被视图选中的NOT NULL列


CREATE OR REPLACE FORCE VIEW EMP_5
  AS SELECT N_ID,DEPART_ID,C_NAME
     FROM EMP
     WHERE DEPART_ID=5
  WITH CHECK OPTION;

SQL> insert into depart values(5,'部门5');

1 row inserted

SQL> INSERT INTO EMP_5(N_ID,DEPART_ID,C_NAME) VALUES(3,5,'员工3');

1 row inserted

SQL> INSERT INTO EMP_5(N_ID,DEPART_ID,C_NAME) VALUES(4,3,'员工4');

INSERT INTO EMP_5(N_ID,DEPART_ID,C_NAME) VALUES(4,3,'员工4')

ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规

CREATE OR REPLACE FORCE VIEW EMP_5
  AS SELECT N_ID,DEPART_ID,C_NAME
     FROM EMP
     WHERE DEPART_ID=5;
--没有with check option  约束
SQL> CREATE OR REPLACE FORCE VIEW EMP_5
  2    AS SELECT N_ID,DEPART_ID,C_NAME
  3       FROM EMP
  4       WHERE DEPART_ID=5;

View created

SQL> INSERT INTO EMP_5(N_ID,DEPART_ID,C_NAME) VALUES(4,3,'员工4');

1 row inserted

--只要不操作有表达式的列可以插入成功
SQL>  CREATE OR REPLACE FORCE VIEW EMP_5
  2    AS SELECT N_ID,DEPART_ID,C_NAME,n_id+depart_id id_depart
  3       FROM EMP
  4       WHERE DEPART_ID=5
  5    WITH CHECK OPTION
  6  ;

View created

SQL> INSERT INTO EMP_5(N_ID,DEPART_ID,C_NAME) VALUES(5,5,'员工5');

1 row inserted

SQL> INSERT INTO EMP_5(N_ID,DEPART_ID,C_NAME,id_depart) VALUES(5,5,'员工5',10);

INSERT INTO EMP_5(N_ID,DEPART_ID,C_NAME,id_depart) VALUES(5,5,'员工5',10)

ORA-01733: 此处不允许虚拟列


--两个以上的基表组成的视图，一次只能更新一个基表中的字段，但是条件可以在不同的表上。
不能执行插入操作（即便只对一个基表指定值，其实相当于对另一个表字段指定为NULL）
SQL> create or replace force view emp_dep as
  2  select e.n_id,e.depart_id,e.c_name,d.n_bh,d.c_departname
  3    from emp e,depart d
  4    where e.depart_id=d.n_bh and d.n_bh=5
  5  with check option constraint con_e_d;

View created

SQL> update emp_dep set c_name=c_name||'-'||c_name where N_BH=5;

2 rows updated

SQL> update emp_dep set c_name=c_name||'-'||c_name,C_DEPARTNAME=C_DEPARTNAME||'-5' where N_BH=5;

update emp_dep set c_name=c_name||'-'||c_name,C_DEPARTNAME=C_DEPARTNAME||'-5' where N_BH=5

ORA-01776: 无法通过联接视图修改多个基表

--不能插入数据
SQL> insert into emp_dep(n_id,depart_id,c_name,n_bh,c_departname) values(5,5,'员工5',5,'部门5');

insert into emp_dep(n_id,depart_id,c_name,n_bh,c_departname) values(5,5,'员工5',5,'部门5')

ORA-01733: 此处不允许虚拟列

DROP VIEW view_name

============================
序列用来生成唯一的整数，可以递增和递减。序列号的存储和生成与表无关，可以多个表共用，是一个可以共享的对象。
CREATE SEQUENCE sequence_name
       [INCREMENT BY n] --如果省略此子句，则序列每次递增1。
       [START WITH n]
       [{MAXVALUE n | NOMAXVALUE}]  nomaxvalue(10^27作为递增序列的最大值，-1作为递减序列的最大值)
       [{MINVALUE n | NOMINVALUE}]  nominvalue(指定1作为递增序列的最小值，-（10^26）作为递减序列的最小值)
       [{CYCLE | NOCYCLE}]
       [{CACHE n | NOCACHE}]
NEXTVAL和CURRVAL：
NEXTVAL：会返回下一个可用的序列值，每次被引用时它都会返回一个唯一的值，即便对于不同的用户也是这样。
CURRVAL：会获取当前的序列值
必须对该序列发出NEXTVAL，然后CURRVAL才能包含值。

SQL> create sequence idv increment by -2 start with 4 nocache nocycle;

create sequence idv increment by -2 start with 4 nocache nocycle

ORA-04008: START WITH 不能大于 MAXVALUE


可以在以下上下文中使用NEXTVAL 和 CURRVAL：
1、不属于子查询一部分的SELECT 语句的SELECT 列表
2、INSERT 语句中的子查询的SELECT 列表
3、INSERT 语句的VALUES子句
4、UPDATE语句的SET 子句

不能在以下上上下文中使用NEXTVAL 和 CURRVAL：
1、视图的SELECT 列表
2、带有DISTINCT关键字的SELECT 语句
3、带有GROUP BY、HAVING或ORDER BY 子句的SELECT 语句
4、SELECT、DELETE 或UPDATE 语句中的子查询
5、CREATE TABLE 或ALTER TABLE语句中的DEFAULT表达式。

事务回退等情况，序列产生的值并不会回退，使用高速缓存（默认会有20个值存放在内存中供使用）时系统崩溃时也会出现间断的序号，或者别的表使用了该序列。

SQL> select n_id,idv.nextval from emp e;

         N_ID    NEXTVAL
------------- ----------
            1        -13
            3        -15
            4        -17
            5        -19
SQL> select * from (select n_id,idv.nextval from emp e);

select * from (select n_id,idv.nextval from emp e)

ORA-02287: 此处不允许序号


ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

修改序列不能从不同的序号处重新开始。
DROP SEQUENCE
===============
CREATE INDEX index_name ON table_name(column[,column]...);
不能修改索引，只能删除后再创建
DROP INDEX index_name;
删除表，索引用约束会自动删除，但是视图不会被删除。

==============
CREATE [PUBLIC] SYNONYM synonym FOR object;
它可以提供表、视图、序列、过程或其它对象的替代名称。
PUBLIC：创建一个所有用户都可以访问的同义词,数据库管理员可以创建这种公用同义词。
------------
准则：
该对象不能在程序包中，
私用同义词名必须与同一用户拥有的所有其它对象都不同。

删除同义词，DROP SYNONYM synonym_name;

只有数据库管理员可以删除公用同义词
DROP PUBLIC SYNONYM dept;
=======
系统权限：获得访问数据库的权限
对象权限：处理数据库对象的权限
======
CREATE USER scott
IDENTIFIED BY tiger;
GRANT privilege[,privilege ...]
TO user[,user | role, PUBLIC...];
PUBLIC:表示授予给每位用户。

select * from session_privs;查询当前系统权限

创建角色
CREATE ROLE role;
GRANT privilege[,privilege ...]  to role;

GRANT role TO user;

CREATE ROLE manager;
GRANT  create table,create view TO manager;
GRANT manager TO USER1,USER2;

----------
用户自己修改密码
ALTER USER scott IDENTIFIED BY lion;
必须具有ALTER USER权限才能更改USER中的其它选项（默认表空间等）。

对象权限：
ALTER
DELETE
EXECUTE
INDEX
INSERT
REFERENCES
SELECT
UPDATE
-----------
对象权限会根据对象的不同而变化
对象的所有者对其具有全部权限
所有者可以将属于他的对象的权限授予其他用户
GRANT object_priv[(columns)]
ON object
TO {user | role | PUBLIC}
[WITH GRANT OPTION];--本选择表示被授予者可以再将对象权限授予其他用户。否则该用户只能使用该权限，不能授予其他用户。
但是撤消了被授予者A的该对象权限，被A授予该对象权限的用户B 也会被取消。

GRANT update(department_name,location_id)
ON departments
TO scott,manager;
REVOKE {privilege [, privilege...] | ALL}
ON object
FROM {user | role | PUBLIC}
[CASCADE CONSTRAINTS] 如果要删除通过REFERENCES 权限对该对象实行的任何引用完整性约束，则此选项是必需的。
-----------------------------------
不限制用户对表空间的使用大小

alter user USERNAME quota unlimited on TABLESPACE

1 SYSTEM 授权 : 允许特定的用户对特定的一类对象做特定的操作 . 可操作的对象 :TABLES,INDEXES,PROCEDURES,SEGMENTS; 系统授权是对某一类对象 .

  GRANT SELECT ANY TABLE TO ZT

 

2 OBJECT( 对象授权 ):

A 允许一个特定的用户对一个特定的对象做特定的操作 . 对象 :TABLE,INDEX,SEGMENT,..

GRANT UPDATE ON EMP TO ZT

GRANT privileges ON OBJECT TO USER

GRANT [SELECT，INSERT，UPDATE，DELETE] ON [TABLE，INDEX，SEGMENT] TO USER

3 添加字段,删除字段.

alter table board_users add  eMail  VARCHAR(25) not null

alter table board_users drop COLUMN  email

修改字段名：
alter table t_gakh_khjg_dj rename column c_ay to c_aymc;
//修改字段属性
alter table t_gakh_khjg_dj modify c_aymc varchar2(400);

alter table t_gakh_khjg_dj drop column c_aymc;

alter table t_gakh_khjg_dj add c_aymc varchar2(600);

4 添加约束

alter table tbl_infor_content_attribute add constraint PK_tbl_infor_content_attribute primary key(infor_id,field_name);

alter table tbl_infor_content_attribute add constraint unique_infor_attri unique (infor_id,field_name);

5 创建临时表

CREATE GLOBAL TEMPORARY TABLE T_ZFDA_AJTJ_TEM(
       N_AJLB NUMBER,
       C_AJLB_MC VARCHAR2(210)
)ON COMMIT PRESERVE ROWS;

--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。
1。会话特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
ON COMMIT PRESERVE ROWS；

2。事务特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
ON COMMIT DELETE ROWS；


6 创建视图
create view view_name as (select * from table)

修改表名
alter table old_name rename to new_name



7添加字段

alter   table   your_table   add   (column1   col_type1,clumn2  col_type2...);

  declare  
  vstr_sql   varchar2(2000):='alter   table   your_table   add   (column1   col_type1,clumn2   col_type2...)';  
  begin  
  execute   immediate   vstr_sql;  
  end;  
  /    

select   column_name,data_type   from   ALL_TAB_COLUMNS   where TABLE_NAME='XX';  

程实现添加字段，屏蔽掉字段已经存在所造成的错误。另外，一次添加一个字段会更好一些。 

  可以查XX表列名。 
  你可以先查出所有的列,判断是否已有，没有就添加

  如果表是由当前用户创建的，则查USER_TAB_COLUMNS；否则查ALL_TAB_COLUMNS，并且加个条件，   OWNER='创建表的用户名'。
=============
导出带条件的表

exp user/pwd@3servicename tables=(mytable) query='where n_id=16001004 or n_id=16001005' file=e:\dm.dmp log=e:\dm.log 

imp user/pwd@3servicename indexfile=e:\dm.sql file=e:\dm.dmp log=e:\dm.log 

这个可以查看具体的

imp 'sys/pwd@servicename as sysdba' file=e:\dm.dmp log=e:\dm.log fromuser=user1 touser=user2

-------------------------------------------
转载：http://www.87717.com/oracle/oracle_13461.html

Oracle

SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME

MySQL

SHOW TABLES

MS SQL Server

select name from sysobjects where type = N'U' order by name

DB2 UDB

SELECT NAME FROM SYSIBM.SYSTABLES WHERE TYPE = 'T' AND CREATOR != 'SYSIBM' ORDER BY NAME
====================================

execute immediate 'sql';
execute immediate 'sql_select' into var_1, var_2;
execute immediate 'sql' using [in|out|in out] bind_var_1, [in|out|in out] bind_var_2;
execute immediate 'sql_select' into var_1, var_2 using [in|out|in out] bind_var_1, [in|out|in out] bind_var_2;
execute immediate 'sql' returning into var_1;
execute immediate 'sql' bulk collect into indexed_var;


用法实例：
Declare
        v_table varchar2(20);
        v_sql Varchar2(100);
        Begin
             v_table:='hqf.testtable';
             v_sql:='select * from '||v_table;
             dbms_output.put_line(v_table);
             dbms_output.put_line(v_sql);
           Execute Immediate v_sql;
        End;


Declare
       v_sid Integer:=20020101;
       v_sql Varchar2(100);
       v_result Varchar2(50);
Begin
     --v_sid:=12;
     v_sql:='Select loc into v_result from scott.dept d where d.deptno=:1 ';

     dbms_output.put_line(v_sql);
     --dbms_output.put_line(v_result);
     Execute Immediate v_sql
     Using 10 ;
     Commit;

End;

--动态命令的实用参考一（ select ..into）
--Execute immediate 命令在使用into关键字时与静态的plsql块中
--into关键字使用方法上的区别。

declare
v_result varchar2(20);
begin
execute immediate 'select dname from scott.dept d where d.deptno=:1' Into v_result   --:1这是一个绑定变量
Using 70;
commit;
dbms_output.put_line('结果为：'||v_result);
end;
--绑定变量，通俗的讲就是一个占位的参数，而后用using中的常量或着变量去找它的相应

Declare
v_result Varchar2(20);
Begin
     Select dname Into v_result From scott.dept d Where d.deptno=10;
     dbms_output.put_line(v_result);
End;

declare
v_result varchar2(20);
begin
execute immediate 'select dname from scott.dept d where d.deptno=:1' Into v_result
Using 70;
commit;
dbms_output.put_line('结果为：'||v_result);
end;


--动态命令的实用参考一（ insert ..into）
--
Begin
     Execute Immediate 'insert into scott.dept values (:1,:2,:3) '
     Using 70,'IT','Beijing';
     Commit;
End;


declare
   l_depnam varchar2(20) := 'testing';
   l_loc     varchar2(10) := 'Dubai';
   begin
   execute immediate 'insert into scott.dept values   (:1, :2, :3)'
     using 60, l_depnam, l_loc;
   commit;
end;


---尽享动态执行sql的execute immediate的命令使用的魅力。
--对比静态的测试，和动态测试。
Declare
v_result Varchar2(20);
Begin
v_result:=mypackage.myfun1(10);
dbms_output.put_line(v_result);
mypackage.mypro1(20020101,v_result);
dbms_output.put_line(v_result);

End;
--Select * From student s Where s.student_id=20020101;
---Select * From testtable Where recordnumber=10;


Declare
v_result Varchar2(20);
Begin
     --动态执行函数
     Execute Immediate 'begin :1:=mypackage.myfun1(:2); end;'
     Using Out v_result,In 10;
     Commit;
     dbms_output.put_line(v_result);

     --动态执行存储过程
     Execute Immediate 'begin mypro1(:1,:2); end;'
     Using In 20020101,Out v_result;
     Commit;
     dbms_output.put_line(v_result);
End;


--动态执行update语句实例：
create or replace function f_testweekday
return integer
is
    v_sql          varchar(2000);
    i_sendcount    integer;
begin
--_' || f_getweekday() || '
    v_sql := 'update t_push_smstemp_inform set sendcount=sendcount+1 returning sendcount into :1';

    execute immediate v_sql using out i_sendcount;

    return 0;
exception
    when others then
    rollback;
    return 0;
end f_testweekday;

评论

1 楼 zfcejb 2011-12-24  

在count函数中使用case函数这条不错，直接让我的sql语句执行从40秒变为几秒