Oracle core question

Question

I am new to oracle. When I create a stored procedure using:

CREATE OR REPLACE PROCEDURE PROCEDURE1 
AS
BEGIN

  SELECT FIRSTNAME,
         LASTNAME
    INTO FirstName,LastName
    FROM EMPLOYEE;

END PROCEDURE1;

I get the following errors:

PL / SQL statement ignored. FIRSTNAME must be declared. ORA-00904 Invalid identifier

Answer 1

I think the keyword "AS" will not work. If this does not work, use "IS".
Holidays are wonderful and very good tips.

If you need help regarding PL / SQL, you can see this link. It is very simple and easy to understand. http://plsql-tutorial.com/

This is my solution to the error you receive;

CREATE OR REPLACE PROCEDURE PROCEDURE1 IS
  v_FIRSTNAME EMPLOYEE.FIRSTNAME%TYPE;
  v_LASTNAME EMPLOYEE.LASTNAME%TYPE;
  CURSOR EMPCURSOR IS
  SELECT FIRSTNAME, LASTNAME FROM EMPLOYEE;      
  BEGIN   
     IF NOT EMPCURSOR%ISOPEN THEN
         OPEN EMPCURSOR;
     END IF;
     LOOP
        FETCH EMPCURSOR INTO V_FIRSTNAME,V_LASTNAME;
        EXIT WHEN EMPCURSOR%NOTFOUND;
     END LOOP;
     IF EMPCURSOR%ISOPEN THEN
        CLOSE EMPCURSOR;
     END;
  END PROCEDURE1;

DBMS_OUTPUT.PUT_LINE(V_FIRSTNAME || ','|| V_LASTNAME) . server output on

Answer 2

, :

CREATE OR REPLACE PROCEDURE PROCEDURE1 
AS

 FirstName EMPLOYEE.FIRSTNAME%TYPE;
 LastName EMPLOYEE.LASTNAME%TYPE;

BEGIN

  SELECT FIRSTNAME,
         LASTNAME
    INTO FirstName,LastName
    FROM EMPLOYEE;

END PROCEDURE1;

% TYPE , . - , .

Answer 3

.

CREATE OR REPLACE
PROCEDURE PROCEDURE1 AS
 V_FIRSTNAME VARCHAR2(60);
 V_LASTNAME  VARCHAR2(60);
BEGIN
        SELECT FIRSTNAME,LASTNAME
         INTO V_FIRSTNAME ,V_LASTNAME  
         FROM EMPLOYEE;
END PROCEDURE1;

---

SQL PL/SQL 1 . , .

CREATE OR REPLACE
    PROCEDURE PROCEDURE1 AS

     CURSOR EMP_CUR IS
         SELECT FIRSTNAME,LASTNAME
         FROM EMPLOYEE;
    EMP_CUR_REC EMP_CUR%ROWTYPE;

    BEGIN
     FOR EMP_CUR_REC IN EMP_CUR LOOP
         -- do your processing
         DBMS_OUTPUT.PUT_LINE('Employee first name is ' || EMP_CUR_REC.FIRSTNAME);
         DBMS_OUTPUT.PUT_LINE('Employee last name is ' || EMP_CUR_REC.LASTNAME);
    END LOOP;
    END PROCEDURE1;

:   EMP_CUR SQL, .  EMP_CUR_REC , SQL.   %ROWTYPE , , ,

FOR LOOP , , .

Answer 4

@Sathya @kayak " - Select * Tablename firstname, lastname tablename, sql".

, , WHERE . WHERE, , -

CREATE OR REPLACE PROCEDURE PROCEDURE1
IS
  rowEmployees EMPLOYEE%ROWTYPE;
BEGIN
  SELECT *
    INTO rowEmployees
    FROM EMPLOYEE
    WHERE EMPLOYEE_ID = 12345;
END PROCEDURE1; 

, WHERE, WHERE, , :

CREATE OR REPLACE PROCEDURE PROCEDURE1 IS
BEGIN
  FOR rowEmployees IN (SELECT *
                         FROM EMPLOYEE
                         WHERE EMPLOYEE_ID IN (12345, 67890, 111213, 141516))
  LOOP
    <do something with rowEmployees here>
  END LOOP;
END PROCEDURE1; 

.