-----------------------------------------------------------------------------------
備份和恢復數據庫略過,在後面講解應用場景207---231,越過。。。。。
數據庫網絡配置略過,其在實際的應用中不是很普遍,所以,此處先不進行詳細的講解,如果後期有時間會進行整理。
應用場景32----244越過。。。。。。。。
-----------------------------------------------------------------------------------
-------------------PL/SQL語言基礎------------------------
基礎知識點:
PL/SQL字符集
PL/SQL示例程序
聲明變量與常量
----------------------------------------------------------
應用場景2245 :使用賦值語句
SET ServerOutput ON; DECLARE BookName VARCHAR2(50); BEGIN BookName := ' Oracle 11g學習筆記'; dbms_output.put_line(BookName); END; /
應用場景246 :使用條件語句
SET ServerOutput ON; DECLARE Num INTEGER := -11; BEGIN IF Num < 0 THEN dbms_output.put_line('負數'); ELSIF Num >0 THEN dbms_output.put_line('正數'); ELSE dbms_output.put_line('0'); END IF; END; /
應用場景247:分支語句CASE
SET ServerOutput ON; DECLARE varDAY INTEGER := 3; Result VARCHAR2(20); BEGIN Result := CASE varDAY WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三' WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 7 THEN '星期七' ELSE '數據越界' END; dbms_output.put_line(Result); END; /
應用場景248 :循環語句LOOP...EXIT...END
SET ServerOutput ON; DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num = 4 THEN EXIT; END IF; dbms_output.put_line(' + '); v_Num := v_Num + 1; END LOOP; dbms_output.put_line(' = '); dbms_output.put_line(v_Sum); END; /
應用場景249 :循環語句LOOP...EXIT WHEN ..END
SET ServerOutput ON; DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); EXIT WHEN v_Num = 4; dbms_output.put_line(' + '); v_Num := v_Num + 1; END LOOP; dbms_output.put_line(' = '); dbms_output.put_line(v_Sum); END; /
應用場景250 :循環語句WHILE ....LOOP...END LOOP
SET ServerOutput ON; DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN WHILE v_Num <= 4 LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num < 4 THEN dbms_output.put_line(' + '); END IF; v_Num := v_Num + 1; END LOOP; dbms_output.put_line(' = '); dbms_output.put_line(v_Sum); END; /
應用場景251 :循環語句FOR...IN...LOOP...END LOOP
SET ServerOutput ON; DECLARE v_Num INTEGER; v_Sum INTEGER := 0; BEGIN FOR v_Num IN 1..4 LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num < 4 THEN dbms_output.put_line(' + '); END IF; END LOOP; dbms_output.put_line(' = '); dbms_output.put_line(v_Sum); END; /
應用場景252 :異常處理
SET SERVEROUTPUT ON; DECLARE x NUMBER; BEGIN x:= 'abc';--向NUMBER類型的變量X中賦值字符串,導致異常 EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('數據類型錯誤'); END; / SET SERVEROUTPUT ON; DECLARE var_DepName VARCHAR(40); BEGIN SELECT Dep_Name INTO var_DepName FROM HRMAN.Departments WHERE Dep_id > 1; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('沒有數據'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('返回多行匹配的數據'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('錯誤情況不明'); END; /
應用場景253 :數值型函數
1.ABS函數
SET ServerOutput ON;
BEGIN
dbms_output.put_line(ABS(-4));
END;
/
2.CEIL函數
SET ServerOutput ON;
BEGIN
dbms_output.put_line(CEIL(116.34));
dbms_output.put_line(CEIL(-112.74));
dbms_output.put_line(CEIL(0));
END;
/
3.FLOOR函數
SET ServerOutput ON;
BEGIN
dbms_output.put_line(FLOOR(116.34));
dbms_output.put_line(FLOOR(-112.74));
dbms_output.put_line(FLOOR(0));
END;
/
4.POWER函數
SET ServerOutput ON;
BEGIN
dbms_output.put_line(POWER(15, 4));
END;
/
5.ROUND函數
SET ServerOutput ON; BEGIN dbms_output.put_line(ROUND(123.456, 2)); dbms_output.put_line(ROUND(123.456, 1)); dbms_output.put_line(ROUND(123.456, 0)); dbms_output.put_line(ROUND(123.456, -1)); dbms_output.put_line(ROUND(123.456, -2)); dbms_output.put_line(ROUND(123.456, -3)); END; /
應用場景254 :字符型函數
SET ServerOutput ON; BEGIN dbms_output.put_line(ASCII('ABC')); END; / COL DEP_NAME FORMAT A20 SELECT Dep_name, LENGTH(Dep_name) FROM HRMAN.Departments; SET ServerOutput ON; BEGIN dbms_output.put_line(UPPER('abc')); END; /
應用場景255 :日期型函數
sysdate函數: SET ServerOutput ON; BEGIN dbms_output.put_line(SYSDATE); END; / TO_CHAR函數 SET ServerOutput ON; BEGIN dbms_output.put_line(TO_CHAR(SYSDATE)); END; / last_day函數 SET ServerOutput ON; BEGIN dbms_output.put_line(LAST_DAY(SYSDATE)); END; / TO_DATE函數 SET ServerOutput ON; BEGIN dbms_output.put_line(TO_DATE('2010-2-5', 'yyyy-mm-dd')); END; / months_between函數: SET ServerOutput ON; DECLARE date1 VARCHAR2(20) := '2010-06-05'; date2 VARCHAR2(20) := '2010-10-05'; BEGIN dbms_output.put_line(MONTHS_BETWEEN(TO_DATE(date2,'yyyy-mm-dd'), TO_DATE(date1, 'yyyy-mm- dd'))); END; / round函數: SET ServerOutput ON; BEGIN dbms_output.put_line(TO_CHAR(ROUND (SYSDATE, 'MM'))); END; 應用場景256 :統計函數 count函數 SELECT COUNT(Dep_id) FROM HRMAN.Departments; MAX函數 SELECT MAX(Dep_id) FROM HRMAN.Departments; MIN函數 SELECT MIN(Dep_id) FROM HRMAN.Departments;