In Oracle, a stored procedure is a named PL/SQL (Procedural Language/Structured Query Language) block that performs one or more specific tasks. Procedures are stored and can be repeatedly executed, providing a way to modularize and reuse code. Here's a brief overview of procedures in Oracle:
1. **Procedure Syntax:**
- A basic procedure structure includes the procedure name, parameters (optional), and the body containing executable code.
```sql
CREATE OR REPLACE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype)
IS
-- Declarations (optional)
BEGIN
-- Executable code
END procedure_name;
```
2. **Parameter Types:**
- Parameters can be either IN, OUT, or IN OUT.
- **IN:** Used for passing input values to the procedure.
- **OUT:** Used for returning output values from the procedure.
- **IN OUT:** Combines both input and output functionalities.
```sql
CREATE OR REPLACE PROCEDURE example_procedure (p_input IN NUMBER, p_output OUT NUMBER)
IS
BEGIN
-- Code using p_input
p_output := p_input * 2; -- Setting the output parameter
END example_procedure;
```
3. **Procedure Execution:**
- Procedures are executed using the `CALL` or `EXECUTE` statement.
```sql
CALL procedure_name(parameter1, parameter2);
```
4. **Exception Handling:**
- Procedures can include exception handling blocks to manage errors gracefully.
```sql
BEGIN
-- Code that might raise an exception
EXCEPTION
WHEN others THEN
-- Handle the exception
END;
```
5. **Stored Procedure Example:**
- Here's an example of a simple stored procedure:
```sql
CREATE OR REPLACE PROCEDURE greet_person (p_name IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_person;
```
- This procedure takes a person's name as input and prints a greeting using `DBMS_OUTPUT.PUT_LINE`.
6. **Advantages of Procedures:**
- Code Reusability: Procedures can be called from various parts of the application.
- Modularity: Complex logic can be encapsulated in procedures, promoting code organization.
- Maintainability: Changes to business logic can be made in one central location.
7. **Procedure Overloading:**
- Procedures can be overloaded, allowing multiple procedures with the same name but different parameter lists.
```sql
CREATE OR REPLACE PROCEDURE example_proc (param1 NUMBER)
IS
BEGIN
-- Code
END;
CREATE OR REPLACE PROCEDURE example_proc (param1 VARCHAR2)
IS
BEGIN
-- Code
END;
```
8. **Dependencies and Recompilation:**
- When a table or view referenced in a procedure is altered, the procedure becomes invalid. It needs to be recompiled to reflect the changes.
```sql
ALTER PROCEDURE procedure_name COMPILE;
No comments:
Post a Comment