最近刚做数据库,有个数据库的插入问题弄了很久都没弄出结果
我先设置一个变量,然后让他=时区的tonumber形式
然后用另外一个变量去和它运算
TimeZone number(10) := 0;
iTimeNumber number(10) := to_number(10000);
--select (to_number(substr(dbtimezone,3,1)))*60*60 into iTimeZone from dual;
iTimeNumber := 1167580800 - iTimeZone + 15*60*60 +30*60;
可是我使用的时候
INSERT INTO table( taskID, jobID, taskName, baseTime, suspendTime, resumeTime, curentState, curentProgress, preExecuteTime, curentPhaseID, retriedTimes, result, resultInfo, bCanRollback )
VALUES ( 10022, 10022, '服务器定时备份', iTimeNumber, 0, 0, 0, 0, 0, -1, 0, 5, '', 0 );
这里使用到了这个变量,却显示无法插入SQL报column not allowed here数据库里面这个baseTime的参数是NUMBER类型的,但是我用tonumber转换的格式它却不能使用,求高人帮助下,
其他变量的设置没有问题,我尝试过把iTimeNumber 换成一个数字就可以运行了,但是一用这个变量代替,就显示column not allowed here
,哪位知道麻烦告诉下,实在万分感谢
我先设置一个变量,然后让他=时区的tonumber形式
然后用另外一个变量去和它运算
TimeZone number(10) := 0;
iTimeNumber number(10) := to_number(10000);
--select (to_number(substr(dbtimezone,3,1)))*60*60 into iTimeZone from dual;
iTimeNumber := 1167580800 - iTimeZone + 15*60*60 +30*60;
可是我使用的时候
INSERT INTO table( taskID, jobID, taskName, baseTime, suspendTime, resumeTime, curentState, curentProgress, preExecuteTime, curentPhaseID, retriedTimes, result, resultInfo, bCanRollback )
VALUES ( 10022, 10022, '服务器定时备份', iTimeNumber, 0, 0, 0, 0, 0, -1, 0, 5, '', 0 );
这里使用到了这个变量,却显示无法插入SQL报column not allowed here数据库里面这个baseTime的参数是NUMBER类型的,但是我用tonumber转换的格式它却不能使用,求高人帮助下,
其他变量的设置没有问题,我尝试过把iTimeNumber 换成一个数字就可以运行了,但是一用这个变量代替,就显示column not allowed here
,哪位知道麻烦告诉下,实在万分感谢
iTimeZone number(10) := 0;
iTimeNumber number(10) := 0;
begin
select (to_number(substr(dbtimezone,3,1)))*60*60 into iTimeZone from dual;
iTimeNumber := 1167580800 - iTimeZone + 15*60*60 +30*60;
delete from OMCDB.tbl_TTJob where JOBID = 10022;
INSERT INTO OMCDB.tbl_TTJob VALUES (10022,'OMC','服务器定时备份',11,0,2,1,iTimeNumber,utl_raw.ca4_to_raw('<FULLBUCKUPDAYBEG>0<FULLBUCKUPDAYEND><BACKUPFILEPATHBEG>/export/home/backup/neteco<BACKUPFILEPATHEND>'),-1,-60,1,0,0,null,null);
delete from OMCDB.TBL_TTASK where TASKID = 10022;
INSERT INTO omcdb.tbl_TTask ( taskID, jobID, taskName, baseTime, suspendTime, resumeTime, curentState, curentProgress, preExecuteTime, curentPhaseID, retriedTimes, result, resultInfo, bCanRollback )
VALUES ( 10022, 10022, '服务器定时备份', iTimeNumber, 0, 0, 0, 1110,0, -1, 0, 5, '', 0 );
commit;
end;
就是这个脚本,本来不定义变量的时候不写Declare 直接用一个数字代替iTimeNumber是可以运行的,但是加上Declare后文件无法检测到变量,我定义变量的语句有错误么,语法不熟悉啊,有知道的麻烦讲解下,多谢了
iTimeZone number(10) := 0;
iTimeNumber number(10) := 0;
begin
select (to_number(substr(dbtimezone,3,1)))*60*60 into iTimeZone from dual;
iTimeNumber := 1167580800 - iTimeZone + 15*60*60 +30*60;
print iTimeZone;
print iTimeNumber
end;
这段是我定义变量的语句,SQL返回
ORA-06550: 第 7 行, 第 7 列:
PLS-00103: Encountered the symbol "ITIMEZONE" when expecting one of the following: := . ( @ % ;
Script line 7, statement line 7, column 7 的错误,这个定义语句有错误么,求指教下啊,
Declare
iTimeZone number(12) := 0;--将长度加大点
iTimeNumber number(12) := 0;
begin
select substr(dbtimezone,3,1)*60*60 into iTimeZone from dual;
/*如果substr截取的是数字组成的字符串,那么可以不再使用to_number将其转换为数字
SQL> select substr(dbtimezone,3,1)*60*60 from dual;
SUBSTR(DBTIMEZONE,3,1)*60*60
----------------------------
28800
*/
iTimeNumber := 1167580800 - iTimeZone + 15*60*60 +30*60;
delete from OMCDB.tbl_TTJob where JOBID = 10022;-- 如果是当前用户,那么直接写表名,
--如果你是超级用户或者可以操作上面这个用户的表,那么可以:用户名.表名
INSERT INTO OMCDB.tbl_TTJob
VALUES (10022,'OMC','服务器定时备份',11,0,2,1,iTimeNumber,
utl_raw.ca4_to_raw('<FULLBUCKUPDAYBEG>0<FULLBUCKUPDAYEND><BACKUPFILEPATHBEG>/export/home/backup/neteco<BACKUPFILEPATHEND>'),
-1,-60,1,0,0,null,null);
--utl_raw.ca4_to_raw这是你自定义的吗?
--我知道utl_raw.cast_to_raw
delete from OMCDB.TBL_TTASK where TASKID = 10022;
INSERT INTO omcdb.tbl_TTask ( taskID, jobID, taskName, baseTime, suspendTime, resumeTime,
curentState, curentProgress, preExecuteTime, curentPhaseID,
retriedTimes, result, resultInfo, bCanRollback )
VALUES ( 10022, 10022, '服务器定时备份', iTimeNumber, 0, 0, 0, 1110,0, -1, 0, 5, '', 0 );
commit;
end;
SQL> set serveroutput on;
SQL>
SQL> Declare
2 iTimeZone number(12) := 0;
3 iTimeNumber number(12) := 0;
4 begin
5 select substr(dbtimezone,3,1)*60*60 into iTimeZone from dual;
6 iTimeNumber := 1167580800 - iTimeZone + 15*60*60 +30*60;
7 dbms_output.put_line(itimezone||' '||itimenumber);
8 end;
9 /
28800 1167607800
PL/SQL procedure successfully completed
sqlplus omcdb/emsems@ossdb < /export/home/BugFix_Compile_V1R2C01/.../service/.../etc/sql/chinese/InsertITM.ora;
直接运行脚本的时候,运行就直接显示
pile_V1R2C01/neteco/service/neteco/etc/sql/chinese/InsertITM.ora;SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 16 15:36:19 2011Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing optionsSQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
显示运行成功了,但是里面的语句一句都没有运行
只要去掉Declare 再点击运行,就会逐句的运行里面的语句,但是去掉Declare就不能定义变量,我加上begin和end也没有用,能教下我这是怎么回事么,还有学友哥能推荐几本写脚本有关的书看下么,多谢了