PLSQL一些常用知识点梳理总结

目录
  • 1、背景
  • 2、变量的声明
  • 3、if 判断
  • 4、case
  • 5、循环
    • 1、loop 循环
    • 2、while 循环
    • 3、for循环
  • 6、游标
    • 1、无参数的游标
    • 2、带参数的游标
  • 7、执行ddl dml
  • 8、存储过程
    • 1、无参数的存储过程
    • 2、有输入输出参数的存储过程
    • 3、merge into 的使用
    • 4、测试异常
    • 5、bulk into & record
    • 1、select into 中使用 bulk into & record
    • 2、fetch into 中使用 bulk into & forall
    • 6、接收数组参数
    • 7、接收数组对象,并将数组对象转换成表使用
    • 8、返回多个参数
  • 9、程序包 package
    • 1、定义包头
    • 2、实现包体
    • 3、调用包中的方法或存储过程

1、背景

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

2、变量的声明

declare
    -- 声明变量
    v_name varchar2(20);
    -- 此变量由 select into 赋值
    v_man_sex number;
    -- v_sex 变量的类型和 student表中的 sex 字段的类型一致
    v_sex student.sex%TYPE;
    -- v_row 中保存的是 student表中的一整行字段, 也可以是游标中的一整行
    v_row student%rowtype;
    -- 声明变量并赋值
    v_addr varchar2(100) := '湖北省';
    -- 声明日期变量
    v_date date := sysdate;
    -- 定义一个记录类型
    type STUDENT_INFO is record
     (
        student_id student.student_id%TYPE,
        student_name student.student_name%TYPE
     );
    -- 定义基于记录的嵌套表
    type nested_student_info is table of STUDENT_INFO;
    -- 声明变量
    student_list nested_student_info;
begin
    -- 直接赋值
    v_name := '直接赋值';
    v_date := to_date('2023-12-12', 'yyyy-mm-dd');
    -- 单个字段语句赋值
    select count(*) into v_man_sex from student where sex = 1;
    -- 多个字段赋值
    select student_name,sex into v_name,v_sex from student where student_id = 'S003';
    -- 获取一行数据 ( 此处需要查询出所有的字段,否则可能报错 )
    select student_id,student_name,sex,CREATE_TIME into v_row from student where student_id = 'S002';
    -- 打印输出
    DBMS_OUTPUT.PUT_LINE('日期:' || v_date || '姓名:' || v_name || ',' || v_row.STUDENT_NAME || ' 男生人数:' || v_man_sex || ' 地址:' || v_addr );
end;

3、if 判断

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

declare
    -- 声明一个变量,记录有多少个学生
    v_student_count number;
begin
    -- 给 v_student_count 变量赋值
    select count(*) into v_student_count from student;
    -- 执行if判断
    if v_student_count > 3 then
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']>3');
    elsif v_student_count >=2 then
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || '] in [2,3]');
    else
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']<2');
    end if;
end;

4、case

-- case
declare
    -- 声明一个变量,记录有多少个学生
    v_student_count number;
begin
    -- 给 v_student_count 变量赋值
    select count(*) into v_student_count from student;
    -- 执行if判断
    case when v_student_count > 3 then
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']>3');
    when v_student_count >=2 then
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || '] in [2,3]');
    else
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']<2');
    end case;
end;

5、循环

输出1到100

1、loop 循环

declare
    -- 定义一个变量并赋值
    v_count number := 1;
begin
    loop
        -- 提出条件
        exit when v_count > 100;
        DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count);
        -- v_count 加1
        v_count := v_count + 1;
    end loop;
end;

2、while 循环

-- while 循环
declare
    -- 定义一个变量并赋值
    v_count number := 1;
begin
    while v_count <= 100 loop
        DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count);
        -- v_count 加1
        v_count := v_count + 1;
    end loop;
end;

3、for循环

-- for 循环
declare
    -- 定义一个变量
    v_count number;
begin
    for v_count in 1..100 loop
        DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count);
    end loop;
end;

6、游标

1、无参数的游标

-- 游标
declare
    -- 声明一个游标
    cursor cur_student is select student_id,student_name,sex from student;
    -- 声明变量
    row_cur_student cur_student%rowtype;
begin
    -- 打开游标
    open cur_student;
    -- 遍历数据
    loop
        -- 获取一行数据
        fetch cur_student into row_cur_student;
        -- 退出
        exit when cur_student%NOTFOUND;
        -- 执行业务逻辑(此句如果移动到exit when上方,则可能会多打印一句)
        DBMS_OUTPUT.PUT_LINE('studentId:' || row_cur_student.STUDENT_ID || ' studentName:' || row_cur_student.STUDENT_NAME);
    end loop;
    -- 关闭游标
    close cur_student;
