oracle存储过程练习题

1.创建一个过程，能向DEPT表中添加一个新记录。（IN参数）
创建过程
CREATE OR REPLACE PROCEDURE SP_INSERT_DEPT( P_DEPTNO IN NUMBER,
P_DNAME IN VARCHAR2,
P_LOC IN VARCHAR2 )
IS
BEGIN
INSERT INTO DEPT
VALUES(P_DEPTNO,P_DNAME,P_LOC);
COMMIT;
END;
/
调用
BEGIN
SP_INSERT_DEPT(79,'技术部','武汉');
END;
/

CREATE OR REPLACE PROCEDURE SP_DEPT_HZH(P_DEPT_DEPTNO IN DEPT.DEPTNO%TYPE,
P_DEPT_DNAME IN DEPT.DNAME%TYPE,
P_DEPT_LOC IN DEPT.LOC%TYPE)
IS
BEGIN

INSERT INTO DEPT_H1 VALUES(P_DEPT_DEPTNO , P_DEPT_DNAME , P_DEPT_LOC ) ;

END;

DECLARE
V_DEPT_DEPTNO DEPT.DEPTNO%TYPE:=&部门编号;
V_DEPT_DNAME DEPT.DNAME%TYPE:=&部门名称;
V_DEPT_LOC DEPT.LOC%TYPE:=&部门所在地;
BEGIN
SP_DEPT_HZH(V_DEPT_DEPTNO,V_DEPT_DNAME,V_DEPT_LOC);
END;

2.从EMP表中查询给定职工的职工姓名和工资。
（提示：使用&来输入员工编号
要求：利用OUT模式的参数将值传给调用者）
创建过程
CREATE OR REPLACE PROCEDURE SP_SELECT_EMP( P_EMPNO IN EMP.EMPNO%TYPE,
P_ENAME OUT EMP.ENAME%TYPE,
P_SALARY OUT EMP.SAL%TYPE)
IS
BEGIN
SELECT ENAME,SAL INTO P_ENAME,P_SALARY FROM EMP WHERE EMPNO = P_EMPNO;
DBMS_OUTPUT.PUT_LINE('员工姓名：'||P_ENAME||' 员工工资：'||P_SALARY);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该部门不存在！');
END;
/
调用
DECLARE
V_NAME EMP.ENAME%TYPE;
V_SALARY EMP.SAL%TYPE;
BEGIN
SP_SELECT_EMP(&员工编号,V_NAME,V_SALARY);
END;
/

3.创建一个过程，在执行调用过程时，可随机输入EMP表中某个雇员的姓名，
根据雇员的姓名，返回该雇员的薪水值，并输出（OUT参数）。
创建过程
CREATE OR REPLACE PROCEDURE SP_SELECT_EMP2( P_ENAME IN EMP.ENAME%TYPE,
P_SALARY OUT EMP.SAL%TYPE) IS
BEGIN
SELECT SAL INTO P_SALARY FROM EMP WHERE ENAME = P_ENAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_SALARY:=0;
END;
/
调用
DECLARE
V_SAL NUMBER(5);
BEGIN
SP_SELECT_EMP2('&员工姓名',V_SAL);
DBMS_OUTPUT.PUT_LINE(V_SAL);
END;
/

4.编写过程，实现交换两个变量的值的功能。并输出交换前和交换后的两个值（IN OUT参数）
创建过程
CREATE OR REPLACE PROCEDURE SP_SWAP(P_NUM1 IN OUT NUMBER,
P_NUM2 IN OUT NUMBER)
IS
V_Z NUMBER(5);
BEGIN
V_Z:=P_NUM1;
P_NUM1:=P_NUM2;
P_NUM2:=V_Z;
END;
/
调用
DECLARE
V_X NUMBER:=10;
V_Y NUMBER:=20;
BEGIN
DBMS_OUTPUT.PUT_LINE('交换前V_X和V_Y的值是：'||V_X||' '||V_Y);
SP_SWAP(V_X,V_Y);
DBMS_OUTPUT.PUT_LINE('交换后V_X和V_Y的值是：'||V_X||' '||V_Y);
END;
/

5.创建存储过程，根据员工编号删除EMP表中的相关记录
（提示：由调用语句提供的员工编号来删除记录，要求员工编号可随机输入）
创建过程
CREATE OR REPLACE PROCEDURE SP_DELETE_EMP(P_EMPNO EMP.EMPNO%TYPE) IS
BEGIN
DELETE FROM EMP WHERE EMPNO = P_EMPNO;
END;
/
调用
EXECUTE SP_DELETE_EMP('&员工编号');
/

6. 创建存储过程：输入部门编号，
输出EMP表中该部门所有职工的员工编号、姓名、工作岗位。
创建过程
CREATE OR REPLACE PROCEDURE SELCET_CUREMP(P_DEPTN IN EMP.DEPTNO%TYPE)
IS
CURSOR C1 IS
SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE DEPTNO = P_DEPTN;
BEGIN
FOR REC IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(REC.EMPNO||' '||REC.EMPNO||' '||REC.JOB);
END LOOP;
END;
/
执行存储过程
EXECUTE SELCET_CUREMP(10);
/

