Create a registration for user through stored procedure-CodePudding

My code can't run. Always pop up success but compiler error. Below is the code

CREATE PROCEDURE sp_register (personID VARCHAR(5), fullname VARCHAR(50), password VARCHAR(10), username VARCHAR(50), address VARCHAR(100), phoneno NUMBER(10), cardNo NUMBER (16))

BEGIN
DECLARE s VARCHAR(20);
IF EXISTS(SELECT person_id FROM Person WHERE Username = username)
    THEN SET s = 'User already exists';
ELSE
    INSERT INTO Person ('Person_ID', 'Name', 'Password', 'Username', 'Address', 'Phone_numbers', 'Card_Card_number') 
        VALUES(personID, name, password, username,address,phoneno,cardNo)
          SET s =  "User Registered"; 
END IF;
END

what is the error and the solution code to solve it.

CodePudding user response：

Need to declare the variable outside of begin block.try this

CREATE PROCEDURE sp_register (personID VARCHAR(5), fullname VARCHAR(50), password VARCHAR(10), username VARCHAR(50), address VARCHAR(100), phoneno NUMBER(10), cardNo NUMBER (16))
As  
s VARCHAR(20);
BEGIN
//Code
END

CodePudding user response：

That code is full of various errors; it is difficult to salvage it so I'd rather start over.

As of the target table, consider storing phone numbers as strings, not numbers.

Sample table:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> CREATE TABLE person
  2  (
  3     person_id          VARCHAR2 (10),
  4     name               VARCHAR2 (10),
  5     password           VARCHAR2 (10),
  6     username           VARCHAR2 (10),
  7     address            VARCHAR2 (10),
  8     phone_numbers      VARCHAR2 (10),
  9     card_card_number   NUMBER
 10  );

Table created.

Procedure:

parameters can't have size nor precision
use varchar2 datatype (because Oracle recommends so)
use in and/or out; default is in, but best practices suggest you designate what's the parameter's purpose
declare can't be used in named PL/SQL procedures
such an if (if exists(subquery)) is invalid; you'd first have to perform select and then - based on what it returns - use if to do this or that
a better option is to skip it alltogether and use not exists
sql%rowcount returns number of affected rows
set is invalid; you'd use e.g. s:= 'whatever'
column names shouldn't be enclosed into single quotes (in your insert into); strings should
as I said, single quotes for strings, not double quotes (in your user registered value)

So:

SQL> CREATE OR REPLACE PROCEDURE sp_register (p_personid  IN VARCHAR2,
  2                                           p_fullname  IN VARCHAR2,
  3                                           p_password  IN VARCHAR2,
  4                                           p_username  IN VARCHAR2,
  5                                           p_address   IN VARCHAR2,
  6                                           p_phoneno   IN NUMBER,
  7                                           p_cardno    IN NUMBER)
  8  IS
  9     l_msg  VARCHAR2 (100);
 10  BEGIN
 11     INSERT INTO person (person_id,
 12                         name,
 13                         password,
 14                         username,
 15                         address,
 16                         phone_numbers,
 17                         card_card_number)
 18        SELECT p_personid,
 19               p_fullname,
 20               p_password,
 21               p_username,
 22               p_address,
 23               p_phoneno,
 24               p_cardno
 25          FROM DUAL
 26         WHERE NOT EXISTS
 27                  (SELECT NULL
 28                     FROM person
 29                    WHERE person_id = p_personid);
 30
 31     l_msg :=
 32        CASE
 33           WHEN SQL%ROWCOUNT > 0 THEN 'User registered'
 34           ELSE 'User already exists'
 35        END;
 36
 37     DBMS_OUTPUT.put_line (l_msg);
 38  END;
 39  /

Procedure created.

Testing:

SQL> BEGIN
  2     sp_register (p_personid  => '100',
  3                  p_fullname  => 'Littlefoot',
  4                  p_password  => 'Big',
  5                  p_username  => 'lf',
  6                  p_address   => 'Croatia',
  7                  p_phoneno   => '0038599',
  8                  p_cardno    => 123);
  9  END;
 10  /
User registered

PL/SQL procedure successfully completed.

SQL> SELECT * FROM person;

PERSON_ID  NAME       PASSWORD   USERNAME   ADDRESS    PHONE_NUMB CARD_CARD_NUMBER
---------- ---------- ---------- ---------- ---------- ---------- ----------------
100        Littlefoot Big        lf         Croatia    38599                   123

What if we try to insert the same ID again? Nothing; user already exists:

SQL> BEGIN
  2     sp_register (p_personid  => '100',
  3                  p_fullname  => 'Littlefoot',
  4                  p_password  => 'Big',
  5                  p_username  => 'lf',
  6                  p_address   => 'Croatia',
  7                  p_phoneno   => '0038599',
  8                  p_cardno    => 123);
  9  END;
 10  /
User already exists

PL/SQL procedure successfully completed.

SQL>