전체 글 (667) 썸네일형 리스트형 026 - [Oracle PL/SQL] Exceptions PL/SQL Basic structure Handling Exceptions with PL/SQL # no exception case. declare v_first_name employees.first_name%type; begin select first_name into v_first_name from employees where employee_id=1; -- there is no emp_id=1 end; ------------------------------------------------- Error report - ORA-01403: no data found ORA-06512: at line 5 01403. 00000 - "no data found" *Cause: No data was found.. 025 - [Oracle PL/SQL] Cursor - FOR UPDATE & CURRENT OF select * from employees where employee_id in (100,200) order by 1 for update; # 현재 상황에서는 employees 테이블은 잠김상태가 되므로 다른 업데이트 및 삭제 등, # 수정을 할 수 없다. commit; # commit 명령어가 실행될때까지 변경할 수 없다. for update 실행화면 for update 때문에 다른 세션에서 update 문이 완료되지 않는다 commit 실행 다른 세션에서 update 문이 완료된다 롤백을 통해서 원래대로 복구 DECLARE CURSOR c_emp_dept30 is SELECT employee_id, first_name,salary FROM employees where department_id=30 f.. 024 - [Oracle PL/SQL] Cursor - Cursor with Parameters DECLARE CURSOR c_emp_dept(v_dept number) --here we defined the parameter without size is SELECT employee_id, first_name FROM employees where department_id=v_dept; v_empno employees.employee_id%type; v_first_name employees.first_name%type; BEGIN OPEN c_emp_dept(10); dbms_output.put_line('dept 10 contains:'); loop fetch c_emp_dept into v_empno, v_first_name; exit when c_emp_dept%notfound; dbms_out.. 023 - [Oracle PL/SQL] Cursor - For loop cursor # basic loop cursor DECLARE CURSOR c_emp_dept30 is SELECT employee_id, first_name FROM employees where department_id=30 order by employee_id; v_empno employees.employee_id%type; v_first_name employees.first_name%type; BEGIN OPEN c_emp_dept30; loop fetch c_emp_dept30 into v_empno, v_first_name; exit when c_emp_dept30%notfound; dbms_output.put_line(v_empno||' '||v_first_name); end loop; close c_em.. 022 - [Oracle PL/SQL] Cursor - Explicit Cursor Attributes # using c_emp%notfound, c_emp%isopen,c_emp%rowcount DECLARE CURSOR c_emp is SELECT employee_id,first_name FROM employees order by employee_id; v_empno employees.employee_id%type; v_first_name employees.first_name%type; BEGIN if c_emp%isopen then null; else open c_emp; end if; dbms_output.put_line('the counter for cursor now is '||c_emp%rowcount); loop fetch c_emp into v_empno, v_first_name; exit.. 021 - [Oracle PL/SQL] Cursor - Explicit Cursor Every SQL statement executed by the Oracle server has an associated individual cursor: * Implicit cursors: Declared and managed by PL/SQL for all DML and PL/SQL SELECT statements * Explicit cursors: Declared and managed by the programmer Explicit Cursor Operations You declare explicit cursors in PL/SQL when you have a SELECT statement that returns multiple rows. You can process each row returned.. 020 - [Oracle PL/SQL] Collections - Varray -- 간단한 샘플 declare type t_locations is varray(3) of varchar2(100); loc t_locations; begin loc:=t_locations('jordan','uae','Syria'); dbms_output.put_line(loc(1) ); dbms_output.put_line(loc(2) ); dbms_output.put_line(loc(3) ); end; ----------------------------------------------- jordan uae Syria # you can not extend the varray, it will give error declare type t_locations is varray(3) of varchar2(10.. 019 - [Oracle PL/SQL] Collections - Nested tables Nested tables • No index in nested table ( unlike index by table ) • It is valid data type in SQL ( unlike index by table, only used in PL/SQL ) • Initialization required/초기화 필수 • Extend required/값을 추가할 경우, extend 필수 • Can be stored in DB declare type t_locations is table of varchar2(100); # index is hidden in the memory loc t_locations; begin loc:=t_locations('USA','UK','JORDAN'); dbms_output.p.. 이전 1 ··· 11 12 13 14 15 16 17 ··· 84 다음
