首页 > 数据库 > Oracle > PLSQL一些常用的知识点

PLSQL一些常用的知识点

福牛 5月 14, 2023 15 0

1、背景

此处简单的记录一下在 oracle中如何使用plsql语法，记录一些简单的例子，防止以后忘记。

2、变量的声明

  1. declare
  2.      — 声明变量
  3.      v_name varchar2(20);
  4.      — 此变量由 select into 赋值
  5.      v_man_sex number;
  6.      — v_sex 变量的类型和 student表中的 sex 字段的类型一致
  7.      v_sex student.sex%TYPE;
  8.      — v_row 中保存的是 student表中的一整行字段, 也可以是游标中的一整行
  9.      v_row student%rowtype;
  10.      — 声明变量并赋值
  11.      v_addr varchar2(100) := ‘湖北省’;
  12.      — 声明日期变量
  13.      v_date date := sysdate;
  15.      — 定义一个记录类型
  16.      type STUDENT_INFO is record
  17.          (
  18.          student_id student.student_id%TYPE,
  19.          student_name student.student_name%TYPE
  20.          );
  21.      — 定义基于记录的嵌套表
  22.      type nested_student_info is table of STUDENT_INFO;
  23.      — 声明变量
  24.      student_list nested_student_info;
  25.  
  26. begin
  27.      — 直接赋值
  28.      v_name := ‘直接赋值’;
  29.      v_date := to_date(‘2023-12-12’, ‘yyyy-mm-dd’);
  30.      — 单个字段语句赋值
  31.      select count(*) into v_man_sex from student where sex = 1;
  32.      — 多个字段赋值
  33.      select student_name,sex into v_name,v_sex from student where student_id = ‘S003’;
  34.      — 获取一行数据 ( 此处需要查询出所有的字段，否则可能报错 )
  35.      select student_id,student_name,sex,CREATE_TIME into v_row from student where student_id = ‘S002’;
  36.      — 打印输出
  37.      DBMS_OUTPUT.PUT_LINE(‘日期:’ || v_date || ‘姓名:’ || v_name || ‘,’ || v_row.STUDENT_NAME || ‘ 男生人数:’ || v_man_sex || ‘ 地址:’ || v_addr );
  38. end;

3、if 判断

统计总共有多少个学生，并进行if判断。

  1. declare
  2.      — 声明一个变量，记录有多少个学生
  3.      v_student_count number;
  4. begin
  5.      — 给 v_student_count 变量赋值
  6.      select count(*) into v_student_count from student;
  7.  
  8.      — 执行if判断
  10.      if v_student_count > 3 then
  11.          DBMS_OUTPUT.PUT_LINE(‘当前学生数为: [‘ || v_student_count || ‘]>3’);
  12.      elsif v_student_count >=2 then
  13.          DBMS_OUTPUT.PUT_LINE(‘当前学生数为: [‘ || v_student_count || ‘] in [2,3]’);
  14.      else
  15.          DBMS_OUTPUT.PUT_LINE(‘当前学生数为: [‘ || v_student_count || ‘]<2’);
  16.      end if;
  17. end;

4、case

  1. — case
  2. declare
  3.      — 声明一个变量，记录有多少个学生
  4.      v_student_count number;
  5. begin
  6.      — 给 v_student_count 变量赋值
  7.      select count(*) into v_student_count from student;
  8.  
  9.      — 执行if判断
  10.  
  11.      case when v_student_count > 3 then
  12.          DBMS_OUTPUT.PUT_LINE(‘当前学生数为: [‘ || v_student_count || ‘]>3’);
  13.      when v_student_count >=2 then
  14.          DBMS_OUTPUT.PUT_LINE(‘当前学生数为: [‘ || v_student_count || ‘] in [2,3]’);
  15.      else
  16.          DBMS_OUTPUT.PUT_LINE(‘当前学生数为: [‘ || v_student_count || ‘]<2’);
  17.      end case;
  18. end;

5、循环

输出1到100

1、loop 循环

  1. declare
  2.      — 定义一个变量并赋值
  3.      v_count number := 1;
  4. begin
  5.      loop
  6.          — 提出条件
  7.          exit when v_count > 100;
  8.          DBMS_OUTPUT.PUT_LINE(‘当前 count = ‘ || v_count);
  9.          — v_count 加1
  10.          v_count := v_count + 1;
  11.      end loop;
  12. end;

2、while 循环

  1. — while 循环
  2. declare
  3.      — 定义一个变量并赋值
  4.      v_count number := 1;
  5. begin
  6.      while v_count <= 100 loop
  7.          DBMS_OUTPUT.PUT_LINE(‘当前 count = ‘ || v_count);
  8.          — v_count 加1
  9.          v_count := v_count + 1;
  10.      end loop;
  11. end;

