loop
if credit_rating < 3 then
..
exit;
end if;
end loop;
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
select cast(sysdate as timestamp) from dual;
复合类型数据
1.记录:
declare
type
emp_record_type
is record
(
r_name emp.ename%type,
r_job emp.job%type
);
emp_record emp_record_type;
begin
select t.ename, t.job into emp_record from emp t where t.empno = '';
dbms_output.put_line('ename = ' || emp_record.r_name || ', r_job = ' || emp_record.r_job);
end;
/
2.联合数组:
declare
type
emp_table_type
is table of
emp.ename%type
index by binary_integer;
emp_table emp_table_type;
begin
select ename into emp_table(0) from emp where empno = '';
dbms_output.put_line('ename = ' || emp_table(0));
end;
/
3.嵌套表:
嵌套表和联合数组类似，但嵌套表可以作为列的数据类型使用，而联合数组不能。
create or replace type item_type as object
(
t_username varchar2(20),
t_password varchar2(20),
t_age smallint
);
declare
type itemtable is table of item_type;
v_table itemtable := itemtable();
begin
v_table.extend;
v_table(v_table.last) := item_type('dys', 'dys123', 10);
end;
利用嵌套表当表列数据类型：
create or replace type itemtable is table of Item_Type;
create table TestTable
(
address varchar2(100),
phoneNumber varchar2(11),
itemList itemtable
)
nested table itemList store as itemList;
4.变长数组:
变长数组可以作为表列的数据类型或对象类型属性的数据类型，嵌套表长度没有限制，而变长数组长度有限制：
create or replace type idArray_Type as varray(100) of number; create or replace type item_type as object
(
v_itemCode char(10),
v_name varchar2(20)
); create or replace type itemArray as varray(10) of item_type; create table TestT
(
v_id number(8),
items itemArray
)
pl sql 基本结构：
declare
v_id number(8) := 10;
v_username varchar2(20);
begin
delete from A;
insert into A values(v_id, 'ding', 'ding123');
select username into v_username from A where id = v_id;
dbms_output.put_line('v_username = ' || v_username);
exception
when no_data_found then
dbms_output.put_line('no data');
end;
/
常量：
declare
PI constant number(9) := 3.1415926;
begin
commit;
end;
变量：
declare
age number(3) := 26;
begin
commit;
end;
其他类型：
emp.empno%type
emp%rowtype
分支：
if ... then
if sales > 10 then
compute_bonus(empid);
update payroll set pay = pay + bonus where empno = emp_id;
end if;
if .. then ... else
if trans_type = 'CR' then
update accounts set balance = balance + debit where ...
else
update accounts set balance = balance - debit wehre ...
end if;
if trans_type = 'CR' then
update accounts set balance = balance - debit where ...
else
if new_balance >= minimum_balance then
update accounts set balance = balance - debit where ...
else
raise insufficient_funds;
end if;
end if;
if .. then ...elsif
begin
if sales > 50000 then
bonus := 1500;
elsif sales > 35000 then
bonus := 500;
else
bonus := 100;
end if;
insert into payroll values(emp_id, bonus...);
end;
case语句：
case grade
when 'A' then
dbms_output.put_line('A');
when 'B' then
dbms_output.put_line('B');
else
dbms_output_put_line('wrong!');
end case;
搜寻式case语句：
case
when grade = 'A' then
dbms_output.put_line('A');
when grade = 'B' then
dbms_output.put_line('B');
else
dbms_output.put_line(''wrong!);
end case;
loop
loop
....
end loop;
exit(只能入到循环中，如果普通PL SQL 块要退出用return)
loop
if a > 3 then
...
exit;
end if;
end loop;
exit .. when
loop
fetch c1 into ...
exit when c1%notfound;
...
end loop;
close c1;
if == exit ... when
if a > 100 then
exit;
end if; -----------------------------------------------------------
exit when a > 100;
loop label(循环标签)
<<outer>>
loop
...
loop
...
exit outer when ...
end loop;
end loop outer;
while ... loop
while a < 100 loop
...
select sal into salary from emp where x = x;
...
total := total + salary;
end loop;
其他用法:
loop
...
exit when a > 10;
end loop;
--------------------------------------------
do{
} while()
---------------------------------------------
done := false;
while not done loop
....
done := boolean_expression;
end loop;
for ... loop
declare
type datelist is table of date index by binary_integer;
dates datelist;
k constant integer := 5;
begin
for j in 1 .. 3 loop
dates(j * k) := sysdate;
end loop;
end;
select count(empno) into emp_count from emp;
for i in 1 .. emp_count loop
...
end loop;
-----------------------------------------------------------
<<main>>
declare
ctr integer;
begin
...
for ctr in 1 .. 25 loop
...
if main.ctr > 10 then
...
end if;
end loop;
end main;
for exit
for j in 1 .. 10 loop
fetch cl into emp_rec;
exit when cl%notfound;
...
end loop;
-------------------------------------------------
<<outer>>
for i in 1 .. 5 loop
...
for j in 1 .. 10 loop
fetch cl into emp_rec;
exit outer when cl%notfound;
...
end loop;
end loop outer;
goto
declare
done boolean;
for i in 1 .. 10 loop
if done then
goto end_loop;
end if;
...
<<end_loop>>
null;
end loop;
endl;
declare
my_ename char(10);
begin
<<get_name>>
select ename into my_ename from emp wher ...
begin
...
goto get_name;
end;
end;
null
exception
when zero_divide then
rollback;
when value_error then
insert into errors values...
when others then
null;
if rating > 90 then
compute_bonus(emp_id);
else
null
end if;
DCL(数据控制语句)
| 权限 | 说明 |
| create user | 创建其他用户(dba角色) |
| drop user | 删除其他用户 |
| select any table | 查询任何用户表或视图 |
| create any table | 在任何表空间中创建表 |
| drop any table | 删除在任何表空间中所创建的表 |
| create session | 连接数据库 |
| create table | 在用户自己表空间中创建表 |
| create view | 在用户自己表空间中创建视图 |
| create sequence | 在用户自己的表空间中创建序列 |
| create proceudre | 在用业内自己表空间中创建存储过程 |
授权:
grant create any table to scott;
撤销授权：
revoke create any table from scott;
保存点：
savepoint a;
execute dbms_transaction.savepoint('B');
回滚保存点:
rollback to B;
exeucte dbms_transaction.rollback_savepoint('A');
回滚全部事物：
rollback;
execute dbms_transaction.rollback;