PLSQL_Study存储过程/函数

FUNCTION(函数)

• Oracle 编写一个函数, 传not_num1, not_num2 两个参数, 判断 not_num1 不为空时, 查询T_PM_MASTER_VOLUME 表, 返回字段A, 如果not_num2 不为空, 查询T_PM_CHILD_VOLUMENO 表返回字段B

CREATE OR REPLACE FUNCTION get_volume_data(not_num1 IN VARCHAR2, not_num2 IN VARCHAR2)
RETURN VARCHAR2
IS
    v_result VARCHAR2(100);
BEGIN
    IF not_num1 IS NOT NULL THEN
        -- 查询 T_PM_MASTER_VOLUME 表并返回字段 A
        SELECT A INTO v_result
        FROM T_PM_MASTER_VOLUME
        WHERE condition; -- 添加适当的查询条件

        RETURN v_result;
    ELSIF not_num2 IS NOT NULL THEN
        -- 查询 T_PM_CHILD_VOLUMENO 表并返回字段 B
        SELECT B INTO v_result
        FROM T_PM_CHILD_VOLUMENO
        WHERE condition; -- 添加适当的查询条件

        RETURN v_result;
    ELSE
        -- 处理其他情况，根据需要返回适当的值
        RETURN 'No data available.';
    END IF;
END;

REPLACE

• CREATE OR REPLACE FUNCTION

• 是用于创建或替换已存在的数据库函数的语句

• REPLACE 并不是指字符串操作中的 REPLACE 函数, 而是指替换已存在的函数定义

• 如果该函数已经存在于数据库中，则会被新的函数定义所替换。如果函数不存在，则会创建一个新的函数。

• 创建存储过程也同样适用

管道函数(pipelined function)

• 管道函数允许使用类似迭代器的方式从函数中返回多个结果集，而无需将所有结果存储在内存中
• 这对于处理大量数据或需要逐步处理结果的情况非常有用
• 用于在SQL查询中有效地处理和返回多行多列结果
• 使用PIPE ROW语句逐行输出结果，并使用PIPELINED关键字声明函数返回结果集; 这个函数可以在SQL查询中使用，并像表一样进行查询操作，返回多行结果。
• 函数允许你在函数执行期间逐行返回结果，而不是一次性返回所有结果
• sql定义

-- 定义管道函数

CREATE OR REPLACE FUNCTION function_name RETURN return_type PIPELINED IS
  -- 函数体
BEGIN
  -- 在此处生成结果集并使用PIPE ROW语句将每行添加到管道
  PIPE ROW(result);
  -- 可以有多个PIPE ROW语句
  -- ...
END;


-- 调用管道函数
SELECT * FROM TABLE(function_name()) where 1=1;

实例

-- 创建一个对象类型作为返回类型：
CREATE TYPE t_ResultRow AS OBJECT (
  id           NUMBER,
  name         VARCHAR2(50),
  description  VARCHAR2(100)
);


-- 创建一个集合类型以存储多个结果行
CREATE TYPE t_ResultTable IS TABLE OF t_ResultRow;



--创建一个管道函数来生成多行数据
CREATE OR REPLACE FUNCTION getTst RETURN t_ResultTable PIPELINED IS
BEGIN
  -- 在此处逐行生成结果并使用PIPE ROW语句将每行添加到管道
  PIPE ROW(t_ResultRow(1, 'Apple', 'A juicy fruit'));
  PIPE ROW(t_ResultRow(2, 'Orange', 'Rich in vitamin C'));
  PIPE ROW(t_ResultRow(3, 'Banana', 'A great source of potassium'));

  RETURN;
END;


SELECT * FROM TABLE(getTst());

PROCEDURE(存储过程)

意义

之前我们编写的PLSQL程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复调用.

可以理解之前的代码全都编写在了main方法中,是匿名程序. JAVA可以通过封装对象和方法来解决复用问题

PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程

存储过程作用：

1， 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源), 需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.

2， ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

根据参数的类型，我们将其分为3类讲解：

l 不带参数的

l 带输入参数的

l 带输入输出参数(返回值)的。

测试数据

