PLSQL common script

- Trigger CREATE OR REPLACE TRIGGER GBJ.B_INSERT_TESTBEFORE INSERT OR UPDATEOF NCOLUMNON GBJ.TEST_TABLE REFERENCING NEW AS NEW OLD AS OLDFOR EACH ROWBEGINif: new.ncolumn = 0 then raise_application_error (-20180, 'only insert a positive number!'); End IF; end b_insert_test; - stored procedure Create or Replace Procedure Proce_Insert IS

v_num1 number: = 3; v_num2 number: = 4; v_Str1 varchar2 (30): = 'fasfasf'; v_Str2 varchar2 (30): = 'fasdfasdfaf'; v_outputstr varcha2 (30);

begin insert into test_table (ncolumn, vcolumn) values ​​(v_Num1, v_Str1); insert into test_table (ncolumn, vcolumn) values ​​(v_Num2, v_Str2); commit; select vcolumn into v_OutputStr from test_table where ncolumn = v_Num1; dbms_output.put_line (v_OutputStr) ; Select vcolumn into v_outputstr from test_table where ncolumn = v_num1; dbms_output.put_line (v_outputstr); end proce_insert; / - create process SQL> SET Serveroutput on; - Setout output Declare

v_num1 number: = 3; v_num2 number: = 4; v_Str1 varchar2 (30): = 'fasfasf'; v_Str2 varchar2 (30): = 'fasdfasdfaf'; v_outputstr varcha2 (30);

begin insert into test_table (ncolumn, vcolumn) values ​​(v_Num1, v_Str1); insert into test_table (ncolumn, vcolumn) values ​​(v_Num2, v_Str2); commit; select vcolumn into v_OutputStr from test_table where ncolumn = v_Num1; dbms_output.put_line (v_OutputStr) SELECT VCOLUMN INTO V_OUTPUTSTR from Test_Table WHERE NCOLUMN = V_NUM1; DBMS_OUTPUT.PUT_LINE (v_outputstr); end; / - PL / SQL block DECLARE

v_ncolumn number: = 6; v_vncolumn varchar2 (30);

begin select vcolumn into v_vncolumn from test_table where ncolumn = v_ncolumn; dbms_output.put_line ( 'The Message' 's' || v_vncolumn); exception when NO_DATA_FOUND THEN dbms_output.put_line ( 'à »ÓÐÕÒμ½Æ ¥ ÅäμÄÊý¾Ý'!); end; / - - Define record type Type t_dzrecord is record (v_vname varcha2 (20); - last name field v_vcode varcha2 (20); - code field v_vsex number (2); - Gender field); - Define record type variable v_dzinfo t_dzRecord; - Record type (in the process) Use SELECT V_VNAME, V_VCODE, V_VSEX INTO V_DZINFO from ** _ Table; - Method 1Select * Into v_dzinfo from ** _ Table; - Method 2 - Declining and field type The same variable Method DECLARE

variable_name table_name.column% TYPE; - Record Application typetype T_dzrecord is record (v_vname table_name.column% TYPE; - Last Name field v_vcode table_name.column% TYPE; - Code field v_vsex table_name.column% TYPE; - gender field ); - For the table, you can use the following simple method to define the record variable Declare

Variable_name table_name% rowType; - Use variable_name.v_vsex: = 1;