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';
    -- 獲取一行數(shù)據(jù) ( 此處需要查詢出所有的字段树埠，否則可能報錯 )
    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 || ' 男生人數(shù):' || v_man_sex || ' 地址:' || v_addr );
end;

3糠馆、if 判斷

統(tǒng)計總共有多少個學(xué)生，并進行if判斷怎憋。

declare
    -- 聲明一個變量又碌，記錄有多少個學(xué)生
    v_student_count number;
begin
    -- 給 v_student_count 變量賦值
    select count(*) into v_student_count from student;

    -- 執(zhí)行if判斷

    if v_student_count > 3 then
        DBMS_OUTPUT.PUT_LINE('當前學(xué)生數(shù)為: [' || v_student_count || ']>3');
    elsif v_student_count >=2 then
        DBMS_OUTPUT.PUT_LINE('當前學(xué)生數(shù)為: [' || v_student_count || '] in [2,3]');
    else
        DBMS_OUTPUT.PUT_LINE('當前學(xué)生數(shù)為: [' || v_student_count || ']<2');
    end if;
end;

4、case

-- case
declare
    -- 聲明一個變量绊袋，記錄有多少個學(xué)生
    v_student_count number;
begin
    -- 給 v_student_count 變量賦值
    select count(*) into v_student_count from student;

    -- 執(zhí)行if判斷

    case when v_student_count > 3 then
        DBMS_OUTPUT.PUT_LINE('當前學(xué)生數(shù)為: [' || v_student_count || ']>3');
    when v_student_count >=2 then
        DBMS_OUTPUT.PUT_LINE('當前學(xué)生數(shù)為: [' || v_student_count || '] in [2,3]');
    else
        DBMS_OUTPUT.PUT_LINE('當前學(xué)生數(shù)為: [' || v_student_count || ']<2');
    end case;
end;

5毕匀、循環(huán)

輸出1到100

1、loop 循環(huán)

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 循環(huán)

-- while 循環(huán)
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循環(huán)

-- for 循環(huán)
declare
    -- 定義一個變量
    v_count number;
begin
    for v_count in 1..100 loop
        DBMS_OUTPUT.PUT_LINE('當前 count = ' || v_count);
    end loop;
end;

6、游標

1展姐、無參數(shù)的游標

-- 游標
declare
    -- 聲明一個游標
    cursor cur_student is select student_id,student_name,sex from student;
    -- 聲明變量
    row_cur_student cur_student%rowtype;
begin
    -- 打開游標
    open cur_student;

    -- 遍歷數(shù)據(jù)
    loop
        -- 獲取一行數(shù)據(jù)
        fetch cur_student into row_cur_student;
        -- 退出
        exit when cur_student%NOTFOUND;
        -- 執(zhí)行業(yè)務(wù)邏輯（此句如果移動到exit when上方躁垛，則可能會多打印一句）
        DBMS_OUTPUT.PUT_LINE('studentId:' || row_cur_student.STUDENT_ID || ' studentName:' || row_cur_student.STUDENT_NAME);

    end loop;

    -- 關(guān)閉游標
    close cur_student;
end;

2剖毯、帶參數(shù)的游標

declare
    -- 聲明一個游標, 需要傳遞v_student_id參數(shù)
    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
    -- 打開游標
    --參數(shù)傳遞方式一： open cur_student('S001');

    -- 參數(shù)傳遞方式二：
    select 'S001' into v_query_student_id from dual;
    open cur_student(v_query_student_id);

    -- 遍歷數(shù)據(jù)
    loop
        -- 獲取一行數(shù)據(jù)
        fetch cur_student into row_cur_student;
        -- 退出
        exit when cur_student%NOTFOUND;
        -- 執(zhí)行業(yè)務(wù)邏輯（此句如果移動到exit when上方教馆，則可能會多打印一句）
        DBMS_OUTPUT.PUT_LINE('studentId:' || row_cur_student.STUDENT_ID || ' studentName:' || row_cur_student.STUDENT_NAME);

    end loop;

    -- 關(guān)閉游標
    close cur_student;
end;

7逊谋、執(zhí)行ddl dml

需要放到 execute immediate中執(zhí)行，否則會報錯土铺。

declare
    v_table_name varchar2(20) := 'student_bak';
    -- 拼接一個動態(tài)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)';

    -- 帶變量的執(zhí)行
    v_sql := 'drop table ' || v_table_name;
    execute immediate v_sql;

end;

8胶滋、存儲過程

1、無參數(shù)的存儲過程

-- 無參數(shù)的存儲過程
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
    -- 循環(huán)游標
    for row_student in c_all_student loop
        DBMS_OUTPUT.PUT_LINE(row_student.STUDENT_ID || ' ' || row_student.STUDENT_NAME);
    end loop;
end;
-- 調(diào)用
begin
    SP_PRINT_ALL_STUDENT();
end;

2舒憾、有輸入輸出參數(shù)的存儲過程

