A chapter on Loops in the complete Microsoft C#.NET tutorial. The do loop evaluates the condition after. C – while loop in C programming with example. If while loop is having only one C statement then the parenthesis are optional. Sample PL/SQL Programs. This appendix provides several PL/SQL programs to guide you in writing your own. The sample programs illustrate several important PL/SQL concepts and features. This appendix discusses the following topics: Running the Programs. Sample 1. Batch Transaction Processing. Sample 5. Embedded PL/SQLSample 6. Calling a Stored Procedure. Running the Programs. All the sample programs in this appendix and several others throughout this guide are available online. So, they are preceded by the following comment: -- available online in file '< filename> '. You can find the online files in the PL/SQL demo directory. For the location of the directory, see the Oracle installation or user's guide for your system. Here is a list of the files and their locations in this guide: You run some samples interactively from SQL*Plus, others from Pro*C programs. You can experiment with the samples from any Oracle account.
However, the Pro*C examples expect you to use the scott/tiger account. Before trying the samples, you must create some database tables, then load the tables with data. You do that by running two SQL*Plus scripts, exampbld and examplod, which are supplied with PL/SQL. You can find these scripts in the PL/SQL demo directory. The first script builds the database tables processed by the sample programs. The second script loads (or reloads) the database tables. To run the scripts, invoke SQL*Plus, then issue the following commands: SQL> START exampbld. FOR Loop. The following example uses a simple FOR loop to insert ten rows into a database table. The values of a loop index, counter variable, and either of two character strings are inserted. Which string is inserted depends on the value of the loop index. Input Table. Not applicable. PL/SQL Block- - available online in file 'sample. NUMBER : = 1. 00. FOR i IN 1. 1. 0 LOOP. IF MOD(i,2) = 0 THEN - - i is even. INSERT INTO temp VALUES (i, x, 'i is even'). INSERT INTO temp VALUES (i, x, 'i is odd'). Output Table. SQL> SELECT * FROM temp ORDER BY col. NUM. Cursors. The following example uses a cursor to select the five highest paid employees from the emp table. Input Table. SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC. ENAME EMPNO SAL. KING 7. 83. 9 5. SCOTT 7. 78. FORD 7. 90. 2 3. JONES 7. 56. BLAKE 7. 69. CLARK 7. 78. ALLEN 7. 49. TURNER 7. 84. MILLER 7. 93. WARD 7. 52. 1 1. MARTIN 7. 65. ADAMS 7. 87. JAMES 7. 90. SMITH 7. 36. PL/SQL Block- - available online in file 'sample. CURSOR c. 1 is. SELECT ename, empno, sal FROM emp. ORDER BY sal DESC; - - start with highest paid employee. Scoping. The following example illustrates block structure and scope rules. An outer block declares two variables named x and counter and loops four times. Inside this loop is a sub- block that also declares a variable named x. The values inserted into the temp table show that the two x's are indeed different. Input Table. Not applicable. PL/SQL Block- - available online in file 'sample. NUMBER : = 0. counter NUMBER : = 0. FOR i IN 1. 4 LOOP. INSERT INTO temp VALUES (x, counter, 'in OUTER loop'). NUMBER : = 0; - - this is a local version of x. FOR i IN 1. 4 LOOP. INSERT INTO temp VALUES (x, counter, 'inner loop'). END LOOP. Output Table. SQL> SELECT * FROM temp ORDER BY col. NUM. Batch Transaction Processing. In the next example the accounts table is modified according to instructions stored in the action table. Each row in the action table contains an account number, an action to be taken (I, U, or D for insert, update, or delete), an amount by which to update the account, and a time tag used to sequence the transactions. On an insert, if the account already exists, an update is done instead. On an update, if the account does not exist, it is created by an insert. On a delete, if the row does not exist, no action is taken. Input Tables. SQL> SELECT * FROM accounts ORDER BY account. Value inserted.'. WHERE CURRENT OF c. UPDATE action SET status = 'Update: Success.'. WHERE CURRENT OF c. Process an INSERT. Updated instead.'. WHERE CURRENT OF c. Process a DELETE. No action taken.'. WHERE CURRENT OF c. Output Tables. SQL> SELECT * FROM accounts ORDER BY account. Embedded PL/SQLThe following example shows how you can embed PL/SQL in a high- level host language such as C and demonstrates how a banking debit transaction might be done. Input Table. SQL> SELECT * FROM accounts ORDER BY account? Status: Transaction completed. Status: Insufficient funds. Status: Transaction completed. Status: Transaction completed. Status: Account not found. Calling a Stored Procedure. This Pro*C program connects to Oracle, prompts the user for a department number, then calls procedure get. The procedure declares three index- by tables as OUT formal parameters, then fetches a batch of employee data into the index- by tables. The matching actual parameters are host arrays. When the procedure finishes, it automatically assigns all row values in the index- by tables to corresponding elements in the host arrays. The program calls the procedure repeatedly, displaying each batch of employee data, until no more data is found. Input Table. SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC. ENAME EMPNO SAL. KING 7. 83. 9 5. SCOTT 7. 78. FORD 7. 90. 2 3. JONES 7. 56. BLAKE 7. 69. CLARK 7. 78. ALLEN 7. 49. TURNER 7. 84. MILLER 7. 93. WARD 7. 52. 1 1. MARTIN 7. 65. ADAMS 7. 87. JAMES 7. 90. SMITH 7. 36. Stored Procedure/* available online in file 'sample. EXEC SQL BEGIN DECLARE SECTION. Define type for null- terminated strings.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2017
Categories |