Oracle-PL/SQL语法基础

朱雀 2023-01-13 04:19 152阅读 0赞

set serveroutput on;
    
    --条件控制(if-then-else) 属性类型(%type) 输出
    declare
    	stu_name students.s_name%type;
    begin
    	select s_name into stu_name from students where s_no='1830090115';
    	if stu_name='LiXiang' then 
    		dbms_output.put_line('NAME:'||stu_name);
    	else
    		dbms_output.put_line('No LiXiang!');
    	end if;
    end;
    /
    
    --条件控制(case) 输入 输出
    BEGIN
        CASE '&str'
          WHEN 'test1' THEN DBMS_OUTPUT.PUT_LINE('test1');
          WHEN 'test2' THEN DBMS_OUTPUT.PUT_LINE ('test2');
          WHEN 'test3' THEN DBMS_OUTPUT.PUT_LINE ('test3');
          WHEN 'test4' THEN DBMS_OUTPUT.PUT_LINE ('test4');
          WHEN 'test5' THEN DBMS_OUTPUT.PUT_LINE ('test5');
          ELSE DBMS_OUTPUT.PUT_LINE ('No Test!');
        END CASE;
    END;
    
    --循环控制(loop) 标量类型 输出
    declare
    	i number:=1;
    begin
    	loop
    	dbms_output.put_line(i||'的平方是'||i*i);
    	i:=i+1;
    	exit when i>10;
    	end loop;
    end;
    /
    
    --循环控制(for) 标量类型 输出
    declare
    	sum_num number:=0;
    begin
    	for i in 1..100 loop
    		sum_num:=sum_num+i;
    	end loop;
    	dbms_output.put_line(sum_num);
    end;
    /
    
    --循环控制(while) 标量类型 输出
    declare
    	i number:=1;
    begin
    	while i<50 loop
    		if mod(i,3)=0 then
    			dbms_output.put_line(i);
    		end if;
    		i:=i+1;
    	end loop;
    end;
    /
    
    --属性类型(%rowtype)
    DECLARE
      str students%rowtype; 
    BEGIN
      select * into str 
      	from students where rownum=1;
      dbms_output.put_line('name: '||str.s_name);
      dbms_output.put_line('number: '||str.s_no);
    END;
    /
    
    --VARRAY
    DECLARE
    	type list is varray(10) of VARCHAR2(10); --INDEX BY PLS_INTEGER;
     	a list:=list();
    BEGIN
    	a.extend(2);
    	a(1):= 'lixiang';
    	a(2):= 'meixia';
    	dbms_output.put_line(a(1));
    	dbms_output.put_line(a(2));
    END;
    /
    
    
    --自定义表 动态SQL
    begin
    	execute immediate 'create table test(name varchar2(10),password varchar2(10))';
    end;
    /