Scribd
Upload
12 views7 pages

Coding Adv.

Uploaded by

sunil.babu101
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
12 views7 pages

Coding Adv.

Uploaded by

sunil.babu101
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 7

1).

ORACLE PROCEDURE:

select * from student;

create table student (id number(10) primary key,name varchar2(100));

create or replace procedure "INSERTUSER"


(id IN NUMBER,
name IN VARCHAR2)
is
begin
insert into student values(id,name);
end;
/

BEGIN
insertuser(102,'Sunil');
insertuser(103,'Suresh');
insertuser(104,'Ramesh');
dbms_output.put_line('record inserted successfully');
END;
/

2). ORACLE FUNCTION:

create or replace function adder(n1 in number, n2 in number)


return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
/

DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
/

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;
/

create or replace function subtract(n1 in number, n2 in number)


return number
is
n3 number(8);
begin
n3 :=n1-n2;
return n3;
end;
/

DECLARE
n3 number(2);
BEGIN
n3 := subtract(35,21);
dbms_output.put_line('Subtraction is: ' || n3);
END;
/

ECLARE
a number;
b number;
c number;
FUNCTION findMin(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 := findMin(a, b);
dbms_output.put_line(' Minimum of (23,45): ' || c);
END;
/

CREATE OR REPLACE FUNCTION TotalStaff


RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM test;
RETURN total;
END;
/

DECLARE
c number(2);
BEGIN
c := TotalStaff();
dbms_output.put_line('Total no. of Staff: ' || c);
END;
/

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;
/

DROP FUNCTION function_name;

CREATE OR REPLACE FUNCTION payment


RETURN number IS
total number(10) := 0;
BEGIN
SELECT sum (salary + bonus) into total
from test;
RETURN total;
END;
/

DECLARE
c number(7);
BEGIN
c := payment();
dbms_output.put_line('Total amount payable: ' || c);
END;
/

CREATE OR REPLACE FUNCTION payment1


RETURN number IS
total number(10) := 0;
BEGIN
SELECT sum (salary + bonus) into total
from test where id=1;
RETURN total;
END;
/

DECLARE
c number(7);
BEGIN
c := payment1();
dbms_output.put_line('Total amount payable: ' || c);
END;
/

alter table test add sumcol as (salary+bonus);

CREATE OR REPLACE FUNCTION sumcount123


RETURN number IS
total number(10) := 0;
BEGIN
update test
set totalpayment = (salary+bonus)
where id = 1;
RETURN total;
END;
/

DECLARE
c number(7);
BEGIN
c := sumcount123();
dbms_output.put_line('Total amount payable: ' || c);
END;
/

CREATE OR REPLACE FUNCTION sumcount2


RETURN number IS
total number(10) := 0;
BEGIN
update test
set totalpayment = (salary+bonus);
RETURN total;
END;
/

DECLARE
c number(7);
BEGIN
c := sumcount2();
dbms_output.put_line('Total amount payable: ' || c);
END;
/

CREATE OR REPLACE FUNCTION sumcount55


RETURN number IS
total number(10) := 0;
BEGIN
EXECUTE IMMEDIATE 'alter table test add sumresult as (salary+bonus)';
RETURN total;
END;
/

CREATE OR REPLACE FUNCTION crtab


RETURN number IS
total number(10) := 0;
BEGIN
EXECUTE IMMEDIATE 'create table testing
AS (select * from test)';
RETURN total;
END;
/

DECLARE
c number(10);
BEGIN
c := crtab();
dbms_output.put_line('Create table copy: ' || c);
END;
/

CREATE OR REPLACE FUNCTION sumcount69


RETURN number IS
total number(10) := 0;
BEGIN
EXECUTE IMMEDIATE 'alter table test add sumresult as (salary+bonus)';
RETURN total;
END;
/

DECLARE
c number(10);
BEGIN
c := sumcount69();
dbms_output.put_line('Total amount payable: ' || c);
END;
/

CREATE OR REPLACE TRIGGER trigg


BEFORE
INSERT OR
UPDATE OF salary, department OR
DELETE
ON test
BEGIN
CASE
WHEN INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
WHEN UPDATING('salary') THEN
DBMS_OUTPUT.PUT_LINE('Updating salary');
WHEN UPDATING('department') THEN
DBMS_OUTPUT.PUT_LINE('Updating department');
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END CASE;
END;
/

insert into test


values (12, 'Raheel dumm', 'Isloo', 35, 44000, 'Roger Officer', 'testii', 12000,
56000);

Oracle trigger lessons

Link 1):-
https://dotnettutorials.net/lesson/system-event-triggers-in-oracle/

Link 2):-
https://www.studytonight.com/plsql/plsql-triggers

Link 3):-
https://www.guru99.com/triggers-pl-sql.html

ALTER TABLE employees


ADD EMP_FK NUMBER(10);

ALTER TABLE employees


ADD FOREIGN KEY (EMP_FK) REFERENCES test(id);

update employees
set EMP_FK = 2 where emp_id=1;

CREATE OR REPLACE VIEW testview


(Employee_name, dept_name, location, Employee2, salary) AS
SELECT test.name, test.department, test.city, employees.emp_name, employees.salary
FROM test, employees
WHERE test.id = employees.emp_id;
/

select * from testview;

You might also like