1. Insert. Insert , , ID_CRE DAT_CRE . 5- . , .
2.3 ר
. :
- ;
- ( DDL );
- ( DDL ).
. .
1. ?
2. . Cache?
3. ?
4. PL/SQL .
3
. PL/SQL. (Procedure) (Function).
3.3.1. PL/SQL
PL/SQL . . NUMBER, . Inventory. , Order_record. Order_record .
DECLARE
in_inventory NUMBER(5);
BEGIN
SELECT quantity INTO in_inventory FROM Inventory
WHERE product = ;
IF in_inventory > 0 THEN --
UPDATE Inventory SET quantity = quantity - 1
WHERE product = ' ';
INSERT INTO Order_record
VALUES (' ', SYSDATE);
ELSE
INSERT INTO Order_record
VALUES (' ', SYSDATE);
END IF;
COMMIT;
END;
PL/SQL SQL ORACLE . , , ( ) . , PL/SQL SQL .
PL/SQL , .. (, ), PL/SQL, , . . , PL/SQL , .
|
|
|
( ) . , . , .
, PL/SQL : , . ( PL/SQL , , .) .
[DECLARE -- ]
BEGIN
--
[EXCEPTION -- ]
END;
. , . . .
PL/SQL , . .
, .
PL/SQL SQL , . . , .
SQL, , , VARCHAR2, DATE. NUMBER, PL/SQL, BOOLEAN, BINARY INTEGER. , part_no 4- in_stock TRUE FALSE. :
part_no NUMBER(4);
in_stock BOOLEAN;
PL/SQL, RECORD TABLE.
. (:=), . , . :
tax:= price * tax_rate;
bonus:= current_salary * 0.10;
raise:= TO_NUMBER(SUBSTR('750 raise', 1, 3));
valid:= FALSE;
SELECT FETCH . ORACLE 10%, :
SELECT sal*0.10 INTO bonus FROM emp
WHERE empno=emp_id;
bonus .
, CONSTANT . . minimum_balance:
|
|
|
miniraura_balance CONSTANT REAL:= 10.00;
PL/SQL , , . , , .
%TYPE , . , , , . , , books title. my_title, , title, %TYPE :
my_title books.title%TYPE
_title %TYPE . title. title (, ), y_title .
PL/SQL . , . %ROWTYPE , . , ( ).
. , , dept_rec. , dept.
DECLARE
dept_rec dept%ROWTYPE;
...
, :
my_deptno:= dept_rec.deptno;
, , , %ROWTYPE , , :
DECLARE
CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp;
emp_rec c1%ROWTYPE;
FETCH cl INTO emp_rec; ename ename emp_rec, sal sal .. (. 3.1).
3.1
| emp_rec | |
| emp_rec.ename | |
| emp_rec.sal | 950.00 |
| emp_rec.hiredate | 03.12.2001 |
| emp_rec.job |
PL/SQL SQL. PL/SQL ORACLE, , , , IF-THEN-ELSE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN GOTO. .
. IF-THEN-ELSE . IF ; THEN , , ; ELSE , , .
, . $500 3, , , . , ; .
DECLARE
acct_balance NUMBER(11, 2);
|
|
|
acct CONSTANT NUMBER(4):= 3;
debit_amt CONSTANT NUMBER(5,2):= 500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
WHERE account_id = acct FOR UPDATE OF bal;
IF acct_balance >= debit_amt THEN
UPDATE accounts SET bal = bal - debit_amt
WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES
(acct, acct_balance, 'Insufficient funds');
-- ,
END IF;
COMMIT;
END;
, , , . , , . , PL/SQL. .
LOOP . LOOP , END LOOP . , :
LOOP
--
END LOOP;
FOR-LOOP . , , .
. , FOR:
FOR i IN 1..order_qty LOOP
UPDATE sales SET custno = customer_id
WHERE snum = snum_seq.NEXTVAL; END LOOP;
WHILE-LOOP . . TRUE, , . FALSE NULL, , .
7902, $4000:
DECLARE
salary emp.sal%TYPE;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno CONSTANT NUMBER(4):= 7902;
BEGIN
SELECT sal, ragr INTO salary, mgr_num FROM emp
WHERE empno = starting_empno;
WHILE salary < 4000 LOOP
SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp
WHERE empno = mgr num;
END LOOP;
INSERT INTO temp VALUES (NULL, salary, last_name);
COMMIT;
END;
EXIT-WHEN , . EXIT, WHEN. TRUE, . , total 25000:
LOOP
...
total = total + salary;
EXIT WHEN total > 25000;
-- ,
END LOOP;
--
GOTO . , , PL/SQL. GOTO , :
IF rating > 90 THEN
GOTO calc_raise; --
|
|
|
END IF;
...
calc_raise
IF job_title = 'SALESMAN' THEN --
raise:= commission * 0.25;
ELSE
raise:= salary * 0.10; END IF;
SQL ORACLE , SQL. PL/SQL, , SQL . : . PL/SQL SQL, , , . , , , . :
DECLARE
CURSOR cl IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;
, , . , . . 3.1, . . :
SELECT empno, ename, job FROM emp WHERE deptno = 20;
. 3.1
- . , COBOL , , . PL/SQL , , , . , , .
. 3.2
. 3.2, OPEN, FETCH CLOSE.
OPEN , , . FETCH . CLOSE .
FOR
, , , FOR OPEN, FETCH CLOSE. FOR %ROWTYPE, , , . FOR _r , c1%ROWTYPE:
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno FROM emp;
...
BEGIN
...
FOR emp_rec IN c1 LOOP
...
salary_total:= salary_total + emp_rec.sal;
END LOOP;
END;
, .
PL/SQL , . , , .. PL/SQL. , .
. , , ZERO-DIVIDE. RAISE.
DECLARE
salary NUBER(7,2);
commission NUMBER(7,2);
comm_missing EXCEPTION; --
BEGIN
SELECT sal, comm INTO salary, commission FROM emp
WHERE empno =:emp_id;
IF commission IS NULL THEN
RAISE comm_missing; --
ELSE
:bonus:= (salary * 0.05) + (commission * 0.15);
END IF;
EXCEPTION --
WHEN comm_missing THEN
--
END;
PL/SQL. , . , RAISE. , . ( commission). , , comm_missing.
|
|
|
, emp_id bonus , PL/SQL.
, . , , . PL/SQL . , PL/SQL , , .
PL/SQL , , (, , ). , , , , .
. 25% . . , ; .
PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN
SELECT comm * 0.25 INTO bonus FROM emp
WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll SET pay = pay + bonus
WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN comm_missing THEN
...
END award_bonus;
