Oracle 存储过程和存储函数学习笔记

心已赠人 2022-06-16 06:52 241阅读 0赞

学习视频：http://www.imooc.com/video/7420

1.存储过程和存储函数：是指存储在数据库中供所有用户调用的子程序；

存储过程和存储函数的相同点：都是一个完成特定功能的程序；

存储过程和存储函数的不同点：是否能用 return 语句返回值，存储过程不能使用return语句，存储方法可以使用return语句；

**2.存储过程结构：**

create or replace procedure  存储过程名字(参数)
    as
    
    begin
    
       PLSQL 子程序体;
    
    end;

**3.创建一个无参的存储过程：**

CREATE OR REPLACE PROCEDURE SAYHELLOWORLD
    /*
    调用存储过程方法：
    1.exec sayHelloWorld();
    2.begin
           sayHelloWorld();
          sayHelloWorld();
    end;
    
    */
    AS  
    --说明部分
    begin
      dbms_output.put_line('Hello World');
    end;

调用过程（使用命令框调用）：

1). 先在PLSQL 中运行上面的存储过程，然后 快捷键 win+r ,输入 cmd  进入cmd命令窗口,输入 sqlplus 账户/密码@IP:端口/数据库实例名  ，连接数据库

![Center][]

2). 先输入   set  serveroutput on    打开屏幕的输出开关。

第一种方法调用存储过程。输入   exec sayHelloWorld();   单击回车，  调用存储过程，程序正常运行  窗口上即打印出 存储过程中设置的 输出语句"Hello World"，调用成功。

![Center 1][]

第二种方法调用存储过程，输入 begin  
       sayHelloWorld();  
      sayHelloWorld();  
end;

/      单击回车

![Center 2][]

**4.创建一个带一个参数的存储过程：**

create or replace procedure changeUserAge(eno in number) --in 表示输入参数  
      /*  
      调用存储过程方法：  
      1.exec sayHelloWorld(1);  
      2.begin  
             sayHelloWorld(1);  
            sayHelloWorld(2);  
      end;  
        
      */
     as
      -- 定义变量保存员工更改前的年龄  
      age0 user_test.user_age%type; --定义变量 age0 ，变量类型和 user_test表中的user_age 一致 
      age1 user_test.user_age%type;
    begin
      --得到员工更改前的年龄 
           select user_age into age0 from user_test where user_id = eno; --更改员工年龄
           update user_test set user_age = (age0 + 100) where user_id = eno; --得到更改后的年龄 
           select user_age into age1 from user_test where user_id = eno; --打印更改前后的年龄 
           dbms_output.put_line('更改前：' || age0 || ' 更改后：' || age1);
    end;
    --调用方法和前面一致，  
    exec sayHelloWorld(1); 括号内为传进去的参数

![Center 3][]

/\*  
调用存储过程方法：  
1.exec sayHelloWorld(1);  
2.begin  
       sayHelloWorld(1);  
      sayHelloWorld(2);  
end;  
 \*/

5.存储函数结构：

create or replace function 函数名(参数列表)
    return  函数值类型
    
    as
    
    begin
       PLSQL 子程序体
    end;

**6.创建一个存储函数：**

---创建一个存储函数：根据ID查询员工的年龄
    create or replace function selectAge(eno in number)
    return number  --返回number型的数据
    as
    --定义变量保存员工的年龄
    age user_test.user_age%type;
    
    begin
      --得到员工的年龄
      select user_age into age from user_test where user_id = eno;
      
      --返回员工年龄
      return age;
    end;

7.创建有多个返回值的存储过程

--创建有多个返回值的存储过程，根据ID查询员工信息
    create or replace procedure selectUserById(uid in number,
                                               uName out VARCHAR2,
                                               uAge out number,
                                               uSex out char)
                                               
    as
    
    
    begin
      select user_Name,user_AGE,USER_SEX into uName,uAge,uSex FROM USER_TEST WHERE USER_ID = uid;
    end;

**小知识：**  
1.在PLSQL 中查看已经创建的 存储过程和存储方法  
![Center 4][]

2. 在本机中登录数据库超级用户(只能登录当前环境中已安装的数据库)：  
在cmd 窗口中输入：sqlplus / as sysdba

给 用户赋予相应的权限（调试存储过程和存储方法时会用到）：  
 grant DEBUG CONNECT SESSION ,DEBUG ANY PROCEDURE TO TEST   --给TEST 用户赋予 DEBUG,CONNECT,SESSION,DEBUG ANY PROCEDURE 权限

![Center 5][]

3.如果只有一个返回值，用存储函数，如果没有返回值或者有多个返回值，就用存储过程

[Center]: /images/20220616/71099d1d8691411d8fa73c0b2b6d77a0.png
[Center 1]: /images/20220616/c3edab0437cf42a491d761e54f95ada6.png
[Center 2]: /images/20220616/e40d85809f244a749c06827260e399a5.png
[Center 3]: /images/20220616/3055b9019a5a4193b1d0e603d8015e08.png
[Center 4]: /images/20220616/887ebc5573394a2aa49d8a175fc3c0a0.png
[Center 5]: /images/20220616/f4075e93952745da949ad48bc8b173a8.png