end;

2、带参数的游标

declare
    -- 声明一个游标, 需要传递v_student_id参数
    cursor cur_student(v_student_id student.student_id%TYPE) is
        select student_id,student_name,sex from student where student_id = v_student_id;
    -- 声明变量
    row_cur_student cur_student%rowtype;
    -- 此变量通过查询获取值,然后带到游标中
    v_query_student_id student.student_id%TYPE;
begin
    -- 打开游标
    --参数传递方式一: open cur_student('S001');
    -- 参数传递方式二:
    select 'S001' into v_query_student_id from dual;
    open cur_student(v_query_student_id);
    -- 遍历数据
    loop
        -- 获取一行数据
        fetch cur_student into row_cur_student;
        -- 退出
        exit when cur_student%NOTFOUND;
        -- 执行业务逻辑(此句如果移动到exit when上方,则可能会多打印一句)
        DBMS_OUTPUT.PUT_LINE('studentId:' || row_cur_student.STUDENT_ID || ' studentName:' || row_cur_student.STUDENT_NAME);
    end loop;
    -- 关闭游标
    close cur_student;
end;

7、执行ddl dml

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

declare
    v_table_name varchar2(20) := 'student_bak';
    -- 拼接一个动态SQL
    v_sql varchar2(100);
begin
    execute immediate 'create table student_bak as select * from student';
    execute immediate 'alter table student_bak add new_cloumn varchar2(20)';
    -- 带变量的执行
    v_sql := 'drop table ' || v_table_name;
    execute immediate v_sql;
end;

8、存储过程

1、无参数的存储过程

-- 无参数的存储过程
create or replace procedure sp_print_all_student
is
    -- 声明一个游标
    cursor c_all_student is select student_id,student_name from student;
    -- 声明一个变量
    row_student c_all_student%rowtype;
begin
    -- 循环游标
    for row_student in c_all_student loop
        DBMS_OUTPUT.PUT_LINE(row_student.STUDENT_ID || ' ' || row_student.STUDENT_NAME);
    end loop;
end;
-- 调用
begin
    SP_PRINT_ALL_STUDENT();
end;

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

-- 有参数的存储过程
create or replace procedure sp_find_student(/** 输入参数 */ i_student_id in student.student_id%TYPE,
                                           /** 输出参数 */ o_student_name out student.student_name%TYPE)
IS
    -- 定义变量并赋值
    v_student_id varchar2(64) := i_student_id;
begin
    DBMS_OUTPUT.PUT_LINE('v_student_id:' || v_student_id);
    -- 将查询到的 student_name 赋值到 o_student_name
    select student_name into o_student_name from student where student_id = i_student_id;
end;
declare
    -- 定义一个变量用于接收存储过程的返回值
    output_student_name student.student_name%TYPE;
begin
    sp_find_student('S001', output_student_name);
    -- 输出存储过程的返回值
    DBMS_OUTPUT.PUT_LINE(output_student_name);
end;

3、merge into 的使用

存在更新,不存在插入。

create or replace procedure sp_merge_into(i_student_id in varchar2)
IS
begin
    -- 如果 using 中查询出来的数据,通过 on 条件匹配的话,则更新 student_bak表,否则插入student_bak表
    merge into STUDENT_BAK t
    using (select * from student where student_id = i_student_id) s
    on ( t.student_id = s.student_id )
    when matched then update set
                                 -- t.STUDENT_ID = s.STUDENT_ID, on中的条件不可更新
                                 t.STUDENT_NAME = s.STUDENT_NAME,
                                 t.SEX = s.SEX,
                                 t.CREATE_TIME = s.CREATE_TIME
    when not matched then insert(student_id, student_name, create_time) values (
                                         s.STUDENT_ID,
                                         s.STUDENT_NAME,
                                         s.CREATE_TIME
                                        );
    commit ;
end;

4、测试异常

create or replace procedure sp_error
IS
    v_num number;
begin
    DBMS_OUTPUT.PUT_LINE('测试异常');
    -- 产生异常
    v_num := 1 / 0;
    exception -- 存储过程异常
        when too_many_rows then
                dbms_output.put_line('返回值多于1行');
        when others then
              -- 异常处理方法,可以是打印错误,然后进行回滚等操作,下面操作一样,看自己情况决定
              rollback;
              dbms_output.put_line('错误码:' ||sqlcode);
              dbms_output.put_line('异常信息:' || substr(sqlerrm, 1, 512));
