Introduction to PL/SQL and SQL

For Scripts I can use:
set heading off
set feedback off
set pagesize 0
set trimspool on

TRIMSPOOL OFF/ON = When we spool to a file, we get several "white spaces" per line, if we want to avoid that, use TRIMSPOOL ON.

MARKUP HTML ON/OFF = Will let you generate an HTML report. Example
SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL emp1.html
SELECT * FROM emp;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON

NUMWIDTH Num = Can be use to modify the width of all the columns with numbers, by default is 10

NUMFORMAT ...... = Define format for all the numbers.

PAGESIZE Num = # of rows per page, defaault value is 66

SHOW ALL or SHOW command = Showsen state of a a particular command or all of them.

RECSEP ON/OFF = Por default el parametro RECSEP esta seteado en WRAP. Esto hace que despues de cada "wrapped record" se insertara el caracter en que se haya seteado RECSEPCHAR, que por defecto es un espacio en blanco. Poniendolo en OFF se eliminara la linea en blanco entre los registros.
The recsep command has basically two functions:
    1. SQL> set recsep off (returns all rows single-spaced)
    2. SQL> set recsep each
    (inserts a linefeed between each row/double-spaced)

UNDERLINE = With this variable we define what is going to be shown after the header of each column. Default value is ---

NOTE= I recommend to use the following at the top of your scripts to clean all variables:
                CLEAR BREAKS
                CLEAR COMPUTES

COLSEP <char> = Generate comma or pipe delimited output for tools such as Excel, Access, and so on while spooling from SQL*Plus. Example:
set pages 0
set feedback off
set colsep ,
select object_name, object_type, status, created
from all_objects
where rownum < 10 ;
Output:
ALL_ALL_TABLES                ,SYNONYM           ,VALID ,14-MAY-03
ALL_APPLY                     ,SYNONYM           ,VALID ,14-MAY-03
ALL_APPLY_CONFLICT_COLUMNS    ,SYNONYM           ,VALID ,14-MAY-03
ALL_APPLY_DML_HANDLERS        ,SYNONYM           ,VALID ,14-MAY-03
ALL_APPLY_ERROR               ,SYNONYM           ,VALID ,14-MAY-03
ALL_APPLY_KEY_COLUMNS         ,SYNONYM           ,VALID ,14-MAY-03
ALL_APPLY_PARAMETERS          ,SYNONYM           ,VALID ,14-MAY-03
ALL_APPLY_PROGRESS            ,SYNONYM           ,VALID ,14-MAY-03
ALL_ARGUMENTS                 ,SYNONYM           ,VALID ,14-MAY-03

For TAB delimited output, you can use:
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep "&TAB"

Interaction with SQL
Use PROMPT to add comments or actions
Use ACCEPT to get values from the user, you could also use & 2 times, which makes Oracle to ask for that value
If I don't want to see comments before ACCEPT use ECHO OFF y VERIFY OFF. Example:
accept month number prompt 'Month to Calculate: '

Titles on Reports
Top Title
TTITLE (center, left o right) ‘mi Titulo’
TTITLE center ‘Un titulo’ skip 1 right ‘Otro Título’ skip 2

You could also define variables with a title and use it later, example:
DEFINE LINE1 = 'First Line middle'
DEFINE LINE2 = 'Second Line Left side'
DEFINE LINE3 = 'Third line rigth side'
TTITLE CENTER LINE1 SKIP 1 LEFT LINE2 SKIP1 RIGHT LINE3

If you want to show the Number of Pages, you can use the function SQL.PNO. Example:
TTITLE LEFT ‘Monthly Report’ RIGHT ‘PAGE:’ FORMAT 999 SQL.PNO SKIP 2

The COLUMN command is a way to format the results of a select, if you need a longer tittle you could youse the | (pipe) option. Another option is to wrap the tittle using the following options:
    WORD_WRAPPED =
    WRAPPED =
    TRUNC =

The full format is the following:
COLUMN nom_col HEADING ‘mi tittle’ NEW_VALUE variable_to_the_tittle NOPRINT NULL text_to_show_if_null OLD_VALUE variable_to_btittle WORD_WRAPPED
WRAPPED
TRUNC

With NOPRINT with NEW_VALUE you could "hide" a variable to print it later in another place, example:
COLUMN today NEW_VALUE xtoday NOPRINT

