PL/SQL流程:措辞问题 [英] PL/SQL process: issue with wording
问题描述
我正在尝试在Oracle APEX 4.1中创建页面处理.具体来说，当此页面上的按钮正在处理提交页面时，我希望此PL/SQL查询能够正常工作.我对PL/SQL的了解不多，正在寻找如何解决此问题的方法.
I am trying to create a page process in Oracle APEX 4.1. Specifically, When a button on this page is process submitting the page, I want this PL/SQL query to work. I don't have much of an understanding of PL/SQL and am looking to find out how to figure this issue out.
我要查询做什么:
我希望此页面处理过程可以遍历我在APEX数据库中拥有的EMPLOYEE表中的每一行.对于每一行，我想将用户名，组和密码移到它们自己的变量中，然后使用APEX_UTIL_CREATE_USER进程创建一个APEX用户.我希望表中的每个员工都能做到这一点.
I would like this page process to loop through each row in the EMPLOYEE table that I have in a database for APEX. For each row, I want to move the username, group and password into their own variables, and then to create an APEX user using the APEX_UTIL_CREATE_USER process. I want this to be done with every employee in the table.
我不特别知道此PL/SQL有什么问题，因为我以前从未使用过它.任何人都可以给我的帮助，我将不胜感激.我将在下面显示查询和错误消息.
I don't know specifically what is wrong with this PL/SQL, as I have never had to use it before. I would greatly appreciate any help anyone can give me with this. I will show the query and the error message below.
PL/SQL查询:
PL/SQL query:
PROCEDURE deploy_employee
(EMP_USERNAME IN EMPLOYEE)
IS
BEGIN
FOR indx IN NVL (EMP_USERNAME.FIRST, 0)
.. NVL (EMP_USERNAME.LAST, -1)
LOOP
emp_user EMPLOYEE.EMP_USERNAME%TYPE;
emp_pass EMPLOYEE.EMP_PASSWORD%TYPE;
emp_group EMPLOYEE.EMP_GROUP%TYPE;
BEGIN
BEGIN
select EMP_USERNAME into emp_user from EMPLOYEE;
select EMP_PASSWORD into emp_pass from EMPLOYEE;
select EMP_GROUP into emp_group FROM EMPLOYEE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
emp_user := NULL;
emp_pass := NULL;
emp_group := NUL;
END;
APEX_UTIL.CREATE_USER(
p_user_name => emp_user,
p_web_password => emp_pass,
p_user_group => emp_gorup,
);
END;
END LOOP;
END deploy_employee;
错误消息:
Error message:
1 error has occurred ORA-06550: line 2, column 1: PLS-00103:
Encountered the symbol "PROCEDURE" when expecting one of the
following: ( begin case declare end exception exit for goto if loop
mod null pragma raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> << continue
close current delete fetch lock insert open rollback savepoint set sql
execute commit forall merge pipe purge The symbol "declare" was
substituted for "PROCEDURE" to continue. ORA-065
页码是2.
再一次感谢我能获得的任何帮助.
Once again I would be greatly appreciative of any help I could gain.
推荐答案
您的过程存在多个问题.
There are multiple issues with your procedure.
- 您缺少 CREATE 关键字，这是编译时错误的根本原因.
PLS-00103.
- You are missing the CREATE keyword, and that's the root cause for the compile time error.
PLS-00103.
有关 CREATE PROCEDURE语句的详细信息，请参见文档. a>创建独立的存储过程或调用规范.
See the documentation for more details on CREATE PROCEDURE statement to create a standalone stored procedure or a call specification.
EMPLOYEE中的EMP_USERNAME
EMP_USERNAME IN EMPLOYEE
- IN参数的数据类型声明不正确.您需要这样做:
EMP_USERNAME IN EMPLOYEE.EMP_USERNAME%TYPE
- FOR LOOP在语法上是错误的.
FOR indx IN NVL(EMP_USERNAME.FIRST，0).. NVL(EMP_USERNAME.LAST，-1)
FOR indx IN NVL (EMP_USERNAME.FIRST, 0) .. NVL (EMP_USERNAME.LAST, -1)
您可以按照以下方式进行操作:
You could do it as:
SQL> CREATE OR REPLACE
2 PROCEDURE deploy_emp(
3 i_emp emp.empno%type)
4 IS
5 emp_user VARCHAR2(50);
6 BEGIN
7 FOR indx IN
8 (SELECT ename FROM emp
9 )
10 LOOP
11 BEGIN
12 BEGIN
13 SELECT ename INTO emp_user FROM emp WHERE empno = i_emp;
14 EXCEPTION
15 WHEN NO_DATA_FOUND THEN
16 emp_user := NULL;
17 END;
18 END;
19 END LOOP;
20 dbms_output.put_line(emp_user);
21 END deploy_emp;
22 /
Procedure created.
SQL> sho err
No errors.
现在，让我们对其进行测试并查看:
Now, let's test it and see:
SQL> set serveroutput on
SQL> EXEC deploy_emp(7369);
SMITH
PL/SQL procedure successfully completed.
SQL>
这篇关于PL/SQL流程:措辞问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
