ADC

  ADC


Practical No: 01


Aim: Writing PL/SQL Blocks with basic programming constructs by including following:

(A). If...then...Else, IF...ELSIF...ELSE... END IF

DECLARE

a number(3) := 100;

BEGIN

IF ( a = 10 ) THEN

dbms_output.put_line('Value of a is 10' );

ELSIF ( a = 20 ) THEN

dbms_output.put_line('Value of a is 20' );

ELSIF ( a = 30 ) THEN

dbms_output.put_line('Value of a is 30' );

ELSE

dbms_output.put_line('None of the values is matching');

END IF;

dbms_output.put_line('Exact value of a is: '|| a );

END;

(B). Case statement

DECLARE

grade char(1) := 'A';

BEGIN

CASE grade

when 'A' then dbms_output.put_line('Excellent');

when 'B' then dbms_output.put_line('Very good');

when 'C' then dbms_output.put_line('Well done');

when 'D' then dbms_output.put_line('You passed');

when 'F' then dbms_output.put_line('Better try again');

else dbms_output.put_line('No such grade');

END CASE;

END;


Practical No: 02

Aim: Writing PL/SQL Blocks with basic programming constructs

for following Iterative Structure:

A. While-Loop Statements

CODE:

DECLARE

a number(2) := 10;

BEGIN

WHILE a < 20 LOOP

dbms_output.put_line('value of a: ' || a);

a := a + 1;

END LOOP;

END;


B. For-Loop Statements

CODE:

DECLARE

a number(2);

BEGIN

FOR a in 10 .. 20 LOOP

dbms_output.put_line('value of a: ' || a);

END LOOP;

END;


Practical No: 03

Aim: Writing PL/SQL Blocks with basic programming constructs by including a GoTO to jump out of a loop and NULL as a  statement inside IF.

A. GoTO Statements

CODE:

DECLARE

a number(2) := 10;

BEGIN

<<loopstart>>

-- while loop execution

WHILE a < 20 LOOP

dbms_output.put_line ('value of a: ' || a);

a := a + 1;

IF a = 15 THEN

a := a + 1;

GOTO loopstart;

END IF;

END LOOP;

END;

B. Null Statements

DECLARE

num1 number:=10;

num2 number:=NULL;

BEGIN

IF num1>5 THEN

dbms_output.put_line ('value of num1 is greater than 5');

ELSE

num2:=20;

dbms_output.put_line ('value of num1 is less than or equal to 5');

dbms_output.put_line ('value of num1 is set to:'||num2);

NULL;

END IF;

END;

Practical No: 04 

Aim: a. Creating simple Sequences with clauses like START WITH,  INCREMENT BY, MAXVALUE, MINVALUE, CYCLE |NOCYCLE,   CACHE | NOCACHE, ORDER | NOORECER. 

A)Creating simple Sequences with clauses like START WITH, INCREMENT

BY, MAXVALUE, MINVALUE, CYCLE | NOCYCLE, CACHE |

NOCACHE, ORDER | NOORECER: 

Input: 

CREATE SEQUENCE sequence_1 

start with 1 

increment by 1 

minvalue 0 

maxvalue 100 

cycle; 

B)Creating and using Sequences for tables: 

create table orders

    (id number primary key,

    customer_name VARCHAR2(50),

    order_date Date);

Insert into orders(id, customer_name, order_date)

values(order_seq.NEXTVAL,'Rohit',SYSDATE);

Insert into orders(id, customer_name, order_date)

values(order_seq.NEXTVAL,'Ayush',SYSDATE);

Insert into orders(id, customer_name, order_date)

values(order_seq.NEXTVAL,'Pankaj',SYSDATE);

Insert into orders(id, customer_name, order_date)

values(order_seq.NEXTVAL,'Harshad',SYSDATE);

Insert into orders(id, customer_name, order_date)

values(order_seq.NEXTVAL,'Sahil',SYSDATE);

SELECT * FROM orders; Practical No: 05 Aim: Writing PL/SQL Blocks with basic programming constructs by including following:   (a) Sequential Statements 

BEGIN

IF i=1 THEN

GOTO mylabel;

else

i:= 10;

END IF;

<<mylabel>>

DBMS_output.put_line(i);

NULL;

END;

/

 (b) unconstrained loop

declare

l_loops number :=0;

begin

dbms_output.put_line('Before my loop');

loop

if l_loops > 4 then

exit;

end if;

dbms_output.put_line('Looped'||l_loops||'times');

l_loops := l_loops+1;

