Apex 使用和学习

 

 

 

##ssampl 

ref doc http://o7planning.org/en/10345/oracle-apex-tutorial-for-beginners   （change from web to pdf）

http://blog.csdn.net/sunansheng/article/details/50408716

 

 

C:\Users\Desktop\06

 

sample 1:

 

工作区信息
工作区名称
dev_workspace
工作区 ID
系统指定的
说明

dev/dev123

test and learn
管理员信息
用户名
ADMIN/admin123.
dev01/dev01123.

电子邮件
test@sina.com
数据库方案信息
重用现有方案
否
方案名
DEV
表空间
APEX_XXX
数据文件
/database/statdb/statdb/APEX_XXX.DBF

由管理员 ADMIN 预配的工作区 dev_workspace。 数据库用户 DEV 已创建, 该用户具有使用数据文件

NOTE:
database column: column name must uppercase.

/database/statdb/statdb/APEX_2949731940585331.dbf 的默认表空间 APEX_2949731940585331

 

 

demo 1:

--新数据库

create database spdb1

--创建dept表

 

create table dept
(
deptno int primary key,
dname varchar(30),
loc varchar(30)
)
;
--创建emp表

create table emp

(empno int primary key,
ename nvarchar(30),
job nvarchar(30),
mgr int,
hiredate datetime,
sal numeric(10,2),
comm numeric(10,2),
deptno int foreign key references dept(deptno) --因为deptno我们根据需要做成外键

)

--针对外键,请注意

--①外键只能指向主键

--②外键和主键的数据类型要一致

--首先建立dept表,因为dept表与emp表存在约束

insert into dept values(10,'ACCOUNTING','NEW YORK')
/
insert into dept values(20,'RESEARCH','DALLAS')
/
insert into dept values(30,'SALES','CHICAGO')
/
insert into dept values(40,'OPERATIONS','BOSTON')
/
--建立emp表

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20)

insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600.00,300.00,30)

insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250.00,500.00,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,20)

insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,1400.00,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,10)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7788,'SCOTT','ANALYST',7566,'1987--4-19',3000.00,20)

insert into emp(EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO) values(7839,'KING','PRESIDENT','1981-11-17',5000.00,10)

insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,0.00,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100.00,20)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7900,'JAMES','CLERK',7698,'1981-12-3',950,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7902,'FORD','ANALYST',7566,'1981-12-3',3000,20)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7934,'MILLER','CLERK',7782,'1982-1-23',1300,10)

insert into emp values(9999,'SHUNPING','CLERK',7782,'1988-5-5',2456.34,55.66,10)

SELECT * FROM EMP

SELECT * FROM DEPT

 

 

 

 

 

javascript:apex.confirm('Are you sure?','delete_confirm');
line 4927

 

// Declare a variable named htmldb_delete_message

var htmldb_delete_message='Would you like to perform this delete action?';

 

// Or:
// "DELETE_CONFIRM_MSG" is a constant available in APEX.
// It has value: Would you like to delete this thực action?

var htmldb_delete_message='"DELETE_CONFIRM_MSG"';

// Then can use this variable in the whole page.

