select sys_context('userenv','ip_address') from dual //查看本机IP地址
create table new_aa as (select * from tm_monthplan) //备份一个新表
drop table new_aa //删除整个表
truncate table new_aa //快速清空一个大表
SELECT LAST_DAY(SYSDATE) FROM DUAL //返回当前月的最后一天
select sysdate from dual //返回当天日期时间
SELECT LAST_DAY(to_date('2006-07-18','yyyy-mm-dd')) FROM DUAL
select sysdate-2 from dual //返回两天前日期
select substr(sid,0,6),concat(concat(concat('[',s.staffNo),'] '),s.staffName) from v_tm_staff s where rownum<17 and to_number(to_char(a.inputdate,'yyyymm')) >= "+beginYear+" order by rownum desc
select * from tm_monthplan where sid='20060831013' for update //对数据可进行修改
select * from TM_CLASSROOM where NVL(sysid,' ')<>'D'
//取得一个表的最后5条记录
select * from (select rownum as id,m.* from tm_monthplan m) tablemonth
where tablemonth.id<=(select max(rownum) from tm_monthplan)
and tablemonth.id>(select max(rownum)-5 from tm_monthplan) order by tablemonth.id desc
INSTR方法的格式为
INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)
例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置。
默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。
所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的显示结果是14
----------------------------------------------------------------//批量执行插入语句
begin
insert into TM_COURSE (CID, CNAME, CENAME, CSORT, DEPT, TRAINEE, QUALIFICATECOURSE, CMARK, TRAINRETRAIN, RETRAINCYCLE, DURATIONTYPE, DURATION, CONVERTDURATION, EXAMMETHOD, TEACHMETHOD, EQUALCOURSE, PRECOURSE, TEACHER, MID, CDESC, REMARK, INFOID, APPROVEID, GID, SYSID, UOPER, UTIME, TRAINEETYPE, SPECSORT, NEWCID, ABILITYSORT, MARK, RCID, RDATE)
values ('DY-MA/OJT-026', '发询价/邀请招标文件准备', null, 'CD00000891', '31240500', null, 'N', null, 'F', null, 'H', '14', '14', 'CD10000373', 'CD00010081', null, null, null, null, null, null, null, null, '31000000', null, 'C020194', to_date('10-01-2007 21:25:37', 'dd-mm-yyyy hh24:mi:ss'), 'P', null, 'DY-MA/OJT-026', null, null, null, null);
insert into TM_COURSE (CID, CNAME, CENAME, CSORT, DEPT, TRAINEE, QUALIFICATECOURSE, CMARK, TRAINRETRAIN, RETRAINCYCLE, DURATIONTYPE, DURATION, CONVERTDURATION, EXAMMETHOD, TEACHMETHOD, EQUALCOURSE, PRECOURSE, TEACHER, MID, CDESC, REMARK, INFOID, APPROVEID, GID, SYSID, UOPER, UTIME, TRAINEETYPE, SPECSORT, NEWCID, ABILITYSORT, MARK, RCID, RDATE)
values ('DY-MA/OJT-027', '报价单审核/参与评标', null, 'CD00000891', '31240500', null, 'N', null, 'F', null, 'H', '14', '14', 'CD10000373', 'CD00010081', null, null, null, null, null, null, null, null, '31000000', null, 'C020194', to_date('10-01-2007 21:26:36', 'dd-mm-yyyy hh24:mi:ss'), 'P', null, 'DY-MA/OJT-027', null, null, null, null);
insert into TM_COURSE (CID, CNAME, CENAME, CSORT, DEPT, TRAINEE, QUALIFICATECOURSE, CMARK, TRAINRETRAIN, RETRAINCYCLE, DURATIONTYPE, DURATION, CONVERTDURATION, EXAMMETHOD, TEACHMETHOD, EQUALCOURSE, PRECOURSE, TEACHER, MID, CDESC, REMARK, INFOID, APPROVEID, GID, SYSID, UOPER, UTIME, TRAINEETYPE, SPECSORT, NEWCID, ABILITYSORT, MARK, RCID, RDATE)
values ('DY-MA/OJT-028', '技术澄清', null, 'CD00000891', '31240500', null, 'N', null, 'F', null, 'H', '14', '14', 'CD10000373', 'CD00010081', null, null, null, null, null, null, null, null, '31000000', null, 'C020194', to_date('10-01-2007 21:27:28', 'dd-mm-yyyy hh24:mi:ss'), 'P', null, 'DY-MA/OJT-028', null, null, null, null);
insert into TM_COURSE (CID, CNAME, CENAME, CSORT, DEPT, TRAINEE, QUALIFICATECOURSE, CMARK, TRAINRETRAIN, RETRAINCYCLE, DURATIONTYPE, DURATION, CONVERTDURATION, EXAMMETHOD, TEACHMETHOD, EQUALCOURSE, PRECOURSE, TEACHER, MID, CDESC, REMARK, INFOID, APPROVEID, GID, SYSID, UOPER, UTIME, TRAINEETYPE, SPECSORT, NEWCID, ABILITYSORT, MARK, RCID, RDATE)
values ('DY-MA/OJT-029', '办理推荐', null, 'CD00000891', '31240500', null, 'N', null, 'F', null, 'H', '14', '14', 'CD10000373', 'CD00010081', null, null, null, null, null, null, null, null, '31000000', null, 'C020194', to_date('10-01-2007 21:28:20', 'dd-mm-yyyy hh24:mi:ss'), 'P', null, 'DY-MA/OJT-029', null, null, null, null);
end;
-------------------------------------------------------导入数据
进入DOS导入导出：
D:/oracle/product/10.1.0/db_1/BIN
EXP IMS/ims123@WEB203 GRANTS=Y FILE=E:/data_205_qin/2006.1.10_web205.DMP TABLES=DEPT,STAFFINFO,TM_ALLOWANCE,TM_APPROVEFLOWDETAIL,TM_APPROVEPROCESS,TM_APROVEFLOW,TM_ATTEND,TM_AUDITCOURSE,TM_AUDITOTHER,TM_AUDITPLAN,TM_AUDITPROCESS,TM_AUTHDEPT,TM_AUTHGRADE,TM_CDETAIL,TM_CERTIFICATE,TM_CLASSROOM,TM_CLASSROOMAPPLY,TM_CLASSROOMUSAGE,TM_COURSE,TM_COURSEPLOT,TM_CSORT,TM_CUSAGE,TM_CUSTOMROLE,TM_CUSTOMROLESTAFF,TM_CUSTOMTASK,TM_DEPT,TM_DEPTADDON,TM_DEPTTRACK,TM_EMERGENCY,TM_EMERGENCYSTAFF,TM_EQUALTRAIN,TM_EVALUATE,TM_EXAMPAPER,TM_FEEDBACK,TM_FUNCTION,TM_FUNCTIONROLE,TM_GUSERAUTH,TM_IMPLEMENT,TM_IMPLEMENT_TMP,TM_INFORM,TM_INFORMTEMPLATE,TM_ITP,TM_LOG,TM_MATERIAL,TM_MATERIALIN,TM_MATERIALOUT,TM_MONTHPLAN,TM_MONTHPLANASSISTINFO,TM_NETCLASS,TM_NETCLASSMATERIAL,TM_NETCLASSPLAN,TM_NETCLASSQUESTION,TM_NETCLASSSTUDENT,TM_NEWS,TM_NEWSTAFF,TM_NEWSTAFFREPORT,TM_OTHERTRAIN,TM_OUTRESOURCE,TM_PERSONALAUTHPLAN,TM_PERSONALPLAN,TM_POST,TM_POSTAUTH,TM_POSTCOURSE,TM_PROJECTAPPLY,TM_PROJECTESTABLISH,TM_QUOTA,TM_QUOTATEMPLATE,TM_REPORT,TM_RESEARCHDETAIL,TM_RESOURCE,TM_RESOURCEIO,TM_ROLE,TM_ROLECUSTOMTASK,TM_ROLEDEFAULTTASK,TM_ROLESTAFF,TM_STAFF,TM_STAFFINFO,TM_STAFFPROXY,TM_SYSFUNCTION,TM_TASK,TM_TASKREPLY,TM_TASKTRACK,TM_TEACHER,TM_TEACHERLOAD,TM_TIMELIMIT,TM_USERAUTH,TM_YEARPLAN,TM_YEARREQUEST
imp ims/qweiopims@web205 file=E:/data_205_qin/2006_2_22/2006.2.22_web203.DMP full=Y
imp system/ims@tms file=E:/tool/database/2007-4-29/8web205_tm_news.dmp full=Y
---------创建表空间用户名-------------------------------
用system/manager用户登陆
CREATE TABLESPACE "ims_data"
LOGGING
DATAFILE 'D:/oracle/product/10.1.0/oradata/ims/IMS_DATA.ORA' SIZE 100M
REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE USER "ims" PROFILE "DEFAULT"
IDENTIFIED BY "123" DEFAULT TABLESPACE "ims_data"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK;
---------创建表空间用户名-------------------------------
select staff_no from tm_staff group by staff_no having count(staff_no)>1
select table_name from user_tab_columns where column_name ='CID' //查询含有列名为CID的所有表
select table_name from user_tab_cols where column_name = upper('cid')
//求两字符窜转换后的时间差
select to_date('2006-08-08', 'yyyy-mm-dd') - to_date('2006-01-01', 'yyyy-mm-dd') from tm_staff where rownum<=1
要截除小数部分，可以用函数trunc。因此，求两个日期之间天数的经验公式是：
trunc(date1) - trunc(date2)
select mp.* from tm_monthplan mp where 1=1 and to_char(mp.begindate,'yyyyMM')<='200608' and to_char(mp.enddate,'yyyyMM')>='200608' and mp.dept.dept like '31%' order by mp.course.cid,to_number(mp.issue)
日期的各部分的常用的的写法
119. 取时间点的年份的写法：
SELECT TO_CHAR(SYSDATE，'YYYY') FROM DUAL；
120. 取时间点的月份的写法：
SELECT TO_CHAR(SYSDATE，'MM') FROM DUAL；
121. 取时间点的日的写法：
SELECT TO_CHAR(SYSDATE，'DD') FROM DUAL；
122. 取时间点的时的写法：
SELECT TO_CHAR(SYSDATE，'HH24') FROM DUAL；
123. 取时间点的分的写法：
SELECT TO_CHAR(SYSDATE，'MI') FROM DUAL；
124. 取时间点的秒的写法：
SELECT TO_CHAR(SYSDATE，'SS') FROM DUAL；
125. 取时间点的日期的写法：
SELECT TRUNC(SYSDATE) FROM DUAL；
126. 取时间点的时间的写法：
SELECT TO_CHAR(SYSDATE，'HH24：MI：SS') FROM DUAL；
127. 日期，时间形态变为字符形态：
SELECT TO_CHAR(SYSDATE) FROM DUAL；
128. 将字符串转换成日期或时间形态：
SELECT TO_DATE('2003/08/01') FROM DUAL；
129. 返回参数的星期几的写法：
SELECT TO_CHAR(SYSDATE，'D') FROM DUAL；
130. 返回参数一年中的第几天的写法：
SELECT TO_CHAR(SYSDATE，'DDD') FROM DUAL；
131. 返回午夜和参数中指定的时间值之间的秒数的写法：
SELECT TO_CHAR(SYSDATE，'SSSSS') FROM DUAL；
132. 返回参数中一年的第几周的写法：
SELECT TO_CHAR(SYSDATE，'WW') FROM DUAL；
208. 将字符串CHAR的第一个字符为大写，其余为小写。
SELECT INITCAP('ABCDE') FROM DUAL；
232.得到大于或等于N的最大整数。
SELECT CEIL(5.6) FROM DUAL；
237.得到小于或等于N的最小整数。
SELECT FLOOR(5.6) FROM DUAL；
248.得到在M位截断的N的值。
SELECT TRUNC(7.7788,2) FROM DUAL；
271.若EXPR1是NULL，则返回EXPR2，否则返回EXPR1。
SELECT NAME，NVL(TO_CHAR(COMM)，'NOT APPLICATION') FROM TABLE1
//字符窜为空转换为整型
select case when (material is null or trim(material)='' ) then 0 else 33 end from tm_monthplan
select nvl(teacher,'0'),round(num,2) from tm_monthplan //字段num取2位小数点
where to_number(teacher)+to_number(nvl(teacher,'0'))<=6
select * from V_TM_STAFF staff0_ where lower(staff0_.SHORTCUT)='zy' //转换为小写
select * from V_TM_STAFF staff0_ where upper(staff0_.SHORTCUT)=upper('zy') //转换为大写
//创建视图
create or replace view v_tm_aa as
(
select trim(s.staff_no) as staff_no,s.staff_name,s.staff_name_eng, s.dept,s.politic_rank, s.politic_post,
s.staff_status, s.born_time, s.last_upd_time, s.last_upd_user, s.join_gnp, s.left_date,
s.name_alias, s.staff_pingyin, s.dept_owner,s.depts, s.staff_sex,
t.shortcut, t.pid, t.password, t.userro, t.belongdept, t.gid, t.uoper, t.utime
from staffinfo s left join tm_staff t on s.staff_no=t.staff_no
)
select * from v_tm_aa
drop view v_tm_aa
//创建触发器
create or replace tr_tm_yearrequest
before insert on tm_yearrequest
--referencing old as old new as new
for each row
declare
--local variables here
begin
:NEW.ADID:='AD' || LPAD(:NEW.ADID, 8, '0');
end tr_tm_yearrequest;
create or replace trigger tr_tm_course
after insert or update on tm_course
referencing old as old new as new
for each row
declare
i integer;
j integer;
begin
i := 0;
j := 0;
FOR r IN cl LOOP
i := i + 1;
begin
update tm_yearrequest q set cid=:new.cid where cid is null and cname=:new.cname;
IF :NEW.INPUTDATE IS NULL THEN
:NEW.INPUTDATE:=SYSDATE;
END IF;
IF :NEW.OPER IS NULL THEN
:NEW.OPER:=:NEW.UOPER;
END IF;
end;
end loop;
end;
--系统SQL语句-----------------------------------------------------------------------
--------------------------------------------------------------------
select * from tm_cdetail //value C类码值 csid 类别编码 cdname C类码中文名称 cdid C类码流水号
where csid in(
select csid from tm_cusage where tablename='Attend' and fieldname='type' //考勤类型 csid类别编码
) and (value='C' or value='AD' and value='A')
select * from tm_attend //type 考勤类型 sid期号 staff_no人员
where type in ()
----------存储过程--------------------------------------------
create or replace procedure p_tm_updatestaffdept as
cursor C_E_tm_dept IS
select mi.staff_no,st.DEPT,mi.traindept from tm_mstafftrainimp mi
join v_tm_staff st on mi.staff_no=st.STAFF_NO and mi.trainstatus='P'
and to_char(mi.trainbegin,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd');
C_REC C_E_tm_dept%ROWTYPE;
begin
for C_REC in C_E_tm_dept
loop
begin
insert into tm_stafftraindept
(STAFF_NO,TRAINDEPT,BELONGDEPT,GID,UOPER,UTIME,FLAGE,NOTES )
values
(
C_REC.STAFF_NO,
C_REC.Traindept,
C_REC.Dept,
substr(C_REC.Traindept,1,2)||'000000',
'P930067',
sysdate,
'',
''
);
update tm_staff set belongdept=C_REC.Traindept,gid=substr(C_REC.Traindept,1,2)||'000000'
,uoper='P930067',utime=sysdate where staff_no=C_REC.Staff_No;
update tm_staffinfo set dept=C_REC.Traindept where staff_no=C_REC.Staff_No;
exception
when others
then
dbms_output.put_line('ERROR occur:[' || SQLERRM(SQLCODE) || ']');
end;
end loop; --循环
commit;
end p_tm_updatestaffdept;
-----------------------------------------------------------------------------------------
create or replace procedure P_TM_CDETAIL
IS
BEGIN
update tm_cdetail cd set cd.cdname=
(
select de.DEPT_NAME from v_tm_dept de where cd.value=de.DEPT and trim(de.DEPT_NAME) is not null
and de.DEPT like '32__0000' and to_char(de.CANCEL_DATE,'yyyy-mm-dd')='0001-01-01'
and cd.gid='32000000' and cd.csid in
(
select cu.csid from tm_cusage cu where cu.tablename ='Monthplan'
and cu.fieldname ='trainee'
and cu.gid ='32000000'
)
) where cd.gid='32000000' and cd.csid in
(
select cu.csid from tm_cusage cu where cu.tablename ='Monthplan'
and cu.fieldname ='trainee'
and cu.gid ='32000000'
) and cd.value like '32%';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR occur:[' || SQLERRM(SQLCODE) || ']');
END P_TM_CDETAIL;
------------------------------------------------------------------------------------------
-------------------------触发存储过程jobs----------------------------------------
begin
sys.dbms_job.submit(job => :job,
what => 'P_tm_upnewstaff;',
next_date => to_date('08-11-2007 11:07:54', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate +1');
commit;
end;
-----------------------------------------------------------------------------
//修改表结构
-- Add/modify columns
alter table TM_EMERGENCYWEEKFOOT rename column REMARK to REMARK2;
alter table TM_EMERGENCYWEEKFOOT add ww VARCHAR2(200);
-- Add comments to the columns
comment on column TM_EMERGENCYWEEKFOOT.REMARK2
is '大亚湾核电应急指挥部2';
comment on column TM_EMERGENCYWEEKFOOT.ww
is '22222222222';
----------------------------------------------------------------------------------------