CREATE TABLE "MES"."T_SYS_PROCEDURE" 
   (	
    "ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(200), 
	"AGE" VARCHAR2(50), 
	"TYPE" NUMBER, 
	"CREATE_USER" VARCHAR2(50), 
	"CREATE_DATE" DATE DEFAULT sysdate, 
	"UPDATE_USER" VARCHAR2(50), 
	"UPDATE_DATE" DATE, 
	"IS_DELETE" NUMBER DEFAULT 0, 
	"EXT1" VARCHAR2(50), 
	"EXT2" VARCHAR2(50), 
	"EXT3" VARCHAR2(50), 
	"EXT4" VARCHAR2(50), 
	"EXT5" VARCHAR2(50), 
	 CONSTRAINT "PK_T_SYS_PROCEDURE" PRIMARY KEY ("ID")
   ) 

COMMENT ON TABLE "MES"."T_SYS_PROCEDURE"  IS '存储过程测试';

--T_SYS_PROCEDURE 存储过程测试
--为id创建序列
CREATE SEQUENCE SEQ_PROCEDURE_ID
             INCREMENT BY 1 --每次增加1，可以写非0的任何整数
             START WITH 1000 -- 从1开始（起始值）
             NOMAXVALUE   -- 设置最大值：设置为 NOMAXVALUE 表示无最大值;设置为  MAXVALUE 999  表示最大值 999
             MINVALUE 1000   -- 设置最小值
             NOCYCLE      -- 不循环一直增加
             CACHE 20; -- 设置20个缓存,不使用缓存则写 NOCACHE
             
--为id创建触发器             
CREATE OR REPLACE TRIGGER tri_PROCEDURE_ins   --每个触发器名不可一样
   BEFORE INSERT ON T_SYS_PROCEDURE FOR EACH ROW  WHEN (NEW.ID IS NULL) --BEFORE INSERT ON 后为自己的表名; WHEN (NEW.ID IS NULL) 触发条件 ID为空
BEGIN 
  SELECT SEQ_PROCEDURE_ID.nextval INTO:NEW.ID FROM DUAL;  -- 创建的自增序列名;ID 为主键名
END;    

游标

游标声明：

CURSOR  游标名[(参数列表)]    IS 查询语句;

游标的打开：

OPEN 游标名;

游标的取值：

FETCH 游标名 INTO 变量列表;

游标的关闭：

CLOSE 游标名;

• 用于临时存储一个查询返回的多行数据（结果集,类似于Java的Jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。

• 游标的使用方式：声明—>打开—>读取—>关闭

游标的属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ISOPEN	布尔型	游标已经打开时值为真，否则为假

其中 %NOTFOUND是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环

代码说明

在Oracle数据库中，SQLCODE和SQLERRM是两个常用的系统变量，用于获取最近执行的SQL语句的错误代码和错误消息。

• SQLCODE变量返回一个整数值，表示最后一条SQL语句的执行结果或错误代码。如果SQL语句执行成功，则SQLCODE为0；如果发生错误，则SQLCODE为负数或正数。通常，负数表示运行时错误，正数表示警告或其他提示信息。

• SQLERRM变量返回一个字符串，给出了与最后一条SQL语句相关的错误信息。它提供了对错误的文本描述。例如，“ORA-00942: 表或视图不存在”。

• 两个系统变量通常被用于在异常处理中获取并处理SQL语句执行过程中的错误信息。

• SQLCODE和SQLERRM的值只会反映最近一条SQL语句的执行结果和错误信息。如果在此之后执行了其他SQL语句，则这些变量的值将被更新为最新的执行结果和错误信息。

• 创建存储过程,入参,返回信息,定义局部表量,代码操作,异常抛出;

create or replace procedure proc_bf_callback_test(v_name       varchar2,
                                                  v_type       varchar2,  -- 定义传参
                                                  ReturnMsg    out varchar2) as  -- 定义返回参数
                                                  
  /**
  变量声明: 1.普通数据类型（char,varchar2, date, number, boolean, long） 2.特殊变量类型（引用型变量、记录型变量）
  
  声明方式: 
  变量名  变量类型（变量长度）  例如： v_name  varchar2(20);
  
  
  引用型变量:  变量的类型和长度取决于表中字段的类型和长度
  通过**表名.列名%TYPE**指定变量的类型和长度，例如： v_name  emp.ename%TYPE;
  
  记录型变量: 接受表中的一整行记录，相当于Java中的一个对象   关键字  ROWTYPE
  语法： 变量名称   表名%ROWTYPE， 例如： **v_emp emp%rowtype;**
  
  
  **/                                                
  v_id         number := 123;   -- 变量赋值方式 1.直接赋值 :=  2. 语句赋值  select 值 into 变量
  v_err        varchar2(600);  -- 定义 局部变量
  v_curType    varchar2(50);
  v_age        T_SYS_PROCEDURE.age%TYPE := 12;  -- 引用型变量
  v_produce    T_SYS_PROCEDURE%ROWTYPE;    --记录型变量
  v_num        number := 1;
  
  v_nameC      varchar2(50);
  v_ageC       varchar2(50);
  cursor cur is select name, age from T_SYS_PROCEDURE where name is not null;  -- 定义无参游标
  cursor curN(v_test number) is select name from T_SYS_PROCEDURE where id = v_test;  -- 定义有参游标
