Oracle procedure Basic Syntax

Transferred from: http://lorry1113.javaeye.com/blog/513851

Keywords: Oracle stored procedures

1. Basic structure CREATE OR REPLACE PROCEDURE stored procedure name (parameter 1 in number, parameter 2 in number) is variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN
END Stored Procedure Name
2.SELECT into STATEMENT the results of a SELECT query into a variable, you can store multiple columns in multiple variables at the same time, you must have one record, otherwise throw an exception (if no record throws No_data_found) Example: BEGIN Sele   CT col1,col2 into variable 1, variable 2 from typestruct where xxx;   EXCEPTION when No_data_found then xxxx;   END; ...
3.IF determine IF v_test=1 then BEGIN do something END; END IF;
4.while Loop while V_test=1 loop BEGIN XXXX END; END LOOP;
5. Variable assignment v_test: = 123;
6. Use the cursor with for in ...   is CURSOR cur are SELECT * from XXX; Begin for Cur_result in cur LOOP BEGIN v_sum: =cur_result. Column name 1+cur_result. Column Name 2 END;   END LOOP; END;
7. Cursor cursor c_user with parameters (c_id number) is the SELECT NAME from USER WHERE typeid=c_id;   OPEN c_user (variable value); LOOP FETCH c_user into V_name;     EXIT FETCH C_user%notfound;   Do something END LOOP; CLOSE C_user;
8. Using PL/SQL Developer Debug Connection database after establishing a test window input call SP code, F9 start debug,ctrl+n single-Step debugging reprint:Oracle Stored ProceduresKeyword: Oracle stored procedure stored procedure creation syntax:
Create or Replace procedure stored procedure name (param1 in type,param2 out type)
As
Variable 1 type (value range);
Variable 2 type (value range);
Begin
Select COUNT (*) into variable 1 from table A where column name =param1;
If (judging condition) then
Select column name into Variable 2 from table a where column name =param1;
Dbms_output. Put_Line (' Print information ');
elsif (judging condition) then
Dbms_output. Put_Line (' Print information ');
Else
Raise exception name (no_data_found);
End if;
Exception
When others then
Rollback;
End;

Precautions:
1, the stored procedure parameter does not take a range of values, in represents the incoming, out represents the output
2, variable with value range, followed by semicolon
3, it is best to use the count (*) function to determine if there is a record of the operation before judging the statement.
4, with Select ... Into ... Assigning values to variables
5, throwing exceptions in code with raise+ exception name

To name the exception
Named system exception causes
Access_into_null undefined Object
If the corresponding when is not included in the Case_not_found case and is not set
ELSE when
Collection_is_null collection Element not initialized
Curser_already_open Cursor already open
Dup_val_on_index duplicate values on a column corresponding to a unique index
Invalid_cursor operation on an illegal cursor
Invalid_number Embedded SQL Statements cannot convert characters to numbers
No_data_found using SELECT INTO not returning rows, or applying the index table uninitialized

Too_many_rows when performing a select INTO, the result set exceeds one row
Zero_divide Divisor is 0
Subscript_beyond_count element subscript exceeds the maximum value of a nested table or Varray
Subscript_outside_limit the subscript is specified as a negative number when using a nested table or Varray
When Value_error is assigned, the variable length is not sufficient to accommodate the actual data
Login_denied PL/SQL applications that connect to the Oracle database provide a
The correct user name or password
not_logged_on PL/SQL applications without a connection to the Oralce database
accessing data
Program_error PL/SQL Internal issues, you may need to reload the data dictionary & Pl./sql
System Package
Rowtype_mismatch host cursor variable is incompatible with the return type of the PL/SQL cursor variable
Self_is_null calling an object method on a NULL object when using an object type
Storage_Error when running PL/SQL, out of memory space
SYS_INVALID_ID Invalid ROWID string
timeout_on_resource         Oracle time-out when waiting for resources     syntax and examples: 1, Syntax for stored procedures to create stored procedures: Create [OR REPLACE] PROCEDURE procedure_name[(parameter_list)]{is|as}[local_declarations] Beginexecutable_statements[exceptionexception_handlers]end [procedure_name]; Where: Procedure_name is the name of the procedure. Parameter_list is a list of parameters. Local_declarations is a partial declaration. Executable_statements is an executable statement. Exception_handlers is an exception handler. Example 1: Demonstrate the creation process (a default value is assigned to the in parameter in the parameter list and cannot be assigned the default value for out, in out parameters) create or replace procedure find_emp (Emp_no in number:=7900) Asempname varchar2 beginselect ename into EmpName from EMP where Empno=emp_no;dbms_output.put_line (' Employee name is ' | | EmpName); Exceptionwhen no_data_found thendbms_output.put_line (' Employee number not found '); end find_emp; Calling procedure: EXECUTE Procudure_name ( Parameters_list), can also be called in the process, write directly on the procudure_name without having to write execute.

Example 2: Demo creating procedure with out parameters create or replace procedure test (value1 varchar2,value2 out number) is identity number; Begin select Sal into the identity from EMP where empno=value1; If identity<2000 then value2:=1000; else value2:=500; End If; End

Call the process with out parameters: declare value2 number; Begin Test (' 7900 ', value2); Dbms_output.put_line (value2); End

Example 3: Demo creating a procedure with an out parameter create or replace procedure swap (P1 in out number,p2 on out number) is v_temp number; Begin V_TEMP:=P1; P1:=P2; P2:=v_temp; End

