Chapter -09 Creating Procedures 01
What Are Procedures?
- Are a type of subprogram that perform an action
- Can be stored in the database as a schema object.
- Promote reusability and maintainability
Creating Procedures:Overview
Creating Procedures with the SQL CREATE OR REPLACE Statement
- Use the CREATE clause to create a stand-alone procedure that is stored in the Oracle database.
- Use the OR REPLACE option to overwrite an existing procedure.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter 1 [mode] datatype1, parameter 2 [mode] datatype2,...)] IS | AS [local_variable_declareations,...] BEGIN --actions END [procedure_name];
Creating a Procedure And Invoking a Procedure
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE OR REPLACE PROCEDURE add_dept IS v_dept_id dept.department_id%TYPE; v_dept_name dept.department_name%TYPE; BEGIN v_dept_id := 280; v_dept_name := 'ST-Curriculum'; INSERT INTO dept(department_id,department_name) VALUES(v_dept_id,v_dept_name); DBMS_OUTPUT.PUT_LINE(' Inserted ' || SQL%ROWCOUNT || 'row'); COMMIT; END; SQL> EXEC add_dept; Inserted 1row PL/SQL procedure successfully completed.
The END Label
- You can append the name of the procedure directly after the END keyword when you complete your procedure.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
PROCEDURE display_stroes(region IN VARCHAR2) IS BEGIN ... END display_stores;
- This name serves as a label that explicitly links the end of the program with its begining.
- You should,as a matter of habit,use an END label.It is especially important to do so when you have a procedure that spans more than a single page,or is one in a series of procedures and functions in a packages body.
Naming Conventions of PL/SQL Structures Examples
PL/SQL Structure | Convention | Example |
Variable | v_variable_name | v_rate |
Constant | c_constant_name | c_rate |
Subprogram parameter | p_parameter_name | p_id |
Bind(host) Variable | b_bind_name | b_sarlary |
Cursor | cur_currsor_name | cur_emp |
Record | rec_record_name | rec_emp |
Type | type_name_type | ename_table_type |
Exception | e_exception_name | e_products_invalid |
File handle | f_file_handle_name | f_file |
What are parameters and parameter modes?
- Are declared after the subprogram name in the PL/SQL header
- Pass or communicate data between the calling environment and the subprogram.
- Are used like local variables but are dependent on their parameter-passing mode:
- -An IN parameter mode(default) provides values for a subprogram to process
- -An OUT parameter mode returns a value to the caller
- -AN IN OUT parameter mode supplies an input value,which may be returned(output) as a modifyed value.
Formal and Actual Prameters
- Formal parameters:Local variables declared in the parameter list of a subprogram specifictiona
- Actual parameters(or arguments):Literal values,variables and expression used in the parameter list of the calling subprogram.
Procedure Parameter Modes
- Parameter modes are specified in the formal parameter declaration,after the parameter name and before its data type.
- The IN mode is the default if no mode is specified.
CREATE PROCEDURE proc_name(param_name [mode] datatype)
Comparing the Parameter Modes
IN | OUT | IN OUT |
Default mode | Must be specified | Must be specified |
Value is paased into subprogram | Value is returned to the calling enviroment | Value passed into subprogram;value retuend to calling enviroment |
Forma parameter acts as a constan | Uninitalized variable | Initialized variable |
Actual parameter can be a literal,exception,constan,or initialized variable | Must be a variable | Must be a variable |
Can be assgined a default value | Can not be assigned a default value | Cannot be assigned a default value. |