'스마트웹&콘텐츠 개발자 과정/PL SQL' 카테고리의 글 목록

스마트웹&콘텐츠 개발자 과정/PL SQL

마지막 수업 2018.07.30
PL/SQL 1일차 2018.07.23

마지막 수업

2018. 7. 30. 11:31

[oracle@localhost ~]$ set pagesize 1000

[oracle@localhost ~]$ set long 9

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 30 09:18:11 2018

Connected to an idle instance.

SYS> startup

ORACLE instance started.

Total System Global Area 623546368 bytes

Fixed Size 1338308 bytes

Variable Size 469763132 bytes

Database Buffers 146800640 bytes

Redo Buffers 5644288 bytes

Database mounted.

Database opened.

SYS> conn hr/hr

Connected.

HR> select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)

2 from dba_tablespaces;

from dba_tablespaces

ERROR at line 2:

ORA-00942: table or view does not exist

HR> conn / as sysdba

Connected.

SYS>

SYS> select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)

2 from dba_tablespaces;

DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME)

--------------------------------------------------------------------------------

CREATE TABLESPACE "SYSTEM" DATAFILE

'/u01/app/oracle/oradata/orcl/system01

CREATE TABLESPACE "SYSAUX" DATAFILE

'/u01/app/oracle/oradata/orcl/sysaux01

CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE

'/u01/app/oracle/oradata/orcl/u

DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME)

--------------------------------------------------------------------------------

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE

'/u01/app/oracle/oradata/orcl/

CREATE TABLESPACE "USERS" DATAFILE

'/u01/app/oracle/oradata/orcl/users01.d

CREATE TABLESPACE "NEW_USERS" DATAFILE

DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME)

--------------------------------------------------------------------------------

'/u01/app/oracle/product/11.2.0/db_

CREATE TABLESPACE "EMP_TBS1" DATAFILE

'/u01/app/oracle/oradata/orcl/emp_tb

CREATE TABLESPACE "EMP_TBS2" DATAFILE

'/u01/app/oracle/oradata/orcl/emp_tb

8 rows selected.

SYS> SYS>

SYS>

SYS> select dbms_metadata.get_ddl('TABLE','DEPARTMENTS','HR') from dual;

DBMS_METADATA.GET_DDL('TABLE','DEPARTMENTS','HR')

--------------------------------------------------------------------------------

CREATE TABLE "HR"."DEPARTMENTS"

