create or replace procedure p_test2(id1 number(2,1))
is
t_ename emp.ename%type;
begin
if id1==23 then
select ename into t_ename from p_test2 where t_empno=id1
end if;
dbms_output.put_line("这个数据为:"+t_ename);
exception
when zero_divide then
dbms_outpput.put_line("错误!");
end;
=============
call p_test2(23);
=======
报ora-06575,调用的程序包或函数是无效的状态!请高手支招!谢谢!
is
t_ename emp.ename%type;
begin
if id1==23 then
select ename into t_ename from p_test2 where t_empno=id1
end if;
dbms_output.put_line("这个数据为:"+t_ename);
exception
when zero_divide then
dbms_outpput.put_line("错误!");
end;
=============
call p_test2(23);
=======
报ora-06575,调用的程序包或函数是无效的状态!请高手支招!谢谢!
后面加个分号;
你可以是用pl/sql dev或者toad进行编译调试。
CREATE OR REPLACE PROCEDURE p_test2 (id1 IN NUMBER)
IS
t_ename emp.ename%TYPE;
BEGIN
IF id1 = 23
THEN
SELECT ename
INTO t_ename
FROM p_test2
WHERE t_empno = id1;
END IF; DBMS_OUTPUT.put_line ("这个数据为:" + t_ename);
EXCEPTION
WHEN ZERO_DIVIDE
THEN
dbms_outpput.put_line ("错误!");
END;
============
pl/sql dev、toad是什么东西,是嵌在pl/sql里的工具吗?
麻烦提供一下具体操作路径!非常感谢!
id1 number(2,1)
) is t_ename emp.ename%type;
begin
if id1 = 23 then
SELECT ename INTO t_ename
FROM p_test2
WHERE t_empno = id1;
end if;
dbms_output.put_line("这个数据为:"+t_ename);
exception
when zero_divide then
dbms_outpput.put_line("错误!");
end;
你最好在pl/sql里调试一下
CREATE OR REPLACE PROCEDURE P_TEST2(ID1 IN NUMBER) IS
T_ENAME EMP.ENAME%TYPE;
BEGIN
IF ID1 = 23 THEN
SELECT ENAME INTO T_ENAME FROM scott.emp WHERE EMPNO = ID1;
END IF;
DBMS_OUTPUT.PUT_LINE('这个数据为:' + T_ENAME);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('错误!');
END;
/
SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 12/17/80 800.00 20
7499 ALLEN SALESMAN 7698 2/20/81 1600.00 300.00 30
7521 WARD SALESMAN 7698 2/22/81 1250.00 500.00 30
7566 JONES MANAGER 7839 4/2/81 2975.00 20
7654 MARTIN SALESMAN 7698 9/28/81 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 5/1/81 2850.00 30
7782 CLARK MANAGER 7839 6/9/81 2450.00 10
7788 SCOTT ANALYST 7566 12/9/82 3000.00 20
7839 KING PRESIDENT 11/17/81 5000.00 10
7844 TURNER SALESMAN 7698 9/8/81 1500.00 0.00 30
7876 ADAMS CLERK 7788 1/12/83 1100.00 20
7900 JAMES CLERK 7698 12/3/81 950.00 30
7902 FORD ANALYST 7566 12/3/81 3000.00 20
7934 MILLER CLERK 7782 1/23/82 1300.00 1014 rows selectedSQL>
SQL> create or replace procedure p_test2(id1 int)
2 is
3 t_ename emp.ename%type;
4 begin
5 begin
6 if (id1=7369) then
7 select ename into t_ename from emp where empno=id1;
8 end if;
9 dbms_output.put_line(':'||t_ename);
10 exception
11 when zero_divide then
12 dbms_output.put_line('!');
13 end;
14 end;
15 /Procedure createdSQL> set serveroutput on;
SQL> exec p_test2(7396);:PL/SQL procedure successfully completedSQL> exec p_test2(7369);:SMITHPL/SQL procedure successfully completedSQL>
create or replace procedure p_test2(id1 number(2,1))
is
t_ename emp.ename%type;
begin
if id1=23 then
select ename into t_ename from p_test2 where t_empno=id1; --加分号
end if;
dbms_output.put_line('这个数据为:'||t_ename); --字符连接用||
exception
when zero_divide then
dbms_output.put_line('错误!'); --字符串用单引号,多了个P
end;
存储过程传入参数不能带长度
(id1 number(2,1))
改成(id1 number)
create or replace procedure p_test2(id1 in number) is
t_ename p_test2.ename%type;
begin
if id1=23 then
select ename into t_ename from p_test2 where t_empno=id1;
end if;
dbms_output.put_line('这个数据为:' || t_ename);
exception
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('错误!' || SQLCODE || ' : ' || SQLERRM);
end;