所以我想创建这个触发器,但我一直收到ORA-00922错误,但我找不到修复方法。 CREATE OR REPLACE TRIGGER employee_briu
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
BEGIN
SET :NEW.first_name = TRIM(:NEW.first_name);
SET :NEW.last_name = TRIM(:NEW.last_name);
SET :NEW.id = UPPER(:NEW.id);
END employee_briu; 错误日志:
我目前正在创建一个SQL脚本,该脚本在数据库中进行了许多更改,我希望用户为每个步骤确认结果。
代码:
SET ECHO ON
SET AUTOCOMMIT OFF
SET EXITCOMMIT OFF
-- make changes
update plan_table
set statement_id = '3'
where statement_id = '2';
-- check if correct
select statement_id from plan_table;
-- ask confirmation
ACCEPT response CHAR DE
这个街区有什么问题吗?请帮我解决这个问题。将根据where子句只选择一行。
表分类
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER(10)
NAME VARCHAR2(100) Y
PL/SQL :
declare
type clazzes_row_type is record
(clazz_rownum number,
clazz_id clazzes.id
给定一张表:
desc SOL_3_ARTIFACTS
Name Null Type
---- ---- -------------
DATA BLOB
ID VARCHAR2(100)
如果需要使用SQL*plus将BLOB列的内容读入为VARCHR2,则需要如下语句:
select UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(lob_loc,2000,1))
from SOL_3_ARTIFACTS where ID = 'RETAILPOOLS'
有人能告诉我如何获
获得这一行的以下错误将创建一个存储过程,该存储过程以其参数作为工业as
CREATE OR REPLACE PROCEDURE businesses (industryid INDUSTRY.INDUSTRYID%TYPE) AS
CURSOR businessloop IS
select tx.UNIONTITLE,b.ABNNUMBER,b.BUSINESSNAME
from business b INNER JOIN business_industry bi
on
b.ABNNUMBER = bi.ABNNUMBER
INNER JOIN industry ind
on
bi
create or replace PROCEDURE GEN_STATEMENT_SP(indexNM IN VARCHAR2, tableNM IN VARCHAR2) AS
BEGIN
DECLARE
uniqueSTMT VARCHAR2(30);
nonUniqueSTMT VARCHAR2(30);
charOn VARCHAR2(5);
tempfld VARCHAR2(500) ;
CURSOR chkTyp IS(SELECT ES_UNIQUENESS from sys.dba_ind_columns where INDEX_NAME = indexNM and TA
喂,伙计们,你们能帮帮我吗?
CREATE TRIGGER operation
AFTER UPDATE ON COMPTE
for each row
BEGIN
IF :old.solde > :new.solde THEN
INSERT INTO HISTORY VALUES(NUMEROCOMPTE,DATESOLDE,'Retrait');
end if;
IF :old.solde < :new.solde THEN
INSERT INTO HISTORY VALUES(NUMEROCOMPTE,DATESOLDE,'Versement'
谁能告诉我为什么这个不能执行?
set serveroutput on ;
Declare
TYPE type_emp IS RECORD(
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE);
rec_emp type_emp;
due_for_a_raise CHAR(1);
begin
SELECT last_name, salary into rec_emp
from employees
where employee_id = 1
我试图在Oracle11g上运行这段代码,它给出了下面的错误。我似乎不能把它弄对。
DECLARE
CURSOR bookcursor IS
SELECT btName, BookCopy.Isbn, pubName, dateDestroyed
FROM booktitle bt
JOIN publisher p
ON bt.pubId = p.pubId
JOIN bookcopy bc
ON bt.Isbn = bc.Isbn
WHERE datedestroyed IS NULL
因此,我正在为oracle编写一个函数,但是我看到了错误,它们说sql语句被忽略了,所以表达式丢失了。
职能:
CREATE OR REPLACE FUNCTION getQueueNumber
(student IN VARCHAR,course IN CHAR)
RETURN INT
IS
queuePosition INT;
date TIMESTAMP;
BEGIN
SELECT waitingDate INTO date
FROM WaitingCourseStudent wcs
WHERE wcs.student=st
FOR r IN (select 'DRR_DEV.' ||object_name as obj from dba_objects where object_type = 'INDEX'
and owner ='DRR_DEV')
LOOP
l_sql := 'ALTER INDEX '||r.obj||' REBUILD'||'';
execute immediate l_sql;
END LOOP;
在上面的FOR..IN循环中,循环特定模式的所有索引
下面有检查表存在的代码&继续进行。这里的问题是为什么它继续到其他部分,即使在数据库中不存在表&也是它返回的"0“。
SQL> SET serveroutput ON trimspool on feed off echo on
declare
c_cnt number;
t_cnt number;
begin
select count(1) into t_cnt from dba_tables where owner='PRODDBA' and table_name='IOT_LIST';
dbms_output.put_line(t
我试图查找所有包含名为arg_column_name的列的表,在这些表的该列中查找值arg_column_value,并向用户返回表的名称和找到的行数。
我用海狸做IDE。我知道pgsql和pl/pgsql,但我以前对Oracle PL/SQL一无所知。
这就是为什么这个问题很有说服力的原因。
我的程序是:
CREATE OR REPLACE PROCEDURE FIND_TABLE_WITH_COLUMN_VALUE (arg_column_name IN VARCHAR2, arg_column_value IN VARCHAR2)
IS
v_rowcount NUMBER;
我在oracle SQL developer上有此错误。
create table loan_audit (
employee_id number not null,
tool_no number not null,
tool_out_date date not null,
due_date date not null);
这是我创建的触发器
CREATE OR REPLACE TRIGGER loan_audit_trigger
AFTER INSERT OR UPDATE OR DELETE
ON loan_02
FOR EACH ROW
BEGIN
IF INSERTING
我在运行这段代码时遇到了问题。它说触发器是用编译错误创建的,但没有解释错误所在,也没有给出任何输出。下面是我得到的代码和错误。我使用的是Oracle11gSQL,使用的是PL/ R1。
CREATE OR REPLACE TRIGGER checkRecommendedAge
BEFORE INSERT OR UPDATE ON Loan
FOR EACH ROW
DECLARE
borAge number;
ex exception;
BEGIN
SELECT count(*) INTO borAge
FROM Loan
WHERE date
请考虑以下代码(query.sql):
create or replace function age (dateOfBirth date)
return number
is
mAge number(5,2);
begin
mAge:=(sysdate-dateOfBirth)/365.25;
return mAge;
end;
SQL> @query.sql
13
14
15 /
Warning: Function created with compilation errors.
当我单击Show error时,我会得到以下内容:
代码:
SQL> sho
我试图根据这个中提供的指令进行插入。
我可以运行这个例子
CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;
CURSOR c IS SELECT * FROM all_objects;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
我正在尝试对我的模式中的每个表进行计数。
SET serveroutput ON;
BEGIN
FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
FOR J IN (SELECT COUNT(1) CNT FROM I.TABLE_NAME) LOOP
DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME||'>>'||J.CNT);
END LOOP;
END LOOP;
END;
/
但是我得到了像这样的错误。
Error report:
ORA-06550: line 3, COLUMN 37:
当我尝试创建一个过程为user1时,我给出了代码和错误消息。我尝试访问OracleXE18c中的示例hr模式中的employees表。我能知道如何通过用户定义的过程访问hr架构对象吗?
CREATE OR REPLACE PROCEDURE proc_1 IS
v_name hr.employees.first_name%TYPE;
v_num NUMBER;
BEGIN
SELECT first_name INTO v_name FROM hr.employees
WHERE employee_id=100;
v_num:=5;
DBMS_OUTPUT.PUT_LINE('test&
我正在使用SQL Developer并编写这段PL/SQL代码,但我遇到了错误。请帮帮忙。 SET SERVEROUTPUT ON
ACCEPT name prompt 'Name of employee'
declare
v_sal NUMBER;
BEGIN
v_sal := &name;
SELECT
salary
INTO v_sal
FROM
employees
WHERE
first_name = '&name';
dbms
groovy教程中:
有关于程序的部分。当我尝试这个例子时:
The same example again but with a GString variation:
def first = 'Sam'
sql.call("{$Sql.VARCHAR = call FullName($first)}") { name ->
assert name == 'Sam Pullara'
}
我得到了异常:
Chyba: ORA-06550: line 1, column 13:
PLS-00222: no function with n
我有一个名为applications的表和一个名为application_history的表。我希望保留应用程序的历史记录,并且每当applications表中的行更新时都使用触发器,以便在实际更新之前将该行复制到application_history。
目前,我已经从另一篇SO post中写出了这段代码:
create or replace
trigger APPLICATION_UPDATE_TRG
BEFORE UPDATE ON TBL_APPLICATIONS
FOR EACH ROW
DECLARE
CURSOR curAppHistory IS
SELECT re
在映射过程参数时出现问题。错误:
Error querying database. Cause: java.sql.SQLException: ORA-06550: line 1, column 7:\nPLS-00201: identifier 'getAnswer' must be declared\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n\r\n### The error may exist in ..retrieveInfoMapper.xml\r\n### The error may inv
我正在尝试显示质押ID、质押金额、每月付款数量、付款日期、付款金额和为每个质押做出的第一次付款,按质押ID排序,然后按付款日期排序。 Name Null? Type
----------- -------- -----------
IDPLEDGE NOT NULL NUMBER(5)
IDDONOR NUMBER(4)
PLEDGEDATE DATE
PLEDGEAMT NUMBER(8,2)
IDPROJ NUMBER(
我得到了这个错误:
Error starting at line 2 in command:
BEGIN
DELETE * FROM book_copies;
DELETE * FROM books;
/* more code here */
END;
Error report:
ORA-06550: line 2, column 10:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 3, column
执行pl/sql im获取错误时:
ORA-06550: line 1, column 316: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.
PLSQL示例:
DECLARE
SQL1 VARCHAR2 (1500);
SQL2 VARCHAR2 (1500);
BEGIN
SQL1 := 'INSERT INTO das_html_caption VALUES (''test_test'')';
我一直在使用sqldeveloper研究来自的触发器,并且我连接了Oracle11g数据库。我成功创建了customers表。但是当我尝试创建触发器时:
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.p
我正在尝试编写一个具有引用游标和自定义类型的存储过程。我希望将结果提取到自定义类型中,然后在循环中打印结果。到目前为止,我在下面通过研究如何做到这一点进行了尝试。但我遇到了一些错误。有人能告诉我做这件事的正确方法吗?
CREATE OR REPLACE type BUNDLE_OBJECT as object
(
customer_ref varchar2(20),
product_id number(9),
bundle_name varchar2(100)
);
/
create or replace type t_bundle IS TABLE OF BUNDLE_O
我正在尝试创建一个如下所示的函数 CREATE OR REPLACE FUNCTION get_sal
(dep_id IN departments.department_id%TYPE)
RETURN NUMBER IS
v_sal employees.salary%TYPE;
BEGIN
SELECT AVG(salary) INTO v_sal FROM Employees
WHERE department_id = dep_id;
RETURN v_sal;
END; 我收到一个错误,上面写着 Error starting at lin
我对帕拉姆有个程序:
procedure GetReceipt(iPaymentID in number
,oReceipt out sys_refcursor
,oReceiptItems out sys_refcursor);
我试着运行这个匿名块:
SET serveroutput on;
DECLARE
oReceipt sys_refcursor;
oReceiptItems sys_refcursor;
我与用户" user“连接到oracle10g数据库,当我在DBA_ROLES上运行select时,它显示结果时没有任何问题,但是当选择在一个过程中返回错误ORA-00942:表或视图不存在。
用户是否丢失了一些特权?为什么可以执行SELECT,但不能将其包含在过程中?
SQL> SELECT COUNT(*) FROM DBA_ROLES;
COUNT(*)
----------
18
SQL> CREATE OR REPLACE PROCEDURE tst_role IS
v VARCHAR2(100);
BEGIN
我有以下代码:
declare
var_cdb varchar2(3);
var_eleven varchar2(2);
...
..
begin
SELECT to_number(substr(version,1,2)) into var_eleven FROM V$INSTANCE;
if var_eleven > 11
then
select cdb into var_cdb from v$database;
if var_cdb = 'YES'
then
....
但是在Oracle 11g中执行PL/SQL代码时,我得到以下错误:
我有一个过程,我使用以下命令调用该过程
exec ot.selector('regions');
但是,我在这一行得到了错误:
select * from ot."||p_table_name||";
所以我试着做的程序是:
create or replace procedure ot.selector(p_table_name varchar2)
is
cursor cur is
select * from ot."||p_table_name||";
begin
for i in cur
l
我有一个变量"a“,它包含错误,我想把它当作数组,用newlines.How分隔,我可以使用shell脚本或awk将其转换成一行吗?
输入数据
a=(BEGIN DE_DUP_DROP_DY_TABLE1('$Table_Name'); END;
Error at line 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DE_DUP_DROP_DY_TABLE1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement i
我需要一个人帮我写PL/SQL。实际上,我不明白为什么Oracle不想接受以下代码:
set serveroutput on;
CREATE OR REPLACE PROCEDURE route (gagnant VARCHAR2) IS
CURSOR perdants IS SELECT NPerdant FROM RENCONTRE WHERE NGagnant = gagnant;
BEGIN
FOR perdant IN perdants
LOOP
dbms.output.put_line(gagnant ||
你好,我正在尝试创建一个触发器,它应该根据所有注册用户的工作类别(医疗/其他)将用户总数检索为两个参数,因此我开发了:
CREATE OR REPLACE TRIGGER count_before_insert
BEFORE INSERT
ON REGISTRATION_FORM
FOR EACH ROW
DECLARE
p_current_doctor_number varchar2(20);
p_current_other_number varchar2(20);
p_ann_id number;
BEGIN
--
因此,我试图在Oracle SQL developer中创建一个过程。该过程接收一个年份和一个月作为一个数字,然后从一个表中检查属性"Date“(它的类型是DATE),如果月份和年份等于该过程接收的日期,那么我们将打印该表。但是我犯了很多错误,而且不起作用。
create or replace procedure PR_SALE( month number, year number ) is
cursor c is
select v.OID_V, v.DATE, v.SHIPPING_COSTS
from ( SALES v inner join CLIENT c on ( v.OID
create or replace PROCEDURE CHECK_BLACKLIST
(
P_EMPLOYEE_USERNAME IN VARCHAR2
, P_ROWCOUNT OUT NUMBER
) AS
BEGIN
SELECT COUNT(*) INTO P_ROWCOUNT FROM boe_safegaurd WHERE UPPER(employee_username) = UPPER(P_EMPLOYEE_USERNAME);
END CHECK_BLACKLIST;
错误
当我运行这个程序时,我输入了joe@example.com。
ORA-06550:
假设员工的到期日期定义为hire_date之后的一年。我已经创建了一个函数,它试图完成这样的任务:
create or replace expiredate(empno in number) return date is
hiredate employees.hire_date%type;
begin
select add_months(e.hire_date,12) into hiredate
from employees e
where empno is not null;
return hiredate;
end e