1 sql函数语法
1.1 条件语句 if else
--if-then
if condition then
sequece_of statements
end if;
--if-then-else
if condition then
sequece_of statements1
else
sequece_of statements1
end if;
--if-then-elseif-else
if condition1 then
sequece_of statements1
elseif condition1 then
sequece_of statements2
else
sequece_of statements3
end if;
--例子存储函数
create or replace function testifelse(idd in varchar) return number is
Result number;
v_para1 number;
v_para2 number;
message varchar2(200);
begin
select count(1) into v_para1 from dual;
if v_para1 <> 0 then v_para1 := '111';
end if;
if v_para2 is null then message := 'number类型的默认是null,不是0';
end if;
dbms_output.put_line('v_para1='||v_para1||'---'||'v_para2='||message);
return(Result);
end testifelse;
1.2 case 语句
case selector
when expression1 then sequence_of_statemts1;
end case;
--例子1:
select case
when 1 = 1 and 2 = 2 then
'正确'
end case
from dual
--例子2 函数
create or replace function testCase(condition in varchar2) return varchar2 is
Result varchar2(20);
v_param dual.dummy%type;
begin
case condition
when '' then Result:='参数是空';
when '1' then Result:='参数是1';
when '2' then Result:='参数是2';
dbms_output.put_line(v_param);
end case;
return(Result);
end testCase;
--例子2 存储过程
create or replace procedure testcase(para_in in varchar2,
para_out out varchar2) is
v_param varchar2(20);
begin
dbms_output.put_line('存储过程');
case para_in
when '1' then
para_out := para_in || 'out参数';
when '2' then
para_out := para_in || 'out参数2';
end case;
end testcase;
1.3 for loop 数组以及数组类型
declare
i number;
type list_type is table of binary_integer index by binary_integer; -- 类型定义
list list_type;
begin
for i in reverse 1 .. 5 loop
list(i) := i * 1;
dbms_output.put_line(list(i));
end loop;
dbms_output.put_line(list.first());
end;
Declare
type my_text_table_type is table of varchar2(200) index by binary_integer;
l_text_table my_text_table_type;
l_index number;
begin
for l_index in 1 .. 5 loop
l_text_table(l_index) := l_index * l_index;
end loop;
l_index := l_text_table.first; --使用first方法
dbms_output.put_line('000'||l_text_table.first);
loop
exit when l_index is null;
dbms_output.put_line(l_index || ':' || l_text_table(l_index));
l_index := l_text_table.next(l_index);
end loop;
end;
下表中列出oracle中集合的方法
方法 描述 使用限制
COUNT 返回集合中元素的个数
DELETE 删除集合中所有元素
DELETE() 删除元素下标为x的元素,如果x为null,则集合保持不变 对VARRAY非法
DELETE(,) 删除元素下标从X到Y的元素,如果X>Y集合保持不变 对VARRAY非法
EXIST() 如果集合元素x已经初始化,则返回TRUE, 否则返回FALSE
EXTEND 在集合末尾添加一个元素 对Index_by非法
EXTEND() 在集合末尾添加x个元素 对Index_by非法
EXTEND(,) 在集合末尾添加元素n的x个副本 对Index_by非法
FIRST 返回集合中的第一个元素的下标号,对于VARRAY集合始终返回1。
LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.
LIMIT 返回VARRY集合的最大的元素个数,对于嵌套表和对于嵌套表和Index_by为null Index_by集合无用
NEXT() 返回在元素x之后及紧挨着它的元素的值,如果该元素是最后一个元素,则返回null.
PRIOR() 返回集合中在元素x之前紧挨着它的元素的值,如果该元素是第一个元素,则返回null。
TRI M 从集合末端开始删除一个元素 对于index_by不合法
TRIM() 从集合末端开始删除x个元素 对index_by不合法
1.4 实战例子
create or replace function getArrearage2(yuangongID in varchar2)
return number is
Result number;
money1 number;
money2 number;
money3 number;
begin
select count(1)
into money1
from (SELECT b.id_worker, SUM(B.ALLOW_MONEY) as totalMoney
FROM T_MONEY1 B
WHERE B.STATUS = '1'
AND B.FLAG = '1'
AND B.BILL_STAUTUS = '4'
AND B.ID_WORKER = yuangongID
group by b.id_worker);
if (money1 0) then
select totalMoney
into money1
from (SELECT b.id_worker, SUM(B.ALLOW_MONEY) as totalMoney
FROM T_MONEY1 B
WHERE B.STATUS = '1'
AND B.FLAG = '1'
AND B.BILL_STAUTUS = '4'
AND B.ID_WORKER = yuangongID
group by b.id_worker);
end if;
select count(1)
into money2
from (SELECT b.id_worker, sum(b.back_money) as back_money
FROM T_MONEY2 B
WHERE B.STATUS = '1'
AND B.FLAG = '1'
AND B.ID_WORKER = yuangongID
group by b.id_worker) tt;
if (money2 0) then
select tt.back_money
into money2
from (SELECT b.id_worker, sum(b.back_money) as back_money
FROM T_MONEY2 B
WHERE B.STATUS = '1'
AND B.FLAG = '1'
AND B.ID_WORKER = yuangongID
group by b.id_worker) tt;
end if;
select count(1)
into money3
from (SELECT p.id_worker, sum(c.money) as money
FROM T_MONEY3 C
LEFT JOIN MB_PAY_ASK P
ON P.ID = C.ID_PAYBILL
WHERE C.MONEY IS NOT NULL
AND C.MONEY '0'
AND C.STATUS = '1'
AND C.FLAG = '1'
AND P.STATUS = '1'
AND P.FLAG = '1'
AND P.ID_WORKER = yuangongID
AND P.PROPOSAL_STATUS = '4'
group by p.id_worker) xx;
if (money3 0) then
select xx.money
into money3
from (SELECT p.id_worker, sum(c.money) as money
FROM T_MONEY3 P
WHERE C.MONEY IS NOT NULL
AND C.MONEY '0'
AND C.STATUS = '1'
AND C.FLAG = '1'
group by p.id_worker) xx;
end if;
Result := money1 - money2 - money3;
return(Result);
end getArrearage2;