哪里都可以写,只要是文本编辑器 create or replace procedure test as temp emp%rowtype; begin select * into temp from emp; exception when others then raise; end test; 然后在sql plus中生成。 最后exec test执行
在《过程》中可以写存储过程-单个的 在《程序包》和《程序包体》里写-组合的 推荐你看书——《ORACLE9I PL/SQL 程序设计》 给个例子昨天写的 :) 写在过程中的 IS intIndex int; intBGWmin number; intBGWmax number; v_id 城建工程项目著录表.编号%type; v_flh 城建工程项目著录表.分类号%type; v_bgwh 城建工程项目著录表.存放位置起始号%type; cursor Proj is select 编号,分类号,存放位置起始号 from 城建工程项目著录表 where 责任者属性编号 = 4; BEGIN open Proj; loop fetch Proj into v_id,v_flh,v_bgwh; exit when Proj%notfound; --判断保管位号 intIndex := instr(v_bgwh,'-'); if intIndex > 0 then intBGWMin:=my_to_number(substr(v_bgwh,1,intIndex-1)); intBGWMax:=my_to_number(substr(v_bgwh,intIndex+1,Length(v_bgwh))); else intBGWMin:=my_to_number(v_bgwh); intBGWMax:=intBGWMin; end if; --将工程编号写入案卷著录表 update 城建工程案卷著录表 set 工程项目编号=v_id where 工程项目编号 is null and to_number(存放位) >= intBGWMin and to_number(存放位) <= intBGWMax and 分类号=v_flh; commit; end loop; close Proj; END ;
create table table_added (tablename varchar2(30) primary key );create table table_modified (tablename varchar2(30), sql_modified varchar2(100) );CREATE OR REPLACE PROCEDURE wffz_struct as cursor c1 is select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns@wffz_new where substr(TABLE_NAME,1,1) <> 'V' order by TABLE_NAME,COLUMN_NAME; ps_TABLE_NAME varchar2(30); ps_COLUMN_NAME varchar2(30); ps_DATA_TYPE varchar2(30); pn_DATA_LENGTH number; ps_rowid VARCHAR2(30); ps_temp VARCHAR2(250); pi_tmp integer; pi_tmp0 integer; pi_count integer := 0; begin open c1; fetch c1 into ps_TABLE_NAME,ps_COLUMN_NAME,ps_DATA_TYPE,pn_DATA_LENGTH; loop exit when c1%notfound; select count(*) into pi_tmp from user_tab_columns@wffz_old where TABLE_NAME = ps_TABLE_NAME; if pi_tmp = 0 then --新增表 pi_count := pi_count + 1; begin insert into table_added values(ps_TABLE_NAME); commit; exception when others then null; end; else select count(*) into pi_tmp from user_tab_columns@wffz_old where TABLE_NAME = ps_TABLE_NAME and COLUMN_NAME = ps_COLUMN_NAME; if pi_tmp = 0 then --新增字段 pi_count := pi_count + 1; begin insert into table_modified values(ps_TABLE_NAME, 'alter table '||ps_TABLE_NAME||' add ('||ps_COLUMN_NAME||' '||ps_DATA_TYPE||'('||pn_DATA_LENGTH||'))'); commit; exception when others then null; end; else select count(*) into pi_tmp from user_tab_columns@wffz_old where TABLE_NAME = ps_TABLE_NAME and COLUMN_NAME = ps_COLUMN_NAME and DATA_TYPE = ps_DATA_TYPE and DATA_LENGTH <> pn_DATA_LENGTH; if pi_tmp > 0 then pi_count := pi_count + 1; begin insert into table_modified values(ps_TABLE_NAME, 'alter table '||ps_TABLE_NAME||' modify ('||ps_COLUMN_NAME||' '||ps_DATA_TYPE||'('||pn_DATA_LENGTH||'))'); commit; exception when others then null; end; end if; end if; end if; --dbms_output.put_line(ps_TABLE_NAME||':'||ps_COLUMN_NAME||':'||ps_DATA_TYPE||':'||pn_DATA_LENGTH); /* where rowid = chartorowid(ps_rowid); exception when others then end ; */ fetch c1 into ps_TABLE_NAME,ps_COLUMN_NAME,ps_DATA_TYPE,pn_DATA_LENGTH; end loop; close c1; dbms_output.put_line('pi_count:'||pi_count); end; /
这是ORACLE中的问题 select * into temp from emp temp变量不能赋予SELECT查询出来的结果集,你可以加一个WHERE条件 保证只检索出一条记录就可以了 如 :ID=911
我加了一个条件empno='7844'后,exec test,执行是成功了,但得到‘PL/SQL 过程已成功完成。’的提示,为什么没有我要检索的结果呢? 我明明是select * from emp where empno='7844'呀,有结果的呀?
存在变量temp中,你又没有显示他们.
set serveroutput on create or replace procedure test as temp emp%rowtype; begin select * into temp from emp where where empno='7844'; dbms_output.put_line(temp.empno); exception when others then raise; end test; /exec ...
这一句是什么意思dbms_output.put_line(temp.empno);编译不过
实际上我的意思就是要实现这一句 select * from emp 用存储过程如何实现
sorry:22:13:10 jlanzpa817>create or replace procedure demo_proc 22:13:23 2 as 22:13:23 3 l_cnt number default 0; 22:13:23 4 begin 22:13:23 5 for x in ( select * from tab) 22:13:23 6 loop 22:13:23 7 dbms_output.put_line(x.tname); 22:13:23 8 end loop; 22:13:23 9 end; 22:13:23 10 /过程已创建。已用时间: 00: 00: 00.80 22:13:24 jlanzpa817>exec demo_proc PL/SQL 过程已成功完成。已用时间: 00: 00: 00.10 22:13:30 jlanzpa817>exec demo_proc A AJSPB AUTHOR_ADDRESS_ARR AUTHOR_INFO AUTHS CLHJFB FLWS FZXYRQZCS HJDY JL OTHERFIELD TABLE_ADDED TABLE_MODIFIED TEST TEST_TRIGGERPL/SQL 过程已成功完成。已用时间: 00: 00: 00.50
set time on set timing onfor more,use sho all
在 glogin.sql 中加: sho user set time on set timing on set serveroutput on set sqlprompt jlanzpa817> dbms_output.put_line(x.列1||x.列2||。); 好象没有其他办法了.
create or replace procedure test
as
temp emp%rowtype;
begin
select * into temp from emp;
exception
when others then
raise;
end test;
然后在sql plus中生成。
最后exec test执行
在《程序包》和《程序包体》里写-组合的
推荐你看书——《ORACLE9I PL/SQL 程序设计》
给个例子昨天写的 :) 写在过程中的 IS
intIndex int;
intBGWmin number;
intBGWmax number;
v_id 城建工程项目著录表.编号%type;
v_flh 城建工程项目著录表.分类号%type;
v_bgwh 城建工程项目著录表.存放位置起始号%type;
cursor Proj is
select 编号,分类号,存放位置起始号 from 城建工程项目著录表 where 责任者属性编号 = 4;
BEGIN
open Proj;
loop
fetch Proj into v_id,v_flh,v_bgwh;
exit when Proj%notfound;
--判断保管位号
intIndex := instr(v_bgwh,'-');
if intIndex > 0 then
intBGWMin:=my_to_number(substr(v_bgwh,1,intIndex-1));
intBGWMax:=my_to_number(substr(v_bgwh,intIndex+1,Length(v_bgwh)));
else
intBGWMin:=my_to_number(v_bgwh);
intBGWMax:=intBGWMin;
end if;
--将工程编号写入案卷著录表
update 城建工程案卷著录表 set 工程项目编号=v_id where 工程项目编号 is null and to_number(存放位) >= intBGWMin and
to_number(存放位) <= intBGWMax and 分类号=v_flh;
commit;
end loop;
close Proj;
END ;
在前端界面中可调用,这个就不是一两句话能说清的了 ^_^
在sql plus 中简单一些
SQL>BEGIN
PROCNAME --存储过程名
END;
/ --回车
对了在《过程》在《程序包》和《程序包体》里写-有语法检查
(tablename varchar2(30) primary key
);create table table_modified
(tablename varchar2(30),
sql_modified varchar2(100)
);CREATE OR REPLACE PROCEDURE wffz_struct
as
cursor c1 is
select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
from user_tab_columns@wffz_new
where substr(TABLE_NAME,1,1) <> 'V'
order by TABLE_NAME,COLUMN_NAME;
ps_TABLE_NAME varchar2(30);
ps_COLUMN_NAME varchar2(30);
ps_DATA_TYPE varchar2(30);
pn_DATA_LENGTH number;
ps_rowid VARCHAR2(30);
ps_temp VARCHAR2(250);
pi_tmp integer;
pi_tmp0 integer;
pi_count integer := 0;
begin
open c1;
fetch c1 into ps_TABLE_NAME,ps_COLUMN_NAME,ps_DATA_TYPE,pn_DATA_LENGTH;
loop
exit when c1%notfound;
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME;
if pi_tmp = 0 then --新增表
pi_count := pi_count + 1;
begin
insert into table_added values(ps_TABLE_NAME);
commit;
exception when others then
null;
end;
else
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME and
COLUMN_NAME = ps_COLUMN_NAME;
if pi_tmp = 0 then --新增字段
pi_count := pi_count + 1;
begin
insert into table_modified values(ps_TABLE_NAME,
'alter table '||ps_TABLE_NAME||' add ('||ps_COLUMN_NAME||' '||ps_DATA_TYPE||'('||pn_DATA_LENGTH||'))');
commit;
exception when others then
null;
end;
else
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME and
COLUMN_NAME = ps_COLUMN_NAME and
DATA_TYPE = ps_DATA_TYPE and
DATA_LENGTH <> pn_DATA_LENGTH;
if pi_tmp > 0 then
pi_count := pi_count + 1;
begin
insert into table_modified values(ps_TABLE_NAME,
'alter table '||ps_TABLE_NAME||' modify ('||ps_COLUMN_NAME||' '||ps_DATA_TYPE||'('||pn_DATA_LENGTH||'))');
commit;
exception when others then
null;
end;
end if;
end if;
end if;
--dbms_output.put_line(ps_TABLE_NAME||':'||ps_COLUMN_NAME||':'||ps_DATA_TYPE||':'||pn_DATA_LENGTH);
/* where rowid = chartorowid(ps_rowid);
exception when others then
end ;
*/
fetch c1 into ps_TABLE_NAME,ps_COLUMN_NAME,ps_DATA_TYPE,pn_DATA_LENGTH;
end loop;
close c1;
dbms_output.put_line('pi_count:'||pi_count);
end;
/
回:bachelor(鹰飞九月),存储过程是提示被建立了,但是不能执行
提示:
*
ERROR 位于第 1 行:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在"SCOTT.test", line 8
ORA-06512: 在line 1
能帮忙看个帖子么,急用
http://www.csdn.net/expert/topic/1034/1034604.xml?temp=.3523828
select * into temp from emp
temp变量不能赋予SELECT查询出来的结果集,你可以加一个WHERE条件
保证只检索出一条记录就可以了 如 :ID=911
我明明是select * from emp where empno='7844'呀,有结果的呀?
as
temp emp%rowtype;
begin
select * into temp from emp where where empno='7844';
dbms_output.put_line(temp.empno);
exception
when others then
raise;
end test;
/exec ...
select * from emp
用存储过程如何实现
22:13:23 2 as
22:13:23 3 l_cnt number default 0;
22:13:23 4 begin
22:13:23 5 for x in ( select * from tab)
22:13:23 6 loop
22:13:23 7 dbms_output.put_line(x.tname);
22:13:23 8 end loop;
22:13:23 9 end;
22:13:23 10 /过程已创建。已用时间: 00: 00: 00.80
22:13:24 jlanzpa817>exec demo_proc PL/SQL 过程已成功完成。已用时间: 00: 00: 00.10
22:13:30 jlanzpa817>exec demo_proc
A
AJSPB
AUTHOR_ADDRESS_ARR
AUTHOR_INFO
AUTHS
CLHJFB
FLWS
FZXYRQZCS
HJDY
JL
OTHERFIELD
TABLE_ADDED
TABLE_MODIFIED
TEST
TEST_TRIGGERPL/SQL 过程已成功完成。已用时间: 00: 00: 00.50
set timing onfor more,use sho all
sho user
set time on
set timing on
set serveroutput on
set sqlprompt jlanzpa817>
dbms_output.put_line(x.列1||x.列2||。);
好象没有其他办法了.