DECLARE L_COUNT NUMBER := 5;
BEGIN
MYLABEL LOOP
DBMS_OUTPUT.PUT_LINE('CURRENT VALUE NEW:' || L_COUNT);
L_COUNT := L_COUNT - 1;
EXIT WHEN L_COUNT <= 0;
END LOOP;
END;
-- FOR LOOP
DECLARE L_INDEX := 0;
BEGIN
FOR MYINDEX IN 0 .. 5
LOOP
DBMS_OUTPUT.PUT_LINE('HELLO NEW');
END LOOP;
END;
BEGIN
FOR l_counter IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE( l_counter );
END LOOP;
END;
-- counter increment
DECLARE L_STEP_COUNTER NUMBER := 2;
BEGIN
FOR L_INDEX IN 0..20
LOOP
DBMS_OUTPUT.PUT_LINE(L_INDEX * L_STEP_COUNTER);
END LOOP;
END;
-- while loop
DECLARE L_NUMBER NUMBER := 5;
BEGIN
DBMS_OUTPUT.PUT_LINE('START LOOP '|| L_NUMBER);
WHILE L_NUMBER >= 0
LOOP
DBMS_OUTPUT.PUT_LINE('VALUE IS: ' || L_NUMBER);
L_NUMBER := L_NUMBER - 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('END LOOP');
END;
-- CURSOR
DECLARE
L_ID NUMBER(10) := 5;
CURSOR MYCURSOR IS SELECT * FROM AD.AD_DEPARTMENTS;
L_DATA MYCURSOR%ROWTYPE;
BEGIN
OPEN MYCURSOR;
LOOP
FETCH MYCURSOR INTO L_DATA;
EXIT WHEN MYCURSOR%NOTFOUND; -- Exit the loop when no more
rows are found
DBMS_OUTPUT.PUT_LINE('INSIDE CURSOR: ' || L_DATA.HOD);
END LOOP;
CLOSE MYCURSOR;
END;
-- LISTAGG
SELECT JOB_ID, LISTAGG(FIRST_NAME, ',') WITHIN GROUP(ORDER BY
FIRST_NAME) AS LISTEMP
FROM [Link] GROUP BY JOB_ID;
Select * from all_objects where object_name like '%MYTABLE%';
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME like '%MYTABLE%' AND
COLUMN_NAME LIKE '%PHONE%';
-- CHECK IF COLUMNS EXISTS OR NOT AND THEN ADD IT
DECLARE COL_EXISTS NUMBER(1);
BEGIN
SELECT COUNT(*) INTO COL_EXISTS FROM USER_TAB_COLS WHERE TABLE_NAME
like '%MYTABLE%' AND COLUMN_NAME LIKE '%PHONE%';
IF COL_EXISTS > 0 THEN
DBMS_OUTPUT.PUT_LINE('COLUMNS EXISTS');
ELSE
DBMS_OUTPUT.PUT_LINE('COLUMNS MISSING');
EXECUTE IMMEDIATE 'ALTER TABLE MYTABLE ADD PHONE NUMBER(10)';
END IF;
END;
-- CASE STATEMENT
SELECT ID, CASE id
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
ELSE 'D'
END as CASE_OUTPUT
FROM MYTABLE;
-- VIRTUAL COLUMN
create table virtual_col_ex(first_name nvarchar2(10), last_name
nvarchar2(10), full_name generated always as (first_name || ' ' ||
last_name));
INSERT ALL
INTO virtual_col_ex(first_name, last_name) values ('satish',
'kumar')
INTO virtual_col_ex(first_name, last_name) values ('laxmi', 'kant')
INTO virtual_col_ex(first_name, last_name) values ('rajitha', 'daneti')
INTO virtual_col_ex(first_name, last_name) values ('neel', 'dastidar')
select 1 from dual;
select * from virtual_col_ex;
-- Primary Key
CREATE TABLE MYTABLE_PK(ID NUMBER(10) PRIMARY KEY, NAME NVARCHAR2(10));
-- RAISE_APPLICATION_ERROR
DECLARE L_MY_NUM NUMBER(2) := 5;
BEGIN
DBMS_OUTPUT.PUT_LINE('START');
RAISE_APPLICATION_ERROR(-20002, 'SATISH KUMAR ERROR');
END;
-- UNIQUE INDEX
CREATE TABLE UNIQUE_INDEX_TBL(ID NUMBER(10), NAME NVARCHAR2(50));
INSERT ALL
INTO UNIQUE_INDEX_TBL(ID, NAME) VALUES(1, 'SATISH')
INTO UNIQUE_INDEX_TBL(ID, NAME) VALUES(2, 'KUMAR')
SELECT * FROM DUAL;
SELECT * FROM UNIQUE_INDEX_TBL;
-- CREATE UNIQUE INDEX:
CREATE UNIQUE INDEX IX_QNIQUE ON UNIQUE_INDEX_TBL(ID, NAME);
-- VERIFY INDEX
SELECT * FROM ALL_INDEXES WHERE TABLE_NAME LIKE '%UNIQUE_INDEX_TBL%';
-- TRY TO INSERT DUPLICATE VALUES
INSERT INTO UNIQUE_INDEX_TBL(ID, NAME) VALUES(1, 'SATISH');
-- O/P: ORA-00001: unique constraint
(SQL_GEJLFMSUHYCYGIOPJRFWBAMCP.IX_QNIQUE) violated ORA-06512: at
"SYS.DBMS_SQL", line 1721
-- CREATE SEQUENCE
CREATE SEQUENCE SEQ_SAT START WITH 1 INCREMENT BY 1
NOCYCLE MAXVALUE 10000 MINVALUE 0;
DROP SEQUENCE SEQ_SAT;
select SEQ_SAT.CURRVAL FROM DUAL; -- GET CURRENT VALUE
select SEQ_SAT.NEXTVAL FROM DUAL; -- GET NEXT VALUE
-- ANCHORED DECLARATIONS
DECLARE HOD_NAME AD.AD_DEPARTMENTS.HOD%TYPE;
BEGIN
SELECT HOD INTO HOD_NAME FROM AD.AD_DEPARTMENTS WHERE DEPARTMENT_ID
= 10;
DBMS_OUTPUT.PUT_LINE(HOD_NAME);
END;
-- DECLARE CONSTANT
DECLARE MY_CONSTANT CONSTANT NUMBER(2) := 10;
BEGIN
MY_CONSTANT := 20; -- GIVES ERROR BCOZ IT'S CONSTANT
DBMS_OUTPUT.PUT_LINE(MY_CONSTANT);
END;
-- BOOLEAN IN PL/SQLCODE
DECLARE MY_BOOL BOOLEAN := FALSE;
BEGIN
IF MY_BOOL THEN
DBMS_OUTPUT.PUT_LINE('IS TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('IS FALSE');
END IF;
END;
-- DBA QUERY
-- GET ALL USER OBJECTS: TABLE, VIEW, TRIGGER
SELECT * FROM USER_OBJECTS;
-- GET ALL INDEXES
SELECT * FROM ALL_INDEXES;