- 浏览: 1087404 次
- 性别:
- 来自: 南京
博客专栏
-
Oracle管理和开发
浏览量:349730
最新评论
-
Simon.Ezer:
请问对于“如果非主键字段值发生改变,则不会同步过去”这种情况, ...
创建增量同步Oracle物化视图问题 -
dahai639:
挺好的,支持一下
Oracle的pipelined函数实现高性能大数据处理 -
zealotpz:
不错,原来是用户oracle 的所属组的问题
以sysdba身份登录oracle报ORA-1031权限不足错误之完美分析 -
mikixiyou:
zhangyuslam 写道如果担心全局索引失效,可以使用如下 ...
Oracle分区表的分区交互技术实现数据快速转移 -
sea0108:
...
Oracle sql loader使用速成
这是一个使用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;
发表评论
-
Oracle分区表的分区交互技术实现数据快速转移
2013-01-22 11:48 14678有一个需求,将某业务表的某个时间点之前的记录转移到它的历史 ... -
等待事件enq TX row lock contention分析
2013-01-17 17:16 33215在Oracle数据库性能报告AWRRPT分析时,发现top ... -
Oracle的UNDO表空间管理总结
2013-01-14 15:06 12892UNDO是Oracle中的一个很 ... -
Oracle在不同windows系统中的迁移
2013-01-09 15:41 3709在Windows操作系统环境下 ... -
Oracle 10.2.0.1在windows 2008上安装失败经历
2013-01-07 10:29 8630这两天遇到这个一个项目上的数据库迁移的问题。原来的环境是数据库 ... -
所有的物化视图刷新脚本
2012-12-28 13:59 0select 'execute dbms_mview.refr ... -
Oracle数据库的SQL性能问题分析
2012-12-27 15:31 5235在Oracle 10.2.0.4数据库中,有一个SQL执行缓慢 ... -
db block gets和consistent gets的分析
2012-12-26 18:09 0在Oracle的文档中有这样一段解释: db block g ... -
创建增量同步Oracle物化视图问题
2012-12-25 14:07 15185我们采用Oracle的物化视图增量刷新机制定时将数据库A上的某 ... -
查归档日志文件每小时生成量
2012-12-18 16:13 10730在O racle数据库中,通过v$archived_lo ... -
如何删掉临时表空间的文件
2012-12-18 15:40 0Unlike Oracle datafiles which m ... -
Oracle sql性能诊断暨event 10046和10053使用
2012-12-17 10:24 0早上em grid control监控显示数据库的负载增加,其 ... -
查LOB字段占用的空间大小
2012-12-13 16:00 4492查询Oracle表中LOB字段的占用空间大小。表中每一个LOB ... -
Oracle Data Guard的重做日志传输和应用状况监控
2012-12-04 14:05 4026这是一个用来监控Oracle Data Guard环境下从主库 ... -
Oracle sql loader使用速成
2012-11-30 14:14 4834Oracle SQL LOADER是Oracle的 ... -
Oracle 11g2的监听器配置
2012-11-29 14:34 4319Oracle的监听器服务注册 ... -
Deleting archivelog on physical standby with RMAN in Oracle 10g
2012-11-28 13:25 0Turns out to be quite easy ... -
to_char将number转成string的小技巧
2012-11-27 14:14 10572很多数据转换处理操作时,会遇到将0.007007040000转 ... -
to_date转成字符串时ORA-01843 not a valid month 问题分析
2012-11-26 16:38 29154(注,本文三度易稿) 在开发Oracle SQL或PL/SQL ... -
Data Guard 10g 的保护级别为RESYNCHRONIZATION问题
2012-11-25 11:01 1878Oracle的data guard创建完成 ...
相关推荐
使用PLSQL开发XML PUBLISHER报表的步骤
PLSQL程序开发指南 PLSQL程序开发指南 PLSQL程序开发指南 PLSQL程序开发指南
PLSQL开发初步.ppt,不错的一份资料
oracle提高必须品,plsql开发总结,都是精华,你指的拥有啊!
快速掌握PLSQL的的编写及相关基础知识,欢迎想快速掌握基础的人下载!
Oracle+PLSQL开发基础
Oracle+PLSQL+高级程序设计..........
在不安装oracle客户端的前提下,是用plsql,实现方法。
PLSQL开发中动态SQL的使用方法.doc
Oracle PLSQL ProGramming 开发指南
PLSQL开发指南(中文) PLSQL开发指南(中文) PLSQL开发指南(中文)
erp报表中大部分是使用plsql经行开发的,PLSQL开发报表入门
这是一个能让初学者入门,并将你带入更高层台阶的书。书中将PL SQL描写的相当详细,希望能帮到大家!
plsql的注册程序
PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全) PLSQL程序设计(全)
plsql安装程序plsql.rar......................................
使用PLSQL创建Oracle数据库User用户并设置权限 主要演示了如何使用 PLSQL 创建一个 User 用户, 并使之具有创建表、修改表、访问表的权限。解决了 使用自定义用户创建表时出现的 没有表空间的访问权限的问题。
不安装Oracle客户端_使用PLSQL_Developer和_Instant_Client工具包连接oracle
Oracle 8i PLSQL高级程序设计.pdf