SQL PLUS

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 = 3;
    dbms_output.put_line(' + ');
    v_Num := v_Num+1;
  END LOOP;
   dbms_output.put_line(' = ');
   dbms_output.put_line(v_Sum);
END;

 

 

--- 返回大于或等于所给数字表达式的最小整数
SET ServerOutput ON;
BEGIN
     dbms_output.put_line(CEIL(116.24));
     dbms_output.put_line(CEIL(-112.75));
     dbms_output.put_line(CEIL(0));
END

--- 返回小于或等于所给数字表达式的最大整数
SET ServerOutput ON;
BEGIN
     dbms_output.put_line(FLOOR(116.24));
     dbms_output.put_line(FLOOR(-112.75));
     dbms_output.put_line(FLOOR(0));
END

 

----------------------------------
SET ServerOutput ON;
BEGIN
     dbms_output.put_line(POWER(15.4));
END

---------------------------------
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

-------------------------------
SET ServerOutput ON;
BEGIN
     dbms_output.put_line(ASCII('ABC'));
END

-----------------
select username,LENGTH(username) from users
where userType=1;

-----------------------------------
SET ServerOutput ON;
BEGIN
     dbms_output.put_line(UPPER('asdfasdfsa'));
END
-------------------------
SYSDATE
------------------
TO_CHAR(SYSDATE)
-----------------------
LAST_DAY(SYSDATE)
------------------------
SET ServerOutput ON;
DECLARE
  date1 VARCHAR(20) := '2008-06-05';
  date2 VARCHAR(20) := '2008-10-05';
BEGIN
     dbms_output.put_line(MONTHS_BETWEEN(TO_DATE(date2,'yyyy-mm-dd'),TO_DATE(date1,'yyyy-mm-dd')));
END

 

 

---------------