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.
(p1 Mode datatype,p2 Mode datatype…) is
Procedure => Is a sub-program.
Proc_name => Name of the procedure.
Var_declaration => The type of variables declared.
SQL> create or replace procedure greetings 2 as 3 begin 4 dbms_output.put_line('Hello world!'); 5 end; 6 / Procedure created Hello world.
In the above example, procedure greetings will display the output as ‘HELLO WORLD’.
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.
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.
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.
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.
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.
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.
DROP PROCEDURE [schema.] procedure;
Schema => Specifies the procedure containing the schema
Procedure = > Procedure name will be specified for dropping.
sql>DROP FUNCTION fun1; FUNCTION DROPPED. PL/SQL Procedure successfully completed.
In the above example, the function fun1 will be dropped from the database table.