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 = '7369'; 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 = '7369'; 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%typeemp%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(循环标签)
<>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;-----------------------------------------------------------<>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;-------------------------------------------------<>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; ... <> null; end loop;endl;
declare my_ename char(10);begin <> 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 nullend 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;