[PL/SQL] 기본문법

출처 : http://blog.naver.com/cdwsorez?Redirect=Log&logNo=90080261599

----------- 기본 문법 -----------
--조건문 if
--형식
-- if 조건 then
--- 명령
-- elsif 조건 then
-- 명령...
-- else
-- 명령..
-- end if;

--반복문(for -loop)
--형식 (증가치가 없다.)
-- for 변수 in 초기값.. 최대값 loop
-- 명령;
-- end loop;

--반복문(while-loop)
--while 조건 loop
--명령
--end loop;
--반복문(loop)
-- loop
-- exit when(i>50); 이런문법도 있다. 거의 종료문에만 쓴다.
-- end loop

--예제
--set serveroutput on

--declare
-- i number(2) := 1;
--begin
-- for i in 1..10 loop
-- dbms_output.put_line('for문'|| i);
-- end loop;
-- i:=0;
-- while i<=10 loop
-- dbms_output.put_line('while문'|| i);
-- i:=i+1;
-- end loop;
-- i:=0;
-- loop
--
-- i:=i+1;
-- exit when(i>10);
-- dbms_output.put_line('loop문'|| i);
-- end loop;
--end;
--/
----------------------------------------------------------------------------------------------------------------------------------
----------- 변수 선언 -----------
--declare
-- v_hiradate date;
-- v_dept naumber(2) not null :=10; //낫널 제약을 걸수있다.
-- v_inc varchar2(10) :='seoul'; //초기값을 문자열로 줄수있다.
-- v_comm constant number :=10; //이렇게 constant로 되어있으면 반드시 초기값을 넣어야한다.
--------
-- v_name sawon.saname%type; // 중요하다.디비에 있는 값을 사용해야할때 그 타입을 저것처럼 알아 올수있다.타입도 같아지고 길이도 같아진다.
-- // 단 이구문은 초기화는 하지 않았다. 아마 할수 있는 방법이 있을듯하다.
-- w sawon%rowtype; //이렇게 하면 한방에 sawon 테이블에 있는 row를 전부 가지고 온다.
-- //사용할때는 w.saname 이런식으로 가지고올수있다. 물론 타입도 같고 길이도 같다.
----------------------------------------------------------------------------------------------------------------------------------
--★★---------------------------------------------------------★★
--컬럼명은 변수로 사용할 수 없다. 절대로.
--select 컬럼 ....into 변수 ...from~~~;
--accept 변수 prompt '문자열' //이문장은 ;을 적어서는 안된다.
--ex)accept no prompt '사번을 입력->'

--set verify off //sql 쿼리문 안보이게 하는것

--사번을 입력받아서 사원의 이름과 급여를 출력
--1.입력받는 명령이 필요
--2.DB에 있는 행정보를 가지고오는것 해당 산원의 정보를 가져오는 방법
--★★---------------------------------------------------------★★

set serveroutput on
set verify off
accept no prompt '사번을 입력->'
declare
v_no number(2) := 0;
v_name sawon.saname%type;
v_sapay sawon.sapay%type;
begin
select saname,sapay into v_name,v_sapay from sawonㅂ
where sabun=&no;
dbms_output.put_line('사원이름:'||v_name);
dbms_output.put_line('사원급여:'||v_sapay);
end;
/
*********************************************************
sql%rowcount; //이것 자체가 실행된 행수를 반환한다.
sql프롬프트창에 var res varchar2(40) 이렇게 입력하면
plssql에서 :var 데이터를 받아올수있다.
SQL> var res varchar2(40)
set serveroutput on
set verify off
accept no prompt '사번을 입력->'
declare
row_cnt number(3);
v_name sawon_ex.saname%type;
begin
select saname into v_name from sawon_ex where sabun=&no;
row_cnt:=sql%rowcount;
:res:= to_char(row_cnt)||'명있습니다';
end;
/
SQL> print var //이렇게
**********************************************************

