oracle子过程,Oracle学习--子过程篇

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

empnameemployee1.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 OUTEmployee1.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

numINTEGER;

exists_empEXCEPTION;

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

numINTEGER;

no_exists_empEXCEPTION;

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

numINTEGER;

no_exists_empEXCEPTION;

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;