oracle 存储过程 Stored Procedure

存储过程 Stored Procedure

   存储过程（Stored Procedure）是在大型数据库系统中；一组为了完成
   特定功能的SQL 语句集，存储在数据库中，经过第一次编译后
   再次调用不需要再次编译，用户通过指定存储过程的名字
   并给出参数（如果该存储过程带有参数来执行它。
   存储过程是数据库中的一个重要对象

Oracle存储过程基本语法:

   CREATE OR REPLACE PROCEDURE 存储过程名 

       AS       

       BEGIN 
    
       NULL
       
       END; 

说明:
行1: CREATE REPLACE PROCEDURE 是一个SQL语句通知Oracle数
据库去创建一个叫做某某某名字的存储过程, 如果存在就覆盖它;
行2: AS关键词表明后面将跟随一个PL/SQL体。
行3: BEGIN关键词表明PL/SQL体的开始。
行4: NULL PL/SQL语句表明什么事都不做，这句不能删去，因为
PL/SQL体中至少需要有一句,该处的NULL可以替换为要具体执行
的SQL语句
行5: END关键词表明PL/SQL体的结束

第一个存储过程,打印helloworld

CREATE OR REPLACE PROCEDURE sayhelloworld

AS

BEGIN

dbms_output.put_line('Hello World');

END;

以上存储过程创建完毕,在调用后会在控制台上打印出Hello World

注: dbms_output.put_line(‘Hello World’);

dbms_ out_put:数据库管理系统中的输出类库put_line();

类库中的一个内置输出函数,用于向控制台输出结果

在执行前需要使用如下命令打开控制台输出:

set serveroutput on;

打开输出后可以执行调用存储过程,有两种方式:

1)使用exec 关键字跟上要执行的存储过程名

  exec sayhelloworld();

2)使用begin end

begin

     sayhelloworld();

  end;

3)使用call关键字跟上要执行的存储过程名

call sayhelloworld();

第二个存储过程(带参)

–为指的员工涨薪100块钱,并且打印涨前和涨后的薪水?

CREATE OR REPLACE PROCEDURE raisesalary (eno in number)

AS

--定义一个变量来存储涨前的薪水

psal emp.sal%type;

BEGIN

SELECT sal into psal from emp where empno=eno;  --得到员工涨前的薪水

update emp set sal =sal+100 where empno=eno;  --给员工涨薪100

dbms_output.put_line('涨前:'||psal|| '涨后:'||(psal+100)); --打印

END;

--调用有参存储过程

BEGIN

  raisesalary(7369);

  raisesalary(7521);

  commit;

END;

如上存储过程是一个带参数的存储过程:

CREATE OR REPLACE PROCEDURE raisesalary (eno in number)

跟在存储过程名后的括号为传入的参数:

eno 是参数名, in 表示为入参,number为入参的类型

定义变量:

psal emp.sal%type;

psal:定义的变量名

emp.sal%type:前面定义的变量与emp表中sal字段的类型自动匹配(%type匹配类型)

into关键字:

SELECT  sal   into   psal   from emp where empno=eno;

into前面跟表中查询后要显示的字段,后面跟要赋值的变量名

上述sql语句的意思就是:

将员工表中符合where过滤条件的员工的工资查询出来赋值给psal变量.

–注意:一般不在存储过程和函数中使用commit和rollback,因为过程和函数是被调用的

–如果在过程中和函数中提交了事务,则无法保证过程和被调用时在一个事务中

–应该有谁调用谁提交

关于in,out,in out三种不同参数类型的解释

–in:调用时传入的参数,但在存储过程内部不能再次更改传入参数的值

–out:调用时无法接收传入的参数,作为传出的参数给调用前声明的变量

–in out:既能作为入参也能作为传出参数

案例:–in

create or replace procedure p_age(age in number)

as

begin

dbms_output.put_line('内:'||age);

    age:=50;

end;

调用:

declare

   age1 number:=30;

begin

   p_age(age1);

   dbms_output.put_line('外:'||age1);

end;

此时输出的结果为:内:30,外:30

由于p_age中声明的age是接收传入的参数,age接收到了传入的age1的值,第一次输30,内部虽然修改了age的值但不能回传,所以两次输出都是调用时声明的age1的值.

案例:–out

create or replace procedure p_age1(age out number)

as

begin

dbms_output.put_line('内:'||page);

    age:=50;

end;

调用:

declare

   age1 number:=30;

begin

   p_age1(age1);

   dbms_output.put_line('外:'||age1);

end;

此时输出的结果为:内: ,外:50

由于p_age1中声明的age是传出的参数,内部不能接收调用传入的age1的值,所以输出空,在过程内部改变age的值,类型为传出,则将修改后age的值传出赋给age1,所以输出50;

案例:–in out

create or replace procedure p_age2(age in out number)

as

begin

dbms_output.put_line('内:'||age);

    age:=50;

end;

调用:

declare

   age1 number:=30;

begin

   p_age2(age1);

   dbms_output.put_line('外:'||age1);

end;

此时输出的结果为:内:30,外:50

由于p_age2中声明的age是传入,传出的参数,过程内部既能接收到外部传入过来的age1的值,第一次输出30,然后重新给age进行赋值,然后将值传出给age1,第二次输出则是修改后的值.

第三个存储过程(无参:即在过程名后不加括号以及传入参数即可)

--查询10号部门的平均工资?

CREATE OR REPLACE PROCEDURE deptavgsal

AS

   dno emp.deptno%type;

   avgsal emp.sal%type;

BEGIN

   SELECT deptno,AVG(sal) INTO dno,avgsal FROM emp GROUP BY deptno  

                                                       HAVING deptno=10;

  dbms_output.put_line(dno||':'||avgsal);

END;

调用:

BEGIN

deptavgsal();

END;