oracle學習筆記（十五） PL/SQL語法結構以及使用

one發表於2019-05-26
Oracle筆記SQL

PL/SQL

簡介

  • PL/SQL 是過程語言(Procedural Language)與結構化查詢語言(SQL)結合而成的程式語言。
  • PL/SQL 是對 SQL 的擴充套件。
  • 支援多種資料型別，如大物件和集合型別，可使用條件和迴圈等控制結構。
  • 可用於建立儲存過程、函式、觸發器和程式包，給SQL語句的執行新增程式邏輯。

作用：
如果出現需要插入1000條資料，這些資料帶有某種規律性，就可以通過PL/SQL進行增加資料的操作
優點：

  1. 支援 SQL，在 PL/SQL 中可以使用：
    • 資料操縱命令：DML，DDL(需要動態執行)
    • 事務控制命令：TCL
    • 遊標控制, 下一章學習
    • SQL函式和SQL運算子
  2. 與 SQL 緊密整合，簡化資料處理。
    • 支援所有 SQL 資料型別
    • 支援 NULL 值
    • 支援 %TYPE 和 %ROWTYPE
      • %TYPE: 引用欄位型別,如：employee.ename%TYPE
      • %ROWTYPE：引用表的行型別， 如: employee%ROWTYPE
  3. 支援物件導向程式設計 (OOP)
  4. 可移植性，可執行在任何作業系統和平臺上的Oralce 資料庫
  5. 更佳的效能，PL/SQL 經過編譯執行

常用資料型別

  • 數值
    number, numeric, decimal, float, int, integer, real
  • 字串
    varchar,varchar2,string(PLSQL特有的)
  • 布林型別boolean
  • 日期常用型別
    date, timestamp
  • 屬性型別
    %type：引用某一個變數的型別或都是某個列名（欄位）的型別。employee.ename%type;
    %rowtype：表示可儲存某錶行的記錄型別。Employee%rowtype;

變數宣告以及賦值

--賦值號為 :=
$變數名$ [CONSTANT] $datatype$ [NOT NULL] [:= value];--賦值
$變數名$ [CONSTANT] $datatype$ [NOT NULL] [DEFAULT expr];--預設值

age int;--宣告一個整型變數age,
age int := 18;--宣告age並賦值
age int default 18;--預設age為18
age constant int :=18;--宣告整型常量age並賦值為18

--宣告一個與employee.ename相同型別的變數myname
myname employee.ename%type;

--宣告一個變數，型別為表的行型別（相當於表中每條資料對應的實體類）
rec_emp employee%rowtype;--employee表中的資料有多少列，rec_emp也有多少列

--例子，輸入員工編號，輸出該員工資訊
declare
  v_empno employee.empno%type;
  rec_emp employee%rowtype;
begin
  v_empno := &請輸入員工編號;
  select * into rec_emp from employee where empno=v_empno;
  --使用 .列名 得到列的值
  dbms_output.put_line(rec_emp.empno||','
                       ||rec_emp.ename||','
                       ||rec_emp.sal||','
                       ||rec_emp.job);
end;
/

--自定義一個新的資料型別，然後再用這個去宣告變數，使用的話也是 .列名
  type TYPE_EMP_REC is record(
      empno employee.empno%type,
      ename employee.ename%type,
      sal employee.sal%type,
      job employee.job%type
  );

--賦值還可以通過select into語句
select ename into myname from employee where empno=7879;
--多個變數，注意要兩邊要對應
select ename,sal into myname,mysal from employee where empno =7879;

基本單元框架

[DECLARE
   --宣告變數、常量、遊標、自定義記錄型別等。
]
BEGIN
    --執行部分。
   [EXCEPTION 
       --異常處理部分。
   ]
END;
/ --立即執行

輸出輸入語句

輸出

oracle內建有一個dbms_output物件，負責輸出
dbms_output物件有兩個輸出方法，一個為put，另外一個外put_line
引數可以接收數值型

--此方法不能立即輸出，需要等待資料緩衝區滿了之後或者是重新整理資料緩衝區之後才會輸出
dbms_output.put('hello world');
--下面兩個任選一個即可讓上面的hello world立即輸出
dbms_output.put_line('');
dbms_output.new_line;

