PLSQL一些常用知識點梳理總結
更新時間：2023年05月14日 14:27:57 作者：huan1993
這篇文章主要為大家介紹了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)計總共有多少個學生，并進行if判斷。
declare
-- 聲明一個變量，記錄有多少個學生
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('當前學生數(shù)為: [' || v_student_count || ']>3');
elsif v_student_count >=2 then
DBMS_OUTPUT.PUT_LINE('當前學生數(shù)為: [' || v_student_count || '] in [2,3]');
else
DBMS_OUTPUT.PUT_LINE('當前學生數(shù)為: [' || 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;
-- 執(zhí)行if判斷
case when v_student_count > 3 then
DBMS_OUTPUT.PUT_LINE('當前學生數(shù)為: [' || v_student_count || ']>3');
when v_student_count >=2 then
DBMS_OUTPUT.PUT_LINE('當前學生數(shù)為: [' || v_student_count || '] in [2,3]');
else
DBMS_OUTPUT.PUT_LINE('當前學生數(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è)務邏輯（此句如果移動到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、帶參數(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è)務邏輯（此句如果移動到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、執(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;
-- 調用
begin
SP_PRINT_ALL_STUDENT();
end;2、有輸入輸出參數(shù)的存儲過程
-- 有參數(shù)的存儲過程
create or replace procedure sp_find_student(/** 輸入參數(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('測試異常');
-- 產生異常
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條數(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;
-- 關閉游標
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ù)組對象轉換成表使用
-- 創(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ù)組轉換成使用
select count(*) into v_student_count from table(cast(arr AS StudentInfoArr))
where studentId like 'S%';
DBMS_OUTPUT.PUT_LINE('傳遞進來學生學號以S開頭的學生有: ' || 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、調用包中的方法或存儲過程
begin
-- 調用方法
DBMS_OUTPUT.PUT_LINE('1+2=' || PKG_HUAN.add(1,2));
-- 調用無參數(shù)的存儲過程
PKG_HUAN.sp_pkg_01();
-- 調用有參數(shù)的存儲過程
PKG_HUAN.sp_pkg_02(12);
end;以上就是PLSQL一些常用知識點梳理總結的詳細內容，更多關于PLSQL常用知識點的資料請關注腳本之家其它相關文章！
相關文章
解決Windows 7下安裝Oracle 11g相關問題的方法
本文將為大家介紹Windows 7下安裝Oracle 11g方面的有關問題解決方案。希望通過本文，能讓大家對11g這款產品有更多的認識，需要的朋友可以參考下2015-08-08
這篇文章主要介紹了oracle創(chuàng)建用戶過程詳解,本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內容,需要的朋友可以參考下2021-08-08
這篇文章主要介紹了rman備份腳本和rman增量備份腳本,需要的朋友可以參考下2014-03-03
這篇文章介紹了Oracle中PL/SQL的塊與表達式，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05
這篇文章主要介紹了SQLPlus命令操作用法詳解,本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內容,需要的朋友可以參考下2021-08-08
Oracle存儲過程里操作BLOB的字節(jié)數(shù)據(jù)的辦法
該篇文章介紹了如何在Oracle存儲過程中操作BLOB的字節(jié)數(shù)據(jù),作者研究了如何獲取BLOB的字節(jié)長度、如何使用DBMS_LOB包進行BLOB操作、如何進行字節(jié)級操作以及如何使用UTL_RAW包,感興趣的朋友一起看看吧2025-03-03
Dual表是Oracle提供的最小的工作表（其實是一種虛擬表），是sys用戶下的一張內部表，只有一行一列（一列：DUMMY，一列：DUMMY，其數(shù)據(jù)類型為：VARCHAR2(1)），接下來通過本文給大家分享Oracle中的Dual系統(tǒng)表,需要的朋友可以參考下2022-09-09
Oracle Connect to Idle Instance解決方法
本文將介紹Oracle如何解決Connect to Idle Instance問題,需要了解的朋友可以參考下2012-11-11
這篇文章主要介紹了Oracle批量執(zhí)行sql語句之禁用所有表的外鍵的相關資料,需要的朋友可以參考下2016-01-01
解決ORA-12170:TNS connect timeout occurred問題
這篇文章主要介紹了解決ORA-12170:TNS connect timeout occurred問題,本文給大家介紹的非常詳細，具有一定的參考借鑒價值，需要的朋友可以參考下2019-12-12