oracle数据库使用五个月的总结
存储过程定义，举个例子如下：
create or replace procedure test_person(id in Number, Ename In Varchar2, age In Varchar2, TCS out mytype) Is --TCS为输出游标 IS 为关键字 V_ename VARCHAR2(30); --临时参数 v_age NUMBER;--临时参数 V_Sql Varchar2(4000); tcs1 mytype; begin V_Sql:='';--自定义sql语句 Open tcs1 for V_Sql; LOOP FETCH tcs1 INTO V_ename , v_age ; EXIT WHEN tcs1%NOTFOUND;--当数据读完后 退出 不再执行后面的操作 /*** 这部分区域可以继续写要操作的sql */ END LOOP; CLOSE tcs1; Commit; Open tcs for '这里写查询sql，也可以像上面那样定个临时参数'； exception when others then rollback; --可以在这自定义错误信息写到自定义表 end;
2.循环数据源 进行后续操作比如插入，修改
create or replace procedure tt1 is begin for H in (这里写select语句) loop insert into iiuser.LSAPRWMS (列名) values (H.列名 ); end loop; COMMIT; exception when others then rollback; end tt1;
3.查询界面数据，采用全展示或分页方式显示数据，返回查询结果集，总页数和总记录条数,示例如下：
/************************************************ 获取mo_po板块的防拆线任务 ***********************************************/ create or replace procedure spGetMoTaskInfo(p_CONDITION in varchar2, p_type in varchar2, p_cur_page in number, p_page_size in number, x_tot_rec out number, x_tot_page out number, X_TCS out SYS_REFCURSOR) is v_Sql varchar2(4000); v_Sql1 varchar2(4000); v_total_rec number; v_total_page number; begin v_Sql1 := ' SELECT pz.mo,v.wip_entity_qty, case nvl(aa.c_count,0) when 0 then ''待粘贴'' else ''已粘贴'' end as status, v.wip_assembly_item,o.oqc_content, o.oqc_fdate,ea.line_code,ws.plant_name FROM po_report_info_zj pz left join po_report_info_oqc o on o.mo = pz.mo LEFT JOIN vw_order_info v on v.wip_entity_name = pz.mo left join Wip_Entity_allInfo_v ea on ea.wip_entity_name = pz.mo left join Wip_Plants ws on ws.plant_id = ea.plant_id left join ( SELECT po.mo,po.attribute1,nvl(count(1),0) as c_count FROM po_report_info_oqc po where po.attribute1 <> '' '' and po.assort = 2 group by po.mo,po.attribute1 ) aa on aa.mo = pz.mo and aa.attribute1 = o.mid where pz.tamper= ''有'' and o.assort = 1 '; v_Sql := v_Sql1 || p_CONDITION; if upper(p_type) = 'NON_PAGE' THEN open X_TCS for v_Sql; else begin PAGING_TOOL.sql_paging(v_Sql, p_cur_page, p_page_size, v_total_rec, v_total_page, x_TCS); --返回总页数，总记录 x_tot_rec := v_total_rec; --数据条数 x_tot_page := v_total_page;--总页数 end; end if; Dbms_Output.put_line(v_Sql); exception when others then raise; end;
-------------先创建包体 和过程名 参数 create or replace package PAGING_TOOL is type mycur is ref cursor; /**********************************/ /* 分页存储过程 */ /*传入SQL语句,返回结果集总记录数 */ /*以及一页记录 */ /**********************************/ procedure sql_paging(P_SQL VARCHAR2, P_CURRENT_PAGE NUMBER, P_PAGE_SIZE NUMBER, P_TOTAL_REC IN OUT NUMBER, P_TOTAL_PAGE IN OUT NUMBER, TCS OUT mycur); end; -----------------详细的分页存储过程 create or replace package body PAGING_TOOL is procedure sql_paging(P_SQL VARCHAR2, P_CURRENT_PAGE NUMBER, P_PAGE_SIZE NUMBER, P_TOTAL_REC IN OUT NUMBER, P_TOTAL_PAGE IN OUT NUMBER, TCS OUT mycur) is P_SQL_FINAL VARCHAR2(10000); P_ROWNUM_1 NUMBER; P_ROWNUM_2 NUMBER; P_MOD NUMBER; begin --当首次执行查询时，计算总共有多少条记录 IF P_TOTAL_PAGE IS NULL THEN BEGIN P_SQL_FINAL := 'SELECT COUNT(ROWNUM) FROM ('; P_SQL_FINAL := P_SQL_FINAL || P_SQL || ')'; execute immediate P_SQL_FINAL INTO P_TOTAL_PAGE; P_TOTAL_REC := P_TOTAL_PAGE; --计算总页数; P_MOD := P_TOTAL_PAGE MOD P_PAGE_SIZE; P_TOTAL_PAGE := TRUNC(P_TOTAL_PAGE / P_PAGE_SIZE); IF P_MOD > 0 THEN P_TOTAL_PAGE := P_TOTAL_PAGE + 1; END IF; END; END IF; --当没有符合条件的结果返回时; IF P_TOTAL_PAGE IS NOT NULL AND P_PAGE_SIZE IS NOT NULL AND P_CURRENT_PAGE IS NOT NULL THEN BEGIN P_ROWNUM_1 := NVL(P_PAGE_SIZE, 0) * NVL(P_CURRENT_PAGE, 0) - NVL(P_PAGE_SIZE, 0); P_ROWNUM_2 := NVL(P_ROWNUM_1, 0) + NVL(P_PAGE_SIZE, 0) + 1; P_SQL_FINAL := 'select bb.* '; P_SQL_FINAL := P_SQL_FINAL || ' from (select rownum row_id, aa.* '; P_SQL_FINAL := P_SQL_FINAL || ' from (' || P_SQL || ') aa '; P_SQL_FINAL := P_SQL_FINAL || ' where rownum < ' || P_ROWNUM_2 || ') bb '; P_SQL_FINAL := P_SQL_FINAL || ' where bb.row_id > ' || P_ROWNUM_1; open TCS for P_SQL_FINAL; END; END IF; end; end;
private void GetData(int pageIndex) { ArrayList arr_list = new ArrayList(); arr_list.Add(getCondition()); arr_list.Add("page"); arr_list.Add(pageIndex); arr_list.Add(DataGrid_Task.PageSize); arr_list.Add(0); arr_list.Add(0); Ptm.WipBaseInfoManage baseManager = new Ptm.WipBaseInfoManage(); DataView dv = baseManager.GetMoPoReportTaskList(arr_list); this.DataGrid_Task.DataSource = dv.Table; this.DataGrid_Task.DataBind(); } //对应的方法实现 public DataView GetMoPoReportTaskList(ArrayList arry) { OracleCommand cmd = new OracleCommand(); OracleConnection conn = new OracleConnection(strConn); cmd.Connection = conn; cmd.CommandText = "spGetMoTaskInfo"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("p_CONDITION",OracleType.VarChar,500); cmd.Parameters.Add("p_type", OracleType.VarChar, 10); cmd.Parameters.Add("p_cur_page", OracleType.Number); cmd.Parameters.Add("p_page_size", OracleType.Number); cmd.Parameters.Add("x_tot_rec", OracleType.Number); cmd.Parameters.Add("x_tot_page", OracleType.Number); cmd.Parameters.Add("X_TCS", OracleType.Cursor); cmd.Parameters["x_tot_rec"].Direction = ParameterDirection.Output; cmd.Parameters["x_tot_page"].Direction = ParameterDirection.Output; cmd.Parameters["X_TCS"].Direction = ParameterDirection.Output; cmd.Parameters["p_CONDITION"].Value = arry[0]; cmd.Parameters["p_type"].Value = arry[1]; cmd.Parameters["p_cur_page"].Value = arry[2]; cmd.Parameters["p_page_size"].Value = arry[3]; try { conn.Open(); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); arry[4] = cmd.Parameters["x_tot_rec"].Value.ToString(); arry[5] = cmd.Parameters["x_tot_page"].Value.ToString(); da.Dispose(); return ds.Tables[0].DefaultView; } catch (Exception ex) { throw new Exception(ex.Message); } finally { cmd.Dispose(); conn.Close(); } }
4.merge into 用法
可以用来两个表之间的关联数据更新 ，或同一个表的insert/update ,下面摘抄的两段代码如下：
merge into users using doctor on (users.user_id = doctor.doctorid) when matched then update set users.user_name = doctor.doctorname when not matched then insert values (doctor.doctorid, doctor.doctorid, '8736F1C243E3B14941A59FF736E1B5A8', doctor.doctorname, sysdate, 'T', ' ', doctor.deptid, 'b319dac7-2c5c-496a-bc36-7f3e1cc066b8');
SQL SERVER 写法 if exists(select 1 from T where T.a='1001' ) update T set T.b=2 Where T.a='1001' else insert into T(a,b) values('1001',2); 对的oracel 写法 MERGE INTO T T1 USING (SELECT '1001' AS a,2 AS b FROM dual) T2 ON ( T1.a=T2.a) WHEN MATCHED THEN UPDATE SET T1.b = T2.b WHEN NOT MATCHED THEN INSERT (a,b) VALUES(T2.a,T2.b);
5.function 函数,如下所示：
function 函数名称(参数名称 参数类型) return 参数类型 is --定义返回类型 V_RESULT varchar2(500); --自定义的参数 begin /* sql语句 */ return V_RESULT; --返回值 end GetInventoryType;
