PL/SQL가벼운 예제

CREATE OR REPLACE PROCEDURE THINKER.p_emp(empno in number)
 IS
BEGIN
 NULL;
END p_emp;


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

CREATE OR REPLACE PROCEDURE THINKER.p_emp(empno in number)
 IS
BEGIN
 DELETE TBL_EMP WHERE employee_id = empno;
END p_emp;

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

CREATE OR REPLACE PROCEDURE THINKER.p_log(v_param IN number)
 IS
BEGIN
 INSERT INTO tbl_log(param) VALUES (v_param);

END p_log;

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

CREATE OR REPLACE PROCEDURE THINKER.p_emp(empno in number)
 IS
BEGIN
 p_log(empno);
 DELETE TBL_EMP WHERE employee_id = empno;
END p_emp;



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









CREATE OR REPLACE PROCEDURE THINKER.p_emp2(empno IN tbl_emp.employee_id%type)
 IS
BEGIN
 NULL;

END p_emp2;

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


CREATE OR REPLACE PROCEDURE THINKER.p_emp2(empno IN tbl_emp.employee_id%type)
 IS
BEGIN
 IF TRUE then
   NULL;

 END IF;

END p_emp2;


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


CREATE OR REPLACE PROCEDURE THINKER.p_emp2(empno IN tbl_emp.employee_id%type)
 IS
  v_job tbl_emp.job_id%TYPE;
BEGIN

 DBMS_OUTPUT.put_line('TARGET employee_id : '||empno);


 IF TRUE then
 
   NULL;

 END IF;

END p_emp2;



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



CREATE OR REPLACE PROCEDURE THINKER.p_emp2(empno IN tbl_emp.employee_id%type)
 IS
  v_job tbl_emp.job_id%TYPE;
BEGIN

 SELECT job_id INTO v_job FROM TBL_EMP WHERE employee_id = empno;

 DBMS_OUTPUT.put_line('TARGET employee_id : '||empno);


 IF TRUE then
 
   NULL;

 END IF;

END p_emp2;


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



CREATE OR REPLACE PROCEDURE THINKER.p_emp2(empno IN tbl_emp.employee_id%type)
 IS
  v_job tbl_emp.job_id%TYPE;
BEGIN

 SELECT job_id INTO v_job FROM TBL_EMP WHERE employee_id = empno;

 DBMS_OUTPUT.put_line('TARGET employee_id : '||empno);

 IF v_job = 'IT_PROG' then

  DBMS_OUTPUT.put_line('UPDATE employee_id : '||empno);
  UPDATE TBL_EMP SET salary = salary * 2 WHERE job_id = v_job;

 END IF;

END p_emp2;

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


CREATE OR REPLACE PROCEDURE THINKER.p_emp3(cnt in number)
 IS
  v_cnt NUMBER := 0;
BEGIN
 FOR v_cnt IN 1.. cnt LOOP

  DBMS_OUTPUT.put_line(v_cnt);

 END LOOP;

END p_emp3;

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


CREATE OR REPLACE PROCEDURE THINKER.p_emp4
 IS
 CURSOR emp_cursor IS
 SELECT employee_id, first_name ||' '|| last_name, salary FROM TBL_EMP;
 
BEGIN

 OPEN emp_cursor ;


 CLOSE emp_cursor;
 

END p_emp4;










CREATE OR REPLACE PROCEDURE THINKER.p_emp4
 IS
 v_id tbl_emp.EMPLOYEE_ID%TYPE;
 v_name VARCHAR2(200);
 v_sal number;

 CURSOR emp_cursor IS
 SELECT employee_id, first_name ||' '|| last_name, salary FROM TBL_EMP;
 
BEGIN

 OPEN emp_cursor ;

 LOOP
   FETCH emp_cursor INTO v_id, v_name, v_sal;
 
   DBMS_OUTPUT.put_line(v_id||' '||v_name);
 
   EXIT WHEN emp_cursor%NOTFOUND;

 END LOOP;


 CLOSE emp_cursor;
 

END p_emp4;

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

CREATE OR REPLACE PROCEDURE THINKER.p_emp5(empno in number)
 IS
  v_name VARCHAR2(200);
  v_job VARCHAR2(30);
BEGIN

 SELECT first_name||''||last_name, job_id INTO v_name, v_job  FROM TBL_EMP WHERE employee_id = empno;

 DBMS_OUTPUT.put_line(v_name||'- '|| v_job);

EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.put_line('NO DATA FOUND');
  p_log2(empno);  


END p_emp5;



CREATE OR REPLACE PROCEDURE THINKER.p_emp5(empno in number)
 IS
  v_name VARCHAR2(200);
  v_job VARCHAR2(30);
BEGIN

 SELECT first_name||''||last_name, job_id INTO v_name, v_job  FROM TBL_EMP WHERE employee_id = empno;

 DBMS_OUTPUT.put_line(v_name||'- '|| v_job);

 UPDATE TBL_EMP SET salary = salary + 100;

EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.put_line('NO DATA FOUND');


END p_emp5;