출력 | | 출력문 켜기 | set serveroutput on |
---|
프로시져 | procedure | procedure 만들기 | create or replace procedure |
---|
반복 | for loop | 구구단 출력 (값 하나 입력 받아서 출력하는 구문) sav gugu2 | create or replace procedure gugu2(su number) as begin for i in 1..9 loop dbms_output.put_line(su || 'X' || i || '=' || su*i); end loop; end; /
|
---|
출력 | dbms_output.put_line | | exec dbms_output.put_line('test'); |
---|
선언 | declare begin end; as begin end; | 변수 선언 anonymous 블럭에서는 declare 와 begin 사이에 선언. procedure 에서는 as와 begin 사이에 선언. sav p2 | declare x number(4); y number(4); begin x:=3; y:=10; dbms_output.put_line(x+y); end; /
선언, 정의 한번에 가능 declare x number(4) :=3; y number(4) :=10; begin -- x:=3; -- y:=10; dbms_output.put_line(x+y); end; / create or replace procedure p2(su number) as x number(4) :=10; begin dbms_output.put_line(x+su); end; /
|
---|
function | create or replace function | 함수생성 sav f1 | create or replace function f1(s1 number, s2 number) return number as begin return s1+s2; end; /
|
---|
function | create or replace function | 세금 구하는 function 세금 3.3% sav tax | create or replace function tax(sal number) return number as begin if sal > 1000 then return sal*0.033; else return 0; end if; end; /
select sal, tax(sal) TAX from emp; |
---|
매개변수 | default | 매개변수 넣지 않을시 default 값 사용 sav p3 | create or replace procedure p3(su number default 0) as begin dbms_output.put_line(su); end; /
|
---|
procedure | 테이블 생성 | 대용량 테이블 생성 sav 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,data varchar2 default 'abcedfg')as begin for i in 1..su loop insert into tm values(tm_s.nextval,data); commit; end loop; end; / exec tm_i(100) / select count(*) from tm /
|
---|
| execute immediate | DDL 문 사용 | begin execute immediate 'create table xxxxx(x date)'; end; /
|
---|
argument | out | argument 로 값 받기 | create or replace procedure p4(su in out number) as begin su:=0; end; / var x number; exec :x := 40; exec p4(x) print x; |
---|
선언 | type, rowtype | - 데이터 유형을 찾기 귀찮아서.
- 향후 alter table문에 의해 column 정의가 바뀌었을 경우 대비
| declare enamex emp.ename%type; deptx dept%rowtype; begin enamex :='Blake'; deptx.deptno := 52; deptx.dname := 'sales2'; deptx.loc := 'seoul'; dbms_output.put_line(enamex || deptx.dname); end; / |
---|
procedure | rowtype select into | select into가 row가 한줄인 것 또는 vector값만 되나 rowtype를 사용해서 vector 값을 넣어서 사용 가능 sav depts | create or replace procedure depts(su dept.deptno%type) as depts dept%rowtype; begin select dname,loc into depts.dname, depts.loc from dept where deptno=su; dbms_output.put_line('dname= ' || depts.dname); dbms_output.put_line('loc= ' || depts.loc); end; /
|
---|
procedure | cursor execute immediate | scott.emp.enme로 user 만들기 | conn / as sysdba; create or replace procedure createU as cursor createU_cur is select * from scott.emp where ename <> 'SCOTT'; begin for urow in createU_cur loop execute immediate 'create user '||urow.ename||' identified by "1"'; dbms_output.put_line('create '||urow.ename); execute immediate 'grant create session to '||urow.ename; end loop; end; / |
---|
procedure | execute immediate | scott.emp.enme로 만든 user 삭제 | conn / as sysdba; create or replace procedure dropU as cursor dropU_cur is select * from scott.emp where ename <> 'SCOTT'; begin for urow in dropU_cur loop execute immediate 'drop user '||urow.ename; dbms_output.put_line('drop '||urow.ename); end loop; end; / |
---|
procedure | execute immediate | 테이블 생성 | create or replace procedure createT(tname varchar2 default 'a',su number default null) as begin if su >=0 then for i in 0..su loop execute immediate 'create table '||tname||i||'(id number(10),data varchar2(20))'; end loop;
elsif su = 0 then execute immediate 'create table '||tname||su||'(id number(10),data varchar2(20))'; else execute immediate 'create table '||tname||'(id number(10),data varchar2(20))'; end if; end; /
|
---|
procedure | execute immediate | 테이블 삭제 | create or replace procedure dropT(tname varchar2,su number default null) as begin if su >=0 then for i in 0..su loop execute immediate 'drop table '||tname||i; end loop; elsif su = 0 then execute immediate 'drop table '||tname||su; else execute immediate 'drop table '||tname; end if; end; /
|
---|
cursor | cursor fetch open %ISOPEN %NOTFOUND %ROWCOUNT | cursor의 기본형 sav empd | create or replace procedure empd(dn emp.deptno%type) as cursor ec is select * from emp where deptno=dn; e emp%rowtype; begin if ec%ISOPEN = FALSE then open ec; end if; loop fetch ec into e; exit when ec%NOTFOUND; dbms_output.put_line(e.ename ||' '||e.sal); end loop; dbms_output.put_line('count = '||ec%ROWCOUNT); end; /
|
---|
cursor | close | 값을 변환해서 사용할 수 있다. | declare dn emp.deptno%type; cursor ec is select * from emp where deptno=dn; e emp%rowtype; begin dn := 10; open ec; loop fetch ec into e; exit when ec%NOTFOUND; dbms_output.put_line(e.ename ||' '||e.sal); end loop; dbms_output.put_line('count = '||ec%ROWCOUNT); close ec; dn := 20; open ec; loop fetch ec into e; exit when ec%NOTFOUND; dbms_output.put_line(e.ename ||' '||e.sal); end loop; dbms_output.put_line('count = '||ec%ROWCOUNT); end; / |
---|
exception | RAISE | user_define_error sal을 올리는데 500 이하면 rollback | create or replace procedure raiseSal( empnox emp.empno%type, raiseSal number) as user_define_error EXCEPTION; begin update emp set sal=sal+raiseSal where empno=empnox; for e in (select sal from emp where empno=empnox) loop if e.sal <=500 then RAISE user_define_error; else commit; end if; end loop; exception when user_define_error then ROLLBACK; dbms_output.put_line('rollback'); end; /
|
---|
package | myPackage package | package와 package body 같이 붙여서 만들 것. exec myPackage.gugu(3) | create or replace package myPackage is procedure gugu2(su number); procedure print(st varchar2); end; / |
---|
package | myPackage package body | exec myPackage.print('aaaa') | create or replace package body myPackage is procedure gugu2(su number) as begin for i in 1..9 loop dbms_output.put_line(su || 'X' || i || '=' || su*i); end loop; end; procedure print(st varchar2) as begin dbms_output.put_line(st); end; end; / |
---|
DML | | hr 계정에 emp 만들기 | conn / as sysdba create table hr.emp as select * from scott.emp; create table hr.dept as select * from scott.dept; conn hr/hr alter table emp add constraint pk_emp primary key(empno); alter table dept add constraint pk_dept primary key(deptno); alter table emp add constraint fk_emp foreign key(deptno) references dept(deptno); create sequence emps start with 7950; create sequence depts start with 60 increment by 10;
|
---|
function | empInsert | insert package | create or replace function empInsert( pEname emp.ename%type, pJob emp.job%type, pMgr emp.mgr%type, pHiredate emp.hiredate%type, Psal emp.sal%type, Pcomm emp.comm%type, PDeptno emp.deptno%type) return emp.empno%type as ret emp.empno%type; begin insert into emp values(emps.nextval, pEname,pJob,pMgr,pHiredate,Psal,Pcomm,PDeptno); select emps.currval into ret from dual; commit; return ret; exception when others then if SQLCODE=-2291 then dbms_output.put_line('no FK...ERROR '); return null; end if; end; /
|
---|
function | empUpdate | update package | create or replace function empUpdate( pEmpno emp.empno%type, pEname emp.ename%type, pJob emp.job%type, pMgr emp.mgr%type, pHiredate emp.hiredate%type, Psal emp.sal%type, Pcomm emp.comm%type, PDeptno emp.deptno%type) return number as updateCount number(10):=0; begin update emp set empno=pEmpno, ename=pEname, job=pJob, mgr=pMgr, hiredate=pHiredate, sal=Psal, comm=Pcomm, deptno=PDeptno where empno=pEmpno; select count(*) into updateCount from emp where empno=pEmpno; return updateCount; end; /
|
---|
procedure | empdelete emp | delete package | create or replace procedure empDelete(pEmpno emp.empno%type) as begin delete from emp where empno= pEmpno; commit; end; /
|
---|
DML | truncate emp | truncate emp | commit; drop table emp; drop table dept; drop sequence emps; drop sequence depts; @@empCreate.sql; truncate table emp; delete from dept; commit; |
---|
DML | drop emp package | drop emp | rop table emp; drop table dept; drop sequence emps; drop sequence depts; drop package empDML; |
---|
반복 | case | decode 함수 대신해서 case 문 사용 | create or replace procedure CaseTest(dn dept.deptno%type) as empr emp%rowtype; cursor ec is select * from emp where deptno=dn; begin open ec; loop fetch ec into empr; exit when ec%NOTFOUND; case dn when 10 then dbms_output.put_line(empr.ename||' '||'ACCOUNT'); when 20 then dbms_output.put_line(empr.ename||' '||'RESEARCH'); when 30 then dbms_output.put_line(empr.ename||' '||'SALES'); when 40 then dbms_output.put_line(empr.ename||' '||'OPERATIONS'); else dbms_output.put_line('No such dname'); end case; end loop; close ec; end; /
|
---|
반복 | while | loop until sal > 15000 | WHILE sal <= 15000 LOOP SELECT salary, manager_id, last_name INTO sal, mgr_id, lname FROM employees WHERE employee_id = mgr_id; END LOOP; |
---|
반복 | while | boolean 으로도 사용가능 If counter > 500, DONE = TRUE | DECLARE done BOOLEAN; counter NUMBER := 0; BEGIN done := FALSE; WHILE done != TRUE LOOP counter := counter + 1; done := (counter > 500); END LOOP; END; / |
---|
반복 | while | IS NOT NULL | WHILE counter IS NOT NULL LOOP ~~~~~~ END LOOP; |
---|