apex.confirm(htmldb_delete_message,DELETE_CONFIRM');

 

begin
if :P6_DEPTNO is null then
select DEPT_SEQ.nextval
into :P6_DEPTNO
from dual;
end if;
end;

 

drop SEQUENCE dept_seq;

CREATE SEQUENCE dept_seq MINVALUE 42 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 42 CACHE 20 NOORDER NOCYCLE NOPARTITION;

 

 

###sample 1

https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/run-workshop?p210_wid=848
Create a Database Package for Business Logic

For Specification, enter the following:

Copy
########sample 1
CREATE OR replace PACKAGE manage_orders
AS
--------------------------------------------------------------
-- create procedure for add a product temporarily
PROCEDURE add_product (
p_product IN NUMBER,
p_quantity IN NUMBER);
--------------------------------------------------------------
-- create procedure for remove a product temporarily
PROCEDURE remove_product (
p_product IN NUMBER);
--------------------------------------------------------------
-- create function to get the number of items in the shopping cart
FUNCTION Get_quantity
RETURN NUMBER;
--------------------------------------------------------------
-- create procedure for validate if a product exists in the shopping cart
FUNCTION Product_exists(
p_product IN NUMBER)
RETURN NUMBER;
--------------------------------------------------------------
-- create procedure for clear the cart
PROCEDURE clear_cart;
--------------------------------------------------------------
-- create function to validate a customer
FUNCTION Customer_exists(
p_customer_email IN VARCHAR2)
RETURN NUMBER;
--------------------------------------------------------------
-- create procedure to insert orders
PROCEDURE create_order (
p_customer IN VARCHAR2 DEFAULT NULL,
p_customer_email IN VARCHAR2,
p_store IN NUMBER,
p_order_id OUT orders.order_id%TYPE,
p_customer_id OUT NUMBER );
END manage_orders;
Click Create Package Specification.
Create Package page

Navigate to body part of the package by clicking on Body tab and enter the following:

#######sample 2
APEX_COLLECTION 更多帮助参考这个语法
https://docs.oracle.com/en/database/oracle/application-express/21.1/aeapi/APEX_COLLECTION.html#GUID-859B488C-2628-44D7-969F-50872C685B76
Collections enable you to temporarily capture one or more nonscalar values.
You can use collections to store rows and columns currently in session state so they can be accessed,
manipulated, or processed during a user's specific session. You can think of a collection as a bucket in which you temporarily store and name rows of information.

Copy
CREATE OR replace PACKAGE BODY manage_orders
AS
PROCEDURE add_product (p_product IN NUMBER,
p_quantity IN NUMBER)
IS
BEGIN
IF NOT apex_collection.collection_exists (p_collection_name => 'PRODUCTS')
THEN
apex_collection.create_collection(p_collection_name => 'PRODUCTS');
END IF;

apex_collection.add_member(p_collection_name => 'PRODUCTS',
p_n001 => p_product,
p_n002 => p_quantity);
END add_product;

PROCEDURE remove_product (p_product IN NUMBER)
IS
l_id NUMBER;
BEGIN
IF apex_collection.Collection_exists (p_collection_name => 'PRODUCTS')
THEN
SELECT seq_id
INTO l_id
FROM apex_collections a
WHERE collection_name = 'PRODUCTS'
AND a.n001 = p_product;

apex_collection.delete_member(p_collection_name => 'PRODUCTS',
p_seq => l_id);
END IF;
END remove_product;

FUNCTION get_quantity
RETURN NUMBER
IS
l_items NUMBER := 0;
BEGIN
IF apex_collection.collection_exists (p_collection_name => 'PRODUCTS')
THEN
SELECT SUM(n002)
INTO l_items
FROM apex_collections a
WHERE collection_name = 'PRODUCTS';
END IF;

RETURN l_items;
END get_quantity;

FUNCTION product_exists(p_product IN NUMBER)
RETURN NUMBER
IS
l_quantity NUMBER;
BEGIN
IF apex_collection.collection_exists (p_collection_name => 'PRODUCTS')
THEN
SELECT a.n002
INTO l_quantity
FROM apex_collections a
WHERE collection_name = 'PRODUCTS'
AND a.n001 = p_product;

RETURN l_quantity;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END product_exists;

PROCEDURE clear_cart
IS
BEGIN
IF apex_collection.collection_exists (p_collection_name => 'PRODUCTS')
THEN
apex_collection.truncate_collection(p_collection_name => 'PRODUCTS');
END IF;
END clear_cart;

FUNCTION customer_exists(p_customer_email IN VARCHAR2)
RETURN NUMBER
IS
l_customer customers.customer_id%TYPE;
BEGIN
SELECT customer_id
INTO l_customer
FROM customers
WHERE email_address = p_customer_email;

RETURN l_customer;
EXCEPTION
WHEN no_data_found THEN
RETURN 0;
END customer_exists;

PROCEDURE create_order (p_customer IN VARCHAR2,
p_customer_email IN VARCHAR2,
p_store IN NUMBER,
p_order_id OUT orders.order_id%TYPE,
p_customer_id OUT NUMBER)
IS
BEGIN
p_customer_id := customer_exists(p_customer_email);

IF p_customer_id = 0 THEN
INSERT INTO customers
(full_name,
email_address)
VALUES (p_customer,
p_customer_email)
returning customer_id INTO p_customer_id;
END IF;

INSERT INTO orders
(order_datetime,
customer_id,
store_id,
order_status)
VALUES (SYSDATE,
p_customer_id,
p_store,
'OPEN')
returning order_id INTO p_order_id;

IF apex_collection.collection_exists (p_collection_name => 'PRODUCTS')
THEN
INSERT INTO order_items
(order_id,
line_item_id,
product_id,
unit_price,
quantity)
SELECT p_order_id,
seq_id,
p.product_id,
p.unit_price,
n002
FROM apex_collections a,
products p
WHERE collection_name = 'PRODUCTS'
AND p.product_id = a.n001;
END IF;

apex_collection.delete_collection(p_collection_name => 'PRODUCTS');
END create_order;
END manage_orders;

 

 

 

##########sample 3

ORA-02292: integrity constraint violated - child record found while creating procedures in ORACLE SQL Developer?

Ask Question

Asked 5 years, 2 months ago

Modified 5 years, 2 months ago

Viewed 7k times

 

0

I have written a procedure that has as input the CUSTOMER_ID. Then the procedure deletes the corresponding customer from the table CUSTOMERS.

CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER 
(CUSTOMER_ID NUMBER) AS 
TOT_CUSTOMERS NUMBER;
BEGIN
    DELETE FROM CUSTOMERS
    WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
    TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
    END;
/

I have to execute the procedure to delete customer with id 1.

EXECUTE DELETE_CUSTOMER(01);

When I do this, I get an error

Error starting at line : 120 in command -
BEGIN DELETE_CUSTOMER(01); END;
Error report -
ORA-02292: integrity constraint (TUG81959.ORDERS_FK_CUSTOMERS) violated - child record found
ORA-06512: at "TUG81959.DELETE_CUSTOMER", line 5
ORA-06512: at line 1
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

I know this is because there is a foreign key for CUSTOMER_ID on the table ORDERS, which means the customer cannot be deleted because he has placed an order. How do I write the code so that I can first delete the corresponding ORDER_DETAILS and then delete the corresponding ORDERS so that I can finally be able to delete a record from CUSTOMERS?

I tried rewriting the code but I am just lost now:

CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER 
(CUSTOMER_ID_IN NUMBER) AS 
TOT_CUSTOMERS NUMBER;
CURSOR C1 IS
DELETE FROM ORDERS
WHERE ORDERS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C2 IS
DELETE FROM ORDER_DETAILS
WHERE ORDER_DETAILS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C3 IS
DELETE FROM CUSTOMERS
WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
BEGIN
    OPEN C1;
    OPEN C2;
    OPEN C3;
    IF C1%FOUND AND C2%FOUND AND C3%FOUND
    THEN TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
    END IF;
    CLOSE C1;
    CLOSE C2;
    CLOSE C3;
END;
/

Here are the tables for reference: 

• oracle
• oracle-sql-developer

Share

Improve this question

Follow

 

asked Oct 22, 2017 at 2:57

pyuntae

111 silver badge33 bronze badges

Add a comment

1 Answer

Sorted by:

                                               Highest score (default)                                                                     Date modified (newest first)                                                                     Date created (oldest first)                               

1

No code needed for this, just define the constraint with ON DELETE CASCADE option.

alter table orders drop constraint ORDERS_FK_CUSTOMERS;
alter table orders add constraint ORDERS_FK_CUSTOMERS 
  foreign key (customer_id) references customers(customer_id) on delete cascade;

Same for order_details.

Share

Improve this answer

Follow

 

answered Oct 22, 2017 at 9:22

Balazs Papp

38.8k22 gold badges2323 silver badges4343 bronze badges

•  
  Just stumbled across this while looking for something else. Realizing the thread is a month old, but if @pyuntae is still following - I gotta say that from a business standpoint, deleting a customer -- and even more so, deleting a PO sounds very problematic. Do you really want to come up to the end of the accounting year knowing that you have been deleting PO's all year long? How would you ever be able to balance your books? How would you honor warranty claims or recalls? Most jurisdictions require, by law, that records like this be retained for a lengthy period of time. 

  – EdStevens

   Nov 26, 2017 at 20:09

   

   

   

  ####sample 3 交互式表格限制列字段的长度

  列字段

  <span style=""><p style="max-width:30px;word-wrap:break-word;">#"数据库产品"#</p></span>

   

  ###sample 4

  交互式报表丢失编辑属性，在这里重新编辑

  

   

   

  ####sample 5 使用行过滤器对日期进行过滤

  M>to_date('2023-09-01','yyyy-mm-dd')