pl sql basics

Oracle Database Programming: PL/SQL programming basics Oracle Database Programming: basic concepts of Oracle databases http://www.bkjia.com/database/201307/226254.html 2. PL/SQL programming basics:

(dep_name) from Hrman. Departments;SET serveroutput on;BEGINDbms_output.put_line (UPPER (' abc '));END;/Scenario 255: Date-based functionsSysdate function:SET serveroutput on;BEGINDbms_output.put_line (sysdate);END;/To_char functionSET serveroutput on;BEGINDbms_output.put_line (To_char (sysdate));END;/Last_day functionSET serveroutput on;BEGINDbms_output.put_line (Last_day (sysdate));END;/to_date functionSET serveroutput on;BEGINDbms_output.put_line (to_date (' 2010-2-5 ', ' yyyy-mm-dd '));END;

;BEGINDbms_output.put_line (UPPER (' abc '));END;/Scenario 255: Date-based functionsSysdate function:SET serveroutput on;BEGINDbms_output.put_line (sysdate);END;/To_char functionSET serveroutput on;BEGINDbms_output.put_line (To_char (sysdate));END;/Last_day functionSET serveroutput on;BEGINDbms_output.put_line (Last_day (sysdate));END;/to_date functionSET serveroutput on;BEGINDbms_output.put_line (to_date (' 2010-2-5 ', ' yyyy-mm-dd '));END;/Months_between function:SET serveroutput on;DECLAREDat

PL/SQL basics-Exception Handling Exception HandlingI. Exception typeThere are two types of Oracle exceptions: system exceptions and custom exceptions.System exceptions are classified into pre-defined exceptions and non-pre-defined exceptions.1. pre-defined exceptionORACLE defines their error numbers and names. Common predefined exceptions are used to handle commo

What pl/SQL must understand:1. process, function, trigger is pl/SQL programming2. process, function, and trigger are in Oracle3. pl/SQL is a very powerful database process Language4. process. functions can be called in java progra

Tags: define type col document DEP pre raise system-defined variable1. Exception Classification: System exceptions, custom exceptions(1) System exceptions, see related API documentation(2) Custom exceptionsDefine your own exceptions: As with custom variables, the type is exceptionException thrown: Use raise to throw a custom exceptionSetServeroutput onDeclare cursorCemp is SelectEname fromEmpwhereDeptno= -; Pename Emp.ename%type; --Self define exceptionSelf_no_data_found exception;begin Op

View and index of basic PL/SQL PL/SQL: a programming Language combined with a Structured Language (Structured Query Language). It is an extension of SQL and supports multiple data types, such as large objects and Collection types, you can use control statements such as condi

, false.% Rowcount returns the number of rows read by the cursor until the current position. 4> example:Set serveroutput on;DeclareVarno varchar2 (20 );Varprice varchar2 (20 );Cursor mycur (vartype number) isSelect emp_no, emp_zc from cus_emp_basicWhere com_no = vartype;BeginIf mycur % isopen = false thenOpen mycur (1, 000627 );End if;Fetch mycur into varno, varprice;While mycur % foundLoopDbms_output.put_line (varno | ',' | varprice );If mycur % rowcount = 2 thenExit;End if;Fetch mycur into var

Basic PL/SQL syntax (2) identifier is the name of a PL/SQL object, which generally includes constants or variables, cursors, reserved words, exceptions, processes, functions, triggers, etc. Generally, www.2cto.com identifiers must comply with the rules: the maximum length is 30 characters, must start with a letter, can

of variables, constants, cursors, user-defined exceptions ... . BEGIN -- Program start Section SQL statements and PL-SQL statements constitute the execution statement-the code body part ... When an exception occurs in the EXCEPTION program, the exception is caught and the exception is handled. --Abnormal part ... END; -- Pr

-- PL/SQL language -- string connector | select 'abc' | '000000' from dual; -- constants and variables declare --

Exception handling:Even if a good Pl-sql program encounters errors or unforeseen events, a good program should be able to handle all kinds of error situations and recover as much as possible from the error. An error that occurs while the program is running becomes an exception. After an exception occurs, the statement terminates execution, and Plsql immediately gives control to the Plsql exception handling

type]) ...]is SELECT statement;Properties:%found,%notfound--is there a record%isopen--Determine if the cursor is open%rowcount--Number of rows affectedNumber of cursors limit:Connecting the SYS user in the SqlplusConn Sys/[email PROTECTED]:1521/ORCL as SysdbaTo view configuration parameters:Show parameter cursorTo modify the settings for the number of cursors:Alter system set OPEN_CURSORS=400 Scope=both;Scope Value: both,memory (changes the current instance only, does not change the parameter f

later found that it is not at all, and that the error Dup_val_on_index, his book is also wrong.Here is an anonymous blockDate.sqlDECLARE l_date date;BEGIN l_date: = SYSDATE-5; Dbms_output. Put_Line (l_date); end;/I still don't know how to call that process, although I tried it before, but I didn't remember it.I'm going to try to write a call on my own, I write a process for printing time, and then call it in an anonymous block.Get_date.sqlCREATE OR REPLACE PROCEDURE get_date (num_in in number)

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

1. First talk about the difference between pl/SQL and SQL * plus. For example, it is easy to understand: Pl/SQL-> C ++ LanguageSQL * plus-> Microsoft Visual C ++ 6.0 Compiler Pl/SQL is

Tags: col program configuration SQL query field positioning Rod AC MVCThese days encountered two problems, are very wonderful, distressed for three days, the final positioning out.Problem one, inserting data through PL/SQL developer to a remote Oracle database server, querying data through PL/

In fact, this is also a problem at work. To tell the truth, I am not willing to do it myself. I also want to write statements for queries, but I have not thoroughly studied this kind of information,Write SQL statements in JavaProgramAt the same time, I chose to be lazy. First, if it is golden, of course there is no need to say that the Direct Write statement is similar to select. If it is PL/

Using PL/SQL profiler to locate PL/SQL bottleneck code for SQL optimization, you can use SQL tuning advisor to achieve automatic SQL optimization and adjustment. For

Develop PL/SQL subprograms and packages, write triggers using PL/SQL, and Apply Oracle and pljdbc to JDBC. 1. subprograms: Declaration, executable, and Exception Handling (optional) 2. subprogram classification: A. Process-execute some operations A. syntax of the creation process: CREATE [or replace] PROCEDURE BEGIN