Database (237) 썸네일형 리스트형 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.. 018 - [Oracle PL/SQL] Collections - Index by Table of records # index by table of record declare type tab_no is table of employees%rowtype index by pls_integer; v_tab_no tab_no; v_total number; begin v_tab_no(1).employee_id:=1; v_tab_no(1).first_name:='ahmed'; v_tab_no(1).last_name:='jad'; v_tab_no(2).employee_id:=2; v_tab_no(2).first_name:='khaled'; v_tab_no(2).last_name:='yaser'; dbms_output.put_line(v_tab_no(1).employee_id||' '||v_tab_no(1).first_name||.. 017 - [Oracle PL/SQL] Collections - Index by tables # method 사용 예시 코드 declare type tab_no is table of varchar2(100) index by pls_integer; v_tab_no tab_no; v_total number; begin v_tab_no(1):='khaled'; v_tab_no(2):='ahmed'; v_tab_no(3):='jad'; v_tab_no(6):='nader'; v_tab_no(5):='joan'; for i in 1..10 loop if v_tab_no.exists(i) then dbms_output.put_line('the element '||i||' is exist in the array and='||v_tab_no(i)); else dbms_output.put_line('the el.. 016 - [Oracle PL/SQL] Collections - Index by tables(Associative arrays) # 숫자 인덱스 declare type tab_no is table of varchar2(20) index by pls_integer; v_tab_no tab_no; begin v_tab_no(1):='aaa'; v_tab_no(6):='bbb'; v_tab_no(4):='ccc'; dbms_output.put_line(v_tab_no(1)); dbms_output.put_line(v_tab_no(6)); dbms_output.put_line(v_tab_no(4)); end; ------------------------- aaa bbb ccc # 문자열 인덱스 declare type tab_no is table of number index by varchar2(20); v_tab_no tab_no; be.. 이전 1 ··· 10 11 12 13 14 15 16 ··· 30 다음
