- decalre
-
- var1 number(2);
- var2 char(2) := '11';
-
- begin
-
- end;
- if a = 1 or b = 2 then
-
- elsif c = 3 then
-
- else
-
- end if;
for循环主要有两个用处。
1、 循环一个范围
格式:for i in [start .. end] loop ... end loop;
- for i in 0..9 loop
- dbms_output.put_line('i:' || i);
- end loop;
2、遍历隐式游标
隐式游标的好处是不需要手动关闭,方便
- for currow in (
- select t.col1, t.col2
- from tableName t
- where ...
- ) loop
- if currow.col1 = 0 then
- return;
- end if;
- end loop;
- isok := 9;
- while isok >= 0 loop
- isok := isok - 1;
-
- if isok = 8 then
- continue;
- end if;
-
- if isok = 4 then
- exit;
- end if;
-
- dbms_output.put_line('isok:' || isok);
- end loop;
-
- dbms_output.put_line('outside while loop .');
- create or replace procedure sp_name (
-
- uid in varchar2,
- startDate in date,
- defaultVar in varchar2 default "",
- isok out number,
- result out varchar2
- )
- as
-
- var1 varchar2(11);
- var2 number(2) := 123;
-
- begin
-
- dbms_output.put_line('isok:' || 'abc');
-
-
- sub_sp_name(param1, prarm2, outParam1, outParam2);
-
- end;
- create or replace function func (
-
- uid in varchar2,
- startDate in date,
- defaultVar in varchar2 default "",
- isok out number,
- result out varchar2
- )
- return number
- as
-
- var1 varchar2(11);
- var2 number(2) := 123;
-
- begin
-
- dbms_output.put_line('isok:' || 'abc');
-
-
- return ret_val;
- end;
1、两者定义类似,都可以带输入输出参数。
2、函数有返回值,存储过程没有。
3、函数的调用要在select语句里;而存储过程不用,可以独立调用。
隐式游标
隐式游标的好处是不需要手动关闭,方便
- for currow in (
- select t.col1, t.col2
- from tableName t
- where ...
- ) loop
- if currow.col1 = 0 then
- return;
- end if;
- end loop;
显式游标
- declare
- isok integer;
- v_event_id number(10);
- v_isagain number(2);
- v_rate number(2);
-
- v_sender char(11) := '13800138000';
-
- cursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender;
-
-
- begin
- open cursorVar;
- loop
- fetch cursorVar into v_event_id, v_isagain, v_rate;
- exit when cursorVar%notfound;
- dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);
- end loop;
-
- close cursorVar;
-
-
-
-
-
-
- end;
带参数游标
- declare
- isok integer;
- v_event_id number(10);
- v_isagain number(2);
- v_rate number(2);
-
- v_sender char(11) := '13800138000';
-
- cursor cursorVar(p_sender varchar2) is select event_id, isagain, rate from call_event where sender = p_sender;
-
- begin
- open cursorVar(v_sender);
- loop
- fetch cursorVar into v_event_id, v_isagain, v_rate;
- exit when cursorVar%notfound;
- dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);
- end loop;
-
- close cursorVar;
- end;
【转载】http://wen866595.iteye.com/blog/1733887
分享到:
相关推荐
1.查询时间段内执行的sql、Produce select * from v$sqlarea a where 1=1 and a.LAST_ACTIVE_TIME >= to_date( '2013-02-21 18:23:00','yyyy-MM-dd HH24:mi:ss') and a.LAST_ACTIVE_TIME < to_date( '2013-...
oracle存储过程中,实现成绩分段显示人数,produce中带三个传入参数:起始分数(例如0),总分(例如100),分数间隔(例如10)。一个返回参数为游标,用来返回结果集。
前言 最近在修复一个比较老的项目报表的bug的时候,因为对该项目不太熟悉,导致生产环境数据修改有误,于是求助导师帮忙回滚数据,现学习一下Oralce数据回滚以备不时之需。 查看某个时间点的表的数据 ...
Now in its third edition, this best-selling book continues to bring you some of the best thinking on how to apply Oracle Database to produce scalable applications that perform well and deliver correct...
对需要学习oracle函数的会有所帮助。掌握函数的代码书写,快速上手。
Produce feature rich web applications in APEX 4 2 Create smartphone applications using jQuery Mobile Use HTML5 and CSS3 in APEX Generate RESTful web services Working with the latest available plug in ...
Now in its second edition, this best-selling book by Tom Kyte of Ask Tom fame continues to bring you some of the best thinking on how to apply Oracle Database to produce scalable applications that ...
Now in its third edition, this best-selling book continues to bring you some of the best thinking on how to apply Oracle Database to produce scalable applications that perform well and deliver correct...
Oracle's JD Edwards EnterpriseOne One View Reporting provides an intuitive, easy-to-use method for you to access data and produce reports with Oracle BI Publisher.
@confusion as there are several tools now within Oracle that share this same name. OSWbb now provides an analysis tool OSWbba which analyzes the log files produced by OSWbb. This tool allows OSWbb to ...
The plan baselines can be evolved over time to produce better performance. B. The newly generated plans are directly placed into the SQL plan baseline without being verified. C. The new SQL ...
The Transformer Inspector tool has been developed by ... It has been conceived for the early stage of the migration process to produce statistical information for any Oracle Forms/Reports application.
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同则...
origin to produce sparse solutions. Furthermore, the penalty functions should be bounded by a constant to reduce bias and satisfy certain conditions to yield continuous solutions. A new algorithm is ...
A design handbook to orchestrate and manage flexible process-driven systems with Oracle BPM and SOA Suite 12c About This Book Learn key principles to model business processes with BPMN and BPEL, and ...
join合并大数据分析宏oracle teradata mysql sas社区stackoverflow statistics人工智慧AI Python R Java Javascript WPS Matlab SPSS Scala Perl CC#Excel MS Access JSON图形映射NLP自然语言处理机器学习igraph ...
Here, we’ll look at how to produce standard summary statistics and estimate regression and classification models with the pandas and statsmodels packages. pandas has functions for calculating ...
;;;;;;;;... 1.... 2.... 3.... 4.... 5.... 6.... The syntax of the file is extremely simple.... Section headers (e.g.... at runtime.... There is no name validation.... (e.g.... previously set variable or directive (e.g....