7.编写一个过程，指定一个员工编号与一个工资增加的百分比，
使EMP表中将该员工的工资（SAL）增加输入的百分比。
创建过程
CREATE OR REPLACE PROCEDURE SP_UP_SAL( P_EMPNO IN NUMBER, P_PARSENT IN FLOAT)
IS
BEGIN
UPDATE EMP SET SAL = SAL*(1+P_PARSENT) WHERE EMPNO = P_EMPNO;
END;
/
调用
BEGIN
SP_UP_SAL(1234,0.5);
END;
/

8.创建函数,从EMP表中查询指定员工编号的职工的工资
CREATE OR REPLACE FUNCTION F_SELECT_SAL(P_EMPNO EMP.EMPNO%TYPE)
RETURN EMP.SAL%TYPE
IS
V_SALARY EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO V_SALARY FROM EMP WHERE EMPNO = P_EMPNO;
RETURN V_SALARY;
END;
/
调用函数有以下四种方式：
方式一：使用变量接收返回值
VAR V_SALARY NUMBER;
EXEC :V_SALARY:=F_SELECT_SAL(7369);
PRINT V_SALARY;
方式二：在SQL语句中直接调用函数
SELECT F_SELECT_SAL(7369) FROM DUAL;
方式三：使用DBMS_OUTPUT调用函数
EXEC DBMS_OUTPUT.PUT_LINE('工资是:'|| F_SELECT_SAL(7369));
方式四：在匿名PL/SQL块中调用函数(推荐使用该种方式-方便好记)
BEGIN
DBMS_OUTPUT.PUT_LINE('工资是:'|| F_SELECT_SAL(7369));
END;
/

9.创建函数，返回EMP表中指定职工的工资和姓名
（提示：返回值是两个，可用RETURN返回一个，另一个用OUT参数带回）
CREATE OR REPLACE FUNCTION F_SELECT_SAL_NAME(P_EMPNO IN EMP.EMPNO%TYPE,
P_NAME OUT EMP.ENAME%TYPE)
RETURN EMP.SAL%TYPE
IS
V_SALARY EMP.SAL%TYPE;
BEGIN
SELECT SAL, ENAME INTO V_SALARY,P_NAME FROM EMP WHERE EMPNO = P_EMPNO;
RETURN V_SALARY;
END;
/
调用
DECLARE
V_NAME EMP.ENAME%TYPE;
V_SAL EMP.SAL%TYPE;
BEGIN
V_SAL:=F_SELECT_SAL_NAME(7369,V_NAME);
DBMS_OUTPUT.PUT_LINE(V_NAME||'的工资为'||V_SAL);
END;
/

10. 创建函数，根据给定的部门编号(提示: 利用&)计算该部门所有职工的平均工资。
CREATE OR REPLACE FUNCTION F_AVG_SAL(P_DEPTNO EMP.DEPTNO%TYPE)
RETURN NUMBER
IS
V_AVGSAL NUMBER(7,2);
BEGIN
SELECT AVG(SAL) INTO V_AVGSAL FROM EMP WHERE DEPTNO = P_DEPTNO;
RETURN V_AVGSAL;
END;
/
调用
BEGIN
DBMS_OUTPUT.PUT_LINE(F_AVG_SAL(&DEPTNO));
END;
/

11.创建函数，将EMP表中工资低于平均工资的职工工资加上200,并返回修改了工资的总人数.
CREATE OR REPLACE FUNCTION F_MODIFY
RETURN NUMBER
IS
BEGIN
UPDATE EMP SET SAL=SAL+200 WHERE SAL<(SELECT AVG(SAL) FROM EMP);
RETURN SQL%ROWCOUNT;
/* sql%rowcount用于记录修改的条数，必须放在一个更新或者删除等修改类语句后面执行，
select语句用于查询的话无法使用，
当你执行多条修改语句时，按照sql%rowcount 之前执行的最后一条语句修改数为准。*/
END;
/
调用
BEGIN
DBMS_OUTPUT.PUT_LINE(F_MODIFY);
END;
/

CREATE OR REPLACE FUNCTION F_ALT_SAL_LJS
RETURN NUMBER
IS
CURSOR C_ES IS(SELECT EMPNO,SAL FROM EMP);
V_AVG_SAL NUMBER;
V_SUM_NUM NUMBER:=0;
BEGIN
SELECT AVG(SAL) INTO V_AVG_SAL FROM EMP;
FOR X IN C_ES LOOP
IF X.SAL<V_AVG_SAL THEN
UPDATE EMP SET SAL=SAL+200 WHERE EMPNO=X.EMPNO;
V_SUM_NUM:=V_SUM_NUM+1;
END IF;
END LOOP;
RETURN V_SUM_NUM;
END;
DECLARE
V_SUM_NUM NUMBER;
BEGIN
V_SUM_NUM:=F_ALT_SAL_LJS;
DBMS_OUTPUT.PUT_LINE(V_SUM_NUM);
END;

