Oracle学习--子过程篇

一，前期准备

 1. 创建员工表：

　　　Create  table Employee(EmpID char(2) primary key,

                                                 EmpName varchar2(8) not null,

                                                 Sex char(2) default '男' not null,

                                                 NativePlace varchar2(50)

                                             );

员工表数据：

INSERT INTO EMPLOYEE( EMPID, EMPNAME, SEX, NATIVEPLACE ) VALUES (

'01', '张华', '男', '山西省');

INSERT INTO EMPLOYEE ( EMPID, EMPNAME, SEX, NATIVEPLACE ) VALUES (

'02', '高卫栋', '男', '河北省');

INSERT INTO EMPLOYEE ( EMPID, EMPNAME, SEX, NATIVEPLACE ) VALUES (

'03', '沈富龙', '男', '河南省');

COMMIT;

２创建工资表：

CREATE TABLE SALARY

(

  EMPID        CHAR(2 BYTE)                     NOT NULL,

  BASICSALARY  NUMBER,

  OTHERSALARY  NUMBER

)

INSERT INTO SALARY ( EMPID, BASICSALARY, OTHERSALARY ) VALUES (

'01', 1000, 0);

INSERT INTO SALARY ( EMPID, BASICSALARY, OTHERSALARY ) VALUES (

'02', 2000, 50);

INSERT INTO SALARY ( EMPID, BASICSALARY, OTHERSALARY ) VALUES (

'03', 3000, 500);

 二。 子过程

１功能描述：将编号为02员工的姓名存储到变量中，再打印出来。

CREATE OR REPLACE PROCEDURE ShowEmpinfor IS

　　empName varchar2(20);--EmpName Employee.EmpName%Type;

　　BEGIN

   　　SELECT EmpName INTO EmpName From Employee1 where empid='02';

   　　DBMS_OUTPUT.PUT_LINE('员工姓名:'||EmpName);

　　END ShowEmpinfor;

２带输入参数的子过程

　功能描述：通过编号获取员工姓名。

　　CREATE OR REPLACE PROCEDURE showempinfor(ID Employee1.EmpID%Type)

　IS

   empname   employee1.empname%TYPE;

　BEGIN

   　SELECT empname

     INTO empname

     FROM employee1

    WHERE empid = ID;

 

   DBMS_OUTPUT.put_line ('员工姓名:' || empname);

　END showempinfor;

/

　执行：

　DECLARE

  ID CHAR(2);

BEGIN

  ID := '01';

  SYSTEM.SHOWEMPINFOR ( ID );

  COMMIT;

END;

２。带输入,输出参数的子过程

功能描述：通过编号获取员工姓名。其中姓名为输出参数。

　CREATE OR REPLACE PROCEDURE showempinfor(ID Employee1.EmpID%Type,Name OUT   Employee1.EmpName%Type)

IS

BEGIN

   SELECT empname

     INTO Name

     FROM employee1

    WHERE empid = ID;

END showempinfor;

/

 执行：

　DECLARE

  ID CHAR(2);

  NAME VARCHAR2(8);

BEGIN

  ID := '01';

  NAME := NULL;

 

  SYSTEM.SHOWEMPINFOR ( ID, NAME );

 

  DBMS_OUTPUT.Put_Line('NAME = ' || NAME);

 

  COMMIT;

END;

 

３带输入,输出,输入输出参数的子过程

  CREATE OR REPLACE PROCEDURE SYSTEM.showempinfor(ID Employee1.EmpID%Type,Name out Employee1.empname%type,sex in out employee1.sex%type)

IS

BEGIN

   SELECT empname

     INTO name

     FROM employee1

    WHERE empid = ID;

  SELECT sex

     INTO sex

     FROM employee1

    WHERE empid = ID;

END showempinfor;

/

执行：

　DECLARE

  ID CHAR(2);

  NAME VARCHAR2(8);

  SEX CHAR(2);

 

BEGIN

  ID := '01';

  NAME := NULL;

  SEX := NULL;

 

  SYSTEM.SHOWEMPINFOR ( ID, NAME, SEX );

 

  DBMS_OUTPUT.Put_Line('NAME = ' || NAME);

  DBMS_OUTPUT.Put_Line('SEX = ' || SEX);

 

  COMMIT;