begin

  select SEQ_PROCEDURE_ID.nextval INTO v_id FROM DUAL;
  
  select * into v_produce from T_SYS_PROCEDURE where id = 666;
  
  --   记录型变量 只能存储一行完成的数据, 不接受指定字段;;; 不接受返回多行的数据
  -- select id, name into v_produce from T_SYS_PROCEDURE where id = 666;
  


    /*  条件分支
      BEGIN
        IF 条件1 THEN 执行1          
         ELSIF 条件2 THEN 执行 2        
         ELSE 执行3  
        END IF; 
      END;
  */
  
  if (v_type = 'N') then v_curType := '男';
  
  elsif (v_type = 'L') then v_curType := '女';
  
  else v_curType := '未知';
  end if; -- 必须存在
  
  
  /*  循环 
        LOOP
          EXIT WHEN 退出循环条件;  
        END LOOP;
          
  */
  
  LOOP 
    exit when v_num > 12;
/*    INSERT INTO T_SYS_PROCEDURE
    (ID, CREATE_DATE, AGE, TYPE, NAME)
    VALUES(SEQ_PROCEDURE_ID.nextval, sysdate, v_produce.id, v_curType, v_num);*/
    
    v_num := v_num + 1;
    
  end loop;
  
  
  
  /*  游标  无参游标    有参游标
   用于临时存储一个查询返回的多行数据（结果集,类似于Java的Jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。
   游标与LOOP一起使用  相当于java forEach 
   游标的使用方式：声明--->打开--->读取--->关闭
   
   定义有参游标, 参数主要作用于其查询游标数据阶段;
   
   | 游标的属性    | 返回值类型 | 说明                                        |
| ------------- | ---------- | ------------------------------------------- |
| %ROWCOUNT     | 整型       | 获得FETCH语句返回的数据行数                 |
| %FOUND        | 布尔型     | 最近的FETCH语句返回一行数据则为真，否则为假 |
| %NOTFOUND     | 布尔型     | 与%FOUND属性返回值相反                      |
| %ISOPEN       | 布尔型     | 游标已经打开时值为真，否则为假              |
  
  */
  
  open cur; -- 打开游标
       loop  --遍历游标中的值
         fetch cur into v_nameC, v_ageC;  --通过FETCH语句获取游标中的值并赋值给变量
         
         exit when cur%notfound;  --通过%NOTFOUND判断是否有值,有值继续操作,没有则退出循环
         
         -- fetch cur into v_nameC, v_ageC;  --通过FETCH语句获取游标中的值并赋值给变量, 执行次数为13次, 比之前多一次, 
         -- %NOTFOUND属性默认值为FLASE,所以在循环中要注意判断条件的位置.如果先判断在FETCH会导致最后一条记录的值被打印两次(多循环一次默认);
         
         update T_SYS_PROCEDURE set ext1 = v_nameC || '--' || v_ageC where name = v_nameC;
         update T_SYS_PROCEDURE set type = type + 1 where id = 666;
         
        end loop;
        
   close cur; --关闭游标
  
  
  
  



  commit;
  ReturnMsg := '666';
  
  
  
  
exception
  when others then  -- 当上述代码块执行报错, 抛出异常, SQLCODE和SQLERRM是两个常用的系统变量
    rollback;
    v_err := SQLCODE || '::' || SUBSTR(SQLERRM, 1, 200);
    dbms_output.put_line(v_err);  -- DBMS_OUTPUT 为oracle内置程序包，相当于Java中的System.out,而PUT_LINE（）是调用的方法,相当于println（）方法
    commit; 
    ReturnMsg := v_err;
end;