主页 / dba / 问题 / 292276
Accepted
AxD
Asked: 2021-05-27 01:19:04 +0800 CST

我怎样才能使这个 DECLARE PROCEDURE 工作？

  • 772

我是 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
oracle plsql

1 个回答

  1. Best Answer

    这是一个很好的例子，为什么您永远不应该将您的 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，它返回用户名不为空的所有行。

    • 1

相关问题