end;

 

４。插入一条数据：

　CREATE OR REPLACE PROCEDURE SYSTEM.insertemployee (emprow employee1%ROWTYPE)

IS

   num         INTEGER;

   exists_emp   EXCEPTION;

BEGIN

   SELECT COUNT (*)

     INTO num

     FROM employee1

    WHERE empid = emprow.empid;

 

   IF num > 0

   THEN

      RAISE exists_emp;

   ELSE

      INSERT INTO employee1

           VALUES (emprow.empid, emprow.empname, emprow.sex,

                   emprow.nativeplace);

 

      COMMIT;

      DBMS_OUTPUT.put_line ('编号为'||EMPROW.EMPID||'的员工已添加成功！');

   END IF;

EXCEPTION

   WHEN exists_emp

   THEN

      DBMS_OUTPUT.put_line ('编号为' || emprow.empid || '的员工已经存在!');

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('出现未知错误!');

END insertemployee;

/

执行：

　DECLARE

  EMPROW Employee1%rowtype;

 

BEGIN

  EMPROW.EMPID:='12';

  EMPROW.EMPNAME:='付涛';

  EMPROW.SEX :='男';

  EMPROW.NATIVEPLACE :='山西省';

  SYSTEM.INSERTEMPLOYEE ( EMPROW );

  COMMIT;

END;

５。修改数据

CREATE OR REPLACE PROCEDURE SYSTEM.updateemployee (emprow employee1%ROWTYPE)

IS

   num             INTEGER;

   no_exists_emp   EXCEPTION;

BEGIN

   SELECT COUNT (*)

     INTO num

     FROM employee1

    WHERE empid = emprow.empid;

 

   IF num = 0

   THEN

      RAISE no_exists_emp;

   ELSE

      UPDATE employee1

         SET empname = emprow.empname,

             sex = emprow.sex,

             nativeplace = emprow.nativeplace

       WHERE empid = emprow.empid;

 

      COMMIT;

      DBMS_OUTPUT.put_line ('修改成功！');

   END IF;

EXCEPTION

   WHEN no_exists_emp

   THEN

      DBMS_OUTPUT.put_line ('编号为' || emprow.empid

                            || '的员工不存在,修改失败!'

                           );

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('出现未知错误!修改失败');

END updateemployee;

/

 

执行：

DECLARE

  EMPROW Employee1%rowtype;

 

BEGIN

  EMPROW.EMPID:='14';

  EMPROW.EMPNAME:='付涛';

  EMPROW.SEX :='男';

  EMPROW.NATIVEPLACE :='山西省';

  SYSTEM.UPDATEEMPLOYEE ( EMPROW );

  COMMIT;

END;

６删除数据

CREATE OR REPLACE PROCEDURE SYSTEM.deleteemployee (emprow employee1%ROWTYPE)

IS

   num             INTEGER;

   no_exists_emp   EXCEPTION;

BEGIN

   SELECT COUNT (*)

     INTO num

     FROM employee1

    WHERE empid = emprow.empid;

 

   IF num = 0

   THEN

      RAISE no_exists_emp;

   ELSE

      DELETE FROM employee1

            WHERE empid = emprow.empid;

 

      COMMIT;

      DBMS_OUTPUT.put_line ('删除成功！');

   END IF;

EXCEPTION

   WHEN no_exists_emp

   THEN

      DBMS_OUTPUT.put_line (   '编号为'

                            || emprow.empid

                            || '的员工不存在，删除失败！'

                           );

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('未知错误,删除失败！');

END deleteemployee;

/

执行删除:

DECLARE

  EMPROW Employee1%rowtype;

 

BEGIN

  EMPROW.EMPID:='14';

  EMPROW.EMPNAME:='付涛';

  EMPROW.SEX :='男';

  EMPROW.NATIVEPLACE :='山西省';

  SYSTEM.DELETEEMPLOYEE(EMPROW);

  COMMIT;

END;