This will create a new variable "xtoday" that will have the value of variable "today" but is not going to be printed.

Break by Column
This is one of the most used features on reports. The BREAK command will not show repetitive values on a select using order by. Example:
BREAK ON break_column

If I want to have an empty line or empty Page bwfore a new value:
BREAK ON break_column SKIP (N_renglones o PAGE)

Calculations
One of the most important commands is COMPUTE which let me calculate totals by a specific field. Its format is:

BREAK ON break_column
COMPUTE math_function OF col_to_apply_function ON variable_collecting_results

If you want different subtotals use several BREAK, example:
BREAK ON company ON division ON Departamento SKIP 1

Using:
BREAK ON REPORT ON col1
COMPUTE SUM OF ...... ON .......... REPORT
You will calculate not only the sub-totals by group, also a total at the end.

Options for BREAK ON:
COL =
ROW = Performs a BREAK on each row
PAGE = Performs a BREAK by page
REPORT = Performs a BREAK at the end of the report

Options for COMPUTE :
SUM – MIN – MAX – AVG – STD
VAR – COUNT – NUM (like count but for nulls)

Single Row Functions

Type	Function	Returns
Number	abs(n)	Absolute value of n. i.e. removes the sign.
	ceil(n)	Smallest integer >=n
	floor(n)	Largest integer <=n
	mod(n,m)	Remainder of n divided by m
	power(n,m)	n raised to the power m
	round(n[,m])	n rounded to m digits to the right of the decimal point. m = 0 by default.
	sign(n)	-1 if n<0 0 if n=0 1 if n>09
	sqrt(n)	Ö n
	trunc(n[,m])	as round but there is no rounding up.
