Contents

1 貉れ, For襭襯 螳 蟆
2 語 螳 貉れ
3
4 襦 危
5 LIKE 一一
6 %TYPE


1 貉れ, For襭襯 螳 蟆 #

  1. /*
  2. OPEN, FETCH, CLOSE, %FOUND煙 覿覿  螳 .
  3. RPAD() 2覯讌 襷り覲 覈 襷 3覯讌 襷り覲 蠍襯 豈譴.
  4. */
  5.  
  6.  
  7. DECLARE CURSOR cur_emp IS
  8. SELECT empno, ename, deptno FROM emp;
  9. BEGIN
  10. 	DECLARE row_count PLS_INTEGER;
  11. 	BEGIN
  12. 		DBMS_OUTPUT.PUT_LINE(RPAD('empno', 5, ' ') || ' ' || 
  13.                 		     RPAD('ename', 10, ' ') || ' ' || 
  14.                         	     RPAD('deptno', 6, ' '));
  15. 		DBMS_OUTPUT.PUT_LINE('-----' || ' ' || '----------' || ' ' || '------');
  16.  
  17.        <>
  18.        FOR cur IN cur_emp LOOP
  19.          --empno : 4, ename : 10, deptno : 2 
  20.          DBMS_OUTPUT.PUT_LINE(RPAD(cur.empno, 4, ' ') || '  ' || 
  21.                               RPAD(cur.ename, 10, ' ') || ' ' || 
  22.                               RPAD(cur.deptno, 2, ' '));
  23.          row_count := cur_emp%ROWCOUNT;	--For 襭 覓旧朱 貉れ襯 螻  
  24.          					--覦覲牛蠍 覓語 伎螳 伎 . 
  25.        END LOOP cursor_loop;
  26.  
  27.        DBMS_OUTPUT.PUT_LINE('');
  28.        DBMS_OUTPUT.PUT_LINE('ROW COUNT : ' || row_count);
  29. 	END;
  30. END;
  31. /
  32.  
  33.  
  34. ====
  35. 蟆郁骸
  36. ====
  37. empno ename      deptno
  38. ----- ---------- ------
  39. 7369  SMITH      20
  40. 7499  ALLEN      30
  41. 7521  WARD       30
  42. 7566  JONES      20
  43. 7654  MARTIN     30
  44. 7698  BLAKE      30
  45. 7782  CLARK      10
  46. 7788  SCOTT      20
  47. 7839  KING       10
  48. 7844  TURNER     30
  49. 7876  ADAMS      20
  50. 7900  JAMES      30
  51. 7902  FORD       20
  52. 7934  MILLER     10
  53. ROW COUNT : 14
  54.  
  55. PL/SQL 豌襴螳 朱 襭給.
  56.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari


2 語 螳 貉れ #

  1. FOR cur_count IN (SELECT ename FROM emp)
  2. DBMS_OUTPUT.put_line(cur_count.ename);
  3.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

3 #

  1. fn_result NUMBER;
  2. --襯 .
  3. --ろ襴渚語企襦 襯 覩碁Μ 伎 .
  4. FUNCTION iifn(boolean_expression IN BOOLEAN, true_number IN NUMBER, false_number IN NUMBER)
  5. IF boolean_expression THEN
  6. RETURN true_number;
  7. ELSIF NOT boolean_expression THEN
  8. RETURN false_number;
  9. END;
  10. fn_result := iifn(2 > 1, 1, 0);
  11. DBMS_OUTPUT.put_line(fn_result);
  12.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

4 襦 危 #

  1. f_num NUMBER;
  2. s_num NUMBER;
  3. --ル 襯 る蠑碁 襦
  4. PROCEDURE swapn(num_one IN OUT NUMBER, num_two IN OUT NUMBER) IS
  5. temp_num NUMBER;
  6. temp_num := num_one;
  7. num_one := num_two;
  8. num_two := temp_num;
  9. END;
  10. f_num := 10;
  11. s_num := 20;
  12. swapn(f_num, s_num);
  13. DBMS_OUTPUT.put_line('First Number = ' || TO_CHAR(f_num));
  14. DBMS_OUTPUT.put_line('Second Number = ' || TO_CHAR(s_num));
  15.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

5 LIKE 一一 #

  1. --讌譴 襷 螳語り
  2. --語襴 螻給覈朱 .
  3. CREATE OR REPLACE FUNCTION fn_return_month(hiredate IN DATE)
  4. h_date VARCHAR2(10);
  5. plsql_err EXCEPTION;
  6. h_date := TO_CHAR(TRUNC(hiredate), 'yyyy-mm-dd');
  7. IF h_date LIKE '____-__-__' THEN
  8. RETURN SUBSTR(h_date, 6, 2);
  9. RAISE plsql_err;
  10. WHEN plsql_err THEN
  11. DBMS_OUTPUT.put_line('讌 殊讌 給. yyyy-mm-dd 朱 襷豢一殊');
  12. END;
  13. --SELECT fn_return_month(SYSDATE) FROM dual;
  14.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

6 %TYPE #

  1. --レ 蟆 蠍 30%襯 炎骸蠍朱 譯手鍵襦 .
  2. --emp.sal 貉殊 語 覲讌 覈襯企襦 %type朱 誤.
  3. CREATE OR REPLACE FUNCTION ufn_stock_option(sal emp.sal%TYPE, sal_percent IN NUMBER )
  4. RETURN emp.sal%TYPE
  5. RETURN sal * sal_percent*0.01;
  6. --SELECT empno, ename, ufn_stock_option(sal, 30) "stock option" FROM emp;
  7.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari