PL SQL笔记(三)

loopif 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.记录:

declaretypeemp_record_typeis record(r_name emp.ename%type,r_job emp.job%type);emp_record emp_record_type;
beginselect 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.联合数组:

declaretypeemp_table_typeis table ofemp.ename%typeindex by binary_integer;emp_table emp_table_type;
beginselect 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
);
declaretype itemtable is table of item_type;v_table itemtable := itemtable();
beginv_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 基本结构：

declarev_id number(8) := 10;v_username varchar2(20);
begindelete 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);exceptionwhen no_data_found thendbms_output.put_line('no data');
end;
/

常量：

declarePI constant number(9) := 3.1415926;
begincommit;
end;

变量：

declareage number(3) := 26;
begincommit;
end;

其他类型：

emp.empno%type
emp%rowtype

分支：

if ... then

if sales > 10 thencompute_bonus(empid);update payroll set pay = pay + bonus where empno = emp_id;
end if;

if .. then ... else

if trans_type = 'CR' thenupdate accounts set balance = balance + debit where ...
elseupdate accounts set balance = balance - debit wehre ...
end if;

if trans_type = 'CR' thenupdate accounts set balance = balance - debit where ...
elseif new_balance >= minimum_balance thenupdate accounts set balance = balance - debit where ...elseraise insufficient_funds;end if;
end if;

if .. then ...elsif

beginif sales > 50000 thenbonus := 1500;elsif sales > 35000 thenbonus := 500;elsebonus := 100;end if;insert into payroll values(emp_id, bonus...);
end;

case语句：

case gradewhen 'A' thendbms_output.put_line('A');when 'B' thendbms_output.put_line('B');elsedbms_output_put_line('wrong!');
end case;

搜寻式case语句：

casewhen grade = 'A' thendbms_output.put_line('A');when grade = 'B' thendbms_output.put_line('B');elsedbms_output.put_line(''wrong!);
end case;

loop

loop....
end loop;

exit(只能入到循环中，如果普通PL SQL 块要退出用return)

loopif a > 3 then...exit;end if;
end loop;

exit .. when

loopfetch c1 into ...exit  when c1%notfound;...
end loop;
close c1;

if == exit ... when

if a > 100 thenexit;
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

declaretype datelist is table of date index by binary_integer;dates datelist;k constant integer := 5;
beginfor j in 1 .. 3 loopdates(j * k) := sysdate;end loop;
end;

select count(empno) into emp_count from emp;
for i in 1 .. emp_count loop...
end loop;
-----------------------------------------------------------
<<main>>
declarectr 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 loopfetch cl into emp_rec;exit when cl%notfound;...
end loop;
-------------------------------------------------
<<outer>>
for i in 1 .. 5 loop...for j in 1 .. 10 loopfetch cl into emp_rec;exit outer when cl%notfound;...end loop;
end loop outer;

goto

declaredone boolean;for i in 1 .. 10 loopif done thengoto end_loop;end if;...<<end_loop>>null;end loop;
endl;

declaremy_ename char(10);
begin<<get_name>>select ename into my_ename from emp wher ...begin...goto get_name;end;
end;

null

exceptionwhen zero_divide thenrollback;when value_error theninsert into errors values...when others thennull;

if rating > 90 thencompute_bonus(emp_id);
elsenull
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;