Oracle知识.doc

oracle系统的当前时间  
select sysdate from dual;    
oracle系统的当前时间，年、月、日  
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'dd') from dual;  

DUAL：Diffusing Update Algorithm ,弥散更新算法.EIGRP组件之一。dual是Oracle与数据字典一起自动创建的一个虚拟表﹐它只有一列﹕DUMMY﹐其数据类型为﹕VARCHAR2(1)。dual中只有一行数据﹕ 'X '。dual属于sys模式﹐但所有用户都可以使用dual名称访问它﹐用SELECT计算常量表达式﹑伪列等值时常用该表﹐因为它只返回一行数据﹐而使用其它表时可能返回多个数据行。用来查那些不属于实际表里的内容，有时也用来检查某表某条件的记录存在性
　　如：select sysdate from dual;
　　select 3+3 from dual;
　　相当与Sql Server的
set @Date=getdate()

select to_date('2004-05-07 13:23:44' , 'yyyy-mm-dd hh24:mi:ss' )    from dual//

TRUNCATE和DELETE有以下几点区别 1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。 2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。 3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。 4、TRUNCATE不能触发任何DELETE触发器。 5、不能授予任何人清空他人的表的权限。 6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。 7、不能清空父表。 TRUNCATE TABLE (schema)table_name DROP(REUSE) STORAGE 在默认是 DROP STORAGE 当使用DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数在特殊情况下使用 REUSE ST

DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据页并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

右连接：select a.studentno, a.studentname, b.classname
  from students a, classes b
  where a.classid(+) = b.classid;
左连接：select a.studentno, a.studentname, b.classname
  from students a, classes b
  where a.classid = b.classid(+);

全外连接 full outer join 或者 full join
全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上

SELECT * 
FROM TESTA 
FULL OUTER JOIN TESTB
ON TESTA.A=TESTB.A

全外连接的等价写法，对同一表先做左连接，然后右连接

SELECT  TESTA.*,TESTB.*
FROM TESTA
LEFT OUTER JOIN TESTB
ON TESTA.A=TESTB.A
UNION
SELECT TESTA.*,TESTB.*
FROM TESTB
LEFT OUTER JOIN TESTA
ON TESTA.A=TESTB.A

内连接 
SELECT * 
FROM TESTA,TESTB
WHERE TESTA.A=TESTB.A

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。
　唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引。
　主键索引数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
　聚集索引
　　在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。
如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

最直接的应对之道，是为category_id建立一个简单的索引：
CREATE INDEX mytable_categoryid ON mytable (category_id);
可以建立多重的索引。
　　CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

 Cursor类型包含三种: 隐式Cursor，显式Cursor和Ref Cursor（动态Cursor）。 
1． 隐式Cursor: 
1).对于Select …INTO…语句，一次只能从数据库中获取到一条数据，对于这种类型的DML Sql语句，就是隐式Cursor。例如：Select /Update / Insert/Delete操作。 
（2） 对于显式游标的运用分为四个步骤： 
ü 定义游标---Cursor  [Cursor Name]  IS; 
ü 打开游标---Open  [Cursor Name]; 
ü 操作数据---Fetch  [Cursor name] 
ü 关闭游标---Close [Cursor Name],这个Step绝对不可以遗漏。 
Sql代码 
1. Set serveroutput on;  
2.   
3. declare   
4.     Cursor cur_policy is  
5.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
6.      from t_contract_master cm  
7.      where cm.liability_state = 2  
8.      and cm.policy_type = 1  
9.      and cm.policy_cate in ('2','3','4')  
10.      and rownum < 5  
11.      order by cm.policy_code desc;  
12. Begin  
13.    For rec_Policy in cur_policy loop  
14.        Dbms_Output.put_line(rec_policy.policy_code);  
15.    end loop;  
16.    Exception   
17.      when others then  
18.          Dbms_Output.put_line(Sqlerrm);    
19. end;  
20. /  

Ref Cursor（动态游标）： 
1） 与隐式Cursor,显式Cursor的区别：Ref Cursor是可以通过在运行期间传递参数来获取数据结果集。而另外两种Cursor，是静态的，在编译期间就决定数据结果集。 
2） Ref cursor的使用: 
ü Type [Cursor type name] is ref cursor 
ü Define 动态的Sql语句 
ü Open cursor 
ü 操作数据---Fetch  [Cursor name] 
ü Close Cursor 
下面是一个Sample： 
Sql代码 
1. Set serveroutput on;  
2.   
3. Declare  
4.     ---define cursor type name  
5.     type cur_type is ref cursor;  
6.     cur_policy cur_type;  
7.     sqlStr varchar2(500);  
8.     rec_policy t_contract_master%rowtype;  
9. begin  
10.    ---define 动态Sql  
11.    sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm  
12.      where cm.liability_state = 2   
13.      and cm.policy_type = 1   
14.      and cm.policy_cate in (2,3,4)   
15.      and rownum < 5   
16.      order by cm.policy_code desc ';  
17. ---Open Cursor  
18.   open cur_policy for sqlStr;  
19.   loop  
20.        fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;  
21.        exit when cur_policy%notfound;  
22.          
23.        Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);  
24.     
25.   end loop;  
26. close cur_policy;      
27.   
28. end;  
29. /