end;
begin
    sp_error();
end;

5、bulk into & record

1、select into 中使用 bulk into & record

create or replace procedure sp_bulk_collect_01
IS
    -- 定义一个记录类型
    type STUDENT_INFO is record
     (
        student_id student.student_id%TYPE,
        student_name student.student_name%TYPE
     );
    -- 定义基于记录的嵌套表
    type nested_student_info is table of STUDENT_INFO;
    -- 声明变量
    student_list nested_student_info;
begin
    -- 使用 bulk collect into 将所获取的结果集一次性绑定到记录变量 student_list 中
    select student_id,student_name bulk collect into student_list from student;
    -- 遍历
    for i in student_list.first .. student_list.last loop
        DBMS_OUTPUT.PUT_LINE('studentId:' || student_list(i).student_id || ' studentName:' || student_list(i).student_name);
    end loop;
end;
begin
    sp_bulk_collect_01;
end;

2、fetch into 中使用 bulk into & forall

-- bulk collect
create or replace procedure sp_bulk_collect_02
IS
    -- 定义一个游标
    cursor cur_student is select student_id,student_name,sex,create_time from student;
    -- 定义基于游标的嵌套表
    type nested_student_info is table of cur_student%rowtype;
    -- 声明变量
    student_list nested_student_info;
begin
    -- 打开游标
    open cur_student;
        loop
            -- 一次获取2条数据插入到 student_list 中
            fetch cur_student bulk collect into student_list limit 2;
            -- 退出
            --exit when student_list%notfound; 不可使用这种方式
            exit when student_list.count = 0;
            -- 输出
            for i in student_list.first .. student_list.last loop
                DBMS_OUTPUT.PUT_LINE('studentId:' || student_list(i).student_id || ' studentName:' || student_list(i).student_name);
            end loop;
            -- 使用 forall 更新数据, 可以将多个dml语句批量发送给SQL引擎,提高执行效率。
            forall i in student_list.first .. student_list.last
                update student set student_name = student_list(i).STUDENT_NAME || '_update' where student_id = student_list(i).STUDENT_ID;
            commit ;
        end loop;
    -- 关闭游标
    close cur_student;
end;
begin
    sp_bulk_collect_02;
end;

6、接收数组参数

-- 创建StudentIdList数组的长度是4,每一项最多存20个字符
create or replace type StudentIdList as varray(4) of varchar2(20);
-- 创建存储过程,接收数组参数
create or replace procedure sp_param_list(studentIdList in StudentIdList)
is
begin
    for i in 1..studentIdList.COUNT loop
        DBMS_OUTPUT.PUT_LINE('studentId:' || studentIdList(i));
    end loop;
end;
declare
 begin
    sp_param_list(STUDENTIDLIST('d','c','S001','S0021222222222233'));
end;

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

-- 创建数据库对象
create or replace type StudentInfo is object(
    studentId varchar2(64),
    studentName varchar2(64)
);
-- 创建数组对象
create or replace type StudentInfoArr as table of StudentInfo;
-- 创建存储过程
create or replace procedure sp_param_list_02(arr in StudentInfoArr)
is
    -- 声明一个变量,记录传递进来的arr的数量
    v_student_count number := 0;
begin
    -- 传递进来的数组转换成使用
    select count(*) into v_student_count from table(cast(arr AS StudentInfoArr))
    where studentId like 'S%';
    DBMS_OUTPUT.PUT_LINE('传递进来学生学号以S开头的学生有: ' || v_student_count || '个');
    -- 输出列表参数
    for i in 1..arr.COUNT loop
        DBMS_OUTPUT.PUT_LINE('studentId:' || arr(i).studentId || ' studentName:' || arr(i).studentName);
    end loop;
end;
declare
begin
    sp_param_list_02(arr => StudentInfoArr(StudentInfo('S001','张三'),StudentInfo('S002','李四')));
end;

8、返回多个参数

create or replace procedure sp_return_value(stuInfoList out Sys_Refcursor)
IS
begin
    open stuInfoList for select STUDENT_ID,STUDENT_NAME,SEX from STUDENT;
end;
declare
    stu Sys_Refcursor;
    v_student_id STUDENT.STUDENT_ID%TYPE;
    v_student_name STUDENT.STUDENT_NAME%TYPE;
    v_sex STUDENT.SEX%TYPE;
begin
    SP_RETURN_VALUE(  stu);
    loop
        fetch stu into v_student_id,v_student_name,v_sex;
        exit when stu%notfound;
        DBMS_OUTPUT.PUT_LINE('studentId:' || v_student_id || ' studentName: ' || v_student_name);
    end loop;
 end;