Call the process with an out parameter: Declare NUM1 number:=100; Num2 number:=200; Begin Swap (NUM1,NUM2); Dbms_output.put_line (' num1= ' | | NUM1); Dbms_output.put_line (' num2= ' | | NUM2); End

Example 4: Granting the execution permission of a procedure to another user grant execute on find_emp to Scott; GRANT EXECUTE on swap to public; Grant the Execute permission of the find_emp process to the user Scott, who grants all database users permission to perform the swap process. Delete procedure syntax: Drop PROCEDURE procudure_name;

2. The syntax of the function definition function is as follows: CREATE [OR REPLACE] Function function_name [(parameter_list)] RETURN datatype {is|as} [local_declarations ] BEGIN executable_statements [EXCEPTION exception_handlers] END [function_name]; Where: Function_name is the name of the function. Parameter_list is a list of parameters. Local_declarations is a partial declaration. Executable_statements is an executable statement. Exception_handlers is an exception handler. Note When using functions: Formal parameters must use only the database type, not the PL/SQL type. The return type of a function must also be a database type. Functions cannot be executed alone, but can only be invoked through SQL statements or a PL/T program block. Example 5: Show how to create a function created or Replace function Fun_hello return VARCHAR2 is begin return ' friends, hello '; End Call function: Select Fun_hello from dual;

Function Authorization: Same as the authorization of the same as specific see Example 4. Delete functions: Drop function Function_name

Procedure and function differential procedure function as part of the expression as a PL/SQL statement, the call in the specification does not contain a return clause that must contain a return clause in the specification does not return any value that must return a single value can contain a return statement, but unlike a function, it cannot be used to return a value Must contain at least one return statement

3. Syntax for package Creation Package Specification: Create [OR REPLACE] Packages package_name is|as [public type and item declarations] [Subprogram Specificati ONS] END [package_name]; Where: Package_name is the name of the package. The public type and item declarations are declaration types, constants, variables, exceptions, cursors, and so on. Subprogram specifications declares the PL/SQL subroutine. Example 6: Demo Creating package specifications Create or Replace packages Pack_op is procedure Pro_print_ename (ID number); Procedure Pro_print_sal (ID number); function fun_re_date (ID number) return date; End

Syntax for creating a package body: Create [OR REPLACE] Packages body package_name is|as [public type and item declarations] [Subprogram bodies] [BEG In initialization_statements] END [package_name]; Where: Package_name is the name of the package. The public type and item declarations are declaration types, constants, variables, exceptions, cursors, and so on. Subprogram bodies is the definition of public and private PL/SQL subroutines.

Example 7: Demo Creating a package principal create or replace the pack body Pack_op is procedure Pro_print_ename (ID number) is name Emp.ename%type; Begin select ename into name from EMP where Empno=id; Dbms_output.put_line (' Staff name: ' | | name); End Pro_print_ename; Procedure Pro_print_sal (ID number) is salary emp.sal%type; Begin select Sal into salary from EMP where empno=id; Dbms_output.put_line (' Staff salary: ' | | Salary); End Pro_print_sal; function fun_re_date (ID number) Return date is Bedate emp.hiredate%type; Begin select HireDate to Bedate from EMP where empno=id; return bedate; End Fun_re_date; End Pack_op;

Example 8: Calling the procedure and functions created in the package exec pack_op.pro_print_ename (7900); EXEC pack_op.pro_print_sal (7900); Select Pack_op.fun_re_date (7900) from dual;

Example 9: A cursor in a demo package creates a package specification for Create or replace packages pack_emp is cursor cur_emp return emp%rowtype; Procedure Pro_cur; End Pack_emp;

Creating a package body create or replace the packages body pack_emp is the cursor cur_emp return emp%rowtype is a select * from EMP; Procedure Pro_cur is rec_emp emp%rowtype; Begin open Cur_emp; Loop fetch cur_emp into rec_emp; Exit when Cur_emp%notfound; If rec_emp.sal<1000 then Dbms_output.put_line (' Employee Pay: ' | | rec_emp.sal| | ' We need to redouble our efforts to raise wages '; elsif rec_emp.sal>=1000 and rec_emp.sal<2000 then Dbms_output.put_line (' Employee Salary: ' | | rec_emp.sal| | ', pay general, fight for a department manager to do '); else Dbms_output.put_line (' Employee Salary: ' | | rec_emp.sal| | ', the salary is good, strive for a general manager to do '); End If; End Loop; End Pro_cur; End Pack_emp;

Call the procedure in the package to invoke the cursor in the package exec pack_emp.pro_cur;

Example 10: A stored procedure returns the cursor child package (This package returns a r_cur cursor) CREATE OR REPLACE scott.pk_wt is the type mytype is REF CURSOR; Procedure P_WT (Mycs out MyType); End

CREATE OR REPLACE Package BODY scott.pk_wt are procedure p_wt (Mycs out MyType) is R_cur MyType; Begin open R_cur for SELECT * from EMP; Mycs:=r_cur; End P_WT; End PK_WT;

Querying information about procedures, functions, and packages: User_objects data dictionary View column object_name format A18 select Object_name,object_type from User_objects where object_type in (' PROCEDURE ', ' FUNCTION ', ' package ', ' package BODY ');

Oracle procedure Basic Syntax