Learn PL SQL, Data Science and Automation

DECLARE CURSOR C1 IS SELECT COLUMN_NAME FROM TABLE WHERE CONDITIONS; BEGIN FOR R1 IN C1 LOOP INSERT INTO TABLE 2(COLUMN DEFINING) VALUES (VALUES DECLARATION); END LOOP; END ;

UPDATE TABLE_NAME SET COLUMN_NAME = LPAD(COLUMN_NAME, 4 , '000' ) WHERE CONDITIONS;

DECLARE CURSOR C1 IS SELECT MAX (COLUMN_NAME1) AS HEADERID FROM TABLE_NAME WHERE CONDITIONS ; CURSOR C2 IS SELECT ID FROM TABLE_NAME WHERE CONDITIONS AND COLUMN_NAME1 IS NULL ; L_MAX_HEADERID number ; BEGIN OPEN C1; FETCH C1 INTO L_MAX_HEADERID; CLOSE C1; FOR R1 IN C2 LOOP L_MAX_HEADERID : = L_MAX_HEADERID + 1 ; UPDATE TABLE_NAME SET COLUMN_NAME1 = L_MAX_HEADERID WHERE ID = R1.ID; END LOOP; END ;

DISABLE SELECT 'ALTER TABLE "' || A.TABLE_NAME || '" DISABLE CONSTRAINT "' || A.CONSTRAINT_NAME || '";' ENABLE_CONSTRAINTS FROM USER_CONSTRAINTS A WHERE A.CONSTRAINT_TYPE = 'R' AND A.R_CONSTRAINT_NAME IN ( SELECT A1.CONSTRAINT_NAME FROM USER_CONSTRAINTS A1 WHERE A1.TABLE_NAME = DECODE( UPPER ( '&1' ), 'ALL' ,A.TABLE_NAME, UPPER ( '&1' )) ); ENABLE SELECT 'ALTER TABLE "' || A.TABLE_NAME || '" ENABLE CONSTRAINT "' || A.CONSTRAINT_NAME || '";' FROM USER_CONSTRAINTS A WHERE A.CONSTRAINT_TYPE = 'R' AND A.TABLE_NAME = DECODE( UPPER ( '&1' ), 'ALL' ,A.TABLE_NAME, UPPER ( '&1' )) AND A.STATUS = 'DISABLED' ;

MINUS SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE MINUS SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE; INTERSECT SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE INTERSECT SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE; UNION SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE UNION SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE; UNION ALL SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE UNION ALL SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE;

Example 01: DECLARE CURSOR NAME_CUR IS SELECT LAST_NAME FROM STUDENT WHERE ROWNUM <= 10 ; TYPE LASTNAME_TYPE IS VARRAY( 10 ) OF STUDENT.LAST_NAME % TYPE; LAST_NAME_VARRAY LAST_NAME = LASTNAME_TYPE(); V_COUNT INTEGER = 0 ; BEGIN FOR NAME_REC IN NAME_CUR LOOP V_COUNT = V_COUNT + 1 ; LAST_NAME_VARRAY(V_COUNT) = NAME_REC.LAST_NAME; DBMS_OUTPUT.PUT_LINE( 'LAST NAME(' || V_COUNT || '):' || LAST_NAME_VARRAY(V_COUNT)); END LOOP; END ; Example 02: DECLARE TYPE MY_VARRAY_TYPE IS VARRAY( 10 ) OF NUMBER ; VARRAY MY_VARRAY_TYPE = MY_VARRAY_TYPE( 1 , 2 , 3 , 4 ); BEGIN DBMS_OUTPUT.PUT_LINE( 'MY VARRAY COUNT=' || VARRAY. COUNT ); DBMS_OUTPUT.PUT_LINE( 'MY VARRAY LIMIT=' || VARRAY.LIMIT); DBMS_OUTPUT.PUT_LINE( 'MY VARRAY FIRST=' || VARRAY.FIRST); DBMS_OUTPUT.PUT_LINE( 'MY VARRAY LAST=' || VARRAY.LAST); VARRAY.EXTEND( 2 , 4 ) DBMS_OUTPUT.PUT_LINE( 'MY VARRAY LAST=' || VARRAY.LAST); DBMS_OUTPUT.PUT_LINE( 'MY...

--CREATE FLASHBACK TABLE CREATE TABLE TABLE_NAME_TEMP AS SELECT * FROM TABLE_NAME AS OF TIMESTAMP TO_TIMESTAMP( 'YYYY-MM-DD HH:MI:SS' , 'YYYY-MM-DD HH:MI:SS' ); --UPDATE FROM FLASHBACK TABLE UPDATE TABLE_NAME SET COLUMN_NAME = 'VALUE' WHERE COLUMN_NAME = 'VALUE' AND ID_COLUMN IN ( SELECT ID_COLUMN FROM TABLE_NAME_TEMP WHERE COLUMN_NAME = 'VALUE' AND COLUMN_NAME = 'VALUE' );