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

和通数据库htsjk.Com2023-07-20 03:48 来源:未知阅读:15039 评论 114 热度4

标签：plsql 知识点梳理总结

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

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 &gt; 3 then
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']&gt;3');
    elsif v_student_count &gt;=2 then
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || '] in [2,3]');
    else
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']&lt;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 &gt; 3 then
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']&gt;3');
    when v_student_count &gt;=2 then
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || '] in [2,3]');
    else
        DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']&lt;2');
    end case;
end;

5、循环

输出1到100

1、loop 循环

declare
    -- 定义一个变量并赋值
    v_count number := 1;
begin
    loop
        -- 提出条件
        exit when v_count &gt; 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 &lt;= 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 =&gt; 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常用知识点的资料请关注PHP之友其它相关文章！

您可能感兴趣的文章:

PLSQL Developer安装详细步骤及 plsql Developer 14注册码
PLSQL Developer13.0.4最新注册码和使用教程详解
教你使用PLSQLDeveloper14连接Oracle11g的详细过程
PLSQLDeveloper登录远程连接Oracle的操作
PLSQL Developer连接oracle数据库配置教程

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

返回首页

评论暂时关闭

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、调用包中的方法或存储过程