DESC STUDENTTABLESET SERVEROUTPUT ON;declare v_str1 VARCHAR2(10);v_str2 VARCHAR2(50);begin v_str1:='hello'; select STUDENT_NAME into v_str2 from STUDENTTABLE where STUDENT_NO=1; dbms_output.put_line(v_str1 ||', '|| v_str2); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO DATA FOUND');end;---------------------------------------VIEW-------------------------------------------------------------------VIEW-------------------------------------------------------------------VIEW-------------------------------------------------------------------VIEW--------------------------------------------------------CREATE A VIEWCREATE OR REPLACE VIEW TEST_VIEWASSELECT * FROM STUDENTTABLE WHERE ROWNUM<10;--QUERY DATA FROM TEST_VIEWSELECT * FROM TEST_VIEW;-----DROP VIEWDROP VIEW TEST_VIEW;--------------------SYNONYM-------------------------------------------------------------------SYNONYM-------------------------------------------------------------------SYNONYM-------------------------------------------------------------------SYNONYM-----------------------------------------------CREATE SYNONYM SYN_PHRASES FOR CITIAP_USER; --HERE I LOGIN AS JFPAP USER TO CREATE A SYNONYM FOR CITIAP USER, TABLE WAS CREATED BY CITIAP USER.GRANT ALL ON CITIAP.PHRASES TO JFPAPUSER;-- HERE I LOGIN AS CITIAP USER TO GRANT TABLE PHRASES TO JFPAP USER--------------------SEQUENCE-------------------------------------------------------------------SEQUENCE-------------------------------------------------------------------SEQUENCE-------------------------------------------------------------------SEQUENCE-------------------------------------------------CREATE A SEQUENCECREATE SEQUENCE MY_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 10;CREATE SEQUENCE MY_SEQ;--DROP SEQUENCEDROP SEQUENCE MY_SEQ;--QUERY SEQUECE VALUESELECT MY_SEQ.CURRVAL FROM DUAL;--CURRENT VALUESELECT MY_SEQ.NEXTVAL FROM DUAL;--NEXT VALUE--------------------PL/SQL BLOCK-------------------------------------------------------------------PL/SQL BLOCK-------------------------------------------------------------------PL/SQL BLOCK-------------------------------------------------------------------PL/SQL BLOCK-----------------------------------------------DECLARE V_STUDENT_NAME VARCHAR2(50);BEGIN select STUDENT_NAME into V_STUDENT_NAME from STUDENTTABLE where STUDENT_NO=&NO; dbms_output.put_line(V_STUDENT_NAME);END;--------------------PROCEDURE-------------------------------------------------------------------PROCEDURE-------------------------------------------------------------------PROCEDURE-------------------------------------------------------------------PROCEDURE-----------------------------------------------CREATE OR REPLACE PROCEDURE UPDATE_NAME(NEWSTUNAME VARCHAR2,STUNO VARCHAR2) ISV_NAME VARCHAR2(10);BEGIN UPDATE STUDENTTABLE SET STUDENT_NAME=NEWSTUNAME WHERE student_no=STUNO; COMMIT; SELECT STUDENT_NAME INTO V_NAME FROM studenttable WHERE student_no=STUNO; dbms_output.put_line('NAME WAS UPDATED, NEW NAME IS '|| V_NAME);END;CALL UPDATE_NAME('??','1');--------------------FUNCTION-------------------------------------------------------------------FUNCTION-------------------------------------------------------------------FUNCTION-------------------------------------------------------------------FUNCTION-----------------------------------------------CREATE OR REPLACE FUNCTION FINDSTUNAME(STU_NO VARCHAR2)RETURN VARCHAR2 is STU_NAME STUDENTTABLE.STUDENT_NAME%TYPE;BEGIN SELECT STUDENT_NAME INTO STU_NAME FROM STUDENTTABLE WHERE student_no=STU_NO; RETURN STU_NAME;END; --------------------PACKAGE-------------------------------------------------------------------PACKAGE-------------------------------------------------------------------PACKAGE-------------------------------------------------------------------PACKAGE-----------------------------------------------CREATE OR REPLACE PACKAGE STU_PKG AS PROCEDURE PROCEDURE UPDATE_NAME (NEWSTUNAME VARCHAR2 , STUNO VARCHAR2) ; FUNCTION FINDSTUNAME ( STU_NO VARCHAR2 ) RETURN VARCHAR2 ; END ; --PACKAG BODYCREATE PACKAGE BODY STU_PKG_BODY IS PROCEDURE UPDATE_NAME(NEWSTUNAME VARCHAR2,STUNO VARCHAR2) ISV_NAME VARCHAR2(10);BEGIN UPDATE STUDENTTABLE SET STUDENT_NAME=NEWSTUNAME WHERE student_no=STUNO; COMMIT; SELECT STUDENT_NAME INTO V_NAME FROM studenttable WHERE student_no=STUNO; dbms_output.put_line('NAME WAS UPDATED, NEW NAME IS '|| V_NAME);END;FUNCTION FINDSTUNAME(STU_NO VARCHAR2)RETURN VARCHAR2 is STU_NAME VARCHAR2(10) ;BEGIN SELECT STUDENT_NAME INTO STU_NAME FROM STUDENTTABLE WHERE student_no=STU_NO; RETURN STU_NAME;END;END;--------------------TRIGGER-------------------------------------------------------------------TRIGGER-------------------------------------------------------------------TRIGGER-------------------------------------------------------------------TRIGGER-----------------------------------------------CREATE OR REPLACE TRIGGER UPDATE_STU_TRIGGERBEFORE INSERT ON STUDENTTABLEFOR EACH ROWBEGIN SELECT MY_SEQ.NEXTVAL INTO :NEW.STUDENT_NO FROM DUAL; END; insert into STUDENTTABLE(student_name) values('hello'); commit; select * from STUDENTTABLE;--------------------IF-------------------------------------------------------------------IF-------------------------------------------------------------------IF-------------------------------------------------------------------IF-----------------------------------------------SELECT * FROM STUDENTTABLE;CREATE OR REPLACE FUNCTION FINDFLAG(STU_NO IN VARCHAR2) RETURN VARCHAR2 IS v_flag VARCHAR2(10); v_name STUDENTTABLE.STUDENT_NAME%TYPE; BEGIN SELECT STUDENT_NAME INTO v_name FROM STUDENTTABLE WHERE student_no=STU_NO; IF '??'=v_name THEN v_flag:=v_name||' 2'; RETURN v_flag; ELSIF '??'=v_name THEN v_flag:=v_name||' 3'; RETURN v_flag; ELSE v_flag:='NOT FOUND!!!'; RETURN v_flag; END IF;END;--------------------CASE WHEN-------------------------------------------------------------------CASE WHEN-------------------------------------------------------------------CASE WHEN-------------------------------------------------------------------CASE WHEN-----------------------------------------------CREATE OR REPLACE FUNCTION FINDFLAG2(STU_NO IN VARCHAR2) RETURN VARCHAR2 IS v_flag VARCHAR2(10); v_name STUDENTTABLE.STUDENT_NAME%TYPE; BEGIN SELECT STUDENT_NAME INTO v_name FROM STUDENTTABLE WHERE student_no=STU_NO; CASE WHEN '??'=v_name THEN v_flag:=v_name||' 2'; RETURN v_flag; WHEN '??'=v_name THEN v_flag:=v_name||' 3'; RETURN v_flag; ELSE v_flag:='NOT FOUND!!!'; RETURN v_flag; END CASE;END;--------------------LOOP-------------------------------------------------------------------LOOP-------------------------------------------------------------------LOOP-------------------------------------------------------------------LOOP-----------------------------------------------SELECT * FROM STUDENTTABLE;DECLARE i INT:=1;BEGIN LOOP INSERT INTO STUDENTTABLE(STUDENT_NAME) VALUES('STUDENT '|| i); EXIT WHEN i=5; i:=i+1; END LOOP; COMMIT;END;--------------------WHILE-------------------------------------------------------------------WHILE-------------------------------------------------------------------WHILE-------------------------------------------------------------------WHILE-----------------------------------------------DECLARE i INT:=6;BEGIN WHILE i<10 LOOP INSERT INTO STUDENTTABLE(STUDENT_NAME) VALUES('STUDENT '|| i); i:=i+1; END LOOP;END;--------------------FOR-------------------------------------------------------------------FOR-------------------------------------------------------------------FOR-------------------------------------------------------------------FOR-----------------------------------------------BEGIN FOR i IN 10..15 LOOP INSERT INTO STUDENTTABLE(STUDENT_NAME) VALUES('STUDENT '|| i); END LOOP; END;--------------------EXCEPTION-------------------------------------------------------------------EXCEPTION-------------------------------------------------------------------EXCEPTION-------------------------------------------------------------------EXCEPTION-----------------------------------------------DECLARE E_INVALID_INPUT EXCEPTION; V_STU_NAME studenttable.STUDENT_NAME%TYPE; V_STU_NO VARCHAR2(10):=&STU_NO;BEGIN IF V_STU_NO='2' THEN RAISE E_INVALID_INPUT; END IF; SELECT STUDENT_NAME INTO V_STU_NAME FROM studenttable WHERE student_no=V_STU_NO; dbms_output.put_line('HELLO, '||V_STU_NAME); EXCEPTION WHEN E_INVALID_INPUT THEN dbms_output.put_line('INPUT ERROR'); END;--------------------IMPLICIT CURSOR-------------------------------------------------------------------IMPLICIT CURSOR-------------------------------------------------------------------IMPLICIT CURSOR-------------------------------------------------------------------IMPLICIT CURSOR--------------------------------------------------SQL%FOUND && SQL%NOTFOUND && SQL%ROWCOUNDDECLARE V_STU_NAME studenttable.STUDENT_NAME%TYPE; V_ROW_STU studenttable%ROWTYPE;BEGIN UPDATE studenttable SET STUDENT_NAME='CITI' WHERE student_no=&STU_NO RETURNING STUDENT_NAME INTO V_STU_NAME; --USING RETURNING IF SQL%FOUND THEN dbms_output.put_line('DATA UPDATED, UPDATED ROW COUNT '|| SQL%ROWCOUNT|| ' , NEW NAME IS '|| V_STU_NAME); END IF; IF SQL%NOTFOUND THEN dbms_output.put_line('DATA NOT FOUND...'); END IF; SELECT * INTO V_ROW_STU FROM studenttable WHERE student_NAME=V_STU_NAME; dbms_output.put_line('V_ROW_STU ====> '|| V_ROW_STU.student_no||', '|| V_ROW_STU.STUDENT_NAME);END;SELECT * FROM STUDENTTABLE;--USING IMPLICIT CURSOR ,ORACLE WILL EXECUTE OPEN, FETCH,CLOSE .--BELOW 2 PROCEDURE A ONE EXAMPLE--BELOW IS TO REMOVE ONE RECORD FROM TABLECREATE OR REPLACE PROCEDURE PRO_REMOVE_STU(V_STU_NO IN studenttable.student_no%TYPE)ISBEGIN DELETE FROM studenttable WHERE STUDENT_NO=V_STU_NO;END;--BELOW IS TRY TO QUERY RECORD COUNT, BUT GET FAILCREATE OR REPLACE PROCEDURE PRO_QUERY_COUNT1IS V_COUNT INTEGER;BEGIN SELECT COUNT(*) INTO V_COUNT FROM studenttable; --10 RECORDS IN TABLE PRO_REMOVE_STU('9999'); --CALL PRO_REMOVE_STU(), BUT 9999 WAS NOT IN DB. dbms_output.put_line(SQL%ROWCOUNT ||' RECORDS WAS FOUND IN TABLE'); --WILL PRINT : 0 RECORDS WAS FOUND IN TABLE .BECAUSE SQL%ROWCOUNT WILL ONLY RECORD THE LATEST DML EXECUTION. DUE TO 9999 IS NOT IN DB,SO THE DELETE RETURN 0.END;-- TO FIX PRO_QUERY_COUNT1 ISSUE CREATE OR REPLACE PROCEDURE PRO_QUERY_COUNT2IS V_COUNT INTEGER; V_FOUND_NUM INTEGER; V_DELETE_COUNT INTEGER;BEGIN SELECT COUNT(*) INTO V_COUNT FROM studenttable; --1 RECORDS IN TABLE V_FOUND_NUM:= SQL%ROWCOUNT; PRO_REMOVE_STU('9999'); --CALL PRO_REMOVE_STU(), BUT 9999 WAS NOT IN DB. V_DELETE_COUNT:=SQL%ROWCOUNT; dbms_output.put_line('TOTAL RECORD NUMBER OF TABLE: '||V_FOUND_NUM || ', REMOVE COUNT NUMBER: '||V_DELETE_COUNT);END; --------------------EXPLICIT CURSOR-------------------------------------------------------------------EXPLICIT CURSOR-------------------------------------------------------------------EXPLICIT CURSOR-------------------------------------------------------------------EXPLICIT CURSOR-----------------------------------------------DECLARE CURSOR CUR_STU IS SELECT STUDENT_NO,STUDENT_NAME FROM studenttable; V_STU_NO studenttable.STUDENT_NO%TYPE; V_STU_NAME studenttable.STUDENT_NAME%TYPE;BEGIN OPEN CUR_STU; LOOP FETCH CUR_STU INTO V_STU_NO,V_STU_NAME; dbms_output.put_line('STU_NO: '|| V_STU_NO||', STU_NAME: '|| V_STU_NAME); EXIT WHEN CUR_STU%NOTFOUND ; END LOOP; CLOSE CUR_STU;END;----use FOR LOOP TO FETCH CURSOR DATADECLARE CURSOR CUR_STU IS SELECT STUDENT_NO,STUDENT_NAME FROM studenttable;BEGIN FOR CUR_STU_TEMP IN CUR_STU LOOP dbms_output.put_line('STU_NO: '|| CUR_STU_TEMP.STUDENT_NO ||', STU_NAME: '|| CUR_STU_TEMP.STUDENT_NAME); END LOOP; END;--------------------TRANSACTION-------------------------------------------------------------------TRANSACTION-------------------------------------------------------------------TRANSACTION-------------------------------------------------------------------TRANSACTION-------------------------------------------------COMMIT--ROLLBACK--SAVEPOINT--LOCK TABLE?