728x90 반응형 개발/sql49 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. oracle sql (1)variable month_sal number;declarev_sal number(8) :=1800;begin:month_sal :=v_sal/12;end;/print month_sal;---------------------------------(2)set serveroutput oned d:\test1.sqldeclare v_sal number(8) :=3000;month_sal number(8);beginmonth_sal :=v_sal/12;dbms_output.put_line('내 급여는'||to_char(month_sal));end;/set serveroutput on@test1.sql (3)declarev_deptno dept.deptno%type;v_dname dept.dname%type.. 2015. 10. 5. 이전 1 ··· 5 6 7 8 9 10 11 ··· 13 다음 728x90 반응형