( "DEPARTMENT_ID" NUMBER(4,0),

"DEPARTM

SYS> !ls

afiedt.buf ex_07_18.sql lab_04_03.sql lab_05_04.sql lab_08_03.sql lab2_01_04.sql m_05_01.sql m_05_05b.sql q_dbf.sql test_hjs_03.sql

Desktop ex_07_22.sql lab_05_01_2.sql lab_06_01.sql lab2_01_01.sql lab2_02_01.sql m_05_02.sql m_05_06.sql q_log.sql test_hjs_04.sql

ex_06_12.sql ex_07_m.sql lab_05_01.sql lab_07_02.sql lab2_01_02.sql lab2_02_02.sql m_05_03.sql m_07_02.sql q_tbs.sql test_hjs_05.sql

ex_06_14.sql job_pkg.plb lab_05_02_2.sql lab_08_01.sql lab2_01_03.sql lab2_02_03a.sql m_05_04.sql oradiag_oracle test_hjs_01.sql test_hjs_06.sql

ex_07_16.sql job_pkg.sql lab_05_02.sql lab_08_02.sql lab2_01_04b.sql labs m_05_05a.sql over_load.sql test_hjs_02.sql

SYS> !mkdir directory

SYS> !ls

afiedt.buf ex_07_16.sql job_pkg.sql lab_05_02.sql lab_08_02.sql lab2_01_04b.sql labs m_05_05a.sql over_load.sql test_hjs_02.sql

Desktop ex_07_18.sql lab_04_03.sql lab_05_04.sql lab_08_03.sql lab2_01_04.sql m_05_01.sql m_05_05b.sql q_dbf.sql test_hjs_03.sql

directory ex_07_22.sql lab_05_01_2.sql lab_06_01.sql lab2_01_01.sql lab2_02_01.sql m_05_02.sql m_05_06.sql q_log.sql test_hjs_04.sql

ex_06_12.sql ex_07_m.sql lab_05_01.sql lab_07_02.sql lab2_01_02.sql lab2_02_02.sql m_05_03.sql m_07_02.sql q_tbs.sql test_hjs_05.sql

ex_06_14.sql job_pkg.plb lab_05_02_2.sql lab_08_01.sql lab2_01_03.sql lab2_02_03a.sql m_05_04.sql oradiag_oracle test_hjs_01.sql test_hjs_06.sql

SYS> !

[oracle@localhost ~]$ ls

afiedt.buf ex_07_16.sql job_pkg.sql lab_05_02.sql lab_08_02.sql lab2_01_04b.sql labs m_05_05a.sql over_load.sql test_hjs_02.sql

Desktop ex_07_18.sql lab_04_03.sql lab_05_04.sql lab_08_03.sql lab2_01_04.sql m_05_01.sql m_05_05b.sql q_dbf.sql test_hjs_03.sql

directory ex_07_22.sql lab_05_01_2.sql lab_06_01.sql lab2_01_01.sql lab2_02_01.sql m_05_02.sql m_05_06.sql q_log.sql test_hjs_04.sql

ex_06_12.sql ex_07_m.sql lab_05_01.sql lab_07_02.sql lab2_01_02.sql lab2_02_02.sql m_05_03.sql m_07_02.sql q_tbs.sql test_hjs_05.sql

ex_06_14.sql job_pkg.plb lab_05_02_2.sql lab_08_01.sql lab2_01_03.sql lab2_02_03a.sql m_05_04.sql oradiag_oracle test_hjs_01.sql test_hjs_06.sql

[oracle@localhost ~]$ rm -rf directory/

[oracle@localhost ~]$ ls

afiedt.buf ex_07_18.sql lab_04_03.sql lab_05_04.sql lab_08_03.sql lab2_01_04.sql m_05_01.sql m_05_05b.sql q_dbf.sql test_hjs_03.sql

Desktop ex_07_22.sql lab_05_01_2.sql lab_06_01.sql lab2_01_01.sql lab2_02_01.sql m_05_02.sql m_05_06.sql q_log.sql test_hjs_04.sql

ex_06_12.sql ex_07_m.sql lab_05_01.sql lab_07_02.sql lab2_01_02.sql lab2_02_02.sql m_05_03.sql m_07_02.sql q_tbs.sql test_hjs_05.sql

ex_06_14.sql job_pkg.plb lab_05_02_2.sql lab_08_01.sql lab2_01_03.sql lab2_02_03a.sql m_05_04.sql oradiag_oracle test_hjs_01.sql test_hjs_06.sql

ex_07_16.sql job_pkg.sql lab_05_02.sql lab_08_02.sql lab2_01_04b.sql labs m_05_05a.sql over_load.sql test_hjs_02.sql

[oracle@localhost ~]$ mkdir reports_dir

[oracle@localhost ~]$ exit

exit

SYS> create directory reports_dir as '/home/oracle/reports_dir';

Directory created.

SYS> !ls /home/oracle/reports_dir

SYS> desc utl_file

PROCEDURE FCLOSE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN/OUT

ID BINARY_INTEGER IN/OUT

DATATYPE BINARY_INTEGER IN/OUT

BYTE_MODE BOOLEAN IN/OUT

PROCEDURE FCLOSE_ALL

PROCEDURE FCOPY

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SRC_LOCATION VARCHAR2 IN

SRC_FILENAME VARCHAR2 IN

DEST_LOCATION VARCHAR2 IN

DEST_FILENAME VARCHAR2 IN

START_LINE BINARY_INTEGER IN DEFAULT

END_LINE BINARY_INTEGER IN DEFAULT

PROCEDURE FFLUSH

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

PROCEDURE FGETATTR

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

LOCATION VARCHAR2 IN

FILENAME VARCHAR2 IN

FEXISTS BOOLEAN OUT

FILE_LENGTH NUMBER OUT

BLOCK_SIZE BINARY_INTEGER OUT

FUNCTION FGETPOS RETURNS BINARY_INTEGER

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

FUNCTION FOPEN RETURNS RECORD

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

ID BINARY_INTEGER OUT

DATATYPE BINARY_INTEGER OUT

BYTE_MODE BOOLEAN OUT

LOCATION VARCHAR2 IN

FILENAME VARCHAR2 IN

OPEN_MODE VARCHAR2 IN

MAX_LINESIZE BINARY_INTEGER IN DEFAULT

FUNCTION FOPEN_NCHAR RETURNS RECORD

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

ID BINARY_INTEGER OUT

DATATYPE BINARY_INTEGER OUT

BYTE_MODE BOOLEAN OUT

LOCATION VARCHAR2 IN

FILENAME VARCHAR2 IN

OPEN_MODE VARCHAR2 IN

MAX_LINESIZE BINARY_INTEGER IN DEFAULT

PROCEDURE FREMOVE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

LOCATION VARCHAR2 IN

FILENAME VARCHAR2 IN

PROCEDURE FRENAME

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SRC_LOCATION VARCHAR2 IN

SRC_FILENAME VARCHAR2 IN

DEST_LOCATION VARCHAR2 IN

DEST_FILENAME VARCHAR2 IN

OVERWRITE BOOLEAN IN DEFAULT

PROCEDURE FSEEK

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN/OUT

ID BINARY_INTEGER IN/OUT

DATATYPE BINARY_INTEGER IN/OUT

BYTE_MODE BOOLEAN IN/OUT

ABSOLUTE_OFFSET BINARY_INTEGER IN DEFAULT

RELATIVE_OFFSET BINARY_INTEGER IN DEFAULT

PROCEDURE GET_LINE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

BUFFER VARCHAR2 OUT

LEN BINARY_INTEGER IN DEFAULT

PROCEDURE GET_LINE_NCHAR

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

BUFFER NVARCHAR2 OUT

LEN BINARY_INTEGER IN DEFAULT

PROCEDURE GET_RAW

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

BUFFER RAW OUT

LEN BINARY_INTEGER IN DEFAULT

FUNCTION IS_OPEN RETURNS BOOLEAN

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

PROCEDURE NEW_LINE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

LINES BINARY_INTEGER IN DEFAULT

PROCEDURE PUT

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

BUFFER VARCHAR2 IN

PROCEDURE PUTF

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

FORMAT VARCHAR2 IN

ARG1 VARCHAR2 IN DEFAULT

ARG2 VARCHAR2 IN DEFAULT

ARG3 VARCHAR2 IN DEFAULT

ARG4 VARCHAR2 IN DEFAULT

ARG5 VARCHAR2 IN DEFAULT

PROCEDURE PUTF_NCHAR

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

FORMAT NVARCHAR2 IN

ARG1 NVARCHAR2 IN DEFAULT

ARG2 NVARCHAR2 IN DEFAULT

ARG3 NVARCHAR2 IN DEFAULT

ARG4 NVARCHAR2 IN DEFAULT

ARG5 NVARCHAR2 IN DEFAULT

PROCEDURE PUT_LINE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

BUFFER VARCHAR2 IN

AUTOFLUSH BOOLEAN IN DEFAULT

PROCEDURE PUT_LINE_NCHAR

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

BUFFER NVARCHAR2 IN

PROCEDURE PUT_NCHAR

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

BUFFER NVARCHAR2 IN

PROCEDURE PUT_RAW

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

FILE RECORD IN

ID BINARY_INTEGER IN

DATATYPE BINARY_INTEGER IN

BYTE_MODE BOOLEAN IN

BUFFER RAW IN

AUTOFLUSH BOOLEAN IN DEFAULT

SYS> CREATE OR REPLACE PROCEDURE create_user (

2 p_user_name VARCHAR2, p_

3 BEGIN

4 END;

5 /

Warning: Procedure created with compilation errors.

SYS> ED

Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE create_user (

2 p_user_name VARCHAR2) IS

3 BEGIN

4 execute immediate 'CREATE USER ' || p_user_name || ' IDENTIFIED BY ' || p_user_name);

5* END;

SYS> /

Warning: Procedure created with compilation errors.

SYS> show error

Errors for PROCEDURE CREATE_USER:

LINE/COL ERROR

-------- -----------------------------------------------------------------

4/86 PLS-00103: Encountered the symbol ")" when expecting one of the

following:

. ( * @ % & = - + ; < / > at in is mod remainder not rem

return returning <an exponent (**)> <> or != or ~= >= <= <>

and or like like2 like4 likec between into using || bulk

member submultiset

The symbol "(" was substituted for ")" to continue.

SYS> ed

Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE create_user (

2 p_user_name VARCHAR2) IS

3 BEGIN

4 execute immediate 'CREATE USER ' || p_user_name || ' IDENTIFIED BY ' || p_user_name;

5* END;

SYS> /

Procedure created.

SYS> exec create_user

BEGIN create_user; END;

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'CREATE_USER'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SYS> exec create_user('hjs');

PL/SQL procedure successfully completed.

SYS> desc dba_users;

Name Null? Type

----------------------------------------------------------------- -------- --------------------------------------------

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

PASSWORD VARCHAR2(30)

ACCOUNT_STATUS NOT NULL VARCHAR2(32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2(30)

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)

EXTERNAL_NAME VARCHAR2(4000)

PASSWORD_VERSIONS VARCHAR2(8)

EDITIONS_ENABLED VARCHAR2(1)

AUTHENTICATION_TYPE VARCHAR2(8)

SYS> select username from dba_users;

USERNAME

------------------------------

MGMT_VIEW

SYS

SYSTEM

DBSNMP

SYSMAN

JSHONG

HJS

OUTLN

USERNAME

------------------------------

WMSYS

APPQOSSYS

DIP

ORACLE_OCM

15 rows selected.

SYS> set timing on

SYS> alter system set cursor_sharing = EXACT;

System altered.

Elapsed: 00:00:00.04

SYS> create tablespace query_data

2 datafile '/u01/app/oracle/oradata/orcl/query_data01.dbf' size 100m

3 autoextend on next 10m maxsize 500m;

Tablespace created.

Elapsed: 00:00:03.68

SYS> connect hr/hr

Connected.

HR> create table big_table (c1 number, c2 varchar2(200)) tablespace query_data;

Table created.

Elapsed: 00:00:00.06

HR> declare

2 i number;

3 str varchar2(200);

4 begin

5 for i in 1..100000 loop

6 str := 'insert into big_table values ('||i||', RPAD('||i||', 100, ''*''))';

7 execute immediate str;

8 end loop;

9 end;

10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:28.39

HR> truncate table big_table;

Table truncated.

Elapsed: 00:00:00.46

HR> declare

2 i number;

3 str varchar2(200);

4 begin

5 str:= 'insert into big_table values (:x1, :x2)';

6 for i in 1..100000 loop

7 execute immediate str using i, RPAD(i, 100, '*');

8 end loop;

9 end;

10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.43

HR> set timing off

HR>

저작자표시 비영리 변경금지

'스마트웹&콘텐츠 개발자 과정 > PL SQL' 카테고리의 다른 글

PL/SQL 1일차 (0)	2018.07.23

PL/SQL 1일차

2018. 7. 23. 10:04

> sqlplus / as sysdba > startup > conn hr/hr

HR> select first_name from employees where employee_id=100;

FIRST_NAME

--------------------------------------------------

Steven

HR> DECLARE

2 v_fname VARCHAR2(20);

3 BEGIN

4 SELECT first_name INTO v_fname FROM employees

5 WHERE employee_id=100;

6 END;

7 /

PL/SQL procedure successfully completed.

HR> ed

Wrote file afiedt.buf

1 DECLARE

2 v_fname VARCHAR2(20);

3 BEGIN

4 SELECT first_name INTO v_fname FROM employees

5 WHERE employee_id=100;

6* END;

HR> show serveroutput

serveroutput OFF

HR> set serveroutput on

HR> ed

Wrote file afiedt.buf

1 DECLARE

2 v_fname VARCHAR2(20);

3 BEGIN

4 SELECT first_name INTO v_fname FROM employees

5 WHERE employee_id=100;

6 DBMS_OUTPUT.PUT_LINE(' The First Name of the Employee is ' || v_fname);

7* END;

HR> /

The First Name of the Employee is Steven

PL/SQL procedure successfully completed.

HR> BEGIN

HR> DBMS_OUTPUT.PUT_LINE('Hello World');

SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.

HR> begin

2 dbms_output.put_line('Hello World');

3 end;

4 /

Hello World

PL/SQL procedure successfully completed.

HR> save lab_01_02

Created file lab_01_02.sql

* 변수에 & 해서 old, new값 안 볼 때 : set verify off

* procedure successfully ~ 보기 싫을 때 : set feedback off

declare

v_maxdid departments.department_id%type;

v_rows varchar2(100);

begin

select max(department_id)

into v_maxdid

from departments;

dbms_output.put_line('Maximum department id is '||v_maxdid);

insert into departments

values (v_maxdid+10, '&b_dname', null, null);

v_rows := sql%rowcount || ' row inserted';

dbms_output.put_line(v_rows);

end;

저작자표시 비영리 변경금지

'스마트웹&콘텐츠 개발자 과정 > PL SQL' 카테고리의 다른 글

마지막 수업 (0)	2018.07.30

PREV 1 NEXT

레도널스의 개발자 되어가기