Oracle stored procedures, cursors use

Oracle Stored procedures:

Grammar:
CREATE [OR REPLACE] PROCEDURE procedure_name
(Arg1 [Mode1] datatype1,arg2 [Mode2] datatype2,...)
is [as]
Pl/sql Block;
Mode is used to specify the input and output parameters: In is an input parameter, out is an output parameter, which can be omitted when the input parameter is in,out.
If there are no input and output parameters, you can omit
(Arg1 [Mode1] datatype1,arg2 [Mode2] datatype2,...) This line

A simple example with input and output parameters

CREATE OR REPLACE PROCEDURE proc_in_out_test (var_empno number, var_ename out VARCHAR2) are BEGIN SELECT ename into Var_enam E from emp WHERE empno = var_empno; EXCEPTION when No_data_found THEN raise_application_error (-20000, ' the employee does not exist '); End;

Call this procedure

sql> var vempno number; sql> var vename varchar2 (10); Sql> exec:vempno:=7934; Sql> exec proc_in_out_test (: vempno,:vename); Pl/sql procedure successfully completed Vename---------MILLER sql> exec:vempno:=7935; Sql> exec proc_in_out_test (: vempno,:vename); Begin Proc_in_out_test (: Vempno,:vename); End ORA-20000: The employee does not exist ORA-06512: in "SCOTT." Proc_in_out_test ", line 7 ORA-06512: In line 1

Display the Tour banner method:

CURSOR cursor_name is select_statement

A simple example:

DECLARE V_empname Emp.ename%type; V_job Emp.job%type; V_deptno Emp.deptno%type; CURSOR Emp_test is-declares cursor SELECT ename, job from emp WHERE deptno = V_deptno; BEGIN V_deptno: = 10; OPEN emp_test; --Open cursor--loop cursor loop FETCH emp_test into V_empname, v_job; --Take value EXIT when emp_test%notfound; --Exit Cycle dbms_output.put_line when no records are recorded (' Empname= ' | | v_empname | | ', job= ' | | V_job); End LOOP; Close emp_test; End; --the properties of the cursor are:%found,%notfounrd,%isopen,%rowcount; --%found: Returns True when a record is retrieved--%NOTFOUNRD: Returns True when a record is not retrieved--%isopen: Returns True when a cursor is opened--%rowcount: represents the number of records retrieved, starting at 1

Parameterized cursors are only slightly different when declaring and opening cursors

CURSOR emp_test2 (P_deptno emp.deptno%type) is--declaration cursor
SELECT ename, job from emp WHERE deptno = P_deptno;

OPEN Emp_test2 (10); --Open cursor

Cursor variables:

Defining cursor Change types
TYPE type_name is REF cursor[return return_type];

CREATE OR REPLACE PROCEDURE cursorvariabletest (p_table in VARCHAR2) are type t_emp_dept is REF CURSOR;--Define cursor variable type v_curs Orvar t_emp_dept; --DECLARE CURSOR variable v_empno emp.empno%type; V_ename Emp.ename%type; V_job Emp.job%type; V_dname Dept.dname%type; V_loc Dept.loc%type; BEGIN IF p_table = ' emp ' THEN v_empno: = 7369; OPEN V_cursorvar for SELECT ename, the job from emp WHERE empno = v_empno; --Open the cursor variable ELSE IF p_table = ' Dept ' THEN Open V_cursorvar for SELECT dname, loc from dept; --Open the cursor variable ELSE raise_application_error (-20000, ' please input emp or dept! '); End IF; End IF; LOOP IF p_table = ' emp ' THEN FETCH v_cursorvar into V_ename, v_job; EXIT when V_cursorvar%notfound; Dbms_output.put_line (' ename= ' | | | v_ename | | ', job= ' | | V_job); ELSE FETCH V_cursorvar into V_dname, V_loc; EXIT when V_cursorvar%notfound; Dbms_output.put_line (' dname= ' | | | v_dname | | ', loc= ' | | V_LOC); End IF; End LOOP; Close V_cursorvar; --Close the cursor variable end;

Constraints and unconstrained cursor variables
An unconstrained cursor variable contains a specific return type (Type,rowtype,record, etc.), and the query's selection list must match the return type of the cursor, otherwise a predefined

The rowtype_mismatch exception that is defined. The cursor variable for the constraint has no return clause.