===Oracle PL/SQL Fundamentals Part-2===
1. Creating and inserting Table department in Assignments_Schema
CREATE TABLE departments
(
dept_id NUMBER NOT NULL PRIMARY KEY,
dept_name VARCHAR2(100)
);
INSERT INTO departments values(1,'IT');
INSERT INTO departments values(2,'Accounting');
commit;
2. Creating and inserting Table employee in Assignments_Schema
CREATE TABLE employees
(
emp_id NUMBER NOT NULL PRIMARY KEY,
emp_name VARCHAR2(100),
emp_dept_id NUMBER,
emp_loc VARCHAR2(100),
emp_sal NUMBER,
emp_status VARCHAR2(1),
CONSTRAINT emp_dept_fk FOREIGN KEY(emp_dept_id) REFERENCES departments(DEPT_ID)
);
insert into employees values(10,'TOM',1,'Chennai',35000,'S');
insert into employees values(20,'TOM',1,'Mumbai',43000,'M');
insert into employees values(50,'TOM',2,'Bangalore',55000,'S');
insert into employees values(60,'TOM',2,'Kolkata',20000,'M');
commit;
NOTE : ---------------------------
%ROWCOUNT Attribute: How Many Rows Affected So Far?
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT,
UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned
no rows. In Example 6-8, %ROWCOUNT returns the number of rows that were deleted.
----------------------------------
3. Creating the procedure
note: SET emp_dept_id = '2' is intentionally made '2' instead of 2 even tho
emp_dept_id is a number
CREATE OR REPLACE PROCEDURE update_dept as
v_emp_id employees.emp_id%type := 10;
BEGIN
UPDATE employees
SET emp_dept_id = '2'
WHERE emp_id = v_emp_id;
dbms_output.put_line('Rows Updated '||SQL%ROWCOUNT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RAISE;
ROLLBACK;
END;
/
4. Warning settings
select dbms_warning.get_warning_setting_string from dual;
Go to Tools --> preferences --> expand databse and click on PL/SQL Compiler and
Enable ALL
Now when you run the above code, It would show errors on emp_dept_id = '2' -bind
type conversion
and Raise; - unreachable code
Below is the correct procedure code
CREATE OR REPLACE PROCEDURE update_dept as
v_emp_id employees.emp_id%type := 10;
BEGIN
UPDATE employees
SET emp_dept_id = 2
WHERE emp_id = v_emp_id;
dbms_output.put_line('Rows Updated '||SQL%ROWCOUNT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
ROLLBACK;
RAISE;
END;
/
5. how to compiler procedure in debug mode
2 ways:
-> using queries
ALTER PROCEDURE update_dept COMPILE DEBUG;
-> open procedure and click on "Compile for debug"
6. turn on native compilation
ALTER PROCEDURE update_dept COMPILE PLSQL_CODE_TYPE=NATIVE;
7. CHECK PL SQL COMPILE TYPES AND WARNINGS
SELECT PLSQL_CODE_TYPE,PLSQL_OPTIMIZE_LEVEL,PLSQL_WARNINGS
FROM ALL_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'UPDATE_DEPT';
8. To call/execute procedure update_dept
call update_dept();
exec update_dept;
execute update_dept;
9. To go out of a PL/SQL Block use 'return' keyword
BEGIN
<statement 1>
return;
<statement 2>
end;
Here statement 2 will never get called because return exits the whole pl sql code
10. How to write a function
create or replace function max_emp_sal return number
as
v_sal number;
begin
select max(emp_sal) into v_sal from employees;
return v_sal;
end;
10. To call function
example 1:
declare
v_sal number;
begin
v_sal := max_emp_sal;
dbms_output.put_line(v_sal);
end;
example 2:
select max_emp_sal from dual;
11. Enabling all plsql warnings
ALTER SESSION SET
PLSQL_WARNINGS='ENABLE:ALL';
12. Turn on native compilation
alter function max_emp_sal compile plsql_code_type = NATIVE;
13. Check all compilation and warning settingsselect PLSQL_CODE_TYPE,
PLSQL_OPTIMIZE_LEVEL,
PLSQL_WARNINGS
from ALL_PLSQL_OBJECT_SETTINGS
where name = 'MAX_EMP_SAL';
13. IN | OUT | IN OUT procedure
create or replace procedure update_emp(p_emp_id in number,p_dept_id number,
p_location out varchar2, p_status in out number)
as
BEGIN
dbms_output.put_line('emp_id: '||p_emp_id);
dbms_output.put_line('department_id: '||p_dept_id);
dbms_output.put_line('location: '||p_location);
dbms_output.put_line('status_initially: '||p_status);
END;
--run the above procedure
DECLARE
v_emp_id number := 10;
v_dept_id number := 1;
v_location varchar2(10) := 'CA';
v_status number := -1;
BEGIN
UPDATE_EMP(
v_emp_id,
v_dept_id,
v_location,
v_status
);
END;
OUTPUT :
emp_id: 10
department_id: 1
location: -- ITS NULL BECASE OUT MOADE IS ALWAYS IN WRITE ONLY MODE SO THE
VALUES CANT BE READ
status_initially: -1
14. IN | OUT | IN OUT example
>create procedure
create or replace procedure update_emp(p_emp_id in number,p_dept_id number,
p_location out varchar2, p_status in out number)
as
BEGIN
--p_dept_id := 9; cant change p_dept_id value coz the mode in IN and it means
read only
p_location := 'XXX';
p_status := 9;
END;
>clear screen;
set serveroutput on;
DECLARE
v_emp_id number := 10;
v_dept_id number := 1;
v_location varchar2(10) := 'CA';
v_status number := -1;
BEGIN
UPDATE_EMP(
v_emp_id,
v_dept_id,
v_location,
v_status
);
dbms_output.put_line('emp_id: '||v_emp_id);
dbms_output.put_line('department_id: '||v_dept_id);
dbms_output.put_line('location: '||v_location);
dbms_output.put_line('status_: '||v_status);
END;
OUTPUT:
emp_id: 10
department_id: 1
location: XXX
status_: 9
NOTE : OUT AND IN OUT values changes if anonymous block runs perfectly
if any exception is raised, then the value doesnt change
below is the example
create or replace procedure update_emp(p_emp_id in number,p_dept_id number,
p_location out varchar2, p_status in out number)
as
v_val_exp exception;
BEGIN
--p_dept_id := 9; cant change p_dept_id value coz the mode in IN and it means
read only
p_location := 'XXX';
if p_status < 1 then
raise v_val_exp;
end if;
p_status := 9;
exception
when v_val_exp then
dbms_output.put_line('v_val_exp exception block hit...!');
RAISE;
END;
clear screen;
set serveroutput on;
DECLARE
v_emp_id number := 10;
v_dept_id number := 1;
v_location varchar2(10) := 'CA';
v_status number := -1;
BEGIN
UPDATE_EMP(
v_emp_id,
v_dept_id,
v_location,
v_status
);
dbms_output.put_line('emp_id: '||v_emp_id);
dbms_output.put_line('department_id: '||v_dept_id);
dbms_output.put_line('location: '||v_location);
dbms_output.put_line('status_: '||v_status);
exception
when others then
dbms_output.put_line('Exception block hits');
dbms_output.put_line('emp_id: '||v_emp_id);
dbms_output.put_line('department_id: '||v_dept_id);
dbms_output.put_line('location: '||v_location);
dbms_output.put_line('status_: '||v_status);
END;
Output:
v_val_exp exception block hit...!
Exception block hits
emp_id: 10
department_id: 1
location: CA
status_: -1
You see even tho exception was raised after location value was updated, still
location value did not change.
remember unless the whole block executes succesfully, the
15. If you use nocopy then even if the exception is raised, the OUT and INOUT
values will not be reverted
syntax:
create or replace procedure update_emp(p_emp_id in number,p_dept_id number,
p_location out nocopy varchar2, p_status in out nocopy number)
16. Creating Package
--print emp id of all the people whose salary is greater than n (n is passed to
procedure as a param)
--creating package specification
create or replace package assignment_pack as
cursor emp_cur(e_sal number) is
select * from employees
where emp_sal > e_sal;
procedure update_emp_id(p_sal number);
END assignment_pack;
--creating package body
create or replace package body assignment_pack as
procedure update_emp_id(p_sal number) as
v_emp_cur emp_cur%rowtype;
begin
open emp_cur(p_sal);
loop
fetch emp_cur into v_emp_cur;
exit when emp_cur%notfound;
dbms_output.put_line(v_emp_cur.emp_id);
end loop;
close emp_cur;
end;
end assignment_pack;
-- calling the procedure
clear screen;
set serveroutput on;
begin
assignment_pack.update_emp_id(30000);
end;
OUTPUT:
10
20
50
--To provide grant to another schema (Example_Schema) to run the above package
grant execute on assignment_pack to Example;
now run the below query on example schema
clear screen;
set serveroutput on;
begin
Assignment.assignment_pack.update_emp_id(30000);
end;
17. Call a function from sql statement
select * from employees
where emp_sal = max_emp_sal;
--here max_emp_sal is a function name that returns the max salary among the
employee
