Data Base

   
1) Code Behind Retrieve datas from Oracle Database

            DataTable dt = new DataTable();        
            conn.ConnectionString = OraDbConnString;
            conn.Open();
            OracleCommand cmd = new OracleCommand(StoreProcedureName", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.Parameters.Add("inId", OracleType.Number).Value = ID;
            cmd.Parameters.Add("p_recordset1", OracleType.Cursor).Direction = ParameterDirection.Output;
            OracleDataAdapter OraAdapter = new OracleDataAdapter(cmd);        
            OraAdapter.Fill(dt);
            conn.Close();        
            return dt

2) Inside Store  Procedure.

create or replace
PROCEDURE  StoreProcedureName
(
p_recordset1 OUT SYS_REFCURSOR ,
inId IN NUMBER,
)
AS
BEGIN

      OPEN p_recordset1 FOR
      SELECT * FROM Table_1 WHERE ID =inId
   
  EXCEPTION
  WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20001, SQLCODE || ' ' || SQLERRM );
END StoreProcedureName;

3) Update Query in Oracle

Update Table_1 T1 SET (Column = 'U')
    (
       select Column,Column1,Column2,Column3
       from Table_2 T2
       WHERE T1.Column1 = T2.Column2
    )
WHERE T1.Column2 = T2.Column2

How to execute storeProcedure from sql Developer

SET serveroutput on;
DECLARE
    InParam1 number;
    InParam2 number;
    OutParam1 varchar2(100);
    OutParam2 varchar2(100);
    OutParam3 varchar2(100);
    OutParam4 number;
BEGIN
    /* Assign values to IN parameters */
    InParam1 := 33;
    InParam2 := 89;

    /* Call procedure within package, identifying schema if necessary */
    schema.package.procedure(InParam1, InParam2,
        OutParam1, OutParam2, OutParam3, OutParam4);

    /* Display OUT parameters */
    dbms_output.put_line('OutParam1: ' || OutParam1);
    dbms_output.put_line('OutParam2: ' || OutParam2);
    dbms_output.put_line('OutParam3: ' || OutParam3);
    dbms_output.put_line('OutParam4: ' || OutParam4);
END;
/

Primary Key:
Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.

Create table with Primary Key:
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO

Alter table with Primary Key:
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO

Unique Key:
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:
ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)
GO

how do I make a composite key with SQL Server Management Studio?

Note : Just Increment Identity (Do not Set as Primary) for Column_ID which increment purpose.

Select both column which you want to set composite key.  With help of ( Ctrl and Mouse)

Does Not allow null.

Right click Set both columns as a primary key, it’s called Composite key.

Result: we can’t enter same combination for “Column_Com_Id” and “Column_CM_ID”.

How to Create Computed Column Specification:

CREATE TABLE [dbo].[TblTesting](
[Start_ID] [int] IDENTITY(1,1) NOT NULL,
[Reading_1] [decimal](18, 2) NULL,
[Reading_2] [decimal](18, 2) NULL,
[Reading_3] [decimal](18, 2) NULL,
[Reading_4] [decimal](18, 2) NULL,
[Price] [money] NOT NULL,
[Qty]  AS ([dbo].[FunTesting]([Reading_1],[Reading_2],[Reading_3],[Reading_4])),
[Amount]  AS ([dbo].[FunTest]([Reading_1],[Reading_2],[Reading_3],[Reading_4],[Price])),
  )

Computed Columns

([dbo].[FunTesting]([Reading_1],[Reading_2],[Reading_3],[Reading_4]))

CREATE FUNCTION [dbo].[FunTesting]
(
-- Add the parameters for the function here
@Reading_1 Decimal(18,2),@Reading_2 Decimal(18,2),@Reading_3 Decimal(18,2),@Reading_4 Decimal(18,2)
)
RETURNS Decimal(18,2)
AS
BEGIN
-- Declare the return variable here
DECLARE @RES Decimal(18,2)
    SET @RES = @Reading_1 + @Reading_2 + @Reading_3 + @Reading_4
RETURN
(
 @RES
)


Computed Columns

([dbo].[FunTesting]([Reading_1],[Reading_2],[Reading_3],[Reading_4],[Price]))

CREATE FUNCTION [dbo].[FunTest]
(
-- Add the parameters for the function here
@Reading_1 Decimal(18,2),@Reading_2 Decimal(18,2),@Reading_3 Decimal(18,2),@Reading_4 Decimal(18,2),@Price MONEY
)
RETURNS Decimal(18,2)
AS
BEGIN
-- Declare the return variable here
DECLARE @RES Decimal(18,2)
    SET @RES = (@Reading_1 + @Reading_2 + @Reading_3 + @Reading_4) * @Price
RETURN
(
 @RES
)