| |
|
Create a Table and Insert a Row
|
Here we will create a table with just 5 fields empid, empname, income, expense, savings ,
We will keep empid as Auto Increment field ,
unlike Mysql or MsAccess here u will not have a datatype Auto Increment but u must use a sequence ,
and call that sequence to increment value , I assume that u are able to login to SQL* Plus
SQL> create sequence increment_value start with 1 increment by 1;
SQL> create table emp_sal
(
empid number(5),
empname varchar2(20),
income number(5,2),
expense number(5,2),
savings number(5,2)
);
SQL> describe emp_sal;
NAME TYPE
--------------------------
EMPID NUMBER(5)
EMPNAME VARCHAR(20)
INCOME NUMBER(5,2)
EXPENSE NUMBER(5,2)
SAVINGS NUMBER(5,2)
SQL> insert into emp_sal values (increment_value.nextval,'James Smith',500,200,300)
1 row created.
|
|
Writing a Procedure to Insert a Row in Table
|
|
Procedures
|
|
|
A procedure is a subprogram that performs a specific action.
You write procedures using the syntax
PROCEDURE name [(parameter[, parameter, ...])] IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
where parameter stands for the following syntax:
parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype_name
[{:= | DEFAULT} expression]
|
Display the table contents and create a Procedure to add employee salary details and
execute the procedure
SQL> select * from emp_sal;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 500 300 200
2 Peter 600 300 300
SQL> CREATE OR REPLACE PROCEDURE add_emp_det
2 IS
3 BEGIN
4 insert into emp_sal values (increment_value.nextval,'Laura',350,250,100);
5 END add_emp_det;
6 /
Procedure created.
SQL> EXECUTE add_emp_det;
PL/SQL procedure successfully completed.
SQL> select * from emp_sal;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 500 300 200
2 Peter 600 300 300
21 Laura 350 250 100
|
|
Writing a Procedure to Increase Salary by 100 to all Employees
|
We will now write a small procedure to increment the salary by 100
SQL> CREATE OR REPLACE PROCEDURE increase_sal
2 IS
3 BEGIN
4 UPDATE emp_sal
5 SET INCOME=INCOME+100;
6 END increase_sal;
7 /
Procedure created.
SQL> EXECUTE increase_sal;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 600 300 200
2 Peter 700 300 300
21 Laura 450 250 100
|
|
Writing a Procedure to Increase Salary for a Given Employee ID using IN PARAMETER
|
We will write a procedure to increment salary to a Employee whose id is a input
Parameter , that is we will ask the user to enter the employee id
and based on that id we will increase the salary for that id
SQL> CREATE OR REPLACE PROCEDURE inc_sal_empid
2 (V_EMPID IN EMP_SAL.EMPID%TYPE)
3 IS
4 BEGIN
5 UPDATE emp_sal
6 SET INCOME = INCOME+200
7 WHERE EMPID = V_EMPID;
8 END inc_sal_empid;
9 /
Procedure created.
SQL> EXECUTE inc_sal_empid(1);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 800 300 200
2 Peter 700 300 300
21 Laura 450 250 100
|
|
Writing a Procedure to Increase Salary for a Given Employee ID using IN -
OUT Parameters
|
Here we will accept the employee id [IN-Param] from the user and display
corresponding employee name [OUT-Param] , Create a varible "g_name" to catch the OUT param
and display this variable value using PRINT
SQL> CREATE OR REPLACE PROCEDURE get_empname
2 (
3 V_EMPID IN EMP_SAL.EMPID%TYPE,
4 V_EMPNAME OUT EMP_SAL.EMPNAME%TYPE
5 )
6 IS
7 BEGIN
8 SELECT EMPNAME INTO V_EMPNAME FROM EMP_SAL WHERE EMPID = V_EMPID;
9 END get_empname;
10 /
Procedure created.
SQL> variable g_name varchar2(15);
SQL> execute get_empname(1,:g_name);
PL/SQL procedure successfully completed.
SQL> print g_name;
G_NAME
--------------------------------
James
|
|
Writing a Procedure to accept IN from user and Showing Corresponding OUT Parameter
|
Here we will ask the user to enter parameter rather than initialise the IN Param and return corresponding OUT Param
watch out the difference in Execution of this and Previos Procedure
SQL> set serverout on;
SQL> DECLARE
2 v_out_name varchar2(10);
3 BEGIN
4 select empname into v_out_name from emp_sal where empid=&var_ip_empid;
5 dbms_output.put_line ('The employee name is' || v_out_name);
6 END;
7 /
Enter value for var_ip_empid: 2
old 4: select empname into v_out_name from emp_sal where empid=&var_ip_empid;
new 4: select empname into v_out_name from emp_sal where empid=2;
The employee name is Peter
PL/SQL procedure successfully completed.
|
|
Writing a Procedure with IN OUT Parameter
|
This is a bit complex steps we will declare a variable which will be not only IN Param
but also OUT param to a procedure, make the variable, initilase the variable, write the procedure
to change the format of this variable, pass the variable to the procedure and execute the same
the way the variable comes out will be different from the way it went in to the Procedure
SQL> VARIABLE VAR_PH_NO VARCHAR2(20);
SQL> BEGIN
2 :VAR_PH_NO := '8006330575';
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> PRINT VAR_PH_NO;
VAR_PH_NO
--------------------------------
8006330575
SQL> CREATE OR REPLACE PROCEDURE PHONE_FORMAT
2 (
3 VAR_PH_NO_2 IN OUT VARCHAR2
4 )
5 IS
6 BEGIN
7 VAR_PH_NO_2 := '(' || SUBSTR(VAR_PH_NO_2,1,3) ||
8 ')' || SUBSTR(VAR_PH_NO_2,4,3) ||
9 '-' || SUBSTR(VAR_PH_NO_2,7);
10 END PHONE_FORMAT;
11 /
Procedure created.
SQL> EXECUTE PHONE_FORMAT(:VAR_PH_NO)
PL/SQL procedure successfully completed.
SQL> PRINT VAR_PH_NO;
VAR_PH_NO
--------------------------------
(800)633-0575
|
|
Different ways to Pass Parameters
|
There are Different ways u can send input Parameters to a Procedure during execution
few are listed below
SQL> CREATE OR REPLACE PROCEDURE insert_data
2 (
3 V_EMPNAME IN EMP_SAL.EMPNAME%TYPE DEFAULT 'UNKNOWN',
4 V_INCOME IN EMP_SAL.INCOME%TYPE DEFAULT 0,
5 V_EXPENSE IN EMP_SAL.EXPENSE%TYPE DEFAULT 0,
6 V_SAVINGS IN EMP_SAL.SAVINGS%TYPE DEFAULT 0
7 )
8 IS
9 BEGIN
10 INSERT INTO EMP_SAL VALUES
11 (increment_value.nextval,V_EMPNAME,V_INCOME,V_EXPENSE,V_SAVINGS);
12 END insert_data;
13 /
SQL> BEGIN
2 insert_data;
3 insert_data('Micheal',400,300,100);
4 insert_data(V_EMPNAME => 'Johnson',V_INCOME => 600,V_EXPENSE => 500,V_SAVINGS => 100);
5 insert_data(V_EMPNAME => 'Andrew',V_INCOME => 600,V_EXPENSE => 500,V_SAVINGS => 100);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 800 300 200
2 Peter 700 300 300
21 Laura 450 250 100
45 UNKNOWN 0 0 0
46 Micheal 400 300 100
47 Johnson 600 500 100
48 Andrew 600 500 100
7 rows selected.
|
|
Declaring Subprograms or Procedure inside Procedure
|
Now we will at how to call a Sub Procedure inside a Procedure, first
we will pass the new values of a record , delete the existing record for a
given EMPID and then INSERT record with new set of values
SQL> CREATE OR REPLACE PROCEDURE ins_del_rec
2 (
3 V_EMPID IN EMP_SAL.EMPID%TYPE,
4 V_EMPNAME IN EMP_SAL.EMPNAME%TYPE DEFAULT 'UNKNOWN',
5 V_INCOME IN EMP_SAL.INCOME%TYPE DEFAULT 0,
6 V_EXPENSE IN EMP_SAL.EXPENSE%TYPE DEFAULT 0,
7 V_SAVINGS IN EMP_SAL.SAVINGS%TYPE DEFAULT 0
8 )
9 IS
10 PROCEDURE ins_rec
11 IS
12 BEGIN
13 INSERT INTO EMP_SAL VALUES
14 (increment_value.nextval,V_EMPNAME,V_INCOME,V_EXPENSE,V_SAVINGS);
15 END ins_rec;
16 BEGIN
17 DELETE FROM EMP_SAL WHERE EMPID=V_EMPID;
18 ins_rec;
19 END ins_del_rec;
20 /
Procedure created.
SQL> BEGIN
2 ins_del_rec(21,V_EMPNAME => 'Lauren K',V_INCOME => 600,V_EXPENSE => 500,V_SAVINGS => 100);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 800 300 200
2 Peter 700 300 300
49 Lauren K 600 500 100
|
|
Invoking Procedure from a Stored Procedure using Cursors
|
Here we will call a procedure inside other procedure, or in simple words
put the employee ids in a cursor, run a for loop for all these employee ids
and increment salary of all the employees by 10
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 800 300 200
2 Peter 700 300 300
49 Lauren K 600 500 100
SQL> CREATE OR REPLACE PROCEDURE inc_sal_empid
2 (V_EMPID IN EMP_SAL.EMPID%TYPE)
3 IS
4 BEGIN
5 UPDATE emp_sal
6 SET INCOME = INCOME+10
7 WHERE EMPID = V_EMPID;
8 END inc_sal_empid;
9 /
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE cursor_demo
2 IS
3 CURSOR empid_curson IS
4 SELECT EMPID FROM EMP_SAL;
5 BEGIN
6 FOR emp_record IN empid_curson
7 LOOP
8 inc_sal_empid(emp_record.EMPID);
9 END LOOP;
10 COMMIT;
11 END cursor_demo;
12 /
Procedure created.
SQL> EXECUTE cursor_demo;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 810 300 200
2 Peter 710 300 300
49 Lauren K 610 500 100
|
|
Handling Exceptions inside a Procedure
|
Some time u may wanto get name of employee whose id u know but
he may not be there in Database then what happends , record not found
exception is raised and u must handle it in a decent way
SQL> CREATE OR REPLACE PROCEDURE ask_empname_execp
2 (
3 V_EMPID IN EMP_SAL.EMPID%TYPE,
4 V_EMPNAME OUT EMP_SAL.EMPNAME%TYPE
5 )
6 IS
7 BEGIN
8 SELECT EMPNAME INTO V_EMPNAME FROM EMP_SAL WHERE EMPID = V_EMPID;
9 EXCEPTION
10 WHEN OTHERS THEN
11 DBMS_OUTPUT.PUT_LINE ('Employee Not found in our Database');
12 END;
13 /
Procedure created.
SQL> set serverout on;
SQL> variable g_name varchar2(15);
SQL> execute ask_empname_execp(1,:g_name);
PL/SQL procedure successfully completed.
SQL> print g_name;
G_NAME
--------------------------------
James
SQL> execute ask_empname_execp(111,:g_name);
Employee Not found in our Database
PL/SQL procedure successfully completed.
|
|
Working with Functions
|
|
Functions
|
|
|
A function is a subprogram that computes a value.
Functions and procedures are structured alike, except
that functions have a RETURN clause. You write functions using the syntax
FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
where parameter stands for the following syntax:
parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype_name
[{:= | DEFAULT} expression]
|
Here we will define a small function which calculates savings=income-expense
and we will write a SQL which show the values of income, expense, savings
as in database and as when called though a function for empid 1
SQL> CREATE OR REPLACE FUNCTION FIND_SAVINGS (V_INCOME IN NUMBER, V_EXPENSE IN NUMBER)
2 RETURN NUMBER
3 IS
4 BEGIN
5 RETURN ( V_INCOME - V_EXPENSE );
6 END FIND_SAVINGS;
7 /
Function created.
SQL> SELECT
2 INCOME AS CURRENT_INCOME,
3 EXPENSE AS CURRENT_EXPENSE,
4 SAVINGS AS CURRENT_SAVINGS,
5 FIND_SAVINGS (INCOME, EXPENSE) AS SAVINGS_FROM_FUNCTION
6 FROM EMP_SAL WHERE EMPID=1;
CURRENT_INCOME CURRENT_EXPENSE CURRENT_SAVINGS SAVINGS_FROM_FUNCTION
-------------- --------------- --------------- ---------------------
810 300 200 510
|
|
Working with Cursors
|
|
Cursors
|
|
|
Oracle uses work areas to execute SQL statements and store processing information.
A PL/SQL construct called a cursor lets you name a work area and access its stored
information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly
declares a cursor for all SQL data manipulation statements, including queries that
return only one row. For queries that return more than one row, you can explicitly
declare a cursor to process the rows individually. An example follows:
DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;
The set of rows returned by a multi-row query is called the result set. Its size is the
number of rows that meet your search criteria.
Declaring a Cursor
Forward references are not allowed in PL/SQL.
So, you must declare a cursor before referencing it in other statements.
When you declare a cursor, you name it and associate it with a specific query using the syntax
CURSOR cursor_name [(parameter[, parameter]...)]
[RETURN return_type] IS select_statement;
where return_type must represent a record or a row in a database table,
and parameter stands for the following syntax:
cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]
For example, you might declare cursors named c1 and c2, as follows:
DECLARE
CURSOR c1 IS SELECT empno, ename, job, sal FROM emp
WHERE sal > 2000;
CURSOR c2 RETURN dept%ROWTYPE IS
SELECT * FROM dept WHERE deptno = 10;
|
The code below uses cursors, it accepts the record set from the
table emp_sal puts them into a cursor and in a for loop
accepts row by row [Record by Record] within the cursor and increments income by 15
SQL> select * from emp_sal;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 910 300 200
2 Peter 810 300 300
49 Lauren K 710 500 100
SQL> CREATE OR REPLACE procedure raise_sal_cur AS
2 emp_rec EMP_SAL%rowtype;
3 CURSOR c1 IS
4 SELECT EMPID, EMPNAME, INCOME FROM EMP_SAL;
5 BEGIN
6 FOR emp_rec IN c1 LOOP
7 UPDATE EMP_SAL SET INCOME=emp_rec.INCOME+15 WHERE EMPID=emp_rec.EMPID;
8 COMMIT;
9 END LOOP;
10 END;
11 /
Procedure created.
SQL> EXEC RAISE_SAL_CUR;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 925 300 200
2 Peter 825 300 300
49 Lauren K 725 500 100
|
|
Procedure to ADD RECORD Into EMP_SAL Table
|
This is the basic of any plsql u need to know how to write procedure
to add record into a table accepting values from the user ,
This entire web site focuses on one main aspect of coding that
is ability to add, modify, delete and view records others all
are derivates of these ...
SQL> select * from emp_sal;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 925 300 200
2 Peter 825 300 300
50 Karl 500 400 100
49 Lauren K 725 500 100
SQL> CREATE OR REPLACE PROCEDURE ADD_RECORD_EMPSAL AS
2 BEGIN
3 insert into emp_sal values
(
increment_value.nextval,
'&var_ip_empname',
&var_ip_income,
&var_ip_expense,
&var_ip_savings
);
4 END;
5 /
Enter value for var_ip_empname: Micheal
Enter value for var_ip_income: 300
Enter value for var_ip_expense: 200
Enter value for var_ip_savings: 100
old 5: insert into emp_sal values
(increment_value.nextval,'&var_ip_empname',&var_ip_income,&var_ip_expense,var_ip_savings);
new 5: insert into emp_sal values
(increment_value.nextval,'Micheal',300,200,100);
SQL> EXECUTE ADD_RECORD_EMPSAL;
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 925 300 200
2 Peter 825 300 300
50 Karl 500 400 100
49 Lauren K 725 500 100
51 Micheal 300 200 100
|
|
Procedure to MODIFY RECORD in EMP_SAL Table
|
Here u will update a record for a given empid and accepting other
parameters from the user..
SQL> select * from emp_sal;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 925 300 200
2 Peter 825 300 300
50 Karl 500 400 100
49 Lauren K 725 500 100
51 Micheal 300 200 100
SQL> CREATE OR REPLACE PROCEDURE MOD_RECORD_EMPSAL AS
2 BEGIN
3 UPDATE EMP_SAL SET EMPNAME='&var_ip_empname',
INCOME=&var_ip_income, EXPENSE=&var_ip_expense,
SAVINGS=&var_ip_savings WHERE EMPID=&v_ip_empid;
4 END;
5 /
Enter value for var_ip_empname: Micheal K
Enter value for var_ip_savings: 400
Enter value for v_ip_empid: 51
old 3: UPDATE EMP_SAL SET EMPNAME='&var_ip_empname',
INCOME=&var_ip_income, EXPENSE=&var_ip_expens
new 3: UPDATE EMP_SAL SET EMPNAME='Micheal K',
INCOME=400, EXPENSE=300, SAVINGS=400 WHERE EMPID=51
Procedure created.
SQL> EXECUTE MOD_RECORD_EMPSAL;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP_SAL;
EMPID EMPNAME INCOME EXPENSE SAVINGS
---------- -------------------- ---------- ---------- ----------
1 James 925 300 200
2 Peter 825 300 300
50 Karl 500 400 100
49 Lauren K 725 500 100
51 Micheal K 400 300 400
Other way of doing it using Dynamic calculation of savings rather than asking
the user to enter savings is as follows
CREATE OR REPLACE PROCEDURE MOD_REC_CAL_EMPSAL
(v_ip_empid number,var_ip_income number,var_ip_expense number)
AS
v_findsavings EMP_SAL.SAVINGS%TYPE;
BEGIN
v_findsavings := var_ip_income-var_ip_expense;
UPDATE EMP_SAL SET
INCOME=&var_ip_income,
EXPENSE=&var_ip_expense,
SAVINGS=v_findsavings
WHERE EMPID=&v_ip_empid;
commit;
END;
|
|
Reach me!
|
|
|
Java, J2EE, J2SE and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc.
in the United States and other countries. 
|
|
If you like this article and/or code 