我声明一个变量,user_name
以便其他人可以对其进行更改以在他们的笔记本电脑上运行我的脚本。这C##AKIRA
是我在SQL Plus中创建的帐户。
DECLARE
user_name varchar2(20);
BEGIN
user_name := 'C##AKIRA';
END;
当我跑步时
SELECT
table_name
FROM
all_tables
WHERE
owner = user_name
Oracle返回错误ORA-00904: "USER_NAME": invalid identifier
。另一方面,我跑步时效果很好
SELECT
table_name
FROM
all_tables
WHERE
owner = 'C##AKIRA'
您能详细说明这个问题吗?
-- Declare a variable called 'user_name'
DECLARE
user_name varchar2(20);
BEGIN
user_name := 'C##AKIRA';
END;
-- Drop a table of it already exists
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE works_in';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
-- Create a table called `works_in`
CREATE TABLE works_in (
coded NUMBER(2),
codew NUMBER(2),
datewi DATE,
hourcount NUMBER(2),
PRIMARY KEY (coded, codew, datewi),
FOREIGN KEY (coded) REFERENCES doctors (coded),
FOREIGN KEY (codew) REFERENCES wards (codew)
);
SELECT
table_name
FROM
all_tables
WHERE
owner = user_name
变量user_name的范围仅适用于:
DECLARE
user_name varchar2(20);
BEGIN
user_name := 'C##AKIRA';
END;
您必须仅在将使用的pls方法内部声明变量。
像这样:
-- Declare a variable called 'user_name'
DECLARE
user_name varchar2(20);
v_table_name varchar2(100);
BEGIN
user_name := 'C##AKIRA';
SELECT
table_name
into v_table_name
FROM
all_tables
WHERE
owner = user_name
and rownum = 1
;
END;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句