CREATE OR REPLACE PROCEDURE find_emp2(emp_no NUMBER) AS BEGIN SELECT * FROM EMP WHERE empno = emp_no; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('雇员编号未找到'); END find_emp2;在T_SQL很容易实现的东西. 到PL/sql 为什么报错!
CREATE OR REPLACE PROCEDURE find_emp2(emp_no NUMBER) a number; AS BEGIN SELECT empno into a FROM EMP WHERE empno = emp_no and rownum = 1;---这里可能会有找到多条记录的情况,也会导致错误所以加个rownum取第一个 EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('雇员编号未找到'); END find_emp2;
CREATE OR REPLACE PROCEDURE find_emp2(emp_no NUMBER) a number; AS BEGIN SELECT empno into a FROM EMP WHERE empno = emp_no and rownum = 1;---这里可能会有找到多条记录的情况,也会导致错误所以加个rownum取第一个 EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('雇员编号未找到'); END find_emp2;
单独的select要用select into的形式 如果是想返回一个结果集,那么最好把结果集放到标签里 CURSOR C_CUR IS SELECT * FROM EMP WHERE empno = emp_no;
select结果集得放到游标中,否则只允许取单个数据
SELECT * FROM EMP WHERE empno = emp_no;ORACLE 儲存過程沒有這種寫法,如果你要全部記錄的話,就需要定義CURSOR了。
在过程里头不能用单纯的select语句
SELECT A INTO B FROM EMP WHERE ///
用游标或者在存储过程体中写insert into table_name(col1,col2,...) select col1,col2,...from target_table where ...才可以。
SELECT ... INTO ... FROM ... WHERE ...CREATE OR REPLACE PROCEDURE find_emp2(emp_no emp.empno%TYPE,r_emp out emp%ROWTYPE) AS BEGIN SELECT * INTO r_emp FROM EMP WHERE empno = emp_no; dbms_output.put_line(r_emp.ename); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('雇员编号未找到'); END find_emp2; DECLARE r_emp emp%ROWTYPE; BEGIN find_emp2('7369',r_emp); END;
select a into c from emp where ... 这样
或者一个ref cursor是有点复杂,习惯了就方便了
你这样直接SELECT不行~
改动量是大~~不过如果是简单的数据集,你们前台直接就可以通过SQL语句来产生呀
find_emp2(emp_no NUMBER)
a number;
AS
BEGIN
SELECT empno into a FROM EMP WHERE empno = emp_no and rownum = 1;---这里可能会有找到多条记录的情况,也会导致错误所以加个rownum取第一个
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');
END find_emp2;
CREATE OR REPLACE PROCEDURE
find_emp2(emp_no NUMBER)
a number;
AS
BEGIN
SELECT empno into a FROM EMP WHERE empno = emp_no and rownum = 1;---这里可能会有找到多条记录的情况,也会导致错误所以加个rownum取第一个
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');
END find_emp2;
如果是想返回一个结果集,那么最好把结果集放到标签里
CURSOR C_CUR IS SELECT * FROM EMP WHERE empno = emp_no;
AS
BEGIN
SELECT * INTO r_emp FROM EMP WHERE empno = emp_no;
dbms_output.put_line(r_emp.ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');
END find_emp2;
DECLARE
r_emp emp%ROWTYPE;
BEGIN
find_emp2('7369',r_emp);
END;