17,086
社区成员
发帖
与我相关
我的任务
分享
create table userTabel(
userid number(10),
username varchar2(100),
constraint PK_USERID PRIMARY KEY(userid)
);
commit;
insert into userTabel values(1,'Albert');
insert into userTabel values(2,'reboot') ;
insert into userTabel values(3,'Jeff');
--创建包以游标的形式返回userTabel的结果集
create or replace package pkg_AA is
-- Author : ADMINISTRATOR
-- Created : 2008-07-17 8:35:52
-- Purpose :
-- Public type declarations
type mycur is ref cursor;
procedure fun_GetRecords(cur_return out mycur);
end pkg_AA;
create or replace package body pkg_AA is
-- Function and procedure implementations
procedure fun_GetRecords(cur_return out mycur)
is
begin
open cur_return for select * from Usertabel;
end fun_GetRecords;
end pkg_AA;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
public partial class OracleCursor_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindGridView();
}
}
private void BindGridView()
{
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
OracleCommand comm = new OracleCommand("pkg_AA.fun_GetRecords", conn);
comm.Parameters.Add("cur_return", OracleType.Cursor).Direction = ParameterDirection.Output;
comm.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
using (OracleDataAdapter da = new OracleDataAdapter(comm))
{
da.Fill(ds);
}
this.GridView1.DataSource = ds.Tables[0].DefaultView;
this.GridView1.DataBind();
}
}
create or replace package body pkg_AA is
-- Function and procedure implementations
function fun_GetRecords return mycur is
cur_return mycur;
begin
open cur_return for select * from Usertabel;
return cur_return;
end;
end pkg_AA;
drop PROCEDURE p_getTT;
drop TYPE T_T;
drop TYPE MYTYPE;
CREATE OR REPLACE TYPE MYTYPE AS OBJECT
(
USERID NUMBER(10),
USERNAME VARCHAR2(100)
);
/
CREATE OR REPLACE TYPE T_T IS TABLE OF MYTYPE;
/
CREATE OR REPLACE procedure p_getTT(p_o OUT T_T) AS
TYPE t_n IS TABLE OF NUMBER(10);
TYPE t_v IS TABLE OF VARCHAR2(100);
v_n t_n;
v_v t_v;
BEGIN
p_o:=T_T();
SELECT USERID,username BULK COLLECT INTO v_n,v_v FROM USERTABEL;
FOR I IN V_n.FIRST .. V_n.LAST LOOP
p_o.extend;
p_o(p_o.count):=MYTYPE(v_n(i),v_v(i));
END LOOP;
END;
/
DECLARE
V_T T_T := T_T();
BEGIN
P_GETTT(V_T);
FOR I IN V_T.FIRST .. V_T.LAST LOOP
DBMS_OUTPUT.PUT_LINE(V_T(I).USERID || ',' || V_T(I).USERNAME);
END LOOP;
END;
/
create or replace procedure query_tables as
select * from userTabel;
--执行
EXEC query_tables