oracle的过程(子程序)和函数

目录

🎈过程(子程序)的定义

        🏷子程序的各个部分

        🏷子程序的分类

        🏷子程序的优点

        🏷创建过程的语法

        🏷过程参数的三种模式

        🏷删除存储过程

🎈函数

        🏷创建函数的语法

        🏷定义函数的限制

        🏷巩固

---

🎈过程(子程序)的定义

        在oracle中的过程又名子程序
        命名的 PL/SQL 块，编译并存储在数据库中

        🏷子程序的各个部分

            声明部分
            可执行部分
            异常处理部分(可选)

        🏷子程序的分类

            过程 － 执行某些操作
            函数 － 执行操作并返回值

        🏷子程序的优点

            模块化
                将程序分解为逻辑模块
            可重用性
                可以被任意数目的程序调用
            可维护性
                简化维护操作
            安全性
                通过设置权限，使数据更安全

        🏷创建过程的语法

create or replace procedure 过程名(参数)
as或者is
	定义的变量
begin
	执行的操作
end;
/

        🏷过程参数的三种模式

        IN 
            用于接收调用程序的值
            默认的参数模式
        OUT
            用于向调用程序的返回值
        IN OUT
            用于接收调用程序的值，并向调用程序返回更新的值

        写一个存储过程，输出：
        *
        **
        ***
        ****

打开输出开关

set serverout on

IN类型

create or replace procedure proc1(i in number)
as
    a varchar2(50);
begin
    a :='';
    for j in 1..i loop
        a :=a || '*';
        dbms_output.put_line(a);
    end loop;
end;
/

        执行存储过程的方法
        SQL*PLUS：exec proc1(6)；

 

        OUT类型

create or replace procedure proc2(j out int)
as
begin
    j :=100;
    dbms_output.put_line(j);
end;
/

执行存储过程的方法

declare
    k number;
begin
    proc2(k);
end;
/

 

 存储过程的执行方式有两种，一种是sql*plus ，另一种是PL/SQL程序块

        INOUT类型

        让用户输入俩个数据来交换他们的值

create or replace procedure proc3(p1 in out number,p2 in out number)
as
    v_temp number;
begin
    v_temp :=p1;
    p1 := p2;
    p2 := v_temp;
end;
/

 

 

declare
    num1 number :=100;
    num2 number :=200;
begin
    proc3(num1,num2);
    dbms_output.put_line('num1='||num1);
    dbms_output.put_line('num2='||num2);
end;
/

 

 

将存储过程的权限分配给某个用户
grant execute on 存储过程的名字 to 用户名;

 通过zmt用户来访问这个存储过程

        🏷删除存储过程

        drop procedure 存储过程的名字;

🎈函数

    函数是可以返回值的命名的PL/SQL子程序

        🏷创建函数的语法

create or replace function 函数名称 return 要返回的数据类型
as或者is
    定义的变量;
begin
	要执行的操作;
    return 返回的数据;
end;
/

        🏷定义函数的限制

                    函数只能接收IN参数，而不能接收IN OUT 或 OUT 参数
                    形参不能是PL/SQL类型，只能是数据库类型
                    函数的返回类型也必须是数据库类型
                访问函数的两种方式
                    使用PL/SQL程序块
                    使用SQL语句

create or replace function fun_hello return varchar2
as
begin
    return '你好，朋友';
end;
/

通过sql*plus来调用  

select fun_hello from dual;

通过PL/SQL程序块

declare 
    ss varchar2(20);
begin
    ss :=fun_hello;
    dbms_output.put_line(ss);
end;
/

 

        🏷巩固

创建一个函数，可以接收用户输入的学号，得到该学生的名次，并输出这个名次。

create table 分数表(
student_no number(3),
name varchar2(10),
score number(3)
);

insert into 分数表 values(1,'小王',80);
insert into 分数表 values(2,'小李',85);
insert into 分数表 values(3,'小张',90);

定义存储过程

create or replace function func1(sno1 int) return int
as
    score1 number;
    mingci1 number;
begin
    select score into score1 from 分数表 where student_no=sno1;
    select count(*) into mingci1 from 分数表 where score > score;
    mingci1 :=mingci1 + 1;
    return mingci1;
end;
/

如果创建函数的过程中出现了错误可以通过   show error  命令来查看哪里出现了错误
通过ed进行修改或者直接修改

 查看学号为3的学生的名次

declare 
    a number;
begin
    a :=func1(3);
dbms_output.put_line('第'||a||'名');
end;
/