3、for循环

  1. — for 循环
  2. declare
  3.      — 定义一个变量
  4.      v_count number;
  5. begin
  6.      for v_count in 1..100 loop
  7.          DBMS_OUTPUT.PUT_LINE(‘当前 count = ‘ || v_count);
  8.      end loop;
  9. end;

6、游标

1、无参数的游标

  1. — 游标
  2. declare
  3.      — 声明一个游标
  4.      cursor cur_student is select student_id,student_name,sex from student;
  5.      — 声明变量
  6.      row_cur_student cur_student%rowtype;
  7. begin
  8.      — 打开游标
  9.      open cur_student;
  10.  
  11.      — 遍历数据
  12.      loop
  13.          — 获取一行数据
  14.          fetch cur_student into row_cur_student;
  15.          — 退出
  16.          exit when cur_student%NOTFOUND;
  17.          — 执行业务逻辑（此句如果移动到exit when上方，则可能会多打印一句）
  18.          DBMS_OUTPUT.PUT_LINE(‘studentId:’ || row_cur_student.STUDENT_ID || ‘ studentName:’ || row_cur_student.STUDENT_NAME);
  19.  
  20.      end loop;
  21.  
  22.      — 关闭游标
  23.      close cur_student;
  24. end;

2、带参数的游标

  1. declare
  2.      — 声明一个游标, 需要传递v_student_id参数
  3.      cursor cur_student(v_student_id student.student_id%TYPE) is
  4.          select student_id,student_name,sex from student where student_id = v_student_id;
  5.      — 声明变量
  6.      row_cur_student cur_student%rowtype;
  7.      — 此变量通过查询获取值，然后带到游标中
  8.      v_query_student_id student.student_id%TYPE;
  9. begin
  10.      — 打开游标
  11.      –参数传递方式一： open cur_student(‘S001’);
  12.  
  13.      — 参数传递方式二：
  14.      select ‘S001’ into v_query_student_id from dual;
  15.      open cur_student(v_query_student_id);
  16.  
  17.      — 遍历数据
  18.      loop
  19.          — 获取一行数据
  20.          fetch cur_student into row_cur_student;
  21.          — 退出
  22.          exit when cur_student%NOTFOUND;
  23.          — 执行业务逻辑（此句如果移动到exit when上方，则可能会多打印一句）
  24.          DBMS_OUTPUT.PUT_LINE(‘studentId:’ || row_cur_student.STUDENT_ID || ‘ studentName:’ || row_cur_student.STUDENT_NAME);
  25.  
  26.      end loop;
  27.  
  28.      — 关闭游标
  29.      close cur_student;
  30. end;

7、执行ddl dml

需要放到 execute immediate中执行，否则会报错。

  1. declare
  2.      v_table_name varchar2(20) := ‘student_bak’;
  3.      — 拼接一个动态SQL
  4.      v_sql varchar2(100);
  5. begin
  6.      execute immediate ‘create table student_bak as select * from student’;
  7.      execute immediate ‘alter table student_bak add new_cloumn varchar2(20)’;
  8.  
  9.      — 带变量的执行
  10.      v_sql := ‘drop table ‘ || v_table_name;
  11.      execute immediate v_sql;
  12.  
  13. end;

8、存储过程

1、无参数的存储过程

  1. — 无参数的存储过程
  2. create or replace procedure sp_print_all_student
  3. is
  4.      — 声明一个游标
  5.      cursor c_all_student is select student_id,student_name from student;
  6.      — 声明一个变量
  7.      row_student c_all_student%rowtype;
  8. begin
  9.      — 循环游标
  10.      for row_student in c_all_student loop
  11.          DBMS_OUTPUT.PUT_LINE(row_student.STUDENT_ID || ‘ ‘ || row_student.STUDENT_NAME);
  12.      end loop;
  13. end;
  14. — 调用
  15. begin
  16.      SP_PRINT_ALL_STUDENT();
  17. end;

2、有输入输出参数的存储过程

  1. — 有参数的存储过程
  2. create or replace procedure sp_find_student(/** 输入参数 */ i_student_id in student.student_id%TYPE,
  3.                      /** 输出参数 */ o_student_name out student.student_name%TYPE)
  4. IS
  5.      — 定义变量并赋值
  6.      v_student_id varchar2(64) := i_student_id;
  7. begin
  8.      DBMS_OUTPUT.PUT_LINE(‘v_student_id:’ || v_student_id);
  9.      — 将查询到的 student_name 赋值到 o_student_name
  10.      select student_name into o_student_name from student where student_id = i_student_id;
  11. end;
  12.  
  13. declare
  14.      — 定义一个变量用于接收存储过程的返回值
  15.      output_student_name student.student_name%TYPE;
  16. begin
  17.      sp_find_student(‘S001’, output_student_name);
  18.      — 输出存储过程的返回值
  19.      DBMS_OUTPUT.PUT_LINE(output_student_name);
  20. end;

