Day 5, advanced PL/SQL applications

5. Advanced PL/SQL applications

1. Design and Development of cursors
What is a cursor and how to use a cursor?
A cursor is a PL/SQL control structure that can explicitly control the processing of SQL statements to facilitate the processing of row data entries in the table.
Cursors can be displayed or implicitly displayed.
Cursor attributes: % found, % isopen, % notfound, % rowcount
Under sqlplus:
Example;
Declare
Cursor mycur is
Select * From books;
Myrecord books % rowtype;
Begin
Open mycur; open cursor
Petch mycur into myrecord; store the cursor in the variable.
While mycur % found loop % found indicates that data is desirable
Dbms_output.put_line (myrecord. books_id | ',' | myrecord. books_name );
Petch mycur into myrecord;
End loop;
Close mycur; close the cursor
End;
/
Save c: \ text.txt

Example: Use cursor Parameters
Declare
Cursor cur_para (ID varchar2) is the parameter here without length
Select books_name from books where books_id = ID;
T_name books. books_name % type; defines a t_name variable and a later type
Begin
Open cur_para ('001 ');
Loop
Petch cur_para into t_name;
Exit when cur_para % notfound;
Dbms_output.put_line (t_name );
End loop;
Close cur_para;
End;
/

% Isopen indicates true when the cursor is opened, and false when the cursor is closed.
% Rowcount total number of retrieved from the cursor

You can also use a cursor to modify data.
Example:
Declare
Cursor cur is
Select name from deptment for update;
Text varchar2 (10 );
Begin
Open cur;
Petch cur into text;
While cur % found Loop
Update deptment set name = Name | '_ t' where current of cur;
Petch cur into text;
End loop;
Close cur;
End;
/

Use of implicit cursors
Begin
For cur in (Select name from deptment) loop
Dbms_output.put_line (cur. Name );
End loop;
End;
/
Disadvantage: It must be compiled every time and cannot be stored in the database.

2. Stored Procedure
Creation of stored procedures, use of parameters, and execution of Stored Procedures
Creation Syntax:
Create [or replace] Procedure procedurename
[(Param1 [{In | Out | in out}] param1_type
[, (Param2 [{In | Out | in out}] param2_type
....
Is |
....
Begin
Proc_body;
End;
/
Example:
Create or replace procedure myproc (ID varchar2)
Is
Name varchar2 (10 );
Begin
Select books_name into name from books where books_id = ID;
Dbms_output.put_line (name );
End myproc; myproc process name can be left blank
/

Execute the Stored Procedure
Declare
TID varchar2 (10 );
Begin
TID: = '001 ';
Myproc (TID); Call functions, stored procedures
End;
/

Begin
Myproc ('001'); you can also call
End;
/

Execute myproc ('001'); in this way, you can also call stored procedures, but you cannot call stored procedures with output parameters.

Create or replace procedure myproc2 (ID varchar2, name out varchar2) name is the output parameter
Is
Begin
Select books_name into name from books where books_id = ID;
End;
/

Declare
TID varchar2 (10 );
Tname varchar2 (10 );
Begin
TID: = '001 ';
Myproc2 (TID, tname );
End;
/

3. storage function design
Function creation, parameter use, and function call

 

4. Design and Application of packages
What is a package? create and use a package