-- 有參數(shù)的存儲過程
create or replace procedure sp_find_student(/** 輸入?yún)?shù) */ i_student_id in student.student_id%TYPE,
                                           /** 輸出參數(shù) */ 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 中查詢出來的數(shù)據(jù)丁溅，通過 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('測試異常');

    -- 產(chǎn)生異常
    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 將所獲取的結(jié)果集一次性綁定到記錄變量 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條數(shù)據(jù)插入到 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 更新數(shù)據(jù), 可以將多個dml語句批量發(fā)送給SQL引擎掘殴，提高執(zhí)行效率赚瘦。
            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;

    -- 關(guān)閉游標
    close cur_student;
end;

begin
    sp_bulk_collect_02;
end;

6、接收數(shù)組參數(shù)

-- 創(chuàng)建StudentIdList數(shù)組的長度是4奏寨，每一項最多存20個字符
create or replace type StudentIdList as varray(4) of varchar2(20);

-- 創(chuàng)建存儲過程起意，接收數(shù)組參數(shù)
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、接收數(shù)組對象病瞳，并將數(shù)組對象轉(zhuǎn)換成表使用

-- 創(chuàng)建數(shù)據(jù)庫對象
create or replace type StudentInfo is object(
    studentId varchar2(64),
    studentName varchar2(64)
);
-- 創(chuàng)建數(shù)組對象
create or replace type StudentInfoArr as table of StudentInfo;

-- 創(chuàng)建存儲過程
create or replace procedure sp_param_list_02(arr in StudentInfoArr)
is
    -- 聲明一個變量揽咕，記錄傳遞進來的arr的數(shù)量
    v_student_count number := 0;
begin
    -- 傳遞進來的數(shù)組轉(zhuǎn)換成使用
    select count(*) into v_student_count from table(cast(arr AS StudentInfoArr))
    where studentId like 'S%';
    DBMS_OUTPUT.PUT_LINE('傳遞進來學(xué)生學(xué)號以S開頭的學(xué)生有: ' || v_student_count || '個');

    -- 輸出列表參數(shù)
    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、返回多個參數(shù)

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逗柴、實現(xiàn)包體

包體可以簡單的理解java中的實現(xiàn)接口的類蛹头。

create or replace package body  pkg_huan as
    -- 實現(xiàn)function
    function add(param1 in number, param2 in number) return number IS
    begin
        return param1 + param2;
    end;
    -- 實現(xiàn)無參數(shù)的存儲過程
    procedure sp_pkg_01 as
    begin
        DBMS_OUTPUT.PUT_LINE('package name:' || v_pkg_name || 'procedure name: sp_pkg_01');
    end;
    -- 實現(xiàn)有參數(shù)的存儲過程
    procedure sp_pkg_02(param1 in varchar2) as
    begin
        DBMS_OUTPUT.PUT_LINE('param1:' || param1);
    end;
end;

3、調(diào)用包中的方法或存儲過程

begin
    -- 調(diào)用方法
    DBMS_OUTPUT.PUT_LINE('1+2=' || PKG_HUAN.add(1,2));
    -- 調(diào)用無參數(shù)的存儲過程
    PKG_HUAN.sp_pkg_01();
    -- 調(diào)用有參數(shù)的存儲過程
    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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市于购，隨后出現(xiàn)的幾起案子袍睡，更是在濱河造成了極大的恐慌，老刑警劉巖肋僧，帶你破解...
沈念sama閱讀 218,755評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件斑胜，死亡現(xiàn)場離奇詭異，居然都是意外死亡嫌吠，警方通過查閱死者的電腦和手機止潘，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,305評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來辫诅，“玉大人凭戴，你說我怎么就攤上這事】话” “怎么了么夫？”我有些...
開封第一講書人閱讀 165,138評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長肤视。經(jīng)常有香客問我档痪，道長，這世上最難降的妖魔是什么邢滑？我笑而不...
開封第一講書人閱讀 58,791評論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任腐螟，我火速辦了婚禮，結(jié)果婚禮上困后，老公的妹妹穿的比我還像新娘乐纸。我一直安慰自己，他們只是感情好操灿，可當我...
茶點故事閱讀 67,794評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布锯仪。她就那樣靜靜地躺著，像睡著了一般趾盐。火紅的嫁衣襯著肌膚如雪庶喜。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,631評論 1贊 305
城市分裂傳說
那天救鲤，我揣著相機與錄音久窟，去河邊找鬼。笑死本缠，一個胖子當著我的面吹牛斥扛，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播，決...
沈念sama閱讀 40,362評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼稀颁，長吁一口氣：“原來是場噩夢啊……” “哼芬失！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起匾灶，我...
開封第一講書人閱讀 39,264評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤棱烂，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后阶女，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體颊糜，經(jīng)...
沈念sama閱讀 45,724評論 1贊 315
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,900評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年秃踩，在試婚紗的時候發(fā)現(xiàn)自己被綠了衬鱼。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點故事閱讀 40,040評論 1贊 350
活死人
序言：一個原本活蹦亂跳的男人離奇死亡憔杨，死狀恐怖鸟赫，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情消别，我是刑警寧澤惯疙，帶...
沈念sama閱讀 35,742評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站妖啥，受9級特大地震影響霉颠，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜荆虱，卻給世界環(huán)境...
茶點故事閱讀 41,364評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一蒿偎、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧怀读，春花似錦诉位、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,944評論 0贊 22
一樁弒父案苍糠，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至啤誊，卻和暖如春岳瞭，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背蚊锹。一陣腳步聲響...
開封第一講書人閱讀 33,060評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工瞳筏，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人牡昆。一個月前我還...
沈念sama閱讀 48,247評論 3贊 371
代替公主和親
正文我出身青樓姚炕，卻偏偏與公主長得像，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子柱宦，可洞房花燭夜當晚...
茶點故事閱讀 44,979評論 2贊 355