Notes

Insert Record using Anonymous Block

DECLARE
MAX DEPARTMENT.DEPARTMENT_ID%type := 0;
BEGIN
SELECT MAX(DEPARTMENT_ID) INTO MAX
FROM DEPARTMENT;
insert INTO DEPARTMENT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES(MAX+10,'TESTING','CHN-102');
COMMIT;
END;
/

Update Location

DECLARE
LOC_ID Department.LOCATION_ID%type;

BEGIN
SELECT LOCATION_ID INTO LOC_ID
from Department
WHERE LOCATION_ID LIKE 'HQ%';
update Department
SET LOCATION_ID = 'HQ-BLR-101'
WHERE LOCATION_ID = LOC_ID;

END;
/

Area of a Circle
DECLARE
I number(5);
A number(7,2);
BEGIN
FOR I IN 3..7
LOOP
A := 3.14 * (I*I);
insert INTO CIRCLE(RADIUS, AREA)
VALUES(I, A);
END LOOP;
END;
/

Insert credit - Procedure

create or replace procedure insert_credit(
credit_id IN number,
credit_card_number IN varchar,
credit_card_expire IN varchar,
holder_name IN varchar,
card_type IN varchar
) AS
BEGIN
INSERT INTO CREDIT_CARD(id, card_number, card_expire, name, cc_type)
VALUES(credit_id, credit_card_number, credit_card_expire, holder_name, card_type);

COMMIT;
END;
/

Select city - Procedure

CREATE OR REPLACE PROCEDURE select_city(
user_id IN number,
city_details OUT varchar
)AS
BEGIN
select city into city_details
from contact join user_details
ON contact.id = user_details.id
where user_details.id = user_id;

if city_details = 'Bangalore'
then
city_details := 'User is from Bangalore';
ELSIF city_details = 'Chennai'
then
city_details := 'User is from Chennai';
else
city_details := 'User is from other cities';
end if;
END select_city;
/

Insert a Record - Triggers

CREATE TRIGGER INSERT_TRIGGER
AFTER INSERT
ON EMPLOYEE
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('NEW EMPLOYEE DETAILS INSERTED');
END;
/

Package with a Procedure to update salary

CREATE OR REPLACE PACKAGE EMP_DESIGNATION
AS
PROCEDURE EMP_DETAILS(design employee.designation%TYPE, incentive number);
END EMP_DESIGNATION;
/
CREATE OR REPLACE PACKAGE BODY EMP_DESIGNATION
AS
PROCEDURE EMP_DETAILS(design employee.designation%TYPE, incentive number)
AS
BEGIN
UPDATE employee
SET employee.SALARY = (employee.SALARY + incentive)
WHERE employee.designation = design;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' employee(s) are updated.');
END EMP_DETAILS;
END EMP_DESIGNATION;
/

Display department names using Cursors

set serveroutput on
DECLARE
CURSOR ASC_DEPT is
select DISTINCT(DEPARTMENT_NAME) FROM Department ORDER BY DEPARTMENT_NAME;
DEPT Department.DEPARTMENT_NAME%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Department Names are :');
OPEN ASC_DEPT;
LOOP
FETCH ASC_DEPT INTO DEPT;
EXIT WHEN ASC_DEPT%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(DEPT);
END LOOP;
CLOSE ASC_DEPT;
END;
/
Procedure with Exception Handling

CREATE OR REPLACE PROCEDURE CHECK_AGE_ELIGIBILITY(
v_id IN EMPLOYEE.EMPID%TYPE,
v_name IN EMPLOYEE.EMPNAME%TYPE,
v_age IN EMPLOYEE.AGE%TYPE)
AS
EX EXCEPTION;
BEGIN
IF(V_AGE < 18)
THEN
RAISE EX;
else
INSERT INTO EMPLOYEE
VALUES(v_id, v_name, V_AGE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Age valid - Record inserted');
END IF;
EXCEPTION
WHEN EX THEN DBMS_OUTPUT.PUT_LINE('Age invalid - Record not inserted');
END;