end loop;

dbms_output.put_line('After my loop');

end;

Practical No: 06

Aim: Writing Procedures in PL/SQL Block 

a. Create an empty procedure, replace a procedure and call procedure 

Input: 

CREATE OR REPLACE PROCEDURE greetings  

AS  

BEGIN 

 dbms_output.put_line('Hello World!'); 

END; 


b. Create a stored procedure and call it


CREATE OR REPLACE PROCEDURE greetings

AS

BEGIN

dbms_output.put_line('Hello World!');

END;

/



c. Define procedure to insert data

Input:


CREATE TABLE employees1 (

emp_id NUMBER PRIMARY KEY,

emp_name VARCHAR2(50),

emp_department VARCHAR2(50)

);


Output:


Input:

CREATE OR REPLACE PROCEDURE insert_employee (

p_emp_id NUMBER,

p_emp_name VARCHAR2,

p_emp_department VARCHAR2

) AS

BEGIN

INSERT INTO employees1 (emp_id, emp_name, emp_department)

VALUES (p_emp_id, p_emp_name, p_emp_department);

-- Commit the transaction to make the insertion permanent

COMMIT;

-- Optionally, display a success message

DBMS_OUTPUT.PUT_LINE('Employee record inserted successfully.');

EXCEPTION

WHEN OTHERS THEN

-- Handle exceptions and rollback the transaction in case of an error

ROLLBACK;

-- Optionally, display an error message

DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;




Input:


BEGIN

insert_employee(101, 'John Doe', 'HR');

END;



d. A forward declaration of procedure

CREATE OR REPLACE PROCEDURE calculate_square(number IN NUMBER) IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Square of ' || number || ' is ' || number *

number);

END;

Practical 7

Aim: Writing Functions in PL/SQL Block. 

a. Define and call a function 


 Input:

DECLARE  

 a number; 

 b number; 

 c number; 

FUNCTION findMax(x IN number, y IN number)  

RETURN number  

IS  

 z number; 

BEGIN 

 IF x > y THEN  

 z:= x; 

 ELSE  

 Z:= y; 

 END IF;  

 RETURN z; 

END; 

BEGIN 

 a:= 23; 

 b:= 45;  

 c := findMax(a, b); 

 dbms_output.put_line(' Maximum of (23,45): ' || c); 

END; 


b. Define and use function in select clause,

Code:

-- Create a sample employees table (for demonstration purposes)

CREATE TABLE employees (

employee_id NUMBER,

first_name VARCHAR2(50),

last_name VARCHAR2(50),

salary NUMBER,

department VARCHAR2(50)

);

-- Insert some sample data into the employees table

INSERT INTO employees VALUES (1, 'John', 'Doe', 50000, 'HR');

INSERT INTO employees VALUES (2, 'Jane', 'Smith', 60000, 'IT');

INSERT INTO employees VALUES (3, 'Michael', 'Johnson', 75000, 'Finance');

INSERT INTO employees VALUES (4, 'Emily', 'Lee', 55000, 'HR');

INSERT INTO employees VALUES (5, 'Robert', 'Williams', 70000, 'IT');

COMMIT;

-- Create a function to calculate the total salary of employees in a department

CREATE OR REPLACE FUNCTION get_total_salary_by_department(dept_name IN

VARCHAR2) RETURN NUMBER IS

total_salary NUMBER := 0;

BEGIN

SELECT SUM(salary) INTO total_salary

FROM employees

WHERE department = dept_name;

RETURN total_salary;

END;

-- Use the function in a SELECT statement

SELECT department, get_total_salary_by_department(department) AS total_salary

FROM employees

WHERE department = 'IT';


c. Call function in dbms_output.put_line

Code:

CREATE OR REPLACE FUNCTION concatenate_strings(str1 IN VARCHAR2, str2 IN

VARCHAR2) RETURN VARCHAR2 IS

BEGIN

RETURN str1 || str2;

END;

/

DECLARE

string1 VARCHAR2(50) := 'Hello, ';

string2 VARCHAR2(50) := 'World!';

result_string VARCHAR2(100);

BEGIN

-- Call the function and store the result in the variable

result_string := concatenate_strings(string1, string2);

DBMS_OUTPUT.PUT_LINE('Result: ' || result_string);

END;

/


d. Recursive function

Code:

DECLARE

num number;

factorial number;

FUNCTION fact(x number)

RETURN number

IS

f number;

BEGIN

IF x=0 THEN

f := 1;

ELSE

