개발 참고자료

CREATE OR REPLACE PROCEDURE ForCursor_Test
IS


CURSOR c1 IS
SELECT b.dname, COUNT(a.empno) cnt, SUM(a.sal) salary
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname;


BEGIN


-- Cursor를 FOR문에서 실행시킨다
FOR f IN c1 LOOP


DBMS_OUTPUT.PUT_LINE('부서명 : ' || f.dname);
DBMS_OUTPUT.PUT_LINE('사원수 : ' || f.cnt);
DBMS_OUTPUT.PUT_LINE('급여합계 : ' || f.salary);


END LOOP;


EXCEPTION


WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');


END;




=======================




CREATE OR REPLACE FUNCTION get_sel_cust_id(
p_schema IN VARCHAR2,
p_table IN VARCHAR2
)
RETURN VARCHAR2
AS
l_retstr VARCHAR2(2000);
BEGIN
IF(p_schema = USER) THEN
l_retstr := null;
-- 소유자 계정이 아닐 경우 access_policy을 조회해서 IN 조건절을 추가해서 생성 합니다.
ELSE
FOR cust_rec IN (SELECT cust_id FROM access_policy WHERE am_name = USER AND access_type='S')
LOOP
l_retstr := l_retstr || ',' || cust_rec.cust_id;
END LOOP;
l_retstr := LTRIM(l_retstr, ',');
l_retstr := 'CUST_ID IN (' || l_retstr || ')';
END IF;
RETURN l_retstr;
END;




=============================================