PLSQL一些常用的知识点 天天新动态

(资料图片仅供参考)

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

-- casedeclare    -- 声明一个变量，记录有多少个学生    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_studentis    -- 声明一个游标    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)ISbegin    -- 如果 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_errorIS    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 & record1、select into 中使用 bulk into & record

create or replace procedure sp_bulk_collect_01IS    -- 定义一个记录类型    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 collectcreate or replace procedure sp_bulk_collect_02IS    -- 定义一个游标    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)isbegin    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;declarebegin    sp_param_list_02(arr => StudentInfoArr(StudentInfo("S001","张三"),StudentInfo("S002","李四")));end;

8、返回多个参数

create or replace procedure sp_return_value(stuInfoList out Sys_Refcursor)ISbegin    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、程序包 package1、定义包头

包头可以简单的理解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