f := x * fact(x-1);

END IF;

RETURN f;

END;

BEGIN

num:= 6;

factorial := fact(num);

dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);

END;

/

Output:



e. Count Employee from a function and return value back

Code:

CREATE TABLE employees (

employee_id NUMBER,

employee_name VARCHAR2(50)

);

Output:


CREATE OR REPLACE FUNCTION count_employees RETURN NUMBER IS

total_employees NUMBER;

BEGIN

SELECT COUNT(*) INTO total_employees FROM employees;

RETURN total_employees;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN 0;

END;

/


DECLARE

emp_count NUMBER;

BEGIN

emp_count := count_employees();

DBMS_OUTPUT.PUT_LINE('Total number of employees: ' || emp_count);

END;

/


f. Call function and store the return value to a variable

Code:

CREATE OR REPLACE FUNCTION count_employees RETURN NUMBER IS

total_employees NUMBER;

BEGIN

SELECT COUNT(*) INTO total_employees FROM employees;

RETURN total_employees;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN 0;

END;


DECLARE

emp_count NUMBER;

BEGIN

emp_count := count_employees(); -- Call the function and store the return value

DBMS_OUTPUT.PUT_LINE('Total number of employees: ' || emp_count);

END;

/

Practical No: 08 

 Aim: Write a SQL block to handle exception by writing:   

 

a. Predefined Exceptions 

 Input: 

DECLARE 

 v_num1 NUMBER := 10; 

 v_num2 NUMBER := 0; 

 v_result NUMBER; 

BEGIN 

 -- Attempt to divide two numbers 

 BEGIN 

 v_result := v_num1 / v_num2; 

 DBMS_OUTPUT.PUT_LINE('Result: ' || v_result); 

 EXCEPTION 

 WHEN ZERO_DIVIDE THEN 

 DBMS_OUTPUT.PUT_LINE('Error: Division by zero'); 

 END; 

 -- Attempt to convert a string to a number 

 BEGIN 

 v_result := TO_NUMBER('abc'); 

 DBMS_OUTPUT.PUT_LINE('Result: ' || v_result); 

 EXCEPTION 

 WHEN VALUE_ERROR THEN 

 DBMS_OUTPUT.PUT_LINE('Error: Invalid conversion'); 

 END; 

END; 


b. User-Defined Exceptions

  Input:

x int:=13;
 y int:=0;
 div_r float;
 exp1 EXCEPTION;
 exp2 EXCEPTION;  
BEGIN
 IF y=0 then
 raise exp1;
 ELSIF y > x then
 raise exp2;
 ELSE
 div_r:= x / y;
 dbms_output.put_line('the result is '||div_r);
 END IF;
EXCEPTION
 WHEN exp1 THEN
 dbms_output.put_line('Error');
 dbms_output.put_line('division by zero not allowed');  
 WHEN exp2 THEN
 dbms_output.put_line('Error');
 dbms_output.put_line('y is greater than x please check the input');  
END;


 c. Redeclared Predefined Exceptions

 Input: 

DECLARE 

 v_num1 NUMBER := 10; 

 v_num2 NUMBER := 0; 

 v_result NUMBER; 

 pragma exception_init(zero_divide, -20001); 

BEGIN 

 -- Attempt to divide two numbers 

 BEGIN 

 v_result := v_num1 / v_num2; 

 DBMS_OUTPUT.PUT_LINE('Result: ' || v_result); 

 EXCEPTION 

 WHEN ZERO_DIVIDE THEN 

 DBMS_OUTPUT.PUT_LINE('Error: Division by zero'); 

 END; 

EXCEPTION 

 WHEN OTHERS THEN 

 DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLCODE || ' - ' || SQLERRM);  RAISE; 

END; 


Practical No: 09 

 Aim: Write an Implicit and explicit cursor to complete the task. 


Write an explicit cursor to complete the task. 

Source code: 

CREATE TABLE CUSTOMERS( 

 ID INT NOT NULL, 

 NAME VARCHAR (20) NOT NULL, 

 ADDRESS CHAR (25), 

 PRIMARY KEY (ID) 

); 


INSERT INTO CUSTOMERS (ID,NAME,ADDRESS) VALUES (1, 'Ramesh', 'Ahmedabad'); 

INSERT INTO CUSTOMERS (ID,NAME,ADDRESS) VALUES (2, 'Khilan', 'Delhi'); 

INSERT INTO CUSTOMERS (ID,NAME,ADDRESS) VALUES (3, 'kaushik', 'Kota'); 

