Difference between revisions of "Plsql variables"
From John Freier
(→Collections) |
|||
| (3 intermediate revisions by the same user not shown) | |||
| Line 14: | Line 14: | ||
== Collections == | == Collections == | ||
| + | Collections are more of an array then a TABLE. In the example below '''MyListType''' is just the name of the array type and it is declared to the variable '''myList'''. | ||
| + | |||
| + | myList.FIRST = The first option in the list, example 'option1' | ||
| + | |||
| + | myList.LAST = The last option in the list, example 'option3' | ||
| + | |||
| + | x = The index in the array. | ||
DECLARE | DECLARE | ||
TYPE MyListType IS TABLE OF VARCHAR(21); | TYPE MyListType IS TABLE OF VARCHAR(21); | ||
| − | myList MyListType := MyListType('option1', 'option2', ' | + | myList MyListType := MyListType('option1', 'option2', 'option3'); |
BEGIN | BEGIN | ||
| − | FOR x IN | + | FOR x IN myList.FIRST .. myList.LAST |
LOOP | LOOP | ||
| − | dbms_output.put_line(' | + | dbms_output.put_line('MyList:' || myList(x)); |
END LOOP; | END LOOP; | ||
| + | |||
| + | END; | ||
| + | |||
| + | == SELECT INTO == | ||
| + | You can set a variable from a query by using INTO. | ||
| + | |||
| + | DECLARE | ||
| + | |||
| + | v_count NUMBER; | ||
| + | v_total NUMBER; | ||
| + | |||
| + | BEGIN | ||
| + | |||
| + | select count(id) INTO v_count FROM user; | ||
END; | END; | ||
Latest revision as of 14:16, 2 July 2013
When declaring variables you must also specify the block where the variables are going to be used.
- attrId = Variable name
- ':=' = Sets the variable to a value
DECLARE
attrId number;
BEGIN
attId:= seq_attr.nextval;
dbms_output.put_line (attId);
END;
Collections
Collections are more of an array then a TABLE. In the example below MyListType is just the name of the array type and it is declared to the variable myList.
myList.FIRST = The first option in the list, example 'option1'
myList.LAST = The last option in the list, example 'option3'
x = The index in the array.
DECLARE
TYPE MyListType IS TABLE OF VARCHAR(21);
myList MyListType := MyListType('option1', 'option2', 'option3');
BEGIN
FOR x IN myList.FIRST .. myList.LAST
LOOP
dbms_output.put_line('MyList:' || myList(x));
END LOOP;
END;
SELECT INTO
You can set a variable from a query by using INTO.
DECLARE
v_count NUMBER;
v_total NUMBER;
BEGIN
select count(id) INTO v_count FROM user;
END;
