PL/SQL 学习笔记3

 SQL> var a numberSQL> begin 2 :a :=1000; 3 end; 4 /PL/SQL procedure successfully completed.SQL> editWrote file afiedt.buf 1 begin 2 dbms_output.put_line(:a); 3* end;SQL> /通过这个代码可以看出通过pl/sql初始化赋值的Bind variable可以被其他Pl/sql程序调用

 

%type 类型应用的话 如果前面用的是database column的话 那么不可以加not null,前面引用的是声明的变量可以应用not null

 

A NOT NULLdatabase column constraint does not apply to variables that are declared using %TYPE.  
Therefore, if you declare a variable using the %TYPEattribute that uses a database column defined as NOT  
NULL, you can assign the NULLvalue to the variable.  

eg:

 

 

绑定变量

A bind variable is a variable that you declare in a host environment.

 Bind variables can be used to pass run-time values, either number or character, into or out of one or more PL/SQL programs. The PL/SQL  
programs use bind variables as they would use any other variable. You can reference variables declared in the  
host or calling environment in PL/SQL statements, unless the statement is in a procedure, function, or  
package. This includes host language variables declared in precompiler programs, screen fields in Oracle   Developer Forms applications, and iSQL*Plus bind variables.

 

绑定变量可以在主机环境下声明 可以在sqlplus 这种应用程序下声明 然后pl/sql程序来调用 调用需要在该变量前加colon

赋值的时候可以使用 exec :var_name := 10000; or begin :var_name := 1000 end; 或者是在PL/SQL中赋值

print(sqlplus command)可以打印出这个值

SQL> var result numberSQL> begin 2 select (sal*12) + nvl(comm,0) 3 into :result 4 from emp 5 where empno = 7369; 6 end; 7 /PL/SQL procedure successfully completed.SQL> print resultRESULT------ 9600SQL> exec :result = 10000BEGIN :result = 10000; END; *ERROR at line 1:ORA-06550: line 1, column 15:PLS-00103: Encountered the symbol "=" when expecting one of the following::= . ( @ % ; indicatorThe symbol ":= was inserted before "=" to continue.SQL> exec :result := 10000;PL/SQL procedure successfully completed.SQL> print resultRESULT------ 10000   

 

1 declare 2 v_name emp.ename%type not null; 3 v_balance number(7,2); 4 v_min_balance v_balance%type not null := 10; 5 begin 6 null; 7* end;SQL> / v_name emp.ename%type not null; *ERROR at line 2:ORA-06550: line 2, column 9:PLS-00218: a variable declared NOT NULL must have an initialization assignment

 

注意 set verify off|on 的命令是对 替代变量造成输出显示的新旧值 的控制 on 是显示

 off 是关闭

 

define命令声明的用户变量会把你初始化的值转化为char类型 50000转化为 5,0,0,0,0 5个char类型的字符

SQL> set verify offSQL> var g_monthly_sal numberSQL> define p_annual_sal = 50000SQL> declare 2 v_sal number(9,2) :=&p_annual_sal; 3 begin 4 :g_monthly_sal := v_sal/12; 5 end; 6 /PL/SQL procedure successfully completed.SQL> print g_monthly_salG_MONTHLY_SAL------------- 4166.7

 

 

 

var g_char varchar2(40)var g_num numberdeclare v_char varchar2(40); v_num number(9,2);begin v_char :='41 is the answer'; v_num :=to_number(substr(v_char,1,2)); :g_char := v_char; :g_num := v_num;end;/print g_charprint g_num

 

nested block

 

An identifier is visible in the block in which it is declared and in all nested subblocks, procedures, and  
functions. If the block does not find the identifier declared locally,

it looks up to the declarative section of  the enclosing (or parent) blocks.

The block never looks down to enclosed (or child) blocks or sideways to sibling blocks.  

1 declare 2 x number := 1; 3 y number := 2; 4 begin 5 declare 6 y number := 3; 7 begin 8 dbms_output.put_line(x||' '||y); 9 end; 10 dbms_output.put_line(x||' '||y); 11* end;SQL> /1 31 2

 

 

<<outer>>declare birthdate Date;begin birthdate := to_date('02-AUG-1975','DD-MON-YYYY'); declare birthdate Date; begin birthdate := to_date('03-JAN-1930','DD-MON-YYYY'); dbms_output.put_line(birthdate || ' ' ||outer.birthdate); end;end;/~

 

practise:

 

Create and execute a PL/SQL block that accepts two numbers through iSQL*Plus substitution
variables. Use the DEFINE command to provide the two values. Pass these two values to the PL/SQL
block through iSQL*Plus substitution variables. The first number should be divided by the second
number and have the second number added to the result. The result should be stored in a PL/SQL
variable and printed on the screen.
Note: SET VERIFY OFF in the PL/SQL block.
DEFINE p_num1 = 2
DEFINE p_num2 = 4

 

set verify offset feedback offdefine p_num1 = 2define p_num2 = 4declare v_num1 number :=0; v_num2 number :=0; v_result number;begin dbms_output.put_line(v_num1 || ' '|| v_num2); v_num1 := &p_num1; v_num2 := &p_num2; v_result := v_num1/v_num2 + v_num2; dbms_output.put_line(v_result);end;/

 

var p_result numberdefine p_salary = 50000define p_bonus = 10declare v_salary number(9,2) :=&p_salary; v_bonus number(9,2) :=&p_bonus;begin :p_result := nvl(v_salary,0) * ( 1 + nvl(v_bonus,0));end;/print p_result