pl/sql基本结构

pl/sql

基本结构

declare 

   example_text  varchar2(100);
begin
		 example_text:='本例为plsql示例程序块!!!';
		 dbms_output.put_line(example_text);

exception
   when others then
		dbms_output.put_line('出现异常了!!!');
		
end;

declare
        v_xm varchar2(8):='Jame';
		v_zym varchar2(10):='计算机';
		v_zxf number(2):=55;

begin
	  update xs set zxf=v_zxf
		   where xm=v_xm;
		if sql%notfound then 
			dbms_output.put_line('没有该人,需要插入该人!!!');
			insert into xs(xh,xm,zym,zxf) values('007',v_xm,v_zym,v_zxf);
     end if;
end;

--禁用约束
alter table dept disable constraint deptno CASCADE

alter table EMP disable constraint FK_DEPTNO;

--启用约束
alter table dept enable constraint deptno

alter table EMP enable constraint FK_DEPTNO;
--查询约束具体状况
select * from user_constraints   --用SCOTT用户执行，否则找不到

declare 
     row_id rowid;
	 info varchar2(100);

begin
	update scott.dept set deptno=90 where dname='RESEARCH' 
	  returning rowid,dname || '::::' || to_char(deptno)
		into row_id,info;
		dbms_output.put_line('rowid:'||row_id);
		dbms_output.put_line(info);
end;

select t.*, t.rowid from DEPT t

declare 
  row_id rowid;
  info varchar2(40);
	
begin
	insert into scott.dept values(12,'财务室','海口')
	returning rowid,dname||'--->'||to_char(deptno) 
	 into row_id,info;
    dbms_output.put_line('rowid:'||row_id);
		dbms_output.put_line(info);
end;