百度360必应搜狗淘宝本站头条
linux文件压缩linux用户组查看linux磁盘mysql安装linuxlinux多线程
当前位置：网站首页 > 优雅编程 > 正文

PLSQL一些常用的知识点（plsql常用操作）

sinye56 2024-10-06 00:41 4 浏览 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;

  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;

  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;

  9. -- 执行if判断

  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;

  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);

  20. end loop;

  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');

  13. -- 参数传递方式二：
  14. select 'S001' into v_query_student_id from dual;
  15. open cur_student(v_query_student_id);

  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);

  26. end loop;

  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)';

  9. -- 带变量的执行
  10. v_sql := 'drop table ' || v_table_name;
  11. execute immediate v_sql;

  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;

  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('测试异常');

  7. -- 产生异常
  8. v_num := 1 / 0;

  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;

  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. );

  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;

  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;

  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;

  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;

  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;

  31. -- 关闭游标
  32. close cur_student;
  33. end;

  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);

  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;

  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 || '个');

  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;

  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;

  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

相关推荐

Linux基础知识之修改root用户密码

现象：Linux修改密码出现：Authenticationtokenmanipulationerror。故障解决办法：进入单用户，执行pwconv，再执行passwdroot。...

Linux如何修改远程访问端口

对于Linux服务器而言，其默认的远程访问端口为22。但是，出于安全方面的考虑，一般都会修改该端口。下面我来简答介绍一下如何修改Linux服务器默认的远程访问端口。对于默认端口而言，其相关的配置位于/...

如何批量更改文件的权限

如果你发觉一个目录结构下的大量文件权限(读、写、可执行)很乱时,可以执行以下两个命令批量修正：批量修改文件夹的权限chmod755-Rdir_name批量修改文件的权限finddir_nam...

CentOS「linux」学习笔记10:修改文件和目录权限

?linux基础操作：主要介绍了修改文件和目录的权限及chown和chgrp高级用法6.chmod修改权限1：字母方式[修改文件或目录的权限]u代表所属者，g代表所属组，o代表其他组的用户，a代表所有...

Linux下更改串口的权限

问题描述我在Ubuntu中使用ArduinoIDE，并且遇到串口问题。它过去一直有效，但由于可能不必要的原因，我觉得有必要将一些文件的所有权从root所有权更改为我的用户所有权。...

Linux chown命令:修改文件和目录的所有者和所属组

chown命令，可以认为是"changeowner"的缩写，主要用于修改文件（或目录）的所有者，除此之外，这个命令也可以修改文件（或目录）的所属组。当只需要修改所有者时，可使用...

chmod修改文件夹及子目录权限的方法

chmod修改文件夹及子目录权限的方法打开终端进入你需要修改的目录然后执行下面这条命令chmod777*-R全部子目录及文件权限改为777查看linux文件的权限：ls-l文件名称查看li...

Android 修改隐藏设置项权限

在Android系统中，修改某些隐藏设置项或权限通常涉及到系统级别的操作，尤其是针对非标准的、未在常规用户界面显示的高级选项。这些隐藏设置往往与隐私保护、安全相关的特殊功能有关，或者涉及开发者选项、权...

完蛋了!我不小心把Linux所有的文件权限修改了!在线等修复!

最近一个客户在群里说他一不小心把某台业务服务器的根目录权限给改了，本来想修改当前目录，结果执行成了根目录。...

linux改变安全性设置-改变所属关系

CentOS7.3学习笔记总结（五十八）-改变安全性设置-改变所属关系在以前的文章里，我介绍过linux文件权限，感兴趣的朋友可以关注我，阅读一下这篇文章。这里我们不在做过的介绍，注重介绍改变文件或者...

Python基础到实战一飞冲天(一)--linux基础(七)修改权限chmod

#07_Python基础到实战一飞冲天（一）--linux基础（七）--修改权限chmod-root-groupadd-groupdel-chgrp-username-passwd...

linux更改用户权限为root权限方法大全

背景在使用linux系统时，经常会遇到需要修改用户权限为root权限。通过修改用户所属群组groupid为root，此操作只能使普通用户实现享有部分root权限，普通用户仍不能像root用户一样享有超...

怎么用ip命令在linux中添加路由表项?

在Linux中添加路由表项，可以使用ip命令的route子命令。添加路由表项的基本语法如下：sudoiprouteadd<network>via<gateway>这...

Linux配置网络

1、网卡名配置相关文件回到顶部网卡名命名规则文件：/etc/udev/rules.d/70-persistent-net.rules#PCIdevice0x8086:0x100f(e1000)...

Linux系列---网络配置文件

1.网卡配置文件在/etc/sysconfig/network-scripts/下:[root@oldboynetwork-scripts]#ls/etc/sysconfig/network-s...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表