t sql programming pdf

Loop structure:1, Loop loop structureGrammar:LOOPThe statement to execute;Exit when END LOOP;Example: An integer that loops out 1-10DECLARE Number: = 1; BEGIN LOOP dbms_output.put_line (v_num); V_num:=+1; EXIT when Ten ; END LOOP; END;The drawback of loop structure: The structure is not clear enough, exit easy to write.2. While loop structureGrammar:While The statement to execute;END LOOP;Example: Displaying an integer of 1-10;DECLARE Number: = 1; B

Label: 1Create or replace procedure Pr_mytest is 2 3 4 5V_test Number (8) :=123; 6V_char VARCHAR2 (Ten):='Database'; 7C_CHANGL Constant Number ( A,2) :=3.14; 8V_BM t_hq_ryxx.bum%type; 9Type Ty_ry isRecord--a record type in a composite type that defines a new record typeTen ( OneV_XINGM t_hq_ryxx.xingm%type, AV_XINGB t_hq_ryxx.xingb%type, -V_gongz Number (Ten,2) - ); the -V_ry Ty_ry; --variables that declare this type of record - -V_ryrow T_hq_ryxx%rowtype; --all fields of the table +

Local variable local variables must begin with @ and must be described with the DECLARE command before they can be used.　　Use the Select or SET command to set the value of the variable. Description form:DECLARE @ variable name variable type [@ variable name variable type]SELECT @ local variable = variable value 　　 SET @ local variable = variable value 　　 IF ... ELSE ...IF 　　 [ELSE IF [conditional expression] 　　 　　 BEGIN ... END ...BEGIN　　 　　 END　　BEGIN ... END is used to set a program block tha

match.Then I wrote an example:CREATE OR REPLACE PROCEDURE give_bonus (emp_no_id in number, bonus in number) AUTHID Current_userisbegin dbms_output.Put_Line (emp_no_id); Dbms_output. Put_Line (bonus); END give_bonus;/This is followed by the use of the case expressiondeclarel_salnumber; beginselectsalintol_salfromemp whereempno=7839;dbms_output. Put_Line (l_sal); give_bonus (7839, CASE whenl_sal>=1000andl_salSo it's actually an expression, and then the expression gets a result.After the introduct

decimal point --String intercept SUBSTRINGSelect substring (' ABCDEFG ', 2, 3);Intercept 3 bits starting from 2nd, index starting from 1 3. Time-Date function: --Get current system time GetDate ()Select GETDATE (); Sysdatetime () Gets the timestamp of the database service --Get Month daySelect year (' 1999-1-1 '); --Determine if the date is correct, isdate return bitSelect IsDate (' 2000-2-31 ') returns the bit type, False is 0,true is 1 --Add Time DateAddSelect DATEADD (year,5, ' 2000-1-1 ');A

numbers for the result rows of a query. You can specify RANK, Dense_rank, and Row_number.The USING clause is equivalent to a joins condition where each column from the left table is compared to a column with the Same name in the right table.? The use of recursive common table expressions and recursive views.If you want to keep duplicates in the result of a Union, specify union all instead of just union.Data Retrieval Errors/sqlcodecan use a select-statement within an INSERT statement to insert