9、程序包 package

1、定义包头

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

create or replace package pkg_huan as
    v_pkg_name varchar2(30) := 'pkg_huan';
    function add(param1 in number, param2 in number) return number;
    procedure sp_pkg_01;
    procedure sp_pkg_02(param1 in varchar2);
end pkg_huan;

2、实现包体

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

create or replace package body  pkg_huan as
    -- 实现function
    function add(param1 in number, param2 in number) return number IS
    begin
        return param1 + param2;
    end;
    -- 实现无参数的存储过程
    procedure sp_pkg_01 as
    begin
        DBMS_OUTPUT.PUT_LINE('package name:' || v_pkg_name || 'procedure name: sp_pkg_01');
    end;
    -- 实现有参数的存储过程
    procedure sp_pkg_02(param1 in varchar2) as
    begin
        DBMS_OUTPUT.PUT_LINE('param1:' || param1);
    end;
end;

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

begin
    -- 调用方法
    DBMS_OUTPUT.PUT_LINE('1+2=' || PKG_HUAN.add(1,2));
    -- 调用无参数的存储过程
    PKG_HUAN.sp_pkg_01();
    -- 调用有参数的存储过程
    PKG_HUAN.sp_pkg_02(12);
end;

以上就是PLSQL一些常用知识点梳理总结的详细内容,更多关于PLSQL常用知识点的资料请关注我们其它相关文章!

(0)

