找一本sql/plus的书看一下,
举个例子create or replace procedure p_DataFinish
(v_DepCode in Department.Depcode%type,
v_Date in Date,
v_Random out varchar2,
v_Message out varchar2 )
is
--查询指定日期、指定电厂的数据上报情况
strRandom varchar2(50);
strDepDesc varchar2(80);
strProduce varchar2(20);
strMaterial varchar2(20);
strReservoir varchar2(20);
strTotal varchar2(20);
intTmp number(22);
cursor curDep is
select DepCode,DepDesc
from Department
where depCode like v_DepCode || '%'
and DepType = '电厂'
order by DepCode;
begin
--主要包括生产数据、燃料数据、水情数据和总体情况
--获取随机数
select round(dbms_random.value(1,1000000),8) into strRandom from dual;
delete from tmpFinish where random=v_random;
--查询并插入数据
for p_Dep in curdep loop
strtotal := '完成';
strDepdesc := p_Dep.DepDesc;
select count(1) into intTmp
from day_record
where to_char(drdate,'YYYYMMDD')=to_char(v_date,'YYYYMMDD')
and Depcode = p_Dep.DepCode
and drState = '2';
if intTmp>0 then
strproduce := '已上报';
else
Strproduce := '未上报';
Strtotal := '未完成';
end if;
if p_Dep.DepCode<>'104' and p_Dep.DepCode<>'105' and p_Dep.DepCode<>'106' then
select count(1) into intTmp
from recpstore
where to_char(rsDate,'YYYYMMDD') = to_char(v_Date,'YYYYMMDD')
and depCode = p_Dep.DepCode
and rsState = '2';
if intTmp>0 then
strmaterial := '已上报';
else
strMaterial := '未上报';
Strtotal := '未完成';
end if;
else
Strmaterial := '不用上报';
end if;
if p_Dep.DepCode='104' or p_Dep.DepCode='105' or p_Dep.DepCode='106' then
select count(1) into intTmp
from reservoirrecord
where to_char(reservoirDate,'YYYYMMDD') = to_char(v_date,'YYYYMMDD')
and sub_id in (select sub_ID
from sub_station
where substr(depcode,1,3) = p_Dep.DepCode)
and resvState = '2';
if Inttmp>0 then
Strreservoir := '已上报';
else
strreservoir :='未上报';
Strtotal := '未完成';
end if;
else
strreservoir := '不用上报';
end if;
insert into tmpfinish(电厂名称,生产数据,燃料数据,水情数据,总体情况,random)
values (strdepdesc,strproduce,strmaterial,strreservoir,strtotal,strrandom);
v_Random := Strrandom;
v_message := 'success';
end loop;
exception
when others then
rollback;
v_Message := '数据库发生不名错误!';
end;
举个例子create or replace procedure p_DataFinish
(v_DepCode in Department.Depcode%type,
v_Date in Date,
v_Random out varchar2,
v_Message out varchar2 )
is
--查询指定日期、指定电厂的数据上报情况
strRandom varchar2(50);
strDepDesc varchar2(80);
strProduce varchar2(20);
strMaterial varchar2(20);
strReservoir varchar2(20);
strTotal varchar2(20);
intTmp number(22);
cursor curDep is
select DepCode,DepDesc
from Department
where depCode like v_DepCode || '%'
and DepType = '电厂'
order by DepCode;
begin
--主要包括生产数据、燃料数据、水情数据和总体情况
--获取随机数
select round(dbms_random.value(1,1000000),8) into strRandom from dual;
delete from tmpFinish where random=v_random;
--查询并插入数据
for p_Dep in curdep loop
strtotal := '完成';
strDepdesc := p_Dep.DepDesc;
select count(1) into intTmp
from day_record
where to_char(drdate,'YYYYMMDD')=to_char(v_date,'YYYYMMDD')
and Depcode = p_Dep.DepCode
and drState = '2';
if intTmp>0 then
strproduce := '已上报';
else
Strproduce := '未上报';
Strtotal := '未完成';
end if;
if p_Dep.DepCode<>'104' and p_Dep.DepCode<>'105' and p_Dep.DepCode<>'106' then
select count(1) into intTmp
from recpstore
where to_char(rsDate,'YYYYMMDD') = to_char(v_Date,'YYYYMMDD')
and depCode = p_Dep.DepCode
and rsState = '2';
if intTmp>0 then
strmaterial := '已上报';
else
strMaterial := '未上报';
Strtotal := '未完成';
end if;
else
Strmaterial := '不用上报';
end if;
if p_Dep.DepCode='104' or p_Dep.DepCode='105' or p_Dep.DepCode='106' then
select count(1) into intTmp
from reservoirrecord
where to_char(reservoirDate,'YYYYMMDD') = to_char(v_date,'YYYYMMDD')
and sub_id in (select sub_ID
from sub_station
where substr(depcode,1,3) = p_Dep.DepCode)
and resvState = '2';
if Inttmp>0 then
Strreservoir := '已上报';
else
strreservoir :='未上报';
Strtotal := '未完成';
end if;
else
strreservoir := '不用上报';
end if;
insert into tmpfinish(电厂名称,生产数据,燃料数据,水情数据,总体情况,random)
values (strdepdesc,strproduce,strmaterial,strreservoir,strtotal,strrandom);
v_Random := Strrandom;
v_message := 'success';
end loop;
exception
when others then
rollback;
v_Message := '数据库发生不名错误!';
end;
p_FirstName students.first_name%TYPE,
p_LastName students.last_name%TYPE,
p_Major students.major%TYPE) AS
BEGIN
-- Insert a new row in the students table. Use
-- student_sequence to generate the new student ID, and
-- 0 for current_credits.
INSERT INTO students (ID, first_name, last_name,
major, current_credits)
VALUES (student_sequence.nextval, p_FirstName, p_LastName,
p_Major, 0);
END AddNewStudent;
/BEGIN
AddNewStudent('Zelda', 'Zudnik', 'Computer Science');
END;
/
create or replace procedure 过程名(Name in out type, Name in out type, ...) is
变量声明、游标声明。begin
内部程序控制
end 过程名;
create or replace procedure delete_commit(p_statement in varchar2,p_commit_batch_size in number default 10000) is
cid integer;
changed_statement varchar2(2000);
finished boolean;
nofrows integer;
lrowid rowid;
rowcnt integer;
errpsn integer;
sqlfcd integer;
errc integer;
errm varchar2(2000);
begin
--set transaction use rollback segment rbs9;
if(upper(p_statement) like '% WHERE %') then changed_statement:=p_statement||' AND rownum<'||to_char(p_commit_batch_size+1);
else changed_statement:=p_statement||' WHERE rownum<'||to_char(p_commit_batch_size+1);
end if;
begin
cid:=dbms_sql.open_cursor;
dbms_sql.parse(cid,changed_statement,dbms_sql.native);
rowcnt:=dbms_sql.last_row_count;
exception
when others then
errpsn:=dbms_sql.last_error_position;
sqlfcd:=dbms_sql.last_sql_function_code;
lrowid:=dbms_sql.last_row_id;
errc:=SQLCODE;
errm:=sqlerrm;
dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid));
raise_application_error(-20000,errm);
end;
finished:=FALSE;
while not(finished)
loop
begin
nofrows:=dbms_sql.execute(cid);
rowcnt:=dbms_sql.last_row_count;
exception
when others then
errpsn:=dbms_sql.last_error_position;
sqlfcd:=dbms_sql.last_sql_function_code;
lrowid:=dbms_sql.last_row_id;
errc:=SQLCODE;
errm:=sqlerrm;
dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid));
raise_application_error(-20000,errm);
end;
if nofrows=0 then
finished:=true;
else finished:=false;
end if;
commit;
end loop;
begin dbms_sql.close_cursor(cid);
exception
when others then
errpsn:=dbms_sql.last_error_position;
sqlfcd:=dbms_sql.last_sql_function_code;
lrowid:=dbms_sql.last_row_id;
errc:=SQLCODE;
errm:=sqlerrm;
dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid));
raise_application_error(-20000,errm);
end;
end delete_commit;
Connection con;
public void testproc(){
Connection con=DBManager.getInstance().getConnection();
String str="{call delete_commit(?,?)}";
try{
java.sql.CallableStatement call=con.prepareCall(str) ;
call.setString(1,"delete from test") ;
call.setInt(2,1000) ;
call.executeQuery() ;
call.close() ;
} catch(Exception ee){
ee.printStackTrace() ;
}
}