Oracle PL/SQL Programming Fifth Edition, chapter fifth, iterative processing with loops

1. First, the simplest loop loop.

Loop.sql

CREATE OR REPLACE PROCEDURE loop_demo AUTHID current_useris l_date date;    BEGIN l_date: = sysdate;        LOOP EXIT when l_date > sysdate + 10;        L_date: = l_date + 1; Dbms_output.    Put_Line (l_date); END LOOP; END loop_demo;/

First of all, I have no parameters for this process, so I'm creating that sentence, and there's no parentheses behind it.

And the following l_date: = L_date + 1;

I started using l_date + = 1;   L_date: + = 1; L_date + +; I started to try these three kinds, and then they were unsuccessful,

Changed to the present format, it seems that is not supported.

BEGIN Loop_demo (); end;/

This can be done directly to see the results.

2. Then a simple for loop, using the numeric value.

For.sql

CREATE OR REPLACE PROCEDURE for_demo AUTHID current_useris l_date date; BEGIN for I in 1..        Ten loops L_date: = Sysdate + i; Dbms_output.    Put_Line (l_date); END LOOP; END for_demo;/

You can then also use the cursor's.

Use a SELECT statement first as a collection of in

CREATE OR REPLACE PROCEDURE for_select AUTHID current_userisbegin for cur in (select ename from EMP) LOOP Dbms_output.    Put_Line (Cur.ename); END LOOP; end;/

3. The following is the practice of while

CREATE OR REPLACE PROCEDURE while_demo AUTHID current_useris l_date date;    BEGIN l_date: = sysdate;        While L_date < Sysdate + LOOP l_date: = l_date + 1; Dbms_output.    Put_Line (l_date); END LOOP; END while_demo;/

Then I saw the sleep function in the book, which I wanted to try, but not the same as I thought.

CREATE OR REPLACE PROCEDURE while_demo AUTHID current_useris l_date date;    BEGIN l_date: = sysdate;        While L_date < Sysdate + LOOP l_date: = l_date + 1; Dbms_output.        Put_Line (l_date);    Dbms_lock.sleep (1); END LOOP; END while_demo;/

First, you need to have permission to use Dbms_lock. Under the administrator

Grant execute on Dbms_lock to Scott;

I thought it was a stop every cycle, but the reality is, has been stopped, stopped to 15 seconds after the full print out.

What's going on here?

There is a pipe below, but I did not succeed, has been stuck, will not quit, and clearly gave sleep time should have passed.

DECLARE pipename CONSTANT VARCHAR2 (n): = ' signaler1 ';    Result INTEGER; Pipebuf VARCHAR2 (64);

BEGIN Result: = Dbms_pipe.create_pipe (Pipename);        LOOP Dbms_lock.sleep (5);            IF dbms_pipe.receive_message (pipename, 0) = 0 Then dbms_pipe.unpack_message (pipebuf);        EXIT when pipebuf = ' Stop ';    END IF; END LOOP; end;/

There was a problem here, I gave Scott that dbms_pipe permission, forgot to enter the user, directly with the SYS run, and then quit

Using Scott to run is not, error, and then should be used in the time of SYS to create a pipeline, Scott does not use this. Then I changed another name.

But why can't I quit ...

DECLARE pipename VARCHAR2 (n): = ' signaler1 '; Result INTEGER: = Dbms_pipe.create_pipe (Pipename); BEGIN dbms_pipe.pack_message (' Stop '); end;/

This is the one that stopped, but it's not good. Looking for a problem.

Oracle PL/SQL Programming Fifth Edition, chapter fifth, iterative processing with loops