Procedure

▣ Procedure

SQL> create procedure p1(su number)

2 as

3 begin

4 for i in 1..5 loop

5 dbms_output.put_line(i*su);

6 end loop;

7 end;

8 /

Procedure created.

   

SQL> sav p1

Created file p1.sql

   

▶ p1 임의로 에러 발생

create procedure p1(su number)

as

begin

for i in 1..5 loop

dbmsi_output.put_line(i*su);

end loop;

end;

/

▶ 실행

SQL> drop procedure p1;

   

Procedure dropped.

   

SQL> @p1

   

Warning: Procedure created with compilation errors.

   

SQL> show error => PL/SQL 에러 확인 방법

Errors for PROCEDURE P1:

   

LINE/COL ERROR

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

5/3 PL/SQL: Statement ignored

5/3 PLS-00201: identifier 'DBMSI_OUTPUT.PUT_LINE' must be declared

   

▶ create or replace

SQL> conn scott/tiger

Connected.

SQL> @p1

   

Procedure created.

   

SQL> @p1

create procedure p1(su number)

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

   

해결책 => create or replace

SQL> create or replace view view1 as select * from dept;

   

View created.

   

SQL> create or replace view view1 as select * from dept;

   

View created.

p1 객체 수정

create or replace procedure p1(su number)

as

begin

for i in 1..5 loop

dbms_output.put_line(i*su);

end loop;

end;

/

   

▶ 구구단 출력

SQL> create or replace procedure gugu

2 as

3 begin

4 for i in 1..9 loop

5 dbms_output.put_line(3 || 'x' || i || '=' || 3*i);

6 end loop;

7 end;

8 /

   

Procedure created.

   

SQL> exec gugu

   

PL/SQL procedure successfully completed.

   

SQL> show serveroutput

serveroutput OFF

SQL> set serveroutput on

SQL> exec gugu

3x1=3

3x2=6

3x3=9

3x4=12

3x5=15

3x6=18

3x7=21

3x8=24

3x9=27

   

PL/SQL procedure successfully completed.