Oracle（27）
1.基本结构&
CREATE OR REPLACE PROCEDURE 存储过程名字
&&& 参数1 IN NUMBER,
&&& 参数2 IN NUMBER
变量1 INTEGER :=0;
变量2 DATE;
END 存储过程名字
2.SELECT INTO STATEMENT
& 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
& 记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
& SELECT col1,col2 into 变量1,变量2 FROM
& EXCEPTION
& WHEN NO_DATA_FOUND THEN
& IF V_TEST=1 THEN
&&& BEGIN&
&&&&&& do something
4.while 循环
& WHILE V_TEST=1 LOOP
& END LOOP;
5.变量赋值
& V_TEST := 123;
6.用for in 使用cursor
& CURSOR cur IS SELECT * FROM
&FOR cur_result in cur LOOP
&&&V_SUM :=cur_result.列名1+cur_result.列名2
&END LOOP;
7.带参数的cursor
& CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
& OPEN C_USER(变量值);
&FETCH C_USER INTO V_NAME;
&EXIT FETCH C_USER%NOTFOUND;
&&& do something
& END LOOP;
& CLOSE C_USER;
8.用pl/sql developer debug
& 连接数据库后建立一个Test WINDOW
& 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
关于oracle存储过程的若干问题备忘
1.在oracle中，数据表别名不能加as，如：
select&a.appname&from&appinfo&a;--&正确
select&a.appname&from&appinfo&as&a;--&错误
&也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。
&&select&af.keynode&into&kn&from&APPFOUNDATION&af&where&af.appid=aid&and&af.foundationid=--&有into，正确编译
&&select&af.keynode&from&APPFOUNDATION&af&where&af.appid=aid&and&af.foundationid=--&没有into，编译报错，提示：Compilation&
&&Error:&PLS-00428:&an&INTO&clause&is&expected&in&this&SELECT&statement
3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出&no data found&异常。
&& 可以在该语法之前，先利用select count(*) from&查看数据库中是否存在该记录，如果存在，再利用select...into...
4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错
&select&keynode&into&kn&from&APPFOUNDATION&where&appid=aid&and&foundationid=--&正确运行
select&af.keynode&into&kn&from&APPFOUNDATION&af&where&af.appid=appid&and&af.foundationid=foundationid;--&运行阶段报错，提示
ORA-01422:exact&fetch&returns&more&than&requested&number&of&rows
5.在存储过程中，关于出现null的问题
假设有一个表A，定义如下：
create&table&A(
id&varchar2(50)&primary&key&not&null,
vcount&number(8)&not&null,
bid&varchar2(50)&not&null&--&外键&
如果在存储过程中，使用如下语句：
select&sum(vcount)&into&fcount&from&A&where&bid='xxxxxx';
如果A表中不存在bid=&xxxxxx&的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
if&fcount&is&null&then
&&&&fcount:=0;
这样就一切ok了。
6.Hibernate调用oracle存储过程
&&&&&&&&this.pnumberManager.getHibernateTemplate().execute(
&&&&&&&&&&&&&&&&new&HibernateCallback()&{
&&&&&&&&&&&&&&&&&&&&public&Object&doInHibernate(Session&session)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&throws&HibernateException,&SQLException&{
&&&&&&&&&&&&&&&&&&&&&&&&CallableStatement&cs&=&session
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&.connection()
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&.prepareCall(&{call&modifyapppnumber_remain(?)}&);
&&&&&&&&&&&&&&&&&&&&&&&&cs.setString(1,&foundationid);
&&&&&&&&&&&&&&&&&&&&&&&&cs.execute();
&&&&&&&&&&&&&&&&&&&&&&&&return&null;
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&});
java 执行存储过程
&& && proc = conn.prepareCall(&{ call HYQ.TESTA(?,?) }&);
&&&&&&proc.setString(1, &100&);
&&&&&&proc.setString(2, &TestOne&);
&&&&&&proc.execute();
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：303600次
积分：4750
积分：4750
排名：第5029名
原创：49篇
转载：792篇
评论：22条
(13)(10)(1)(25)(2)(1)(198)(280)(155)(92)(64)(12)(19)(1)oracle sqldeveloper - ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5 - Stack Overflow
Join the Stack Overflow Community
Stack Overflow is a community of 6.8 million programmers, just like you, helping each other.
J it only takes a minute:
variable b_employee_id employees.employee_id%
v_last_name employees.last_name%
v_emp_id employees.employee_id%
select employee_id into :b_employee_id from employees
where last_name='&v_last_name';
5,61336106179
The error message is self explanatory. Your select statement returned more than one row.
When you use the INTO clause the select cannot return more than one row.
From the :
By default, a SELECT INTO statement must return only one row.
Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and
the values of the variables in the INTO clause are undefined. Make
sure your WHERE clause is specific enough to only match one row
If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard
against this exception by selecting the result of an aggregate
function, such as COUNT(*) or AVG(), where practical. These functions
are guaranteed to return a single value, even if no rows match the
condition.
exact fetch returns more than requested number of rows
This means, there are more than one entry in the database for given last_name.
If you want, you can get max employee_id for given last_name as below.
select max(employee_id) into :b_employee_id from employees
where last_name='&v_last_name';
5,61336106179
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
rev .25389
Stack Overflow works best with JavaScript enabledQuick Search:
Oracle PL/SQL Code Library
Find Or Post Oracle Jobs
Oracle Discussion & Chat
&Oracle Database Error: ORA-01422& & & []
Select a Location
PSOUG Home Page
Oracle Code Library
mySQL Code Library
PHP Code Library
JavaScript Code Library
Oracle Terms & Definitions
Oracle Error Codes
PSOUG Community Blogs
Oracle Jobs Board
PSOUG Forum
Oracle User Group Directory
Free Oracle Magazines
Online Learning Center
PSOUG Presentations
Advanced Code Search
News and Events
Sponsors Page
Submit Code
Contact Us
Exact fetch returns more than requested number of rows
The number specified in exact fetch is less than the rows returned.
Rewrite the query or change number of rows requested.
By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row.
If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of an aggregate function, such as COUNT or AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition.
Add a comment&
Chars left:&1000
&(1000 max)
(No HTML, but newlines will be preserved)
This stumped me at first. It means that you're trying to execute a SELECT ... INTO ...
inside a PL/SQL block, but the SELECT is returning more than 1 row. A SELECT INTO always needs to return a single row. If there is more than 1 row coming back then this is the error that gets generated. If there are no rows returned you'll get the &ORA-01403: No data found& error message. Hope this helps someone!
54 users online &&
& 2009 psoug.org
PSOUG LOGIN
Username:&
Password:&
" class="stxt">