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));