相关推荐

  • PLSQL Developer安装详细步骤及 plsql Developer 14注册码

    目录 一.PL/SQL官网下载 二.PL/SQL工具安装 三.pl/sqlzhongwen 四.验证是否成功 补充:PL/SQLplsqlDeveloper14最新版注册码(亲测可用) 激活成功 一.PL/SQL官网下载 官网下载地址:https://www.allroundautomations.com/registered-plsqldev/ 或者在我的网盘下载 链接: https://pan.baidu.com/s/1sx1W4t658hkjrOACRMXZ3w?pwd=7xr8 提取码:

  • PLSQL Developer13.0.4注册和使用教程详解

    目录 01. PLSQL Developer简介 02. PLSQL Developer下载 03. PLSQL Developer注册 04. PLSQL Developer使用 05. 客户端实例别名配置 06. 附录 01. PLSQL Developer简介 PL/SQL Developer 13是一个集成开发环境,专门用于开发 Oracle 数据库的存储程序单元. 随着时间的推移,我们已经看到越来越多的业务逻辑和应用程序逻辑进入 Oracle Server,因此 PL/SQL 编程已经

  • PLSQLDeveloper登录远程连接Oracle的操作

    远程连接oracle 只用PLSQLDeveloper客户端,不配置tnsnames.ora文件 在PL/SQL中填写: 1.UserName:用户名,如hr 2.Password:密码,如hr123 3.DataBase:远程连接的IP地址:端口号/数据库实例,比如:172.22.97.102:1521/XE (数据库实例名是全局数据库名,有的开发叫数据库服务名) 4.Connect as:Normal 登录本地: localhost:1521/XE 127.0.0.1:1521/XE Sql

  • 教你使用PLSQLDeveloper14连接Oracle11g的详细过程

    目录 一.环境配置 1.安装PLSQLDeveloper14 2.下载并解压Oracle客户端 3.配置window操作系统环境变量 二.工具配置 1.Oracle客户端配置 2.PLSQLDeveloper14配置 3.重启PLSQLDeveloper14客户端 一.环境配置 1.安装PLSQLDeveloper14 https://www.allroundautomations.com/ PLSQL14下载地址: https://www.jb51.net/softs/740737.html

  • PLSQL Developer连接oracle数据库配置教程

    需要安装的工具: 一:Oracle的下载安装: 下载地址: http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html 详细安装此处不做赘余描述,需要注意的是下载好的两文件需解压至同一目录下以及环境变量的路径配置(尽量使用英文字符路径): 二.安装32位Instant Client 下载32位Instant Client(轻量级的客户端),作为本地Oracle环境 (旧版本里64位In

  • jQuery常用知识点总结以及平时封装常用函数

    本文为大家介绍了jQuery中常用知识点及函数,包含许多细节方面的知识,下面我们一起学习一下. jQuery中为我们提供了很多有用的属性,自己总结的一些常用的函数.个人认为在在线排盘开发中会比较常用的,仅供大家学习和参考. 刚开始学习前端的时候开始整理这个文档,现在内容已经逐渐增多.虽然现在看起来,文档里的内容非常简单,但是看着这些内容,好像还依稀记得这一行行代码当时被记录的情景.所以我想把这段回忆保存起来,为刚接触前端的童鞋们提供一个简单的查询的途径,也以此来缅怀我的前端学习之路. ** 此文

  • CodeIgniter常用知识点小结

    本文简单总结了CodeIgniter开发中的常用知识点.分享给大家供大家参考,具体如下: 跳转: $this->load->helper('url'); redirect(); 常量定义: config/constants.php 关于语言文件: 只说我自己的做法 为了统一管理错误信息 决定做一个error_lang.php 在application/language下新建文件夹chinese 新建文件 error_lang.php 在config.php里: $config['languag

  • python常用知识梳理(必看篇)

    接触python已有一段时间了,下面针对python基础知识的使用做一完整梳理: 1)避免'\n'等特殊字符的两种方式: a)利用转义字符'\' b)利用原始字符'r' print r'c:\now' 2)单行注释,使用一个#,如: #hello Python 多行注释,使用三个单引号(或三个双引号),如: '''hello python hello world''' 或 """hello python hello world""" 另外跨越多行

  • js+jquery常用知识点汇总

    一.jquery源码中常见的知识点 1.string,number类型转换的快捷方法 复制代码 代码如下: // @param s为字符串,n为数字 function fn(obj){     //转换为String类型     var s = obj +"";     //转换为number类型     var n = +obj; } 分享一个面试例子: //加会将其后面自动转换成字符串 "64"+4="644" //减会将其自动转换成数字 &

  • javascript面向对象程序设计实践常用知识点总结

    本文实例讲述了javascript面向对象程序设计实践常用知识点.分享给大家供大家参考,具体如下: 实践一:原型中的引用类型的属性是共享的 var Person = function(){}; Person.prototype = { info:{ "name":"Tom" } } var p1 = new Person(); var p2 = new Person(); p1.info.name = '我是p1'; p2.info.name = '我是p2'; c

  • thinkPHP5框架路由常用知识点汇总

    本文实例讲述了thinkPHP5框架路由常用知识点.分享给大家供大家参考,具体如下: 一.路由的模式 普通模式(默认pathinfo,不解析路由) 'url_route_on' => false 混合模式(pathinfo+解析路由) 'url_route_on' => true, 'url_route_must'=> false, 强制模式(必须路由) 'url_route_on' => true, 'url_route_must' => true, 二.动态注册路由 1.

  • 整理CocosCreator常用知识点

    一.场景加载 cc.director.loadScene('场景名称');//场景跳转 cc.director.preloadScene('场景名称');//预加载场景 cc.director.getScene();//获取当前场景 二.查找节点 1,节点查找 node = cc.find("Canvas/bg");//路径访问节点 性能消耗相对较大 this.node.getChildByName('name');//名称获取子节点 性能消耗较小 node.getComponent(

  • Java Dubbo框架知识点梳理

    1.Dubbo是什么 Dubbo 是一个分布式.高性能.透明化的 RPC 服务框架,提供服务自动注册.自动发现等高效服务治理方案, 可以和 Spring 框架无缝集成. RPC 指的是远程调用协议,也就是说两个服务器交互数据. 2.Dubbo的由来 互联网的快速发展,Web应用程序的规模不断扩大,一般会经历如下四个发展阶段. 单一应用架构:当网站流量很小时,只需一个应用,将所有功能都部署在一起即可. 垂直应用架构:当访问量逐渐增大,单一应用按照有业务线拆成多个应用,以提升效率.此时,用于加速前端

  • Vue3.x项目开发的一些常用知识点总结

    目录 一.定义组件属性 二.formatter简写 三.子父组件通信 四.监听组件属性变化 五.自定义指令 总结 PS:以下知识点都是基于 vue3.x + typescript + element-plus + setup语法糖 使用的. 一.定义组件属性 const props = defineProps({ visible: { type: Boolean, default: false } }) console.log(props.visible) [warning] 注意:define

  • Android 内存优化知识点梳理总结

    目录 RAM 和 ROM 常见内存问题 内存溢出 内存泄漏 常见内存泄漏场景 静态变量或单例持有对象 非静态内部类的实例生命周期比外部类更长导致的内存泄漏 Handler 导致的内存泄漏 postDelayed 导致的内存泄漏 View 的生命周期大于 Activity 时导致的内存泄漏 集合中的对象未释放导致内存泄漏 WebView 导致的内存泄漏 内存抖动 解决方案 其他优化点 App 内存过低时主动清理 前言: Android 操作系统给每个进程都会分配指定额度的内存空间,App 使用内存

随机推荐