Character	chr(n)	the character corresponding to the decimal value e.g. In the ascii character set chr(10) is a new line character.
	concat(s1,s2)	s1\|\|s2 (used for portability where the ‘\|’ character may cause problems)
	initcap(s)	s with the first letter of each word in upper case
	lower(s)	s with all letters lower case
	lpad(s,n[,c])	s left-padded with c’s to a size of n
	rpad(s,n[,c])	as lpad but to the right
	replace(s1,s2[,s3])	s1 with all instances of s2 replaced by s3
	ltrim(s1[,s2])	s1 with letters in s2 trimmed from left until a letter not in s2 is found.
	rtrim(s1,n,[s2)	as ltrim but to the right
	substr(s1,n,m)	the m character long substring of s1 beginning with character n
	translate(s1,s2,s3)	s1 but with each character in s2 replaced by the corresponding letter from s3.
	upper(s)	s with all letters upper case.
Character returning number	ascii(c)	returns the decimal value of c in the character set of the database (note that this may not be ascii!!)
	instr(s1,s2[,n[,m]])	returns the character position of the mth occurrence of the string s2 in s1 beginning with the nth character.
	length(s1)	the length of s1
Date	add_months(d,n)	the date n days after d
	last_day(d)	the date of the last day of the month which d is in
	months_between(d1,d2)	number of months between d1 and d2; this is expressed as a floating point number calculated on the basis if 31 days per month.
	next_day(d, day)	e.g. if day=Monday then returns the date of the first Monday after d.
	round(d,f)	returns d rounded to the nearest f (e.g. ‘Day’, ‘Month’, etc)
	sysdate	the current database date and time
	trunc(d,f)	as round but truncates rather than rounds.
Conversion	to_char(d,f) to_char(n,f)	Converts a date or a number to characters.
	to_date(s1,f)	Converts a character string in format f to a date
	to_number(c,f)	Converts a character string in format f to a number
Other	greatest(e1,e2….)	Whichever of e1, e2, etc is the greatest
	least(e1,e2,….)	Whichever of e1, e2, etc is the least
	nvl(e1, e2)	e1 unless it is null in which case e2
	user	the current database username
Decode	decode	decode is not strictly a function but rather a bit of non-standard SQL syntax (for that reason its available in SQL but not in PL/SQL).

Simulating IF/THEN/ELSE logic
There is no IF command in SQL*Plus. However, you can use some of the techniques shown above along with the DECODE operator to simulate an IF command.
In this example, the user is asked to enter the number of a script to run. The user’s answer is evaluated and the appropriate script is run. If the user entered an incorrect value, the badscript.sql script is run to display an error message.
Prompt ’1 - script1.sql’
prompt ’2 - script2.sql’
prompt ’3 - script3.sql’
accept script_number prompt - ’Enter the number of the script to run: ’
@formatoff
column script_name noprint new_value script_name_var
select decode ( &script_number,
        1, ’script1.sql’,
        2, ’script2.sql’,
        3, ’script3.sql’,
        ’badscript.sql’) script_name
from dual;
@formaton
@&script_name_var

Probably the easiest way to copy data from one table to another is to use
INSERT INTO table1 SELECT * FROM table2;

This method doesn’t work well with large tables because this command is processed as a single unit of work. Since you can’t have ORACLE do a commit in the middle of the insert, you have to have a rollback segment large enough to handle the complete transaction. It also won’t work if the source table has a LONG datatype.

You could write a store procedure that opens a cursor, does the inserts, and does a commit after a certain number of rows. This can get tedious if you have a large number of columns in the table, since you will have to refer to each one in the INSERT command.
The SQL*Plus COPY command allows you continue to use simple SQL to define the data that you are copying. It also allows you to take occasional COMMITS during the copy operation. The commit frequency is controlled by the ARRAYSIZE and COPYCOMMIT settings in your SQL*Plus session. The ARRAYSIZE setting determines how many rows are in a ’batch’. The COPYCOMMIT setting determines how many batches to process before doing a commit.

The COPY command requires that at least one of the tables be a remote table. It expects a SQL*Net connect string to identify the remote database. To copy one local table to another local table, simply specify the connect string for the local database.

The COPY command allows you to CREATE, REPLACE, INSERT, or APPEND the destination table. CREATE will create the table. REPLACE removes any existing rows before inserting new data. INSERT requires that the table exists, but that it is empty. APPEND adds rows and does nothing with any existing rows.

The following example uses the COPY command to create the local table mytable2 as a copy of the local table mytable1. A commit is taken after every 1000 rows. Note that since COPY is a SQL*Plus command, not a SQL command, you must use a ’-’ to continue the command to multiple lines. Also, the COPY command is not stored in your edit buffer, so you’ll have to retype the command if you make mistakes (or save the command in a script file).
set arraysize 10
set copycommit 100
set long 3000
copy from myusername/mypassword@mydb create mytable2 using select * from mytable1 ;

Array fetch/bind size is 10. (arraysize is 10)
Will commit after every 100 array binds. (copycommit is 100)
Maximum long size is 3000. (long is 3000)
Table MYTABLE2 created.

Outer Joing
An outer join forces a query to return a row even if the row in one table has no matching row in the other. i.e. it will return at least as many rows as the corresponding equijoin and probably more (since that’s the reason for writing it in the first place). e.g. the following query lists one row for each order pplus one row for each customer who has not placed any orders:
select c.name, o.order_date
from customers c, orders o
where o.cust_id = c.id (+);

DECLARE
   CURSOR get_customer IS
       SELECT * FROM customer;
   cust_row get_customer%ROWTYPE;
BEGIN
-- Begin cursor processing.
   OPEN   get_customer;
   LOOP
       -- Retreive one row.
FETCH get_customer INTO cust_row;
      -- Exit the loop after all rows have been retreived.
      EXIT WHEN get_customer %NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(cust_row.name);
   -- End processing.
   END LOOP;
   CLOSE get_customer;
END;
/

DECLARE
    CURSOR get_customer IS
       SELECT name FROM customer;
    cust_name customer.name%TYPE;
BEGIN
   OPEN   get_customer;
   LOOP
    FETCH get_customer INTO cust_name;
      EXIT WHEN get_customer %NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(cust_name);
   END LOOP;
   CLOSE get_customer;
END;
/

REM *** Simple Loop ***
DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
    -- Insert a row into temp_table with the current value of the
    -- loop counter.
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- Exit condition - when the loop counter > 50 we will
    -- break out of the loop.
    IF v_Counter > 50 THEN
      EXIT;
    END IF;
END LOOP;
END;
/

REM *** Using EXIT WHEN ***
DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
    -- Insert a row into temp_table with the current value of the
    -- loop counter.
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- Exit condition - when the loop counter > 50 we will
    -- break out of the loop.
    EXIT WHEN v_Counter > 50;
END LOOP;
END;
/

REM *** WHILE Loop Example 1 ***
DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
-- Test the loop counter before each loop iteration to
-- insure that it is still less than 50.
WHILE v_Counter <= 50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
END LOOP;
END;
/

REM *** WHILE Loop Example 2 ***
DECLARE
v_Counter BINARY_INTEGER;
BEGIN
-- This condition will evaluate to NULL, since v_Counter
-- is initialized to NULL by default.
WHILE v_Counter <= 50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
END LOOP;
END;
/

REM *** Numeric FOR Loop ***
BEGIN
FOR v_Counter IN 1..50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop Index');
END LOOP;
END;
/

REM *** FOR Loop Scoping Rules ***
DECLARE
v_Counter NUMBER := 7;
BEGIN
-- Inserts the value 7 into temp_table.
INSERT INTO temp_table (num_col)
    VALUES (v_Counter);
-- This loop redeclares v_Counter as a BINARY_INTEGER, which hides
-- the NUMBER declaration of v_Counter.
FOR v_Counter IN 20..30 LOOP
    -- Inside the loop, v_Counter ranges from 20 to 30.
    INSERT INTO temp_table (num_col)
      VALUES (v_Counter);
END LOOP;
-- Inserts another 7 into temp_table.
INSERT INTO temp_table (num_col)
    VALUES (v_Counter);
END;
/

REM ***FOR Loop Ranges ***
DECLARE
v_LowValue NUMBER := 10;
v_HighValue NUMBER := 40;
BEGIN
FOR v_Counter IN REVERSE v_LowValue .. v_HighValue LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Dynamically specified loop ranges');
END LOOP;
END;
/

REM *** Using Labels ***
DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop count');
    v_Counter := v_Counter + 1;
    IF v_Counter > 50 THEN
      GOTO l_EndOfLoop;
    END IF;
END LOOP;
<<l_EndOfLoop>>
INSERT INTO temp_table (char_col)
    VALUES ('Done!');
END;
/

Predefined Exceptions

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. To handle other Oracle errors, you can use the OTHERS handler. The error-reporting functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error numbers.
PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and SQLCODE return values.

*Exception Name*	*Oracle Error*	*SQLCODE Value*
CURSOR_ALREADY_OPEN	ORA-06511	-6511
DUP_VAL_ON_INDEX	ORA-00001	-1
INVALID_CURSOR	ORA-01001	-1001
INVALID_NUMBER	ORA-01722	-1722
LOGIN_DENIED	ORA-01017	-1017
NO_DATA-FOUND	ORA-01403	+100
NOT_LOGGED_ON	ORA-01012	-1012
PROGRAM_ERROR	ORA-06501	-6501
ROWTYPE_MISMATCH	ORA-06504	-6504
STORAGE_ERROR	ORA-06500	-6500
TIMEOUT_ON_RESOURCE	ORA-00051	-51
TOO_MANY_ROWS	ORA-01422	-1422
VALUE_ERROR	ORA-06502	-6502
ZERO_DIVIDE	ORA-01476	-1476

Brief descriptions of the predefined exceptions follow:

CURSOR_ALREADY_OPEN is raised if you try to open an already open cursor. You must close a cursor before you can reopen it.
DUP_VAL_ON_INDEX is raised if you try to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSOR is raised if you try an illegal cursor operation. For example, INVALID_CURSOR is raised if you close an unopened cursor.
INVALID_NUMBER is raised in a SQL statement if the conversion of a character string to a number fails because the string does not represent a valid number. For example, the following INSERT statement raises INVALID_NUMBER when Oracle tries to convert 'HALL' to a number:
        INSERT INTO emp (empno, ename, deptno) VALUES ('HALL', 7888, 20);
In procedural statements, VALUE_ERROR is raised instead.

LOGIN_DENIED is raised if you try logging on to Oracle with an invalid username/password.
NO_DATA_FOUND is raised if a SELECT INTO statement returns no rows or if you reference an uninitialized row in a PL/SQL table. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised. SQL group functions such as AVG and SUM always return a value or a null.
NOT_LOGGED_ON is raised if your PL/SQL program issues a database call without being connected to Oracle.
PROGRAM_ERROR is raised if PL/SQL has an internal problem.
ROWTYPE_MISMATCH is raised if the host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when you pass an open host cursor variable to a stored subprogram, if the return types of the actual and formal parameters are incompatible, PL/SQL raises ROWTYPE_MISMATCH.
STORAGE_ERROR is raised if PL/SQL runs out of memory or if memory is corrupted.
TIMEOUT_ON_RESOURCE is raised if a timeout occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS is raised if a SELECT INTO statement returns more than one row.
VALUE_ERROR is raised if an arithmetic, conversion, truncation, or size-constraint error occurs. For example, when you select a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR.
In procedural statements, VALUE_ERROR is raised if the conversion of a character string to a number fails. For example, the following assignment statement raises VALUE_ERROR when PL/SQL tries to convert 'HALL' to a number:
DECLARE
   my_empno NUMBER(4);
   my_ename CHAR(10);
BEGIN
   my_empno := 'HALL'; -- raises VALUE_ERROR
In SQL statements, INVALID_NUMBER is raised instead.

ZERO_DIVIDE is raised if you try to divide a number by zero because the result is undefined.

Developing Stored Procedures and Functions

	Procedure	Function
Syntax	create or replace procedure myproc ( p_vat_rate IN NUMBER DEFAULT 17.5, p_price IN OUT NUMBER, p_vat OUT NUMBER) is price_invalid EXCEPTION; begin if p_price <= 0.0 then raise price_invalid; end if; p_vat := (p_vat_rate/100) * p_price; p_price := p_price + p_vat; exception when price_invalid then raise_application_error(-20000, ’Price must be positive’); when others then raise_application_error(-20999, ’Unexpected error’); end;	create or replace function vat ( vat_rate IN NUMBER, price IN NUMBER) is l_vat NUMBER; begin l_vat := (vat_rate/100) * price; return l_vat; end;
sqlplus	variable price number variable vat number exec myproc(15,:price,:vat) or exec myproc(p_vat=>:vat, p_price=>:price);	variable price number variable rate number exec :price := :price + vat(:rate,:price); or select vat(15, p.price) from prices p where p.id=123;
Parameters	An IN parameter may be used in the program unit but not changed. e.g. it may not appear on the left hand side of an assignment. An OUT can be assigned to but may not be used/looked at. It may appear on the left hand side of an assignment but not the right hand side. It cannot appear in the condition of an if statement or while/for loop. An IN OUT is effectively unrestricted – it can be used wherever a variable declared locally in the program unit can be used.
Notes	The sqlplus execute (or exec) statement is a shorthand for begin … .end; Generally, it is best to use the ‘create or replace’ statement as this preserves grants on the program unit. Parameters and variables can be defined using the Oracle datatypes (number, varchar2, etc) or by reference to database objects e.g. sale_price product.price%type; -- defines a simple variable my_product product%rowtype; -- defines a record raise_application_error allows the programmer to pass an error code/message to the calling environment e.g. sqlplus or forms. The error code must be in the range –20000 to –20999. The calling program unit can use pragma exception_init as follows: declare invalid_price EXCEPTION; pragma exception_init(-20000,invalid_price); l_price NUMBER := 10; begin myproc(p_vat=>16, p_price=>l_price) exception when invalid_price then -- handle the error;. end; If a function/procedure fails with an unhandled exception, Oracle rolls back DML statements to the start of the program unit and then passes the exception to the calling program unit, sql*plus, or whatever else called the program unit.

Running Packaged Procedures
CREATE OR REPLACE
PROCEDURE RUN_PACKS IS
BEGIN
FIN_PACK.UPDATE_SAL (999999, 67500);
DBMS_OUTPUT.PUT_LINE(FIN_PACK.AVG_SAL);
END;

Built in Packages Available
- DBMS_OUTPUT displays to the screen
- DBMS_PIPE communicates between sessions
- DBMS_LOCK manages program locks
- DBMS_JOB allows you to schedule jobs
- UTL_FILE reads from and writes tp operating system files
- DBMS_SQL for dynamic SQL and DDL

PL/SQL Tables
Used to perform array processing, must contain Primary Key
DECLARE
     TYPE cust_name IS TABLE OF customer.name%TYPE
            INDEX BY BINARY_INTEGER;
     STORED_NAME    cust_name;
BEGIN
     SELECT name INTO stored_name(1) FROM customer
      WHERE rownum = 1;
      DBMS_OUTPUT.PUT_LINE(stored_name(1));
END;

DECLARE
     TYPE cust_rec IS TABLE OF customer.name%ROWTYPE
            INDEX BY BINARY_INTEGER;
     STORED_CUST    cust_rec;
BEGIN
     SELECT * INTO stored_cust(1) FROM customer
      WHERE rownum = 1;
      DBMS_OUTPUT.PUT_LINE(stored_name(1).name);
END;

PL/SQL Table Methods
Assists with table handling
table_name.method(parameters) e.g. stored_cust.EXISTS(1)

BEGIN
     SELECT * INTO stored_cust(1) FROM customer
      WHERE rownum = 1;
      IF stored_cust.EXISTS(1)
         THEN
            DBMS_OUTPUT.PUT_LINE(stored_name(1).name);
      END IF;
END;

stored_rec.COUNT        -- Number of elements in the table
stored_rec.FIRST           -- Goes to first record
stored_rec.LAST            -- Goes to last record
stored_rec.PRIOR (n)     -- Returns the preceding index number
stored_rec.NEXT (n)      -- Returns the next index number
stored_rec.TRIM            -- Removes the last element in table
stored_rec.TRIM(n)       -- Removes the last n elements in table
stored_rec.DELETE           -- Deletes all elements from table
stored_rec.DELETE(n)      -- Deletes the nth elements from table
stored_rec.DELETE(m, n) -- Deletes elements in range m to n
stored_rec.EXTEND          -- Appends one null row to table
stored_rec.EXTEND(n)      -- Appends n null rows to table
stored_rec.EXTEND(n, I)   -- Appends n copies of the I row to table

Cursor Loops
Explicitly declared cursor and record	CREATE OR REPLACE PROCEDURE <procedure_name> IS CURSOR <cursor_name> IS <SQL statement> <record_name> <cursor_name>%ROWTYPE; BEGIN OPEN <cursor_name> LOOP FETCH <cursor_name> INTO <record_name>; EXIT WHEN <cursor_name>%NOTFOUND; <other code> END LOOP; CLOSE <cursor_name>; END <procedure_name>; /
	CREATE OR REPLACE PROCEDURE exp_all IS CURSOR my_cur IS SELECT zip_code FROM zip_city; my_rec my_cur%ROWTYPE; BEGIN OPEN my_cur; LOOP FETCH my_cur INTO my_rec; EXIT WHEN my_cur%NOTFOUND; NULL; END LOOP; CLOSE my_cur; END exp_all; /
Explicitly declared cursor and implicit record	CREATE OR REPLACE PROCEDURE <procedure_name> IS CURSOR <cursor_name> IS <SQL statement> BEGIN FOR <record_name> IN <cursor_name> LOOP <other code> END LOOP; END <procedure_name>; /
	set serveroutput on CREATE OR REPLACE PROCEDURE exp_and_imp IS CURSOR my_cur IS SELECT zip_code FROM zip_city; BEGIN FOR my_rec IN my_cur LOOP NULL; END LOOP; END exp_and_imp; /
Implicitly declared cursor and record	CREATE OR REPLACE PROCEDURE <procedure_name> IS BEGIN FOR <record_name> IN (<SQL statement>) LOOP <other code> END LOOP; END <procedure_name>; /
	CREATE OR REPLACE PROCEDURE imp_all IS BEGIN FOR my_rec IN (SELECT zip_code FROM zip_city) LOOP NULL; END LOOP; END imp_all; /
Cursor Loop With WHERE CURRENT OF Clause	CREATE OR REPLACE PROCEDURE <procedure_name> IS <cursor definition> BEGIN FOR <record_name> IN (<cursor_name>) LOOP <other code> UPDATE <table_name> SET <column_name> = <value> WHERE CURRENT OF <cursor_name> END LOOP; END <procedure_name>; /
	CREATE TABLE test ( pid NUMBER(3), cash NUMBER(10,2)); INSERT INTO test VALUES (100, 10000.73); INSERT INTO test VALUES (200 25000.26); INSERT INTO test VALUES (300, 30000.11); INSERT INTO test VALUES (400, 45000.99); INSERT INTO test VALUES (500, 50000.08); COMMIT; CREATE OR REPLACE PROCEDURE wco IS CURSOR x_cur IS SELECT pid, cash FROM test WHERE cash < 35000 FOR UPDATE; BEGIN FOR x_rec IN x_cur LOOP UPDATE test SET cash = FLOOR(cash) WHERE CURRENT OF x_cur; END LOOP; COMMIT; END wco; / exec wco; SELECT * FROM test;