3、merge into 的使用

存在更新，不存在插入。

  1. create or replace procedure sp_merge_into(i_student_id in varchar2)
  2. IS
  3. begin
  4.      — 如果 using 中查询出来的数据，通过 on 条件匹配的话，则更新 student_bak表，否则插入student_bak表
  5.      merge into STUDENT_BAK t
  6.      using (select * from student where student_id = i_student_id) s
  7.      on ( t.student_id = s.student_id )
  8.      when matched then update set
  9.                      — t.STUDENT_ID = s.STUDENT_ID, on中的条件不可更新
  10.                      t.STUDENT_NAME = s.STUDENT_NAME,
  11.                      t.SEX = s.SEX,
  12.                      t.CREATE_TIME = s.CREATE_TIME
  13.      when not matched then insert(student_id, student_name, create_time) values (
  14.                      s.STUDENT_ID,
  15.                      s.STUDENT_NAME,
  16.                      s.CREATE_TIME
  17.                      );
  18.      commit ;
  19. end;

4、测试异常

  1. create or replace procedure sp_error
  2. IS
  3.      v_num number;
  4. begin
  5.      DBMS_OUTPUT.PUT_LINE(‘测试异常’);
  6.  
  7.      — 产生异常
  8.      v_num := 1 / 0;
  9.  
  10.      exception — 存储过程异常
  11.          when too_many_rows then
  12.                  dbms_output.put_line(‘返回值多于1行’);
  13.          when others then
  14.                  — 异常处理方法，可以是打印错误，然后进行回滚等操作，下面操作一样，看自己情况决定
  15.                  rollback;
  16.                  dbms_output.put_line(‘错误码：’ ||sqlcode);
  17.                  dbms_output.put_line(‘异常信息：’ || substr(sqlerrm, 1, 512));
  18. end;
  19.  
  20. begin
  21.      sp_error();
  22. end;

5、bulk into & record

1、select into 中使用 bulk into & record

  1. create or replace procedure sp_bulk_collect_01
  2. IS
  3.      — 定义一个记录类型
  4.      type STUDENT_INFO is record
  5.          (
  6.          student_id student.student_id%TYPE,
  7.          student_name student.student_name%TYPE
  8.          );
  9.  
  10.      — 定义基于记录的嵌套表
  11.      type nested_student_info is table of STUDENT_INFO;
  12.      — 声明变量
  13.      student_list nested_student_info;
  14. begin
  15.      — 使用 bulk collect into 将所获取的结果集一次性绑定到记录变量 student_list 中
  16.      select student_id,student_name bulk collect into student_list from student;
  17.  
  18.      — 遍历
  19.      for i in student_list.first .. student_list.last loop
  20.          DBMS_OUTPUT.PUT_LINE(‘studentId:’ || student_list(i).student_id || ‘ studentName:’ || student_list(i).student_name);
  21.      end loop;
  22. end;
  23.  
  24. begin
  25.      sp_bulk_collect_01;
  26. end;

2、fetch into 中使用 bulk into & forall

  1. — bulk collect
  2. create or replace procedure sp_bulk_collect_02
  3. IS
  4.      — 定义一个游标
  5.      cursor cur_student is select student_id,student_name,sex,create_time from student;
  6.      — 定义基于游标的嵌套表
  7.      type nested_student_info is table of cur_student%rowtype;
  8.      — 声明变量
  9.      student_list nested_student_info;
  10. begin
  11.      — 打开游标
  12.      open cur_student;
  13.          loop
  14.              — 一次获取2条数据插入到 student_list 中
  15.              fetch cur_student bulk collect into student_list limit 2;
  16.              — 退出
  17.              —exit when student_list%notfound; 不可使用这种方式
  18.              exit when student_list.count = 0;
  19.  
  20.              — 输出
  21.              for i in student_list.first .. student_list.last loop
  22.                  DBMS_OUTPUT.PUT_LINE(‘studentId:’ || student_list(i).student_id || ‘ studentName:’ || student_list(i).student_name);
  23.              end loop;
  24.  
  25.              — 使用 forall 更新数据, 可以将多个dml语句批量发送给SQL引擎，提高执行效率。
  26.              forall i in student_list.first .. student_list.last
  27.                  update student set student_name = student_list(i).STUDENT_NAME || ‘_update’ where student_id = student_list(i).STUDENT_ID;
  28.              commit ;
  29.          end loop;
  30.  
  31.      — 关闭游标
  32.      close cur_student;
  33. end;
  34.  
  35. begin
  36.      sp_bulk_collect_02;
  37. end;

