sql如下:CREATE or replace PROCEDURE DELETE_JOB_HISTORY
(
p_employee_id NUMBER
)
IS
BEGIN
select * from talent where id=p_employee_id;
DELETE FROM users
WHERE usersid = p_employee_id;
END DELETE_JOB_HISTORY;
执行的时候报错:
10:14:01 SQL> DELETE FROM users
10:14:01 2 WHERE usersid = p_employee_idWHERE usersid = p_employee_id
^
ERROR 位于第 2 行:
ORA-00904: "P_EMPLOYEE_ID": 标识符无效将查询和删除的sql换下顺序也报错,意思就是参数不能被重复利用不知如何解决望高人指点。。
(
p_employee_id NUMBER
)
IS
BEGIN
select * from talent where id=p_employee_id;
DELETE FROM users
WHERE usersid = p_employee_id;
END DELETE_JOB_HISTORY;
执行的时候报错:
10:14:01 SQL> DELETE FROM users
10:14:01 2 WHERE usersid = p_employee_idWHERE usersid = p_employee_id
^
ERROR 位于第 2 行:
ORA-00904: "P_EMPLOYEE_ID": 标识符无效将查询和删除的sql换下顺序也报错,意思就是参数不能被重复利用不知如何解决望高人指点。。
(
p_employee_id NUMBER
)
IS
a number(2);
BEGIN
select count(*) into a from talent where id=p_employee_id;
DELETE FROM users
WHERE usersid = p_employee_id;
END DELETE_JOB_HISTORY;
2 is
3 begin
4 SELECT * FROM EMP WHERE deptno = v_deptno;
5 end;
6 /警告: 创建的过程带有编译错误。已用时间: 00: 00: 00.11
scott@SZTYORA> show errors
PROCEDURE TEST_PROC2 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PLS-00428: 在此 SELECT 语句中缺少 INTO 子句
你写这一句的目的是什么?如果没用的话,可以不要的在你现在写的存储过程里,从功能上来说这个是多余的呀
不是出在重用参数的问题,而是在存储过程里面的select语句必须赋值给一个变量。select * from talent where id=p_employee_id;这个需要赋值给一个游标变量。
(
p_employee_id NUMBER
)
IS
cursor c1;
BEGIN
open c1 for select * from talent where id=p_employee_id;
DELETE FROM users
WHERE usersid = p_employee_id;
END DELETE_JOB_HISTORY;
select * from talent where id=p_employee_id;
oracle 中都是 select col1 into v_col1 from tb 必须把值赋给变量
你可以加DBMS_OUTPUT.PUTLINE(变量名称),输出打印
你的这段代码的确有问题
(
p_employee_id NUMBER
)
IS
BEGIN
select * from talent where id=p_employee_id;
EXECUTE IMMEDIATE 'DELETE FROM users WHERE usersid = '||p_employee_id;
END DELETE_JOB_HISTORY;存储过程中delete ,insert, update要使用动态SQL,不能直接执行.
别的先不说了 这里就要抱错了把
若是count(*) into 则无no_data_found异常
(
p_employee_id NUMBER
)
IS
BEGIN
delete from ACTIVITYINFO where id=p_employee_id;
delete from TALENT where id= p_employee_id;
delete from TRAININFO where id= p_employee_id;
delete
END DELETE_JOB_HISTORY;
异常:04:48:02 SQL> CREATE or replace PROCEDURE DELETE_JOB_HISTORY
(
p_employee_id NUMBER
)
IS
BEGIN
delete from ACTIVITYINFO where id=p_employee_id
已创建
耗时: 00:00:00.1904:48:02 SQL> delete from TALENT where id= p_employee_id delete from TALENT where id= p_employee_id
^
ERROR 位于第 1 行:
ORA-00904: "P_EMPLOYEE_ID": 标识符无效
1.去掉其中的select 语句
CREATE or replace PROCEDURE DELETE_JOB_HISTORY
(p_employee_id NUMBER)
IS
BEGIN
DELETE FROM emp WHERE empno = p_employee_id;
END DELETE_JOB_HISTORY;
因为select * from emp where empno =p_employee_id;是要返回一个有游标的。2.添加一个游标
--定义一个游标
create or replace package test_cs as
type cs is ref cursor;
end test_cs;--建立一个存储过程
CREATE or replace PROCEDURE DELETE_JOB_HISTORY
(p_employee_id NUMBER,test_cs out test_cs.cs)
ISBEGIN
open test_cs for select * from talent where id=p_employee_id;
DELETE FROM emp WHERE empno = p_employee_id;
END DELETE_JOB_HISTORY;
select * from talent where id=p_employee_id;--oracle存储过程不能直接用select如果必须要用必须用into赋值或者用游标取值
oracle 中存储过程 不能返回结果集,它的作用只限于操作上的处理如delete update insert等。如果要返回结果集,只能借助于游标;示例如下:
1、建立表tt
create table tt(a integer,b varchar2(20),c varchar2(20));
insert into tt values(1,'test11','test12');
insert into tt values(2,'test21','test22');
2、建立包spm
create or replace package spm
IS TYPE rctl IS REF CURSOR;
RETURN tt%ROWTYPE;
PROCEDURE proc1(rc1 IN OUT rctl);
END;
3、建立存储过程spm_proc1
create or replace procedure spm_proc1(rc1 IN OUT spm.rctl)
AS
BEGIN
OPEN rc1 FOR SELECT * FROM tt;
END;
p_employee_id NUMBER;
select t.object_name,t.object_type,t.status from user_objects t
WHERE t.object_name=upper('<过程或者包的名字>');
查询一下你的对象的状态,如果不是VALID状态,你的过程就有问题,不过我的确很震撼,你尽然运行了,。扯起来是语法问题了,呵呵
楼上可能没有看清楚,LZ是传参,一个参数可以不带符号,多个参数应当用逗号分开,最后一个不要逗号。你第一行代码是不允许在ORACLE的过程代码中使用的,不论是匿名块还是函数还是过程还是触发器还是包体都不允许,SELECT只能用在几个地方,SELECT要么跟INTO到一个或多个变量中,要么用游标IS或者自定义游标的FOR,要么用BULK COLLECT INTO到一个表类型对象中,要么INSERT INTO ...SELECT,要么在子查询中,要么MERGE表达式中,要么动态SQL但是执行完还是得在外面来个INTO啥的,等等,但是绝对不能直接一个SELECT啥也没有,我知道这个SQL SERVER是支持的,因为SQL SERVER的过程还可以直接返回结果集,还可以返回多个结果集(ORACLE是通过返回OUT参数类型的游标实现)。所以提示的错误,我估计在编译就有问题,只是你自己没有注意而已,呵呵。运行时提示的是下一行,这个已经算是ORACLE给你面子了,它基本很多提示不是人类可以看明白的。。检查语法,代码有问题,也不是说过程中直接执行SQL,动态SQL的执行方式也是推荐用USING将参数传递进去,而不是直接拼串,造成过多的latch征用问题。
从这一楼的描述看出,楼主是在SQLPLUS下执行的,由于代码之间有空行,所以报了错。有个选项set sqlblanklines on即可解决问题