一个简单的 oracle 存储过程 出错

来源：百度知道 编辑：UC知道 时间：2024/07/04 19:24:21
存储过程如下
CREATE OR REPLACE PROCEDURE pro_test
IS
BEGIN
select * from emp;
END;
==================
SQL> @pro_test
6 /

Warning: Procedure created with compilation errors

执行出错~~~~~~~~
请高手帮我写一个完整的,小弟初学.....
谢谢了
小弟在线等,,,,,,,,,,,,,,,
1楼回答的不对....还是错的,,,还是报Warning: Procedure created with compilation errors

CREATE OR REPLACE PROCEDURE pro_test
IS
result varchar2(10);
BEGIN
select name into result from emp;
dbms_output.put_line(result);
END;

在存储过程当中使用简单的select语句是错误的，至少需要into来返回值

1楼的在select name into result from emp 之后加个where子句，返回的name必须是单行的，才不会出错

1. bulk collect into
CREATE OR REPLACE PROCEDURE pro_test
IS
TYPE pro_type IS TABLE OF emp%ROWTYPE;
pro pro_type;
BEGIN
SELECT * BULK COLLECT INTO pro FROM emp;
FOR i in 1..pro.count LOOP
dbms_output.put_line(pro(i).name);
END LOOP;
END;

2. 游标
CREATE OR REPLACE PROCEDURE pro_test
IS
CURSOR curpro IS SELECT * FROM EMP;
TYPE pro_type IS TABLE OF emp%ROWTYPE;
pro pro_type;
BEGIN
OPEN curpro;
FETCH curpro BULK COLLECT INTO pro;
FOR i in 1..pro.count LOOP
dbms_output.put_line(pro(i).name);
END LOOP;
END;

3. 包 vs调用
--包规则
CREATE OR REPLACE PACKAGE pp_t