oracle proc

1: --------------------------------------
在oracle创建数值型数组
CREATE OR REPLACE type numArray as table of number;

创建一个表
CREATE TABLE ARRAYTEST
(
ID NUMBER,
NAME VARCHAR2(10 BYTE)
)

创建一个存储过程
CREATE OR REPLACE procedure Proc_NumArray_test(nums in numArray) as
vIndex integer;
begin
for vIndex in 1..nums.count loop
insert
into ARRAYTEST(id,name)
values(nums(vIndex),nums(vIndex));
end loop;
commit;
end Proc_NumArray_test;
2:----------------------------------------------------
create or replace type myvarray_list as varray(10) of varchar2(50);
/

类型已创建。

SQL> create or replace procedure show_list(
p_varlist in myvarray_list,value1 in string,value2 out string
)
is
str varchar2(50);
begin
for x in 1..p_varlist.count loop
select min(userphone) into str from where userflag = '0' and sn = p_varlist(x)
if str is null then
update reg_user set userphone = '111' , userflag = '0' where user_id=1 ;
value2 := str;
exit;
end if;

end loop;
end;
/
3退出循环,参数返回值的问题
过程:
CREATE OR REPLACE PROCEDURE test(a IN VARCHAR2, b OUT VARCHAR2) IS
BEGIN
b:= a;
END;

调用:
SQL> var testb varchar2
SQL> exec test( 'a ',:testb);

PL/SQL 过程已成功完成。

SQL> print testb;

TESTB
--------------------------------
a
不为空
经试验,判断strsql不为null或者''的方法一样，都是
if strsql is not null then

end if;

不能用
if strsql <> '' then
end if;
因为pl/sql认为''也是null，所以这样的判断条件失效,证明如下：

SQL>
SQL> declare
2 cc varchar2(10) :='';
3 begin
4 if cc is null then
5 dbms_output.put_line('为null');
6 end if;
7 end;
8 /
为null

end if;

create or replace procedure dd is
i pls_integer;
begin
i:=0;
loop
i:=i+1;
if i> 10 then
exit;
end if;

dbms_output.put_line(i);
end loop;
end;
ibatis如何取得存储过程的返回值
<parameterMap class="map" id="swapParameters">
<parameter property="namestr" javaType="java.lang.String" jdbcType="varchar" mode="IN"/>
<parameter property="returnid" javaType="java.lang.Integer" jdbcType="int" mode="OUT"/>
</parameterMap>
<procedure id="insertNameByProcedure" parameterMap="swapParameters">
{call UP_GETINSERTEDIDENTITY_TT(?,?)}
</procedure>


sql 代码
CREATE PROCEDURE `UP_GETINSERTEDIDENTITY_TT`(inname VARCHAR(20), OUT returnid INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ''
BEGIN
insert into `tt`(NAME) values(inname);
select LAST_INSERT_ID() into returnid ;
END;
java 代码
Integer returnid = null;
HashMap<String,Object> paramMap = new HashMap<String,Object>();
paramMap.put("namestr", insertname);
paramMap.put("returnid", returnid);
this.getSqlMapClientTemplate().queryForObject("insertNameByProcedure", paramMap);
return returnid;