728x90 반응형 oracle17 oracle sql 서브쿼리,function,no_data_found,too_many_rows,others ed fn_deptInfocreate or replace function fn_deptInfo(v_name emp.ename%type)return varchar2isv_no number;v_dname varchar2(50);v_loc varchar2(50);v_total varchar2(100);beginselect d.deptno, d.dname, d.loc into v_no, v_dname, v_loc from (select deptno from emp where ename=v_name) e,dept d where d.deptno=e.deptno;v_total := '부서번호:'||v_no ||'부서명:'||v_dname||'장소:'||v_loc;return v_total;end;/----------.. 2015. 10. 7. oracle sql function, dual,upper ed fn_sum2 create or replace function fn_sum2(v_sum1 in number,v_sum2 in number)return numberis v_sum number;beginv_sum :=v_sum1 + v_sum2;return v_sum;end;/@fn_sum2variable sum number;execute :sum :=fn_sum2(10,20)print sum; select fn_sum2(10,20) from dual; --------------------------------------ed sum_dept; create or replace function sum_dept(v_deptno in dept.deptno%type)return number isv_sum num.. 2015. 10. 6. oracle sql cursor update,delete, 무결성 제약조건 create table dept_no(dept_no number);declarecursor dept_cursor is select *from dept;beginfor dept_record in dept_cursor loopinsert into dept_no(deptno) values(dept_record.deptno);end loop;end;/ 2.emp테이블로 커서를 만든 후 테이블 내용을 출력한다. set serveroutput ondeclarecursor emp_cursor1 is select * from emp;beginfor emp_record in emp_cursor1 loopdbms_output.put_line(emp_record.ename||','||emp_record.job);end lo.. 2015. 10. 6. oracle sql 암시적 커서,명시적 커서,loop 커서암시적커서명시적커서 ▶속성SQL%ROWCOUNT:수행된 SQL 문의 결과로서 나온 행의 수를 반환한다. @암시적커서:SQL%ROWCOUNT set serveroutput ondeclarev_deptno number :=&del_no;emp_aa varchar2(40);begindelete from emp where deptno =v_deptno;dbms_output.put_line(SQL%ROWCOUNT ||'명이 삭제되었습니다.');end;/------------------------------------------------------------------- @명시적 커서(1)dept 테이블을 이용한 커서를 만들고 loop를 실행한다. declarev_deptno dept.deptno%type;v.. 2015. 10. 5. 이전 1 2 3 4 5 다음 728x90 반응형