sql大資料 基礎（PL/SQL編程）

它是一門Oracle支持的編程語言
可以將多條sql陳述句封裝在一起,形成一個整體

plsql 語法結構

宣告部分(declare), 開始部分(begin)(執行代碼),結束end結束符號DECLARE
DECLARE
宣告部分
1 變數 (可變)
2 常量(不可變)
3 游標
BEGIN
–要實作的業務代碼
EXCEPTION --例外
end;

宣告一個變數列印變數的值

例：DECLARE
a NUMBER(10) :=10;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
END;
例：DECLARE
a NUMBER(10):=10;
BEGIN
a:=20;
DBMS_OUTPUT.PUT_LINE(a);
END;

資料型別

數值型
NUMBER
PLS_INTEGER --整型
BINARY_INTEGER --整型
simple_integer --整型 效率較高
字符型
VARCHAR2()
VARCHAR
CHAR
LOB --大文本型別
布爾型別
TRUE
FALSE
–時間型別
DATE
比較運算子
< = >= <= != <>
BETWEEN AND
邏輯運算子
NOT
AND
OR
plsql中
:= 賦值號
… 范圍符號
=> 傳值

常量
ECLARE
CONSTANT a NUMBER(10) = 10;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
END;

plsql 中變數型別

普通變數名 (由數字,字母,下劃線組成,且不能以數字開頭),變數名要起的有意義
記錄型
DECLARE
TYPE 型別名 IS RECORD (
變數名 變數型別,
變數名2 變數型別,
…
);
變數名 型別名;
BEGIN
end;

DECLARE
TYPE t IS RECORD(
a VARCHAR2(100),
b NUMBER(10),
c DATE
);
例：”DECLARE
TYPE t IS RECORD(
a VARCHAR2(100),
b NUMBER(10),
c DATE
);
my_t t;
BEGIN
my_t.a :=‘10’;
my_t.c :=SYSDATE;
DBMS_OUTPUT.PUT_LINE(my_t.a);
dbMS_OUTPUT.PUT_LINE(my_t.c);
END;

定義三個變數

declare
a VARCHAR2(100);
b NUMBER(10);
c DATE;

TYPE t IS RECORD(
a VARCHAR2(100);
b NUMBER(10);
c DATE;
)
my_t t; 給型別名t取一個變數名
a emp.ename %TYPE; a 的型別和emp表中的ename列的型別一致!!

定義一個變數來接收emp中的ename列型別的值

DECLARE
v_ename emp.ename %TYPE;
v_date emp.hiredate%TYPE;
BEGIN
v_ename:=‘king!!’;
v_date :=SYSDATE;
DBMS_OUTPUT.PUT_LINE(v_ename || v_date); --列印多個值,值和值之間不能用逗號,要用連接符
END;

變數定義的型別

