Day30

Q 1)作成したプロセスリストの表示

SELECT* FROM user_procedures;

Q 2)プロセス内容の確認

SELECT*FROM USER_SOURCE;

Q 3)申請部署番号20の社員の社員番号、氏名、給与のプロセスEX 01
カーソルの宣言方法:変数のように使用します.1つ以上のrowオブジェクトのオブジェクトを管理する

CREATE OR REPLACE PROCEDURE EX01
 AS
    v_empno emp.empno%type;
    v_ename emp.ename%type;
    v_sal number(7,2) := 0;
    
cursor emp_cursor is  (**1. 커서 선언** : 하나 이상의 row를 담는 객체 cursor user_name is ~)
    select empno, ename, sal
    from emp where deptno=20;
    
Begin
OPEN emp_cursor ;  (**2. 커서를 시작**)
 loop
 FETCH emp_cursor into v_empno, v_ename, v_sal ; (**3. 변수 대입**)
 exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
        
 DBMS_OUTPUT.PUT_LINE(v_empno||' '|| v_ename ||' '|| v_sal);
 end loop;
 close emp_cursor; (** 4.커서 종료**)
 end EX01;

Q 4)EX 01プロセスの実行

SET SERVEROUTPUT ON
EXECUTE EX01;

Q 5)社員表に社員の名前と給料を印刷する.EX 02プロセスの作成

SELECT ENAME, SAL
FROM EMP

CREATE OR REPLACE PROCEDURE EX02
 AS
    v_ename emp.ename%type;
    v_sal number(7,2) := 0;
    
cursor emp_cursor is --(1. 커서 선언)
select ename, sal
from emp;
    
Begin
OPEN emp_cursor ; -- (2. 커서를 시작)
loop
FETCH emp_cursor into v_ename, v_sal ; --(3.변수 대입)
exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
        
DBMS_OUTPUT.PUT_LINE(v_ename ||' '|| v_sal);
end loop;
close emp_cursor; -- (4.커서 종료)
 end EX02;

Q 6)運転中に部門番号を渡して出力部門番号を作成するプロセスEX 03

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO =10; -- V_DEPTNO (커서의 매개변수로 선언)

SELECT EMPNO, ENMAE,SAL
FROM EMP
WHERE DEPTNO=20; -- V_DEPTNO (커서의 매개변수로 선언)

EXEC EX03;


-- **프로시저를 실행 할 때 부서번호를 전달해서 해당 부서번호를 출력하는 프로시저 만들기 EX03**

CREATE OR REPLACE PROCEDURE EX03 AS 
V_EMPNO emp.empno%TYPE;
V_ENAME emp.ename%TYPE;
V_SAL EMP.SAL%TYPE;

cursor emp_cursor(V_DEPTNO NUMBER) is --(1. 커서 선언)
    select EMPNO, ename, sal
    from emp
    WHERE DEPTNO=V_DEPTNO;

BEGIN
 OPEN emp_cursor(10) ;  --(2.커서를 시작)
    loop
        FETCH emp_cursor into v_empno, v_ename, v_sal ; --(3. 변수 대입)
        exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
        DBMS_OUTPUT.PUT_LINE(v_empno||' '|| v_ename ||' '|| v_sal);
        end loop;
        close emp_cursor; -- (4.커서 종료)
 
OPEN emp_cursor(20) ; -- 2. 커서를 시작
loop
FETCH emp_cursor into v_empno, v_ename, v_sal ; --3. 변수 대입
exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;

DBMS_OUTPUT.PUT_LINE(v_empno||' '|| v_ename ||' '|| v_sal);
end loop;
close emp_cursor; -- 4.커서 종료
    end EX03;

Q 7)宣言されたカーソルcursor user name is構文;

カーソルSQL%を使用してプロシージャを呼び出す場合は、従業員番号を入力して給与を1.1%引き上げます.EX 04

は明示的な大容量で、SQL%を使用してプログラムを通じて4回にわたって出力賃金の引き上げと賃金の引き上げの従業員数を獲得し、

を出力する.

