oracle数据库存储过程例子

存储过程：一段程序，用于改变数据库对象的状态，可以包含一个或多个行为，往往是将一个表的记录经过处理后放到另一个表。

1、基本语法：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] TYPE[,....])]
{IS | AS }
  [LOCAL declarations]
BEGIN
  executable statements;
[EXCEPTION
  exception_statements;
END procedure_name;

例子：

--/
CREATE OR REPLACE PROCEDURE proc_1 (num number, name varchar2)
IS
BEGIN
  insert into emp(empno, ename) values (num, name);
END;
/

调用：

--/
BEGIN
  proc_1(1002, '1002');
END;
/

2、in和out参数：
in参数表示存储过程的输入参数。out参数表示存储过程的输出参数，输出参数会改变参数的值，传递到调用方的一个变量当中。
例子：

--/
CREATE OR REPLACE PROCEDURE proc_2 (num in number, v_emp out emp%rowtype)
IS
BEGIN
  select * into v_emp from emp where empno = num;
END;
/

调用：

--/
DECLARE
  var emp%rowtype;
BEGIN
  proc_2(1000, var);
  dbms_output.put_line(var.ename);
END;
/

3、同时带in和out参数：
IN OUT模式是IN和OUT方式的组合，又称为输入/输出参数。
当过程被调用的时候，实际参数的值会被传递给过程，形式参数可以被读出和写入。当过程调用结束，控制返回到调用环境时，形式参数的内容被赋予实际的值。
例子：

--/
CREATE OR REPLACE PROCEDURE proc_3(x IN NUMBER, y IN OUT NUMBER)
IS
  v_tmp NUMBER;
BEGIN
  v_tmp := x + y;
  y := v_tmp;
END;
/

调用：

--/
DECLARE
  num1 number :=1;
  num2 number :=2;
BEGIN
  proc_3(num1, num2);
  dbms_output.put_line(num2);
END;
/

4、
存储过程中具体的处理依赖于pl sql的基础语法来实现。

存储过程示例：为指定的职工在原工资的基础上长10%的工资
 
/*
为指定的职工在原工资的基础上长10%的工资，并打印工资前和工资后的工资
*/
SQL> create or replace procedure raiseSalary(empid in number)
    as
    pSal emp.sal%type;--保存员工当前 工资
    begin
--查询该员工的工资
    select sal into pSal from emp where empno=empid;
--给该员工涨工资
    update emp set sal = sal*1.1 where empno=empid;
--打印涨工资前后的工资
    dbms_output.put_line('员工号:' || empid || '涨工资前
   ' || psal || '涨工资后' || psal*1.1);
    end;
 1  /
 
Procedure created
--存储过程调用
--方法一
SQL> set serveroutput on
SQL> exec raisesalary(7369);
 
员工号:7369涨工资前
800涨工资后880
 
方法二
    set serveroutput on
begin
 raisesalary(7369);
end;
/
 
PL/SQL procedure successfully completed
 
 
       存储函数
      函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。
 
     建立存储函数的语法：
 
CREATE [OR REPLACE] FUNCTION函数名(参数列表)
 RETURN 函数值类型
AS
PLSQL子程序体；
 
 
      示例：查询某职工的年收入。
SQL> /**/
    /*
    查询某职工的总收入
    */
    create or replace function queryEmpSalary(empid in number)
    return number
   as
    pSal number; --定义变量保存员工的工资
    pComm number; --定义变量保存员工的奖金
   begin
   select sal,comm into psal,pcomm from emp where empno = empid;
   return psal*12+nvl(pcomm,0);
   end;
   /
 
Function created
 
l        函数的调用
 
SQL> declare
    v_sal number;
    begin
    v_sal:=queryEmpSalary(7934);
    dbms_output.put_line('salary is:'|| v_sal);
    end;
    /
 
salary is:15600
 
PL/SQL procedure successfully completed
 
SQL> begin
    dbms_output.put_line('salary is:'|| queryEmpSalary(7934));
    end;
    /
 
salary is:15600
 
PL/SQL procedure successfully completed