//커서 정의
무엇이냐.. 지금까지는 하나의 행에대한 처리를 했었다
각 행마다 다른 처리를 하면 쓸수가 없다. 그런데
행마다 동일한 작업을 한다면 커서를 정의해서 쓸수있다.

------------------------------------커서의 기본 템플릿이다...
set serveroutput on
set verify off
declare
cursor cur_1 is select saname,sapay,SANIVE from sawon; --커서의 정의이다.
begin
if cur_1%isopen then
close cur_1; --시작하기전에 일단 한번 닫아주자
end if;
open cur_1; --깔끔하게 새로 연결
loop
fetch cur_1 into v_name,v_pay,v_hiredate; --문장을 패치해온다..^^ 한열씩...
if cur_1%notfound then --패치가 없으면 끝내자...
exit;
end if;
end loop;
end;
/
--to_char(temp,'00');요거는 temp의 출력형식을 결정하는 것이다.

///커서를 생성할때 실제 존재하지 않는 컬럼을 생성할수있는데 이때는 반드시 별칭을 사용해야된다.
*********** 커서에서의 for문 ***********
for 카운트변수 in 커서명 loop
명령
end loop
---------------for문 템플릿----------------------------------------
set serveroutput on
set verify off
declare
cursor cur_1 is select saname,sapay,SANIVE from sawon;
begin
for i in cur_1 loop
-- dbms_output.put_line(v_name||v_par_rk);

end loop;
end;
/
-------------------------------------------------------------------

**프로시져
--자주실행되는 sql/pls문을 미리 컴파일해서 데ㅣ터 베이스에 저장(생성시 한번만 컴파일)->서버
--서버에서 생성된 프로시저는 클라이언트에서 호출(실행)은된다->응용 프로그램에서 많이 사용
--테이블이 아닌 프로스지 단위의 사용권한을 부여하기 때문에 보안 기능을 강화 할 수 있다.
--프로싀져 생성->컴파일->호출(sqlplus or app)
--실제로 하나의 프로시져를 여러 사용자가 호출하더라도 메모리 공유->메모리 적약. static인듯.
--속도가 빠르다.
-- 복잡한 계산을 하기 위한 목적이 아니라 데이터를 변경하기 위한 목적으로 많이 사용된다.
--실행 가능한 명령으로 저장되고 필요시 파리미터 사용 -type만 지정-길이는 결정않함.
--in mode:상수 취급, 프로시져 호출시 값을 넘겨주는 파라미터
--out mode :프로시져가 실행된 후 값을 호출한 클라이언트에게 넘겨주는 파라미터--리턴값...
--in out mode:값을 넘겨주고 결과를 다시 클라이언트에게 넘겨주는거 모 그냥 포인터 정도로...
--대상 테이블을 생각하고 작업을 하는 경우가 많다
--형식
create or replace pricedure procedure_name / /procedure_name의 객체가 있다면 덮어쓰고 없다면 생성
[파라미터 변수][(in),out mode,in out mode] (type ...)
is
--로컬변수 선언
begin
명령
end;
/

create or replace procedure pro_1 (v_id number,v_name varchar2)
is
begin
insert into ex values(v_id,v_name);
commit;
end;
/
SQL>@파일명 프로시져명(파라미터들)
SQL>exec 프로시져명(파라미터들)
----------------------------------------------------------------------------------------
**위에 sql에서 exec 프로시져명(파라미터들) 이런식으로 호출하는 것을
직접호출이라고 하고
간접호출은 프로시져에서 다른 프로시져를 호출하는 것을 말한다...

--에러 정보 보는 법
1. show error procedure 프로시져명
show error function 함수명
show error trigger 트리거명
2. user_errors뷰 에러 정보를 가진 뷰이다..
3. user_objects뷰 중요하다
4. 객체정보:user_objects view; <-여기에 보면 만들어놓은 procedure 정보를 볼수있다
5. 객체 소스 정보 user_source view

--생성한 프로시져를 삭제하고 싶으면
create로 만들었으니까 drop procedure 프로시져명

