A PLSQL Procedure is the sub program that returns multiple values. The name itself indicates that it can carry out some set of order tasks. Oracle database provides the mechanism to create procedures that can encapsulate a series of tasks performed in an order, and all these procedures can be called by various clients. The database compiles and stores it internally to enhance the re-usability and performance. A PLSQL Procedure is a named program store unit in Oracle database and provides required features of a stored program unit like consistency and performance optimization in the pl/sql code.

Syntax for creating a procedure

Sample example for creating a procedure. [c]SQL> create or replace procedure greetings 2 as 3 begin 4 dbms_output.put_line('Hello world!'); 5 end; 6 / Procedure created Hello world.[/c] In the above example, procedure greetings will display the output as 'HELLO WORLD'.

In SQL*Plus, the PLSQL Procedure can be compiled by simply typing it and putting a slash after the end keyword. The same can be performed in SQL by typing it in the SQL worksheet and executing the script. After compiling the procedure, it should be opened in a separate window in SQL containing the compile option on the top. The procedure can be compiled again using ALTER PROCEDURE. ALTER PROCEDURE update_dept compile will recompile the update_dept procedure. It can improve the performance of programming unit, which is in the native compilation and pl/sql optimize mode. The programming unit is valid for procedures, functions, as well as packages. Oracle by default compiles a programming unit of PL/SQL as a readable code intermediate machine. The machine code is interpreted by the run time engine during the run time. With negative executions, the code is directly compiled within native 'c' code that need not to be interpreted at run time thereby improving the performance. Native compilation is particularly useful for computation intensive program unit where a lot of pl/sql logic, expressions and computations are involved. Oracle provides an option called PL/SQL_CODE_TYPE that consists of two values: default INTERPRETED AND NATIVE VALUES.

The below example updates the table names in procedures. [c]SQL> create or replace procedure update_dept AS 2 I_emp_id employee.emp_id%TYPE:=10; 3 BEGIN 4 UPDATE EMPLOYEE 5 SET emp_dept_id=2 6 WHERE emp_id=I_emp_id; 7 COMMIT; 8 EXCEPTION 9 WHEN OTHERS THEN 10 DBMS_OUTPUT.PUT_LINE(SQLERRAM); 11 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); 12 ROLLBACK; 13 RAISE; 14 END update_dept; 15 / PL/SQL Program successfully executed.[/c] In the above example, the department table dept is updated as I_emp_id and the commit command is executed. If the alterations are not performed, it will raise exceptions.

The procedure in the PL/SQL can call another PL/SQL Procedure. Parameters without procedure can be called precisely by utilizing the EXECUTE or EXEC statements.

The below example describes the concept of calling function. [c]SQL> create or replace procedure adjust_salary( 2 in_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,in_percent IN NUMBER) 3 IS 4 BEGIN 5 --update employee's salary 6 UPDATE employee 7 SET salary=salary +salary*in_percent/100 8 WHERE employee_id=in_employee_id; 9 END; 10 / enter emp_id and sal 1 12000 empid=1; sal=14880 PL/SQL program successfully executed.[/c] In the above example, the salary of an emp will be updated, as set salary=salary+salary*in_percent/100 and the value will be stored in employee records.

When compiling the procedures, occasionally errors or warnings will be displayed. The errors or warnings are applicable to procedures as well as other program units like functions and packages. Errors will occur while compiling the program that contains misspelled table name and the compiler cannot find the name. Until the error is fixed, the compiler cannot execute the program and mark as invalid. Oracle compiler gives warning messages. These are conditions that are not errors, but leads to incorrect results and the warning starts with the PLW keyword. For instance, PLW-06002 indicates unreachable code. The server category is the set of conditions that might lead to incorrect results. The raise statement will immediately transfer control to the calling client, followed by the code upon which it will never get called. So, the compiler indicates that is the unreachable code warning. The warning can be set at different levels like enable, disable and are self explanatory or the compiler treats the warning as an error condition. The procedure that is compiled with the warning can still be executed, however, a procedure that is compiled with errors cannot be executed as the compiler marks it as invalid. And, finally errors can arise in check compiler log and warning arises when the created procedure contains compilation errors.

By using the DROP PROCEDURE proclamation, one can delete a procedure that is not available in the database. And, these announcements can't erase a methodology that contains the package. Drop bundle articulation will drop the whole bundle without the system.

The below example describes the procedure of dropping a table. [c]sql>DROP FUNCTION fun1; FUNCTION DROPPED. PL/SQL Procedure successfully completed.[/c] In the above example, the function fun1 will be dropped from the database table.

• PLSQL Procedure - Is a sub program that can return multiples values.
• Compiling Procedure - Is the process of compiling a program.
• Native Compilations - Is useful for computation of intensive program unit.
• Calling a pl/sql procedure - A procedure will call another procedure.
• Error - Check compiler log.
• Warning - Created with compilation errors.
• Drop procedure - Drop procedure is to drop a procedure from the database.