PLSQL一些常用的知识点

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

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;

10、参考链接

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

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
返回列表

上一篇：MySql的数据存储之B+树（浅谈）

下一篇：用go设计开发一个自己的轻量级登录库/框架吧(业务篇)

“PLSQL一些常用的知识点” 的相关文章

【大唐杯备考】——5G系统勘察设计（学习笔记）1年前 (2023-02-02)
FineReport使用1年前 (2023-02-02)
大数据分析新玩法之Kusto宝典1年前 (2023-02-02)
Spring Cloud Alibaba 2022.0.0.0 版本发布啦！1年前 (2023-02-02)
P1005 [NOIP2007 提高组] 矩阵取数游戏1年前 (2023-02-02)
学习笔记——Mybatis逆向工程MBG；MyBatis逆向工程MBG使用步骤1年前 (2023-02-02)
数据库实践丨使用MTK迁移Mysql源库后主键自增列导致数据无法插入问题1年前 (2023-02-02)
【双十一特辑】爱心代码（程序员的浪漫）-李峋1年前 (2023-02-02)
Redis数据库 ---- 五种数据类型常用命令汇总_redis unlike key1年前 (2023-02-02)
JavaScript 实现继承1年前 (2023-02-02)

阿里云国际版