我怎樣才能使這個 DECLARE PROCEDURE 工作？

我是 Oracle DB 的新手，來自 MS SQL Server。

我創建了這個腳本：

DEFINE USER_NAME = &1
DEFINE PASSWORD = &2
DEFINE TABLESPACE = &3

CONNECT &DB_ADMIN/&DB_PWD@//localhost:1521/&DB_NAME

SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF

DECLARE PROCEDURE Create_User
 ( userName IN VARCHAR2
 , password IN VARCHAR2
 , tSpace IN VARCHAR2
 ) AS
 dbCount INTEGER := -1;
 createStmt VARCHAR2(500);
BEGIN
 SELECT COUNT(*)
   INTO dbCount
   FROM CDB_USERS u
     INNER JOIN v$pdbs d ON u.CON_ID = d.CON_ID
   WHERE d.NAME = '&DB_NAME'
     AND u.USERNAME = userName;

 IF dbCount > 0
   THEN
     EXECUTE IMMEDIATE 'DROP USER ' || userName || ' CASCADE';
     DBMS_OUTPUT.PUT_LINE('User ' || userName || ' dropped.');
   END IF;

 createStmt := 'CREATE USER ' || userName || ' IDENTIFIED BY ' || password || ' DEFAULT TABLESPACE ' || tSpace || ' QUOTA UNLIMITED ON ' || tSpace;
 DBMS_OUTPUT.PUT_LINE('User about to be created:');
 DBMS_OUTPUT.PUT_LINE(createStmt);
 DBMS_OUTPUT.PUT_LINE('');


 DBMS_OUTPUT.PUT_LINE('Creating user ' || userName || ' ...');
 EXECUTE IMMEDIATE createStmt;
 DBMS_OUTPUT.PUT_LINE('Database ' || userName || ' successfully created.');

 END Create_User;

BEGIN
 Create_User('&USER_NAME', '&PASSWORD', '&TABLESPACE');
 END;
 /

當我在 SqlPlus 中執行它時，如下所示：

SQL> DEFINE DB_NAME = 'MYDB'
SQL> DEFINE DB_ADMIN = 'SA'
SQL> DEFINE DB_PWD = 'pwd'
SQL> @/db-install/sql/add-user.sql 'USERNAME' 'pwd' 'TSPACE'

我收到此錯誤消息：

Connected.
DECLARE PROCEDURE Create_User
*
ERROR at line 1:
DECLARE
*
ERROR at line 1:
ORA-01918: user 'USERNAME' does not exist
ORA-06512: at line 19
ORA-06512: at line 36

有人可以指出我正確的方向嗎？我究竟做錯了什麼？

感謝您的回答。

---

編輯

我在另一個腳本中有完全相同的程式碼，並且在那裡執行良好：

DEFINE DB_NAME = &1
DEFINE DB_ADMIN = &2
DEFINE DB_PWD = &3

CONNECT &DB_ADMIN/&DB_PWD@//localhost:1521/&DB_NAME

SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF

BEGIN
 DBMS_OUTPUT.PUT_LINE('Adding tablespaces to database ...');
 END;
 /


--- Procedure: dropping and creating a given tablespace ---
DECLARE
 dbCount INTEGER;
 createStmt VARCHAR2(500);
 filePath dba_data_files.FILE_NAME%TYPE;
PROCEDURE Create_TS
...

編輯 2

如果我將變數放在 and 之間DECLARE並沒有什麼區別PROCEDURE：

DECLARE
 dbCount INTEGER := -1;
 createStmt VARCHAR2(500);
PROCEDURE Create_User
 ( userName IN VARCHAR2
 , password IN VARCHAR2
 , tSpace IN VARCHAR2
 ) AS
BEGIN

Connected.
DECLARE
*
ERROR at line 1:
ORA-01918: user 'USERNAME' does not exist
ORA-06512: at line 19
ORA-06512: at line 36

這是一個很好的例子，為什麼您永遠不應該將您的 PL/SQL 變數命名為與列名相同的名稱。

set serveroutput on
DECLARE
 PROCEDURE Create_User
 ( userName IN VARCHAR2
 , password IN VARCHAR2
 , tSpace IN VARCHAR2
 ) AS
 dbCount INTEGER := -1;
 createStmt VARCHAR2(500);
BEGIN
 SELECT COUNT(*)
   INTO dbCount
   FROM DBA_USERS u
   WHERE u.USERNAME = userName;
 dbms_output.put_line('User count: ' || dbcount);
end;
begin
 create_user('abc', 'abc', 'abc');
end;
/

User count: 26

u.USERNAME = userName過濾器是username = username，它返回使用者名不為空的所有行。

引用自：https://dba.stackexchange.com/questions/292276