분류 전체보기 (670) 썸네일형 리스트형 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.. 015 - [Oracle PL/SQL] Records - Nested Records Nested Records define 및 접근방법 drop table emp_tel; create table emp_tel (emp_id number primary key, full_name varchar2(20), mob1 varchar2(20), mob2 varchar2(20), landline varchar2(20) ) insert into emp_tel values (1,'London','+971505914999','+97125914777','+971508105499'); select * from emp_tel; /* EMP_ID FULL_NAME MOB1 MOB2 LANDLINE ---------- -------------------- -------------------- -----------.. 014 - [Oracle PL/SQL] Records - %rowtype select * from DEPARTMENTS where department_id=10; /* ------------------------------------------------------------ DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 */ # 테이블을 데이터는 제외하고 구조를 복사 create table copy_DEPARTMENTS as select * from DEPARTMENTS where 1=2; # 새로운 테이블에는 데이터가 없는것을 확인 select * from .. 이전 1 ··· 12 13 14 15 16 17 18 ··· 84 다음 목록 더보기
