- decalre
- var1 number(2);
- var2 char(2) := ‘11‘;
-
- begin
-
- end;
- if a = 1 or b = 2 then
-
- elsif c = 3 then
-
- else
-
- end if;
case语句如果作为分支控制语句,最后结束语句是end case,如果是作为select语句里的控制语句则只需要end。
- declare
- num number(10) := 1;
- begin
- case
- when num = 0 then dbms_output.put_line( ‘zero‘);
- when num = 1 then dbms_output.put_line( ‘one‘);
- else dbms_output.put_line( ‘default‘);
- end case;
-
- case num
- when 0 then dbms_output.put_line( ‘zero‘);
- when 1 then dbms_output.put_line( ‘one‘);
- else dbms_output.put_line( ‘default‘);
- end case;
- end;
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;
oracle 存储过程 基础
原文:http://www.cnblogs.com/copo/p/4720930.html