OraFAQ Forum: SQL & PL/SQL » TRIVIAL: Stored Procedures for Dummies :) help!

Home » SQL & PL/SQL » SQL & PL/SQL » TRIVIAL: Stored Procedures for Dummies :) help!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TRIVIAL: Stored Procedures for Dummies :) help! [message #3728]

Tue, 15 October 2002 06:18

jwebster
Messages: 6
Registered: October 2002

Junior Member

Trying to get an example of a stored procedure that can display the results of select first_name from employees;

the first_name is of type varchar2(15). I'd like to make a procedure that can display the results on screen in sqlplus windows NT 8i. Please help me wrap this statement in a procedure. Is this possible? I heard a need something like dbms_output.put_line ??
Need an idiots example though.

Report message to a moderator

Re: TRIVIAL: Stored Procedures for Dummies :) help! [message #3730 is a reply to message #3728]

Tue, 15 October 2002 06:23

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 DEC 17 1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 FEB 20 1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 FEB 22 1981 00:00:00       1000        500         30
      7566 JONES      MANAGER         7839 APR 02 1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 SEP 28 1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 MAY 01 1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 JUN 09 1981 00:00:00       2450                    10
      7839 KING       PRESIDENT            NOV 17 1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 SEP 08 1981 00:00:00       1500          0         30
      7900 JAMES      CLERK           7698 DEC 03 1981 00:00:00        950                    30
      7902 FORD       ANALYST         7566 DEC 03 1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 JAN 23 1982 00:00:00       1300                    10
       111 mag        CLERK           7902 OCT 09 2002 09:23:08      10000                    20
       111 vivek      CLERK           7902 DEC 17 1980 00:00:00       1000                    20

14 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure mysample
  2  is
  3  cursor c1 is select ename from emp;
  4  begin
  5  for mag in c1 loop
  6     exit when c1%notfound;
  7     dbms_output.put_line('the name is :'||mag.ename);
  8     end loop;
  9* end;
SQL> /

Procedure created.

SQL> set serveroutput on
SQL> exec mysample;
the name is :SMITH
the name is :ALLEN
the name is :WARD
the name is :JONES
the name is :MARTIN
the name is :BLAKE
the name is :CLARK
the name is :KING
the name is :TURNER
the name is :JAMES
the name is :FORD
the name is :MILLER
the name is :mag
the name is :vivek

PL/SQL procedure successfully completed.

Report message to a moderator

Previous Topic:	How do procedures & PL/SQL Block works
Next Topic:	ora-0090

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 07 01:05:50 CDT 2024