刚开始学oracle,不知道如何用,请大哥们解答以下的问题。感谢不尽!
请问在哪里写存储过程?在哪里运行?
请给一个最简单的例子,不用带参数!例如select * from emp
conn scott/tiger
请问在哪里写存储过程?在哪里运行?
请给一个最简单的例子,不用带参数!例如select * from emp
conn scott/tiger
解决方案 »
- oracle 语句优化。有关联条件,但表在连接时还是merge join,建成临时表或变换表位置变成hash join 是什么原因
- 求助:oracle从sql server更新数据
- 存储过程里面动态查询sql语句,如何转义单引号?
- 怎么老是ORA-12560: TNS: 协议适配器错误
- DB2表中有ROWID吗?在线等,马上结帖.
- .net连接oracle的一个问题,求救
- 请教一个sql语句写法
- 请教sql,怎样实现表数据的转置?
- 做PL/SQL的开发,用什么工具比较好?
- 如何将已知的列数据横向排列
- 怎么编写触发器?
- 急!!Richard Stevens的UNIX网络编程(卷一)第二版的源代码那里有down.给全分
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||。);
好象没有其他办法了.