--프로시져의 권한 설정
그 테이블을 사용할 권한이 있어도 프로시져를 사용할 권한이 없으면
만들어둔 프로시져를 사용할수는 없다. 당연한거지만..^^
따라서 프로시져에 대한 권한을 줄수있다.
----------------------------예제----------------------------------------------
--emp 테이블에서 신입 사원의 입력하는 프로시져를 생성
--job이 salesman이면 comm->0,그외의 job 이면 comm->null)
--사번은 시퀀스를 이용(1001-1999) 가능,입사일은 오늘 날짜
--호출형태
--exec emp_in('홍길동','CLERK',1000,7209,30)
--create sequence dno_s increment by 1 start with 1001 maxvalue 1999;
create or replace procedure emp_in(v_ename emp.ename%type,v_job emp.job%type,v_sal emp.sal%type,v_mgr emp.mgr%type,v_deptno emp.deptno%type)
is
begin
if v_job='SALESMAN' then
insert into emp values(dno_s.nextval,v_ename ,v_job ,v_mgr ,sysdate,v_sal ,0,v_deptno);
else
insert into emp values(dno_s.nextval,v_ename ,v_job ,v_mgr ,sysdate,v_sal ,null,v_deptno);
end if;
commit;
end;
/
------------------------------------------------------------------------------
--sql%notfound: sql문을 실행하지 못했을때
--dbms_output.put_line('출력하기^^');
--변경 사항 업하기
create or replace procedure emp_up
(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type,v_sal emp.sal%type,v_mgr emp.mgr%type,v_deptno emp.deptno%type)
is
begin
update emp set ename=v_ename,job=v_job,sal=v_sal,mgr=v_mgr,deptno=v_deptno where empno=v_empno;
if sql%notfound then

else
endif

commit;

end;
/

///아웃파라미터 같은경우 리턴값이라 생략할수없다.
-->프로시져명(인파라미터1,인파라미터2,아웃파라미터)
호출시 SQL>variable res number
SQL>exec 프로시져(2,2,:unmber)

***function(함수)
--복잡한 계산식이나 연산을 객체로 생성
-- 함수 사용가능한 모든 곳에서 호출 가능
-- DML이 목적이 아니다.(insert 제외한 다른 dml은 사용할수없다)-insert도 왠만하면 하지 말자..
-- 반드시 리턴값이 존재한다.(한개) ->out mode는 없다. .. in mode만 존재

--형식
create or replace function func_name
(파라미터변수 type ...)
return type
is
지역변수
begin
명령들;
반드시 return 변수;
end
-- 호출형태
select saname,deptno,dname_f(deptno) "부서명" from sawon;

ex) 부서명을 보여주는 함수이당..
create or replace function dname_f
(dno number)
return varchar2
is
v_name varchar2(10);
begin
select dname into v_name from dept where deptno=dno;
return v_name;
end;
/

--주민번호를 가지고 성별을 반환하는 함수 생성
--select goname,gotel,sex_f(gojumin) "성별" from gogek;
ex)
create or replace function sex_f
(v_gojumin varchar2)
return varchar2
is
v_sex varchar2(20);
begin
if substr(v_gojumin,8,1)='1' then
v_sex:='남';
else
v_sex:='여';
end if;
return v_sex;
end;
/
--사원정보를 입력시 부서 테이블에 해당 부서의 존재 유/무를 판단하는 함수 생성
--이것은 참,거짓만을 반원한다. 그렇다면 그건 다른 프로그램에서 사용하가 위한 것이된당.
create or replace function isdept
(v_gojumin varchar2)
return boolean
is
cnt number;
ret boolean;
begin
select count(*) into cnt from dept where deptno=dno;
if cnt=0 then
ret=false;
else
ret=true;
end if;
return boolean;
end;
/ -- 해보면 안다..
--주민번호 유/무효 체크하는 함수를
--j_ck

