sql_prepare_final_exam.sql

set serveroutput on;

CREATE OR PROCEDURE GET_ANNSAL
( VEMPNO IN EMP.EMPNO%TYPE,
ANNSAL OUT NUMBER)
IS
BEGIN
	SELECT SAL * 12 INTO ANNSAL
	FROM EMP
	WHERE EMPNO = VEMPNO;

	ANNSAL := SAL * 12;
END;
/

ALTER TABLE EMP01
ADD COLUMN (JOB VARCHAR2(10));

ALTER TABLE EMP01
DROP COLUMN (JOB);

CREATE TABLE EMP01
AS
SELECT * FROM EMP
WHERE 0 = 1;

CREATE TABLE EMP01
AS
SELECT * FROM EMP;

CREATE TABLE EMP01(
ENAME VARCHAR2(10) CONSTRAINT EMP01_ENAME_PK PRIMARY KEY);

CREATE TABLE EMP01(
ENAME VARCHAR2(10) CONSTRAINT EMP01_ENAME_UK UNIQUE KEY);

CREATE TABLE EMP01(
DEPTNO VARCHAR2(10) CONSTRAINT EMP01_ENAME_FK REFERENCES DEPT(DEPTNO));

CREATE TABLE EMP01(
DEPTNO VARCHAR2(10));
ALTER TABLE ADD CONSTRAINT EMP01_ENAME_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);

CREATE TABLE EMP01(
ENAME VARCHAR2(10),
PHONE VARCHAR2(10),
CONSTRAINT EMP01_COMBO_PK PRIMARY KEY(ENAME, PHONE));

CREATE INDEX IND_EMP01_DEPTNO
ON EMP01(DEPTNO);

CREATE UNIQUE INDEX IND_EMP01_ENAME
ON EMP01(ENAME);

CREATE INDEX IND_EMP01_ANNSAL
ON EMP01(SAL * 12);

CREATE OR REPLACE FUNCTION function_name(
variagle [option] variable_type,
...)
RETURN type(NUMBER, VARCHAR2 ...)
IS
function's declareS;
BEGIN
statements;
statements;
END;
/

CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE01
IS
	VDEPTNO DEPT.DEPTNO%TYPE;
	VDNAME DEPT.DNAME%TYPE;
	VLOC DEPT.LOC%TYPE;
	CURSOR C1
	IS
--	select_statement;
	SELECT * FROM DEPT;
BEGIN
	OPEN C1;
	LOOP
--	FETCH C1 INTO VDEPT;
	FETCH C1 INTO VDEPTNO, VDNAME, VLOC;
	EXIT WHEN C1%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO || VDEPT.DNAME || VDEPT.LOC);
	END LOOP;
	CLOSE C1;
END;
/

CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE01
IS
	VDEPT DEPT%ROWTYPE;
	CURSOR C1
	IS
	SELECT * FROM DEPT;
BEGIN
--	DON'T NEED TO "OPEN C1"
	FOR VDEPT IN C1
	EXIT WHEN C1%NOTFOUND;
--	DON'T NEED TO "FETCH C1 INTO VDEPT"
	DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO || VDEPT.DNAME || VDEPT.LOC);
	END LOOP;
--	DON'T NEED TO "CLOSE C1"
END;
/

IF (condition) THEN
	statements;
END IF;

IF (condition) THEN
	tatements;
ELSE
	statements;
END IF;

IF (condition) THEN
	statements;
ELSIF (condition) THEN
	statements;
ELSE (condition) THEN
	statements;
END IF;

SET SERVEROUTPUT ON;



DECLARE
	N NUMBER := 1;
BEGIN
	LOOP
	IF (N > 5) THEN
	EXIT;
	END IF;
	N := N + 1;
	DBMS_OUTPUT.PUT_LINE(N);
	END LOOP;
END;
/


--FOR LOOP
DECLARE
BEGIN
	FOR N IN 1..5 LOOP
		DBMS_OUTPUT.PUT_LINE(N);
	END LOOP;
END;
/

DECLARE
	N NUMBER := 1;
BEGIN
	WHILE (N > 5) LOOP
		DBMS_OUTPUT.PUT_LINE(N);
		N := 1;
	END LOOP;
END;
/


--TABLE TYPE
DECLARE
	TYPE VENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE
	INDEX BY BINARY_INTEGER;

	VENAME_TABLE VENAME_TABLE_TYPE;

	I BINARY_INTEGER := 0;
BEGIN
	FOR K IN (SELECT ENAME FROM EMP) LOOP
		I := I + 1;
		VENAME_TABLE(I) = K.ENAME;
	END LOOP;

	FOR J IN 1..I LOOP
		DBMS_OUTPUT.PUT_LINE(VENAME_TABLE(I));
	END LOOP;
END;
/


--RECORD TYPE
DECLARE
	TYPE VEMP_RECORD_TYPE IS RECORD(
		VENAME EMP.ENAME%TYPE,
		VEMPNO EMP.EMPNO%TYPE,
		VJOB EMP.JOB%TYPE
	);

	VEMP_RECORD VEMP_RECORD_TYPE;
BEGIN
	SELECT ENAME, EMPNO, JOB
	INTO VEMP_RECORD
	FROM EMP
	WHERE ENAME ='SCOTT';


--TABLE TYPE
DECLARE
	TYPE VENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE
	INDEX BY BINARY_INTEGER;

	TYPE VJOB_TABLE_TYPE IS TABLE OF EMP.JOB%TYPE
	INDEX BY BINARY_INTEGER;

	VENAME_TABLE VENAME_TABLE_TYPE;
	VJOB_TABLE VJOB_TABLE_TYPE;

	I BINARY_INTEGER := 0;
BEGIN
	FOR K IN (SELECT ENAME, JOB FROM EMP) LOOP;
		I := I + 1;
		VENAME_TABLE(I) := K.ENAME;
		VJOB_TABLE(I) := K.JOB;
	END LOOP;

	FOR J IN 1..I LOOP
		DBMS_OUTPUT.PUT_LINE(RPAD(VENAME_TABLE(I), 12) || RPAD(VJOB_TABLE(I), 7) );
	END LOOP;
END;
/



--CURSOR_SAMPLE01
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE01
IS
	VDEPT DEPT%ROWTYPE;
	CURSOR C1
	IS
	SELECT * FROM DEPT;
BEGIN
	OPEN C1;
	LOOP
	C1 FETCH INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC;
	EXIT WHEN C1%NOTFOUND;
--	IF (C1%NOTFOUND) THEN
--		EXIT;
--	END IF;
	DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO || VDEPT.DNAME || VDEPT.LOC);
	END LOOP;
	CLOSE C1;
END;
/


--CURSOR_SAMPLE02
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE02
IS
	VDEPT DEPT%ROWTYPE;
	CURSOR C1
	IS
	SELECT * FROM DEPT;
BEGIN
	FOR VDEPT IN C1 LOOP
		EXIT WHEN C1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO || VDEPT.DNAME || VDEPT.LOC);
	END LOOP;
END;
/


--FUCTION EXPRESSION
CREATE OR REPLACE FUNCTION function_name(
	function_variable01 [MODE] variable_type [ := default_value ],
	..
)
RETURN type(number, varchar..)
IS
	function's local_variables
BEGIN
	statements;
	statements;
END;
/