oracle存储过程

--1.查询指定员工工号的员工姓名和工资

CREATEORREPLACEPROCEDURE getNameAndSal(

  v_empno INNUMBER,--in,out要放在数据类型和变量之间

  v_ename OUTVARCHAR2,

  v_sal OUTNUMBER

)

IS

--变量的声明

v_num NUMBER:=0;

BEGIN

  SELECT ename,sal

  INTO v_ename,v_sal

  FROM emp

  WHERE empno = v_empno;

 

EXCEPTION

  WHEN no_data_found THEN

    dbms_output.put_line('工号不存在');

    WHENOTHERSTHEN

      dbms_output.put_line('发生其他异常');

END;

 

--------------------------------------------------------------

 

--写一个匿名块调用

DECLARE

  v_ename emp.ename%TYPE;

  v_sal emp.sal%TYPE;

  v_empno emp.empno%TYPE:=&empno;

 

BEGIN

  --调用存储过程

  getNameAndSal(v_empno,v_ename,v_sal);

  dbms_output.put_line('员工姓名：' || v_ename || ',员工工资：' || v_sal);

END;

 

--------------------------------------------------------------------------

CREATEORREPLACEPROCEDURE getSum(

     v_sum INOUTNUMBER

)

IS

   CURSOR sal_cur IS

   SELECT sal FROM emp;

 

BEGIN 

 -- v_sum := 0;

   FOR sal_rec IN sal_cur LOOP

    

       v_sum := v_sum + sal_rec.sal;

   ENDLOOP;

   dbms_output.put_line('v_sum = ' || v_sum);

END;

 

--------------------------------------------------------------------------

--2.查询指定部门下所有员工的总工资和总人数

CREATEORREPLACEFUNCTION getSumSal(

  v_deptno INNUMBER,

  v_sum OUTNUMBER  --使用out参数保存总工资

)

RETURNNUMBER     --返回值返回总人数

IS

  --定义一个变量存放总人数

  v_count NUMBER:=0;

BEGIN 

  SELECTSUM(sal),COUNT(*)

  INTO v_sum,v_count

  FROM emp

  WHERE deptno = v_deptno;

 

  RETURN v_count;

END;

  --调用函数

  DECLARE

  v_deptno NUMBER:=&deptno;

  v_sum NUMBER;

  v_count NUMBER;

 

  BEGIN

    v_count:=getsumsal(v_deptno,v_sum);

    dbms_output.put_line('v_sum=' || v_sum ||'v_count=' || v_count);

END;

 

----------------------------------------------------------------------------

--查询指定部门的人数，返回为人数

CREATEORREPLACEFUNCTION getCount(

  v_deptno INNUMBER,

  v_sum OUTNUMBER

)

RETURNNUMBER

IS

  --定义一个变量存放总人数

  v_count NUMBER:=0;

BEGIN

  SELECTCOUNT(*)

  INTO v_count

  FROM emp

  WHERE deptno = v_deptno;

 

  RETURN v_count;

END getCount;

 

--不带out参数的函数可以直接在SQL语句中调用

SELECT getCount(20) FROM dual;

 

-----------------------------------------------------------------------

--包的规范

CREATEORREPLACEPACKAGE emppkg

IS

   --过程的声明   

   PROCEDURE getnameandsal(v_empno INNUMBER,v_ename OUTVARCHAR2,v_sal OUTNUMBER);

   PROCEDURE getsum(v_deptno INNUMBER,v_sum OUTNUMBER);

END emppkg;

 

--包的主体

CREATEORREPLACEPACKAGEBODY emppkg

IS

    --过程1：查询指定员工工号的姓名和工资

    PROCEDURE getnameandsal(

      v_empno INNUMBER,

      v_ename OUTVARCHAR2,

      v_sal OUTNUMBER

    )

    IS

    BEGIN

      SELECT ename,sal

      INTO v_ename,v_sal

      FROM emp

      WHERE empno = v_empno;

   EXCEPTION

     WHEN no_data_found THEN

       dbms_output.put_line('没有该工号');

     END getnameandsal;

    

     --过程2：查询指定部门的总工资

     PROCEDURE getsum(

       v_deptno INNUMBER,

       v_sum OUTNUMBER

     )

     IS

     BEGINSELECTSUM(sal)

       INTO v_sum

       FROM emp

       WHERE deptno = v_deptno;

       EXCEPTION

         WHEN no_data_found THEN

           dbms_output.put_line('部门号不存在');

     END getsum;

END emppkg;

 

-------------------------------------------------------------

--调用包中的过程

DECLARE

    v_sum NUMBER;

    BEGIN

      --使用报名.过程名调用

      emppkg.getsum(&deptno,v_sum)

      dbms_output.put_line('v_sum=' || v_sum);

    END;