Default Argument

SQL> create or replace procedure p3(su number)

2 as

3 begin

4 dbms_output.put_line(su);

5 end;

6 /

   

Procedure created.

   

SQL> sav p3

Created file p3.sql

   

SQL> exec p3(3);

3

   

PL/SQL procedure successfully completed.

   

SQL> exec p3;

BEGIN p3; END;

   

*

ERROR at line 1:

ORA-06550: line 1, column 7:

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

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

   

SQL> ed p3

create or replace procedure p3(su number default 0) => 수정

as

begin

dbms_output.put_line(su);

end;

/

   

SQL> @p3

   

Procedure created.

   

SQL> exec p3

0

   

   

▣ testSet

drop table tm

/

drop sequence tm_s

/

CREATE TABLE TM(ID NUMBER(10),DATA VARCHAR2(30),

CONSTRAINT TM_PK PRIMARY KEY(ID))

/

create sequence tm_s

/

create or replace procedure tm_i(su number)as

begin

for i in 1..su loop

insert into tm values(tm_s.nextval,'aaaa');

commit;

end loop;

end;

/

exec tm_i(100)

/

select count(*) from tm

/

   

▶ 아래 테이블에서 data 컬럼이 글자 1개일 때 10만 row를 넣으면 몇 개의 block 을 사용할까? 글자가 30자 일경우에는?

S SCOTT> select count(distinct substr(rowid,1,15)) from tm;

COUNT(DISTINCTSUBSTR(ROWID,1,15))

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

                              164    =>   1자일 경우

S SYS> select count(distinct substr(rowid,1,15)) from tm;

COUNT(DISTINCTSUBSTR(ROWID,1,15))

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

                              564 =>   30자일 경우