commit;



DECLARE  

 c_id customers.id%type;  

 c_name customers.name%type;  

 c_addr customers.address%type;  

 CURSOR c_customers is  

 SELECT id, name, address FROM customers;  

BEGIN  

 OPEN c_customers;  

 LOOP  

 FETCH c_customers into c_id, c_name, c_addr;  

 EXIT WHEN c_customers%notfound;  

 dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);  

 END LOOP;  

 CLOSE c_customers;  

END;  

Implicit Code :

CREATE TABLE CUSTOMERS1( 

 ID INT NOT NULL, 

 NAME VARCHAR (20) NOT NULL, 

 AGE INT NOT NULL, 

 ADDRESS CHAR (25), 

 SALARY DECIMAL (18, 2), 

 PRIMARY KEY (ID) 

); 

/ 


INSERT INTO CUSTOMERS1 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 

INSERT INTO CUSTOMERS1 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 

INSERT INTO CUSTOMERS1 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 

Commit;


DECLARE  

 total_rows number(2);  

BEGIN  

 UPDATE CUSTOMERS1 

 SET salary = salary + 500;  

 IF sql%notfound THEN  

 dbms_output.put_line('no customers selected');  

 ELSIF sql%found THEN  

 total_rows := sql%rowcount; 

 dbms_output.put_line( total_rows || ' customers selected ');  

 END IF;  

END;  


Practical No: 10

Aim: Creating and working with Insert/Update/Delete Trigger using Before/After clause.


-- Create custom table

CREATE TABLE custom(

   Id INT,

   Name VARCHAR2(20),

   Score INT

);

-- Insert into custom Table

INSERT INTO custom (Id, Name, Score) VALUES (1, 'Sam', 800);

INSERT INTO custom (Id, Name, Score) VALUES (2, 'Ram', 699);

INSERT INTO custom (Id, Name, Score) VALUES (3, 'Tom', 250);

INSERT INTO custom (Id, Name, Score) VALUES (4, 'Om', 350);

INSERT INTO custom (Id, Name, Score) VALUES (5, 'Jay', 750);

-- insert statement should be written for each entry in Oracle Sql Developer


CREATE TABLE Affect (

   Id INT,

   Name VARCHAR2(20),

   Score INT

);


-- BEFORE INSERT trigger

CREATE OR REPLACE TRIGGER BEFORE_INSERT

BEFORE INSERT ON custom

FOR EACH ROW

BEGIN

   INSERT INTO Affect (Id, Name, Score)

   VALUES (:NEW.Id, :NEW.Name, :NEW.Score);

END;

/

INSERT INTO custom (Id, Name, Score) VALUES (6, 'Arjun', 500);

BEFORE DELETE Trigger

-- BEFORE DELETE trigger

CREATE OR REPLACE TRIGGER BEFORE_DELETE

BEFORE DELETE ON custom

FOR EACH ROW

BEGIN

   INSERT INTO Affect (Id, Name, Score)

   VALUES (:OLD.Id, :OLD.Name, :OLD.Score);

END;

/

DELETE FROM custom WHERE Id = 3;

BEFORE UPDATE Trigger

-- BEFORE UPDATE trigger

CREATE OR REPLACE TRIGGER BEFORE_UPDATE

BEFORE UPDATE ON custom

FOR EACH ROW

BEGIN

   INSERT INTO Affect (Id, Name, Score)

   VALUES (:OLD.Id, :OLD.Name, :OLD.Score);

END;

/

UPDATE custom SET Score = 900 WHERE Id = 5;

SELECT * FROM Affect;

SELECT * FROM custom;

Comments

Post a Comment

Popular posts from this blog

JBA

Image
Practical1-A A.1-Constructor Overloading public class Student { //instance variables of the class int id; String name; Student(){ System.out.println("this a default constructor"); } Student(int i, String n){ id = i; name = n; } public static void main(String[] args) { //object creation Student s = new Student(); System.out.println("\nDefault Constructor values: \n"); System.out.println("Student Id : "+s.id + "\nStudent Name : "+s.name); System.out.println("\nParameterized Constructor values: \n"); Student student = new Student(10, "David"); System.out.println("Student Id : "+student.id + "\nStudent Name : "+student.name); } } sdaDDSAD A.2-MethoD Overloading class Adder{ static int add(int a,int b){return a+b;} static int add(int a,int b,int c){return a+b+c;} } class TestOverloading1{ public static void main(String[] args){ System.out.println(A...
Read more