dbms_output.put_line('hello world')；--立即輸出

如果是在命令列視窗的話，沒有見到輸出語句，需要設定開啟輸出

set serveroutput on;

輸入

--&號在PL/SQL中作為一個替代變數，用於在執行中輸入值。如果輸入的是字元型別那把&號放在兩個單引號中，
--若是數字則不用單引號括起來
v_ename := '&請輸入姓名：';
v_sal := &請輸入工資;

輸入輸出例子

declare
    v_name varchar2(10) := '&輸入名字';
    v_age int := &輸入年齡;
begin
    dbms_out.put_line('我是'||v_name||'年齡為'||v_age);
    --使用||連線字串
end;
/

判斷與分支語句

if判斷

--if判斷
if 表示式 then
end if

if-else判斷

--if-else判斷
if 表示式 then 
--程式碼
else
--程式碼
end if

if-else if判斷

--if-else if判斷
if 表示式 then
    else if 表示式 then
    --程式碼
    else
    --程式碼
    end if
end if

case分支語句

--1.判斷是否滿足表示式
case 
    when 表示式 then
    --程式碼
    when 表示式 then
    --程式碼
    else
    --程式碼
end case;   

--2.判斷是否等於某個數值
case 
    when 數值 then
    --程式碼
    when 數值 then
    --程式碼
    else
    --程式碼
end case;

數值不能能為字串型別

迴圈語句for while loop

loop迴圈

--無條件迴圈
loop
    --程式碼以及初始化相關
    --滿足條件退出當前迴圈
    IF 條件 THEN 
       EXIT;
    END IF;
    --或者使用 EXIT WHEN 條件
end loop;

while迴圈

--有條件迴圈
--滿足條件進入迴圈
while 條件 LOOP
    --初始化數值;
    --程式碼
    EXIT WHEN 條件; --滿足條件退出迴圈
    --程式碼
end loop;

for迴圈

--oracle會自動建立計數器變數，也就是我們的i
--除了i，也可以起其他的名字

--相當於for(i=1;i<=10;i++)
for i in 1..10 loop
    --statement;
end loop;

--相當於for(i=10;i>=1;i--)     
for i in reverse 1..10 loop
    --statement;
end loop;

-使用變數作為for迴圈的範圍
declare
  v_min integer default 1;
  v_max integer default 20;
begin
  for i in reverse v_min..v_max loop
    dbms_output.put(i||',');
  end loop;
  dbms_output.new_line;
end;
/

迴圈結構練習

--遍歷輸出某個部門的全部員工資訊
declare
  v_deptno integer := &請輸入部門號;
begin
  for emp in (select e.* from employee e where deptno=v_deptno) loop
    dbms_output.put_line(||emp.empno||','||emp.ename||','||emp.job||','||emp.sal);
  end loop;
end;
/

--for列印99乘法表
begin
  --如果不在rownum,可以嗎？
  for num1 in 1..9 loop
    for num2 in 1..num1 loop
      dbms_output.put(num2||'*'||num1||'='||num1*num2||' ');
      end loop;
      dbms_output.new_line;
  end loop;
end;
/

--loop列印99乘法表
declare
  num1 int := 1;
  num2 int;
begin
  loop
    num2 := 1;
    loop 
      dbms_output.put(num2||'*'||num1||'='||num1*num2||' ');
      exit when num1=num2;
      num2 := num2+1;
    end loop; 
    dbms_output.new_line;
    exit when num1=9; 
    num1 := num1+1;
  end loop;
end;
/

--while列印99乘法表
declare
  num1 int := 1;
  num2 int;
begin
  while num1 <= 9 loop
    num2 := 1;
    while num2 <= num1 loop
      dbms_output.put(num2||'*'||num1||'='||num1*num2||' ');
      exit when num2=num1;
      num2 :=num2+1;
      end loop;
      dbms_output.new_line;
      exit when num1=9;
      num1 := num1+1;
  end loop;
end;
/

相關文章