存储过程为
CREATE OR REPLACE PROCEDURE DELETE_BAD_WORD AS
CURSOR C_BAD_WORD IS SELECT * FROM BAD_WORD;
BEGIN
FOR V_BAD_WORD IN C_BAD_WORD LOOP
DELETE FROM LEAVE_WORD
WHERE TITLE LIKE '%'||V_BAD_WORD.KEY_WORD||'%' OR CONTENT LIKE '%'||V_BAD_WORD.KEY_WORD||'%';
END LOOP;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END DELETE_BAD_WORD;
/
CREATE OR REPLACE PROCEDURE DELETE_BAD_WORD AS
CURSOR C_BAD_WORD IS SELECT * FROM BAD_WORD;
BEGIN
FOR V_BAD_WORD IN C_BAD_WORD LOOP
DELETE FROM LEAVE_WORD
WHERE TITLE LIKE '%'||V_BAD_WORD.KEY_WORD||'%' OR CONTENT LIKE '%'||V_BAD_WORD.KEY_WORD||'%';
END LOOP;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END DELETE_BAD_WORD;
/
CURSOR C_BAD_WORD IS SELECT * FROM BAD_WORD;
BEGIN
FOR V_BAD_WORD IN C_BAD_WORD LOOP
DELETE FROM LEAVE_WORD WHERE TITLE LIKE '%'||V_BAD_WORD.KEY_WORD ||'%'
or content LIKE '%'||V_BAD_WORD.KEY_WORD||'%';
END LOOP;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END DELETE_BAD_WORD;
/
SQL> exec DELETE_BAD_WORD;PL/SQL 过程已成功完成。现在好了,数据删除了,谢谢各位!
我创建了如下存储过程,但是不知道如何调用?CREATE OR REPLACE PROCEDURE "YCYB"."INS_TABLE" ( p_a char, p_b char)
as
v_a char(10);
v_b char(10);
begin
insert into test_prc values(v_a,v_b);
end;SQL> desc test_prc
名称 空? 类型
----------------------------------------- -------- -----------
A NOT NULL CHAR(10)
B NOT NULL CHAR(10)sql>exec INS_TABLE 后面如何把参数传入?
我创建了如下存储过程,但是不知道如何调用?CREATE OR REPLACE PROCEDURE "YCYB"."INS_TABLE" ( p_a char, p_b char)
as
v_a char(10);
v_b char(10);
begin
insert into test_prc values(v_a,v_b);
end;SQL> desc test_prc
名称 空? 类型
----------------------------------------- -------- -----------
A NOT NULL CHAR(10)
B NOT NULL CHAR(10)sql>exec INS_TABLE 后面如何把参数传入?
BEGIN ins_table('a','b'); END;*
ERROR 位于第 1 行:
ORA-01400: 无法将 NULL 插入 ("WZW_TEST"."TEST_PRC"."A")
ORA-06512: 在"WZW_TEST.INS_TABLE", line 7
ORA-06512: 在line 1
insert into test_prc values(p_a,p_b);
v_a VARCHAR2(10);
v_b VARCHAR2(10);
没有任何意义了,直接把参数传入了啊!
CREATE OR REPLACE PROCEDURE "WZW_TEST"."INS_TABLE" ( p_a
VARCHAR2, p_b VARCHAR2)
as
begin
select a,b into p_a,p_b from test_prc where rownum <=1;
dbms_output.put_line('p_a:'||p_a||' p_b:'||p_b);
end INS_TABLE;
创建两个变量