SELECT to_char(TO_DATE('2001-12-20','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd') into Result FROM dual;return Result;
FROM dual是什么意思啊,你好热情啊,另外一个帖也是你帮我回答的
dual 是系统表我最近有空 到处抢分 水平太差,只能回答一些简单问题
我用的时候也是直接用from dual吗
DECLARE ret VARCHAR2(200); BEGIN ret:=TO_CHAR(TO_DATE('2001-12-20','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd'); DBMS_OUTPUT.PUT_LINE(ret); END; -- 20011220-------- 你可以不用select 赋值
jianhu21() ( ) 信誉:100 Blog 2006-12-27 14:47:02 得分: 0
我用的时候也是直接用from dual吗
--可以
建议lz多用搜索zt 可以将dual表理解为一系统表,用户不要对其去做增、删动作,而只做查询动作就可以了。 实际上在sql server中,一条select 语句可以不需要from对象,而oracle中的dual表就是因为oracle的SQL语法必须要from对象而产生的(个人看法)。DUAL is a table automatically created by Oracle along with the data dictionary. DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value 'X'. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table.
create or replace function SETCASENO(strNO in VARCHAR2,strDate in VARCHAR2) return VARCHAR2 is Result VARCHAR2(50); begin DECLARE ret VARCHAR2(200); ret:=TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd'); if INSTR(strName,'批次',1,1)>0 then Result:='#YDSP'||ret; else Result:='#DDXZ'||ret; end if; return(Result); end SETCASENO; 我这还是没调通过,你帮我看看吧
create or replace function SETCASENO(strName in VARCHAR2,strDate in VARCHAR2) return VARCHAR2 is Result VARCHAR2(50); ret VARCHAR2(200); begin ret:=TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd'); if INSTR(strName,'批次',1,1)>0 then Result:='#YDSP'||ret; else Result:='#DDXZ'||ret; end if; return Result ; end SETCASENO; --提示什么错误
FUNCTION SA.SETCASENO 编译错误错误: PLS-00103: 出现符号 "="在需要下列之一时: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char time timestamp interval date binary national character nchar 符号 "<an identifier>" 被替换为 "=" 后继续。 行: 5 文本: strDate:=TO_CHAR(TO_DATE(strDate1,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd');错误: PLS-00103: 出现符号 "IF"在需要下列之一时: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor 符号 "begin" 被替换为 "IF" 后继续。 行: 6 文本: if INSTR(strName,'批次',1,1)>0 then错误: PLS-00103: 出现符号 "end-of-file"在需要下列之一时: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe 行: 13错误: 提示: Variable 'else' 被声明但从来没有被用于 'SETCASENO' 行: 8 文本: else
CREATE OR REPLACE FUNCTION ff_0003 (strName IN VARCHAR2,strDate IN VARCHAR2) RETURN VARCHAR2 IS Result VARCHAR2(50); ret VARCHAR2(200); BEGIN ret:=TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd'); IF INSTR(strName,'批次',1,1)>0 THEN Result:='#YDSP'||ret; ELSE Result:='#DDXZ'||ret; END IF; RETURN Result ; END ff_0003; /测试 DECLARE RetVal VARCHAR2(200); STRNAME VARCHAR2(200); STRDATE VARCHAR2(200);BEGIN STRNAME := '批次1111'; STRDATE := '2006-12-11'; RetVal := ET_STATISTICS.FF_0003 ( STRNAME, STRDATE ); DBMS_OUTPUT.PUT_LINE(RetVal); COMMIT; END; --结果 #YDSP20061211
CREATE OR REPLACE FUNCTION ff_0003 (strName IN VARCHAR2,strDate IN VARCHAR2) RETURN VARCHAR2 IS Result VARCHAR2(50); ret VARCHAR2(200); BEGIN ret:=TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd'); ................. ///////////////////// 我把strDate IN VARCHAR2改成strDate IN DATE ret:=SELECT TO_CHAR(strDate ,'yyyymmdd') FROM dual; 还是不行哦 郁闷
CREATE OR REPLACE FUNCTION ff_0003 (strName IN VARCHAR2,strDate IN DATE) RETURN VARCHAR2 IS Result VARCHAR2(50); ret VARCHAR2(200); BEGIN ret:=TO_CHAR(strDate,'yyyymmdd'); IF INSTR(strName,'批次',1,1)>0 THEN Result:='#YDSP'||ret; ELSE Result:='#DDXZ'||ret; END IF; RETURN Result ; END ff_0003; /--test DECLARE RetVal VARCHAR2(200); STRNAME VARCHAR2(200); STRDATE DATE;BEGIN STRNAME := '批次1111'; STRDATE :=SYSDATE; RetVal := ET_STATISTICS.FF_0003 ( STRNAME, STRDATE ); DBMS_OUTPUT.PUT_LINE(RetVal); STRDATE :=TO_DATE('2006-12-10','yyyy-mm-dd hh24:mi:ss'); RetVal := ET_STATISTICS.FF_0003 ( STRNAME, STRDATE ); DBMS_OUTPUT.PUT_LINE(RetVal); COMMIT; END; --结果 #YDSP20061227 #YDSP20061210 =============================================================== 我不能再帮你调试了 我都要晕了
into Result
FROM dual;return Result;
到处抢分
水平太差,只能回答一些简单问题
BEGIN
ret:=TO_CHAR(TO_DATE('2001-12-20','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd');
DBMS_OUTPUT.PUT_LINE(ret);
END;
--
20011220--------
你可以不用select 赋值
我用的时候也是直接用from dual吗
--可以
可以将dual表理解为一系统表,用户不要对其去做增、删动作,而只做查询动作就可以了。
实际上在sql server中,一条select 语句可以不需要from对象,而oracle中的dual表就是因为oracle的SQL语法必须要from对象而产生的(个人看法)。DUAL is a table automatically created by Oracle along with the data dictionary. DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value 'X'. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table.
begin
DECLARE ret VARCHAR2(200);
ret:=TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd');
if INSTR(strName,'批次',1,1)>0 then
Result:='#YDSP'||ret;
else
Result:='#DDXZ'||ret;
end if;
return(Result);
end SETCASENO;
我这还是没调通过,你帮我看看吧
is Result VARCHAR2(50);
ret VARCHAR2(200);
begin
ret:=TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd');
if INSTR(strName,'批次',1,1)>0 then
Result:='#YDSP'||ret;
else
Result:='#DDXZ'||ret;
end if;
return Result ;
end SETCASENO;
--提示什么错误
FUNCTION SA.SETCASENO 编译错误错误: PLS-00103: 出现符号 "="在需要下列之一时:
constant exception
<an identifier> <a double-quoted delimited-identifier> table
LONG_ double ref char time timestamp interval date binary
national character nchar
符号 "<an identifier>" 被替换为 "=" 后继续。
行: 5
文本: strDate:=TO_CHAR(TO_DATE(strDate1,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd');错误: PLS-00103: 出现符号 "IF"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "IF" 后继续。
行: 6
文本: if INSTR(strName,'批次',1,1)>0 then错误: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
行: 13错误: 提示: Variable 'else' 被声明但从来没有被用于 'SETCASENO'
行: 8
文本: else
(strName IN VARCHAR2,strDate IN VARCHAR2) RETURN VARCHAR2
IS Result VARCHAR2(50);
ret VARCHAR2(200);
BEGIN
ret:=TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd');
IF INSTR(strName,'批次',1,1)>0 THEN
Result:='#YDSP'||ret;
ELSE
Result:='#DDXZ'||ret;
END IF;
RETURN Result ;
END ff_0003;
/测试
DECLARE
RetVal VARCHAR2(200);
STRNAME VARCHAR2(200);
STRDATE VARCHAR2(200);BEGIN
STRNAME := '批次1111';
STRDATE := '2006-12-11'; RetVal := ET_STATISTICS.FF_0003 ( STRNAME, STRDATE );
DBMS_OUTPUT.PUT_LINE(RetVal);
COMMIT;
END; --结果
#YDSP20061211
是用下面的方法吗
TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd');
是字符串 or date
dual为oracle系统表
已经把他变成VARCHAR2了吧,反正就是不行,郁闷啊
DECLARE
RetVal VARCHAR2(200);
STRNAME VARCHAR2(200);
STRDATE VARCHAR2(200);BEGIN
STRNAME := '批次1111';
STRDATE :=TO_CHAR(SYSDATE,'yyyy-mm-dd'); RetVal := ET_STATISTICS.FF_0003 ( STRNAME, STRDATE );
DBMS_OUTPUT.PUT_LINE(RetVal);
COMMIT;
END;
建议修改函数的入口参数类型
并修改函数
ret:=TO_CHAR(strDate,'yyyymmdd');剩下的也只能你自己慢慢调试了
(strName IN VARCHAR2,strDate IN VARCHAR2) RETURN VARCHAR2
IS Result VARCHAR2(50);
ret VARCHAR2(200);
BEGIN
ret:=TO_CHAR(TO_DATE(strDate,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd');
.................
/////////////////////
我把strDate IN VARCHAR2改成strDate IN DATE
ret:=SELECT TO_CHAR(strDate ,'yyyymmdd') FROM dual;
还是不行哦
郁闷
(strName IN VARCHAR2,strDate IN DATE) RETURN VARCHAR2
IS Result VARCHAR2(50);
ret VARCHAR2(200);
BEGIN
ret:=TO_CHAR(strDate,'yyyymmdd');
IF INSTR(strName,'批次',1,1)>0 THEN
Result:='#YDSP'||ret;
ELSE
Result:='#DDXZ'||ret;
END IF;
RETURN Result ;
END ff_0003;
/--test
DECLARE
RetVal VARCHAR2(200);
STRNAME VARCHAR2(200);
STRDATE DATE;BEGIN
STRNAME := '批次1111';
STRDATE :=SYSDATE; RetVal := ET_STATISTICS.FF_0003 ( STRNAME, STRDATE );
DBMS_OUTPUT.PUT_LINE(RetVal);
STRDATE :=TO_DATE('2006-12-10','yyyy-mm-dd hh24:mi:ss'); RetVal := ET_STATISTICS.FF_0003 ( STRNAME, STRDATE );
DBMS_OUTPUT.PUT_LINE(RetVal);
COMMIT;
END; --结果
#YDSP20061227
#YDSP20061210
===============================================================
我不能再帮你调试了
我都要晕了
insert into语句,我能连续用多次吗,我需要插入好几张表