Oracle Apex创建过程

SKT T1面包师

1）创建一个打印“ This is the Final Test”的过程（PrintProc）。

2）创建一个过程（UpdateProc），该过程需要一个“学生ID”，然后将其州更新为“纽约”。

Pic学生数据库在此处输入图片说明教师数据库的Pic ＃1是

CREATE OR REPLACE
PROCEDURE PrintProc IS
BEGIN
   DBMS_OUTPUT.PUT_LINE(’This is the Final Test’);
END;

为了执行我做了

begin 
   PrintProc;
end;

但我有一个错误

对于＃2是

CREATE OR REPLACE PROCEDURE UpdateProc
AS
BEGIN
   Update Student
   set s_state = 'New York'
   where s_state = 'WI'
END;

并在第6行得到此错误错误：PL / SQL：ORA-00933：SQL命令未正确结束

执行它

Begin
   UpdateProc;
end;

6）创建一个程序（PrintStudentsProc），该程序将打印出由Kim Cox教过的学生的列表。

CREATE OR REPLACE PROCEDURE PrintStudentsProc
AS
BEGIN
   Select S.S_ID, F.F_ID
   FROM Faculty F INNER JOIN STUDENT S ON F.F_ID = S.F_ID
   WHERE F.F_ID = 1                                                   
END;

我在第4行出现此错误：PLS-00428：此SELECT语句中应有一个INTO子句

Was about to make another topic about triggers but stackoverflow bugged out again and I can't post for -9 days cause I posted 6 recent(not even recent) questions.

5) Create a trigger (UpdateTrigger) that outputs a message saying "Student record is going to be updated” Before the Update Takes place on Student Table,

I did

CREATE OR REPLACE TRIGGER UpdateTriggers
BEFORE UPDATE ON StudentsInfo
BEGIN
   DBMS_OUTPUT.PUT_LINE('Student record is going to be updated');
   Update StudentsInfo Set StudentsUpdated = StudentsUpdated + 1;
End;

and how would I execute it?

begin
   UpdateTriggers;
end;

learningloop

1. Error in PrintProc

The error in following code:

begin 
PrintProc;
end;

is caused by the invalid character ’ in DBMS_OUTPUT.PUT_LINE in PrintProc. Replace ’ with ', this should resolve that error.

DBMS_OUTPUT.PUT_LINE('This is the Final Test');

2. Error in UpdateProc

The error in UpdateProc is caused by missing semi-colon in update statement. Add semicolon like the following:

Update Student
set s_state = 'New York'
where s_state = 'WI';

3. Error in PrintStudentsProc

关于中的错误PrintStudentsProc，您不能在plsql块内执行普通的select语句。您需要使用cursor。此外，您在以下查询中缺少分号：

Select S.S_ID, F.F_ID
FROM Faculty F INNER JOIN STUDENT S ON F.F_ID = S.F_ID
WHERE F.F_ID = 1;

游标循环示例：

CREATE OR REPLACE PROCEDURE PrintStudentsProc AS
BEGIN
   FOR stud_rec IN (
       Select S.S_ID SID, F.F_ID FID
       FROM Faculty F INNER JOIN STUDENT S ON F.F_ID = S.F_ID
       WHERE F.F_ID = 1)
   LOOP
       DBMS_OUTPUT.PUT_LINE(stud_rec.SID||', '||stud_rec.FID);
   END LOOP;
END;
/

在这里看看其他类型的游标

4.触发错误

对于触发器，在创建触发器之后，您需要执行调用触发器的语句。您的情况是在更新之前，因此您需要在表上执行更新查询。

CREATE OR REPLACE TRIGGER update_trigger BEFORE
  update ON StudentsInfo FOR EACH ROW 
  DECLARE 
        stud_updated int;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Student record is going to be updated'); 
    select StudentsUpdated into stud_updated from StudentsInfo where s_id=:new.s_sid;
    --increase the value
    stud_updated := stud_updated+1;
    Update StudentsInfo Set StudentsUpdated = :stud_updated;
            DBMS_OUTPUT.PUT_LINE('Student updated count:'||stud_updated);
END;
/

每当您对StudentsInfo表执行更新时，上述触发器都应该能够执行。

另外，检查此sqlfiddle

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-03-24

我来说两句

0 条评论

登录后参与评论

上一篇：getPaginationCount（）函数在Laravel的雄辩ORM中不起作用

TOP 榜单

文章