这是一个使用PL SQL开发的计算器程序,实现基本的数学运算。根据提供的数组,展现输入过程,计算出结果。
这个在stanford iphone2011教程的作业的另外一种实现方法。但那个是需要用obj-c实现,我这个是用pl sql实现。
这个存储过程中,使用了collection的复杂操作实现计算数据和计算符号的处理,使用递归实现计算的反复调用。
实现 加减程序正弦余弦N次方和N次方根的计算,和输入过程描述。
Version 1.1
create or replace
PROCEDURE run_science_program_v2
AS
type typ_program
IS
TABLE OF VARCHAR2(100);
type typ_progs
IS
TABLE OF typ_program;
type typ_opers
IS
TABLE OF VARCHAR2(10);
c_operations_1 typ_opers := typ_opers('+', '-');
c_operations_2 typ_opers := typ_opers('/', '*');
c_operations_3 typ_opers := typ_opers('power', 'sqrt-n');
c_operations_4 typ_opers := typ_opers('sin', 'cos', 'tan', 'sqrt');
v_trail typ_program;
v_program typ_program := typ_program();
v_progs typ_progs := typ_progs();
FUNCTION exist_operation(
p_operation typ_opers,
p_input VARCHAR2)
RETURN BOOLEAN
IS
result BOOLEAN;
BEGIN
result := false;
FOR i IN p_operation.first .. p_operation.last
LOOP
IF (p_operation(i) = p_input) THEN
result := true;
EXIT;
END IF;
END LOOP;
RETURN result;
END;
FUNCTION calculator(
p_head FLOAT,
p_operation VARCHAR2,
p_tail FLOAT)
RETURN FLOAT
IS
Result FLOAT;
BEGIN
result := 0;
CASE
WHEN p_operation = '+' THEN
result := p_head + p_tail;
WHEN p_operation = '-' THEN
result := p_head - p_tail;
WHEN p_operation = '*' THEN
result := p_head * p_tail;
WHEN p_operation = '/' THEN
result := p_head / p_tail;
WHEN p_operation = 'power' THEN
result := power(p_head, p_tail);
WHEN p_operation = 'sqrt' THEN
result := sqrt(p_tail);
WHEN p_operation = 'sqrt-n' THEN
result := power(p_head, 1 / p_tail);
WHEN p_operation = 'sin' THEN
result := sin(p_tail);
WHEN p_operation = 'cos' THEN
result := cos(p_tail);
WHEN p_operation = 'tan' THEN
result := cos(p_tail);
END CASE;
RETURN(Result);
END;
FUNCTION program(
p_program typ_program)
RETURN typ_program
IS
v_program typ_program;
v_head FLOAT;
v_operation VARCHAR2(10);
v_tail FLOAT;
BEGIN
v_program := p_program;
IF (v_program.exists(v_program.count - 1)) THEN
IF (v_program(v_program.count - 1) IN ('+', '-', '*', '/', 'power',
'sqrt-n')) THEN
v_head := v_program(v_program.count - 2);
v_operation := v_program(v_program.count - 1);
v_tail := v_program(v_program.count);
v_program.trim(3);
v_program.extend(1);
v_program(v_program.count) := calculator(v_head, v_operation, v_tail);
elsif (v_program(v_program.count - 1) IN ('sin', 'cos', 'tan', 'sqrt'))
THEN
v_head := 0;
v_operation := v_program(v_program.count - 1);
v_tail := v_program(v_program.count);
v_program.trim(2);
v_program.extend(1);
v_program(v_program.count) := calculator(v_head, v_operation, v_tail);
END IF;
IF (v_program.exists(v_program.count - 1)) THEN
v_program := program(v_program);
END IF;
END IF;
RETURN(v_program);
END;
FUNCTION run_program(
p_trail typ_program)
RETURN FLOAT
AS
result FLOAT;
v_trail typ_program;
v_input VARCHAR2(300);
BEGIN
result := 0.0;
v_trail := p_trail;
v_progs.delete;
v_program.delete;
FOR i IN v_trail.first .. v_trail.last
LOOP
v_input := v_trail(i);
CASE
WHEN
(
v_input = '('
)
THEN
v_progs.extend(1); --增加programs的元素program
v_progs(v_progs.count) := v_program;
v_program.delete; --删除v_program上所有元素
WHEN
(
v_input = ')'
)
THEN
v_program := program(v_program);
v_progs(v_progs.count).extend(1);
v_progs(v_progs.count)(v_progs(v_progs.count).count) := v_program(1);
v_program := v_progs(
v_progs.count);
v_progs.trim(1);
WHEN
(
v_input = '='
)
THEN
v_program := program(v_program);
WHEN
(
exist_operation(c_operations_1, v_input)
)
THEN
v_program := program(v_program);
v_program.extend(1);
v_program(v_program.count) := v_input;
WHEN
(
exist_operation(c_operations_2, v_input)
)
THEN
IF (v_program.exists(v_program.count - 1)) THEN
IF (exist_operation(c_operations_2, v_program(v_program.count - 1)) OR
exist_operation(c_operations_3, v_program(v_program.count - 1)) OR
exist_operation(c_operations_4, v_program(v_program.count - 1)))
THEN
v_program := program(v_program);
END IF;
END IF;
v_program.extend(1);
v_program(v_program.count) := v_input;
WHEN
(
exist_operation(c_operations_3, v_input) OR exist_operation(
c_operations_4, v_input)
)
THEN
IF (v_program.exists(v_program.count - 1)) THEN
IF (exist_operation(c_operations_3, v_program(v_program.count - 1)) OR
exist_operation(c_operations_4, v_program(v_program.count - 1)))
THEN
v_program := program(v_program);
END IF;
END IF;
v_program.extend(1);
v_program(v_program.count) := v_input;
ELSE
/*case when else */
v_program.extend(1);
v_program(v_program.count) := v_input;
END CASE;
END LOOP;
result := v_program(1);
RETURN(result);
END;
FUNCTION desc_program(
p_trail typ_program)
RETURN VARCHAR2
AS
result VARCHAR2(4000);
type t_boolean
IS
TABLE OF BOOLEAN;
v_trail typ_program;
v_input VARCHAR2(300);
v_operation_change BOOLEAN;
v_oper_changes t_boolean := t_boolean();
BEGIN
result := '';
v_operation_change := false;
v_trail := p_trail;
v_progs.delete;
v_program.delete;
FOR i IN v_trail.first .. v_trail.last
LOOP
v_input := v_trail(i);
CASE
WHEN
(
v_input = '('
)
THEN
v_progs.extend(1); --增加programs的元素program
v_progs(v_progs.count) := v_program;
v_program.delete; --删除v_program上所有元素
v_program.extend(1);
v_program(v_program.count) := v_input;
v_oper_changes.extend(1);
v_oper_changes(v_oper_changes.count) := v_operation_change;
v_operation_change := false;
WHEN
(
v_input = ')'
)
THEN
v_program.extend(1);
v_program(v_program.count) := v_input;
result := '';
FOR j IN v_program.first .. v_program.last
LOOP
result := result || v_program(j);
END LOOP;
v_operation_change := v_oper_changes(v_oper_changes.count);
v_oper_changes.trim(1);
IF (v_operation_change) THEN
v_operation_change := false;
result := '(1/' || result || ')';
END IF;
v_progs(v_progs.count).extend(1);
v_progs(v_progs.count)(v_progs(v_progs.count).count) := result;
v_program := v_progs(
v_progs.count);
v_progs.trim(1);
WHEN
(
v_input = 'sqrt-n'
)
THEN
v_program.extend(1);
v_input := '^';
v_program(v_program.count) := v_input;
v_operation_change := true;
ELSE
/*case when else */
IF (v_operation_change) THEN
v_operation_change := false;
v_program.extend(1);
v_program(v_program.count) := '(1/' || v_input || ')';
ELSE
v_program.extend(1);
IF (v_input = 'power' OR v_input = 'sqrt-n') THEN
v_input := '^';
END IF;
v_program(v_program.count) := v_input;
END IF;
END CASE;
END LOOP;
result := '';
FOR j IN v_program.first .. v_program.last
LOOP
result := result || v_program(j);
END LOOP;
RETURN(result);
END;
FUNCTION chinese_number(
p_input FLOAT)
RETURN VARCHAR2
AS
/*
created by wfg on 2012-7-27
It is used to change Arabic numbers to Chinese big numbers.
*/
type typ_money
IS
TABLE OF VARCHAR2(20);
c_numbers typ_money := typ_money('零', '壹', '贰', '叁', '肆', '伍', '陆', '柒', '捌',
'玖');
c_unit typ_money := typ_money('分', '角', '圆', '拾', '佰', '仟', '万', '拾', '佰',
'仟', '亿', '拾', '佰', '仟', '兆', '拾', '佰', '仟');
v_array_money typ_money := typ_money();
chinese_number VARCHAR2(30);
result VARCHAR2(1000);
v_number INTEGER;
v_number_element INTEGER;
i INT;
BEGIN
IF (p_input >= power(10, 16)) THEN
result := '超出计算范围';
RETURN result;
END IF;
v_number := ROUND(p_input * 100);
i := 1;
v_array_money.extend(1);
v_array_money(v_array_money.count) := '整';
WHILE v_number > 0
LOOP
v_number_element := mod(v_number, 10);
IF (v_number_element = 0) THEN
IF i IN (3, 7, 11, 15) THEN
chinese_number := c_unit(i);
ELSE
chinese_number := c_numbers(v_number_element + 1);
END IF;
ELSE
chinese_number := c_numbers(v_number_element + 1) || c_unit(i);
END IF;
CASE
WHEN chinese_number = '零' THEN
IF (v_array_money(v_array_money.count) NOT IN ('整', '零', '圆', '万', '亿',
'兆')) THEN
v_array_money.extend(1);
v_array_money(v_array_money.count) := chinese_number;
END IF;
WHEN chinese_number LIKE '%亿' THEN
IF (v_array_money(v_array_money.count) IN ('万')) THEN
v_array_money(v_array_money.count) := chinese_number;
ELSE
v_array_money.extend(1);
v_array_money(v_array_money.count) := chinese_number;
END IF;
WHEN chinese_number LIKE '%兆' THEN
IF (v_array_money(v_array_money.count) IN ('万', '亿')) THEN
v_array_money(v_array_money.count) := chinese_number;
ELSE
v_array_money.extend(1);
v_array_money(v_array_money.count) := chinese_number;
END IF;
ELSE
v_array_money.extend(1);
v_array_money(v_array_money.count) := chinese_number;
END CASE;
v_number := floor(v_number / 10);
i := i + 1;
END LOOP;
FOR i IN v_array_money.first .. v_array_money.last
LOOP
result := v_array_money(i) || result;
END LOOP;
RETURN result;
END;
BEGIN
v_trail := typ_program('(', '2', '+', '3', ')', '*', '4', 'power', '(', '1',
'+', '8', 'sqrt-n', '(', '2', '+', '2', ')', '+', '1', ')', '-', '10', '=');
dbms_output.put('This programStack ' || (desc_program(v_trail)));
dbms_output.put_line(' ' || run_program(v_trail));
dbms_output.put_line('' || chinese_number(run_program(v_trail)));
END run_science_program_v2;
Version 1.0
create or replace procedure run_science_program as
type typ_program is table of varchar2(100);
type typ_progs is table of typ_program;
type typ_opers is table of varchar2(10);
c_operations_1 typ_opers := typ_opers('+', '-');
c_operations_2 typ_opers := typ_opers('/', '*');
c_operations_3 typ_opers := typ_opers('power', 'sqrt-n');
c_operations_4 typ_opers := typ_opers('sin',
'cos',
'tan',
'sqrt');
v_trail typ_program;
v_program typ_program := typ_program();
v_progs typ_progs := typ_progs();
function exist_operation(p_operation typ_opers, p_input varchar2)
return boolean is
result boolean;
begin
result := false;
for i in p_operation.first .. p_operation.last loop
if (p_operation(i) = p_input) then
result := true;
exit;
end if;
end loop;
return result;
end;
function calculator(p_head float, p_operation varchar2, p_tail float)
return float is
Result float;
begin
result := 0;
case
when p_operation = '+' then
result := p_head + p_tail;
when p_operation = '-' then
result := p_head - p_tail;
when p_operation = '*' then
result := p_head * p_tail;
when p_operation = '/' then
result := p_head / p_tail;
when p_operation = 'power' then
result := power(p_head, p_tail);
when p_operation = 'sqrt' then
result := sqrt(p_tail);
when p_operation = 'sqrt-n' then
result := power(p_head, 1 / p_tail);
when p_operation = 'sin' then
result := sin(p_tail);
when p_operation = 'cos' then
result := cos(p_tail);
when p_operation = 'tan' then
result := cos(p_tail);
end case;
return(Result);
end;
function program(p_program typ_program) return typ_program is
v_program typ_program;
v_head float;
v_operation varchar2(10);
v_tail float;
begin
v_program := p_program;
if (v_program.exists(v_program.count - 1)) then
if (v_program(v_program.count - 1) in
('+', '-', '*', '/', 'power', 'sqrt-n')) then
v_head := v_program(v_program.count - 2);
v_operation := v_program(v_program.count - 1);
v_tail := v_program(v_program.count);
v_program.trim(3);
v_program.extend(1);
v_program(v_program.count) := calculator(v_head,
v_operation,
v_tail);
elsif (v_program(v_program.count - 1) in
('sin', 'cos', 'tan', 'sqrt')) then
v_head := 0;
v_operation := v_program(v_program.count - 1);
v_tail := v_program(v_program.count);
v_program.trim(2);
v_program.extend(1);
v_program(v_program.count) := calculator(v_head,
v_operation,
v_tail);
end if;
if (v_program.exists(v_program.count - 1)) then
v_program := program(v_program);
end if;
end if;
return(v_program);
end;
function run_program(p_trail typ_program) return float as
result float;
v_trail typ_program;
v_input varchar2(300);
begin
result := 0.0;
v_trail := p_trail;
v_progs.delete;
v_program.delete;
for i in v_trail.first .. v_trail.last loop
v_input := v_trail(i);
case
when (v_input = '(') then
v_progs.extend(1); --增加programs的元素program
v_progs(v_progs.count) := v_program;
v_program.delete; --删除v_program上所有元素
when (v_input = ')') then
v_program := program(v_program);
v_progs(v_progs.count).extend(1);
v_progs(v_progs.count)(v_progs(v_progs.count).count) := v_program(1);
v_program := v_progs(v_progs.count);
v_progs.trim(1);
when (v_input = '=') then
v_program := program(v_program);
when (exist_operation(c_operations_1, v_input)) then
v_program := program(v_program);
v_program.extend(1);
v_program(v_program.count) := v_input;
when (exist_operation(c_operations_2, v_input)) then
if (v_program.exists(v_program.count - 1)) then
if (exist_operation(c_operations_2,
v_program(v_program.count - 1)) or
exist_operation(c_operations_3,
v_program(v_program.count - 1)) or
exist_operation(c_operations_4,
v_program(v_program.count - 1))) then
v_program := program(v_program);
end if;
end if;
v_program.extend(1);
v_program(v_program.count) := v_input;
when (exist_operation(c_operations_3, v_input) or
exist_operation(c_operations_4, v_input)) then
if (v_program.exists(v_program.count - 1)) then
if (exist_operation(c_operations_3,
v_program(v_program.count - 1)) or
exist_operation(c_operations_4,
v_program(v_program.count - 1))) then
v_program := program(v_program);
end if;
end if;
v_program.extend(1);
v_program(v_program.count) := v_input;
else
/*case when else */
v_program.extend(1);
v_program(v_program.count) := v_input;
end case;
end loop;
result := v_program(1);
return(result);
end;
function desc_program(p_trail typ_program) return varchar2 as
result varchar2(4000);
type t_boolean is table of boolean;
v_trail typ_program;
v_input varchar2(300);
v_operation_change BOOLEAN;
v_oper_changes t_boolean := t_boolean();
begin
result := '';
v_operation_change := false;
v_trail := p_trail;
v_progs.delete;
v_program.delete;
for i in v_trail.first .. v_trail.last loop
v_input := v_trail(i);
case
when (v_input = '(') then
v_progs.extend(1); --增加programs的元素program
v_progs(v_progs.count) := v_program;
v_program.delete; --删除v_program上所有元素
v_program.extend(1);
v_program(v_program.count) := v_input;
v_oper_changes.extend(1);
v_oper_changes(v_oper_changes.count) := v_operation_change;
v_operation_change := false;
when (v_input = ')') then
v_program.extend(1);
v_program(v_program.count) := v_input;
v_operation_change := v_oper_changes(v_oper_changes.count);
v_oper_changes.trim(1);
if (v_operation_change) then
v_operation_change := false;
v_program.extend(1);
v_program(v_program.count) := ')';
end if;
for j in v_program.first .. v_program.last loop
result := result || v_program(j);
end loop;
v_progs(v_progs.count).extend(1);
v_progs(v_progs.count)(v_progs(v_progs.count).count) := result;
v_program := v_progs(v_progs.count);
v_progs.trim(1);
result := '';
when (exist_operation(c_operations_3, v_input)) then
v_program(v_program.count) := v_input || '(' ||
v_program(v_program.count) || ',';
v_operation_change := true;
else
/*case when else */
if (v_operation_change) then
v_operation_change := false;
v_program.extend(1);
v_program(v_program.count) := v_input;
v_program.extend(1);
v_program(v_program.count) := ')';
else
v_program.extend(1);
v_program(v_program.count) := v_input;
end if;
end case;
end loop;
for j in v_program.first .. v_program.last loop
result := result || v_program(j);
end loop;
return(result);
end;
begin
/*
v_trail := typ_program('5',
'+',
'9',
'power',
'(',
'2',
'*',
'2',
')',
'+',
'2',
'*',
'(',
'30',
'+',
'10',
')',
'-',
'10',
'=');
*/
v_trail := typ_program('(',
'2',
'+',
'3',
')',
'*',
'4',
'power',
'(',
'1',
'+',
'sqrt',
'(',
'2',
'+',
'2',
')',
'+',
'1',
')',
'-',
'10',
'=');
dbms_output.put('This programStack ' || desc_program(v_trail));
dbms_output.put_line(' ' || run_program(v_trail));
end run_science_program;