PL/SQL基本流程控制语句语法

----------------------------------------------------------------------
DECLARE
  conVerson constant VARCHAR2(20) := '1.0.01';
BEGIN
  DBMS_OUTPUT.put_line(conVerson);
END;
----------------------------------------------------------------------
DECLARE
  Database VARCHAR2(50) := 'Oracle 10g';
BEGIN
  dbms_output.put_line(Database);
END;
----------------------------------------------------------------------
DECLARE
  Database VARCHAR2(50);
BEGIN
  Database := 'Oracle 11g';
  dbms_output.put_line(Database);
END;
----------------------------------------------------------------------
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;
----------------------------------------------------------------------
DECLARE
  varDAY INTEGER := 5;
  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;
----------------------------------------------------------------------
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 = 10 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;
----------------------------------------------------------------------
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 = 9;
    dbms_output.put_line('+');
    V_num := V_num + 1;
  END LOOP;
  dbms_output.put_line('=');
  dbms_output.put_line(V_sum);
END;
----------------------------------------------------------------------
DECLARE
  v_Num INTEGER := 1;
  v_Sum INTEGER := 0;
BEGIN
  WHILE v_Num <= 11 LOOP
    v_Sum := v_Sum + v_Num;
    dbms_output.put_line(v_Num);
    IF v_Num < 11 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;
----------------------------------------------------------------------
DECLARE
  v_Num INTEGER;
  v_Sum INTEGER := 0;
BEGIN
  FOR v_Num IN 1 .. 3 LOOP
    v_Sum := v_Sum + v_Num;
    dbms_output.put_line(v_Num);
    IF v_Num < 3 THEN
      dbms_output.put_line('+');
    END IF;
  END LOOP;
  dbms_output.put_line('=');
  dbms_output.put_line(v_Sum);
END;
----------------------------------------------------------------------
DECLARE
  x NUMBER;
BEGIN
  x := 'a123';
EXCEPTION
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.put_line('----- TYPE ERROR -----');
END;
----------------------------------------------------------------------
DECLARE
  var_Username VARCHAR(40);
BEGIN
  SELECT NAME INTO var_Username FROM EMPLOYEE WHERE GIVENNAME = 'Joe';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('NO DATA');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.put_line('TOO MANY DATA');
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('UNKNOWN ERROR');
END;
----------------------------------------------------------------------
DECLARE
BEGIN
  DBMS_OUTPUT.put_line(ABS(-4));
  DBMS_OUTPUT.put_line(CEIL(116.24));
  DBMS_OUTPUT.put_line(CEIL(-112.75));
  DBMS_OUTPUT.put_line(CEIL(0));
  DBMS_OUTPUT.put_line(FLOOR(116.24));
  DBMS_OUTPUT.put_line(FLOOR(-112.75));
  DBMS_OUTPUT.put_line(FLOOR(0));
  DBMS_OUTPUT.put_line(POWER(15, 4));
  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;
----------------------------------------------------------------------
DECLARE
  date1 VARCHAR2(20) := '2008-06-05';
  date2 VARCHAR2(20) := '2008-10-05';
BEGIN
  DBMS_OUTPUT.put_line(ASCII('ABC'));
  DBMS_OUTPUT.put_line(SYSDATE);
  DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE));
  DBMS_OUTPUT.put_line(LAST_DAY(SYSDATE));
  DBMS_OUTPUT.put_line(MONTHS_BETWEEN(TO_DATE(date2, 'yyyy-mm-dd'),
                                      TO_DATE(date1, 'yyyy-mm-dd')));
END;
----------------------------------------------------------------------
SELECT * FROM EMPLOYEE;
SELECT COUNT(ID) FROM EMPLOYEE;
SELECT MAX(ID) FROM EMPLOYEE;
SELECT MIN(ID) FROM EMPLOYEE;
----------------------------------------------------------------------
DECLARE
  Ver NUMBER;
BEGIN
  SELECT LENGTH(USERNAME) into Ver FROM EMP WHERE USERID = 1;
  dbms_output.put_line(Ver);
END;

----------------------------------------------------------------------
DECLARE
  DEPNAME AA_DEPARTMENTS.DEPARTMENT_NAME%TYPE;
BEGIN
  SELECT DEPARTMENT_NAME INTO DEPNAME FROM AA_DEPARTMENTS WHERE DEPARTMENT_ID=10;
  DBMS_OUTPUT.put_line(DEPNAME);
END;
----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION f_compute_costproject_code(cbs_code IN varchar2)
  RETURN varchar2 AS
  res_costproject_code varchar(40);
BEGIN
  res_costproject_code := case
                          -------------------
                            when cbs_code in
                                 ('0101', '0201', '0301', '0501') then
                             '01'
                          -------------------
                            when cbs_code in
                                 ('0102', '0202', '0302', '0502') then
                             '02'
                          -------------------
                            when cbs_code in
                                 ('0103', '0203', '0303', '0503') then
                             '03'
                          -------------------
                            when cbs_code in
                                 ('0106', '0206', '0306', '0506') then
                             '04'
                          -------------------
                            when cbs_code in
                                 ('0104', '0204', '0304', '0504') then
                             '05'
                          -------------------
                            when cbs_code in
                                 ('0105', '0205', '0305', '0505') then
                             '06'
                          -------------------
                            when cbs_code in
                                 ('0108', '0208', '0308', '0508') then
                             '07'
                          -------------------
                            when cbs_code in
                                 ('0107', '0207', '0307', '0507') then
                             '08'
                          -------------------
                            when cbs_code in ('0410', '9010') then
                             'other'
                            else
                             'none'
                          end;
  RETURN res_costproject_code;
END;