CREATE OR REPLACE PROCEDURE EX04 (P_EMPNO IN EMP.EMPNO%TYPE)
AS 
V_SAL EMP.SAL%TYPE;
V_UPDATE_CNT NUMBER;
BEGIN
    SELECT SAL INTO V_SAL
    FROM EMP
    WHERE EMPNO = P_EMPNO;
    
    IF SQL%FOUND THEN --명시적 커서가 있다면 다음을 수행하자.
    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재한다 '|| V_SAL);
    
      UPDATE EMP
      SET SAL= SAL*1.1
      WHERE EMPNO=P_EMPNO;
    
      V_UPDATE_CNT := SQL%ROWCOUNT;
       DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원의 수'|| V_UPDATE_CNT);
       END IF;
END EX04;

EXEC EX04(7900);

Q 8)宣言されたカーソルuser name is構文;EX 05の作成プロセスを使用します.
部門番号に対応する給与を入力し、1.1%の昇給金額に変更し、4回、氏名、給与を出力します.FOR文を使用します.

  CREATE OR REPLACE PROCEDURE EX05 (P_DEPTNO IN EMP.DEPTNO%TYPE)AS
 CURSOR EMP_CURSOR IS 
 SELECT EMPNO,ENAME,SAL
 FROM EMP
 WHERE DEPTNO = P_DEPTNO
  FOR UPDATE;
BEGIN
    FOR ABC IN EMP_CURSOR LOOP 
    UPDATE EMP
    SET SAL = SAL*1.1
    WHERE CURRENT OF EMP_CURSOR;  --현재 커서의 ROW를 가져와서 수정 하겠다.
     DBMS_OUTPUT.PUT_LINE(ABC.EMPNO || '      ' || ABC.ENAME || '     '||ABC.SAL);
     END LOOP;
END EX05;


 EXEC EX05(20);

Q 9)EX 06を実行し、EMPテーブルの全内容を返す

 SELECT * FROM EMP;
 
 
 CREATE OR REPLACE PROCEDURE EX06 ( MY_RES OUT SYS_REFCURSOR) AS 
BEGIN
 OPEN MY_RES FOR
 SELECT * FROM EMP;
END EX06;
 
 
 
 VAR RES REFCURSOR;
 EXEC EX06(:RES)
 PRINT :RES;

Q 10)EX 07を実行して職業を入力し、社員の全ての情報を返す

CREATE OR REPLACE PROCEDURE EX07 (M_JOB IN EMP.JOB%TYPE ,M_RES OUT SYS_REFCURSOR ) AS 
BEGIN
 OPEN M_RES FOR
 SELECT * FROM EMP WHERE JOB =M_JOB;
END EX07;



VAR RES REFCURSOR;
 EXEC EX07('SALESMAN',:RES);
 PRINT :RES;

Q 11)パッケージ:高度に関連する関数またはプロセスを組み合わせて管理と使用する

PACKAGE = SPEC _ BODY

SPEC=宣言

パッケージの関数、プロシージャ、変数の定義

BODY=宣言で定義する識別子の実コード部

削除

声明及び身体全部削除:DROP PACKAGE PACKAGE NAME;

身体部分のみ削除:DROP PACKAGE BODY PACKAGE NAME;

EMPテーブルでは、MY PACKAGEを作成して、合計給与と平均給与を取得します.

--spec
CREATE OR REPLACE PACKAGE my_package
is
procedure my_sum;
procedure my_avg;
end my_package;

--body
CREATE OR REPLACE PACKAGE BODY my_package
IS
  procedure my_sum IS
    CURSOR E_SUM IS 
    SELECT SUM(NVL(SAL,0)) FROM EMP;
 TOTAL_SUM NUMBER;
  BEGIN
    OPEN E_SUM;
       FETCH E_SUM INTO TOTAL_SUM;
       DBMS_OUTPUT.PUT_LINE('봉급의 합계 : '|| TOTAL_SUM);
     CLOSE E_SUM;
  END my_sum;
  
   procedure my_avg IS
    CURSOR E_AVG IS 
    SELECT SUM(NVL(SAL,0)) FROM EMP;
 TOTAL_avg NUMBER;
  BEGIN
    OPEN E_AVG;
       FETCH E_AVG INTO TOTAL_AVG;
       DBMS_OUTPUT.PUT_LINE('봉급의 합계 : '|| TOTAL_AVG);
     CLOSE E_avg;
  END MY_avg;
  END MY_PACKAGE;