PLSQL一些常用知識點梳理總結(jié)-其他數(shù)據(jù)庫-數(shù)據(jù)庫-碩編程

PLSQL一些常用知識點梳理總結(jié)

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表中的一整行字段, 也可以是游標(biāo)中的一整行
  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é)生，并進(jìn)行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 &gt; 3 then
      DBMS_OUTPUT.PUT_LINE('當(dāng)前學(xué)生數(shù)為: [' || v_student_count || ']&gt;3');
  elsif v_student_count &gt;=2 then
      DBMS_OUTPUT.PUT_LINE('當(dāng)前學(xué)生數(shù)為: [' || v_student_count || '] in [2,3]');
  else
      DBMS_OUTPUT.PUT_LINE('當(dāng)前學(xué)生數(shù)為: [' || v_student_count || ']&lt;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 &gt; 3 then
      DBMS_OUTPUT.PUT_LINE('當(dāng)前學(xué)生數(shù)為: [' || v_student_count || ']&gt;3');
  when v_student_count &gt;=2 then
      DBMS_OUTPUT.PUT_LINE('當(dāng)前學(xué)生數(shù)為: [' || v_student_count || '] in [2,3]');
  else
      DBMS_OUTPUT.PUT_LINE('當(dāng)前學(xué)生數(shù)為: [' || v_student_count || ']&lt;2');
  end case;
end;

5、循環(huán)

輸出1到100

1、loop 循環(huán)

declare
  -- 定義一個變量并賦值
  v_count number := 1;
begin
  loop
      -- 提出條件
      exit when v_count &gt; 100;
      DBMS_OUTPUT.PUT_LINE('當(dāng)前 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 &lt;= 100 loop
      DBMS_OUTPUT.PUT_LINE('當(dāng)前 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('當(dāng)前 count = ' || v_count);
  end loop;
end;

6、游標(biāo)

1、無參數(shù)的游標(biāo)

-- 游標(biāo)
declare
  -- 聲明一個游標(biāo)
  cursor cur_student is select student_id,student_name,sex from student;
  -- 聲明變量
  row_cur_student cur_student%rowtype;
begin
  -- 打開游標(biāo)
  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)閉游標(biāo)
  close cur_student;
end;

2、帶參數(shù)的游標(biāo)

declare
  -- 聲明一個游標(biāo), 需要傳遞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;
  -- 此變量通過查詢獲取值，然后帶到游標(biāo)中
  v_query_student_id student.student_id%TYPE;
begin
  -- 打開游標(biāo)
  --參數(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)閉游標(biāo)
  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
  -- 聲明一個游標(biāo)
  cursor c_all_student is select student_id,student_name from student;
  -- 聲明一個變量
  row_student c_all_student%rowtype;
begin
  -- 循環(huán)游標(biāo)
  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
            -- 異常處理方法，可以是打印錯誤，然后進(jìn)行回滾等操作，下面操作一樣，看自己情況決定
            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
  -- 定義一個游標(biāo)
  cursor cur_student is select student_id,student_name,sex,create_time from student;
  -- 定義基于游標(biāo)的嵌套表
  type nested_student_info is table of cur_student%rowtype;
  -- 聲明變量
  student_list nested_student_info;
begin
  -- 打開游標(biāo)
  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)閉游標(biāo)
  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
  -- 聲明一個變量，記錄傳遞進(jìn)來的arr的數(shù)量
  v_student_count number := 0;
begin
  -- 傳遞進(jìn)來的數(shù)組轉(zhuǎn)換成使用
  select count(*) into v_student_count from table(cast(arr AS StudentInfoArr))
  where studentId like 'S%';
  DBMS_OUTPUT.PUT_LINE('傳遞進(jìn)來學(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 =&gt; 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;

以上就是PLSQL一些常用知識點梳理總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于PLSQL常用知識點的資料請關(guān)注碩編程其它相關(guān)文章！

下一節(jié)：Oracle中的table()函數(shù)使用

Oracle數(shù)據(jù)庫

相關(guān)文章

其他數(shù)據(jù)庫

黄色电影一区二区,韩国少妇自慰A片免费看,精品人妻少妇一级毛片免费蜜桃AV按摩师 ,超碰 香蕉

PLSQL一些常用知識點梳理總結(jié)

1、背景

2、變量的聲明

3、if 判斷

4、case

5、循環(huán)

1、loop 循環(huán)

2、while 循環(huán)

3、for循環(huán)