***Trigger(트리거) show
--연쇄작용-업무의 자동화를 할수있다.왜 데이타 삽입이나 삭제시 바로 동작해주니까..
--특정 테이블에 지정된 이벤트(DML)가 발생하면 자동으로 호출되어 실행되는 객체(호출문 없다)
--제약조건으로 할수없는 제약들,누구는 입력 가능하고 누구는 입력 불가능하게 하는것들.
자료무결성
--변경한 사용자나 테이블의 변경 정보를 기록하여 감시
--예)국민 연금, 세금관련 업무...
--형식
create or replace trigger trigger_name
[after[before]][insert or update or delete] on table_name --한마디로 이벤트 핸들러이다.
*before/after 냐는 실행하고 업데이를 하느냐 아니면 업데이트 하기전에 실행하는냐의 차이.
begin
명령:
*TCL 명령 사용불가!! 트리거에서만 못쓴다.(commit,rollback,등등)
end;
/
------
ex)
create table test (txt varchar2(30));
truncate table dept_ex;
create or replace trigger t1
after insert on dept_ex
begin
insert into test values('행 삽입');
end;
/
이렇게 했을때
insert into dept_ex values(10,'영업부','서울');
한번 트리거가 수행된다.
헌데..
insert into dept_ex select * from dept;
이렇게 해도 단 한번만 트리거가 수행된다. - 신기하다.
이걸 막기위해서 for each row라는 걸 추가한다.
------
ex)
create table test (txt varchar2(30));
truncate table dept_ex;
create or replace trigger t1
after insert on dept_ex
begin
insert into test values('행 삽입');
for each row; -- 요렇게 요렇게 하면
end;
/

--[:old 컬럼명] : 삭제된 컬럼 값
--[:new 컬럼명] : 입력된 컬럼 값
당연한 이야기지만 삭제되면 오울드뿐이고 삽입되면 뉴가있고, 갱신이되면 둘다 있다..

--dept table에 새로운 행의 입력이 발생하면 dept_ex table에 입력
create or replace trigger dept_in
after insert on dept
for each row; -- 요렇게 요렇게 하면
begin
insert into test values(:new.deptno,:new.dname,:new.loc);
end;
/
--dept table에 새로운 행의 삭제가 발생하면 dept_ex table도 함께 삭제
create or replace trigger dept_del
after delete on dept
for each row -- 요렇게 요렇게 하면
begin
delete dept_ex where deptno=:old.deptno and dname=:old.dname and loc=:old.loc;
end;
/

--dept table에 변경시 dept_ex table도 함께 변경
create or replace trigger dept_mod
after update on dept
for each row -- 요렇게 요렇게 하면
begin
delete dept_ex where deptno=:old.deptno and dname=:old.dname and loc=:old.loc;
insert into dept_ex values(:new.deptno,:new.dname,:new.loc);
end;
/

--중요한거 하나더 하나의 테이블 이벤트에는 하나의 트리거만 연결할수있다.

---업데이트 케스케이트는 sql에서 지원하지 않는다.
케스케이드 종속적인거..제약조건걸때 생기는 문제
이거를 트리거를 이용해서 만들어보자는 거다.
--부서테이블의 부서번호가 변경되면, 그 부서를 참조하는 사원들의 부서번호도 함께 변경되는 트리거
- update cascade
create or replace trigger dept_mod
before update on dept --요기 이렇게 before를 이용하는 방법이있다
for each row
begin
update sawon set deptno=:new.deptno where deptno=:old.deptno;
end;
/
---위든 밑이든 상관없다.
create or replace trigger dept_mod
after update on dept --요기 이렇게 before를 이용하는 방법이있다
for each row
begin
update sawon set deptno=:new.deptno where deptno=:old.deptno;
end;
/

특정 컬럼의 단위에 이벤트를 발생하고자 한다면 of 절이있다.단 업데이트만 가능 당연하다..^^
create or replace trigger dept_mod
after update of deptno on dept --요기 바로 요기 저렇게 of deptno처럼 저 컬럼이 변경되었을때만 이벤트가 발생
for each row
begin
update sawon set deptno=:new.deptno where deptno=:old.deptno;
end;
/

