Jeong's

SET SERVEROUTPUT ON; DECLARE O_CUR SYS_REFCURSOR; V_M_ID VARCHAR2(100); V_M_NAME VARCHAR2(100); V_M_PW VARCHAR2(100); V_EMAIL VARCHAR2(100); V_LOGIN_CNT NUMBER; BEGIN pkg_members.select_members('MEM001', 'TEST', 'TEST', O_CUR); LOOP FETCH O_CUR INTO V_M_ID, V_M_NAME, V_M_PW, V_EMAIL, V_LOGIN_CNT; EXIT WHEN O_CUR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(V_M_ID||', '||V_M_NAME||', '||V_M_PW||', '||V_EMAIL||..

1 ~ 12 : SELECT TO_CHAR(LEVEL, '00') LV FROM DUAL CONNECT BY LEVEL

SELECT * FROM SCORES ORDER BY CASE WHEN SCORE >= 80 THEN SCORE END DESC ,CASE WHEN SCORE < 80 THEN SCORE END ASC;

DECLARE TYPE ARR_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; STUDENTS_NAME ARR_TYPE; I NUMBER := 1; J NUMBER := 0; BEGIN FOR CUR IN (SELECT * FROM STUDENTS) LOOP STUDENTS_NAME(I) := CUR.S_NAME; I := I + 1; END LOOP; FOR J IN 1..I - 1 LOOP DBMS_OUTPUT.PUT_LINE(STUDENTS_NAME(J)); END LOOP; END; /

-- SEQ 생성 INSERT INTO TEST_TBL VALUES( (SELECT NVL(MAX(SEQ), 0) + 1 FROM TEST_TBL) ); -- SEQ 삭제 후 재정렬 DECLARE V_SEQ NUMBER; BEGIN V_SEQ := 4; DELETE FROM TEST_TBL WHERE SEQ = V_SEQ; UPDATE TEST_TBL SET SEQ = SEQ - 1 WHERE SEQ > V_SEQ; END;

package header : create or replace PACKAGE PKG_STUDENTS AS -- CREATE PROCEDURE INSERT_STUDENT ( IN_S_NAME IN VARCHAR2 ,IN_S_ADDR IN VARCHAR2 ); -- READ(RETREIVE) PROCEDURE SELECT_STUDENT ( IN_S_NAME IN VARCHAR2 ,IN_S_ID IN VARCHAR2 ,IN_S_ADDR_NM IN VARCHAR2 ,OUT_CUR OUT SYS_REFCURSOR ); -- UPDATE PROCEDURE UPDATE_STUDENT ( IN_S_ID IN VARCHAR2 ,IN_S_NAME IN VARCHAR2 ,IN_S_ADDR IN VARCHAR2 ); -- D..

create or replace procedure spgetinfo1 (in_dept in emp.deptno%type ,v1 out dept.dname%type ,v2 out emp.empno%type ,v3 out emp.sal%type ) is cursor c1 is select d.dname, count(e.empno), avg(e.sal) from emp e right join dept d on e.deptno = d.deptno where e.deptno = in_dept group by d.dname; begin open c1; fetch c1 into v1, v2, v3; dbms_output.put_line('====================='); dbms_output.put_lin..

row_number() // 열 번호를 출력 rank() // 일반적인 랭크 dense_rank() // 1등이 다수여도 2등이 나옴