> 科技资讯 > PLSQL一些常用的知识点

PLSQL一些常用的知识点

科技资讯 投稿 7600℃ 0 评论

PLSQL一些常用的知识点

1、背景

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
            
            

                编程笔记
                »
                PLSQL一些常用的知识点
            

            

                
                    赞同 (36)
                
                or
                
                    
                    分享 (0)
游客 发表我的评论   换个身份
取消评论

表情
(0)个小伙伴在吐槽