Oracle PL/SQL programming experiment six
experiment content
To bs user login BOOKSALES database, the use of PL/SQL program written the following function modules,
SQL> CONN bs/bs @ booksales

(1) create a function to customer number as a parameter and return the total price of this customer order books,

 create or replace function sumprice (
Id customers. The customer_id % type) 
Return the books. The cost % type 
As 
Sumprice books. The cost % type; 
The begin 
Select sum (quantity * cost) into sumprice from customers, books, the orders, orderitem 
Where customers. The customer_id=the orders. The customer_id and orders. The order_id=orderitem. Order_id and orderitem. Isbn=books. Isbn and customers. Customer_id=id; 
Return sumprice; 
The exception 
The when no_data_found then 
Dbms_output. Put_line (' the id is invaild! '); 
End sumprice; 

/[/code]
(6) create a stored procedure, output the number of different types of books, the average price,

 create or replace procedure books_msg 
As 
Books_count orderitem. Quantity % type; 
The begin 
For emp (in 
Select the title, ISBN, cost from books 
) 
Loop 
Select sum (quantity) into books_count from orderitem where ISBN=emp. ISBN. 
Dbms_output. Put_line (emp) title | | 'unit price:' | | emp. The cost | | ':' | | books_count); 
End loop; 
End books_msg; 

(18) to create a trigger, prohibit customers in non-working time (before 8:00 in the morning, evening after 17:00) order

 CREATE OR REPLACE the TRIGGER trg_orderitem 
BEFORE the INSERT OR UPDATE OR DELETE ON orderitem 
The BEGIN 
IF TO_CHAR (SYSDATE, 'HH24: MI) NOT BETWEEN' 08:00 'AND' 17:00 '
The OR TO_CHAR (SYSDATE, DY, 'NLS_DATE_LANGUAGE=AMERICAN) IN (' SAT', 'SUN') 
THEN 
RAISE_APPLICATION_ERROR (20005, 'only in the normal time to change,'); 
END IF; 
END trg_orderitem; 

/[/code]
Check
Call a function to see the results;

 declare 
Sprice books. The cost % type; 
The begin 
Sprice:=sumprice (1); 
Dbms_output. Put_line (' price '| | sprice); 
The exception 
The when no_data_found then 
Dbms_output. Put_line (' not found '); 
end; 

Execute the stored procedure to see the results;


Trigger flip-flop see

Insert into orderitem values (' 1000 ', '1', 1-18619-8 '978-7-12', '5');

CodePudding user response:

Thanks for sharing, it is recommended that the record in a blog post,