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%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;