存储过程定义，举个例子如下：
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;
for loop 循环示例
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();
v_Sql1 varchar2();
v_total_rec number;
v_total_page number;
begin
v_Sql1 := '
SELECT pz.mo,v.wip_entity_qty,
case nvl(aa.c_count,) when 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(),) as c_count FROM po_report_info_oqc po where po.attribute1 <> '' ''
and po.assort =
group by po.mo,po.attribute1
) aa on aa.mo = pz.mo and aa.attribute1 = o.mid
where pz.tamper= ''有'' and o.assort = '; 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();
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 > THEN
P_TOTAL_PAGE := P_TOTAL_PAGE + ;
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, ) * NVL(P_CURRENT_PAGE, ) -
NVL(P_PAGE_SIZE, );
P_ROWNUM_2 := NVL(P_ROWNUM_1, ) + NVL(P_PAGE_SIZE, ) + ;
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;
oracle调用的分页存储过程
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();
arr_list.Add();
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,);
cmd.Parameters.Add("p_type", OracleType.VarChar, );
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[];
cmd.Parameters["p_type"].Value = arry[];
cmd.Parameters["p_cur_page"].Value = arry[];
cmd.Parameters["p_page_size"].Value = arry[]; try
{
conn.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
arry[] = cmd.Parameters["x_tot_rec"].Value.ToString();
arry[] = cmd.Parameters["x_tot_page"].Value.ToString();
da.Dispose();
return ds.Tables[].DefaultView; }
catch (Exception ex)
{
throw new Exception(ex.Message); }
finally
{ cmd.Dispose();
conn.Close();
} }
c#web界面调用相关代码
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 from T where T.a='' )
update T set T.b= Where T.a=''
else
insert into T(a,b) values('',);
对的oracel 写法
MERGE INTO T T1
USING (SELECT '' AS a, 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(); --自定义的参数
begin
/*
sql语句
*/
return V_RESULT; --返回值
end GetInventoryType;