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;
2带输入参数的子过程
功能描述:通过编号获取员工姓名。
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;
2。带输入,输出参数的子过程
功能描述:通过编号获取员工姓名。其中姓名为输出参数。
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;
3带输入,输出,输入输出参数的子过程
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;
4。插入一条数据:
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;
5。修改数据
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;
6删除数据
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;