pl/sql 摘抄

1：SET SERVEROUTPUT ON 打开
---exec dbms_output.put_line('');
---exec dbms_output.put('');
注:dbms_output.put_line()不能打印boolean类型的值.

2：DECLARE
v_Num2 NUMBER := 2;
v_String1 VARCHAR2(50) := 'Hello World! ';
v_OutputStr VARCHAR2(50);


3：与数据库表类型相关：
/*
declare
v_empno1 emp.empno%type; //emp表字段级
v_empno2 v_empno1%type;

v_temp dept%rowtype; //dept 表级
begin
v_temp.dname :='hpjianhua'
...
*/
DECLARE
D_NO DEPT.DEPT_NO%TYPE;
D_NAME DEPT.DEPT_NAME%TYPE;
BEGIN
SELECT DEPT_NO,DEPT_NAME [b]INTO[/b] D_NO,D_NAME
FROM DEPT;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
/*
when others then ... end
*/
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_NO));
4：再创建一序列: 并调用相关序列 事务

create sequence seq_errorlog_id start with 1 increment by 1;

declare
v_deptno dept.deptno%type :=10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno =v_deptno;
commit;
exception
when others then
rollback;
v_errcode :=SQLCODE;
v_errmsg :=SQLERRM;
insert into errorlog values (
seq_errorlog_id.nextval , v_errcode,v_errmsg,sysdate
);
commit;
end;
5：for 顺序及反序

for k in 1..10 loop
dbms_output.put_line(k);
end loop;

for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
6:sql%rowcount 用法

select deptno into v_deptno from emp2 where empno = ""; dbms_output.put_line(sql%rowcount || '条记录被影响!');

7：自定义类型
a:Record变量类型
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno :=52;
v_temp.dname :='hpjianhua';
v_temp.loc :='HK';
dbms_output.put_line(v_temp.deptno ||' '||v_temp.dname);
end;
b:--Table 变量类型:也是 自定义的变量类型

declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0) :=4323;
v_empnos(2) :=2342;
v_empnos(-1) :=9999;
dbms_output.put_line(v_empnos(-1));
end;
8：嵌套循环和标号
DECLARE
result INT;
BEGIN
<<outer>>
FOR i IN 1..100 LOOP
<<inter>>
FOR j IN 1..100 LOOP
result:=i*j;
EXIT outer WHEN result=1000;
EXIT WHEN result=500;
END LOOP inner;
dbms_output.put_line(result);
END LOOP outer;
dbms_output.put_line(result);
END;
9：游标goto
DECLARE
i INT:=1;
BEGIN
LOOP
INSERT INTO temp VALUES(i);
IF i=10 THEN
GOTO end_loop;
END IF;
i:=i+1;
END LOOP;
<<end_loop>>
dbms_output.put_line('循环结束');
END;