此function实现的是将小写数字转换成大写字母,看的书上的例子,谁能帮我解释下问题出在哪?
CREATE OR REPLACE FUNCTION smalltobig(smallmoney in VARCHAR2)
RETURN bigmoney IS BigWrite VARCHAR2(54);
Bignum VARCHAR2(2);
RMB VARCHAR2(2);
moneyplace NUMBER;
dotplace NUMBER;
moneynum NUMBER;
myexception EXCEPTION;BEGIN
dotplace := INSTR(smallmoney, '.');
IF(LENGTH(smallmoney) > 14) OR ((LENGTH(smallmoney) > 12) AND (dotplace = 0)) THEN
RAISE myexception;
END IF;
IF dotplace = 0 THEN
moneyplace := 0;
Else
moneyplace := dotplace - LENGTH(smallmoney);
END IF;
FOR moneynum IN REVERSE 1..LENGTH(smallmoney) LOOP
IF moneynum <> dotplace THEN
CASE SUBSTR(smallmoney, moneynum, 1)
WHEN '1' THEN BigWrite := '壹';
WHEN '2' THEN BIGWrite := '贰';
WHEN '3' THEN BigWrite := '叁';
WHEN '4' THEN BigWrite := '肆';
WHEN '5' THEN BigWrite := '伍';
WHEN '6' THEN BigWrite := '陆';
WHEN '7' THEN BigWrite := '染';
WHEN '8' THEN BigWrite := '捌';
WHEN '9' THEN BigWrite := '玖';
WHEN '0' THEN BigWrite := '零';
END CASE;
CASE moneyplace
WHEN '-2' THEN RMB := '分';
WHEN '-1' THEN RMB := '角';
WHEN '0' THEN RMB := '元';
WHEN '1' THEN RMB := '拾';
WHEN '2' THEN RMB := '佰';
WHEN '3' THEN RMB := '仟';
WHEN '4' THEN RMB := '萬';
WHEN '5' THEN RMB := '拾';
WHEN '6' THEN RMB := '佰';
WHEN '7' THEN RMB := '仟';
WHEN '8' THEN RMB := '亿';
WHEN '9' THEN RMB := '拾';
WHEN '10' THEN RMB := '佰';
WHEN '11' THEN RMB := '仟';
END CASE;
moneyplace := moneyplace + 1;
IF BigWrite IS NULL THEN
BigWrite := Bignum || RMB;
ELSE
BigWrite := Bignum || RMB || BigWrite;
END IF;
END IF;
END LOOP;
RETURN BigWrite;
EXCEPTION
WHEN myexception THEN
DBMS_OUTPUT.put_line('THIS IS ONLY TO CONVERT LESS THAN 14 OR NOT GREATER THAN 12');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('NOT INVALID MONEY.')
END;然后写一个调用此函数的过程DECLARE MYMONEY VARCHAR2(100);
BEGIN
MYMONEY := smalltobig('789.12');
DBMS_OUTPUT.put_line(MYMONEY);
END;
执行后,报的错是:
PLS-00905: object smalltobig is invalid.
谁帮我看下问题出在哪,谢谢!
CREATE OR REPLACE FUNCTION smalltobig(smallmoney in VARCHAR2)
RETURN bigmoney IS BigWrite VARCHAR2(54);
Bignum VARCHAR2(2);
RMB VARCHAR2(2);
moneyplace NUMBER;
dotplace NUMBER;
moneynum NUMBER;
myexception EXCEPTION;BEGIN
dotplace := INSTR(smallmoney, '.');
IF(LENGTH(smallmoney) > 14) OR ((LENGTH(smallmoney) > 12) AND (dotplace = 0)) THEN
RAISE myexception;
END IF;
IF dotplace = 0 THEN
moneyplace := 0;
Else
moneyplace := dotplace - LENGTH(smallmoney);
END IF;
FOR moneynum IN REVERSE 1..LENGTH(smallmoney) LOOP
IF moneynum <> dotplace THEN
CASE SUBSTR(smallmoney, moneynum, 1)
WHEN '1' THEN BigWrite := '壹';
WHEN '2' THEN BIGWrite := '贰';
WHEN '3' THEN BigWrite := '叁';
WHEN '4' THEN BigWrite := '肆';
WHEN '5' THEN BigWrite := '伍';
WHEN '6' THEN BigWrite := '陆';
WHEN '7' THEN BigWrite := '染';
WHEN '8' THEN BigWrite := '捌';
WHEN '9' THEN BigWrite := '玖';
WHEN '0' THEN BigWrite := '零';
END CASE;
CASE moneyplace
WHEN '-2' THEN RMB := '分';
WHEN '-1' THEN RMB := '角';
WHEN '0' THEN RMB := '元';
WHEN '1' THEN RMB := '拾';
WHEN '2' THEN RMB := '佰';
WHEN '3' THEN RMB := '仟';
WHEN '4' THEN RMB := '萬';
WHEN '5' THEN RMB := '拾';
WHEN '6' THEN RMB := '佰';
WHEN '7' THEN RMB := '仟';
WHEN '8' THEN RMB := '亿';
WHEN '9' THEN RMB := '拾';
WHEN '10' THEN RMB := '佰';
WHEN '11' THEN RMB := '仟';
END CASE;
moneyplace := moneyplace + 1;
IF BigWrite IS NULL THEN
BigWrite := Bignum || RMB;
ELSE
BigWrite := Bignum || RMB || BigWrite;
END IF;
END IF;
END LOOP;
RETURN BigWrite;
EXCEPTION
WHEN myexception THEN
DBMS_OUTPUT.put_line('THIS IS ONLY TO CONVERT LESS THAN 14 OR NOT GREATER THAN 12');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('NOT INVALID MONEY.')
END;然后写一个调用此函数的过程DECLARE MYMONEY VARCHAR2(100);
BEGIN
MYMONEY := smalltobig('789.12');
DBMS_OUTPUT.put_line(MYMONEY);
END;
执行后,报的错是:
PLS-00905: object smalltobig is invalid.
谁帮我看下问题出在哪,谢谢!
END;
/
RETURN bigmoney IS编译应该没通过,这里的bigmoney是怎么回事?自定义类型?
但是你的代码里面返回的是bigwriter,却是varchar2类型。
最后的分号是全角的。FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers]
END [name] RETURN的应该是了个类型。
CREATE OR REPLACE FUNCTION smalltobig(smallmoney in VARCHAR2)
RETURN bigmoney IS后面'贰';也改成半角的了,
但是错误仍然存在。
bigmoney 是什么,自定义的数据类型?
CREATE OR REPLACE FUNCTION smalltobig(smallmoney in VARCHAR2)
RETURN varchar2 IS BigWrite VARCHAR2(54);
Bignum VARCHAR2(2);
RMB VARCHAR2(2);
moneyplace NUMBER;
dotplace NUMBER;
moneynum NUMBER;
myexception EXCEPTION;BEGIN
dotplace := INSTR(smallmoney, '.');
IF(LENGTH(smallmoney) > 14) OR ((LENGTH(smallmoney) > 12) AND (dotplace = 0)) THEN
RAISE myexception;
END IF;
IF dotplace = 0 THEN
moneyplace := 0;
Else
moneyplace := dotplace - LENGTH(smallmoney);
END IF;
FOR moneynum IN REVERSE 1..LENGTH(smallmoney) LOOP
IF moneynum <> dotplace THEN
CASE SUBSTR(smallmoney, moneynum, 1)
WHEN '1' THEN BigWrite := '壹';
WHEN '2' THEN BIGWrite := '贰';
WHEN '3' THEN BigWrite := '叁';
WHEN '4' THEN BigWrite := '肆';
WHEN '5' THEN BigWrite := '伍';
WHEN '6' THEN BigWrite := '陆';
WHEN '7' THEN BigWrite := '染';
WHEN '8' THEN BigWrite := '捌';
WHEN '9' THEN BigWrite := '玖';
WHEN '0' THEN BigWrite := '零';
END CASE;
CASE moneyplace
WHEN '-2' THEN RMB := '分';
WHEN '-1' THEN RMB := '角';
WHEN '0' THEN RMB := '元';
WHEN '1' THEN RMB := '拾';
WHEN '2' THEN RMB := '佰';
WHEN '3' THEN RMB := '仟';
WHEN '4' THEN RMB := '萬';
WHEN '5' THEN RMB := '拾';
WHEN '6' THEN RMB := '佰';
WHEN '7' THEN RMB := '仟';
WHEN '8' THEN RMB := '亿';
WHEN '9' THEN RMB := '拾';
WHEN '10' THEN RMB := '佰';
WHEN '11' THEN RMB := '仟';
END CASE;
moneyplace := moneyplace + 1;
IF BigWrite IS NULL THEN
BigWrite := Bignum || RMB;
ELSE
BigWrite := Bignum || RMB || BigWrite;
END IF;
END IF;
END LOOP;
RETURN BigWrite;
EXCEPTION
WHEN myexception THEN
DBMS_OUTPUT.put_line('THIS IS ONLY TO CONVERT LESS THAN 14 OR NOT GREATER THAN 12');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('NOT INVALID MONEY.');
END;
CREATE OR REPLACE FUNCTION smalltobig(smallmoney in VARCHAR2)
RETURN VARCHAR2 IS
还有不行
结果:拾壹
少了个分号。帮你试一下,这样编译通过了。
CREATE OR REPLACE FUNCTION smalltobig (smallmoney in VARCHAR2)
RETURN VARCHAR2 IS BigWrite VARCHAR2(54);
Bignum VARCHAR2(2);
RMB VARCHAR2(2);
moneyplace NUMBER;
dotplace NUMBER;
moneynum NUMBER;
myexception EXCEPTION;BEGIN
dotplace:= INSTR(smallmoney, '.');
IF(LENGTH(smallmoney) > 14) OR ((LENGTH(smallmoney) > 12) AND (dotplace = 0)) THEN
RAISE myexception;
END IF;
IF dotplace = 0 THEN
moneyplace := 0;
Else
moneyplace := dotplace - LENGTH(smallmoney);
END IF;
FOR moneynum IN REVERSE 1..LENGTH(smallmoney) LOOP
IF moneynum <> dotplace THEN
CASE SUBSTR(smallmoney, moneynum, 1)
WHEN '1' THEN BigWrite := '壹';
WHEN '2' THEN BIGWrite := '贰';
WHEN '3' THEN BigWrite := '叁';
WHEN '4' THEN BigWrite := '肆';
WHEN '5' THEN BigWrite := '伍';
WHEN '6' THEN BigWrite := '陆';
WHEN '7' THEN BigWrite := '染';
WHEN '8' THEN BigWrite := '捌';
WHEN '9' THEN BigWrite := '玖';
WHEN '0' THEN BigWrite := '零';
END CASE;
CASE moneyplace
WHEN '-2' THEN RMB := '分';
WHEN '-1' THEN RMB := '角';
WHEN '0' THEN RMB := '元';
WHEN '1' THEN RMB := '拾';
WHEN '2' THEN RMB := '佰';
WHEN '3' THEN RMB := '仟';
WHEN '4' THEN RMB := '萬';
WHEN '5' THEN RMB := '拾';
WHEN '6' THEN RMB := '佰';
WHEN '7' THEN RMB := '仟';
WHEN '8' THEN RMB := '亿';
WHEN '9' THEN RMB := '拾';
WHEN '10' THEN RMB := '佰';
WHEN '11' THEN RMB := '仟';
END CASE;
moneyplace := moneyplace + 1;
IF BigWrite IS NULL THEN
BigWrite := Bignum || RMB;
ELSE
BigWrite := Bignum || RMB || BigWrite;
END IF;
END IF;
END LOOP;
RETURN BigWrite;
EXCEPTION
WHEN myexception THEN
DBMS_OUTPUT.put_line('THIS IS ONLY TO CONVERT LESS THAN 14 OR NOT GREATER THAN 12');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('NOT INVALID MONEY.');
END;
调用FUNCTION的时候,怎么显示的:NOT INVALID MONEY.
DECLARE
money VARCHAR2(100);
BEGIN
money := smalltobig('789.12');
DBMS_OUTPUT.put_line(money);
END;
/
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('NOT INVALID MONEY.');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('NOT INVALID MONEY.');
CREATE OR REPLACE FUNCTION smalltobig(smallmoney IN VARCHAR2) RETURN VARCHAR2 IS
BigWrite VARCHAR2(54);
Bignum VARCHAR2(2);
RMB VARCHAR2(2);
moneyplace NUMBER;
dotplace NUMBER;
moneynum NUMBER;
myexception EXCEPTION;BEGIN
dotplace := INSTR(smallmoney, '.');
IF (LENGTH(smallmoney) > 14) OR ((LENGTH(smallmoney) > 12) AND (dotplace = 0)) THEN
RAISE myexception;
END IF;
IF dotplace = 0 THEN
moneyplace := 0;
ELSE
moneyplace := dotplace - LENGTH(smallmoney);
END IF;
FOR moneynum IN REVERSE 1 .. LENGTH(smallmoney) LOOP
IF moneynum <> dotplace THEN
CASE SUBSTR(smallmoney, moneynum, 1)
WHEN '1' THEN
Bignum := '壹';
WHEN '2' THEN
Bignum := '贰';
WHEN '3' THEN
Bignum := '叁';
WHEN '4' THEN
Bignum := '肆';
WHEN '5' THEN
Bignum := '伍';
WHEN '6' THEN
Bignum := '陆';
WHEN '7' THEN
Bignum := '柒';
WHEN '8' THEN
Bignum := '捌';
WHEN '9' THEN
Bignum := '玖';
WHEN '0' THEN
Bignum := '零';
END CASE;
CASE moneyplace
WHEN '-2' THEN
RMB := '分';
WHEN '-1' THEN
RMB := '角';
WHEN '0' THEN
RMB := '元';
WHEN '1' THEN
RMB := '拾';
WHEN '2' THEN
RMB := '佰';
WHEN '3' THEN
RMB := '仟';
WHEN '4' THEN
RMB := '萬';
WHEN '5' THEN
RMB := '拾';
WHEN '6' THEN
RMB := '佰';
WHEN '7' THEN
RMB := '仟';
WHEN '8' THEN
RMB := '亿';
WHEN '9' THEN
RMB := '拾';
WHEN '10' THEN
RMB := '佰';
WHEN '11' THEN
RMB := '仟';
END CASE;
moneyplace := moneyplace + 1;
IF BigWrite IS NULL THEN
BigWrite := Bignum || RMB;
ELSE
BigWrite := Bignum || RMB || BigWrite;
END IF;
END IF;
END LOOP;
RETURN BigWrite;
EXCEPTION
WHEN myexception THEN
DBMS_OUTPUT.put_line('THIS IS ONLY TO CONVERT LESS THAN 14 OR NOT GREATER THAN 12');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('NOT INVALID MONEY.');
END;
RETURN VARCHAR2 IS BigWrite VARCHAR2(540);
Bignum VARCHAR2(20);
RMB VARCHAR2(20);
moneyplace NUMBER;
dotplace NUMBER;
moneynum NUMBER;
myexception EXCEPTION;BEGIN
dotplace:= INSTR(smallmoney, '.');
IF(LENGTH(smallmoney) > 14) OR ((LENGTH(smallmoney) > 12) AND (dotplace = 0)) THEN
RAISE myexception;
END IF;
IF dotplace = 0 THEN
moneyplace := 0;
Else
moneyplace := dotplace - LENGTH(smallmoney);
END IF;
FOR moneynum IN REVERSE 1..LENGTH(smallmoney) LOOP
IF moneynum <> dotplace THEN
CASE SUBSTR(smallmoney, moneynum, 1)
WHEN '1' THEN BigWrite := '壹';
WHEN '2' THEN BIGWrite := '贰';
WHEN '3' THEN BigWrite := '叁';
WHEN '4' THEN BigWrite := '肆';
WHEN '5' THEN BigWrite := '伍';
WHEN '6' THEN BigWrite := '陆';
WHEN '7' THEN BigWrite := '染';
WHEN '8' THEN BigWrite := '捌';
WHEN '9' THEN BigWrite := '玖';
WHEN '0' THEN BigWrite := '零';
END CASE;
CASE moneyplace
WHEN '-2' THEN RMB := '分';
WHEN '-1' THEN RMB := '角';
WHEN '0' THEN RMB := '元';
WHEN '1' THEN RMB := '拾';
WHEN '2' THEN RMB := '佰';
WHEN '3' THEN RMB := '仟';
WHEN '4' THEN RMB := '萬';
WHEN '5' THEN RMB := '拾';
WHEN '6' THEN RMB := '佰';
WHEN '7' THEN RMB := '仟';
WHEN '8' THEN RMB := '亿';
WHEN '9' THEN RMB := '拾';
WHEN '10' THEN RMB := '佰';
WHEN '11' THEN RMB := '仟';
END CASE;
moneyplace := moneyplace + 1;
IF BigWrite IS NULL THEN
BigWrite := Bignum || RMB;
ELSE
BigWrite := Bignum || RMB || BigWrite;
END IF;
END IF;
END LOOP;
RETURN BigWrite;
END;
这样是可以的,但是结果有问题了
23-42行的bigwirte换成bignum,‘染'字换成'柒'
改了吗?对照一下我贴的那个存储过程--这是我那个存储过程的执行结果
SQL> select smalltobig('783.9') from dual;SMALLTOBIG('783.9')
--------------------------------------------------------------------------------
柒佰捌拾叁元玖角
SQL>