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')我创建了如下一个用户表,我想通过Oracle存储过程实现,返回多行记录,也就是说我想通过存储过程实现(select * from userTabel)这个语句的结果,这个Procedure应该怎么写呢?谢谢各位大侠,
我是Oracle初学者,刚刚入门,请多多关照,谢谢!
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')我创建了如下一个用户表,我想通过Oracle存储过程实现,返回多行记录,也就是说我想通过存储过程实现(select * from userTabel)这个语句的结果,这个Procedure应该怎么写呢?谢谢各位大侠,
我是Oracle初学者,刚刚入门,请多多关照,谢谢!
cursor c_row_rec
is
select table_name from user_tables ;
begin
for v_row_rec in c_row_rec loop
dbms_output.put_line(v_row_rec.table_name);
end loop;
end;
照着改改
这句代码在PROC中的时候,有该怎么写这个过程呢,怎样执行这个PROC呢,谢谢!
create or replace procedure query_tables as
select * from userTabel;
--执行
EXEC query_tables
只能用更复杂的解决办法: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;
/
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') 我创建了如下一个用户表,我想通过Oracle存储过程实现,返回多行记录,也就是说我想通过存储过程实现(select * from userTabel)这个语句的结果,这个Procedure应该怎么写呢?谢谢各位大侠,
我是Oracle初学者,刚刚入门,请多多关照,谢谢! 欢迎各位大侠,再关注哈这个小小的问题哈,我想要的结果是:
1 Albert
2 reboot
3 Jeff是一个结果集的形式,可以直接帮定到DataGrid中的数据集,谢谢!
如:OPEN 游标变量 FOR SELECT 。
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;
-- Created : 2008-07-17 8:35:52
-- Purpose :
-- Public type declarations
type mycur is ref cursor; function fun_GetRecords return mycur;end pkg_AA;
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;
-----------------------------
前台页面添加GridView控件,后台C#代码如下: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(); }
}
---------------------------
ITMiner希望是你所要的,也希望对大家有所帮助!!