DECLARE
a NUMBER(10); – 單行單列
b NUMBER(10):=100;
c VARCHAR2(10);
d emp.ename %TYPE; --單行單列
TYPE t IS RECORD(e NUMBER(10), f emp.empno %TYPE); --單行 多列
my_t t;
v_emp emp %ROWTYPE; --g可以接收emp的一行(所有列)記錄 (單行多列)
BEGIN
a:=10;
– dbMS_OUTPUT.PUT_LINE(a);
dbMS_OUTPUT.PUT_LINE(b);
a:=20;
–DBMS_OUTPUT.PUT_LINE(a);
b:=a ; --將a的值賦予b
dbMS_OUTPUT.PUT_LINE(b);
my_t.e:=50;
dbMS_OUTPUT.PUT_LINE(my_t.e);
v_emp.ename:=‘king!’;
v_emp.sal:=1009;
dbMS_OUTPUT.PUT_LINE(v_emp.ename||’ '||v_emp.sal);
END;

普通變數
變數名 變數型別[:= 默認值]

記錄型 RECORD
TYPE 型別名 is RECORD (
變數名1 變數型別[:= 默認值]
變數名2 變數型別[:= 默認值]
…
)

%TYPE 型別(可以接收某個表的某一列)
變數名 表名.列名%TYPE

%ROWTYPE 型別 (可以接收某個表的全部列)
變數名 表名 %ROWTYPE

–plsql 中支持的語言
DML : INSERT , DELETE ,UPDATE
SELECT 列1,列2,… INTO 變數1, 變數2 --,在程式塊中,必須要使用到
–不直接支持create ,可以通過動態sql方式完成create

例：查詢編號為7369的員工的姓名

DECLARE
v_name emp.ename%TYPE;
BEGIN
SELECT ename INTO v_name FROM emp WHERE empno=7369;
dbMS_OUTPUT.PUT_LINE(v_name);
END;

–在控制臺上列印輸出 員工編號為7369的 名字,工資,工種,入職日期
–要求輸出格式為 名字:smith 工資:789 工種:salesman 入職日期:1991-10-02

–第一種方法

DECLARE
v_name VARCHAR2(100);
v_sal emp.sal%TYPE;
v_job emp.job %TYPE;
v_date DATE;
BEGIN
SELECT ename,sal,job,hiredate INTO v_name,v_sal,v_job,v_date
FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE('名字: ‘||v_name||’ ‘||‘工資:’||v_sal||’ ‘||‘工種:’||v_job||’ '||‘入職日期:’||v_date);
END;

–第二種寫法

DECLARE
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE(v_emp.ename||’ '||v_emp.sal||v_emp.hiredate);
END;

–第三種寫法

DECLARE
TYPE t IS RECORD (
v_name VARCHAR2(100),
v_sal emp.sal%TYPE,
v_job emp.job %TYPE,
v_date DATE
);
v_emp t;
BEGIN
SELECT ename,sal,job,hiredate INTO v_emp.v_name ,v_emp.v_sal,v_emp.v_job,v_emp.v_date
FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE(v_emp.v_name);
END;

–注意: 目前只能接收單行的資料,超過一行就會報錯

增刪改

–insert 增
RETURNING 表名.列名 INTO 變數名
–可以將增刪改后的某個值輸出在列印臺上

DECLARE
v_name VARCHAR2(200);
BEGIN
INSERT INTO p(empno,ename) VALUES(1111,‘tiger’) RETURNING p.ename INTO v_name ;
dbMS_OUTPUT.PUT_LINE('新增了 '||v_name);
END;

–UPDATE 改

DECLARE
v_name VARCHAR2(20);
BEGIN
UPDATE p SET ename=‘smalltiger’ WHERE empno=1111 RETURNING p.ename INTO v_name;
dbMS_OUTPUT.PUT_LINE('將名字更新為 '||v_name);
COMMIT;
END;
SELECT * FROM p

–DELETE 刪

DECLARE
v_no p.empno%TYPE;
BEGIN
DELETE FROM p WHERE empno=1111 RETURNING p.empno INTO v_no;
dbMS_OUTPUT.PUT_LINE(‘洗掉了編號為 ‘||v_no||’ 的資料’);
COMMIT;
END;

–常量
DECLARE
常量名 CONSTANT 資料型別;
BEGIN
業務代碼
end;

例：DECLARE
C_NAME CONSTANT VARCHAR2(100) := ‘smith’;
BEGIN
–C_NAME := ‘king’;
DBMS_OUTPUT.PUT_LINE(C_NAME);
END;

練習：向emp表中 增加 empno, ename,sal , 修改 ,洗掉 ,將增刪改后的empno和ename列印輸出

DECLARE
v_no p.empno%TYPE;
v_name VARCHAR2(100);
v_sal NUMBER(10);
BEGIN
—insert操作
INSERT INTO p (empno,ename,sal) VALUES(1122,‘haha’,789)
RETURNING p.empno,p.ename INTO v_no, v_name;
DBMS_OUTPUT.PUT_LINE('添加了 '||v_no||v_name);
–UPDATE操作
UPDATE p SET ename=‘hehehe’ WHERE empno=1122
RETURNING p.empno,p.ename INTO v_no, v_name;
DBMS_OUTPUT.PUT_LINE('修改了 '||v_no||v_name);
–DELETE 操作
DELETE FROM p WHERE empno=1122
RETURNING p.empno,p.ename INTO v_no, v_name;
DBMS_OUTPUT.PUT_LINE('洗掉了 '||v_no||v_name);
END;

–在列印臺上輸出列印 emp表的員工的個數,格式 emp: 人數

– select count(*) from emp
DECLARE
v_no NUMBER(10);
BEGIN
SELECT COUNT(1) INTO v_no FROM emp;
DBMS_OUTPUT.PUT_LINE('emp: '||v_no);
END;