DDL과 같이 사용하는 PL/SQL

SQL> create or replace procedure idept(

2 deptno number,

3 dname varchar2,

4 loc varchar2

5 ) as

6 begin

7 insert into scott.dept values(deptno,dname,loc);

8 commit;

9 end;

10 /

   

Procedure created.

   

SQL> sav idept

Created file idept.sql

SQL> exec idept(51,'play','seoul');

   

PL/SQL procedure successfully completed.

   

SQL> select * from dept;

   

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

51 play seoul

   

   

▶ 아래 테이블에 첫 번째 Argument의 숫자만큼 row를 두 번째 Argument에 문자열로 삽입하는 procedure tt를 만드시오

SQL> create table t(id number(10),data varchar2(50),

2 constraint t_pk primary key(id));

   

Table created.

   

SQL> create sequence ts;

   

Sequence created.

   

SQL> create or replace procedure tt(

2 id number,

3 data varchar2

4 ) as

5 begin

6 for i in 1..id loop

7 insert into t values(ts.nextval,data);

8 commit;

9 end loop;

10 end;

11 /

   

Procedure created.

   

SQL> sav tt

Created file tt.sql

SQL> exec tt(100,'abcd');

   

PL/SQL procedure successfully completed.