--1. 변경을 시도한 세션(계정)과 날짜(시간 포함)을 black_list table에 기록하는 트리거
--2. 변경할 수 없도록 하고 에러 메세지를 출력하는 트리거
----이렇게 두개를 동시에 할수는 없다.. 트리거에서 롤백을 사용할수 없기때문...
----ms에서는 가능
--black_list 테이블
create table black_list(account varchar2(30),time varchar2(40),id varchar2(40));
----밑에꺼처럼 하면
create or replace trigger dept_mod
after update of deptno on dept --요기 바로 요기 저렇게 of deptno처럼 저 컬럼이 변경되었을때만 이벤트가 발생
for each row
begin
raise_application_error(-20001,'부서번호는 변경할수없습니다');
end;
/

--사용자 정의 에러(rollback 포함)
--raise_application_error(-20001,'메세지') --에러번호는 -20001~-20999까지 줄수있다.
create or replace trigger sawon_mod_error
after update of SAPAY on sawon --요기 바로 요기 저렇게 of SAPAY 처럼 저 컬럼이 변경되었을때만 이벤트가 발생
for each row

begin
raise_application_error(-20001,'급여는 변경할수없습니다');
--insert into black_list values( (select user from dual) ,to_char(sysdate,'yy-mm-dd hh:mm'));

end;
/

--사번까지 보여주자..
create or replace trigger sawon_mod_error
after update of SAPAY on sawon --요기 바로 요기 저렇게 of SAPAY 처럼 저 컬럼이 변경되었을때만 이벤트가 발생
for each row
begin
insert into black_list values( user,to_char(sysdate,'yy-mm-dd hh:mm'),:new.sabun);
end;
/

loyee테이블과 loan(대출정보)테이블이용 대출하면 자동으로 employee에 기록이 되도록

이런걸 해보자
여러개의 이벤트를 한꺼번에 하는 방법
create or replace trigger sawon_mod_error
after insert or update or delete on emp
begin
if inserting then
할일
elsif updating then
할일
elsif deleting then
할일
end if;
end;
/
문법만 가지고 와서 간단히 아래 처럼 구현해 볼수있다.
create or replace trigger loan_chang
after insert or update or delete on loan -- 그냥 이런 문법도 가능하다는 정도
for each row
begin
if inserting then --위에 사용한것과 함께 사용해야한다는거
update employee set rck='Y' where emp_id = :new.emp_id;
elsif updating then
update employee set rck='Y' where emp_id = :new.emp_id;
elsif deleting then
update employee set rck='N' where emp_id = :old.emp_id;
end if;
end;
/

--sawon 테이블에서 급여가 변경되는 경우,
--기존 급여보다 적거나 기존 급여보다 20% 초과하면 에러 메세지를 출력하는 트리거 생성
create or replace trigger say_change
after update of SAPAY on sawon
for each row
begin
if :old.sapay> :new.sapay then
raise_application_error(-20001,'전보다 적게는 불가능');
elsif :new.sapay>:old.sapay*1.2 then
raise_application_error(-20001,'너무 많음');
end if;
end;
/
이렇게 할수있다.
****이상태에서 대리는 이런 제한으로부터 자유롭게 해보자.
create or replace trigger say_change
after update of SAPAY on sawon
for each row
when (new.sajob<>'대리') --일단 :이게 없다는거 또한 대리면 이 트리거가 수행되지 않는다는거
begin
if :old.sapay> :new.sapay then
raise_application_error(-20001,'전보다 적게는 불가능');
elsif :new.sapay>:old.sapay*1.2 then
raise_application_error(-20001,'너무 많음');
end if;
end;
/
alter trigger 트리거명 [disable][enable]; --
alter table 테이블명 [disable][enable] all triggers;--선택된 테이블에 걸린 트리거를 한꺼번 인/디 할수있다

[출처] pl/sql_문법 |작성자 쿠마