基本語法一(宣告變數、型態) (PL/SQL 一)

※匯入假資料練習

DROP TABLE EMP  PURGE ;
DROP TABLE DEPT PURGE ;
DROP TABLE BONUS PURGE ;
DROP TABLE SALGRADE PURGE ;
    
CREATE TABLE DEPT (
    DEPTNO    NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME    VARCHAR2(14),
    LOC    VARCHAR2(13) 
);
    
CREATE TABLE EMP (
    EMPNO    NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME    VARCHAR2(10),
    JOB    VARCHAR2(9),
    MGR    NUMBER(4),
    HIREDATE    DATE,
    SAL    NUMBER(7,2),
    COMM    NUMBER(7,2),
    DEPTNO    NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
    
CREATE TABLE BONUS (
    ENAME    VARCHAR2(10),
    JOB    VARCHAR2(9),
    SAL    NUMBER,
    COMM    NUMBER
);
    
CREATE TABLE SALGRADE ( 
    GRADE    NUMBER,
    LOSAL    NUMBER,
    HISAL    NUMBER
);
    
INSERT INTO DEPT VALUES    (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES    (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES    (40,'OPERATIONS','BOSTON');
    
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','DD-MM-YYYY'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','DD-MM-YYYY'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','DD-MM-YYYY'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','DD-MM-YYYY'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','DD-MM-YYYY'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','DD-MM-YYYY'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','DD-MM-YYYY'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('19-04-1987','DD-MM-YYYY')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','DD-MM-YYYY'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','DD-MM-YYYY'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('23-05-1987','DD-MM-YYYY')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','DD-MM-YYYY'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','DD-MM-YYYY'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','DD-MM-YYYY'),1300,NULL,10);
    
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
    
COMMIT;

※最簡單的PL/SQL

DECLARE
    
BEGIN
    NULL;
END;
/

※大小寫沒差

※NULL一定要有，不然會出錯，所以才叫最簡單的PL/SQL

※在command(也就是dos)和PL/SQL Developer有些版本要加最後一行的「/」，而官方的Oracle SQL Developer不用

※單行註解用「--」； 多行註解用「/*」「*/」包起來

※我用PL/SQL Developer中文出現亂碼，我在環境變數加「NLS_LANG=AMERICAN_AMERICA.ZHT16MSWIN950」就解決了

※宣告變數

DECLARE
    i NUMBER;
BEGIN
    i := 3;
    DBMS_OUTPUT.PUT_LINE('i=' || i);
END;

※和java相反，要先宣告變數名稱，後面接型態

※給值用「:=」，可以在DECLARE裡，也可以在BEGIN裡

※相連用「||」，不是「+」哦！

※DBMS_OUTPUT.PUT_LINE的功能是在控制台輸出訊息，和java的System.out.println一樣

※而DBMS_OUTPUT.PUT和java的System.out.print類似，除了沒換行以外，它還要輸入NEW_LINE或PUT_LINE才會輸出，可參考第十六篇的DBMS_OUTPUT包

※NOT NULL 和 CONSTANT

i NUMBER NOT NULL := 3;
    
j CONSTANT NUMBER := 4;

※如果是 NOT NULL 後面一定要給值，寫在 BEGIN 不行，但如果都寫那就表示BEGIN會覆蓋DECLARE的值

※CONSTANT 和 NOT NULL 很像，後面一定要給值，但後面不能再覆蓋，而 CONSTANT 和javascript 的 const 一樣，第一次就要給值了
而 java 的 final，第一次可以不給值，給值只能給一次

※數字一般都是用NUMBER；字串用CHAR、VARCHAR2、NCHAR、NVARCHAR2； 日期用DATE、布林用BOOLEAN

※NUMBER 還可以寫 NUMBER(5, 2)，表示小數點有兩位，全部有 5 位，所以整數位就是 3 位，「.」不算

※VARCHAR 和 VARCHAR2 目前一樣，大部分都習慣用 VARCHAR2

※CHAR(3)表示存3位，如果少於3位，後面會是空格，也就是長度永遠都是3，假設給java處理，又不想要空格，就要trim()掉，或用SQL的rtrim()； 但如果是這種需求，其實用VARCHAR2就好了，會自動幫我們去掉

※NCHAR或NVARCHAR2，多個N表示非ASCII和ASCII長度都一樣，假設在CHAR裡，「一」這個中文字，可能長度是3，「a」長度是1，用N的話就都是3，也就是不管什麼字都能統一它的長度，但缺點是會浪費空間，所以如果確定都會存ASCII，就用CHAR或VARCHAR2

※上面講的是常用的，還有很多類型，可參考官網

※%TYPE

DECLARE
    xxx_deptno DEPT.DEPTNO%TYPE;
    xxx_dname DEPT.DNAME%TYPE;
BEGIN
    xxx_deptno := &pk;  
    
    SELECT DNAME INTO xxx_dname 
    FROM DEPT 
    WHERE DEPTNO = xxx_deptno;
    
    DBMS_OUTPUT.PUT_LINE('pk是' || xxx_deptno || '，部門是' || xxx_dname);
END;

※也就是DEPT.DEPTNO的型態是什麼，xxx_deptno的型態就是什麼，xxx_dname一樣

※在PL/SQL裡要用「INTO」將資料庫的欄位給在這裡宣告的變數

※&pk的pk是隨便打的臨時變數，而「&」會在執行時跳出一個視窗讓我們打(想看更詳細的請看這篇)，PL/SQL Developer不支援，要在官方的Oracle SQL Developer試，如以下的畫面：

※但預設在Oracle SQL Developer看不到執行的結果，要如下的設定

※執行完PL/SQL，在命令檔輸出，只會出現「匿名區塊已完成」，表示成功，而失敗也會在這裡出現錯誤訊息

※而一般的SQL的結果會出現在「查詢結果」的活頁標籤，沒有PL/SQL的結果，所以要如上設定

※叫出「DBMS輸出」後，選左上角的「+」，會出現上面的「選取連線」，然後選一位使用者，選完會在左下角顯示目前的使用者，表示成功了，而再次執行PL/SQL，就會在這裡看到訊息了

※%ROWTYPE

DECLARE
    xxx_dept DEPT%ROWTYPE;
BEGIN
    
    SELECT * INTO xxx_dept 
    FROM DEPT 
    WHERE DEPTNO = 40;
    
    DBMS_OUTPUT.PUT_LINE('pk是' || xxx_dept.deptno || '，部門是' || xxx_dept.dname);
END;

※DEPT%ROWTYPE就是DEPT表的一條記錄，變成一個型態給xxx_dept接收

※透過「*」給xxx_dept就是一條記錄，輸出時就「.」下去就可以了

※子類型

DECLARE
    SUBTYPE ooo IS NUMBER(3, 2);
    xxx ooo := 1.23;
BEGIN
    DBMS_OUTPUT.PUT_LINE('xxx是' || xxx);
END;

※就是是自己定義一個型態，如ooo是數字，而xxx是一個ooo的型態，也就是xxx就是數字，這個我不知道能做什麼，可看官網

※宣告型態

※宣告 integer 和 number 時可以不給長度；但 varchar 和 varchar2 一定要給

※宣告 integer，但賦值時給小數，會自動四捨五入到整數

※宣告 number(5，2)，表示小數點 2 位，全部有 5 位(不包括「.」)，所以整數位為 5 - 2 = 3

※宣告 number 時，若賦值時小數點超過會自動四捨五入；但整數超過會直接報錯