Oracle 기본정리 - Procedure(if,for,while,in,out 예제)
프로그래밍 정리/Oracle
2019. 12. 4. 16:27
반응형
Oracle 기본정리 - Procedure(if,for,while,in,out 예제)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 | ------------------------------------------------------------ --문제2 if문 /* 2000미만 low 2000~4000미만 middle 4000이상 high if... deptno=10 'a' deptno=20 'a' deptno=30 'a' deptno=40 'a' 사원번호 이름 급여 급여결과 부서코드 부서명 */ create or replace procedure proc8(a number) is TYPE emp_rec_type is record(empno emp.empno%type ,ename emp.ename%type ,sal emp.sal%type ,deptno emp.deptno%type); v_emp_rec emp_rec_type; v_dname varchar2(1); v_result varchar2(6); begin select empno, ename, sal, deptno into v_emp_rec from emp where empno=a; if (v_emp_rec.sal < 2000) then v_result:='low'; elsif (v_emp_rec.sal between 2000 and 4000) then v_result:='middle'; else v_result:='high'; end if; if (v_emp_rec.deptno=10) then v_dname:='a'; elsif (v_emp_rec.deptno=20) then v_dname:='b'; elsif (v_emp_rec.deptno=30) then v_dname:='c'; elsif (v_emp_rec.deptno=40) then v_dname:='d'; end if; dbms_output.put_line(v_emp_rec.empno); dbms_output.put_line(v_emp_rec.ename); dbms_output.put_line(v_emp_rec.sal); dbms_output.put_line(v_result); dbms_output.put_line(v_emp_rec.deptno); dbms_output.put_line(v_dname); exception when NO_DATA_FOUND then dbms_output.put_line('값이 없습니다.'); end; execute proc8(7844); ------------------------------------------------------------------------------------- --while문 연습 declare n number(4):=0; begin loop dbms_output.put_line(n); n:=n+1; if n>=5 then exit; end if; end loop; end; -------------------------------------------- --for문 연습 declare begin for i in 1..5 loop dbms_output.put_line(i); end loop; end; ---------------------------------------------------------- --is table of 사용해보기 => map<Integer , varchar2(10)> 라고생각하면 쉽다 create or replace procedure getdept(dcode emp.deptno%type) is type emp_table_ename_type is table of emp.ename%type index by PLS_INTEGER; vemp_ename emp_table_ename_type; begin select ename bulk collect into vemp_ename from emp where deptno=dcode; for i IN vemp_ename.first .. vemp_ename.last LOOP dbms_output.put_line(vemp_ename(i)); END LOOP; end; execute getdept(10); ----------------------------------------------------------------------- --문제 /* 특정부서 정보 다뽑기 exec getdept01(10); exec getdept01(20); */ create or replace procedure getdept01(getdept emp.deptno%type) is type emp_table_type is table of emp%rowtype index by pls_Integer; emp_row emp_table_type; begin select * bulk collect into emp_row from emp where deptno=getdept; for i in emp_row.first .. emp_row.last loop dbms_output.put_line(emp_row(i).empno); dbms_output.put_line(emp_row(i).ename); dbms_output.put_line(emp_row(i).sal); dbms_output.put_line(emp_row(i).hiredate); end loop; exception when VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('데이터 없음!'); end; execute getdept01(100); ------------------------------------------------------------------------ create or replace procedure getdept02(a number) is TYPE emp_rec_type is record(empno emp.empno%type ,ename emp.ename%type ,job emp.job%type ,sal emp.sal%type); TYPE emp_table_type is table of emp_rec_type index by pls_integer; emp_row emp_table_type; begin select empno, ename, job, sal bulk collect into emp_row from emp where deptno=a; for i in emp_row.first .. emp_row.last loop dbms_output.put_line(emp_row(i).empno); dbms_output.put_line(emp_row(i).ename); dbms_output.put_line(emp_row(i).job); dbms_output.put_line(emp_row(i).sal); end loop; end; execute getdept02(10); --------------------------------------------------------------------------- --매개변수 in(받는 변수), out(출력 변수) create or replace procedure proc_emp( vempno in emp.ename%type ,vename out emp.ename%type ,vsal out emp.sal%type ) is begin select ename, sal into vename, vsal from emp where empno=vempno; end; variable v_name varchar2(20); variable v_sal number; execute proc_emp(7844,:v_name,:v_sal); print v_name; print v_sal; | cs |
반응형
'프로그래밍 정리 > Oracle' 카테고리의 다른 글
Oracle 기본정리 - trigger 예제 (0) | 2019.12.04 |
---|---|
Oracle 기본정리 - Cursor 예제 (0) | 2019.12.04 |
Oracle 기본정리 - Declare, Procedure 문제 (0) | 2019.12.04 |
Oracle 기본정리 - Declare, Procedure 기본 (0) | 2019.12.04 |
Oracle 기본정리 - 인덱스(Index) (0) | 2019.12.04 |