Oracleサンプル4ストレージ・プロシージャ

ストレージ・プロシージャの使用例

CREATE TABLE EMPS
AS
  SELECT * FROM EMP;
CREATE TABLE DEPTS
AS
  SELECT * FROM DEPT;
SELECT * FROM EMPS;
SELECT * FROM DEPTS;

--    ---------------------
--1.      
CREATE OR REPLACE PROCEDURE PROC_DEMO1
AS --  PL/SQL  DECLARE
   V_NAME EMPS.ENAME%TYPE;
   V_SAL EMPS.SAL%TYPE;
   V_DEPTNO EMPS.DEPTNO%TYPE;
   V_AVGSAL NUMBER;
   V_RESULT VARCHAR2(50);
   CURSOR CUR_RESULT IS
          SELECT ENAME,SAL,DEPTNO FROM EMPS;
BEGIN
  OPEN CUR_RESULT;
  LOOP
      FETCH CUR_RESULT INTO V_NAME,V_SAL,V_DEPTNO;
      EXIT WHEN CUR_RESULT%NOTFOUND; --          

      SELECT AVG(SAL) INTO V_AVGSAL FROM EMPS
             WHERE DEPTNO = V_DEPTNO;
      IF V_SAL>V_AVGSAL THEN
        V_RESULT := '  ';
      ELSIF V_SAL = V_AVGSAL THEN
        V_RESULT := '  ';
      ELSE
        V_RESULT := '   ';
      END IF;
      DBMS_OUTPUT.PUT_LINE(V_NAME||':'||V_RESULT);
  END LOOP;
  CLOSE CUR_RESULT;
END PROC_DEMO1;

--      1
--1).   PL/SQL  
BEGIN
  SCOTT.PROC_DEMO1;
END;
--2).      
--     scott    debug   
GRANT DEBUG CONNECT SESSION TO SCOTT;





--2.      
CREATE SEQUENCE SEQ_DEPTS
START WITH 50
INCREMENT BY 10
SELECT * FROM USER_SEQUENCES;

CREATE OR REPLACE PROCEDURE PROC_DEMO2
(   --     VARCAHR2      
    IN_NAME IN VARCHAR2,--     
    IN_LOC IN VARCHAR2  --      
)
AS
    --   
BEGIN
    --   
    INSERT INTO DEPTS VALUES(SEQ_DEPTS.NEXTVAL,IN_NAME,IN_LOC);
    COMMIT;
END PROC_DEMO2;





--3.            
CREATE OR REPLACE PROCEDURE PROC_DEMO3
(
   IN_DEPTNO IN EMPS.DEPTNO%TYPE,
   OUT_RESULT OUT NUMBER
)
AS
BEGIN
   SELECT COUNT(*) INTO OUT_RESULT
          FROM EMPS WHERE DEPTNO = IN_DEPTNO;
END PROC_DEMO3;






--4.           
CREATE OR REPLACE PROCEDURE PROC_DEMO4
(
       IN_ID IN NUMBER,
       OUT_RESULT OUT SYS_REFCURSOR --  Cursor
                  --    Cursor      Cursor    
                  --            
)
AS
BEGIN
  OPEN OUT_RESULT FOR
       SELECT E.ENAME,E.SAL FROM EMPS E
              WHERE E.DEPTNO = IN_ID;
END PROC_DEMO4;
--  PROC_DEMO4
DECLARE
    V_ID NUMBER :=20;
    V_RESULT SYS_REFCURSOR;
    V_ENAME VARCHAR2(50);
    V_SAL NUMBER;
BEGIN
   SCOTT.PROC_DEMO4(V_ID,V_RESULT);
   LOOP
     FETCH V_RESULT INTO V_ENAME,V_SAL;
     EXIT WHEN V_RESULT%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(V_ENAME||'  '||V_SAL);
   END LOOP;
   CLOSE V_RESULT;
END;



--5.      
CREATE OR REPLACE PROCEDURE PROC_DEMO5
(
       IN_INDEX IN NUMBER, --    currPageNo
       IN_SIZE IN NUMBER, --    pageSize
       OUT_RESULT OUT SYS_REFCURSOR,--       
       OUT_COUNT OUT NUMBER,--           


       IN_NAME IN VARCHAR2,
       IN_START IN VARCHAR2,
       IN_END IN VARCHAR2,
       IN_DEPT IN NUMBER
)
AS
       V_SQL VARCHAR2(500); --SQL  
       V_WHERE_SQL VARCHAR2(300); --  SQL  
       V_START_PAGE NUMBER;
       V_END_PAGE NUMBER;
BEGIN
       IF IN_NAME IS NOT NULL THEN
         V_WHERE_SQL := ' AND ENAME LIKE ''%'||IN_NAME||'%'' ';
       END IF;
       IF IN_START IS NOT NULL AND IN_END IS NOT NULL  THEN
         V_WHERE_SQL :=V_WHERE_SQL||' AND HIREDATE BETWEEN TO_DATE('''||IN_START||''',''yyyy-mm-dd'') AND
         TO_DATE('''||IN_END||''',''yyyy-mm-dd'') ';
       END IF;
       IF IN_DEPT > 0 THEN
         V_WHERE_SQL := V_WHERE_SQL||' AND DEPTNO = '||IN_DEPT;
       END IF;
       V_SQL := 'SELECT COUNT(*) FROM EMPS WHERE 1=1 '|| V_WHERE_SQL;

       EXECUTE IMMEDIATE V_SQL INTO OUT_COUNT; --               
       V_START_PAGE := (IN_INDEX-1)*IN_SIZE;
       V_END_PAGE := IN_INDEX*IN_SIZE;
       V_SQL := 'SELECT * FROM ( '||
             ' SELECT E.*,ROWNUM AS R FROM EMPS E WHERE 1=1 '||V_WHERE_SQL||
             ' ) WHERE R<=:1 AND R>:2 ';
      OPEN OUT_RESULT FOR V_SQL  USING V_END_PAGE,V_START_PAGE; --                   USING
END PROC_DEMO5;

SELECT * FROM (
       SELECT E.*,ROWNUM AS R FROM EMPS E WHERE 1=1
)
WHERE R<=5 AND R>0;

SELECT * FROM USER_PROCEDURES;--        
DROP PROCEDURE PROC_DEMO1;--        
DROP PROCEDURE PROC_DEMO2;--        
DROP PROCEDURE PROC_DEMO3;--        
DROP PROCEDURE PROC_DEMO4;--        
DROP PROCEDURE PROC_DEMO5;--