Example#1 : Procedure for SELECT Statement using dual table.
SQL> CREATE OR REPLACE PROCEDURE proc_SelectDual
IS
vName varchar2(100);
vName varchar2(100);
BEGIN
SELECT 'Hello World' INTO vName from dual;
DBMS_OUTPUT.PUT_LINE(vName);
DBMS_OUTPUT.PUT_LINE(vName);
END;
/
SQL> SET SERVEROUTPUT ON
SQL> EXEC proc_SelectDual;
result : Hello World
SQL> SET SERVEROUTPUT ON
SQL> EXEC proc_SelectDual;
result : Hello World
Example#2 :
Step#1 : Create table & insert few values.
SQL> CREATE TABLE Stu_InfoStep#1 : Create table & insert few values.
(
stuid number,
name varchar2(10),
city varchar2(10),
amount number
);
SQL> Insert into Stu_Info values(1000,'RAM','NEW DELHI',1000);
SQL> Insert into Stu_Info values(1001,'SACHIN', 'MUMBAI',2000);
SQL> Insert into Stu_Info values(1002,'SMITH','NEW YORK',3000);
SQL> Insert into Stu_Info values(1003,'CHENG', 'BEIJING',4000);
SQL> SELECT * from Stu_Info;
STUID
|
NAME
|
CITY
|
AMOUNT
|
1000
|
RAM
|
NEW DELHI
|
1000
|
1001
|
SACHIN
|
MUMBAI
|
2000
|
1002
|
SMITH
|
NEW YORK
|
3000
|
1003
|
CHENG
|
BEIJING
|
4000
|
Step#2 : Create procedure for SELECT INTO using OUT Parameter.
SQL> CREATE OR REPLACE PROCEDURE proc_SelectStuInfo
( p_stuid IN Stu_info.stuid%TYPE,
p_name OUT Stu_info.name%TYPE,
p_city OUT Stu_info.city%TYPE,
p_amount OUT Stu_info.amount%TYPE)
IS
BEGIN
SELECT name, city, amount
INTO p_name, p_city, p_amount
FROM Stu_info
WHERE stuid = p_stuid;
INTO p_name, p_city, p_amount
FROM Stu_info
WHERE stuid = p_stuid;
END;
/
Step#3 : Execute Procedure
SQL> DECLAREp_name Stu_info%TYPE;
p_city Stu_info%TYPE;
p_amount Stu_info%TYPE;
BEGIN
proc_SelectStuInfo(1000,p_name,p_city,p_amount);
DBMS_OUTPUT.PUT_LINE('Name : ' || p_name);
DBMS_OUTPUT.PUT_LINE('City : ' || p_city);
DBMS_OUTPUT.PUT_LINE('Amount : ' || p_amount);
END;
/
Result : Name : RAM
City : NEW DELHI
Amount : 1000
No comments:
Post a Comment