type Tcursor is ref cursorprocedure getlevelbyname(p_level in varchar2,outCursor out TCursor) as CC TCursor; begin
open CC for select t.cid Cvalue,t.code_name Ctext from code_title_level t where t.cname=p_level and t.active='Y';
outCursor:=CC;
end;
用REF CURSOR。 例子同楼上的!
我试试,顺便再问各位刚碰到的一个问题,有两个表t1(ID id,zoneno varchar2(4))、t2(zoneno varchar2),我想执行如下的语句: insert into t1 values(s_id.nextval,select * from t2),可是会出错,要如何才能实现该功能。s_id是我定义的sequence变量。
insert into t1 values(s_id.nextval,select t2.zoneno from t2)
create trigger name_tri before insert on t1 for each row begin select s_id.nextval into :new.id from dual; end; /insert into t1 (zoneno) (select * from t2);
insert into t1 (select s_id.nextval,t2.zoneno from t2)
insert into t1 (select s_id.nextval,t2.zoneno from t2)不行的,因为t2表中的记录不仅一条
SQL> select * from aa;ID FID -- --- 1 0 2 1 3 1 4 2 5 3 6 4 6 57 rows selectedCREATE SEQUENCE s_id INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 CYCLE NOCACHE NOORDER;SQL> desc bb; Name Type Nullable Default Comments ---- ----------- -------- ------- -------- A VARCHAR2(2) Y B VARCHAR2(2) Y SQL> insert into bb select s_id.nextval,fid from aa;7 rows insertedSQL> select * from bb;A B -- -- 1 0 2 1 3 1 4 2 5 3 6 4 7 57 rows selected
高,实在是高人,请问高人:我想在存储过程中实现创建序号和表,如(肯定出错,我主要是想表达我的想法):create .... (v_name int varchar2, v_msg out number) is v_err_code number; begin drop sequence $seq; create sequence $seq increment by 1 start with 1 minvalue 1; create table $seq ( ..... ) exception when others then ... end ..;其中:seq是一个变量,用来创建序号和表明的,如seq='best',则该存储过程能自动创建best序号和best表名,分数没问题,要多少给多少!在线!
create or replace procedure name_pro(seq in varchar2,v_name int varchar2, v_msg out number) as str varchar2(50); begin str:='create or replace sequence '||seq||' increment by 1 start with 1 minvalue 1';--注意用要权限grant create any sequence to 当前用户 execute immediate str; str:='create table '||seq||' ( ..... )';--grant create any table to 当前用户 execute immediate str; exception when others then ... end; /
我试了,基本上OK,先谢谢了!,但存在以下两个问题: 1:str:='create or replace sequence '||seq||' increment by 1 start with 1 不可含有replace,去掉即可。2:insert into bb select s_id.nextval,fid from aa;中若加上group by 就会提示不能用序号,可我程序又必须能实现此项功能,请问高兄台如何解决,不胜感激!!在线!
“beckham”,在吗,再拉兄弟一把!!
insert into bb select s_id.nextval,fid from aa group by fid;--是这样吗?把你的语句写出来
很高兴看到你的到来,我前面写错了,不是group by ,是order by 如: insert into bb(id,zoneno,accno,cino,amount) select s_id.nextval,zoneno,accno,cino,amount from aa where zoneno<'aaaa' and amount>111.11 order by zoneno,accno;又:type Tcursor is ref cursor procedure getlevelbyname(p_level in varchar2,outCursor out TCursor) as CC TCursor; ... 中的type Tcursor is ref cursor在存储过程中如何定义??实在不好意思,我对Oracle不熟,以前用的较多的是Informix ,谢谢老兄了!
问题一: QL> insert into bb select s_id.nextval,fid from aa order by fid;insert into bb select s_id.nextval,fid from aa order by fidORA-02287: 此处不允许序号序列号不是表所有,它是不以表排序而排序,解决方法还是以触发器生成吧.create trigger name_tri before insert on t1 for each row begin select s_id.nextval into :new.id from dual; end; / insert into bb(zoneno,accno,cino,amount) select zoneno,accno,cino,amount from aa where zoneno<'aaaa' and amount>111.11 order by zoneno,accno;问题二: 首先创建一个包 CREATE OR REPLACE PACKAGE pkg_test AS TYPE TCursor IS REF CURSOR;END pkg_test; / procedure getlevelbyname(p_level in varchar2,outCursor out pkg_test.TCursor) as CC pkg_test.TCursor; ............
CC TCursor;
begin
open CC for
select t.cid Cvalue,t.code_name Ctext from code_title_level t
where t.cname=p_level
and t.active='Y';
outCursor:=CC;
end;
例子同楼上的!
insert into t1 values(s_id.nextval,select * from t2),可是会出错,要如何才能实现该功能。s_id是我定义的sequence变量。
before insert on t1
for each row
begin
select s_id.nextval into :new.id from dual;
end;
/insert into t1 (zoneno) (select * from t2);
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 57 rows selectedCREATE SEQUENCE s_id INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 CYCLE
NOCACHE NOORDER;SQL> desc bb;
Name Type Nullable Default Comments
---- ----------- -------- ------- --------
A VARCHAR2(2) Y
B VARCHAR2(2) Y SQL> insert into bb select s_id.nextval,fid from aa;7 rows insertedSQL> select * from bb;A B
-- --
1 0
2 1
3 1
4 2
5 3
6 4
7 57 rows selected
(v_name int varchar2,
v_msg out number)
is
v_err_code number;
begin
drop sequence $seq;
create sequence $seq increment by 1 start with 1 minvalue 1;
create table $seq
(
.....
)
exception
when others then
...
end ..;其中:seq是一个变量,用来创建序号和表明的,如seq='best',则该存储过程能自动创建best序号和best表名,分数没问题,要多少给多少!在线!
v_msg out number)
as
str varchar2(50);
begin
str:='create or replace sequence '||seq||' increment by 1 start with 1 minvalue 1';--注意用要权限grant create any sequence to 当前用户
execute immediate str;
str:='create table '||seq||'
(
.....
)';--grant create any table to 当前用户
execute immediate str;
exception
when others then
...
end;
/
1:str:='create or replace sequence '||seq||' increment by 1 start with 1 不可含有replace,去掉即可。2:insert into bb select s_id.nextval,fid from aa;中若加上group by 就会提示不能用序号,可我程序又必须能实现此项功能,请问高兄台如何解决,不胜感激!!在线!
insert into bb(id,zoneno,accno,cino,amount) select s_id.nextval,zoneno,accno,cino,amount from aa where zoneno<'aaaa' and amount>111.11 order by zoneno,accno;又:type Tcursor is ref cursor
procedure getlevelbyname(p_level in varchar2,outCursor out TCursor) as
CC TCursor; ...
中的type Tcursor is ref cursor在存储过程中如何定义??实在不好意思,我对Oracle不熟,以前用的较多的是Informix ,谢谢老兄了!
QL> insert into bb select s_id.nextval,fid from aa order by fid;insert into bb select s_id.nextval,fid from aa order by fidORA-02287: 此处不允许序号序列号不是表所有,它是不以表排序而排序,解决方法还是以触发器生成吧.create trigger name_tri
before insert on t1
for each row
begin
select s_id.nextval into :new.id from dual;
end;
/
insert into bb(zoneno,accno,cino,amount) select zoneno,accno,cino,amount from aa where zoneno<'aaaa' and amount>111.11 order by zoneno,accno;问题二:
首先创建一个包
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE TCursor IS REF CURSOR;END pkg_test;
/
procedure getlevelbyname(p_level in varchar2,outCursor out pkg_test.TCursor)
as
CC pkg_test.TCursor;
............
user_id(pk)
user_name
.....用户明细表user_record
user_id --用户ID
client_id --客户id
.....
pk(user_id,client_id)web端分页以用户表与用户明细表关联
1。会话特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
ON COMMIT PRESERVE ROWS; 2。事务特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的临时表虽然是存在的,但是你试一下insert 一条记录然后用别的连接登上去select,记录是空的,明白了吧。
下面两句话再贴一下:
--ON COMMIT DELETE ROWS 说明临时表是事务指定,每次提交后ORACLE将截断表(删除全部行)
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定,当中断会话时ORACLE将截断表。