Oracle Database Cursor and stored procedure PL/SQL

Oracle Database cursor and stored procedure

Spool abc. log -- start logging

Create or replace procedure db. PROC_FIRST (flag out interger,
T_DATE IN VARCHAR2)

AS

Varno varchar2 (20 );
Varprice varchar2 (20 );

CURSOR mycur (vartype number) is
Select emp_no, emp_zc from cus_emp_basic
Where com_no = vartype;

BEGIN
If NOT mycur % ISOPEN then
Open mycur (1, 043925 );
End if;
FETCH mycur INTO varno, varprice;
WHILE mycur % FOUND
LOOP

Dbms_output.put_line (varno | ',' | varprice );
If mycur % rowcount = 2 then
EXIT;
End if;
Fetch mycur into varno, varprice;
End loop;
CLOSE mycur;

 

EXCEPTION WHEN OTHERS THEN
ROLLBACK;

End;

Spool off

The record is not stored in the database. It is stored in the memory space like a variable. When using the record, you must first define the record structure and then declare the record variable. PL/SQL records can be viewed as user-defined data types.

Execute immediate sqlstr;

It parses and immediately executes dynamic SQL statements or PL/SQL blocks created when they are not running. The performance of dynamic creation and execution of SQL statements is advanced.

System variables and CURSOR of PL/SQL:

Cursor property:
% ISOPEN
% FOUND
% NOTFOUND
% ROWCOUNT

Exception:

SQLCODE () error code
SQLERRM () error message
NO_DATA_FOUND