12.创建一个函数，仅有一个形参，它接收调用函数中传递过来的实参–部门号，
函数的返回值为该部门的一整条记录信息（注意：此处能够接收一整条记录的变量该怎么定义？）。
要求在调用函数中输出该部门的部门名称与位置。
CREATE OR REPLACE FUNCTION F_FIND_DEPT(P_DEPTNO NUMBER)
RETURN DEPT%ROWTYPE
IS
V_DEPT DEPT%ROWTYPE;
BEGIN
SELECT * INTO V_DEPT FROM DEPT WHERE DEPTNO = P_DEPTNO;
RETURN V_DEPT;
END;
/
调用
DECLARE
V_DEPT DEPT%ROWTYPE;
BEGIN
V_DEPT:=F_FIND_DEPT(30);
DBMS_OUTPUT.PUT_LINE(V_DEPT.DNAME||'---'||V_DEPT.LOC);
END;
/

18.基于表EMP和DEPT 构造存储过程
–参数：ENAME IN VARCHAR2
SALARY IN NUMBER
V_JOB OUT VARCHAR2
V_DNAME OUT VARCHAR2
–先查找指定员工，如果查出多条记录，提示并异常退出；如果没有该名员工，提示并异常退出。
–如果非上述情况，先判断该名员工的职位，如果职位不是’MANAGER’,且要修改的薪水大于8000，拒绝修改并提示：“普通员工不能赚这么多薪水”。
–否则修改该名员工的薪水，
–输出参数：该职员的职位，和所在部门的名字，并打印输出。

CREATE OR REPLACE PROCEDURE CHANGE_SALARY(V_ENAME IN EMP.ENAME%TYPE,
V_SALARY IN EMP.SAL%TYPE,
V_JOB OUT VARCHAR2,
V_DNAME OUT VARCHAR2)
IS
V_ECOUNT INT DEFAULT 0;
R_EMP EMP%ROWTYPE;
TOO_MANY_SALARY EXCEPTION;
BEGIN
SELECT COUNT(1) INTO V_ECOUNT FROM EMP WHERE EMP.ENAME=V_ENAME;
IF V_ECOUNT=0 THEN
RAISE NO_DATA_FOUND;
ELSIF V_ECOUNT>1 THEN
RAISE TOO_MANY_ROWS;
ELSE
BEGIN
SELECT * INTO R_EMP FROM EMP WHERE EMP.ENAME=V_ENAME;
IF R_EMP.JOB!='MANAGER' AND V_SALARY>=8000 THEN
RAISE TOO_MANY_SALARY;
ELSE
BEGIN
UPDATE EMP SET SAL=V_SALARY WHERE EMP.ENAME=V_ENAME;
COMMIT;
V_JOB:=R_EMP.JOB;
SELECT DNAME INTO V_DNAME FROM DEPT WHERE DEPTNO=R_EMP.DEPTNO;
DBMS_OUTPUT.PUT_LINE('职位：'||V_JOB||',部门：'||V_DNAME);
END;
END IF;
END;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘未查询到该员工，请检查是否输入错误!’);
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(‘查询到’||V_ECOUNT||’个员工，请检查是否输入错误!’);
WHEN TOO_MANY_SALARY THEN DBMS_OUTPUT.PUT_LINE(‘普通员工不能赚这么多薪水!’);
WHEN OTHERS THEN NULL;
END CHANGE_SALARY;

转载其他人的过程：
CREATE OR REPLACE PROCEDURE CHANGE_SALARY(
V_ENAME IN EMP.ENAME%TYPE,
V_SALARY IN EMP.SAL%TYPE,
V_JOB OUT EMP.JOB%TYPE,
V_DNAME OUT DEPT.DNAME%TYPE
)
IS
V_EMPNO EMP.EMPNO%TYPE;
TOO_MANY_SALARY EXCEPTION;
BEGIN
SELECT E.EMPNO, E.JOB, D.DNAME INTO V_EMPNO, V_JOB, V_DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND UPPER(ENAME) = UPPER(V_ENAME);

IF V_JOB <> ‘MANAGER’ AND V_SALARY > 8000 THEN
RAISE TOO_MANY_SALARY;
END IF;

UPDATE EMP SET SAL = V_SALARY WHERE EMPNO = V_EMPNO;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘员工姓名为’ || V_ENAME || ‘的员工的工资已经修改为’ || V_SALARY);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘您输入的人员姓名’ || ‘对应信息不存在’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘人员姓名为’||V_ENAME ||’不止一个，无法更新！’);
WHEN TOO_MANY_SALARY THEN
DBMS_OUTPUT.PUT_LINE(‘你输入的工资过高，无法录入’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);

END;
测试方式：
DECLARE
V_JOB EMP.JOB%TYPE;
V_DNAME DEPT.DNAME%TYPE;
BEGIN
CHANGE_SALARY(‘BLAKE’,8500,V_JOB ,V_DNAME );
IF V_JOB IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(‘职位：’||V_JOB||’,部门：’||V_DNAME);
END IF;
END;