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;
