婚纱礼服外贸网站/营销网站类型
0. 常用函数
substr(str,start,length);//可以反向索引。length不指定就到结尾
to_number(str);//str转number
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;//日期转字符
nvl(tt,0);//若是空值则为0
1. 游标
对于集合数据的处理，学会使用带参数的方式。
同一个会话默认最多300个光标
set system set open_cursors=400 scope=
{both;仅更改当前memory;spfile;重启生效}
//属性
//cus1%found cus1%notfound cus1%isopen cus1%rowcount影响行数
SET serveroutput ON;
DECLARE
CURSOR c1
IS
SELECT bookno,booktitle FROM bebook;
bookno bebook.bookno%type;//引用类型
booktitle bebook.booktitle%type;
rowbook bebook%rowtype;//行引用类型
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO bookno,booktitle;
EXIT
WHEN c1 %notfound;
dbms_output.put_line('this string breaks here.'||bookno||booktitle);
END LOOP;
END;
//带参数的光标
Cursor
cemp(dno number) is select ename from emp where deptno = dno;
Open cemp(10);
2. rowtype
利用这个数据类型增加程序的健壮性。不会受到表结构更改导致程序更改。
记录可以整体赋值
rowtype参考
rowtype参考
//读入数据到rowtype类型中
create table testtable();
r testtable%rowtype;
select * into r from testtable where pno=...;
//rowtype类型数据插入表中
insert into testtable2 values r;
-----------------------------------------------------
-----------------------------------------------------
declare
v_dept dept%rowtype;
begin
v_dept.deptno := 60;
v_dept.dname := 'sample';
v_dept.loc := 'chicago';
insert into dept values v_dept;
end;
declare
v_dept dept%rowtype;
begin
v_dept.deptno := 60;
v_dept.dname := 'sample2';
v_dept.loc := 'dallas';
update dept set ROW=v_dept where deptno=v_dept.deptno;
end;
declare
rwEmp t_mst_employee%rowtype;
begin
select * into rwEmp from t_mst_employee where emp_no='10001';
rwEmp.emp_no := '20001';
insert into t_mst_employee values rwEmp;
update t_mst_employee set ROW=rwEmp where emp_no='3900';
end;
3. 过程
create or replace PROCEDURE "STATISTICS_ORDERSUMxxx"(
branchNo BEbranch.branchNo%type,
reportPerson VARCHAR2,
ordersum_table_cursor OUT sys_refcursor)
IS
testcur sys_refcursor;
v_typegoodNo BEproduct.productClass%TYPE; --类别编号
v_pritypegoodNo BEproduct.productClass%TYPE; --上一游标读取的类别编号
v_branchNo BEbranch.branchNo%TYPE; --游标读取的分店编号
v_pribranchNo BEbranch.branchNo%TYPE; --上一个游标读取的分店编号
v_branchname BEbranch.branchName%type; --各分店
v_branchsum NUMBER; --分店总数
v_typenum NUMBER;
ordersum_table odreport1%rowtype;
--游标定义
CURSOR ordersum_cur(pno VARCHAR2)
IS
SELECT d.typegoodno,
SUM(b.quantity)
FROM BDprocureplan a,
BDplandetail b,
BEproduct c,
DTtypegood d
WHERE a.branchno =pno
AND a.planno = b.planno
AND b.productno = c.productno
AND c.productclass = d.typegoodno
GROUP BY d.typegoodno;
CURSOR branch_cur
IS
SELECT branchNo,branchName FROM BEbranch;
maketime DATE;
mycount INT:=0;
BEGIN
SELECT COUNT(*) INTO mycount FROM bebranch;
OPEN branch_cur;
LOOP
FETCH branch_cur INTO v_branchNo,v_branchname;
EXIT
WHEN branch_cur%NOTFOUND;
ordersum_table.branchname := v_branchname;
ordersum_table.branchno := v_branchno;
dbms_output.put_line(ordersum_table.branchname);
OPEN ordersum_cur( v_branchNo );
LOOP
FETCH ordersum_cur INTO v_typegoodNo,v_typenum;
EXIT
WHEN ordersum_cur%NOTFOUND;
CASE v_typegoodNo
WHEN'001'THEN
ordersum_table.clothessum := ordersum_table.clothessum+v_typenum;
WHEN'002'THEN
ordersum_table.shoesum:=ordersum_table.shoesum+v_typenum;
WHEN'003'THEN
ordersum_table.foodsum:=ordersum_table.foodsum+v_typenum;
WHEN'004'THEN
ordersum_table.sourcesum:=ordersum_table.sourcesum+v_typenum;
WHEN'005'THEN
ordersum_table.drinksum:=ordersum_table.drinksum+v_typenum;
WHEN'006'THEN
ordersum_table.drinkingsum:=ordersum_table.drinkingsum+v_typenum;
WHEN'007'THEN
ordersum_table.vegetablesum:=ordersum_table.vegetablesum+v_typenum;
WHEN'008'THEN
ordersum_table.fruitsum:=ordersum_table.fruitsum+v_typenum;
WHEN'009'THEN
ordersum_table.moatsum:=ordersum_table.moatsum+v_typenum;
WHEN'010'THEN
ordersum_table.electricsum:=ordersum_table.electricsum+v_typenum;
WHEN'011'THEN
ordersum_table.officesum:=ordersum_table.officesum+v_typenum;
WHEN'012'THEN
ordersum_table.studysum:=ordersum_table.studysum+v_typenum;
WHEN'013'THEN
ordersum_table.diansum:=ordersum_table.diansum+v_typenum;
END CASE;
ordersum_table.allsum := ordersum_table.allsum+v_typenum;
END LOOP;
insert into odreport1 values ordersum_table;
CLOSE ordersum_cur;
END LOOP;
CLOSE branch_cur;
COMMIT;
END "STATISTICS_ORDERSUMxxx";
Java中调用
//获取
CallableStatement cstmt = null;
String sAutoNo = null;
String sSql = "{call AUTO_No(?,?)}";
try {
cstmt = connDB.getConn().prepareCall(sSql);
cstmt.setString(1, tableName);
cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //注册字符变量
cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);//注册游标变量
cstmt.execute();
sAutoNo = cstmt.getString(2);//cstmt.getInt(2);
ResultSet rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
//do something
}