CREATE OR REPLACE PROCEDURE GET_EMPLOYE(P_EMPNO IN NUMBER DEFAULT 7788)
AS
EMPLOYE.ENAME VARCHAR2(10);
EMPLOYE.JOB VARCHAR2(10);
EMPLOYE.SAL NUMBER(7,2);
EMPLOYE.DEPTNO NUMBER(4);
BEGIN
SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
DBMS_OUTPUT.PUT_LINE('----- 雇员信息 -----');
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
END ;
警告: 创建的过程带有编译错误。PROCEDURE GET_EMPLOYE 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
3/11 PLS-00103: 出现符号 "."在需要下列之一时:
constant exception
<an identifier> <a double-quoted delimited-identifier> table
LONG_ double ref char time timestamp interval date binary
national character nchar
AS
EMPLOYE.ENAME VARCHAR2(10);
EMPLOYE.JOB VARCHAR2(10);
EMPLOYE.SAL NUMBER(7,2);
EMPLOYE.DEPTNO NUMBER(4);
BEGIN
SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
DBMS_OUTPUT.PUT_LINE('----- 雇员信息 -----');
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
END ;
警告: 创建的过程带有编译错误。PROCEDURE GET_EMPLOYE 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
3/11 PLS-00103: 出现符号 "."在需要下列之一时:
constant exception
<an identifier> <a double-quoted delimited-identifier> table
LONG_ double ref char time timestamp interval date binary
national character nchar
--like this
TYPE EMPLOYE IS RECORD(
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
SAL NUMBER(7,2),
DEPTNO NUMBER(4)
);
AS
EMPLOYE emp%rowtype;
BEGIN
SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
DBMS_OUTPUT.PUT_LINE('----- 雇员信息 -----');
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
END ;
CREATE OR REPLACE PROCEDURE GET_EMPLOYE(P_EMPNO IN NUMBER DEFAULT 7788)
AS
BEGIN
FOR EMPLOYE IN (SELECT * FROM EMP WHERE EMPNO=P_EMPNO) LOOP
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
DBMS_OUTPUT.PUT_LINE('----- 雇员信息 -----');
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
END ;
BEGIN
GET_EMPLOYE();
END;134 PL/SQL block, executed in 0.25 sec.
获取雇员SCOTT信息成功
----- 雇员信息 -----
雇员编号:7788
雇员名称:SCOTT
雇员职务:ANALYST
雇员工资:3000
部门编号:20
Total execution time 0.266 sec.
CREATE OR REPLACE PROCEDURE GET_EMPLOYE(P_EMPNO IN NUMBER DEFAULT 7788)
AS
EMPLOYE emp%rowtype;
BEGIN
SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
DBMS_OUTPUT.PUT_LINE('----- 雇员信息 -----');
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
END ;
----麻烦了点,使用了结构体
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL>
SQL> CREATE OR REPLACE PROCEDURE GET_EMPLOYE(P_EMPNO IN NUMBER DEFAULT 7788) AS
2 TYPE employe_cst IS RECORD(
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(10),
5 SAL NUMBER(7, 2),
6 DEPTNO NUMBER(4));
7 EMPLOYE employe_cst;
8 BEGIN
9 SELECT ename,job,sal,deptno INTO EMPLOYE FROM EMP WHERE EMPNO = P_EMPNO;
10 DBMS_OUTPUT.PUT_LINE('获取雇员' || EMPLOYE.ENAME || '信息成功');
11 DBMS_OUTPUT.PUT_LINE('----- 雇员信息 -----');
12 --DBMS_OUTPUT.PUT_LINE('雇员编号:' || EMPLOYE.EMPNO);
13 DBMS_OUTPUT.PUT_LINE('雇员名称:' || EMPLOYE.ENAME);
14 DBMS_OUTPUT.PUT_LINE('雇员职务:' || EMPLOYE.JOB);
15 DBMS_OUTPUT.PUT_LINE('雇员工资:' || EMPLOYE.SAL);
16 DBMS_OUTPUT.PUT_LINE('部门编号:' || EMPLOYE.DEPTNO);
17 EXCEPTION
18 WHEN OTHERS THEN
19 DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
20 END;
21 /Procedure createdSQL> begin
2 get_employe();
3 end;
4 /PL/SQL procedure successfully completedSQL> set serveroutput on;
SQL> begin
2 get_employe();
3 end;
4 /获取雇员SCOTT信息成功
----- 雇员信息 -----
雇员名称:SCOTT
雇员职务:ANALYST
雇员工资:3000
部门编号:20PL/SQL procedure successfully completedSQL>
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
DBMS_OUTPUT.PUT_LINE('----- 雇员信息 -----');
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
END LOOP;
请问这段程序的功能是有条件的把emp里面的值复制给employe,好奇为啥要用循环
EMPLOYE.JOB VARCHAR2(10);
EMPLOYE.SAL NUMBER(7,2);
EMPLOYE.DEPTNO NUMBER(4);
错了,又不是声明变量。
应该进行声明一个结构体变量!