SQL> l
1 create or replace trigger student_result_update_cascade
2 after update or delete of stuid on t_student
3 for each row
4 begin
5 update t_result
6 set stuid=:new.stuid
7 where stuid=:old.stuid;
8* end student_result_update_cascade;
SQL> /触发器已创建SQL> delete from t_student where stuid='s102203';
delete from t_student where stuid='s102203'
*
第 1 行出现错误:
ORA-04091: 表 SCOTT.T_RESULT 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.STUDENT_RESULT_UPDATE_CASCADE", line 2
ORA-04088: 触发器 'SCOTT.STUDENT_RESULT_UPDATE_CASCADE' 执行过程中出错
SQL> spool off
SQL> l
1 declare
2 type executetestcurtype is ref cursor;
3 executetest_cv executetestcurtype;
4 sql_stmt varchar2(200);
5 tea_teaname varchar2(10);
6 tea_salary number:=3500;
7 tea_record t_teacher%rowtype;
8 begin
9 sql_stmt:='select teaname,salary into tea_teaname,tea_salary
10 from t_teacher
11 where salary>:s';
12 open executetest_cv for sql_stmt using tea_salary;
13 loop
14 fetch executetest_cv into tea_record;
15 exit when executetest_cv%notfound;
16 dbms_output.put_line('教师姓名:'||tea_record.teaname||'工资:'||tea_record.salary);
17 end loop;
18 close executetest_cv;
19* end;
SQL> /
declare
*
第 1 行出现错误:
ORA-00905: 缺失关键字
ORA-06512: 在 line 12
SQL> spool off
SQL> declare
2 type deptname_table_type is table of t_teacher.teaname%type;
3 deptname_tab deptname_table_type;
4 type teasalary_table_type is table of t_teacher.salary%type;
5 teasalary_tab teasalry_table_type;
6 sql_stmt varchar2(200);
7 deptid varchar2(15):='t_10';
8 tname t_teacher.teaname%type;
9 tsalary t_teacher.salary%type;
10 begin
11 sql_stmt:='update t_teacher set salary=salary*1.2 where deptid=:did';
12 returning teaname,salary into :tname,:tsalary;
13 execute immediate sql_stmt
14 using deptid
15 returning bulk collect into deptname_tab,teasalary_tab;
16 for i in 1..deptid_tab.count loop
17 dbms_output.put_line('教师姓名:'||deptname_tab(i)||'新工资:'||teasalary_tab(i));
18 end loop;
19 end;
20 /
SP2-0552: 未声明绑定变量 "TSALARY"。
SQL> spool off
SQL> declare
2 type executetestcurtype is ref cursor;
3 executetest_cv executetestcurtype;
4 type profession_table_type is table of t_teacher.profession%type;
5 profession_tab profession_table_type;
6 type teaname_table_type is table of t_teacher.teaname%type;
7 teaname_tab teaname_table_type;
8 deptid varchar2(15):='t_10';
9 sql_stmt varchar2(200);
10 begin
11 sql_stmt:='select teaname,profession from t_teacher where deptid:=:did';
12 open executetest_cv for sql_stmt
13 using deptid;
14 fetch executetest_cv
15 bulk collect into teaname_tab,profession_tab;
16 close executetest_cv;
17 for i in 1..teaname_tab.count loop
18 dbms_output.put_line('教师姓名:'||teaname_tab(i)||'职称:'||profession_tab(i));
19 end loop;
20 end;
21 /
declare
*
第 1 行出现错误:
ORA-00920: 无效的关系运算符
ORA-06512: 在 line 12
SQL> spool off
1 create or replace trigger student_result_update_cascade
2 after update or delete of stuid on t_student
3 for each row
4 begin
5 update t_result
6 set stuid=:new.stuid
7 where stuid=:old.stuid;
8* end student_result_update_cascade;
SQL> /触发器已创建SQL> delete from t_student where stuid='s102203';
delete from t_student where stuid='s102203'
*
第 1 行出现错误:
ORA-04091: 表 SCOTT.T_RESULT 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.STUDENT_RESULT_UPDATE_CASCADE", line 2
ORA-04088: 触发器 'SCOTT.STUDENT_RESULT_UPDATE_CASCADE' 执行过程中出错
SQL> spool off
SQL> l
1 declare
2 type executetestcurtype is ref cursor;
3 executetest_cv executetestcurtype;
4 sql_stmt varchar2(200);
5 tea_teaname varchar2(10);
6 tea_salary number:=3500;
7 tea_record t_teacher%rowtype;
8 begin
9 sql_stmt:='select teaname,salary into tea_teaname,tea_salary
10 from t_teacher
11 where salary>:s';
12 open executetest_cv for sql_stmt using tea_salary;
13 loop
14 fetch executetest_cv into tea_record;
15 exit when executetest_cv%notfound;
16 dbms_output.put_line('教师姓名:'||tea_record.teaname||'工资:'||tea_record.salary);
17 end loop;
18 close executetest_cv;
19* end;
SQL> /
declare
*
第 1 行出现错误:
ORA-00905: 缺失关键字
ORA-06512: 在 line 12
SQL> spool off
SQL> declare
2 type deptname_table_type is table of t_teacher.teaname%type;
3 deptname_tab deptname_table_type;
4 type teasalary_table_type is table of t_teacher.salary%type;
5 teasalary_tab teasalry_table_type;
6 sql_stmt varchar2(200);
7 deptid varchar2(15):='t_10';
8 tname t_teacher.teaname%type;
9 tsalary t_teacher.salary%type;
10 begin
11 sql_stmt:='update t_teacher set salary=salary*1.2 where deptid=:did';
12 returning teaname,salary into :tname,:tsalary;
13 execute immediate sql_stmt
14 using deptid
15 returning bulk collect into deptname_tab,teasalary_tab;
16 for i in 1..deptid_tab.count loop
17 dbms_output.put_line('教师姓名:'||deptname_tab(i)||'新工资:'||teasalary_tab(i));
18 end loop;
19 end;
20 /
SP2-0552: 未声明绑定变量 "TSALARY"。
SQL> spool off
SQL> declare
2 type executetestcurtype is ref cursor;
3 executetest_cv executetestcurtype;
4 type profession_table_type is table of t_teacher.profession%type;
5 profession_tab profession_table_type;
6 type teaname_table_type is table of t_teacher.teaname%type;
7 teaname_tab teaname_table_type;
8 deptid varchar2(15):='t_10';
9 sql_stmt varchar2(200);
10 begin
11 sql_stmt:='select teaname,profession from t_teacher where deptid:=:did';
12 open executetest_cv for sql_stmt
13 using deptid;
14 fetch executetest_cv
15 bulk collect into teaname_tab,profession_tab;
16 close executetest_cv;
17 for i in 1..teaname_tab.count loop
18 dbms_output.put_line('教师姓名:'||teaname_tab(i)||'职称:'||profession_tab(i));
19 end loop;
20 end;
21 /
declare
*
第 1 行出现错误:
ORA-00920: 无效的关系运算符
ORA-06512: 在 line 12
SQL> spool off
create or replace trigger student_result_update_cascade
2 after update or delete of stuid on t_student
3 for each rowt
declare pragma autonomous_transaction;
4 begin
5 update t_result
6 set stuid=:new.stuid
7 where stuid=:old.stuid;
8* end student_result_update_cascade;
29 sql_stmt:='select teaname,salary into tea_teaname,tea_salary from t_teacher where salary>:s';
写规范点 你的匿名快语法没问题