Oracle基础知识

1.创建一个和其他表结构相同的SQL
create table t_ref as select * from apas_info;
create table t_ref as select * from apas_info;
create table t as select * from apas_info;
insert into t_ref select * from apas_info;
select * from t a;
//逐行提交
DECLARE
BEGIN
     FOR cur IN (SELECT * FROM t_ref) LOOP
         INSERT INTO t VALUES cur;
         COMMIT;
     END LOOP;
END;
/
//批量提交
DECLARE
    v_count NUMBER;
  BEGIN
    FOR cur IN (SELECT * FROM t_ref) LOOP
      INSERT INTO t VALUES cur;
      v_count := v_count + 1;
      IF v_count >= 100 THEN
        COMMIT;
      END IF;
    END LOOP;
    COMMIT;
  END;
/
//oracle优化提交
http://space.itpub.net/519536/viewspace-620226
2.删除表数据
truncate table 表名
3.获取当前用户连接的所有表
1.SELECT * FROM tab;
2.SELECT table_name
  FROM all_tables
WHERE owner = (SELECT t1.username
                  FROM v$session t1, v$mystat t2
                 WHERE t1.sid = t2.sid
                   AND ROWNUM < 2)
   AND owner NOT IN ('SYSTEM', 'SYS', 'SYSMAN', 'DBSNMP', 'MGMT_VIEW',
        'OUTLN', 'WMSYS', 'DIP', 'TSMSYS');
4.获取当掉用户连接库的所有系列
select * FROM all_sequences WHERE SEQUENCE_OWNER = (SELECT t1.username
                  FROM v$session t1, v$mystat t2
                 WHERE t1.sid = t2.sid
                   AND ROWNUM < 2)
   AND SEQUENCE_OWNER NOT IN ('SYSTEM', 'SYS', 'SYSMAN', 'DBSNMP', 'MGMT_VIEW',
        'OUTLN', 'WMSYS', 'DIP', 'TSMSYS');
5.查看数据库关键字
select * from v$reserved_words;
6.修改表名
ALTER TABLE old_table_name RENAME TO new_table_name;
7.插入Date类型的
，把一个CHAR或VARCHAR2插入到DATE类型的字段中，也是需要进行类型转换的， PL/SQL会调用TO_DATE将字符按照默认的日期格式转成日期类型的值
fzbwj.setFbstarttime(DateUtil.parse(publicity.getBmsjfrom(),DateUtil.YEAR_MONTH_DAY_PATTERN));