| please help me in these pl/sql !! [message #39343] |
Tue, 09 July 2002 06:59  |
shalu
Messages: 7
Registered: November 2001
|
Junior Member |
|
|
hello,
I am a new one to pl/sql, and am not able to understand the output for these q's, can someone help me out.
Q1)
ITEM_TABLE
ITEM_NUM ITEM_DESC
================================
7 ITEM 7
4 ITEM 4
9 ITEM 9
2 ITEM 2
5 ITEM 5
DECLARE
CURSOR item_cursor( parm1 INTEGER DEFAULT 5)
IS SELECT item_num
FROM item_table
WHERE item_num < parm1
ORDER BY item_num;
item_number item_table.item_num%TYPE;
BEGIN
OPEN item_cursor;
FETCH item_cursor INTO item_number;
WHILE item_cursor%FOUND LOOP
FETCH item_cursor INTO item_number;
DBMS_OUTPUT.PUT_LINE(item_number);
END LOOP;
CLOSE item_cursor;
END;
What would be the result of this? [[I feel it should be 2, 4; but it's not so]]
Q2)
user1:
CREATE TABLE user1.user1tab
(id NUMBER NOT NULL CONSTRAINT pk_user1tab PRIMARY KEY,
description VARCHAR2(20));
GRANT INSERT, UPDATE, REFERENCES, INDEX ON user1tab TO user2;
user2:
CREATE TABLE user2.user2tab
(id NUMBER NOT NULL,
sales_rep VARCHAR2(20),
CONSTRAINT fk_id
FOREIGN KEY (id)
REFERENCES user1.user1tab(id));
CREATE INDEX i_user1tab_desc ON user1.user1tab(description);
user1:
REVOKE ALL ON user1.user1tab FROM user2;
What would be the consequences of this sample code?
Q3)
CREATE OR REPLACE PACKAGE p AS
PROCEDURE my_proc;
END p;
CREATE OR REPLACE PACKAGE BODY p AS
i NUMBER := 1;
j NUMBER;
k NUMBER := 3;
FUNCTION my_func( j IN OUT NUMBER ) RETURN NUMBER IS
i NUMBER := 5;
BEGIN
i := j;
k := i;
j := i * 3;
DBMS_OUTPUT.PUT_LINE( 'my_func i = ' ||i||' , '||'my_func j = ' ||j ||', '||'my_func k = '||k );
RETURN( j );
END my_func;
PROCEDURE my_proc IS
BEGIN
j := i * 2;
k := my_func( i )+k;
DBMS_OUTPUT.PUT_LINE( i || ' ' || j || ' ' || k );
END my_proc;
END p;
BEGIN
p.my_proc;
END;
What values will be the printed when 'p.my_proc' is executed as shown above?
[[I feel the ans should have been : 1, 2, 6]]
Thanks in advance
Regards
Shalu
|
|
|
|
| Re: please help me in these pl/sql !! [message #39345 is a reply to message #39343] |
Tue, 09 July 2002 08:34  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
You must be taking a course on how NOT to code.
Q1) 4; 4
Explan: It fetches 2, %FOUND is true so it fetches 4 and prints it out, %FOUND is still true so it tries to fetch but can't but it still prints out 4, %FOUND is no longer true so it exits the loop.
Q3) my_func i = 1 , my_func j = 3, my_func k = 1
3 2 4
This is a classic example of why you should prefix variables with the scope; for example, preface parameters with i_, o_, or io_, depending on whether it is IN, OUT or IN OUT; preface local variables with l_, and package level variables with pv_. If that was done, wouldn't it have been obvious?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