6、接收数组参数

  1. — 创建StudentIdList数组的长度是4，每一项最多存20个字符
  2. create or replace type StudentIdList as varray(4) of varchar2(20);
  3.  
  4. — 创建存储过程，接收数组参数
  5. create or replace procedure sp_param_list(studentIdList in StudentIdList)
  6. is
  7. begin
  8.      for i in 1..studentIdList.COUNT loop
  9.          DBMS_OUTPUT.PUT_LINE(‘studentId:’ || studentIdList(i));
  10.      end loop;
  11. end;
  12. declare
  13.      begin
  14.      sp_param_list(STUDENTIDLIST(‘d’,‘c’,‘S001’,‘S0021222222222233’));
  15. end;

7、接收数组对象，并将数组对象转换成表使用

  1. — 创建数据库对象
  2. create or replace type StudentInfo is object(
  3.      studentId varchar2(64),
  4.      studentName varchar2(64)
  5. );
  6. — 创建数组对象
  7. create or replace type StudentInfoArr as table of StudentInfo;
  8.  
  9. — 创建存储过程
  10. create or replace procedure sp_param_list_02(arr in StudentInfoArr)
  11. is
  12.      — 声明一个变量，记录传递进来的arr的数量
  13.      v_student_count number := 0;
  14. begin
  15.      — 传递进来的数组转换成使用
  16.      select count(*) into v_student_count from table(cast(arr AS StudentInfoArr))
  17.      where studentId like ‘S%’;
  18.      DBMS_OUTPUT.PUT_LINE(‘传递进来学生学号以S开头的学生有: ‘ || v_student_count || ‘个’);
  19.  
  20.      — 输出列表参数
  21.      for i in 1..arr.COUNT loop
  22.          DBMS_OUTPUT.PUT_LINE(‘studentId:’ || arr(i).studentId || ‘ studentName:’ || arr(i).studentName);
  23.      end loop;
  24. end;
  25.  
  26. declare
  27. begin
  28.      sp_param_list_02(arr => StudentInfoArr(StudentInfo(‘S001’,‘张三’),StudentInfo(‘S002’,‘李四’)));
  29. end;

8、返回多个参数

  1. create or replace procedure sp_return_value(stuInfoList out Sys_Refcursor)
  2. IS
  3. begin
  4.      open stuInfoList for select STUDENT_ID,STUDENT_NAME,SEX from STUDENT;
  5. end;
  6.  
  7. declare
  8.      stu Sys_Refcursor;
  9.      v_student_id STUDENT.STUDENT_ID%TYPE;
  10.      v_student_name STUDENT.STUDENT_NAME%TYPE;
  11.      v_sex STUDENT.SEX%TYPE;
  12. begin
  13.      SP_RETURN_VALUE( stu);
  14.      loop
  15.          fetch stu into v_student_id,v_student_name,v_sex;
  16.          exit when stu%notfound;
  17.          DBMS_OUTPUT.PUT_LINE(‘studentId:’ || v_student_id || ‘ studentName: ‘ || v_student_name);
  18.      end loop;
  19.      end;

9、程序包 package

1、定义包头

包头可以简单的理解Java中的接口。

  1. create or replace package pkg_huan as
  2.      v_pkg_name varchar2(30) := ‘pkg_huan’;
  3.      function add(param1 in number, param2 in number) return number;
  4.      procedure sp_pkg_01;
  5.      procedure sp_pkg_02(param1 in varchar2);
  6. end pkg_huan;

2、实现包体

包体可以简单的理解java中的实现接口的类。

  1. create or replace package body pkg_huan as
  2.      — 实现function
  3.      function add(param1 in number, param2 in number) return number IS
  4.      begin
  5.          return param1 + param2;
  6.      end;
  7.      — 实现无参数的存储过程
  8.      procedure sp_pkg_01 as
  9.      begin
  10.          DBMS_OUTPUT.PUT_LINE(‘package name:’ || v_pkg_name || ‘procedure name: sp_pkg_01’);
  11.      end;
  12.      — 实现有参数的存储过程
  13.      procedure sp_pkg_02(param1 in varchar2) as
  14.      begin
  15.          DBMS_OUTPUT.PUT_LINE(‘param1:’ || param1);
  16.      end;
  17. end;

3、调用包中的方法或存储过程

  1. begin
  2.      — 调用方法
  3.      DBMS_OUTPUT.PUT_LINE(‘1+2=’ || PKG_HUAN.add(1,2));
  4.      — 调用无参数的存储过程
  5.      PKG_HUAN.sp_pkg_01();
  6.      — 调用有参数的存储过程
  7.      PKG_HUAN.sp_pkg_02(12);
  8. end;

10、参考链接

1、http://www.cis.famu.edu/support/10g/Oracle_Database_10g/doc/appdev.102/b14261/objects.htm

标签

相关推荐

    发表评论