PLSQL程式設計介紹:
将一些資料的處理過程放在資料庫上面,可以避免因為網絡瓶頸造成的一些時間上的消耗。
可有利于系統的移植,大部分的資料處理和分析可以共享。
過程化程式設計語言,用來編寫包含SQL語句的程式。可以向資料庫應用程式中加入業務邏輯處理功能。
pl/sql結構
在PL/SQL中隻能用 SQL語句中的DML部分,不能使用DDL部分,如果要在PL/SQL中使用DDL(如CREATE TABLE等)的話,隻能以動态的方式使用。
PL/SQL程式由塊結構組成,每一個塊都包含有PL/SQL和SQL語句
文法結構:
[DECLARE
-- 程式的聲明部分,例如,定義變量
]
BEGIN
-- 程式的執行體,具體的操作代碼
[EXCEPTION
-- 異常的處理
]
END ;
常量的指派文法:
varName constant varType := varValue;
變量的幾種指派方式:
1: varName varType := varValue;
2: select into
3: varName user.table.column%type;
4: varCollecton user.table%type;
--1.使用select into 為變量指派
declare
v_empno number; --員工編号。
begin
--将emp表中SMITH員工的編号指派給v_empno并輸出編号的值
select empno into v_empno from scott.emp where ename='SMITH';
dbms_output.put_line('v_empno 等于' || v_empno); --輸出v_empno的值
end;
--2.求圓的面積(常量的使用)
declare
pi constant number :=3.14; --圓周率長值(注:pi的值為常量)
r number default 3; --圓的半徑預設值3
area number; --面積。
begin
area:=pi*r*r; --計算面積
dbms_output.put_line(area); --輸出圓的面積
end;
--3.求圓的面積(變量的使用)當PI等于另外一個值的時候,使用變量求出圓的面積(使用:= 方式給變量指派)
declare
pi number :=3.14; --圓周率長值
r number default 3; --圓的半徑預設值3
area number; --面積。
begin
pi :=45; -- 值可以改變
area:=pi*r*r; --計算面積
dbms_output.put_line(area); --輸出圓的面積
end;
複合資料類型的使用:
%TYPE: 用于取得表、視圖、或遊标中的字段類型
%ROWTYPE: 用于取得表、視圖或遊标的行類型
--4.聲明一個變量,變量的類型為scott.emp表的empno字段類型,然後在控制台輸出
--聲明一個變量,變量的類型為scott.emp表的整行的複合類型,然後在控制台輸出
-- 聲明一個變量,變量的類型為scott.emp表的empno字段類型
declare
id_num scott.emp.empno%type;
begin
select empno into id_num from scott.emp where ename='SMITH'; -- 為id_num指派
dbms_output.put_line(id_num); -- 輸出id_num中的值
end;
-- 聲明一個變量,變量的類型為scott.emp表的整行的複合類型,然後在控制台輸出
declare
emp_info scott.emp%rowtype;
begin
select * into emp_info from scott.emp where empno=7369;
-- 輸出emp_info中的值
dbms_output.put_line(emp_info.empno ||',' || emp_info.ename);
end;
%type,%rowtype總結:
不需要知道被引用的表列或行的具體類型
如果被引用對象的資料類型發生改變,PL/SQL 變量的資料類型也随之改變
文法:
一、
IF <條件1> THEN
語句
[ELSIF <條件2> THEN
語句
ELSIF <條件n> THEN
語句]
[ELSE
語句]
END IF;
二、
CASE <變量>
WHEN <表達式1> THEN 語句1;
WHEN <表達式2> THEN 語句2;
...
WHEN <表達式n> THEN 語句n;
ELSE 語句;
END CASE;
1. 如果某個學生的Java成績大于等于90分則列印獎勵IPone6一個,否則列印繼續努力
declare
java_score int;
begin
java_score:= '&請輸入學生的Java成績';
if java_score > 90 then
dbms_output.put_line('獎勵IPone6一個');
else
dbms_output.put_line('繼續努力');
end if;
end;
2. 如果某個學生的Java成績大于等于90分,并且C語言成績大于等于80分則獎勵蘋果電腦一台,
否則列印繼續努力
declare
java_score int; -- Java成績
c_score int; -- c語言成績
begin
java_score:= '&請輸入學生的Java成績';
c_score:= '&請輸入學生的C語言成績';
if java_score >= 90 and c_score >= 80 then
dbms_output.put_line('獎勵蘋果電腦一台');
else
dbms_output.put_line('繼續努力');
end if;
end;
3.某學生的分數>=90,則該學生成績為“A”;如果該學生的分數在90-80之間,則該學生成績為“B”;
如果該學生的分數在80-70之間,則該學生成績為“C”;如果該學生的分數在70-60之間,則該學生成績的分數在60以下,則該學生成績為“不及格”。
declare
score integer;
grade char;
begin
score:='&請輸入學生成績'; --使用&符号可以接收使用者的輸入資料
if score >=90 then
grade:='A';
elsif score >=80 then
grade:='B';
elsif score >=70 then
grade:='C';
elsif score >=60 then
grade:='D';
else
grade:='F';
end if;
dbms_output.put_line('Your grade is '||grade);
end;
4.根據月份的值判斷該月份所屬季節
declare
v_month number;
v_season varchar2(30);
begin
v_month:='&請輸入月份'; --使用&符号可以接收使用者的輸入資料
if v_month = 12 or v_month=1 or v_month = 2 then
v_season:= '冬季';
elsif v_month = 3 or v_month=4 or v_month = 5 then
v_season:= '春季';
elsif v_month = 6 or v_month=7 or v_month = 8 then
v_season:= '夏季';
elsif v_month = 9 or v_month=10 or v_month = 11 then
v_season:= '秋季';
else
v_season:='月份錯誤';
end if;
dbms_output.put_line(v_month || '月份是'||v_season);
end;
5.某學生的分數>=90,則該學生成績為“A”;如果該學生的分數在90-80之間,則該學生成績為“B”;
如果該學生的分數在80-70之間,則該學生成績為“C”;如果該學生的分數在70-60之間,則該學生成績
為“D”;如果該學生的分數在60以下,則該學生成績為“不及格”。(使用case 的方式來實作)
declare
score int;
v_level varchar(2); --成績等級
begin
score:='&請輸入成績';
case
when score >= 90 then v_level:='A';
when score >= 80 then v_level:='B';
when score >= 60 then v_level:='C';
else v_level:= 'D';
end case;
dbms_output.put_line(v_level);
end;
LOOP
要執行的語句;
IF <條件語句> THEN
要執行的語句;
EXIT ; --條件滿足,退出循環語句
END IF;
END LOOP;
6.case 進階用法,實作查詢的行轉列
-- 考試資訊表
create table t_exam_info(
f_id int primary key,
f_name varchar2(30),
f_subject varchar(30),
f_score number(3,1)
);
insert into t_exam_info values(1, '德瑪', '國文', 89);
insert into t_exam_info values(2, '德瑪', '數學', 90);
insert into t_exam_info values(3, '德瑪', '英語', 80);
insert into t_exam_info values(4, '亞希', '國文', 81);
insert into t_exam_info values(5, '亞希', '數學', 82);
insert into t_exam_info values(6, '亞希', '英語', 83);
insert into t_exam_info values(7, '卡特', '數學', 84);
insert into t_exam_info values(8, '卡特', '英語', 85);
select * from t_exam_info;
select f_name,
sum( case when f_subject = '國文' then f_score ELSE 0 END ) 國文,
sum( case when f_subject = '數學' then f_score ELSE 0 END ) 數學,
sum( case when f_subject = '英語' then f_score ELSE 0 END ) 英語
from t_exam_info
group by f_name
--1.要求聲明變量,每次循環都需要為變量減少數字1,并輸出結果。當變量值小于3時,退出循環操作。
declare
v1 integer:=10;
begin
loop
v1:=v1 - 1;
dbms_output.put_line('v1:' || v1);
if v1 < 3 then
dbms_output.put_line('The variable v1 is less than 3');
exit; --使用exit語句退出loop循環
end if;
end loop;
end;
LOOP
要執行的語句;
EXIT WHEN <條件語句>; --條件滿足,退出循環語句
END LOOP;
declare
v1 integer:=10;
begin
loop
v1:=v1 - 1;
dbms_output.put_line('v1:' || v1);
exit when v1 < 3; --使用exit when(後面使用boolean表達式,如果該表達式傳回true則退出目前循環)語句退出loop循環
end loop;
end;
WHILE <布爾表達式> LOOP
要執行的語句;
END LOOP;
--2.要求聲明變量,每次循環都需要為變量增加數字1,并輸出結果。當變量值小于20時,退出循環操作(while循環實作)。
declare
total integer:=0;
begin
while total < 20 loop
dbms_output.put_line('The valus of total is ' || total);
total:=total + 1;
end loop;
end;
FOR 循環計數器 IN [ REVERSE ] 下限 .. 上限 LOOP
要執行的語句;
END LOOP [循環标簽];
--3.要求聲明變量,然後再确定循環值的範圍,每次循環都需要為變量增加數字1,并在最後輸出循環次數
declare
total integer:=0;
begin
for i in 2..4 loop --使用for确定循環的值的範圍
total:=total + 1;
end loop;
dbms_output.put_line('The total is ' || total);
end;
--4.使用循環列印以下圖形
*
**
***
****
*****
練習嵌套循環
外層循環控制行
内層循環控制每一行列印的星星的個數
declare
begin
for i in 1..5 loop
for j in 1..i loop
dbms_output.put('*'); -- 不換行輸出
end loop;
-- 列印完一行之後換行
dbms_output.put_line('');
end loop;
end;
-- 5.使用for循環列印九九乘法表
declare
begin
for i in 1..9 loop
for j in 1..i loop
dbms_output.put(j || '*' || i || '=' || (i * j) || ' ');
end loop;
dbms_output.put_line('');
end loop;
end;